[sqlite] NFS Query Performance

[William Hachfeld]

Heh everyone!

Can anyone explain to me why SQLite shows such poor query performance when the
database is located on an NFS-mounted file system? When running with a single
process accessing the database, I'm finding that my insertion rates are similar
to local disk, but my query performance is much, much, slower.

To illustrate, I built my test code (source at the end of this post) as:

g++ -o sqlite-test sqlite-test.cxx -lsqlite3 -lrt

using GCC 3.3.3. My Dell test system had a 3.2Ghz P4, 1Gb of RAM, and a 80 ATA
HD (2Mb cache). I'm running SuSE 9.1 on this system. I ran each test three
times:


/tmp 36749.3, 36662.5, 36239.8 Insertions/Second
/tmp 264.195, 268.678, 266.233 Queries/Second

~ 36515.5, 36538.9, 36673.6 Insertions/Second
~ 274.449, 273.875, 273.236 Queries/Second

NFS(1) 37128.2, 37479, 37347.9 Insertions/Second
NFS(1) 44.2276, 45.408, 52.3626 Queries/Second

NFS(2) 36812.2, 37272.3, 36595.2 Insertions/Second
NFS(2) 68.3878, 68.3142, 68.0701 Queries/Second


/tmp: Local "tmpfs" file system
~: Local "reiserfs" file system
NFS(1): Served from Origin 2000 16p system, RAID-5 SCSI disk array, fairly
heavy user load
NFS(2): Served from lowly PIII Linux system, local ATA disk, no user load


Note that insertion performance is basically the same across all
configurations, but the query performance is seriously degraded when running on
NFS. In practice, on our real application, I'm seeing as much as a 50x speedup
when moving the database from NFS onto the local disk. Colleagues working on
the same project have all seen similar results.

Does anyone have an idea why this is the case? Is there anything I can do to
improve the query performance on NFS-mounted databases? Since we are using
SQLite for storing our application's "save files", our users are going to want
to be able to locate the database in a directory of their choosing.

Also note that I'm not complaining about SQLite's query performance in general.
The absolute numbers in this test case are artifically low because I'm forcing
a full table scan by querying on a non-indexed field. My concern is with the
relative performance between local versus NFS file systems.

Thanks guys!

-- William Hachfeld





#include <assert.h>
#include <inttypes.h>
#include <iostream>
#include <sqlite3.h>
#include <stdio.h>
#include <stdlib.h>
#include <string>
#include <time.h>
#include <unistd.h>

uint64_t Now()
{
struct timespec now;
assert(clock_gettime(CLOCK_REALTIME, &now) == 0);
return (static_cast<uint64_t>(now.tv_sec) * 1000000000) +
static_cast<uint64_t>(now.tv_nsec);
}

int main(int argc, char* argv[])
{
const int N = 10000;

// Form and display the name of the database
std::string dbname = std::string(get_current_dir_name()) + "/tmp.db";
std::cout << "Database \"" << dbname << "\"" << std::endl;

// Open the database
sqlite3* handle = NULL;
assert(sqlite3_open(dbname.c_str(), &handle) == SQLITE_OK);
assert(handle != NULL);

// Create and populate a table with 'N' entries

assert(sqlite3_exec(handle, "BEGIN TRANSACTION;",
NULL, NULL, NULL) == SQLITE_OK);

assert(sqlite3_exec(handle,
"CREATE TABLE Test ("
" key INTEGER PRIMARY KEY,"
" value INTEGER"
");",
NULL, NULL, NULL) == SQLITE_OK);

uint64_t t_start = Now();
for(int i = 0; i < N; ++i) {
char* statement =
sqlite3_mprintf("INSERT INTO Test (value) VALUES (%ld);", i);
assert(statement != NULL);
assert(sqlite3_exec(handle, statement, NULL, NULL, NULL) == SQLITE_OK);
sqlite3_free(statement);
}
uint64_t t_stop = Now();

assert(sqlite3_exec(handle, "COMMIT TRANSACTION;",
NULL, NULL, NULL) == SQLITE_OK);

std::cout << (static_cast<double>(N) /
(static_cast<double>(t_stop - t_start) / 1000000000.0))
<< " Insertions/Second" << std::endl;

// Perform 'N' queries on the table

t_start = Now();
for(int i = 0; i < N; ++i) {
char* statement =
sqlite3_mprintf("SELECT * FROM Test WHERE value = %ld;", i);
assert(statement != NULL);
assert(sqlite3_exec(handle, statement, NULL, NULL, NULL) == SQLITE_OK);
sqlite3_free(statement);
}
t_stop = Now();

std::cout << (static_cast<double>(N) /
(static_cast<double>(t_stop - t_start) / 1000000000.0))
<< " Queries/Second" << std::endl << std::endl;

// Close the database
assert(sqlite3_close(handle) == SQLITE_OK);

// Remove the database
assert(remove(dbname.c_str()) == 0);
}

