Hi,
well, the query is over a network. Single switch with two VLANs between the
systems, network speed is *200MBit/s*. The php code structure executing was:
... prepare query ...
mysql_connect();
mysql_query();
mysql_fetch_row();
mysql_close();
... process and send data to browser ...
The table the query executed on did have a total of about 25000 rows at that
time. The query executing had to return exacty one row (max()). When the query
thread did terminate all threads waiting for inserts did keep *hanging* either
in state 'System lock' or state 'Opening table'. Thus:
The whole database was *deadlocked* with only insert queries pending after a
a data retrieval query thread did execute. In this state a regular shutdown
of the database is *not* possible, you have to send SIGTERM to shut down
mysql. Oh, the same thing did happen *again* today.
The behaviour looks like in some cases mysql doesn't unlock after a data
retrieval query or that pending inserts don't get properly restarted.
As a hint the inserting daemons keep continously connected to mysql as long as
the database is up, there are automatic reconnect attempts in case the database
is not available.
To finally assert that replication isn't involved I'm running this now with the
slaves stopped.
If it happens again I will try to set up code that is able to reproduce the
problem on a test system (single database without replication there).
On 06-Feb-2001 Sinisa Milivojevic wrote:
> Hi!
>
> First of all, please do not send us entire schema and your PHP
> scripts, as they make sense on your site only.
>
> Second, the above processlist does not show any deadlock. There is a
> query that is sending data, on which INSERT and UPDATE queries are
> waiting for.
>
> Judging by the above output I would also recommend you to increase
> table_cache and speed up connections if possible. If the above
> "Sending data" is done over network, you could turn compression on or
> make faster network.
>
>
> Regards,
>
> Sinisa
>
> ____ __ _____ _____ ___ == MySQL AB
> /*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
> /*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
> /*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaka, Cyprus
> /*/ /*/ /*/\*\_/*/ \*\_/*/ |*|____
> ^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^
> /*/ \*\ Developers Team
>
Andreas Steinmetz
D.O.M. Datenverarbeitung GmbH
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
