David Taveras wrote:
Hello,
We have a BSD box with the following installed:
mysql-client-5.0.77 multithreaded SQL database (client)
mysql-server-5.0.77 multithreaded SQL database (server)
p5-DBD-mysql-4.010 MySQL drivers for the Perl DBI
php5-mysql-5.2.8 mysql database access extensions for php5
We are experiencing intermittent slowdowns on the queries made with PHP to
mysql to the point where pages take a lot of time to load, upon further
investigation with mytop we observe that it only keep an average of 1-2
simultenaous threads and a query time of avg 2-3 seconds.
During which the mysqld process reaches 99% continously for minutes.
We have repaired and optimized the tables, and the DB is 200mb. The storage
engine is MyISAM.
I understand that further optimization can be done to my.cnf , that has been
done a lot but with the same results.. andbefore I go to that path again my
question is:
Iam wondering what other tools exist to load test the mysql daemon, or how
to better debug this situation... more tools must exist out there? Perhaps
there must be a PHP/DB that I can load... and run a stress test like you
would test network issues with speedtest.net just a thought.. I know you
dont compare apples to oranges.
MySQL databases can be slow for any number of reasons. When I need to
attack a performance problem I look for bottlenecks in 4 physical
components of the server: CPU, RAM, DISK, NETWORK. If any of those
places are overloaded, I then examine the configurable components of
MySQL to see how I can reduce/eliminate that overload. Here are the big
things I look at:
* Table design - Is the data being stored efficiently? Is it properly
indexed?
* Query design - Is this query written efficiently? Can it use any
indexes that are already on the tables? Does it retrieve only the rows
and columns that the user actually needs or is it moving a lot of extra
data for no good reason? Does it use "batch-oriented" logic and not
"procedural SQL"?
* Server configuration - Is the server configured to remain within the
RAM limits of the machine? Are the buffers allocated appropriately for
the usage patterns of the storage engines? Can we reduce any hardware
contention through setting changes?
* Usage patterns - This is not really a server configuration but it *is*
the leading cause of slow performance. There are usually many ways to
write the same query or to perform the same action. The MySQL server
will do exactly what you tell it to do, even if that means performing
billions or trillions of comparisons to answer a single query. One very
slow (or very greedy) query has the ability to interfere with every
other fast query on the machine at that time giving them all the
appearance of being slow. Concentrate on the worst offenders, because
your problem may be in your USER not the machine.
So, you already know that your CPU tops out (99%) when you issue a
certain query. This means your query is either doing a lot of
computation or a lot of memory manipulation. Look at your query and
think to yourself, "How would I answer that query if I were the server".
Use the EXPLAIN command to show you which indexes, if any, will be used
for the query and how many rows it is pulling from each of the source
tables. Multiply those row numbers together to get an estimate of how
many comparisions the query is trying to compute for you.
And finally, know the MySQL manual. It is your best source of
information when it comes to understanding or interpreting the
information you can collect. I think a nice place to start will be here:
http://dev.mysql.com/doc/refman/5.0/en/optimization.html
Warmest regards,
--
Shawn Green, MySQL Senior Support Engineer
Sun Microsystems, Inc.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
