• Created by Unknown User (jaredg), last modified on Dec 03, 2007

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

DROP TABLE IF EXISTS "metaData";
CREATE TABLE "metaData" (
  "metaID" bigint(20) NOT NULL,
  "ip_name_src" varchar(52) NOT NULL default '',
  "ip_name_dst" varchar(52) NOT NULL default '',
  "transport" varchar(10) NOT NULL default '',
  "packetSize" smallint(6) NOT NULL default '0',
  "count" smallint(6) NOT NULL default '0',
  "packetInterval" smallint(6) default NULL,
  "deadline" smallint(6) default NULL,
  "ttl" smallint(6) default NULL,
  PRIMARY KEY  ("metaID"),
  UNIQUE ("ip_name_src","ip_name_dst","packetSize","count")
);

DROP TABLE IF EXISTS data_200112;
CREATE TABLE data_200112 (
  metaID bigint(20) NOT NULL default 0,
  minRtt float default NULL,
  meanRtt float default NULL,
  medianRtt float default NULL,
  maxRtt float default NULL,
  timestamp bigint(12) NOT NULL default 0,
  minIpd float default NULL,
  meanIpd float default NULL,
  maxIpd float default NULL,
  duplicates tinyint(1) default NULL,
  outOfOrder tinyint(1) default NULL,
  clp float default NULL,
  iqrIpd float default NULL,
  lossPercent float default NULL,
  rtts text,
  seqNums text,
  PRIMARY KEY  (metaID,timestamp),
  KEY meanRtt (meanRtt,medianRtt,lossPercent,meanIpd,clp)
);

SQLite table structure

Only minor changes were required for SQLite compliance. SQLite does not support referential integrity so foreign key definitions must be removed and it does not support constraint names or indexes defined in the table definition with the 'KEY' or 'INDEX' keywords.

DROP TABLE IF EXISTS "metaData";
CREATE TABLE "metaData" (
  "metaID" bigint(20) NOT NULL,
  "ip_name_src" varchar(52) NOT NULL default '',
  "ip_name_dst" varchar(52) NOT NULL default '',
  "transport" varchar(10) NOT NULL default '',
  "packetSize" smallint(6) NOT NULL default '0',
  "count" smallint(6) NOT NULL default '0',
  "packetInterval" smallint(6) default NULL,
  "deadline" smallint(6) default NULL,
  "ttl" smallint(6) default NULL,
  PRIMARY KEY  ("metaID")
);

DROP TABLE IF EXISTS data_200112;

CREATE TABLE "data_200112" (
  "metaID" bigint(20) NOT NULL default 0,
  "minRtt" float default NULL,
  "meanRtt" float default NULL,
  "medianRtt" float default NULL,
  "maxRtt" float default NULL,
  "timestamp" bigint(12) NOT NULL default 0,
  "minIpd" float default NULL,
  "meanIpd" float default NULL,
  "maxIpd" float default NULL,
  "duplicates" tinyint(1) default NULL,
  "outOfOrder" tinyint(1) default NULL,
  "clp" float default NULL,
  "iqrIpd" float default NULL,
  "lossPercent" float default NULL,
  "rtts" text,
  "seqNums" text,
  PRIMARY KEY  ("metaID","timestamp")
);

Note: some excessive quoting around field names was removed from some of the statements, but both engines appear to support DDL with or without it.

Database structure creation times

Database creation was quite fast, although MySQL reasonably involves more overhead.

MySQL DDL timing

jaredg@atreides > time mysql -u perftest perftest < metadata_create_mysql.sql
0.005u 0.002s 0:00.02 0.0%      0+0k 0+0io 0pf+0w

jaredg@atreides > time mysql -u perftest perftest < data_create_mysql.sql
0.008u 0.001s 0:00.01 0.0%      0+0k 0+0io 0pf+0w

SQLite DDL timing

jaredg@atreides > time sqlite3 perftest.db < metadata_create.sql
0.001u 0.002s 0:00.00 0.0%      0+0k 0+0io 0pf+0w

jaredg@atreides > time sqlite3 data.db < data_create_sqlite.sql
0.000u 0.002s 0:00.01 0.0%      0+0k 0+0io 0pf+0w

Data insert times

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.

Sample insert data for data_200112 table

INSERT INTO data_200112 (metaID, minRtt, meanRtt, medianRtt, maxRtt, timestamp, minIpd, meanIpd, maxIpd, duplicates, outOfOrder, clp, iqrIpd, lossPercent, rtts, seqNums) VALUES (390,19,21,NULL,31,1007194529,NULL,NULL,NULL,NULL,NULL,NULL,NULL,10,NULL,NULL);
INSERT INTO data_200112 (metaID, minRtt, meanRtt, medianRtt, maxRtt, timestamp, minIpd, meanIpd, maxIpd, duplicates, outOfOrder, clp, iqrIpd, lossPercent, rtts, seqNums) VALUES (390,19,22,NULL,49,1007196329,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,NULL,NULL);
INSERT INTO data_200112 (metaID, minRtt, meanRtt, medianRtt, maxRtt, timestamp, minIpd, meanIpd, maxIpd, duplicates, outOfOrder, clp, iqrIpd, lossPercent, rtts, seqNums) VALUES (390,19,20,NULL,21,1007198127,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,NULL,NULL);
INSERT INTO data_200112 (metaID, minRtt, meanRtt, medianRtt, maxRtt, timestamp, minIpd, meanIpd, maxIpd, duplicates, outOfOrder, clp, iqrIpd, lossPercent, rtts, seqNums) VALUES (390,19,19,NULL,20,1007199920,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,NULL,NULL);
INSERT INTO data_200112 (metaID, minRtt, meanRtt, medianRtt, maxRtt, timestamp, minIpd, meanIpd, maxIpd, duplicates, outOfOrder, clp, iqrIpd, lossPercent, rtts, seqNums) VALUES (390,19,20,NULL,23,1007201718,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,NULL,NULL);

MySQL insert DML timing

jaredg@atreides > time mysql -u perftest perftest < metadata_data.sql
0.006u 0.004s 0:00.05 0.0%      0+0k 0+0io 0pf+0w

jaredg@atreides > time mysql -u perftest perftest < data_insert.sql
6.063u 4.518s 0:53.76 19.6%     0+0k 0+0io 0pf+0w

SQLite insert DML timing

For SQLite it was ncessary to explicitly enable an exclusive lock during the length of the insert process. This was done by starting the entire series of insert statements with begin exclusive transaction; and ending with commit;. Without that, SQLite acquires and then releases a lock on the database file using a semaphore file on the file system with each insert statement. This is very costly – a very small number of rows were inserted in a minute without exclusive locking, while the entire dataset could be inserted in under 60 seconds with it enabled.

Exclusive locking was not used during the metaData inserts.

jaredg@atreides > time sqlite3 perftest.db < metadata_data.sql
0.077u 0.098s 0:01.16 13.7%     0+0k 0+0io 0pf+0w

jaredg@atreides > time sh sqlite_load.sh
42.961u 2.456s 0:50.38 90.1%    0+0k 0+0io 0pf+0w

Contents of sqlite_load.sh - begin_txn.sql and commit.sql contain the SQL statements described above

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
  • No labels