Most of our servers for years have been hooked to FC (fiber channel) attached disk with /tmp down localdisk. This has worked well but now due to the complexity and number of servers involved it’s easier to just buy servers with localdisk (12 disk 15,000k SAS disk). We have been configuring them with 1 large array for data and a separate non raided disk for /tmp. We’ve noticed early on that having /tmp in the same volume as /data is very damaging as it will easily IO bound a busy server and the sequential writes of /tmp don’t play well with the random IO of /data. The problem now is that even with /data and /tmp being on separate disks, heavy IO down /tmp (large sort files) can still IO bound the controllers (3ware, MegaRaid LSI) and cause contention to /data. Any thoughts on the best way to avoid this situation? I’m half tempted to somehow plug the one disk directly into an onboard scsi controller (if available) and not going through the raid controller so I can use it for /tmp. The other option is to buy a flash PCI card and use it for /tmp. It seems to me that the SCSI controllers should be able to handle this without affecting the other controllers. I tried turning off writeback caching on the /tmp disk but the behavior was unchanged. Any other ideas?
I would use a separate controller card, if possible. You could also look into some of Tyan’s dual AMD processor motherboards supporting multiple PCI busses, if PCI bus usage is an issue. Also, the CPU in the controller card could be the limiting factor. Is a faster version available?
I am not an expert, however.
You don’t say anything about how large your temporary files are so I can’t give you any definitive advice.
But since you are already considering buying additional hardware I would suggest buy a lot of RAM for the money instead of separate controllers etc.
Usually a lot more better way of spending your money since sorting can then be performed in RAM instead of temporary files.
The temporary files created by mysql can vary but can go up to several GB in size which is alsotypically when we see the issue. We currently have 16->32GB of RAM in the boxes so it seems they have plenty of RAM available. It seems the problem is that if the sort takes serveral minutes the file systems cache will sync to disk wether you want it to or not. Also typically when we see this issue it probably could do all the sorting in RAM even at several GB.
Some questions:
-
Does your tables contain BLOB or CLOB columns?
-
What settings do you have on your server variables?:
sort_buffer_size
max_heap_table_size
tmp_table_size
BTW:
You want write back cache activated to speed up random writes.
It will not do much for sequential writes since then the cache is usually filled up very fast anyway. But for random writes it usually speeds up a lot.