Home > Archive > PHP Language > March 2006 > Mysql & locked queries
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Mysql & locked queries
|
|
|
| Hi!
I have a huge problem and can't find any answers on the Internet - only
questions. I hope someone can at least give me some hints in the right
direction.
I know this might seem like MySQL question at first, but I suspect the PHP
is the culprit to all of this. There might be something that I don't
understand about how PHP communicates with MySQL that could help me solve
the issue.
I have a search engine that I built myself, and a whole application around
it. Today the search engine stopped working. It was not the first time that
this has happened, and I knew that restarting mysql & apache would make it
work again. But today I set to discover what was happening and I stumbled
across this command:
mysql> SHOW PROCESSLIST;
This command showed a lot (101) of connections, most of them in "Locked",
some in "statistics" state.
I saved the output and restarted MySQL and Apache, and this temporarily
solved the issue. However, I have no idea when this might happen again, so
I would like to find a cause.
Now, MySQL docs are not really helpful on the subject:
Locked
The query is locked by another query.
statistics
The server is calculating statistics to develop a query execution plan.
So what does 'Locked' mean? Why would a query be locked? I don't lock
anything explicitly, so I guess PHP does it for me? Or is MySQL locking the
colliding queries?
While we are at it, how do the multiple PHP threads run? How does PHP ensure
data integrity? I have read a lot of stuff on PHP but I haven't seen
anything on the subject. Any guidelines?
Any hints, suggestions, directions, solutions, ideas, anything... would be
very appreciated. I'm lost. :(
Have a nice day (or night)!
Anze
| |
|
| Anze wrote:
>Hi!
>
>I have a huge problem and can't find any answers on the Internet - only
>questions. I hope someone can at least give me some hints in the right
>direction.
>
>I know this might seem like MySQL question at first, but I suspect the PHP
>is the culprit to all of this. There might be something that I don't
>understand about how PHP communicates with MySQL that could help me solve
>the issue.
>
>I have a search engine that I built myself, and a whole application around
>it. Today the search engine stopped working. It was not the first time that
>this has happened, and I knew that restarting mysql & apache would make it
>work again. But today I set to discover what was happening and I stumbled
>across this command:
>mysql> SHOW PROCESSLIST;
>This command showed a lot (101) of connections, most of them in "Locked",
>some in "statistics" state.
>I saved the output and restarted MySQL and Apache, and this temporarily
>solved the issue. However, I have no idea when this might happen again, so
>I would like to find a cause.
>
>Now, MySQL docs are not really helpful on the subject:
> Locked
> The query is locked by another query.
> statistics
> The server is calculating statistics to develop a query execution plan.
>
>So what does 'Locked' mean? Why would a query be locked? I don't lock
>anything explicitly, so I guess PHP does it for me? Or is MySQL locking the
>colliding queries?
>
>While we are at it, how do the multiple PHP threads run? How does PHP ensure
>data integrity? I have read a lot of stuff on PHP but I haven't seen
>anything on the subject. Any guidelines?
>
>Any hints, suggestions, directions, solutions, ideas, anything... would be
>very appreciated. I'm lost. :(
>
>Have a nice day (or night)!
>
>Anze
>
>
This sounds like a sql issue - the database will lock conflicting
queries, most likely during the statistics phase. PHP doesn't ensure
data integrity, your database design would do that, and your application
development can either assist it or conflict with it. I would post this
in the MySQL group.
Tom
| |
|
| > This sounds like a sql issue - the database will lock conflicting
> queries, most likely during the statistics phase. PHP doesn't ensure
> data integrity, your database design would do that, and your application
> development can either assist it or conflict with it. I would post this
> in the MySQL group.
Thank you for the fast response - and I think you helped me solve the issue.
I'll have to dig into te sources and everything, but at least now I have a
theory to work on. I think I should have locked that tables before deleting
from them. There are some very complex select queries running and if some
user performed a search at the same time as data was changed it might have
caused a deadlock condition.
Thank you! I don't think I would have guessed alone. :)
I would still however like to hear about concurrency in PHP / MySQL:
I guess the scripts run concurrently? If some script takes 20s to run, the
other (faster) scripts probably run without problems?
My other guess would be that the queries are 'atomic'? That is, while a
query from PHP to MySQL is executing the other queries are waiting their
turn? Or are they executed concurrently?
Anze
| |
|
| Anze wrote:
>
>Thank you for the fast response - and I think you helped me solve the issue.
>I'll have to dig into te sources and everything, but at least now I have a
>theory to work on. I think I should have locked that tables before deleting
>from them. There are some very complex select queries running and if some
>user performed a search at the same time as data was changed it might have
>caused a deadlock condition.
>Thank you! I don't think I would have guessed alone. :)
>
>I would still however like to hear about concurrency in PHP / MySQL:
>I guess the scripts run concurrently? If some script takes 20s to run, the
>other (faster) scripts probably run without problems?
>My other guess would be that the queries are 'atomic'? That is, while a
>query from PHP to MySQL is executing the other queries are waiting their
>turn? Or are they executed concurrently?
>
>Anze
>
>
Again, execution sequence is in the database.
Tom
| |
|
| >>I would still however like to hear about concurrency in PHP / MySQL:
> Again, execution sequence is in the database.
The first question has nothing to do with DB, and the second one is about
how PHP calls MySQL - I know how mySQL processes requests, but I don't know
how PHP makes them.
Any ideas?
Anze
| |
| Colin McKinnon 2006-03-11, 6:56 pm |
| Anze wrote:
>
> Thank you for the fast response - and I think you helped me solve the
> issue. I'll have to dig into te sources and everything, but at least now I
> have a theory to work on. I think I should have locked that tables before
> deleting from them. There are some very complex select queries running and
> if some user performed a search at the same time as data was changed it
> might have caused a deadlock condition.
IME MySQL simply does not do parallel queries (I believe they are scheduled
for the 5.1 realease). Queries are processed on a first come / first served
basis. If you have a slow query everything else has to wait its turn.
The best way to resolve is:
1) enable slow query logging
2) optimize the schema with regard to the slow queries
3) if necessary monitor the server and kill off long running queries
(usually the first entry in the proceslist - see the mysqladmin command.
IIRC its all in the MySQL manual.
HTH
C.
| |
|
| > 1) enable slow query logging
> 2) optimize the schema with regard to the slow queries
> 3) if necessary monitor the server and kill off long running queries
> (usually the first entry in the proceslist - see the mysqladmin command.
Thank you for the tips! I didn't know about slow query logging, I'll try to
set it up.
Found the source of the problem however - someone took advantage of my
search engine and turned it into a slow monster that locked up the system.
It's my error completely, I should have checked the number of words. It
looks like a spam bot was trying to send e-mail through my search form and
consequently crashed the system because the SQL query for the 'search' was
so huge...
> IIRC its all in the MySQL manual.
RTFM always helps, it's just a question of 'which manual' and 'where in the
manual'... ;)
Thanks again!
Anze
|
|
|
|
|