Monitoring mysql querytime

ellegaard

Hi everybody

I am currently load-testing some sites running on Linux/Apache 1.3/PHP/Mysql .
I wish to find out how much of the loadtime is due to the database queries, without changing the PHP code. ( To measure microtime() before/after or the like).

I can see al the requests on the Apache server at a given time using the server-status functionality. That reveals which requests are taking a long time, but not why.

I would be gratefull for any suggestions.

If anyone knows of some loadtools for linux with monitoring capabilities that would be ideal.

Regards Jens Ellegaard

[deleted]

The best way to measure how long your queries took is the one way you don't want to do (use microtime).

Why your queries are slow depends first of all what speed you think you should be able to achieve.
If you are joining 5 tables with 500k records each, you should not expect the query to finish inside 0.02 seconds (unless you have a big server)

Also, MySQL is not allways good at locking a table.
So if you happend to run one UPDATE query or the like, all the other queries that use that table are put on hold.

If you search for mysql and optimize in the forum you'll find enough mysql optimizer tips to keep you busy for a while.

[deleted]

Thanks for the reply, Vincent 🙂

My problem is unfortunately not solved though.

I am not looking for clues as to WHY the mysql queries might take a long time, but for ways to find out if long loadtimes are due to the db queries or the PHP script-execution.

Since I may have to test this without being able to alter the PHP code,(it may be somebody elses sites I am testing), I was wondering if there is either some sort of load tool with monitoring capabilities for linux systems, or some way of monitoring what is taking the majority of the time when apache handles a request.

I can see the individual requests processed by Apache, including the time they have been underway, using the server-status directive.
I can thus locate which requests that take a long time to load.

What I cant see is whether its mysql or the scipt-execution that takes a long time.

I would be happy, gratefull and more popular with ecerybody at work i somebody could give me a hint how to accomplish this 🙂

With regards
Jens Ellegaard

[deleted]

Well I'm affraid you are a bit stuck then.

You can't find out what slows the script down unless you let the script tell you what it has and how long it took.

[deleted]

I correct myself.
You could use "strace" on linux, or "truss" on solaris
to see what your php is up to. Theoretically
you should be able to see how long it takes to complete it's mysql tasks and the rest.