Results 1 to 25 of 66
Thread: Causes of High Server Load
Feb 17, 2005, 11:19 #22
- Join Date
- Sep 2003
- Los Angeles
- 1 Post(s)
- 0 Thread(s)
quick way to check mysql query quality
Probably the quickest way to see if your MySQL queries are fairly well put together is to use the following two tools from within MySQL. I think with Cpanel's implementation of phpMyAdmin you can only use one though (depends on your web hosting company). Direct MySQL server access is best.
With the speed of your queries this may not be too important but really worth checking anyway.
From the MySQL prompt (or from in the SQL window in phpMyAdmin)
mysql>show full processlist;
Look out for "Copying to tmp table" in any of your processes. A query that has to create temporary tables can almost always benefit from some attention. Look out for any queries that use a join without properly using a where statement on the first table (and each table as it is joined in, especially tables with a lot of data). Any "sort by" or "distinct" can be bad news as well.
The second is just a general indicator that indexing may help - kind of tough on a shared server
Look for "Handler_read_rnd_next" - compare it with the other "Handler_read_*" - if "Handler_read_rnd_next" is really large, look into your indexing and your where statements. If you have a suspect page, you can try checking the number for "Handler_read_rnd_next" then running your page a bunch of times and checking again right away.
There can definitely be a lot of things that will run up the load and it can be very difficult to troubleshoot without being able to use command line tools. You might be headed for a good VPS or dedicated server with your needs.
Well, hope that helps!--- Have a good day! ---
Development and Administration Group
InMotion Hosting, Inc.