More ways to debug mysql slowness..?

--001485f6da149aa0750471fd2caf
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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.

Thanks

David

--001485f6da149aa0750471fd2caf--

Re: More ways to debug mysql slowness..?

David,

At 03:28 PM 8/25/2009, 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.

That is extremely slow. Look at your slow query log to see which queries
are slow. You can post them here and maybe someone can help you to optimize
the query.
How much memory does the server have? How large are the tables?


>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.
>
>Thanks
>
>David


Take a look at MONyog from http://webyog.com/en/. It will monitor the
MySQL server. They have a trial download available.

Mike



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: More ways to debug mysql slowness..?

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=gcdmg-mysql-2@m.gmane.org