InnoDB Website

Fast, Reliable, Proven transactional storage for MySQL

MySQL/InnoDB-4.0.15 sets a world record in SPECjAppServer2002 price/performance

(August 2004) MySQL/InnoDB, BEA WebLogic Application Server & Sun Hardware Team Beats SPEC Results from Oracle, Microsoft & IBM. SPECjAppServer2002 (Java Application Server) is a client/server benchmark for measuring the performance of Java Enterprise Application Servers using a subset of J2EE APIs in a complete end-to-end web application.

The combination of MySQL, WebLogic and Sun provided the best all-around price/performance of any submitted Multiple Node environment, beating out solutions from Oracle, IBM, Microsoft and others by more than 40%. Additionally, the MySQL entry offered the fastest total operations per second performance of any of the other databases running on a four-processor server. See the press release at the MySQL website.

See the full disclosure.

MySQL/InnoDB-4.0.1 and Oracle 9i win the database server benchmark of PC Magazine and eWEEK

Read the full story.

InnoDB and PostgreSQL in mass inserts and a big join

Results from a test with MySQL/InnoDB-3.23.39 and PostgreSQL-7.1.1.

	Wall clock time in seconds
Test	InnoDB	PostgreSQL 7.1.1
Insert of 100 000 rows, copied from a table	1.96	9
Sum of an integer column from a join of 100 000 rows	0.58	7

Test details: the computer was a 2-CPU Xeon 450 MHz with 512 MB physical memory and Linux kernel 2.4. Both databases were configured with a 24 MB buffer pool (called shared cache in PostgreSQL) and a 4 MB log buffer. The SQL queries were:

CREATE TABLE T1 (A INT NOT NULL, B INT, PRIMARY KEY(A)) TYPE=INNODB;
CREATE TABLE T2 (A INT NOT NULL, B INT, PRIMARY KEY(A)) TYPE=INNODB;

Then insertion of 100 000 rows in an ascending order to T1. The time we measured for insertions was from the following statement:

INSERT INTO T2 SELECT * FROM T1;

The join time was the average of the following:

SELECT SUM(T1.B) FROM T1, T2 WHERE T1.A = T2.B;
SELECT SUM(T1.B) FROM T1, T2 WHERE T1.A = T2.B;
SELECT SUM(T1.B) FROM T1, T2 WHERE T1.A = T2.B;
SELECT SUM(T1.B) FROM T1, T2 WHERE T1.A = T2.B;
SELECT SUM(T1.B) FROM T1, T2 WHERE T1.A = T2.B;

The test against PostgreSQL measures basic internal speed of the database server. Since each test is done with just a single SQL statement, the time used to communication with the client is negligible and each test runs in a single transaction. Both InnoDB and PostgreSQL are transactional SQL databases with row level locking, thus results can be compared.

InnoDB and MyISAM table types in MySQL

MyISAM is the default table type used in MySQL and InnoDB is a table type supporting transactions in MySQL. I wrote a Perl program which inserts 100 000 rows to a table with 3 integer columns and two indexes. Then another Perl program fetches each row either through a secondary index or the primary key. The Perl programs for each test are at the end of this web page.

I ran the tests on a Linux 2-CPU Xeon 450 MHz. The results were:

	Wall clock time in seconds
Test	InnoDB	MyISAM
100 000 inserts	25	40
100 000 selects on primary key	57	58
100 000 selects on secondary key	68	95

InnoDB and a market-leading database

This test was run using a standalone version of InnoDB, no MySQL included. The test uses the stored procedure capability of InnoDB, which is not available through MySQL.

Test computer:: HP Omnibook 4150, 300 MHz mobile PII, 512 kB cache, 64 MB RAM, 4 GB disk, Win NT 4.0 sp 4.
Test configuration of databases:: 16 MB buffer cache, 15 MB other memory, 5 MB log buffer, 15 MB log files.

Source code of tests for InnoDB is included at the end of this page. Note that the tests were not run in the exactly same way for the other database: the comparison does not satisfy strict standards. The name of the other database cannot be mentioned here because its license agreement forbids the publication of benchmarks without a permission from the vendor.

	Times in seconds
	Server process CPU time (from Task Manager)		Wall clock time
