Andreas Steinmetz writes:
> 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 |
> +-----+-----------+-----------+--------+-------------+--------+-----------------
> ---+----------------------------------------------------------------------------
> --------------------------+
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
---------------------------------------------------------------------
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
