For this particular test cache_size didn't matter much...actually ran a touch
faster with smaller cache (although may be within the margin of error)
I dropped the cache_size to 125 pages (which matches the 16X increase in page
size that I did)
On SSD:
time sqlite3 gen.db <gen.sql
real 3m55.124s
user 0m43.293s
sys 0m36.095s
Compared with 2000 default cache_size:
time sqlite3 gen.db <gen.sql
real 4m4.816s
user 0m43.606s
sys 0m34.004s
So it appears that the database size reduction seems to make most of the
difference here.
And here's how I generate the test data:
#include <stdio.h>
int
main ()
{
int i;
char s[513];
for (i = 0; i < 512; ++i)
s[i] = '0';
s[512] = 0;
printf("pramga page_size=16384;\n");
printf("pramga cache_size=125;\n");
printf("CREATE TABLE [TestTable] ( [Id] INTEGER PRIMARY KEY AUTOINCREMENT,
[Text] TEXT);\n");
printf("begin;\n");
for (i = 0; i < 1000000; ++i) {
printf ("INSERT INTO TestTable (id, text) VALUEs (abs(random() %% (1 <<
48)), '%s');\n", s);
}
printf("commit;\n");
return 0;
}
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 Simon Slavin [slav...@bigfraud.org]
Sent: Monday, January 16, 2012 3:53 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] SSD with TRIM
On 16 Jan 2012, at 7:18am, Max Vlasov wrote:
> Michael, thanks for the info, your tests confirms some advantages of ssd,
> at least on some of the current platform (linux, can you confirm?).
> Probably the page size is a parameter that also should be adjusted for
> optimal performance. I suppose it can be different for different ssd
> models, but it's just a guess.
Optimal page size is related to cache size. And the design of standard desktop
computers means you have at least two levels of cache between your CPU and your
hard disk surface. And your SQLite app may not be the only thing running on
the computer that wants frequent disk access. So figuring out an optimal page
size isn't easy.
Simon.
_______________________________________________
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
