Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

Matts (1087)

Matts
  (email not shown publicly)

I work for MessageLabs [messagelabs.com] in Toronto, ON, Canada. I write spam filters, MTA software, high performance network software, string matching algorithms, and other cool stuff mostly in Perl and C.

Journal of Matts (1087)

Friday February 22, 2002
12:26 PM

More on DBD::SQLite

[ #3042 ]

More performance tests today (after fixing some mem leaks). I imported my 72MB axkit.org log file into an SQLite database. The import took a while - about 10 mins perhaps. Though I guess that's not *too* bad...

Following that I started doing some queries on it, like what are the most popular URLs, what are the most seen 404's, etc.

Results are coming back in under a second. This thing is amazing. I didn't even put an index on it, so it was doing a full table scan, and stuff like:

SELECT url, count(*) as count FROM access_log
WHERE url like '%.xml'
GROUP BY url
ORDER BY count desc
LIMIT 50

was coming right back at me instantly. Brilliant stuff. I'm going to investigate using this here at work for some offline query stuff.

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • I'd bet a good part of your performance is coming from the OS caching the file in memory. How's the performance look if there's not nearly so much cache handy? (Still good, I expect, but...)
  • How does SQLite handle fork? Can a child inherit a database handle or will it choke (like most RDBM's)?

    Also, have you tried using Perl's threads to run concurrent queries? If you don't have a threaded Perl, there's always the "Inline" modules. :)

    Just curious.

    • Well it locks the entire DB on updates anyway, so I wouldn't recommend it much for multi-user systems.

      I'm curious about all that stuff too, but it's hard to setup those sorts of tests. Well actually not *that* hard - I could just set it up under mod_perl, but I don't have that many hours in the day ;-)
  • The next version of SPOPS, which will come out whenever I wake up -- I have a strict no-bleary-eyed CPAN release policy -- includes support for SQLite. (Oracle too, but let's keep on message.)

    One thing I noticed is that SQLite doesn't seem to like table definitions with an explicit 'NULL' declaration. For instance, the following will fail:

    CREATE TABLE testme (
    id int not null primary key,
    name varchar(20) null
    )

    with a DBD::SQLite::db do failed: near "null": syntax error at ...

    Other tha

    • Watch out for the memory leaks though :-D

      I've got a fix for some of them, but I'm still seeing slight leaks. I'm wondering if that's not in the underlying library though, since leaking 1 scalar per request would see more leakage than I've seen so far.
      • I'm glad I checked the latest CPAN releases before releasing this version of SPOPS -- I see you put the $dbh->last_insert_rowid method in, which means that I don't have to use the random string key generation method :-)