I have a server which was originally a low spec virtual machine with 128MB RAM, but which is now running on 4-times that RAM (shortly to be doubled again) and a faster "CPU". Available bandwidth has also been doubled.
With the increase I'm disappointed not to be seeing much difference in the performance of websites on the server. Many of them are heavily database dependent with lots of queries, so the real fix is to optimise the code itself, but in the short term the CPU and memory hike needs to be showing some kind of benefit.
The behaviour of the server is to work fine then suddenly run like a dog for a while, then come back up.
How do I go about finding what the bottleneck is? ("top" showed both before the upgrade and since that no swap is being used and CPU usage is never particularly high, with Apache sometimes hitting ~20% but mostly not showing.
In case I have some kind of virus/trojan/etc lurking, advice as to what to look for there also appreciated.
Hi,
2009/3/9 Mark Rogers mark@quarella.co.uk:
With the increase I'm disappointed not to be seeing much difference in the performance of websites on the server. Many of them are heavily database dependent with lots of queries, so the real fix is to optimise the code itself, but in the short term the CPU and memory hike needs to be showing some kind of benefit.
Install Gentoo? ;)
The behaviour of the server is to work fine then suddenly run like a dog for a while, then come back up.
For how long does it run slowly? What distro/kernel version?
Is it disk IO related? (just wondering if you're affected by that kernel IO bug in modern kernels...)
Srdjan
Hi,
2009/3/9 Steve Fosdick lists@pelvoux.nildram.co.uk:
On Mon, 2009-03-09 at 17:35 +0000, Srdjan Todorovic wrote:
Is it disk IO related? (just wondering if you're affected by that kernel IO bug in modern kernels...)
Which one is that, please?
http://it.slashdot.org/article.pl?sid=09/01/15/049201
There should be some LKML threads about it too, and it might've been covered on LWN.
Srdjan
On Mon, 2009-03-09 at 17:21 +0000, Mark Rogers wrote:
I have a server which was originally a low spec virtual machine with 128MB RAM, but which is now running on 4-times that RAM (shortly to be doubled again) and a faster "CPU". Available bandwidth has also been doubled.
With the increase I'm disappointed not to be seeing much difference in the performance of websites on the server. Many of them are heavily database dependent with lots of queries, so the real fix is to optimise the code itself, but in the short term the CPU and memory hike needs to be showing some kind of benefit.
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.
Tuning the SQL is probably useful and I'd start by timing each query so as to tune the slowest, frequently used query first.
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?
The behaviour of the server is to work fine then suddenly run like a dog for a while, then come back up.
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.
How do I go about finding what the bottleneck is? ("top" showed both before the upgrade and since that no swap is being used and CPU usage is never particularly high, with Apache sometimes hitting ~20% but mostly not showing.
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....
Regards, Steve.
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/
On Mon, Mar 09, 2009 at 05:21:13PM +0000, Mark Rogers wrote:
I have a server which was originally a low spec virtual machine with
^^^^^^^
In case I have some kind of virus/trojan/etc lurking, advice as to what to look for there also appreciated.
Not knowing what the hardware above is doing could be a clue, what's the virtualisation method? and is the box itself being hammered by other virtual machines? That would effectively make them similar to a virus or trojan in some respects ;)
Adam
On Mon, 2009-03-09 at 22:17 +0000, Adam Bower wrote:
Not knowing what the hardware above is doing could be a clue, what's the virtualisation method? and is the box itself being hammered by other virtual machines? That would effectively make them similar to a virus or trojan in some respects ;)
Adam
Also if you are still running virtual and you are using a virtual disk image rather than a dedicated partition (I'd hope not if you care about the performance) then look into using the noop scheduler rather than the default CFQ one as the optimisation of the latter doesn't make sense when you are running from an image.
If you are running on a dedicated volume then what is it running on..RAID 5 will murder write speeds on MySQL
Also if they are not important to you the you should consider mounting volumes with noatime or if they are important but you don't want the same level of write overhead on reads and you have a kernel >= to 2.6.20 then relatime.
Either of those optimisations can result in a 20% or so performance boost in specific circumstances and more in special cases.
Consider if you need the binary log, if not and your access is write heavy then disable it. (although in reality the performance cost doesn't seem to be that heavy and it can come in handy)
-
Adam Bower -
Mark Rogers -
Srdjan Todorovic -
Steve Fosdick -
Wayne Stallwood