Re: [sqlite] SQLite :memory: performance difference between v2 and v3?
> In any case we seem to be settling to a long term > sustained rate of > about 40 flushes per second for WinXP. Since SQLite > is getting only 10 > inserts per second, it seems to me that SQLite must > be doing about 4 > flushes per insert. Does that seem right to you > Richard? For what it's worth, it's maybe slightly worse on FreeBSD. Here's a sample of iostat for the (original) C benchmark with sqlite2: tty ad0 cpu tin tout KB/t tps MB/s us ni sy in id 0 43 11.71 661 7.56 12 0 5 2 81 0 129 13.65 457 6.08 2 0 1 1 96 0 43 13.16 662 8.52 2 0 6 2 91 0 43 13.21 767 9.89 2 0 8 2 89 0 43 13.61 591 7.86 5 0 2 1 93 0 43 13.19 523 6.74 0 0 5 2 93 0 43 13.21 520 6.71 1 0 3 1 95 0 43 13.45 453 5.95 1 0 6 0 93 0 43 13.26 663 8.59 2 0 4 1 93 0 43 13.17 410 5.27 2 0 2 2 95 0 43 13.52 495 6.53 2 0 2 0 97 8 43 13.41 565 7.40 2 0 6 1 91 I/O transactions/s rate probably averages to something like 550, and the benchmark reported ~90 queries/s, so the rate is around 6 I/O ops per INSERT. sqlite3 is similar, but with a twist: tty ad0 cpu tin tout KB/t tps MB/s us ni sy in id 0 43 11.48 560 6.28 2 0 4 2 92 0 129 11.79 695 8.01 2 0 5 1 92 0 43 12.12 405 4.79 5 0 5 0 91 0 43 12.57 483 5.92 1 0 7 0 93 8 43 12.66 384 4.74 2 0 3 2 94 0 43 12.00 416 4.87 1 0 4 0 95 0 43 11.93 756 8.81 2 0 3 2 93 0 43 12.04 696 8.19 3 0 3 1 93 8 43 12.36 677 8.17 1 0 3 1 95 8 43 12.38 680 8.22 2 0 5 0 94 0 43 11.74 554 6.36 0 0 1 1 98 0 43 12.16 657 7.80 1 0 2 1 96 0 43 12.16 789 9.37 2 0 3 1 95 0 43 12.36 573 6.92 2 0 5 2 92 The I/O transactions/s rate is similar (or at least not significantly lower) but the transactions are a bit smaller. Measured ~65 INSERTs/sec. so it's ~8 I/O transactions per INSERT. Exact statistics calulations are left as an excercise for the reader :) (interpretation of cpu stats: time spent in file system & disk is "sy" (=system) + "in" (=interrupt); id=idle, us=user) __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] SQLite :memory: performance difference between v2 and v3?
On 5/4/06, Dennis Cote <[EMAIL PROTECTED]> wrote: It seems *really* strange that deleting the file would reduce the average run time of the 1 loop case by more than 60 seconds. Neither overwriting the file or deleting the file should take anywhere near this long. These are small files of only 10K characters after all. I think I understand that part. In one case (the deleted file) it's going to the free disk block list to allocate new chunks of disk space for the file. This is probably very quick since that's probably cached. In the other case, If the file isn't deleted it has to read the directory entry, figure out where the disk block is that represents that portion of the file, then seek to it, insert the data into the existing block image, then write.
Re: [sqlite] SQLite :memory: performance difference between v2 and v3?
Doug Currie wrote:
Add the line:
DeleteFile("test.txt");
in front of the CreateFile() call...
Doug,
This does make a difference. I thought that the CREATE_ALWAYS flag to
CreateFile would delete any existing file, but upon further
investigation I found out that what it does is open the file and set its
length to 0. Apparently it it faster to delete the file. In either case
it seems to be a side effect of these calls, since their execution time
is not included in the measurements.
There seems to be another effect due to the run time of the program. For
loops of 1000 flushes I consistently get higher rates than for loops of
1 flushes. I know some OS's lower the priority of long running
process that do lots of I/O. This is supposed to be an indication of a
long running batch type process so they do this to improve the response
to more interactive processes. In my case the CPU is spending most of
its time in the idle process waiting for the disk so this still doesn't
make much sense.
The following is the from my first six runs with 1000 flushes per run.
The first column is the time, the second is the flushes/sec rate.
2638.5
1855.6
2638.5
1952.6
2638.5
1855.6
These are the average and standard deviation of the execution time
above, as well as the ratio of the standard deviation to the average as
percentage which is an indication of the variability.
22.246.5
4.28.9
19.02%19.12%
When I increased the number of loops by a factor of 10 to average over a
longer time I got the following results for seven runs.
27436.5
31431.8
23842.0
26537.7
31232.1
22943.7
26837.3
271.437.3
32.84.5
12.07%12.03%
The average flush rate dropped to 80% of it previous value, and the
variability dropped by about 40%.
After I saw your posting, I modified my code to explicitly delete the
file before each run and repeated the tests. For 10 runs with 1000 loops
I got:
1283.3
1283.3
1190.9
1566.7
1283.3
1471.4
1471.4
1471.4
1283.3
1283.3
12.878.9
1.37.9
10.29%10.00%
The average flush rate increased by 70% and the variability dropped by
about 50%.
I then repeated the test with loops of 1 flushes per run and got the
following results.
20748.3
21147.4
20449.0
19750.8
20648.5
205.048.8
5.11.2
2.51%2.55%
Again the average flush rate over the longer run is about 60% of what it
was for the short runs. The variability is only 20% of what it was
without the explicit delete.
It seems *really* strange that deleting the file would reduce the
average run time of the 1 loop case by more than 60 seconds. Neither
overwriting the file or deleting the file should take anywhere near this
long. These are small files of only 10K characters after all.
Something else is going on here. If we can figure out what, we may be
able to speed up SQLite under Windows. It currently seems to be running
about 1/8 the speed of FreeBSD when doing inserts with individual
transactions.
In any case we seem to be settling to a long term sustained rate of
about 40 flushes per second for WinXP. Since SQLite is getting only 10
inserts per second, it seems to me that SQLite must be doing about 4
flushes per insert. Does that seem right to you Richard?
Dennis Cote
Re: [sqlite] SQLite :memory: performance difference between v2 and v3?
Hi, - Original Message - From: "Dennis Cote" <[EMAIL PROTECTED]> To: <sqlite-users@sqlite.org> Sent: Thursday, May 04, 2006 2:27 PM Subject: Re: [sqlite] SQLite :memory: performance difference between v2 and v3? > All, > > More mysteries. To investigate this low insert performance under WinXP I > wrote a simple test program that loops writing one character to a file > and then flushing the file. The strange thing is that it seems to > alternate between two different run times as shown below. This is for 6 > consecutive runs. If you are using WinXP with Service Pack 2 this can be the problem. I have experienced a lot of issues with the Windows XP Firewall. The funniest (or strangest) thing I have found so far is that the Firewall seems to block certain things even if it is deactivated. The problem gets a lot worse if you use an Anti-Virus software. In my case I use Viruscan and although I unchecked all the options to check TCP/IP connections, emails, etc it still slows my network traffic down. If I disable it everything works perfectly. For example I work with mysql and connections to query data from the server (another PC in local network) takes forever when Viruscan is active. Just some thoughts. Best Regards, COS
Re: [sqlite] SQLite :memory: performance difference between v2 and v3?
Thursday, May 4, 2006, 1:27:49 PM, Dennis Cote wrote:
> More mysteries. To investigate this low insert performance under WinXP I
> wrote a simple test program that loops writing one character to a file
> and then flushing the file. The strange thing is that it seems to
> alternate between two different run times ...
Your program...
$ flushtst
15 seconds, 67 flushes/sec
$ flushtst
26 seconds, 38 flushes/sec
$ flushtst
26 seconds, 38 flushes/sec
$ flushtst
27 seconds, 37 flushes/sec
$ flushtst
27 seconds, 37 flushes/sec
Add the line:
DeleteFile("test.txt");
in front of the CreateFile() call...
$ flushtst
15 seconds, 67 flushes/sec
$ flushtst
14 seconds, 71 flushes/sec
$ flushtst
13 seconds, 77 flushes/sec
$ flushtst
12 seconds, 83 flushes/sec
e
Re: [sqlite] SQLite :memory: performance difference between v2 and v3?
This program is almost completely I/O bound. It spends all its time sleeping so its CPU usage is nearly zero. There might be some disk contention from other programs, but I would think that should be fairly constant. I am re-testing with longer run times to check this. You give up your time slice to the operating system when you block waiting on I/O. If the operating system doesn't return control to you before your current I/O completes then you'll certainly slow down. I've seen current versions of windows just freeze for long periods if a CD isn't readable, so I'm certain this can happen. Given Ivan's notes about how NTFS works under the covers I would bet on his idea over mine though.
Re: [sqlite] SQLite :memory: performance difference between v2 and v3?
Jay Sprenkle wrote: Initially performance is good but the degrades? Some other processes must be sucking up cpu time? Jay, This program is almost completely I/O bound. It spends all its time sleeping so its CPU usage is nearly zero. There might be some disk contention from other programs, but I would think that should be fairly constant. I am re-testing with longer run times to check this. Dennis Cote
Re: [sqlite] SQLite :memory: performance difference between v2 and v3?
--- Dennis Cote <[EMAIL PROTECTED]> wrote: > All, > > More mysteries. To investigate this low insert > performance under WinXP I > wrote a simple test program that loops writing one > character to a file > and then flushing the file. The strange thing is > that it seems to > alternate between two different run times as shown > below. This is for 6 > consecutive runs. Don't know for sure, but I can offer some educated guesses for the scattering of results: - NTFS is a fairly complex file system (more complex than traditional FS-es), with several tables into which file data must be written and cross linked, as well as a data journal. The scattering of locations for these journals means that seek times are different, depending where the data is laid out on disk. In particular because there's a journal, consecutive program runs never actually write to the same place on the disk even if it seems so to the application. The results you have seen (alternating between two values) can also be provoked on complex database systems (in particular PostgreSQL) with simple benchmarks (e.g. pgbench) on relativly simple non-journaled file systems (such as UFS) when one benchmark run nearly fills a write-ahead log and the log gets processed/commited in the next run. - Windows has many background disk users / writers - Explorer, registry and other components are known to "wake up" periodically and write their data (whatever it is). __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] SQLite :memory: performance difference between v2 and v3?
On 5/4/06, Dennis Cote <[EMAIL PROTECTED]> wrote: All, More mysteries. To investigate this low insert performance under WinXP I wrote a simple test program that loops writing one character to a file and then flushing the file. The strange thing is that it seems to alternate between two different run times as shown below. This is for 6 consecutive runs. C:\Temp\test\Debug>test.exe 13 seconds, 77 flushes/sec C:\Temp\test\Debug>test.exe 22 seconds, 45 flushes/sec C:\Temp\test\Debug>test.exe 17 seconds, 59 flushes/sec C:\Temp\test\Debug>test.exe 18 seconds, 56 flushes/sec C:\Temp\test\Debug>test.exe 21 seconds, 48 flushes/sec C:\Temp\test\Debug>test.exe 22 seconds, 45 flushes/sec Initially performance is good but the degrades? Some other processes must be sucking up cpu time?
Re: [sqlite] SQLite :memory: performance difference between v2 and v3?
All,
More mysteries. To investigate this low insert performance under WinXP I
wrote a simple test program that loops writing one character to a file
and then flushing the file. The strange thing is that it seems to
alternate between two different run times as shown below. This is for 6
consecutive runs.
C:\src\sqlite_speed>win_flush_test.exe
26 seconds, 38 flushes/sec
C:\src\sqlite_speed>win_flush_test.exe
18 seconds, 56 flushes/sec
C:\src\sqlite_speed>win_flush_test.exe
26 seconds, 38 flushes/sec
C:\src\sqlite_speed>win_flush_test.exe
19 seconds, 53 flushes/sec
C:\src\sqlite_speed>win_flush_test.exe
26 seconds, 38 flushes/sec
C:\src\sqlite_speed>win_flush_test.exe
18 seconds, 56 flushes/sec
Does anyone have any ideas what might cause this kind of behavior?
My test code is included below. Note in the version using the standard
library, fflush() seems to be ignored under WinXP because it does about
1M flushes per second. Hence the Win32 API version which produces the
results above.
Dennis Cote
#include
#include
#include
#ifdef WIN32
#include
int main(int argc, char *argv[])
{
int i, count = 1000;
char c;
time_t bgn, end;
double t;
HANDLE f;
long written;
f = CreateFile("test.txt", GENERIC_WRITE, 0, NULL, CREATE_ALWAYS,
FILE_ATTRIBUTE_NORMAL, 0);
bgn = time(NULL);
for (i = 0; i < count; i++) {
c = 'a' + i % 26;
WriteFile(f, , 1, , NULL);
FlushFileBuffers(f);
}
end = time(NULL);
CloseHandle(f);
t = difftime(end, bgn);
printf("%0.0f seconds, %0.0f flushes/sec\n", t, count / t);
//system("PAUSE"); //for Dev-Cpp IDE
return 0;
}
#else
int main(int argc, char *argv[])
{
int i, count = 100;
FILE* f;
char c;
time_t bgn, end;
double t;
f = fopen("test.txt", "w");
bgn = time(NULL);
for (i = 0; i < count; i++) {
c = 'a' + i % 26;
fputc(c, f);
fflush(f);
}
end = time(NULL);
fclose(f);
t = difftime(end, bgn);
printf("%0.0f seconds, %0.0f flushes/sec\n", t, count / t);
//system("PAUSE"); //for Dev-Cpp IDE
return 0;
}
#endif
Re: [sqlite] SQLite :memory: performance difference between v2 and v3?
> > Good ol' gprof. > > It works as well today as it did 15 years ago. --- Bill KING <[EMAIL PROTECTED]> wrote: > Unless your app (and the stuff you want to profile) is multi-threaded. Ugh. So true. Valgrind is your friend with multithreaded code. Quantify used to good when Pure Software owned it, I'm not so sure now. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] SQLite :memory: performance difference between v2 and v3?
Using the latest CVS and -O2, the :memory: database test with 100K inserts in a transaction completes in 6.921s. :memory: inserts are now just 15% slower when transactions are not used, as opposed to 7 times slower in yesterday's CVS. The timings for 100K inserts in a transaction with the latest CVS code is the same as yesterday's code (6.875s). > With -O2, the same :memory: no-outer-BEGIN/COMMIT benchmark is 6 times faster > (7.953s now versus > 47.828s with yesterday's CVS). __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] SQLite :memory: performance difference between v2 and v3?
Joe Wilson wrote: >> Just curious: what did you use to generate the >> profile? (i.e. which profiler?) >> > > Good ol' gprof. > It works as well today as it did 15 years ago. > > > __ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com > > Unless your app (and the stuff you want to profile) is multi-threaded. Ugh. -- Bill King, Software Engineer Trolltech, Brisbane Technology Park 26 Brandl St, Eight Mile Plains, QLD, Australia, 4113 Tel + 61 7 3219 9906 (x137) Fax + 61 7 3219 9938 mobile: 0423 532 733
Re: [sqlite] SQLite :memory: performance difference between v2 and v3?
> Just curious: what did you use to generate the > profile? (i.e. which profiler?) Good ol' gprof. It works as well today as it did 15 years ago. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] SQLite :memory: performance difference between v2 and v3?
> > profile results with dirty pages fix: > > > > % cumulative self self > total > > time seconds secondscalls ms/call > ms/call name > > 9.20 0.31 0.31 100011 0.00 > 0.03 sqlite3VdbeExec > > 7.42 0.56 0.25 4849544 0.00 > 0.00 sqlite3VdbeRecordCompare > > 6.68 0.79 0.23 10487713 0.00 > 0.00 parseCellPtr Just curious: what did you use to generate the profile? (i.e. which profiler?) __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] SQLite :memory: performance difference between v2 and v3?
The results below are for a non-optimized build. With -O2, the same :memory: no-outer-BEGIN/COMMIT benchmark is 6 times faster (7.953s now versus 47.828s with yesterday's CVS). --- Joe Wilson <[EMAIL PROTECTED]> wrote: > > Can you rerun your tests with the latest CVS check-in > > and let me know if it makes a difference? > > With the latest CVS, Dennis' :memory: database test without outer > BEGIN/COMMIT is now 4.3 times > faster on my machine (58.1s before, 13.4s now). > > profile results with dirty pages fix: > > % cumulative self self total > time seconds secondscalls ms/call ms/call name > 9.20 0.31 0.31 100011 0.00 0.03 sqlite3VdbeExec > 7.42 0.56 0.25 4849544 0.00 0.00 sqlite3VdbeRecordCompare > 6.68 0.79 0.23 10487713 0.00 0.00 parseCellPtr > 5.04 0.95 0.17 12943618 0.00 0.00 sqlite3VdbeSerialGet > 5.04 1.12 0.17 46 0.00 0.00 sqlite3BtreeMoveto > 4.45 1.27 0.15 6471807 0.00 0.00 sqlite3MemCompare > 3.56 1.40 0.12 19270230 0.00 0.00 get2byte > 3.56 1.51 0.12 12181181 0.00 0.00 findCell > 3.26 1.62 0.11 3227291 0.00 0.00 binCollFunc > 2.97 1.73 0.10 2064924 0.00 0.00 sqlite3pager_get > 2.82 1.82 0.10 4500154 0.00 0.00 sqlite3pager_unref > 2.67 1.91 0.0929816 0.00 0.02 balance_nonroot > 2.37 1.99 0.08 2716112 0.00 0.00 _page_ref > 2.23 2.06 0.07 5270826 0.00 0.00 findOverflowCell > 2.08 2.13 0.07 2100045 0.00 0.00 sqlite3VdbeSerialType > 2.08 2.21 0.07 1332302 0.00 0.00 initPage > 2.08 2.27 0.0786067 0.00 0.00 assemblePage > 1.78 2.33 0.06 34 0.00 0.01 sqlite3BtreeInsert > 1.48 2.38 0.05 6735322 0.00 0.00 put2byte > 1.48 2.44 0.05 3432951 0.00 0.00 pager_lookup > > > results for same test with yesterday's CVS: > > % cumulative self self total > time seconds secondscalls ms/call ms/call name > 83.61 25.2025.20 12 0.25 0.25 > pager_get_all_dirty_pages > 1.87 25.77 0.56 100011 0.01 0.17 sqlite3VdbeExec > 1.00 26.07 0.30 4849544 0.00 0.00 sqlite3VdbeRecordCompare > 0.78 26.30 0.23 10487713 0.00 0.00 parseCellPtr > 0.63 26.49 0.19 12943618 0.00 0.00 sqlite3VdbeSerialGet > 0.60 26.67 0.18 46 0.00 0.00 sqlite3BtreeMoveto > 0.50 26.82 0.15 3432951 0.00 0.00 pager_lookup > 0.43 26.95 0.13 2064924 0.00 0.00 sqlite3pager_get > 0.38 27.07 0.12 6471807 0.00 0.00 sqlite3MemCompare > 0.36 27.18 0.11 19270230 0.00 0.00 get2byte > 0.36 27.29 0.11 1368027 0.00 0.00 reparentPage > 0.36 27.39 0.1129816 0.00 0.03 balance_nonroot > 0.33 27.50 0.10 2716112 0.00 0.00 _page_ref > 0.33 27.59 0.10 1858370 0.00 0.00 getAndInitPage > 0.30 27.68 0.09 4849549 0.00 0.00 fetchPayload > 0.28 27.77 0.09 6735322 0.00 0.00 put2byte > 0.28 27.86 0.09 1964919 0.00 0.00 getPage > 0.27 27.93 0.08 12181181 0.00 0.00 findCell > 0.27 28.02 0.08 5270826 0.00 0.00 findOverflowCell > 0.27 28.09 0.08 2722659 0.00 0.00 pageDestructor > 0.27 28.18 0.08 1332302 0.00 0.00 initPage > 0.27 28.25 0.08 359548 0.00 0.00 insertCell > 0.27 28.34 0.08 34 0.00 0.01 sqlite3BtreeInsert > 0.25 28.41 0.07 4500154 0.00 0.00 sqlite3pager_unref > > > __ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com > __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] SQLite :memory: performance difference between v2 and v3?
> Can you rerun your tests with the latest CVS check-in > and let me know if it makes a difference? With the latest CVS, Dennis' :memory: database test without outer BEGIN/COMMIT is now 4.3 times faster on my machine (58.1s before, 13.4s now). profile results with dirty pages fix: % cumulative self self total time seconds secondscalls ms/call ms/call name 9.20 0.31 0.31 100011 0.00 0.03 sqlite3VdbeExec 7.42 0.56 0.25 4849544 0.00 0.00 sqlite3VdbeRecordCompare 6.68 0.79 0.23 10487713 0.00 0.00 parseCellPtr 5.04 0.95 0.17 12943618 0.00 0.00 sqlite3VdbeSerialGet 5.04 1.12 0.17 46 0.00 0.00 sqlite3BtreeMoveto 4.45 1.27 0.15 6471807 0.00 0.00 sqlite3MemCompare 3.56 1.40 0.12 19270230 0.00 0.00 get2byte 3.56 1.51 0.12 12181181 0.00 0.00 findCell 3.26 1.62 0.11 3227291 0.00 0.00 binCollFunc 2.97 1.73 0.10 2064924 0.00 0.00 sqlite3pager_get 2.82 1.82 0.10 4500154 0.00 0.00 sqlite3pager_unref 2.67 1.91 0.0929816 0.00 0.02 balance_nonroot 2.37 1.99 0.08 2716112 0.00 0.00 _page_ref 2.23 2.06 0.07 5270826 0.00 0.00 findOverflowCell 2.08 2.13 0.07 2100045 0.00 0.00 sqlite3VdbeSerialType 2.08 2.21 0.07 1332302 0.00 0.00 initPage 2.08 2.27 0.0786067 0.00 0.00 assemblePage 1.78 2.33 0.06 34 0.00 0.01 sqlite3BtreeInsert 1.48 2.38 0.05 6735322 0.00 0.00 put2byte 1.48 2.44 0.05 3432951 0.00 0.00 pager_lookup results for same test with yesterday's CVS: % cumulative self self total time seconds secondscalls ms/call ms/call name 83.61 25.2025.20 12 0.25 0.25 pager_get_all_dirty_pages 1.87 25.77 0.56 100011 0.01 0.17 sqlite3VdbeExec 1.00 26.07 0.30 4849544 0.00 0.00 sqlite3VdbeRecordCompare 0.78 26.30 0.23 10487713 0.00 0.00 parseCellPtr 0.63 26.49 0.19 12943618 0.00 0.00 sqlite3VdbeSerialGet 0.60 26.67 0.18 46 0.00 0.00 sqlite3BtreeMoveto 0.50 26.82 0.15 3432951 0.00 0.00 pager_lookup 0.43 26.95 0.13 2064924 0.00 0.00 sqlite3pager_get 0.38 27.07 0.12 6471807 0.00 0.00 sqlite3MemCompare 0.36 27.18 0.11 19270230 0.00 0.00 get2byte 0.36 27.29 0.11 1368027 0.00 0.00 reparentPage 0.36 27.39 0.1129816 0.00 0.03 balance_nonroot 0.33 27.50 0.10 2716112 0.00 0.00 _page_ref 0.33 27.59 0.10 1858370 0.00 0.00 getAndInitPage 0.30 27.68 0.09 4849549 0.00 0.00 fetchPayload 0.28 27.77 0.09 6735322 0.00 0.00 put2byte 0.28 27.86 0.09 1964919 0.00 0.00 getPage 0.27 27.93 0.08 12181181 0.00 0.00 findCell 0.27 28.02 0.08 5270826 0.00 0.00 findOverflowCell 0.27 28.09 0.08 2722659 0.00 0.00 pageDestructor 0.27 28.18 0.08 1332302 0.00 0.00 initPage 0.27 28.25 0.08 359548 0.00 0.00 insertCell 0.27 28.34 0.08 34 0.00 0.01 sqlite3BtreeInsert 0.25 28.41 0.07 4500154 0.00 0.00 sqlite3pager_unref __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] SQLite :memory: performance difference between v2 and v3?
Dennis Cote <[EMAIL PROTECTED]> wrote: > > My results are summarized below. > Can you rerun your tests with the latest CVS check-in and let me know if it makes a difference? -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] SQLite :memory: performance difference between v2 and v3?
Ivan Voras wrote: When using a file the difference is much smaller, but still there: ~70 qps with sqlite3, ~90 qps with sqlite2. I also noticed that your values for this case are much higher than mine, which are 10 and 13 ips respectively. I realize that you are using a different OS (FreeBSD vs WinXP) but I was wondering if you had any special disk hardware that might account for some of this difference. Are you using fast SCSI disks or perhaps a RAID array? Dennis Cote
Re: [sqlite] SQLite :memory: performance difference between v2 and v3?
> Does it only happen with a :memory: database or even > if you use a > file? When using a file the difference is much smaller, but still there: ~70 qps with sqlite3, ~90 qps with sqlite2. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] SQLite :memory: performance difference between v2 and v3?
--- Erik Jensen <[EMAIL PROTECTED]> wrote: > Does it only happen with a :memory: database or even > if you use a > file? I'll test it this evening (but I need a :memory: database for my application) > With version of the v3 lib did you use? It's 3.3.5. > I'm asking because i noticed a similar performance > drop (factor 10-15) > when i wanted to upgrade my application from 3.2.1 > to 3.3.5 > > Regards, > Eric > > __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] SQLite :memory: performance difference between v2 and v3?
IV> I've spent a long time debugging low performance of an IV> application that uses :memory: database and have found IV> that sqlite v2 is much faster than v3. After some IV> digging around it seems that even two proof-of-concept IV> programs that are identical except for used SQLite IV> version reproduce this behaviour just fine: >> ./sqlite3_bench IV> 2564 qps >> ./sqlite_bench IV> 2 qps IV> (that's queries per second) IV> I've attached the source of these two programs, but in IV> case the attachments get stripped: all they do is open IV> a database, create a simple table with 3 fields, set IV> one of them as primary key and add a index on another IV> field, then insert 100,000 dummy records. IV> Approximately the same situation is for a test that IV> selects 100,000 records from this database one by one, IV> only sqlite3 is about 2x faster in this case (sqlite3: IV> 6000 qps, sqlite2: 2 qps). IV> Is this a "known behaviour"? Should I stick to using IV> sqlite2 for memory databases? Two questions: Does it only happen with a :memory: database or even if you use a file? With version of the v3 lib did you use? I'm asking because i noticed a similar performance drop (factor 10-15) when i wanted to upgrade my application from 3.2.1 to 3.3.5 Regards, Eric
RE: [sqlite] SQLite :memory: performance difference between v2 and v3?
> In any case, when doing any kind of benchmarking that involves disk access, > you must clear the OS disk cache so that the algorithm used by the OS is > removed from the equation (and also to be able to compare results), otherwise > all you're doing is benchmarking a moving target. In Linux, if you open up a file with O_DIRECT I believe the OS will not put it into the file system buffers (OS disk chace). This works exceptionally well for VERY VERY LARGE databases that take up more than the available RAM and you prefer to use your RAM for database page caches/query caches rather than OS filesystem buffers (and avoid the double-caching effect). I don't know if you can force a database to be opened with O_DIRECT though in sqlite. Your hard drive usually has a read-ahead cache too, so that also can interfere with true disk-bound benchmarking. Best regards, Richard Dale. Norgate Investor Services - Premium quality Stock, Futures and Foreign Exchange Data for markets in Australia, Asia, Canada, Europe, UK & USA - www.premiumdata.net
Re: [sqlite] SQLite :memory: performance difference between v2 and v3?
- Original Message From: Jay Sprenkle <[EMAIL PROTECTED]> On 5/1/06, Ivan Voras <[EMAIL PROTECTED]> wrote: > > I've spent a long time debugging low performance of an > > application that uses :memory: database and have found > > that sqlite v2 is much faster than v3. After some > > digging around it seems that even two proof-of-concept > > programs that are identical except for used SQLite > > version reproduce this behaviour just fine: > Testing is difficult to do correctly. As several people noted on this list > just > today the first time they ran a query it had much different > performance than subsequent > runs of the query. Did you run these tests more than one time? What's your > environment? The precaching trick desribed earlier can not be done on memory databases as they are already... in memory. The main reason first queries (sometimes it's more like the first few hundred queries if the db is big) are significantly slower are because of the way sqlite relies blindly on the OS caching mechanism for caching the indexes/primary keys in memory. In any case, when doing any kind of benchmarking that involves disk access, you must clear the OS disk cache so that the algorithm used by the OS is removed from the equation (and also to be able to compare results), otherwise all you're doing is benchmarking a moving target. To discard the disk caches: on linux: easy, just umount and mount the partition that contains the db file on windows: I don't know of any other way than clearing the whole cache with a tool such as Clearmem.exe (from the Windows Server 2003 Resource Kit, but you can find it on the web very easily) Hope this helps. Nicolas
