You are viewing an old version of this page. View the current version.

Compare with Current View Page History

Version 1 Next »

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.

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 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

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