MySQL: sysbench test - InnoDB vs Memory tables

Question

I've done some tests in order to investigate performance issue on the new HP Gen8 server (Intel(R) Xeon(R) CPU E5-2630 0 @ 2.30GHz)

I've created two tables, first one is using InnoDB storage engine and the second one is in Memory - heap table.

System details:

sysbench-0.4.12-5.el6.x86_64
CentOS release 6.4 (Final)

Prepare stage:

InnoDB

# sysbench --db-driver=mysql --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 --mysql-db=sbtest1 prepare

Memory (heap)

# sysbench --db-driver=mysql --test=oltp --mysql-table-engine=heap --oltp-table-size=1000000 --mysql-db=sbtest2 prepare

Testing stage:

Sysbench – read only test – single table with 1 mln rows - data size 559MB (527MB data + 31MB indexes)

InnoDB

# sysbench --db-driver=mysql --test=oltp --mysql-table-engine=innodb --num-threads=128 --max-requests=100000 --oltp-read-only run

Total time: 16.3648s, TPS (transactions per second): 6111.40

Memory (heap)

# sysbench --db-driver=mysql --test=oltp --mysql-table-engine=heap --mysql-engine-trx=no --num-threads=128 --max-requests=100000 --oltp-read-only run

This test is running much longer and I had to stop it as the load on the server was very high - even if this is in memory table!?.

Answer 1

I have addressed InnoDB vs MEMORY before

• Jan 21, 2012 : Is InnoDB Engine up to speed against Memory Engine?
• Jan 17, 2012 : Mysql Memory table getting many locks

I also addressed the one headache MEMORY tables have: There is still disk I/O because of the .frm file (See my post I am using the MEMORY storage engine but MySQL still writes to my disk...Why?).

Also worth remembering is the fact that MEMORY tables do table-level locks.

You should just increase the InnoDB buffer pool and leave MEMORY tables behind

• HTF: Thanks, changing index type to BTREE helped a lot.