Proof is in the pudding...
Try this program: It's got two forks going. Does 5000 inserts and 5000
selects simultaneously. (it's not pretty but it works). It shows how many
BUSY times occur for both sections.
Run it without any args and it won't sleep at all.
There are four sleep values of 1,2,4,8 -- add them together to get different
sleep points in the code if you want or just use them individually. Putting
one usleep() anywhere works pretty well though it does slow down the select
noticeably.
1 = sleep when BUSY on SELECT
2 = sleep between SELECTs
4 = sleep when BUSY on INSERT
8 = sleep between INSERTs
You can see below that no sleeps, or #4 cause some busy hits. Not a lot of
time difference once you do any sleep.
It does 600 inserts per second when sleep is on without BEGIN/COMMIT which
makes it < 1.7ms per insert with the minimum sleep possible and over .1600
inserts per second with no sleeps at all.
This is on an 8-core Xeon 2.5Ghz
And all you need is that one sleep somewhere to drop the CPU usage to virtually
zero. Though you are running 2.5 times slower.
cc -Wall -g -o test1 test1.c sqlite3.o -lpthread -ldl
time ./test1
nbusy1=30921
nbusy2=709
0.417u 0.755s 0:02.92 39.7% 0+0k 0+0io 0pf+0w
time ./test1 1
nbusy1=0
nbusy2=0
0.000u 0.003s 0:07.44 0.0% 0+0k 0+0io 0pf+0w
time ./test1 2
nbusy1=4
nbusy2=0
0.000u 0.004s 0:07.78 0.0% 0+0k 0+0io 0pf+0w
time ./test1 4
nbusy1=259
nbusy2=160
0.000u 0.001s 0:08.03 0.0% 0+0k 0+0io 0pf+0w
time ./test1 8
nbusy1=1
nbusy2=0
0.000u 0.002s 0:07.88 0.0% 0+0k 0+0io 0pf+0w
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sys/types.h>
#include <sys/wait.h>
#include <unistd.h>
#include "sqlite3.h"
int
main (int argc, char *argv[])
{
sqlite3 *db = NULL;
int ret = -1;
pid_t pid;
int i, j;
int busy;
char *sql0 = "drop table data";
char *sql1 = "create table data(i integer)";
char *sql2 = "insert into data values(1)";
char *sql3 = "select count(*) from data";
char *errmsg;
if (argc > 1)
sscanf (argv[1], "%d", &busy);
ret = sqlite3_open ("test1.db", &db);
if (ret != SQLITE_OK) {
fprintf (stderr, "open error\n");
exit (-1);
}
sqlite3_exec (db, sql0, NULL, NULL, &errmsg);
if (ret != SQLITE_OK) {
fprintf (stderr, "exec error: %s\n", errmsg);
exit (-1);
}
sqlite3_exec (db, sql1, NULL, NULL, &errmsg);
if (ret != SQLITE_OK) {
fprintf (stderr, "exec error: %s\n", errmsg);
exit (-1);
}
sqlite3_exec (db, sql2, NULL, NULL, &errmsg);
if (ret != SQLITE_OK) {
fprintf (stderr, "exec error: %s\n", errmsg);
exit (-1);
}
sqlite3_close (db);
pid = fork ();
if (pid == 0) { // child process
int nbusy = 0;
sqlite3 *db = NULL;
sqlite3_stmt *p_stmt = NULL;
ret = sqlite3_open ("test1.db", &db);
if (ret != SQLITE_OK) {
fprintf (stderr, "open error\n");
exit (-1);
}
for (i = 1; i <= 5000; i++) {
if ((i % 100) == 0) {
printf ("%5d\r", i);
fflush (stdout);
}
ret = sqlite3_prepare_v2 (db, sql3, -1, &p_stmt, NULL);
if (ret != SQLITE_OK) {
fprintf (stderr, "prepare error: %s\n", sqlite3_errmsg (db));
}
do {
ret = sqlite3_step (p_stmt);
if (ret == SQLITE_BUSY) {
if (busy && 0x01)
usleep (1);
nbusy++;
continue;
}
if (ret != SQLITE_DONE && ret != SQLITE_ROW) {
fprintf (stderr, "\nstep1 er...@%d: %d=%s\n", j, ret,
sqlite3_errmsg (db));
usleep (100000);
}
} while (ret != SQLITE_DONE);
sqlite3_finalize (p_stmt);
if (busy && 0x02)
usleep (1);
}
fprintf (stderr, "nbusy1=%d \n", nbusy);
exit (0);
}
else {
int nbusy=0;
sqlite3 *db = NULL;
sqlite3_stmt *p_stmt = NULL;
ret = sqlite3_open ("test1.db", &db);
if (ret != SQLITE_OK) {
fprintf (stderr, "open error\n");
exit (-1);
}
for (j = 1; j <= 5000; j++) {
if ((j % 100) == 0) {
printf ("\t%5d\r", j);
fflush (stdout);
}
ret = sqlite3_prepare_v2 (db, sql2, -1, &p_stmt, NULL);
if (ret != SQLITE_OK) {
fprintf (stderr, "prepare error: %s\n", sqlite3_errmsg (db));
}
do {
ret = sqlite3_step (p_stmt);
if (ret == SQLITE_BUSY) {
nbusy++;
if (busy && 0x04)
usleep (1);
continue;
}
if (ret != SQLITE_DONE) {
fprintf (stderr, "step2 er...@%d: %d, %s\n", j, ret,
sqlite3_errmsg (db));
usleep (1);
}
} while (ret != SQLITE_DONE);
if (busy && 0x08)
usleep (1);
}
fprintf (stderr, "nbusy2=%d \n", nbusy);
waitpid (pid, 0, 0);
}
sqlite3_close (db);
return 0;
}
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
________________________________
From: [email protected] on behalf of Jay A. Kreibich
Sent: Tue 5/18/2010 2:12 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] read only databases and in-memory databases
On Tue, May 18, 2010 at 12:50:20PM -0500, Black, Michael (IS) scratched on the
wall:
> Rats on the interprocess locks.
More to the point, database connection mutexes are just for the
database connection. Even the same process with more than one
database connection might fight with each other, and the mutex locks
are going to do nothing to prevent that. All concurrency locking is
currently done through the file locks, so that's what you have to
work with.
> I was still talking about sleeping for spinning the BUSY return --
Sleeping and spinning are two fundamentally different ideas. You
have to pick one, and you only get to choose on an MP/MC machine.
> but just doing it as fast as possible without using much CPU
Fast burns CPU. If you spin, you never let go of the CPU. If you
sleep, you usually stay asleep. Most OSes won't really give you
extremely short sleep times and, even if they could, the wake-up
and context switch is an expensive thing, especially if you just hit
the lock again and go back to sleep.
> instead of picking 10ms which seeem too long to me when the
> transactions should take sub-millisecond times.
Only if you're not sync'ing the disks. On most systems, the simplest
write transaction is going to take at least 15ms.
http://sqlite.org/faq.html#q19
-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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
