| Posted by ignitionservers, 09-28-2009, 05:19 PM | | Hi, I have this really strange problem with a certain 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 particular 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:
Anything look wrong in there ?
Here is my apache config:
Please help. I really don't know how to track down this issue
|
| Posted by foobic, 09-28-2009, 06:31 PM | | 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.
|
| Posted by ignitionservers, 09-28-2009, 07:10 PM | | 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..
|
| Posted by CodyRo, 09-28-2009, 07:11 PM | | 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.
|
| Posted by foobic, 09-28-2009, 07:43 PM | | Definitely worth checking.
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.
|
| Posted by ignitionservers, 09-29-2009, 04:24 AM | | 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 certain 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.
|
| Posted by ignitionservers, 09-29-2009, 06:30 AM | | 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..
|
| Posted by CodyRo, 09-29-2009, 10:13 AM | | Let us how that goes !
|
| Posted by Crashus, 09-29-2009, 07:02 PM | | Hmmmm, try to turn KeepAlive on for testing purpose.
|
| Posted by ignitionservers, 10-02-2009, 01:36 PM | | 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 ?
|
| Posted by RBBOT, 10-02-2009, 03:04 PM | | 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.
|
| Posted by ignitionservers, 10-10-2009, 02:46 AM | | 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 ?
|
| Posted by ignitionservers, 10-16-2009, 06:18 PM | | 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 certain 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. : )
|
|
 Add to Favourites
 Print this Article
|
