Re: [sqlite] INDEX Types

[Black, Michael (IS)]

OK...you're right...a reboot kills it.  Here's the program I generated the data 
with:

#include <stdio.h>
main()
{
        char sql[4096];
        char key[9];
        long *lkey=key;
        int i;
        FILE *fp1,*fp2;
        fp1=fopen("gendat1.sql","w");
        fp2=fopen("gendat2.sql","w");
        fputs("CREATE TABLE t (a text);",fp1);
        fputs("CREATE INDEX tx on t(a);",fp1);
        fputs("BEGIN;",fp1);
        fputs("BEGIN;",fp2);
        for(i=0;i<1100000;++i) {
                int j;
                for(j=0;j<8;++j) {
                        key[j]=(random()%26)+'a';
                }
                if (i < 1000000) {
                        fprintf(fp1,"insert into t values('%s');\n",key);
                }
                else {
                        fprintf(fp2,"insert into t values(%ld);\n",*lkey);
                }
        }
        fputs("COMMIT;",fp1);
        fputs("COMMIT;",fp2);
        fclose(fp1);
        fclose(fp2);
}


time sqlite3 index.db < gendat1.sql
21.594u 5.497s 0:27.87 97.1%    0+0k 0+0io 0pf+0w
time sqlite3 index.db < gendat2.sql
12.302u 1.035s 0:04.46 74.6%     0+0k 0+0io 0pf+0w

rm index.db
time sqlite3 index.db < gendat1.sql
20.641u 6.477s 0:27.91 97.1%    0+0k 0+0io 0pf+0w

reboot
time sqlite3 index.db < gendat2.sql
2.474u 1.219s 1:28.56 4.1%      0+0k 0+0io 0pf+0w

However, reboot again and add "select count(*) from a;" as the first line of 
gendat2.sql

time sqlite3 index.db <gendat2.sql
1100000
2.389u 1.123s 0:07.39 47.3%     0+0k 0+0io 0pf+0w
That's faster than the 2nd run before rebooting.




I'm on Redhat 5.7
Intel Xeon E5420 @ 2.5Ghz 32G memory  SCSI disks (Dell)


I noticed that if you clear cache like this (as root)

sync; echo 3 >/proc/sys/vm/drop_caches

You don't see the same performance drop.

So I'm wondering is this is disk cache instead of Linux cache.


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 Fabian [fabianpi...@gmail.com]
Sent: Wednesday, November 09, 2011 1:36 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] INDEX Types


2011/11/9 Luuk <luu...@gmail.com>

>
> no, i did not reboot, I created two NEW databases, so rebooting should not
> make a big difference because its not cached in both cases.


If you just created it, why wouldn't it be in cache? You just written all
the data, so it's highly likely to be in some kind of cache (OS,HD,etc.).

As already explained to Michael, I experience no problems when I create the
database, close it, re-open it, and add some more rows. The problem only
appears if I reboot in between those steps.
_______________________________________________
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