Hi,
unfortunately I can't set up a scenario that easily reproduces the problem. The
last lockups happened on saturday and today (monday). What I can confirm is:
1. It doesn't seem to be a slave related problem. During the last two lockups I
checked the master as well as the slave statuses, all were in sync. I then
issued 'slave stop' on both slaves, killed the master (TERM), restarted it and
then issued 'slave start' on both slaves then continued syncing just fine.
2. The lockup always happens when there's a concurrent insert and a query.
Today I just looked at some performance statiststics (a web page with 6
generated performance graphics) when the lockup occured. 5 of the 6 graphics
were complete, the last one didn't complete and the database lockup was back
again.
What I could do would be to send you the database schema and some of the php
report scripts.
Fortunately I managed today to react fast to retrieve the process list
(attached). Looking at it, could it be that the problem occurs when a query is
processed on a table and more than one insert to the same table is pending?
The termination of id 584 didn't help. All further inserts were stuck in state
'System lock' (all but 1) or 'Opening table' (1, this was the successor of id 2
which I killed to test what would happen after a reconnect) and all further
queries were stuck in state 'Opening tables'.
As a remainder: I already tried mandatory write locks for the inserters and
mandatory read locks for the queriers which didn't help.
I didn't check, if more than one database is affected by the lockup.
On 04-Feb-2001 Sinisa Milivojevic wrote:
> Peter Zaitsev writes:
> > Hello Andreas,
> >
> > Thursday, February 01, 2001, 7:42:31 PM, you wrote:
> >
> >
> > I must confirm the problem with table locks. Mysql realy may deadlock
> > sometimes, and the funny thing is the solution to this case is to kill
> > the oldest locked thread waiting this condition - afterwards
> > everything resolves. So this may mean something like broadcast is lost
> > sometimes (?)
> >
> >
>
>
> Hi!
>
> I guess we may sound to be obnoxious, but can you make a repeatable
> case out of it ??
>
> d
> Regards,
>
> Sinisa
>
> ____ __ _____ _____ ___ == MySQL AB
> /*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
> /*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
> /*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaka, Cyprus
> /*/ /*/ /*/\*\_/*/ \*\_/*/ |*|____
> ^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^
> /*/ \*\ Developers Team
>
Andreas Steinmetz
D.O.M. Datenverarbeitung GmbH
+-----+-----------+-----------+--------+-------------+--------+-----------------
---+----------------------------------------------------------------------------
--------------------------+
| Id | User | Host | db | Command | Time | State
| Info
|
+-----+-----------+-----------+--------+-------------+--------+-----------------
---+----------------------------------------------------------------------------
--------------------------+
| 1 | logdaemon | localhost | syslog | Sleep | 17 |
| NULL
|
| 2 | logdaemon | 10.1.1.3 | syslog | Query | 54 | System lock
| INSERT INTO pcp (time,host,cpu_user,cpu_nice,cpu_sys,cpu_idle,mem_used,mem_
free,mem_shared,mem_cache |
| 3 | logdaemon | 10.1.1.6 | syslog | Query | 43 | Opening table
| INSERT INTO pcp (time,host,cpu_user,cpu_nice,cpu_sys,cpu_idle,mem_used,mem_
free,mem_shared,mem_cache |
| 4 | logdaemon | 10.1.1.4 | syslog | Sleep | 16 |
| NULL
|
| 5 | logdaemon | 10.1.1.7 | syslog | Sleep | 17 |
| NULL
|
| 7 | logdaemon | 10.1.1.2 | syslog | Sleep | 17 |
| NULL
|
| 14 | replicate | 10.1.1.2 | NULL | Binlog Dump | 176368 | Waiting for upda
te | NULL
|
| 15 | replicate | 10.1.1.6 | NULL | Binlog Dump | 176358 | Waiting for upda
te | NULL
|
| 519 | dbmaster | 10.1.1.4 | syslog | Query | 0 | NULL
| show processlist
|
| 584 | logreader | 10.1.1.4 | syslog | Query | 54 | Sending data
| select max(swap_used+swap_free) from pcp where host='castor' and time>='200
10204230000' and time<'20 |
+-----+-----------+-----------+--------+-------------+--------+-----------------
---+----------------------------------------------------------------------------
--------------------------+
---------------------------------------------------------------------
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
