Welcome to WebmasterWorld Guest from 54.163.168.15

Forum Moderators: coopster & jatar k

Debugging Slow Scripts

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

   
5:01 pm on Mar 4, 2009 (gmt 0)

5+ Year Member



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?

7:10 pm on Mar 4, 2009 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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.

7:42 pm on Mar 4, 2009 (gmt 0)

5+ Year Member



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!
7:52 pm on Mar 4, 2009 (gmt 0)

5+ Year Member



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.
8:18 pm on Mar 17, 2009 (gmt 0)

5+ Year Member



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.

9:50 am on Mar 18, 2009 (gmt 0)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member



This breaks it down for you:
Optimizing Queries with
EXPLAIN
[dev.mysql.com]
1:39 pm on Mar 18, 2009 (gmt 0)

5+ Year Member



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.
1:43 pm on Mar 18, 2009 (gmt 0)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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?
1:55 pm on Mar 18, 2009 (gmt 0)

5+ Year Member



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.
3:36 pm on Mar 18, 2009 (gmt 0)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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"?

3:44 pm on Mar 18, 2009 (gmt 0)

5+ Year Member



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.

3:55 pm on Mar 18, 2009 (gmt 0)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member



OK then, everything is running through the same connection, and through PHP scripts. What type of connection are you making?
4:07 pm on Mar 18, 2009 (gmt 0)

5+ Year Member



I'm not sure what to call it, but I use mysql_pconnect().
4:56 pm on Mar 18, 2009 (gmt 0)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member



That's a persistent connection. Try mysql_connect instead.
6:02 pm on Mar 18, 2009 (gmt 0)

5+ Year Member



Hmmm, I tried that. Still no improvement. I'm banging my head against the wall here.
12:40 am on Mar 23, 2009 (gmt 0)

10+ Year Member



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.
5:09 am on Mar 23, 2009 (gmt 0)

5+ Year Member



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.

11:19 am on Mar 23, 2009 (gmt 0)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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...]

 

Featured Threads

My Threads

Hot Threads This Week

Hot Threads This Month