Redback Posted November 12, 2007 Share Posted November 12, 2007 I have been having an issue with some MYSQL queries running multiple times when the server is under a heavy load. Example, if I do an 'update Table1 set item1 = 0 where 1 = 1' This runs fine under normal conditions, but will repeat itself multiple times when the server is under load. Is this just an optimization or server resource issue. If so, I would assume the query would just timeout and not run at all instead of running a few times. Quote Link to comment Share on other sites More sharing options...
aschk Posted November 12, 2007 Share Posted November 12, 2007 I'm curious about your statement. You do realise that having "WHERE 1=1" will ALWAYS evaluate to true. Making ALL rows item1 column zero (0)? Quote Link to comment Share on other sites More sharing options...
Redback Posted November 12, 2007 Author Share Posted November 12, 2007 Yes, I just put that there for demonstration. My question isnt really about the query statement, but rather a reason behind update queries running multiple x's when the server is under heavy load. Quote Link to comment Share on other sites More sharing options...
aschk Posted November 12, 2007 Share Posted November 12, 2007 Is it coming up in the MySQL slow query log? Have you seen the running process happen multiple times? Is the PHP script processing this statement more than once? Is the table you're working with locked at the time? How do you know it's running more than once? What IS the actual SQL you're running? It might give us a more of an idea why it's running slowly or repeating. Quote Link to comment Share on other sites More sharing options...
Redback Posted November 12, 2007 Author Share Posted November 12, 2007 Yes, it is coming up on the MySQL slow query log. The php script has a select and update statement. The select statement must scan over 1/2 million records before the update is executed based on the select results. WHat is happening is that before this php script finishes it will start again so then I will get 2 updates. This only happens when the server is under heavy load. Under normal operating conditions this script runs fine. I know it is running more than once because I can view the 2 different update results when the cron was only run one. You mentioned you would like the SQL version so you could get some insight on why it is running slowly. This brings in my question. Can a slow query cause problems like this? If so, is there a variable in MYSQL that determines how long a query has to complete before it will attempt again? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.