Debugging Slow Scripts

What is the best way to find the part of your script that is running slowly

[inveni0]

I have a page and need to know how to find which part of my script is causing the page to load so slowly. I originally thought that it was a MySQL query that was causing the delay, but a regular SQL query runs quickly, so now I'm thinking that the problem is in my script.

How do most of you find these slow spots?

[rocknbil]

I have found memory_get_usage() [us3.php.net] and memory_get_peak_usage() [us3.php.net] helpful in checking your script's memory optimization.

Another way is to add a start time variable/array at the top of the script and drop markers throughout the script, noting time from start time and presenting a summary at script's end.

[inveni0]

I dropped some time markers that displayed the seconds, and the biggest hold up is during that select statement I was using earlier...so I guess that is the problem. Back to the drawing board!

[jeffatrackaid]

Look into PHP profiling applications. Use EXPLAIN on mysql statements to see how they are searching and if they are using the proper indexes. I have seen SQL queries not use the correct index. By forcing the correct index, you can sometimes prevent expensive row by row searches.

[inveni0]

When I use EXPLAIN, I get this:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE _images ALL NULL NULL NULL NULL 290 Using temporary; Using filesort
1 SIMPLE _ads eq_ref PRIMARY PRIMARY 4 hometow_fas._images.adId 1 Using where
1 SIMPLE _zipcodes ALL PRIMARY NULL NULL NULL 32236 Using where 

But I have no idea what that stuff means.

[coopster]

This breaks it down for you:
Optimizing Queries with

EXPLAIN

[dev.mysql.com]

[inveni0]

I still can't see how that explains why the query runs in 0.16 seconds through straight SQL, but takes 14.0 seconds to run through my PHP script. None of my other queries are like this.

[coopster]

Are you certain it is the query that is taking so long to run within the PHP script? Or is it something in the underlying PHP code that is causing your performance issue?

[inveni0]

I set up little time markers before and after the query. So, the time markers list the time immediately before and immediately after the query is performed via PHP. The difference between the markers is always about 14 seconds.

[coopster]

OK, so for certain, it is this one query itself, not your connection or any other query. Because if your connection through the PHP script is other than localhost you may discover a latency issue there.

the query runs in 0.16 seconds through straight SQL

What exactly do you mean by this statement, "straight SQL"?

[inveni0]

It does connect through another connection besides localhost, but so do all of my other queries.

By 'straight through SQL', I mean that there is no lag when I run the query through phpMyAdmin.

[coopster]

OK then, everything is running through the same connection, and through PHP scripts. What type of connection are you making?

[inveni0]

I'm not sure what to call it, but I use mysql_pconnect().

[coopster]

That's a persistent connection. Try mysql_connect instead.

[inveni0]

Hmmm, I tried that. Still no improvement. I'm banging my head against the wall here.

[PSWorx]

How much data is in this particular table, i found with a user tracking system i setup that when it became over populated with entries that the home page to the cms slowed in loading as it took a few seconds to pull the user tracked data for display.

[ag_47]

If you are using mysql_pconnect() in all your scripts, then there's probably a huge buildup of 'Sleeping' connections from previous sessions. I've found that mysql_pconnect() usually generates a new connection (instead of using the old one) when different IPs/Ports call a script.

You should consider flushing all the connections and using mysql_connect() in all scripts.

[coopster]

I've found that mysql_pconnect() usually generates a new connection (instead of using the old one) when different IPs/Ports call a script.

Yep, that's the nature of HTTP, it is a stateless protocol.

[webmasterworld.com...]
[webmasterworld.com...]
[webmasterworld.com...]