Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2005
    Posts
    77

    PHP Script takes REALLY long, But only sometimes ?!

    Hi, I have this really strange problem with a particular script. The problem is also that the problem is random and hard to replicate. There is this one script which handles most requests to one of my websites. Now, it accesses mysql and picks up stuff from pretty big database. The largest queries would return something like 7000 rows or so. [average 5 columns per row.. only text..]

    Anyway, the issue is not of load really because whenever this has happened the server load has always been within healthy limits. (0.4, 0.3, 0.16 and so on..). It's not an IO problem either.

    But SOMETIMES the script just takes really long to execute. Say I request a certain page.. my browser will be stuck with "Waiting for a reply..." and this could go on for like 5-10 minutes before the page finally decides to load. The funny part is that it appears that the script itself gets locked because if I try to open any other pages that rely on the same .php script, they won't load either. But ANY OTHER pages, regardless of whether they're static, or PHP load fine.

    I've also tried looking at Apache through Cpanel seeing the processes handling my requests.

    Say Request #1 is hung.

    If I open a new request I see in "Apache Status" that this request is being handled by a different child. Yet it hangs!!

    And what's more bizarre is that once the first page loads, all the others load as well. It's not an issue with my MaxClients setting because I have 460 child processes and the max is 500.

    Just now this happened so I quickly picked out the process from "Apache Status" in cpanel and ran this:

    Code:
    [email protected] [/home/masj]# pstack 24880
    #0  0x00000038324ca51f in poll () from /lib64/libc.so.6
    #1  0x00002ba8d12dd732 in apr_poll () from /usr/local/apache/lib/libapr-0.so.0
    #2  0x00002ba8d12ddcf9 in apr_wait_for_io_or_timeout ()
    #3  0x00002ba8d12d0a3b in apr_socket_sendv ()
    #4  0x00002ba8d12d0f52 in apr_sendv () from /usr/local/apache/lib/libapr-0.so.0
    #5  0x00000000004b03ac in writev_it_all ()
    #6  0x00000000004b2d8e in core_output_filter ()
    #7  0x00000000004a5b5d in ap_pass_brigade ()
    #8  0x0000000000434f7b in logio_out_filter ()
    #9  0x00000000004a5b5d in ap_pass_brigade ()
    #10 0x0000000000464b56 in chunk_filter ()
    #11 0x00000000004a5b5d in ap_pass_brigade ()
    #12 0x00000000004a93ad in ap_content_length_filter ()
    #13 0x00000000004a5b5d in ap_pass_brigade ()
    #14 0x00000000004a5d5e in ap_filter_flush ()
    #15 0x00002ba8d10a2dc9 in apr_brigade_write ()
    #16 0x00000000004a9861 in buffer_output ()
    #17 0x00000000004a9985 in ap_rwrite ()
    #18 0x00002ba8d1e2f026 in php_apache_sapi_ub_write ()
    #19 0x00002ba8d1d7c854 in php_ub_body_write_no_header ()
    #20 0x00002ba8d1daf1c7 in zend_print_zval_ex ()
    #21 0x00002ba8d1dd010b in ZEND_ECHO_SPEC_CONST_HANDLER ()
    #22 0x00002ba8d1dcce8c in execute () from /usr/local/apache/modules/libphp5.so
    #23 0x00002ba8d752b311 in zend_oe ()
    #24 0x00002ba8d1dcf7ff in ZEND_INCLUDE_OR_EVAL_SPEC_CONST_HANDLER ()
    #25 0x00002ba8d1dcce8c in execute () from /usr/local/apache/modules/libphp5.so
    #26 0x00002ba8d752b311 in zend_oe ()
    #27 0x00002ba8d1dadb1d in zend_execute_scripts ()
    #28 0x00002ba8d1d6badb in php_execute_script ()
    #29 0x00002ba8d1e2f9dd in php_handler ()
    #30 0x00000000004920f2 in ap_run_handler ()
    #31 0x00000000004929c1 in ap_invoke_handler ()
    #32 0x000000000046cc6e in ap_internal_redirect ()
    #33 0x00000000004880a0 in handler_redirect ()
    #34 0x00000000004920f2 in ap_run_handler ()
    #35 0x00000000004929c1 in ap_invoke_handler ()
    #36 0x000000000046c2bc in ap_process_request ()
    #37 0x0000000000464c3f in ap_process_http_connection ()
    #38 0x00000000004a22c4 in ap_run_process_connection ()
    #39 0x00000000004a2713 in ap_process_connection ()
    #40 0x0000000000490569 in child_main ()
    #41 0x0000000000490710 in make_child ()
    #42 0x00000000004909a3 in perform_idle_server_maintenance ()
    #43 0x0000000000490e7f in ap_mpm_run ()
    #44 0x00000000004999fa in main ()
    Anything look wrong in there ?

    Here is my apache config:
    Code:
    KeepAlive Off
    UseCanonicalName Off
    AccessFileName .htaccess
    DefaultType text/plain
    HostnameLookups Off
    ErrorLog logs/error_log
    
    <IfModule prefork.c>
        ServerLimit 500
        StartServers 5
        MinSpareServers 5
        MaxSpareServers 10
        MaxClients 500
        MaxRequestsPerChild 100
    </IfModule>
    Please help. I really don't know how to track down this issue

  2. #2
    Join Date
    Feb 2005
    Location
    Australia
    Posts
    5,842
    Just a guess, but perhaps your ultra-slow script is locking the database tables so that another instance of it is also blocked, while other scripts proceed normally. Then if you're caching the results somehow (MySQL query cache or otherwise), subsequent requests could be much faster.

    Try turning on the slow query log and see if that gives you more info.
    Chris

    "Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them." - Laurence J. Peter

  3. #3
    Join Date
    Jul 2005
    Posts
    77
    Thanks for replying foobic. I was kind of stupid with that so far because I DID have the slow query log turned on but I forgot to touch and chown the file. Since there was no file, I thought I didn't have any slow queries.

    Well, I've created it now so lets see if anything comes in. Though I do remember a while back some of these queries ended up as slow queries.

    Is it usual for a SELECT statement to lock a table though ? I'm asking because this script mostly runs large select statements and a few updates here and there. But most of them are select..

  4. #4
    Join Date
    Feb 2006
    Location
    Buffalo NY
    Posts
    1,348
    Quote Originally Posted by MasJ View Post
    Thanks for replying foobic. I was kind of stupid with that so far because I DID have the slow query log turned on but I forgot to touch and chown the file. Since there was no file, I thought I didn't have any slow queries.

    Well, I've created it now so lets see if anything comes in. Though I do remember a while back some of these queries ended up as slow queries.

    Is it usual for a SELECT statement to lock a table though ? I'm asking because this script mostly runs large select statements and a few updates here and there. But most of them are select..
    How large is the table(s) you're selecting from? Are you selecting columns that are indexed? Your issue could be simply that you're selecting a large amount of data in a not-so-optimized DB schema.
    Cody R.
    Hawk Host Inc. Proudly Serving websites since 2004.
    Let's Encrypt Sponsor.

  5. #5
    Join Date
    Feb 2005
    Location
    Australia
    Posts
    5,842
    Quote Originally Posted by CodyRo View Post
    How large is the table(s) you're selecting from? Are you selecting columns that are indexed? Your issue could be simply that you're selecting a large amount of data in a not-so-optimized DB schema.
    Definitely worth checking.

    Quote Originally Posted by MasJ View Post
    Is it usual for a SELECT statement to lock a table though ? I'm asking because this script mostly runs large select statements and a few updates here and there. But most of them are select..
    May be wrong but I don't think either selects or updates lock the tables automatically - your script may (should?) obtain the appropriate locks itself. Given a mixture of selects and updates it's even possible that the author decided to simply get a write lock on all affected tables at the start and release it on completion.
    Chris

    "Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them." - Laurence J. Peter

  6. #6
    Join Date
    Jul 2005
    Posts
    77
    Quote Originally Posted by CodyRo View Post
    How large is the table(s) you're selecting from? Are you selecting columns that are indexed? Your issue could be simply that you're selecting a large amount of data in a not-so-optimized DB schema.
    That's the thing, the table itself isn't really big. It's just 7.2MB in size.


    I checked the mysql slow queries log but it didn't really help. Mainly because there were no slow queries on this database. There were a bunch of them from a vbulletin installation but none from this particular script/database. *sigh*


    The columns being selected are NOT indexed. There's an id column though which is the primary key. But I haven't indexed them...

    Also, I wrote the script myself and though I'm mostly n00bish at PHP, I didn't incorporate any locking unless PHP does that automatically when running select statements, etc.

  7. #7
    Join Date
    Jul 2005
    Posts
    77
    I've created some indexes on the table now. Also optimized some query after using the EXPLAIN command on my selects. Lets see if this changes anything..

  8. #8
    Join Date
    Feb 2006
    Location
    Buffalo NY
    Posts
    1,348
    Quote Originally Posted by MasJ View Post
    I've created some indexes on the table now. Also optimized some query after using the EXPLAIN command on my selects. Lets see if this changes anything..
    Let us how that goes !
    Cody R.
    Hawk Host Inc. Proudly Serving websites since 2004.
    Let's Encrypt Sponsor.

  9. #9
    Join Date
    Apr 2009
    Posts
    839
    Hmmmm, try to turn KeepAlive on for testing purpose.

  10. #10
    Join Date
    Jul 2005
    Posts
    77
    An update on this. The indexes did help a bit with speed on the queries.

    However, I had the problem again just now. One thing, though puts all table locked theories to rest I think.

    While one browser window (Firefox) was trying to get a response from the script.. (it was waiting...).

    I opened the same URL in Safari and it worked fine. Firefox was still stuck, even AFTER the page was done loading in Safari. After that, when I tried browsing some other pages in Firefox, they were still getting stuck until about 5 mins. after when everything started working normally.

    All this while, everything was working fine in Safari.

    But this isn't a browser issue because I've faced the same problem in Firefox, Safari and IE.

    Also, the queries for this 'stuck' page didn't show up on the slow-query log. Infact, mysql isn't showing any queries from this script in the slow-query log. [slow-query time is set to 3]

    How exactly will KeepAlive help me diagnose the issue ?

    Any ideas ?

  11. #11
    Join Date
    Dec 2006
    Posts
    477
    What engine is your table using - myisam or innodb? For myisam the table is locked as a whole whenever a write operation is executed. For innodb only the row being written is locked and other threads can read from the rest of the table.

    If you had a background cron job that occasionally performed a long running write operation on a myisam tables it could cause all threads reading from that table to hang waiting for the write to complete.

    Try running "show process list" from mysql while its hung - that will tell you if your script is running a SQL statement at all or is hung elsewhere.

  12. #12
    Join Date
    Jul 2005
    Posts
    77
    SHOW PROCESSLIST showed me that a query had hung somewhere. The problem is that sometimes this query takes 250 seconds to execute and in usual cases it'll execute in <1s. Sometimes is also like one in a large number of queries.

    I ran a KILL ID and that helped me return things to normal.

    Is there any way I can mysql to implicitly kill long running queries such as this ? I mean, set a limit like 60s on a query or something ?

  13. #13
    Join Date
    Jul 2005
    Posts
    77
    **UPDATE**

    This has been solved (I think..).

    Well, here's how I kind of guessed the problem.

    I had an old news script which never used CAPTCHA's [on the comments system..], so because of all the spam, I implemented a CAPTCHA on it. Well, since I did that I had to introduce sessions into the script, for the generation of the random code.

    Well, once I did that, this particular script (comments.php) started hanging the same way as the other script! Random slow-downs now and then, where it takes ages with the browser stuck at "Waiting for reply..".

    Finally, I figured that all I had really added was session handling into this script so that must be a problem.

    A simple google search for "session_start php hang" revealed a HUGE NUMBER of results with folks talking about how their scripts would sometimes hang at session_start when using PHP's inbuilt session handler.

    So I used this guide:
    http://www.devshed.com/c/a/PHP/Stori...in-a-Database/

    And changed my sessions to a database system (it was based on flat-files..). I don't have THAT many simultaneous sessions but this problem was there regardless of load, so I guess it's a bug in PHP's session handling.


    As of now, all the slow-downs are GONE! I'm posting this up here for future reference. I hope it helps folks who have inexplicable random slowdowns in their scripts and aren't able to narrow down the problem.

    Thanks again for all the help. : )

Similar Threads

  1. How long will it takes ?
    By champka in forum Running a Web Hosting Business
    Replies: 12
    Last Post: 08-28-2005, 07:11 PM
  2. how long would whm update takes?
    By jolo2 in forum Hosting Software and Control Panels
    Replies: 2
    Last Post: 05-26-2004, 02:01 PM
  3. How long does it takes?
    By bullman in forum Domain Names
    Replies: 4
    Last Post: 02-15-2004, 10:40 AM
  4. Connecting through ftp takes too long
    By capricornnl in forum Dedicated Server
    Replies: 6
    Last Post: 03-05-2003, 04:10 PM
  5. how long it takes to set up an account?
    By LVT in forum Web Hosting
    Replies: 16
    Last Post: 10-26-2002, 02:59 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •