Innodb – MyISAM InnoDB performance

innodbmyisamperformance

MyISAM vs InnoDB

I keep reading how InnoDB is faster than MyISAM. I wanted to find out myself.

When I was using a forloop in PHP to fill my MySQL test database with 100,000 numbers using PDO driver.

|a|
+-+
|0|
|1|
|2|
|3|
|4|
...
|99999|

I find out that with InnoDB it went 95 records per second.
After a while I stopped my script cause going to 100,000 is just going to take too long.
Changing my table to MyISAM and re-running the script made a whopping 6000 records per second!
Both tables have no indexes.

How is it that the internet is claiming InnoDB is faster than MyISAM. Could it be that PDO is bottlenecking InnoDB?

Best Answer

This is often one of the most mythical subjects of MySQL. To separate fact from fiction, you need to remember the strengths and weaknesses of each Storage Engine. Each Storage Engine and application will govern the workload, read I/O, write I/0, and tuning options.

I have written many posts about how and when to pick one storage engine over the other:

Sep 20, 2011 : Best of MyISAM and InnoDB
May 03, 2012 : Which is faster, InnoDB or MyISAM?
Jul 05, 2012 : InnoDB vs MyISAM with many indexes
Sep 26, 2012 : Choosing MyISAM over InnoDB for these project requirements; and long term options

I have many more posts but you can start with these.

OBSERVATION #1

Just looking at the output of your SHOW ENGINE INNODB STATUS\G I see a variety of table lock message at the bottom of each transaction. You have one transaction that is holding lock 38096 rows

------------------
---TRANSACTION 0 20639508, ACTIVE 30 sec, process no 1655, OS thread id 47843558143744 fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 700 lock struct(s), heap size 63472, 38096 row lock(s)
MySQL thread id 99117, query id 2086890 s16468810.onlinehome-server.info 87.106.82.83 web0 Updating
UPDATE userproducts SET lowest_price=109.9, last_lowest_price_update='2012-09-12 17:14:53' WHERE product_id LIKE 'B002AL8F72'
------- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 631 page no 985 n bits 136 index `PRIMARY` of table `usr_web0_1`.`userproducts` trx id 0 20639508 lock_mode X waiting
Record lock, heap no 59 PHYSICAL RECORD: n_fields 34; compact format; info bits 0
 0: len 4; hex 815d9f43; asc  ] C;; 1: len 6; hex 0000013aef08; asc    :  ;; 2: len 7; hex 00000000391358; asc     9 X;; 3: len 4; hex 80006f9a; asc   o ;; 4: len 12; hex 30302d3956335a2d47555a34; asc 00-9V3Z-GUZ4;; 5: len 10; hex 42303032414c38463732; asc B002AL8F72;; 6: len 4; hex cdccdb42; asc    B;; 7: len 4; hex 00000000; asc     ;; 8: len 4; hex cdccdb42; asc    B;; 9: len 4; hex 000080bf; asc     ;; 10: len 4; hex 800003e7; asc     ;; 11: len 4; hex cdccdb42; asc    B;; 12: len 30; hex 537572706c75732056696e74616765204a61636b65204d36352052656769; asc Surplus Vintage Jacke M65 Regi;...(truncated); 13: len 30; hex 687474703a2f2f6563782e696d616765732d616d617a6f6e2e636f6d2f69; asc http://ecx.images-amazon.com/i;...(truncated); 14: len 4; hex 800034c5; asc   4 ;; 15: len 10; hex 42656b6c656964756e67; asc Bekleidung;; 16: len 3; hex 4e2f41; asc N/A;; 17: len 3; hex 4e2f41; asc N/A;; 18: len 4; hex 80000000; asc     ;; 19: len 4; hex 80000000; asc     ;; 20: len 4; hex 80000000; asc     ;; 21: len 4; hex 80000000; asc     ;; 22: len 4; hex 80000000; asc     ;; 23: len 4; hex 80000000; asc     ;; 24: len 1; hex 80; asc  ;; 25: SQL NULL; 26: SQL NULL; 27: len 4; hex 00000000; asc     ;; 28: len 4; hex 00000000; asc     ;; 29: len 4; hex 00000000; asc     ;; 30: len 4; hex 6575726f; asc euro;; 31: len 4; hex 00000000; asc     ;; 32: len 8; hex 8000124cc3d291ec; asc    L    ;; 33: len 8; hex 8000124cc3d28fc6; asc    L    ;;

OBSERVATION #2

Looking back at the table structure, I see the userproducts table has no index on product_id. Because of this, you are doing full table scans to perform the update of a single row.

OBSERVATION #3

You also have a duplicate index in the userproducts table. Two indexes start with the userinfos_id column. No need to bloat indexes with duplicate leading columns.

OBSERVATION #4

You have innodb_thread_concurrency = 8. It should be set to zero for infinite concurrency

See my past posts on this

May 26, 2011 : About single threaded versus multithreaded databases performance
Sep 12, 2011 : Possible to make MySQL use more than one core?
Feb 24, 2012 : Tuning dedicated Percona Server with XtraDB for OLTP
Mar 16, 2012 : Using multiple cores for single MySQL queries on Debian

RECOMMENDATION #1

Add this to /etc/my.cnf

[mysqld]
innodb_thread_concurrency = 0

and restart MySQL

RECOMMENDATION #2

Please run this during your off period

ALTER TABLE userproducts ADD INDEX product_id (product_id),DROP INDEX userinfos_id;

RECOMMENDATION #3 (Optional Long Term Goal)

You need to restructure your InnoDB Layout because you have innodb_file_per_table on. That nullifies the need for multiple ibdata files. You only need one. Please see my past posts on this:

Oct 29, 2010 : My Original Post in StackOverflow
Apr 01, 2012 : Is innodb_file_per_table advisable?
Mar 25, 2012 : Why does InnoDB store all databases in one file?
Feb 03, 2012 : Scheduled optimization of tables in MySQL InnoDB

Give it a Try !!!

Best Answer

Related Solutions

Mysql: Insert performance INNODB vs MYISAM

Mysql – Where is the INNODB Performance Leak