RE: 4.0.15 skip-external-locking
-->-Original Message-
-->From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]
-->Sent: Wednesday, October 15, 2003 12:16 PM
-->To: Dathan Vance Pattishall
-->Cc: [EMAIL PROTECTED]
-->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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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?
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.
> This same behavior has not been observed in 3.23.57. Unfortunately
> it's not easy to downgrade a mysql server as I have it setup in this
> environment. So, maybe this can be a fix forward with some simple
> setting.
What do the Table_locks_* values in SHOW STATUS look like?
> 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?
Jeremy
--
Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo!
<[EMAIL PROTECTED]> | http://jeremy.zawodny.com/
MySQL 4.0.15-Yahoo-SMP: up 31 days, processed 1,184,112,694 queries (431/sec. avg)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4.0.15 skip-external-locking
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 is going on? This same behavior has not been observed in 3.23.57.
Unfortunately it's not easy to downgrade a mysql server as I have it
setup in this environment. So, maybe this can be a fix forward with some
simple setting.
I also notice some long waiting for tables even though the table with
this status is not being flushed, altered etc.
- Dathan Vance Pattishall
- Sr. Programmer and mySQL DBA for FriendFinder Inc.
- http://friendfinder.com/go/p40688