[Jay Sprenkle]

On a system we developed several years ago (non sqlite) we noted that obtaining
locks on an NFS mounted file was VERY slow. I *think* it's trying to
establish a lock
then waiting at least the propagation delay of your network to ensure
there was no
collision with another process.

Doing anything over a network is not good for performance.

Post by William Hachfeld
Heh everyone!
Can anyone explain to me why SQLite shows such poor query performance when the
database is located on an NFS-mounted file system? When running with a single
process accessing the database, I'm finding that my insertion rates are similar
to local disk, but my query performance is much, much, slower.

[William Hachfeld]

Post by Jay Sprenkle
On a system we developed several years ago (non sqlite) we noted that
obtaining locks on an NFS mounted file was VERY slow. I *think* it's trying
to establish a lock then waiting at least the propagation delay of your
network to ensure there was no collision with another process.
Doing anything over a network is not good for performance.

And because I used a transaction on the insert, the lock only has to be
obtained once, and thus does not significantly affect performance?

[Jay Sprenkle]

That sounds right. I might not understand what you're doing so it's
just a SWAG ;)

Post by William Hachfeld

Post by Jay Sprenkle
On a system we developed several years ago (non sqlite) we noted that
obtaining locks on an NFS mounted file was VERY slow. I *think* it's trying
to establish a lock then waiting at least the propagation delay of your
network to ensure there was no collision with another process.
Doing anything over a network is not good for performance.

And because I used a transaction on the insert, the lock only has to be
obtained once, and thus does not significantly affect performance?

[Kurt Welgehausen]

SQLite is not a client-server database. In the worst case,
when you query a database file over NFS, all the tables in
your query are sent over the network to be processed on
your cpu; so if you want to select one row from a table of
a million rows, the million rows are retrieved over the
network (in the worst case).

You may want to consider a client-server database for this
reason and also because there have been reports that
locking does not work reliably on some implementations of
NFS -- so you could be risking a corrupted db file if you
have multiple users.

I think this has been discussed on the list. You could
try a search of the archive:

<http://www.mail-archive.com/sqlite-users-CzDROfG0BjIdnm+***@public.gmane.org/>


Regards

[Jay Sprenkle]

Post by Kurt Welgehausen
SQLite is not a client-server database. In the worst case,
when you query a database file over NFS, all the tables in
your query are sent over the network to be processed on
your cpu; so if you want to select one row from a table of
a million rows, the million rows are retrieved over the
network (in the worst case).

Didn't even think about that aspect of it. Thanks :)

[William Hachfeld]

Post by Kurt Welgehausen
SQLite is not a client-server database.

Yup. I realize that.

Post by Kurt Welgehausen
In the worst case, when you query a database file over NFS, all the tables in
your query are sent over the network to be processed on your cpu; so if you
want to select one row from a table of a million rows, the million rows are
retrieved over the network (in the worst case).

Which is where presumably SQLite's page caching would come in... At least for
subsequent queries on the same table.

Post by Kurt Welgehausen
You may want to consider a client-server database for this reason and also
because there have been reports that locking does not work reliably on some
implementations of NFS -- so you could be risking a corrupted db file if you
have multiple users.

We definitely do NOT want a client-server database. As I noted, SQLite is being
used to store, in effect, an application's "save files". The fact that SQLite
stores its databases as a single, user movable, file is one of the key features
that makes it of interest to us.

Multiple users will also be a rarity (if it EVER occurs). What SQLite is giving
us is the ability to mainpulate a large (gigabyte sized) file containing
complex structures, without having to do all the "dirty work" (indexing,
endian-ness translation, caching, etc.) ourselves.

Post by Kurt Welgehausen
I think this has been discussed on the list. You could try a search of the

I did. Almost all of the posts seemed to relate to the correctness of the NFS
locking and how it could be improved. I really don't care so much about correct
locking for our application. I'm more worried about the performance.

