On Tue, 14 Dec 2004 13:54:35 -0500, D. Richard Hipp <[EMAIL PROTECTED]> wrote:
> Christopher Petrilli wrote:
> > Has anyone had any experience in storing a million or more rows in a
> > SQLite3 database? I've got a database that I've been building, which
> > gets 250 inserts/second, roughly, and which has about 3M rows in it.
> > At that point, the CPU load is huge.
> >
>
> I just ran a test case inserting 3 million rows in a database.
> Wall-clock time was 122 seconds for a rate just shy of 25000 inserts
> per second. The final database size was 222428160. To check to see
> if performance was falling off with increases size, I then inserted
> an additional million rows. 41 seconds: 24390 inserts per second.
> New file size 297440256. This is on three year old hardware.
That was about my insert performance as well (AMD64), as it's
basically disc limited any more. I found substantial differences
between my SATA drive and my PATA drive though.
> Inserts can be significantly slower if you have indices. The more
> indices you have the slower things might go. (Depending on what
> your indices and your data look like.) If possible, it is recommended
> that you do all your inserts first, then do the CREATE INDEX statements
> as a separate step afterwards.
Alas, I think it is the indexing that's killing me. I'm contemplating
shrinking the size of the database (i.e. partitioning the data into
multiple databases, and doing some in-memory joining in my
application), which means it'd be easier to add the indexes after the
database has been "filled" if that makes sense.
> What does your schema look like?
Here's the schema, or mostly... I've removed a bunch of columns that
aren't really public... think of it as another 8 INTEGER columns,
without indexes.
CREATE TABLE events (
event_id VARCHAR(32) PRIMARY KEY,
sensor_ts INTEGER NOT NULL,
my_ts INTEGER NOT NULL,
sensor_id INTEGER NOT NULL,
src_ip INTEGER NOT NULL,
dst_ip INTEGER NOT NULL,
event_class INTEGER NOT NULL,
event_type INTEGER NOT NULL,
user_name TEXT,
info TEXT);
CREATE INDEX events_sensor_ts_idx ON events(sensor_ts);
CREATE INDEX events_conduit_ts_idx ON events(conduit_ts);
CREATE INDEX events_src_ip_idx ON events(src_ip);
CREATE INDEX events_dst_ip_idx ON events(dst_ip);
--
| Christopher Petrilli
| [EMAIL PROTECTED]
