List:General Discussion« Previous MessageNext Message »
From:Dathan Vance Pattishall Date:October 15 2003 8:25pm
Subject:RE: 4.0.15 skip-external-locking
View as plain text  


-->-----Original Message-----
-->From: Jeremy Zawodny [mailto:Jeremy@stripped]
-->Sent: Wednesday, October 15, 2003 12:16 PM
-->To: Dathan Vance Pattishall
-->Cc: mysql@stripped
-->Subject: Re: 4.0.15 skip-external-locking
-->
-->On Tue, Oct 14, 2003 at 10:49:16AM -0700, Dathan Vance Pattishall
wrote:
-->> 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
('521.25624894_50628.1066152283')
-->>
-->> 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
"show
-->> 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.

http://www.mysql.com/doc/en/SHOW_PROCESSLIST.html
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
--skip-external-locking option.


-->
-->External locking is that which allows seprate MySQL servers to share
a
-->data directory.
-->
-->> 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
this server.

-->
-->> I also notice some long waiting for tables even though the table
with
-->> 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
contention.


- Dathan Vance Pattishall
  - Sr. Programmer and mySQL DBA for FriendFinder Inc.
  - http://friendfinder.com/go/p40688



Thread
4.0.15 skip-external-lockingDathan Vance Pattishall14 Oct
  • Re: 4.0.15 skip-external-lockingJeremy Zawodny15 Oct
    • RE: 4.0.15 skip-external-lockingDathan Vance Pattishall15 Oct