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);
}
