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.
Re:How's it look without disk caching?
Matts on 2002-02-22T19:42:45
I'm no good at that sort of low level thing... How would I find out?Re:How's it look without disk caching?
Elian on 2002-02-22T20:04:57
The most sure way is to bounce the machine before doing the read test. That's probably a bit much, so the next best way would be to try the read test again after you're pretty sure that the disk caches have been flushed. Running Mozilla for a while's a good way to do that.:)
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.
Re:Fork & Threads
Matts on 2002-02-22T19:44:59
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 than that, everything is peachy. Great work!
Re:Third party support
Matts on 2002-02-23T07:48:57
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.Re:Third party support
lachoy on 2002-02-23T17:57:23
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:-)
