0

We have a setup of a few cloud servers that connect to a MySQL cluster on dedicated servers over Rackspace's Rack Connect network. We have noticed recently that the time for database queries (via PHP) have slowed down and as part of investigating we wanted to figure out whether it was an increase in the amount of time to process the query and generate results, or is it the time to transfer the data from the servers to database cluster that's increased?

Is there a way for me to figure this out? So far our timing is just a total overall for each query.

1

Not really. But...

Use PHP's microtime(true) to give you precise times. Then time trivial things such as SELECT 1. This should give you a feel for the "network roundtrip time". That is effectively added (once) to each SELECT that you do.

As for queries getting slower... Look at whether the tables are getting bigger. Look at the EXPLAIN plans to see what counts it estimates. Look at the algorithms to see if they are O(1), O(N), or O(N*N), etc.

By "cluster" do you mean "NDB cluster"? Or "Galera Cluster"? Or something else?

Another tool is to do this:

FLUSH STATUS;
SELECT ...
SHOW SESSION STATUS LIKE 'Handler%';

This gives a precise count of the number of rows (index or data) that need to be read/written to perform the SELECT. Track that over time to see if it is growing due to data size growth (as opposed to something else making things sluggish).

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.