Today I was trying to execute a long-running (but highly optimized) query on a server in our company. I've run the same query on the same data on my home computer (5400rpm harddisk) and it took about 40min. The server is about 10 times slower, maybe still running! The server does about 30 inserts/second, my home computer 300 inserts/second.
As the CPU load is very low (3%), memory is plenty and network is not used as the query is executed locally I suspect the disk I/O to be the bottleneck. At my home computer instead (which got better CPU power) two cores were nearly fully loaded.
The relevant disk configuration is as follows:
SEAGATE ST3500631NS (7200 rpm, 500 GB)
LSI MegaRAID based RAID 5
4 disks, 1 hot spare
- No read-ahead
- Direct Cache Mode
- Harddisk-Cache-Mode: off
Surely, RAID5 may not be the fastest, but the disks compensate by being faster as at home.
Some further interesting data:
- Avg. Queue Length: 30
- Avg. Queue Length (Read) : 2
Avg. Queue Length (Write): 26
Bytes/s read: 1.3MB/s
Bytes/s write: 1.2MB/s
Writes per Second: 36
How can I resolve this write bottleneck? Is it because of the many small writes? Is there some malfunctioning driver or a possibility to cumulate some transactions for bigger writes?
The seconds per write are very large!