Steve Fosdick wrote:
If increasing RAM and CPU power has made little difference the conclusion surely is that the performance is I/O limited. Where was the increase in bandwidth - between the server and the clients? If so perhaps the disk I/O bandwidth is the bottleneck.
The bit in the jigsaw that I forgot to mention: the server is a virtual server hosted by a third party; although I have root (well sudo) access via SSH to the VM I have no control over the host.
The bandwidth was previous an unmetered but throttled connection, it's now a metered unthrottled with burst capability twice what it had been before.
Tuning the SQL is probably useful and I'd start by timing each query so as to tune the slowest, frequently used query first.
The problem is that I don't have all that much control over the applications. Many of them (for example there's an osCommerce site) are written to make ridiculous amounts of queries per page. It seems to be mostly the number of queries, not the speed of each one, that's causing the problem, but that's just my gut feeling.
What I think are the relevant parameters in my.cnf are: key_buffer = 16M max_allowed_packet = 16M thread_stack = 128K query_cache_limit = 1048576 query_cache_size = 16777216 query_cache_type = 1
Any idea how I'd determine if these are good settings?
It may also be possible to tune the database. For example, now you have more RAM could you make it do hash joins (with the hash index in RAM) rather than external joins like sort/merge and indexed?
I'm afraid you lost me there.... Does MySQL even do hash joins?
In that case I'd note the times when the performance is poor and try to work out if there is regular job that runs at that time. If, for example, disk I/O is the bottleneck something that does some kind of disk scan (like updatedb - part of locate) could reduce performance for a while.
I'll certainly take a look, but whenever I've checked there's been no evidence of anything much happening at all!
It seems to me a 'top' for I/O may be useful so I googled it and found the following:
http://linuxpoison.blogspot.com/2008/07/iotop-io-performance-meter-like-top....
That looks good, thanks. (Site homepage is here: http://guichaz.free.fr/iotop/ )
When I went to install it I discovered that I am running quite an old version of Ubuntu (6.06!) and it doesn't look like iotop will run without some work, so I think I'm going to need to upgrade to 8.04 first.
The concept of iotop made me look further and I also then found mytop: http://jeremy.zawodny.com/mysql/mytop/