Test	InnoDB	Other DB	InnoDB	Other DB
Insert 100 000 rows in a stored procedure loop	4	63	5.2	180
Insert 100 000 rows copied from a table	4	18	7.5	65
`COUNT(*)` of a 100 000 row star join	0.5	2.5	0.5	2.5
100 000 row star join where also data is retrieved from the smaller table, produces no rows in the result set	0.7	7	0.7	7
10 000 TPC-A-like transactions in buffer pool, with commits	3	27	6	60
10 000 TPC-A-like operations, without commits	3	17	4.6	30
Update a non-indexed column of 100 000 rows	2	14	6.2	40
Delete 100 000 rows	6+10(*	40	13+10(*	200

*) In delete, InnoDB used 10 seconds to purge the records which had been marked as deleted.

Source code of tests for InnoDB

CREATE TABLE T (A INT, B CHAR, C INT);

CREATE UNIQUE CLUSTERED INDEX T_IND ON T (A);
CREATE INDEX T_IND2 ON T (C);

CREATE TABLE S (SA INT, SB CHAR, SC INT);

CREATE UNIQUE CLUSTERED INDEX S_IND ON S (SA);
CREATE INDEX S_IND2 ON S (SC);

/* Insert of 100 000 rows in a loop */
I INT;
BEGIN
FOR I IN 1 .. 100000 LOOP
INSERT INTO T VALUES (I, '1234567890', RND(0, 9999));
END LOOP;
COMMIT WORK;

/* Insert of 100 000 rows from a table */
INSERT INTO S SELECT * FROM T CONSISTENT READ;
COMMIT WORK;

/* Star join of 100 000 rows */
ROW_PRINTF SELECT COUNT(*) FROM T, S WHERE C = SA
                    CONSISTENT READ;

/* Star join of 100 000 rows, when data has been read in buffer
pool */
ROW_PRINTF SELECT COUNT(*) FROM T, S WHERE C = SA
                    CONSISTENT READ;

/* Star join of 100 000 rows where also data retrieved,
produces no output rows */
ROW_PRINTF SELECT * FROM T, S WHERE C = SA AND SB = 'a234567890'
                    CONSISTENT READ;

ROW_PRINTF SELECT * FROM T, S WHERE C = SA AND SB = 'a234567890'
                    CONSISTENT READ;
COMMIT WORK;

/* Run 10 000 TPC-A-like transactions in buffer pool, commits
with no log flush */
I INT;
BEGIN
FOR I IN 1 .. 10000 LOOP
UPDATE T SET B = 'a234567890' WHERE A = RND(1, 1000);
UPDATE T SET B = 'a234567890' WHERE A = RND(1, 1000);
UPDATE T SET B = 'a234567890' WHERE A = RND(1, 1000);
INSERT INTO T VALUES (100000 + I, '1234567890', 5);
COMMIT WORK;
END LOOP;

/* Run 10 000 TPC-A-like transactions where no commits */
I INT;
BEGIN
FOR I IN 1 .. 10000 LOOP
UPDATE T SET B = 'a234567890' WHERE A = RND(1, 50000);
UPDATE T SET B = 'a234567890' WHERE A = RND(1, 50000);
UPDATE T SET B = 'a234567890' WHERE A = RND(1, 50000);
INSERT INTO T VALUES (110000 + I, '1234567890', 5);
END LOOP;
COMMIT WORK;

/* Update 100 000 rows on non-index field */
UPDATE S SET SB = 'a234567890';
COMMIT WORK;

/* Delete 100 000 rows: garbage collection will start when delete is
ready */
DELETE FROM S;
COMMIT WORK;

Perl programs used in tests of MySQL table types MyISAM and InnoDB

........................
$dbh = $server->connect();

$dbh->do("set autocommit = 0");

$dbh->do("drop table speed1");

$dbh->do(
"create table speed1 (a int not null, b int, c int,\\
primary key (a), index (b)) type = innodb");

for ($j = 0; $j < 100000; $j = $j + 1) {

       $dbh->do(”insert into speed1 values ($j, $j, $j)”);
}

$dbh->do(”commit”);

$dbh->disconnect;                               # close connection
……………………..
$dbh = $server->connect();

$dbh->do(”set autocommit = 0″);

$s = 0;

for ($j = 0; $j < 100000; $j = $j + 1) {
      $s += fetch_all_rows($dbh, "select * from speed1 where a = $j");
}

$dbh->do(”commit”);

print(”$s rows fetched \\n”);

$dbh->disconnect;                               # close connection
……………………
$dbh = $server->connect();

$dbh->do(”set autocommit = 0″);

$s = 0;

for ($j = 0; $j < 100000; $j = $j + 1) {
      $s += fetch_all_rows($dbh, "select * from speed1 where b = $j");
}

$dbh->do(”commit”);

print(”$s rows fetched \\n”);

$dbh->disconnect;                               # close connection
…………………….

The Perl program used in the scalability test with concurrent inserts and selects to the same table:

use DBI;
use Benchmark;

chomp($pwd = `pwd`); $pwd = "." if ($pwd eq '');
require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\\n";

$dbh = $server->connect();

$dbh->do("set autocommit = 1");

$dbh->do("drop table speed1");

$dbh->do(
"create table speed1 (a int not null, b int, c int,\\
 primary key (a), index (b)) type = myisam")
|| die $dbh->err;

$s = 0;

for ($j = 0; $j < 1000000; $j = $j + 50) {
  $dbh->do(”insert into speed1 values ($j, $j, $j)”);
  $s += fetch_all_rows($dbh, “select * from speed1 where a = $j - 100″);
}

$dbh->do(”commit”);

print(”$s rows fetched\\n”);

$dbh->disconnect;