On Tue, 2003-11-04 at 05:39, D. Richard Hipp wrote:
> Avner Levy wrote:
> >
> > We have just finished testing the same scenario with MySql at amazingly 
> > they continued to insert 1500-3000 rows per second even when the 
> > database had 60,000,000 records. I don't know how this magic is done...
> 
> Nor do I.  If anybody can clue me in, I would appreciate it.  I suspect
> the secret must be in the file format for their indices.  Does anybody
> have any idea what that format is?  Or how indices work in MySQL?

I think I can reproduce this, but it took me a while to make things
fair:

I started my tests by simply loading 30,000 records of this variety into
both systems.

MySQL: 1m43.661s
SQLite: 0m3.198s

That's when the columns are labeled VARCHAR(255) NOT NULL to MySQL; If I
decide to be terribly evil, you can see CHAR(255) makes everything much
worse:

MySQL: 2m9.326s

Interestingly enough, if I give MySQL a bone, and call them INTEGER NOT
NULL:

MySQL: 0m13.327s

Which sounds a whole hell of a lot more like the scale I'm seeing
described. SQLite still wins though...

Now my next tests involved writing 100,000 records, doing a filesystem
sync and waiting for the drive to drop, then burning in 30,000 records:

SQLite: 11m37.869s
MySQL: 0m47.218s

This looks like I've got it reproduced after all, doesn't it? Except
that wasn't fair- I used INTEGER NOT NULL that time.

MySQL: 12m57.978s (VARCHAR)
MySQL: 12m13.078s (CHAR)

So for a 3x increase in data, SQLite takes 30x longer, and MySQL appears
to scale linearly until we actually make it work as hard as SQLite, in
which case it only takes 10x longer- and when we try and cheat MySQL
again with CHAR(255), we see almost the same values.

Sound more fair? 10x versus 30x?

Now, then.

It should be quite obvious that what SQLite loses is twofold;

1. SQLite cannot give INTEGERs any special consideration (because we're
typeless) so therefore traversing variable-length keys costs us quite a
bit.

2. MySQL is apparently packing several index values into the same block-
something that might be worth looking into.


#1 appears to allow MySQL to shortcut SQLite in a small number of cases-
actually, that's conjecture. I don't know how often it is that we build
a table with multiple INTEGER indexes-- it doesn't happen often enough
to me, but oh well... let's say small number of scenarios then.

... but #2- which has less of an effect overall allows MySQL to change
30x into 10x- something almost worth optimizing... I think this supports
the idea that a re-engineering of the indexing system is definitely a
good idea.


NOTE that I was never able to achieve quite the same values as reported-
interesting ones all the same, but what's curious is that MySQL writes
to separate files for table data. Having 60,000,000 records in one table
simply isn't going to affect MySQL's inserts against another table. It's
a worthless test, IMO-- this is what the ATTACH keywords are for :)


---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Reply via email to