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
-->> 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
--skip-external-locking option.

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

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