MySQL Forums
Forum List  »  MyISAM

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
9425
April 26, 2006 05:37AM
9938
April 27, 2006 04:36AM
4119
June 13, 2006 09:59PM
2992
June 14, 2006 10:22AM
3225
June 15, 2006 11:18AM
3112
July 14, 2006 03:52PM
2965
August 22, 2006 11:34AM


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.