-- William Hachfeld

[Griggs, Donald]

I'm not sure how your network is set up, but if you're using 100mbps
ethernet, the full- table-scan queries are only 4-5 times slower over the
net. Wouldn't that be the same order of magnitude as the ratio of link
speed to ATA local disk bus speed?

As for the inserts, if you're achieving over 36,000 of these per second, are
you perhaps in a CPU-bound state rather than a disk-bound one?

Given that there's no sqlite server software on the network host, scanning
full tables 50 or 60 times a second sounds awfully fast to me --- though I'm
an admitted newcomer to databases and may have some faulty logic here.

I wonder if placing sqlite on the host, and adding something like SQRELAY
would be worth the time to setup? If your queries are much faster, you
might consider keeping it, or using a more conventional database like
postgres or mysql.

Donald Griggs

Opinions are not necessarily those of Misys Healthcare Systems nor its board
of directors.

[William Hachfeld]

Post by Griggs, Donald
I'm not sure how your network is set up, but if you're using 100mbps
ethernet, the full- table-scan queries are only 4-5 times slower over the
net. Wouldn't that be the same order of magnitude as the ratio of link
speed to ATA local disk bus speed?

Possibly. Although as I noted, our "real" application is showing a speed
differential of about 50x. Much more than can be attributed to simple transfer
rates. But it sounds like the answer is the crappy NFS locking speed.

Post by Griggs, Donald
As for the inserts, if you're achieving over 36,000 of these per second, are
you perhaps in a CPU-bound state rather than a disk-bound one?

I think, as I indicated to Jay earlier, that the reason the insert speed
doesn't degrade is that I'm performing a transaction around all the inserts.
Thus I expect that I only pay the NFS locking price once rather than multiple
times when I'm doing the query.

Post by Griggs, Donald
Given that there's no sqlite server software on the network host, scanning
full tables 50 or 60 times a second sounds awfully fast to me --- though I'm
an admitted newcomer to databases and may have some faulty logic here.
I wonder if placing sqlite on the host, and adding something like SQRELAY
would be worth the time to setup? If your queries are much faster, you
might consider keeping it, or using a more conventional database like
postgres or mysql.

Nope. that isn't going to be an option for me. Preserving the "zero
configuration" and "single file" environment is of utmost importance in our
application.

Sounds like I'm pretty much stuck.

-- William Hachfeld

[Jay Sprenkle]

Any chance you can write a really simple shell script/C/Perl program to do
a bunch of lock/unlocks and benchmark it?

Post by William Hachfeld
Possibly. Although as I noted, our "real" application is showing a speed
differential of about 50x. Much more than can be attributed to simple transfer
rates. But it sounds like the answer is the crappy NFS locking speed.

[Ted Unangst]

Post by William Hachfeld
Nope. that isn't going to be an option for me. Preserving the "zero
configuration" and "single file" environment is of utmost importance in our
application.

If you don't need locking, and it sounds like you don't, just neuter the
fcntl calls in os_unix.c. In fact, there's a #define and comment for
DJGPP that does exactly what you want.

[William Hachfeld]

Post by Ted Unangst
If you don't need locking, and it sounds like you don't, just neuter the
fcntl calls in os_unix.c. In fact, there's a #define and comment for DJGPP
that does exactly what you want.

Hmmm. Thanks for the suggestion, Ted. I think I'll try this out. If nothing
else, this will help confirm whether the majority of the slowdown is due to NFS
locking performance.

-- William Hachfeld

[Cariotoglou Mike]

Just a thought. If transactions speed up the access, try this:

Begin exclusive
Select ....
Select ...
Select ...
Commit

Ie use a transaction around READS.
This may acquire the lock once, and give you the same performance as
inserts.

-----Original Message-----
Sent: Tuesday, April 19, 2005 10:18 PM
Subject: Re: [sqlite] NFS Query Performance

Post by Ted Unangst
If you don't need locking, and it sounds like you don't,

just neuter

Post by Ted Unangst
the fcntl calls in os_unix.c. In fact, there's a #define

and comment

Post by Ted Unangst
for DJGPP that does exactly what you want.

Hmmm. Thanks for the suggestion, Ted. I think I'll try this
out. If nothing else, this will help confirm whether the
majority of the slowdown is due to NFS locking performance.
-- William Hachfeld

[Dan Kennedy]

