Page History

To compare the performance of MySQL vs SQLite, a quick sample database was made based off of a previous PingER SQL database schema. Existing converted data was available based on PingER results from December, 2001.

Summary

SQLite performs at the same or better speed vs. MySQL when dealing with "single-threaded" activty, i.e. only one process reading and writing to the database. SQLite's locking must be taken into account when inserting into a table, however. Further tests on concurrent access are in order.

Testing environment

Dell desktop with single core non-hyperthreading Intel Pentium 4 2.8 ghz, 1gb RAM, local SATA storage, Red Hat Enterprise Desktop 4u5 (2.4.21), MySQL 4.1.20 running locally, SQLite 3.3.4.

The shell time command is used below to measure the amount of processing involved with each task. Unfortunately, it cannot capture the CPU use of the MySQL daemon processes that are handling the other end of the client-server connection. Elapsed time must be used to estimate run time. The machine being used for testing was not being used for anything else at the time the tests were run.

Table structure

MySQL table structure

...

210 rows were inserted into the metaData table and 325,720 rows were inserted into the data_200112 table. The sample data consists of a month's worth of PingER results from 22 sources to between 4 and 9 destinations.

The data was formatted in traditional SQL style single INSERT statements (rather than the compound multiple-row INSERT format MySQL supports).

MySQL's elapsed time was actually longer than SQLite. The shell time command does not capture the CPU use of the MySQL daemon processes that are handling the client-server connectivity.

Sample insert data for data_200112 table

...

cat begin_txn.sql data_insert.sql commit.sql | sqlite3 data.db

Query timing

Queries run

select m.ip_name_src, min(minRtt)
from metaData m join data_200112 d on m.metaID = d.metaID
where m.ip_name_dst like '%slac.stanford.edu'
  and m.ip_name_src not like '%stanford.edu'
  and d.minRtt is not null and d.minRtt > 0
group by m.ip_name_src
order by m.ip_name_src;

select m.ip_name_dst, min(minRtt), max(maxRtt)
from metaData m join data_200112 d on m.metaID = d.metaID
where m.ip_name_src like '%bnl.gov'
  and d.maxRtt is not null
group by m.ip_name_dst
order by m.ip_name_dst;

select m.ip_name_dst, min(minRtt), max(maxRtt), avg(meanRtt)
from metaData m join data_200112 d on m.metaID = d.metaID
where m.ip_name_src like '%hep.net'
and timestamp between 1008403200 and 1008435600
group by m.ip_name_dst
order by m.ip_name_dst;

MySQL timings

jaredg@atreides > time mysql -u perftest perftest < queries.sql
... returned data omitted ...
0.006u 0.002s 0:01.68 0.0%      0+0k 0+0io 0pf+0w

SQLite timings

jaredg@atreides > time sh sqlite_queries.sh
... returned data omitted ...
0.644u 0.063s 0:00.74 94.5%     0+0k 0+0io 0pf+0w