I'm running on Linux with ext3 and just wrote a Python test program to
insert rows into a table with a single column, no indexing, and doing
a commit after each insert. When I first ran it, I got around 440
inserts per second, which is clearly impossible. This is a 7200rpm
drive, so even if I could write a row on every revolution, the maximum
insert rate would be 120 per second. I tried adding "pragma
sychronous=normal", and it sped up to 520 TPS. With synchronous=full
it slowed back to 420-460, so this must be the Python default. Adding
synchronous=off increased the rate to over 6000 TPS -- basically
writing to memory instead of disk.
After using hdparm to turn off the drive cache (hdparm -W 0 /dev/sda),
the insert rate was 15 rows per second. So it seems that for my
particular hardware configuration, the Linux fsync call isn't doing
what it should be doing. I have built the SQLite stuff from source,
so perhaps my build has a problem. If you look on the Linux kernel
mailing list archives, there are several discussions about drive write
caching not playing nice with ext3's efforts to ensure data is
actually on the disk.
$ uname -a
Linux amd 2.6.25-ARCH #1 SMP PREEMPT Sat Jun 14 18:07:19 CEST 2008
i686 AMD Athlon(tm) 64 Processor 3200+ AuthenticAMD GNU/Linux
Here is a test program you can run, to show your system's maximum
physical I/O rate:
#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);
}
fsync(fd);
}
elap = time(NULL)-start;
printf("Time: %d seconds; TPS=%f\n", elap, MAX*1.0/elap);
}
On my system, I get these results:
[...@amd ~]$ cc -o sync sync.c
[...@amd ~]$ ./sync
Time: 25 seconds; TPS=120.000000
[...@amd ~]$
Running vmstat concurrently, we see this:
[...@amd]$ vmstat 5
procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
0 0 0 791108 127632 76356 0 0 0 9 10 33 1 1 98 1
0 1 0 791100 127648 76356 0 0 0 439 102 212 0 1 13 86
0 1 0 791100 127664 76356 0 0 0 478 119 242 0 1 0 99
0 1 0 790976 127672 76356 0 0 0 478 119 242 0 1 0 99
0 1 0 790976 127688 76356 0 0 0 481 120 244 0
1 0 99 <-- steady state
0 1 0 790976 127696 76356 0 0 0 482 120 244 0 1 0 99
0 0 0 790984 127700 76356 0 0 0 167 40 75 0 0 71 29
0 0 0 790984 127712 76356 0 0 0 3 1 6 0 0 100 0
During the steady state, there are 480KBytes written per second.
Linux does I/O in 4K chunks, so dividing 480K by 4K gives you I/O's
per second: 120.
Jim
On 5/27/09, Marcus Grimm <mgr...@medcom-online.de> wrote:
> Thanks Nick,
>
> good point. ahh yes, I've read about this somewhere...
>
> My extension is currently ".db", a quick check indicates
> that using ".abc" gives a slight speed improvement, maybe 10%.
> But that is allready very close to the variation I get between
> different test runs, so I'm not really sure if I have this
> "microsoft effect" here.
>
> Anyway, thanks for the reminder.
>
> Well, I think now there is nothing wrong here,
> it is just as it is... Currently I achieve about 10 transactions/second,
> maybe
> not that bad... still slower than the "few dozen" that are mentioned on the
> www pages, but I agree that there too many issues that may affect the disk
> speed.
>
>
> Marcus
>
>
> Brandon, Nicholas (UK) wrote:
>>> my developemnt system is a Win XP, with of course NTFS, I
>>> don't know which drive it has, I guess a standard 7200 rpm.
>>>
>>
>> What file extension (i.e. the letters after the dot in the filename) do
>> you give the database?
>>
>> I faintly recall there is a windows peculiarity with system restore or
>> something similar that archives certain file extensions in the
>> background. That may contribute to your slow down.
>>
>> Nick
>>
>> ********************************************************************
>> This email and any attachments are confidential to the intended
>> recipient and may also be privileged. If you are not the intended
>> recipient please delete it from your system and notify the sender.
>> You should not copy it or use it for any purpose nor disclose or
>> distribute its contents to any other person.
>> ********************************************************************
>>
>> _______________________________________________
>> 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!
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
