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.
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)