Debian server dies when MySQL builds index?

Question

MySQL

There is an EQ-4 server with Debian Squeeze 2.6.32-5-amd64. There is Software-RAID 1.

MySQL 5.1.49 lives on the server, which has a 4 GB MyISAM table with 500 MB indexes. Indexes are removed into memory and selections from the table occur quite quickly (by the way, some settings managed to speed up the selection by 10 times, but I forgot which ones I changed the config with).

Problems start when data is added to the table or an index is built on a clean table (I often delete data and load completely new ones). Disk utilization for /dev/sda - 100%, for /dev/sdb - 50%. All disk operations start to slow down terribly. Access to other tables(with ridiculous dimensions) is very slow. Apache and nginx create processes and threads in bulk. Monitoring does not monitor (crashes due to timeout for responses from some utilities). LA flies up to 30..50 (normally it is about 0.5 there).

I don't mind that the database needs a disk. But what can be done so that when the disk is actively used by one application, other applications do not die so quickly ? Is it possible to somehow specify priorities?

Don't offer FreeBSD :-)
I'd love to, but I can't handle installation and configuration.


UPDATE:
As a result of a little testing, I switched to another I / O scheduler. Was cfq (!!!), became anticipatory. Now I'm testing at work.


UPDATE-2:
Tested different schedulers in real situations. The choice of scheduler and the correct setting of their parameters greatly affects the performance and responsiveness of the server. Now I settled on the deadline, but you can’t give an unambiguous recommendation here: the choice depends on the priority tasks of your server. I will try to understand the issue and write in more detail.

Answer 1

Try declaring your large table with DELAY_KEY_WRITE, although this is potentially dangerous, it gives a very noticeable performance boost in your and similar cases, since it eliminates the simultaneous writing of data files and indexes to disk.

Answer 2

In vain you are driving cfq) With the help of it, and only it, you can set the priority of the IO process with the ionice utility. Play around with priorities, not with schedulers.
And yes ... Add screws all the same) If you use SATA, go to SAS. If SAS, increase their number.
I myself once suffered with these schedulers and off-scale IOWAIT, until it dawned on me that if the battleneck is in IO, then the software methods of OS tuning are ineffective in the end. It is necessary to expand the possibilities of IO.

Answer 3

if possible, move the database to a separate disk, at least it
helps me in the virtual machine (virtual disk) they say, play around with the disk settings
vasilisc.com/speedup_ubuntu#speedup_fs