What happens if you wrap the SELECT statements in a transaction in the
same way as you have the INSERTs?
--- William Hachfeld <[EMAIL PROTECTED]> wrote:
>
> 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);
> }
>
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
