Mo Sep 18 15:29:53 CEST 2006

myisam vs innodb

Very quick'n'dirty almost unuseable benchmark of mysql 5 myIsam vs. innoDB tables. In these rudimentary tests (which btw have no correlations with reality) myIsam is about 2.7 times as fast as innoDB is - or innoDB is even 2.7 times as slow as myIsam is, depending on your point of view..
MyIsam lacks support of transactions and locks tables instead of rows, so you should use innoDB anyway - although it is slower and needs more space.

iso@deathbird:~$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13 to server version: 5.0.24a-Debian_3-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database testeimer;
Query OK, 1 row affected (0.04 sec)

mysql> use testeimer;
Database changed

mysql> create table `testmyisam` ( `id` bigint auto_increment, 
`title` varchar(128), primary key (`id`)) type=MyISAM charset=utf8;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> create table `testinnodb` ( `id` bigint auto_increment, 
`title` varchar(128), primary key (`id`)) type=innoDB charset=utf8;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> Bye
iso@deathbird:~$ vi testme.sql
iso@deathbird:~$ head -1 testme.sql
INSERT INTO `testmyisam` ( `title` ) values ( 'kapsobor' );
iso@deathbird:~$ wc testme.sql
 100000 1000000 6000000 testme.sql
iso@deathbird:~$ time mysql -u root testeimer < testme.sql

real    1m43.189s
user    0m8.269s
sys     0m3.572s
iso@deathbird:~$ vi testme.sql
iso@deathbird:~$ head -1 testme.sql
INSERT INTO `testinnodb` ( `title` ) values ( 'kapsobor' );
iso@deathbird:~$ wc testme.sql
 100000 1000000 6000000 testme.sql
iso@deathbird:~$ time mysql testeimer < testme.sql

real    4m48.227s
user    0m9.785s
sys     0m3.720s
iso@deathbird:~$ time mysql testeimer -e "update testmyisam set title='eimersaufen'";

real    0m4.308s
user    0m0.020s
sys     0m0.004s
iso@deathbird:~$ time mysql testeimer -e "update testinnodb set title='eimersaufen'";

real    0m7.569s
user    0m0.016s
sys     0m0.012s
iso@deathbird:~$ time mysql testeimer -e "select count(*) from testmyisam"
+----------+
| count(*) |
+----------+
|   100000 |
+----------+

real    0m0.118s
user    0m0.020s
sys     0m0.000s
iso@deathbird:~$ time mysql testeimer -e "select count(*) from testinnodb"
+----------+
| count(*) |
+----------+
|   100000 |
+----------+

real    0m0.572s
user    0m0.024s
sys     0m0.004s
iso@deathbird:~$ time mysql testeimer -e "select count(id) from testmyisam"
+-----------+
| count(id) |
+-----------+
|    100000 |
+-----------+

real    0m0.110s
user    0m0.024s
sys     0m0.004s
iso@deathbird:~$ time mysql testeimer -e "select count(id) from testinnodb"
+-----------+
| count(id) |
+-----------+
|    100000 |
+-----------+

real    0m0.307s
user    0m0.020s
sys     0m0.004s
iso@deathbird:~$ time mysql testeimer -e "update testmyisam set title=title + 'kapsobor'";

real    0m8.362s
user    0m0.016s
sys     0m0.008s
iso@deathbird:~$ time mysql testeimer -e "update testinnodb set title=title + 'kapsobor'";

real    0m10.894s
user    0m0.024s
sys     0m0.008s

Posted by iso | Permanent Link | Tags: nerd stuff, mysql, linux | comments >>