Portal Home > Knowledgebase > Articles Database > Server slow down with 1.5 million page views per month


Server slow down with 1.5 million page views per month




Posted by mglanfield, 06-09-2011, 04:37 PM
I think I've read enough MySQL and server optimization articles that my head is going to explode... We have been having server issues, and I just can't figure out why. First off, here is what I know about my dedicated server: CPU: Xeon E3110 Dual Core, no hyperthreading Memory: 4 Gb OS: Linux CentOS We get about 1.5 million pageviews per month to our website, which of course is database driven. On this website there is a forum (phpBB), a web store (x-cart), and a content section (custom built). Bandwidth doesn't seem to be the issue. I believe it to be a MySQL problem as when one site is slow, other sites work fine on the same server (on different IP addresses, but mainly different databases). I've been looking at long MySQL queries, but they don't make sense (SELECT COUNT(*) FROM sample_table_name WHERE ID = 2000 shouldn't take long, but it shows at 10-40 seconds sometimes). Where on earth should I start to see what the problem is, or is it just my hardware? I apologize if I am too vague here. Feel free to tell me what information I need to include to get help. Thanks!

Posted by barbus, 06-09-2011, 05:04 PM
Try to use top and dstat. It might help to find the cause of the issue. Start with LA, memory, CPU IO wait.

Posted by m4rc3, 06-09-2011, 07:59 PM
Can you show us the content of your my.cnf file ? It seems to me that neither the mysql nor the database have been optimized.

Posted by vx|brian, 06-09-2011, 08:03 PM
Try typing this in your SSH: mysqladmin processlist Look if there's any queries that are using a lot of time, the more time they take, the more the server slows down. Something else to notice is that if you have a query that is updating something in the database that takes a while to execute, your visitors will all see the website as "stuck" because the database is locked and all further queries start waiting/stacking up, PHP processes stack up waiting for MYSQL and your load averages start going up. A very good way to maintain server stability is to make sure everything happens quickly and smoothly in order to keep smooth constant operation of everything together.

Posted by mglanfield, 06-09-2011, 08:58 PM
I've used top and didn't see anything strange. The SHOW processlist also doesn't show any hanging sql queries. However, we wrote a program to log any queries that took more than 1 second to run, and we're getting all sorts of those, but most don't make sense to be taking too long (simple SELECT COUNT(*) searching by a primary index), which means they are simply taking too long because they are locked out. I'll get you the contents of my.cnf in a moment, but there isn't much in it...

Posted by vx|brian, 06-09-2011, 09:08 PM
1 second queries are not that much of an issue. If you are using log slow queries, increase it to 5 or 10 seconds, that will reduce the amount of queries listed there and will help you take out the large CPU users.

Posted by mglanfield, 06-09-2011, 09:11 PM
Some of them are 40-60 seconds long, and some of those are the same simple queries. While there might be some sub-optimized queries, most on the list appear to be fine, but still take forever.

Posted by vx|brian, 06-09-2011, 09:17 PM
What is the hard drive setup on the server? Perhaps that is becoming the issue, try to trackdown how many rows were processed to find that result.

Posted by mglanfield, 06-09-2011, 09:18 PM
I'm not sure I understand what you mean by this. Try to trackdown how many rows were processed to find what result?

Posted by m4rc3, 06-09-2011, 10:49 PM
The problem could be that there are too many writes/read on the drive so mysql has to wait a lot before it can actually write/read to/from it. That's and IOwait problem and you can solve it by changing the mysql datadir to a less use hard drive or get a new one just for mysql. Now I didn't get to see your my.cnf but just in case you can try this out:

Posted by mglanfield, 06-10-2011, 09:09 AM
Here is what my.cnf looks like: This was recently updated as we had some MySQL problems (just last night actually). For example, the max_connections is not in there, which I need to put back (to at least 300). Thoughts?

Posted by lynxus, 06-10-2011, 09:26 AM
Id suggest two things. 1) Optimize the table ( # optimize table tablename; ) 2) Check your indexes.. If you have too many.. Thi scan slow it down.. Too few and it can slow it down. Make sure you have index on the columns that you use most in your WHERE clause. So in this case, make sure that ID is indexed. Note: Lots on indexes can slow down updates and inserts, However if its mosly selects then you should be fine. Id suggest a cronjob to run once a week to optimize the tables when your leased loaded.

Posted by swiftyCMS, 06-11-2011, 11:47 PM
Optimize your dbase. Don't assume that mysql is the problem. from bash do 'ps -aux |more' (will list all processes and show memory usage) Let us know.

Posted by mglanfield, 06-13-2011, 09:09 AM
Thanks for your help everybody. I hired a server management company, and they optimized the server by updating firewall settings, my.cnf, and several other things. As far as I can tell, it is running quite well now. I'll have to keep an eye on it during peak times, but it looks like for the most part my problem is solved. Thanks again!

Posted by Blair, 06-13-2011, 04:40 PM
You didn't say how big your database is, but key_buffer=256M looks way too small. I'm sure my.cnf optimization helped. However, you can do much better. 4GB is pretty small for a modern server, with the cheap price of RAM. If you want the best performance, convert your tables to innodb and add RAM equal to your database tables plus indexes (you'll need to replace search with something like Sphinx). I speak from experience, I have a server running a 1.5M page view forum. Recently converted from MyISAM to InnoDB and upgraded from 4GB to 32GB of RAM. Blazing fast now.



Was this answer helpful?

Add to Favourites Add to Favourites    Print this Article Print this Article

Also Read
Help on OpenVZ Setup (Views: 704)


Language: