Re: [sqlite] 1.1GB database - 7.8 million records

[Simon Slavin]

On 31 Dec 2012, at 9:13pm, Roger Binns  wrote:

> You should create the indexes after inserting the data.  If you don't you
> end up with index data and row data intermingled in the database which
> makes things slower.
> 
> Journaling off definitely worked for me when I benchmarked it.  I was
> working with a 15GB dump from postgres on Linux.

To elucidate, theoretically the fastest combination of operations is ...

Turn journalling off
Delete all indexes
If you need to create tables, do it here
BEGIN
... do all your inserts
COMMIT
Create indexes
Turn journalling on

Note that if you're using FOREIGN KEYS that changes this a little since SQLite 
won't let you do an insert without an index which lets it do the necessary 
lookups.

However, unless you're doing this sort of thing as part of a regular working 
day this really doesn't matter.  It takes 5 minutes to load your rows instead 
of 4 ?  Who cares.  For anything that takes more than 3 minutes I'm off getting 
coffee anyway.  For normal operation the amount of time you save from

BEGIN
DELETE FROM myTable;
... do all your inserts
COMMIT

isn't worth the difference, and saves a lot of programming and error-handling.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 1.1GB database - 7.8 million records

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 31/12/12 12:33, Michael Black wrote:
> Journaling off might work if you don't have any indexes.

You should create the indexes after inserting the data.  If you don't you
end up with index data and row data intermingled in the database which
makes things slower.

Journaling off definitely worked for me when I benchmarked it.  I was
working with a 15GB dump from postgres on Linux.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlDiABEACgkQmOOfHg372QQesQCgjTJbDb3Yt2iyC/7vUEJAFuTq
1T0AoIPDu/fpdtOoEEnmkNn4vr/lGTpe
=bgfW
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 1.1GB database - 7.8 million records

[Michael Black]

I turned journalling off in my test program and sqlite3 is running about
3000/inserts per second right now at around 6M records.  Lousy performance
compared to WAL mode.
journal=memory behaved the same way.

Journaling off might work if you don't have any indexes.

Taking the primary key off of my test program cranks up to 374,000
inserts/sec average over 50M records with journal_mode=WAL - pretty decent
throughput I'd say.

Interestingly enough sqlite4 is slower in that case (without the random
primary key)running around 80,000 inserts/sec

So sqlite4 is faster when inserting random numbers in the index but slower
when not (i.e. only the rowid index).



-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Roger Binns
Sent: Monday, December 31, 2012 1:59 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] 1.1GB database - 7.8 million records

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 31/12/12 10:35, Michael Black wrote:
> One transaction like you did is best.
> 
> I recently ran a test which ran pretty well with a commit every 1M
> records. Doing every 100,000 records slowed things down dramatically.

If you are creating the initial database then you can turn journalling etc
off until the database, indices etc are fully created.  This will get you
a little more speed too.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlDh7mkACgkQmOOfHg372QR/dwCfVhcMaYJIr6pTFKsL1LbaFiVJ
xk8An3lyoOv/LLmi9lWh8ZFEFJdCGfZO
=ie9C
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 1.1GB database - 7.8 million records

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 31/12/12 10:35, Michael Black wrote:
> One transaction like you did is best.
> 
> I recently ran a test which ran pretty well with a commit every 1M
> records. Doing every 100,000 records slowed things down dramatically.

If you are creating the initial database then you can turn journalling etc
off until the database, indices etc are fully created.  This will get you
a little more speed too.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlDh7mkACgkQmOOfHg372QR/dwCfVhcMaYJIr6pTFKsL1LbaFiVJ
xk8An3lyoOv/LLmi9lWh8ZFEFJdCGfZO
=ie9C
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 1.1GB database - 7.8 million records

[Michael Black]

One transaction like you did is best.

I recently ran a test which ran pretty well with a commit every 1M records.
Doing every 100,000 records slowed things down dramatically.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of
joe.fis...@tanguaylab.com
Sent: Monday, December 31, 2012 12:32 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] 1.1GB database - 7.8 million records

Very impressive. With SQLite 3.7.14.1
Took 4 minutes to load a 1.5GB MySQL dump with 7.8 million records.
Count(*) takes 5 seconds. Even runs on a USB key. Wow!
Also loaded a smaller one (33MB database [30 tables/dumps] in 10 
seconds, largest file had 200,000 records).

I wrapped the 7.8 million records in one [BEGIN TRANSACTION;] [COMMIT 
TRANSACTION;] block.
Had to use VIM to edit the file.
Using the Transaction is significantly faster with a large number of 
inserts.
What's the rule of thumb on how many records per transaction?
Does it matter how many are used, is one transaction OK?

Joe Fisher
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 1.1GB database - 7.8 million records

[joe.fis...@tanguaylab.com]

Very impressive. With SQLite 3.7.14.1
Took 4 minutes to load a 1.5GB MySQL dump with 7.8 million records.
Count(*) takes 5 seconds. Even runs on a USB key. Wow!
Also loaded a smaller one (33MB database [30 tables/dumps] in 10 
seconds, largest file had 200,000 records).


I wrapped the 7.8 million records in one [BEGIN TRANSACTION;] [COMMIT 
TRANSACTION;] block.

Had to use VIM to edit the file.
Using the Transaction is significantly faster with a large number of 
inserts.

What's the rule of thumb on how many records per transaction?
Does it matter how many are used, is one transaction OK?

Joe Fisher
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 1.1GB database - 7.8 million records

[joe.fis...@tanguaylab.com]

Very impressive.
Took 4 minutes to load a 1.5GB MySQL dump with 7.8 million records.
Count(*) takes 5 seconds. Even runs on a USB key. Wow!

Also loaded a smaller one (33MB database [30 tables/dumps] in 10 seconds).

Joe Fisher
Oregon State University

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users