We’re experiencing problems with the performance of our website/MySQL server. There are times when MySQL Server uses a lot of CPU, and the site is very slow, or will not load at all.
Usually this seems to resolve itself after a while, but sometimes it takes a long time, up until a couple of hours. Sometimes it helps to restart MySQL Server, sometimes not.
It seems like MySQL Server ‘clogs up’ over time, until eventually it needs to be restarted to properly function again.
We are running a news site with about 20,000 pageviews a day. Both MySQL and the IIS are running on the same machine. Sometimes we also use the server for sending out our newsletter (about 9,000 subscribers)
Intel Xeon 2.8 GHz processor
2 GB RAM
The content of the site consists mostly of news articles, banners and photo reports.
The queries performed mostly are SELECT queries, but we also use tracking software (MetaTraffic), which writes at least one (and sometimes several) entries in the database for every pageview. This is probably a heavy load for the server, but I have done some research and no other users seem to be having performance problems because of MetaTraffic, on the contrary, it is complimented for it’s efficiency.
For most tables we use the InnoDB storage engine, except for MetaTraffic and the photoalbum tables I use MyISAM, because the software created these tables in MyISAM by default (even though InnoDB is the default storage engine on my server).
So far I’ve tried my best to optimize the server variables, the queries and the ASP code.
Here are some of the server vars:
max_allowed_packet = 1M
sort_buffer_size = 1M
query_cache_size = 2M
query_cache_limit = 1M
query-cache-type = 2
table_cache = 80
key_buffer_size = 100M
Those are the most relevant when it comes to performance I think, or am I forgetting something?
So any help on this would be greatly appreciated. I could probably optimize the queries / code a bit more but I doubt that will be a huge improvement. I was also told that the problem may be on OS level: MySQL Server and IIS might get in the way of each other, so one of them would start using virtual memory. I would have to check where in the OS the I/O takes place, and this would show the bottlenecks.
But I don’t know all that much about IIS and Windows Servers, so I have no idea to resolve that. Anyone who can confirm this is probably the problem and can point me in the right direction of resolving it?
Or could there be other causes?
Tomorrow I’ll post the status vars during peak time.