-->From: Jeremy Zawodny [mailto:Jeremy@stripped]
-->Sent: Wednesday, October 15, 2003 12:16 PM
-->To: Dathan Vance Pattishall
-->Subject: Re: 4.0.15 skip-external-locking
-->On Tue, Oct 14, 2003 at 10:49:16AM -0700, Dathan Vance Pattishall
-->> I wrote a quick PERL script to give me the status of a table of a
-->> particular database or queries in general on a db server.
-->> 71 | 2958 | System lock | e115 | messages
-->> REPLACE INTO antispam (code) values
-->> For 71 seconds show full processlist is reporting that the database
-->> messages and table antispam has a system lock for 71 seconds YET
-->> skip-external-locking is on in my my.cnf file and the command
-->> variables" shows that skip external locking is on.
-->> | skip_external_locking | ON
-->What makes you think those are related?
System Lock is caused by an external lock as in the description of show
process list, yet skip external locks is set.
System lock The thread is waiting for getting to get a external system
lock for the table. If you are not using multiple mysqld servers that
are accessing the same tables, you can disable system locks with the
-->External locking is that which allows seprate MySQL servers to share
-->> What is going on?
-->Lock contention, I'd guess. But having only seen one particular
-->thread, it's hard to know.
-->What do the Table_locks_* values in SHOW STATUS look like?
I graph these with RRD, and find that table_locks_wait is huge compared
to data from a previous day, yet the query nor data has changed much for
-->> I also notice some long waiting for tables even though the table
-->> this status is not being flushed, altered etc.
-->Is the CPU busy? Or the disks?
Neither. This is what is troubling. I actually removed the largest query
off of this server which btw hits a different table. After this action
was performed the system was fine. I wonder if there is some sort of
thread starvation/deadlock problem in mySQL exposed during lock
- Dathan Vance Pattishall
- Sr. Programmer and mySQL DBA for FriendFinder Inc.