Server Overloading ... Optimization

Portal Home > Knowledgebase > Articles Database > Server Overloading ... Optimization

Posted by ihavenoidea, 12-03-2009, 04:27 PM
Hi, I run a VERY busy file host and recently its seen an increase in the number of visitors. My load is going up to 100.00 in WHM on my 30ghz Dual Xeon 8GB server. Now I had a look in the usage logs and see that 80% of the load is from mysql. I had a look at the mysql processes and can see that there are more than 100 processes.. some with a query time over 60+! I need some serious mysql optimization but im not sure how. Can someone give me a hand?
Posted by njoker555, 12-03-2009, 04:35 PM
you could use mysqltuner and see what it recommends for your my.cnf file. FOr something that heavy, you may want to hire an expert at MySQl that will be able to optimize mysql for you.
Posted by net, 12-03-2009, 05:58 PM
Moved > Hosting Security and Technology.
Posted by JulesR, 12-04-2009, 02:30 AM
At first guess, and perhaps the easiest cause to identify, is that it may be an indexing issue. Queries without indexes (especially on large tables) can take quite a long time to complete. Easily fixed by adding an index on the relevant fields in the database. Here's some reading for you: http://www.databasejournal.com/featu...nd-Indexes.htm
Posted by Mavus, 12-04-2009, 03:44 AM
Even with good database normalization, indexes still increase processing. However, if you lack a well designed 'normalized Data Store' then nothing can be done, until it is normalized properly. Normalization is a process of removing data redundancy by implementing normalization rules. There are five levels or degrees. Once your data base is well normalized, then you create indexes based upon anticipated querries. These querries can even be pre-done and stored for a very quick response to perhaps a great deal of use in some cases. No matter, the first two steps are critical, first normalization, second indexes. You might take a look at 'Building a Data Warehouse' by Vincent Rainardi
Posted by ksv2nash, 12-04-2009, 03:58 AM
Hello, have you do mysql optimization....?
Posted by neXeon, 12-04-2009, 04:08 AM
If you have the money, I'd recommend hiring a professional with experience in regards to MySQL databases/servers. These guys usually can narrow down the problem pretty quickly.
Posted by JulesR, 12-04-2009, 04:08 AM
Mavus: Whilst I agree normalization is an excellent practice to get into, it's clear that the load issues aren't caused poor normalization. Since the OP is experiencing loads of 300+ I'd actually suggest the normalization (if any is even needed) occurs after the load is decreased significantly. Indexing does indeed still carry processing time, but it can be dramatically different by comparison to a query without. A 60+ second query can easily be reduced to <1s with proper indexing.
Posted by Mavus, 12-04-2009, 04:13 AM
This relates to resolving fragmentation on the drive itself, which is a lesser consideration even if present so far as cpu usage goes, however it can free up harddrive space.
Posted by Mavus, 12-04-2009, 04:19 AM
Agreed. And if poor normalization is present it could take a great deal of work to amend it. I would try indexing first, but in the long run without good normalization it may be only a temporary fix. But we also do not know how large these data files are or how many fields each sports. It could be handy to study the querries as well. The idea I am pursuing is implementing cache tables where you set up a regular table in the database that stores the results of commonly called for queries. Last edited by Mavus; 12-04-2009 at 04:27 AM.
Posted by RelativeDesign-Jerret, 12-04-2009, 04:38 AM
Personally, I would start by looking for long running queries. It's not too uncommon to find a half dozen or so calls that are the root of 50% of your problem. http://www.electrictoolbox.com/show-...queries-mysql/. If you do find a handful you'll have to evaluate them to see why they're taking so long, you may need to add a couple indexes or simply re-write the offending SQL statements. It's also possible that you're running into locking issues depending on the table types being used, etc. These can be much harder to resolve but could explain the long running queries (not so much the CPU utilization though). One final thought is to look at db size verses memory consumption. I don't have any specific recommendations but if you're running everything on one box you may find that you simply don't have enough RAM left for your MySQL server and it's having to go to the disks too frequently. Good luck, Jerret

Add to Favourites Print this Article