What happens if you wrap the SELECT statements in a transaction in the
same way as you have the INSERTs?

Post by William Hachfeld
Heh everyone!
Can anyone explain to me why SQLite shows such poor query performance when the
database is located on an NFS-mounted file system? When running with a single
process accessing the database, I'm finding that my insertion rates are similar
to local disk, but my query performance is much, much, slower.
g++ -o sqlite-test sqlite-test.cxx -lsqlite3 -lrt
using GCC 3.3.3. My Dell test system had a 3.2Ghz P4, 1Gb of RAM, and a 80 ATA
HD (2Mb cache). I'm running SuSE 9.1 on this system. I ran each test three
/tmp 36749.3, 36662.5, 36239.8 Insertions/Second
/tmp 264.195, 268.678, 266.233 Queries/Second
~ 36515.5, 36538.9, 36673.6 Insertions/Second
~ 274.449, 273.875, 273.236 Queries/Second
NFS(1) 37128.2, 37479, 37347.9 Insertions/Second
NFS(1) 44.2276, 45.408, 52.3626 Queries/Second
NFS(2) 36812.2, 37272.3, 36595.2 Insertions/Second
NFS(2) 68.3878, 68.3142, 68.0701 Queries/Second
/tmp: Local "tmpfs" file system
~: Local "reiserfs" file system
NFS(1): Served from Origin 2000 16p system, RAID-5 SCSI disk array, fairly
heavy user load
NFS(2): Served from lowly PIII Linux system, local ATA disk, no user load
Note that insertion performance is basically the same across all
configurations, but the query performance is seriously degraded when running on
NFS. In practice, on our real application, I'm seeing as much as a 50x speedup
when moving the database from NFS onto the local disk. Colleagues working on
the same project have all seen similar results.
Does anyone have an idea why this is the case? Is there anything I can do to
improve the query performance on NFS-mounted databases? Since we are using
SQLite for storing our application's "save files", our users are going to want
to be able to locate the database in a directory of their choosing.
Also note that I'm not complaining about SQLite's query performance in general.
The absolute numbers in this test case are artifically low because I'm forcing
a full table scan by querying on a non-indexed field. My concern is with the
relative performance between local versus NFS file systems.
Thanks guys!
-- William Hachfeld
#include <assert.h>
#include <inttypes.h>
#include <iostream>
#include <sqlite3.h>
#include <stdio.h>
#include <stdlib.h>
#include <string>
#include <time.h>
#include <unistd.h>
uint64_t Now()
{
struct timespec now;
assert(clock_gettime(CLOCK_REALTIME, &now) == 0);
return (static_cast<uint64_t>(now.tv_sec) * 1000000000) +
static_cast<uint64_t>(now.tv_nsec);
}
int main(int argc, char* argv[])
{
const int N = 10000;
// Form and display the name of the database
std::string dbname = std::string(get_current_dir_name()) + "/tmp.db";
std::cout << "Database \"" << dbname << "\"" << std::endl;
// Open the database
sqlite3* handle = NULL;
assert(sqlite3_open(dbname.c_str(), &handle) == SQLITE_OK);
assert(handle != NULL);
// Create and populate a table with 'N' entries
assert(sqlite3_exec(handle, "BEGIN TRANSACTION;",
NULL, NULL, NULL) == SQLITE_OK);
assert(sqlite3_exec(handle,
"CREATE TABLE Test ("
" key INTEGER PRIMARY KEY,"
" value INTEGER"
");",
NULL, NULL, NULL) == SQLITE_OK);
uint64_t t_start = Now();
for(int i = 0; i < N; ++i) {
char* statement =
sqlite3_mprintf("INSERT INTO Test (value) VALUES (%ld);", i);
assert(statement != NULL);
assert(sqlite3_exec(handle, statement, NULL, NULL, NULL) == SQLITE_OK);
sqlite3_free(statement);
}
uint64_t t_stop = Now();
assert(sqlite3_exec(handle, "COMMIT TRANSACTION;",
NULL, NULL, NULL) == SQLITE_OK);
std::cout << (static_cast<double>(N) /
(static_cast<double>(t_stop - t_start) / 1000000000.0))
<< " Insertions/Second" << std::endl;
// Perform 'N' queries on the table
t_start = Now();
for(int i = 0; i < N; ++i) {
char* statement =
sqlite3_mprintf("SELECT * FROM Test WHERE value = %ld;", i);
assert(statement != NULL);
assert(sqlite3_exec(handle, statement, NULL, NULL, NULL) == SQLITE_OK);
sqlite3_free(statement);
}
t_stop = Now();
std::cout << (static_cast<double>(N) /
(static_cast<double>(t_stop - t_start) / 1000000000.0))
<< " Queries/Second" << std::endl << std::endl;
// Close the database
assert(sqlite3_close(handle) == SQLITE_OK);
// Remove the database
assert(remove(dbname.c_str()) == 0);
}

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

