Well, this is becoming a pattern. Wake up, take a look at the systems here at Thistle Dew World HQ, and find the server hammered with loads north of 36 (taking as a rule, load average should equal the number of CPUs, that would be bad in a single CPU system), I think it’s time I made some hardware improvements. The graph tells an ugly tale: from 4 AM until 6:30 or so, it was thrashing so hard it couldn’t even graph it’s own vital signs.
So I did a little research on tuning, and the consensus is that this system is starved for resources. I could add faster disks (some configuration of RAID, at various levels of expenditure), break the database server off by itself, or simply add RAM. Option 3 looks to offer the most bang for the buck: the box is capable of holding three times as much RAM as it has now, so that looks like a pretty quick improvement.
I’m seeing a lot of httpd in free(): warning: recursive call errors and all signs point to some issues with PHP and Apache under load, though to be fair, most of the Googling I have done mentions Apache 1.3, not 2.
High Performance MySQL Chapter 6: Server Performance Tuning:
To bridge the gap between blazingly fast CPUs and comparatively slow disks, we have memory. With respect to performance, it’s in the middle—significantly faster than disks but still much slower than the CPU. The underlying operating system generally uses free memory to cache data read from and written to disk. That means if you frequently query the same small MyISAM table over and over, there’s a very good chance you’ll never touch the disk. Even though MySQL doesn’t cache row data for MyISAM tables (only the index blocks), the entire MyISAM table is likely in the operating system’s disk cache.
Modern CPUs are even substantially faster than main memory. To combat this mismatch, chip makers have designed multilevel caching systems. It’s common for a CPU to contain level 1, level 2, and even level 3 caches. The caches use significantly faster and more expensive memory, so they’re generally a fraction of the size of main memory; a 512-KB L2 cache is generous.
With that in mind, simply adding memory to your server will improve MySQL performance only if the operating system can make good use of it by caching even more disk blocks. If your database is 512 MB, and you already have 1 GB of memory, adding more memory probably won’t help.
On the other hand, if you run more than just MySQL on the server, adding memory may help. Maybe that Java application server you’ve been running is eating up a lot of the memory that could otherwise cache disk access. Keep in mind that Linux, like most modern operating systems, considers caching disk I/O an optional feature. It doesn’t reserve any memory for it. So when free memory is low, MySQL can really suffer because MyISAM tables expect the OS to do some read caching.
And of course, FreeBSD 4.x is not the best choice for MySQL anyway.
According to the script here, the database I am most concerned with is only 5 Mb in size. Perhaps there are some other steps I can take in the meantime.