Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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)
share|improve this question

2 Answers 2

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.

share|improve this answer
    
This is interesting. Can you explain a little more how this would work? –  pjmorse Dec 10 '12 at 12:00
    
If you have a slower time during the night, run a cron job to insert 1 days worth of empty rows of data. Then fill these rows during the day. You will need a separate counter to keep track of the "current" data row to fill, but the big job of allocating pages to hold the data will be done. And convert to fixed field lengths also if possible. There might be another way to pre-allocate row storage for mysql, I haven't checked. –  Avibodha Dec 13 '12 at 0:35

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.