I also ran without WAL mode (about 3X slower than WAL mode)
On SSD: (system spent a LOT of time in disk wait states)
time sqlite3 gen.db <gen.sql
real 10m47.173s
user 0m46.647s
sys 0m34.217s
I didn't bother with hard drive without WAL mode -- see below....
I also ran with page_size=16384 (I noticed things slowed down on me at about
1/4th the way through for me with 1024 page size)
On SSD with 16384 page size and no WAL mode:
time sqlite3 gen.db <gen.sql
real 4m4.816s
user 0m43.606s
sys 0m34.004s
Note: Database is only 595M with this page size. Much more efficient storage
may explain most of the time difference.
On hard drive with 16384 page size and no WAL mode (now you can see the SSD
latency advantage):
time sqlite3 gen.db </mnt/ssd/gen.sql
real 17m18.919s
user 0m42.658s
sys 0m36.355s
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 Max Vlasov [max.vla...@gmail.com]
Sent: Saturday, January 14, 2012 1:24 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] SSD with TRIM
On Sat, Jan 14, 2012 at 6:12 AM, Simon Slavin <slav...@bigfraud.org> wrote:
>
> Fast. Fasty fast. Speed is high. INSERT, UPDATE, and DELETE all
> significantly faster. SELECT is a bit faster, but there's less difference.
>
>
Simon, very interesting. Can you make some tests related to internal
fragmentation? As an advanced user of sqlite, you probably will easily
invent your own tests :), but if not there is a test I used at least once
INSERT INTO TestTable (id, text) VALUEs (abs(random() % (1 << 48)),
'12345678901234567...') {the text is about 512 bytes long}
1,000,000 inserts should create a 1GB base and the time with general hd was
above 15 minutes, maybe even half an hour. So I'm very interested to see
was there any progress with overall ssd performance.
Thanks,
Max
_______________________________________________
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
