#1 What's the size of your database?
#2 What's your cache_size setting?
#3 How are you loading the data? Are your table inserts interleaved or by
table? Your best bet would be by interleaving during insert so cache hits
would be better.
Looks to me like you're getting disk thrashing in test3 and test4 which
cache_size could affect also.
And are you running your test twice to bypass the initial cache filling of
sqlite?
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
________________________________
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Bo Peng [ben....@gmail.com]
Sent: Saturday, October 22, 2011 10:05 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Concurrent readonly access to a large database.
> It's not only speed in KB/sec that matters. It's also disk system
> usage as reported by iostat. If it's close to 100% then SQLite can't
> do any better.
A sad day.
I copied the database to a faster driver with RAID 0, made another
copy of the database (referred to as DB1), and ran another set of
tests:
test1: two sequential processes of sqlite count(*) table1 and table 2
in DB1 --> 7m15s
test2: two concurrent processes of sqlite count(*) table1 and table2
in DB1 --> 5m22s
test3: four concurrent processes of sqlite count(*) table 1, 2, 3, and
4 in DB1 --> 12m58s
test4: two concurrent processes of sqlite count(*) table1 in DB1, and
table1 in DB2 --> 9m51s.
Although running two or more processes can save some time, the
performance gain is not that big (tests 2 and 3), splitting the
database into several smaller ones would not help either (test 4).
Anyway, the iostat output of my system is
2011 Oct 22 21:16:36, load: 0.03, disk_r: 2676 KB, disk_w: 0 KB
UID PID PPID CMD DEVICE MAJ MIN D BYTES
0 0 0 ?? 14 8 65536
503 732 730 sqlite3 ?? 14 14 R 1323008
503 731 730 sqlite3 ?? 14 14 R 1355776
If I understand correctly, the IO load is only 3% when two sqlite3
processes are running, so perhaps I can still tweak sqlite3 to run
faster. I will also copy the database around and see if other disks
(SSD?), operating system (linux?), and file systems can provide better
performance.
Thanks again for all the help,
Bo
_______________________________________________
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