[Klint Gore]

Post by William Hachfeld
uint64_t t_stop = Now();
assert(sqlite3_exec(handle, "COMMIT TRANSACTION;",
NULL, NULL, NULL) == SQLITE_OK);

What happens to your insert times if you swap the above 2 lines? If you
stop timing before you commit, I would have thought that the journal
file writes have been timed instead of the database. If it's a new
database then it's probably not going to make any difference, but if
it's an existing larger database then it might.

klint.

+---------------------------------------+-----------------+
: Klint Gore : "Non rhyming :
: EMail : kg-***@public.gmane.org : slang - the :
: Snail : A.B.R.I. : possibilities :
: Mail University of New England : are useless" :
: Armidale NSW 2351 Australia : L.J.J. :
: Fax : [phone] : :
+---------------------------------------+-----------------+

[William Hachfeld]

Good suggestions, guys!

First, I modified the previously-posted test program to time the insertion rate
outside of the transaction (i.e. Kilnt's suggestion). I left the creation of
the table, however, outside of the timed insertion loop. In other words:

Create Table
Begin Timing
Begin Transaction
Loop Performing 10,000 Inserts
Commit Transaction
End Timing

Second, I took Mike & Dan's suggestion and put a transaction around the queries
as well. In other words:

Begin Timing
Begin Transaction
Loop Performing 10,000 Queries
Commit Transaction
End Timing

When I run this modified version on the same four file systems as before, I
get:

/tmp 27198.7, 29177.2, 25465.3 Insertions/Second
/tmp 301.234, 300.551, 304.311 Queries/Second

~ 26596.9, 27185.5, 26012 Insertions/Second
~ 306, 303.746, 302.499 Queries/Second

NFS(1) 33789.5, 33675.7, 30738.6 Insertions/Second
NFS(1) 304.678, 305.742, 303.51 Queries/Second

NFS(2) 30446.6, 30528.9, 29344.3 Insertions/Second
NFS(2) 300.903, 304.742, 303.782 Queries/Second

These results are pretty much directly in line with what one would expect given
the hypothesis that poor NFS locking performance was causing the slowdown. Key
points:

1) Timing outside the transaction for inserts did, as expected, lower the
amortized insertion rate in each case.

2) Adding a transaction around the queries did, as expected, increase the
amortized query rate in every case.

3) Adding a transaction around the queries, thus locking only once, as
expected, brought the query rate on NFS up to the query rate on local disk
(assuming the single, poor, NFS lock is amortized across a sufficient number
of queries).

So my conclusion from all of this is that I need to do one of two things. I
either need to group my queries together and bracket them with a transaction,
or I need to try the suggestion yesterday of disabling the locking.


-- William Hachfeld

[Christian Smith]

Post by Klint Gore

Post by William Hachfeld
uint64_t t_stop = Now();
assert(sqlite3_exec(handle, "COMMIT TRANSACTION;",
NULL, NULL, NULL) == SQLITE_OK);

What happens to your insert times if you swap the above 2 lines? If you
stop timing before you commit, I would have thought that the journal
file writes have been timed instead of the database. If it's a new
database then it's probably not going to make any difference, but if
it's an existing larger database then it might.

I know the snippet above is from a test program, but don't ever put things
like sqlite3_exec() inside an assert()! I hope you're (OP) not doing this
in your application.

Christian

[William Hachfeld]

Post by Christian Smith
I know the snippet above is from a test program, but don't ever put things
like sqlite3_exec() inside an assert()! I hope you're (OP) not doing this in
your application.

I realize that evaluation of assert() is short-circuited when NDEBUG is defined
during the compile (i.e typically when optimizations are used). Using assert()
in a test program like this is a very quick, very effective, way to insure that
each of your OS calls is completing successfully. Much better (IMHO) than
naively assuming that everything succeeds and then being dumbfounded later when
things break in mysterious ways.

-- William Hachfeld