Dealing with cron jobs and MySQL/CPU contention on a single dedicated server architecture

Question

We have a single small EC2 instance where we run a front-end server (php5-fpm/nginx) but also a lot of fetching cron php scripts inserting a lot of data (2k+rows every ~15min) which seems to completely hang the MySQL server for 1-2 minutes, crashing the front-end website (getting time out).

I think using a master and a slave (read-only, for the front-end) MySQL servers could fix this (?), but I am limited to 1 instance for this project.

I already have tweaked MySQL config with mysqltuner and set the inserts to low priority.

Prior to being on EC2, we were using a shared hosting and we did not encounter this problem, probably because of better overall hardware on shared server vs our small EC2 instance?

Possibles solutions I tought of, but they doesn't seems perfect or to really solve the problem at source :

• use cpulimit for cron jobs (will be slower to fetch stuff, but MySQL will still hang during massive inserts)
• artificially slow the insert rate (possible but at a limite extent, 2k rows must be inserted quit quickly). I think one large insert should be better than 2k atomic inserts.
• running two MySQL deamons on the same server (it seems like a bad idea at first but maybe it could ease the contention)
• switch to InnobDB (using misam) or even a full switch to PostgreSQL?
• fully cache front-end to avoid any MySQL related hanging (not possible on all pages, already a lot of cached data but they must be updated often after the crons fetching new data run)

Answer 1

I'd suggest pre-populating the table(s) with enough empty rows for a full day's worth of data. Should make inserting much faster since allocation isn't needed.

Answer 2

I think innodb is your best option. MyISAM does table level locking which will block during your updates. MySQL Locking Details Doc

I have also had mixed results with m1.small instances. They are fractional CPU splits and have to yield if your VM roommates are busy. (check vmstat for cpu steal) You may try briefly upgrading to a c1.medium. EC2 instance types It has the same ram footprint as a small but 5X the CPU resources. (at double the costs)

Two MySQL instances on a small is probably a bad idea. (only one virtual CPU)

If you want to stay on the cheap, try to spread those 2000 writes out over the 15 minute window. (~200 minute)

You also didn't give any details about your memory or mysql settings. Is your myisam_block_size 4k? What is your MyISAM key_buffer? Have you looked at the MyISAM concurrent_insert setting?

Cheers.