Yesterday, i had locking table problem in myIsam table. The solution proposed is to change existing myIsam table into InnoDB table. So we changed the engine table as proposed. For information, my existing table had about 50000 rows. Atfer changing the table functionality test was performed and the program worked well. But we notice another problem when real load is applied to the application. we notice the reduced TPS.
From mysql command “show full processlist”, we saw UPDATE query in action.
So in default localhos mysql server i tried to run that run the same query againts +- 5000 rows innodb table & +-5000 rows myIsam table
UPDATE testisam SET sent = sent + 1 WHERE id = ‘5000’;
Table isam, 100 query update on particular row
Id sent startupdate endupdate
5000 99 2015-09-23 15:48:53 2015-09-23 15:48:53
execution time ~ 1 s
UPDATE testinno SET sent = sent + 1 WHERE id = ‘5000’;
Table inno, 100 query update on particular row
Id sent startupdate endupdate
5000 99 2015-09-23 15:47:07 2015-09-23 15:47:11
execution time ~5 s
From the result above, in default configuration, UPDATE query to single row on innodb table will take longer time than myIsam table. While it is true that InnoDB engine will prevent table locking, several tuning and changes in query should be made in order to work well with InnoDB table.
So because this “tuning & changes in query” will take sometime to learn/read. I decided to revert back to MyIsam table.. 😛
