Large number of updates on master: myisam vs innodb

MySQL & MariaDB Other MySQL® Questions
1

traditionally, i’ve always gone with myisam and replication. master takes all the inserts and slave handles all the reads and this architecture has worked well.

i’m working on a new project that involves a large number of updates to existing records (as opposed to the large number of inserts from before). the master isn’t holding up as well. the number of tables have also increased slightly (previous projects were in the range of 10 tables whereas this project has around 20 tables).

i’ve noticed a sharp drop in the key efficiency of the master, so i’ve boosted the key_buffer size on the master. it seems to help a bit, but the updates are still taking up to 1-2 seconds to complete at peak traffic (~1k qps). i thought it could be the limited number of open files affecting the system (because of the increase in number of tables as compared to previous projects), so i increased table_cache a bit, but i have yet to see any significant impact from that tweak…

should i start migrating the tables that see the most amount of updates to innodb because of the row-level locking? will there be a performance hit on the slave as a result of this?

these tables aren’t even that big right now (~500k rows). i’ve had tables with ~500 millions rows on myisam that ran more smoothly than this…

any advice would be greatly appreciated!!

2

I’m not sure why you would divide reads and writes that way. In the scenario you’ve described, the slave must perform all reads and writes, while the master performs only writes. All you’ve really got is a spare and no load balancing. If your master starts getting slow, you should expect that your slave is lagging by even more, assuming equivalent hardware.

To see if the table cache is an issue, you can just do SHOW STATUS LIKE ‘Opened_tables’. If that number is growing rapidly, it suggests you need a larger table cache.

You can also look at SHOW STATUS LIKE ‘Table_locks_waited’. If that is increasing rapidly it means there are a lot of queries which had to wait to get access to a table. I’m not sure there is a status counter for the amount of time spent waiting for table locks, like there is for InnoDB row locks. If you have the slow query log enabled, it will show how much time was spent waiting for locks during the execution of a slow query, so that will give you some indication if that is the problem.

I don’t think 1k updates per second sounds unreasonable for MyISAM, but it’s been years since I’ve used it for anything. Switching to InnoDB will only help if you’re very certain you’re spending a large amount of time waiting for table locks.