On Tue, Dec 4, 2012 at 2:30 PM, Keith Chew <keith.c...@gmail.com> wrote:
> Hi
>
> I have an existing application, which I am experimenting with
> replacing MySQL with sqlite. I have 2 boxes, one running MySQL, the
> other sqlite.
>
> Execution times for every insert/update/delete SQLs is measured, and a
> warn log is printed if it takes > 250ms to perform the SQL.
>
> OS configuration:
> - kernel 2.6.39
> - ext3, data=ordered, commit=1, barrier=1
>
> On the MySQL box, I do not see any warn logs, so everything is going
> well. On the sqlite box, I see warn logs come up quite often, ranging
> from 350ms up to 4000ms to run simple insert/update/delete SQL.
>
> I have tried many sqlite configurations including:
> - WAL, synchronous=0, wal_autocheckpoint=0
>
> which should make sqlite run the fastest!
>
> My question is how do I go about tracing the cause of these latencies?
>
Here is one approach:
(1) Get the latest SQLite sources from the Fossil repository
(2) Do "./configure; make sqlite3.c" to build the amalgamation
(3) Do "cat src/test_sqllog.c >>sqlite3.c" to append the "test_sqllog.c"
file to the end of the amalgamation from step (2).
(4) Edit the "sqlite3.c" file to insert a line near the top:
#define SQLITE_ENABLE_SQLLOG
Or, you can compile with -DSQLITE_ENABLE_SQLLOG, whichever is easiest for
you.
(5) Set the environment variable SQLITE_SQLLOG_DIR=$dir where $dir is an
empty directory someplace where you want logging information to be written.
(6) Run your application
The $dir will contain initial database contents and scripts of SQL
statements that were run. You can rerun those SQL statement using the
command-line shell to find slow ones, then do things like EXPLAIN QUERY
PLAN to figure out why they are slow and perhaps fix them by adding indices.
Further information in the header comment of test_sqllog.c.
http://www.sqlite.org/src/artifact/7813b47021a6d4e39bb7b1b328a8893dc59885cb
The SQLLOG facility is new. So you'll have to use the latest sources out
of Fossil for it to work.
> I am starting to look at tools like blktrace, is that the right path?
> Some guidance on how to use some low level tools would be appreciated,
>
> Regards
> Keith
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
