Here's an update for the commit rate tester for Mac OSX. With the
original version, using fsync(), OSX doesn't do real syncs because the
fsync call isn't properly implemented (my opinion). Apple took a
shortcut with fsync and only flushes OS buffers to the drive, but the
drive is then free to reorder the requests (that's okay) and execute
them at a later time (that's not okay). You have to use
fdcntl(F_FULLSYNC) to get data actually written to the platters. At
least Apple does document this behavior. Here's the updated program:
#include <stdio.h>
#include <fcntl.h>
#include <unistd.h>
#include <stdlib.h>
#include <time.h>
#define MAX 3000
main () {
int fd;
int n;
int loops;
time_t elap;
time_t start;
if ((fd=open("big", O_RDWR+O_CREAT, 0777)) == -1) {
perror("Error opening file");
exit(1);
}
start = time(NULL);
for(loops=0; loops<MAX; loops++) {
if (lseek(fd, 0, SEEK_SET) == -1) {
perror("Error seeking file");
exit(1);
}
n = write(fd, &n, 1);
if (n != 1) {
perror("Error writing file");
exit(1);
}
#ifdef __APPLE__
fcntl(fd, F_FULLFSYNC);
#else
fsync(fd);
#endif
}
elap = time(NULL)-start;
printf("Time: %d seconds; TPS=%f\n", elap, MAX*1.0/elap);
}
With F_FULLSYNC, my G5 server and G4 laptop running 10.4 get these results:
g5:~ mac$ ./sync
Time: 174 seconds; TPS=17.241379
$ ./sync
Time: 265 seconds; TPS=11.320755
The G5 is running a software RAID driver, so I thought that might be
contributing to the awful performance, but the G4 laptop w/o RAID is
even worse - I'm assuming because the drive rotational speed is
probably 4200RPM instead of 7200RPM.
These results are pretty horrible. The SQLite commit documentation
says that Apple is doing a hard drive reset to implement the cache
flush, which is why the performance is so bad. Maybe they have
improved things with Leopard (10.5).
Since SQLite does 3-4 syncs per commit, I was curious what the commit
performance would be like in this environment. Here are the results
with a Python test program and SQLite 3.6.14.2:
import sqlite3
import time
def runtest(path, sync):
con = sqlite3.connect(path)
con.execute('create table if not exists t (f)')
con.execute('pragma synchronous=' + sync)
con.execute('pragma fullsync=ON')
con.execute('delete from t')
con.commit()
start = time.time()
end = start+60
i = 0
n = 3000
while i < n and time.time() < end:
con.execute("insert into t (f) values (?)", (i,))
con.commit()
i += 1
elap = time.time() - start
print "For pragma synchronous=", sync, "Time:", elap, "TPS:", n/elap
con.close()
path = "table"
runtest(path, "off")
runtest(path, "normal")
runtest(path, "full")
g5:~ mac$ Python-2.6.1/python.exe dbsync.py
/Users/mac/Python-2.6.1/Lib/sqlite3/dbapi2.py:27: RuntimeWarning:
Python C API version mismatch for module _sqlite3: This Python has API
version 1013, module _sqlite3 has version 1012.
from _sqlite3 import *
For pragma synchronous= off Time: 1.97417807579 TPS: 1519.61975305
For pragma synchronous= normal Time: 4.06918787956 TPS: 737.247846202
For pragma synchronous= full Time: 4.78447008133 TPS: 627.028688445
Obviously, there's no way a 7200RPM drive can actually achieve these
results. I'm building my own version of Python and sqlite here, and
after some research, I think I didn't build sqlite with the
-DHAVE_FULLFSYNC flag, so it isn't actually doing the fdcntl() call.
Jim
On 5/29/09, Jim Wilcoxson <pri...@gmail.com> wrote:
> I agree that adding this to the library, and making it accessible via
> a pragma command would be very useful. For example, pragma commitrate
> 1000 would test the commit rate of 1000 commits and return the results
> in transactions per second as a row.
>
> If I install my app on a client's machine, I could run this test
> periodically to ensure that the system environment is going to support
> "no corruption" operation, and/or send some kind of warning to my
> customer that there is a risk of corruption because their system
> environment has problems.
>
> If it were only an external program bundled with sqlite, I couldn't
> really make use of it, because I'd have to distribute the program and
> instructions how to use it, and rely on customers to actually do it.
>
> This is a pretty small function. Just for my own use, I'd consider
> foreign key support to be way more bloated that this.
>
> Jim
>
> On 5/29/09, Marcus Grimm <mgr...@medcom-online.de> wrote:
>>> On Thu, May 28, 2009 at 03:12:55PM -0700, Allen Fowler scratched on the
>>> wall:
>>>>
>>>> > just for anybody who is interested:
>>>>
>>>> >
>>>> > I translated Jim's function into window code and added
>>>> > a page of 1024 that will be written, instead of a single byte.
>>>> > On my Win-XP system I got 55 TPS, much faster than sqlite
>>>> > seems to write a page but that might be related to the
>>>> > additional overhead sqlite needs to do.
>>
>> just to add: I traced a little what sqlite does when an
>> simple UPDATE is done within a transaction: It does
>> two syncs on the journal file and one final sync on the
>> db itselve, so achieving something like 15 TPS is reasonable.
>>
>>
>>>> >
>>>> > This brings me to a nice to have feature request:
>>>> > How about adding similar test function in the sqlite API ?
>>>> > This might use the vfs to write pages and gives some feedback
>>>> > on the performance of the system where sqlite runs on.
>>>> > It might also detect problems with the commit function...
>>>> > Just an idea...
>>>> >
>>>>
>>>> Interesting idea.
>>>
>>> It would make a lot more sense to make this an external utility
>>> or an extension of the sqlite3 shell. Adding it to the core library
>>> is a definite case of code bloat.
>>
>> Adding it into the API would allow my application to
>> easily make the test for example the first time it runs
>> on a system. But maybe a problem for the users that
>> apply a sqlite wrapper.
>> However, having it in sqlite3 shell would be very useful as
>> well.
>>
>> Marcus
>>
>>>
>>> Actually, a whole suite of performance related tests might be
>>> interesting.
>>>
>>> -j
>>>
>>> --
>>> Jay A. Kreibich < J A Y @ K R E I B I.C H >
>>>
>>> "Our opponent is an alien starship packed with atomic bombs. We have
>>> a protractor." "I'll go home and see if I can scrounge up a ruler
>>> and a piece of string." --from Anathem by Neal Stephenson
>>> _______________________________________________
>>> 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
>>
>
>
> --
> Software first. Software lasts!
>
--
Software first. Software lasts!
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
