Skip navigation links

MySQL Forums :: MyISAM :: Problems with system locking


Advanced Search

Problems with system locking
Posted by: Brian O'Donnell ()
Date: April 26, 2006 05:37AM

Hi all,

We are currently running into fairly regular problems (although not reproducible enough to provide a test case hence the forum rather than a bug report!) with our MySQL 4.1.10 installation.

We only have one mysqld running and as I understand it, this means system locking should never be seen, yet we regularly see a state of 'system locked' in the output from SHOW PROCESSLIST and when several clients are in this state, the CPU usage sometimes goes through the roof and everything [always] slows down.

The application sitting on top of the MySQL installation relies on being able to insert records at a reasonable rate (currently around 800 rows per second, give or take) and these locks cause data loss as the application can only buffer so many in memory before having to drop them.

The app runs 24x7.

We have 4 instances of the application running, providing the 800 rows between them (not 800 each). Each is connected via a JDBC driver (the official MySQL one though probably not the latest and greatest).

There are no 'holes' in the database as data is INSERTed and SELECTed only.

The SELECTs are quite substantial - its quicker to read data in a sequential manner (in the order its written) than it is to ask MySQL to scan round the disk looking for the relevant records (pulling back around 2.5 million rows per query). The query is quick [typically sub-second], retrieving the data is the problem, but thats another story and probably unrelated to the locking issue.

A bit about our installation....

Dedicated box, 3.8 GHz Intel Xeon 2 CPI with hyperthreading.
4Gb memory.
External SCSI RAID 2+ terrabytes.

Whilst it may not be significant the database contains 2 main tables, one of which appears to get a .MYD file in the region of 140-180Gb and a .MYI file in the region of 30-40Gb when these problems become noticeable.

At this point we have to manually move these files, creating a new database for the applications to use as we cannot afford to let the system locks cause a loss of data.

At the same time we're seeing occasional error 127 errors but it seems (from searching through the forums, google, and the bug database) that this is something of a fact of life with MyISAM? The stock response seems to be 'repair table' but try doing this on a live server of this size and you're there for hours, at which point it gets a system lock or something and then you're losing data.

So, anyone out there got any ideas whats causing the system locks? We have --skip-locking configured (and are trying --skip-external-locking now although I can't believe you'd rename it and not support the old version) and anyway its supposed to be disabled by default isn't it? We definitely only have one MySQL instance running.

Any suggestions would be welcome. I have put the [relevant] contents of our my.cnf file below for reference.


Cheers
Brian
#####

# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock


# The MySQL server
[mysqld]

skip-innodb
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 8M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

Options: ReplyQuote


Subject Views Written By Posted
Problems with system locking 7123 Brian O'Donnell 04/26/2006 05:37AM
Re: Problems with system locking 8361 Ingo Strüwing 04/27/2006 04:36AM
Re: Problems with system locking 3015 Brian O'Donnell 04/27/2006 07:41AM
Re: Problems with system locking 2551 Ingo Strüwing 05/02/2006 06:13AM
Re: Problems with system locking 2453 Brian O'Donnell 05/02/2006 07:49AM
Re: Problems with system locking 2081 Ingo Strüwing 05/02/2006 10:46AM
Re: Problems with system locking 1915 Brian O'Donnell 05/12/2006 04:10AM
Re: Problems with system locking 2050 Ingo Strüwing 05/15/2006 02:57AM
Re: Problems with system locking 2106 Brian O'Donnell 04/28/2006 03:23AM
Re: Problems with system locking 1914 Ingo Strüwing 05/02/2006 06:27AM
Re: Problems with system locking 2978 Ananth Reddy 06/13/2006 09:59PM
Re: Problems with system locking 1951 Ingo Strüwing 06/14/2006 08:04AM
Re: Problems with system locking 1997 Ananth Reddy 06/14/2006 10:22AM
Re: Problems with system locking 1972 Ingo Strüwing 06/15/2006 09:26AM
Re: Problems with system locking 2261 Ananth Reddy 06/15/2006 11:18AM
Re: Problems with system locking 2123 Ingo Strüwing 06/16/2006 03:18AM
Re: Problems with system locking 2010 Ananth Reddy 07/14/2006 03:52PM
Re: Problems with system locking 2013 Ingo Strüwing 07/31/2006 04:16AM
Re: Problems with system locking 2149 Ingo Strüwing 08/22/2006 11:34AM
Re: Problems with system locking 2145 Carsten Kosthorst 07/19/2006 04:07AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.