MySQL Bugs: #56008: Inquiry lag

Bug #56008	Inquiry lag
Submitted:	16 Aug 2010 10:59	Modified:	16 Sep 2010 18:25
Reporter:	Alexandr Kosterin	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	mysql-server-5.5.4	OS:	FreeBSD (FreeBSD 7.2-RC1)
Assigned to:		CPU Architecture:	Any
Tags:	Inquiry lag conf

Description:
There was a problem with with base. Symptoms the following:

There is table MyISAM, the size 6GB, 9mln 500t. records. Table structure:
CODE:
CREATE TABLE `frases` (
`fid` int(11) NOT NULL AUTO_INCREMENT,
`key` varchar(32) NOT NULL,
`lema` varchar(512) NOT NULL,
`frase` varchar(512) NOT NULL,
`time` datetime NOT NULL,
`nf` varchar(512) DEFAULT NULL,
`show` int(11) DEFAULT '-1',
`site_list` text NOT NULL,
`direct` varchar(2048) DEFAULT NULL,
`site_cat` varchar(10000) DEFAULT NULL,
PRIMARY KEY (`fid`),
UNIQUE KEY `key` (`key`)
) ENGINE=MyISAM AUTO_INCREMENT=41316431 DEFAULT CHARSET=cp1251

On a server have it 8GB operative memory.

The problem - about time a day hangs inquiry 
"SELECT ` fid ` FROM frases WHERE ` key ` = ' 9e6ef9a789b1ac7879a9c2d70cd31037 ' LIMIT 1" hangs in the status "statistics".

Usually to the table in parallel address ~15 scripts address. 
Some do similar samples with other values ` key `, 
Some do Insert and Update tables frases.
The zavisshy inquiry cannot be stopped a command kill.

my.cnf
[client]
port=3308
socket=/tmp/mysql2.sock
[mysqld]
port=3308
socket=/tmp/mysql2.sock
table_open_cache = 1000
query_cache_size = 128M
query_cache_limit = 32M
read_rnd_buffer_size = 32M
myisam_sort_buffer_size = 8M
sort_buffer_size = 8M
key_buffer_size = 2048M
tmp_table_size = 64M
max_heap_table_size = 64M
thread_cache_size = 128
#innodb_buffer_pool_size = 2048M
max_allowed_packet = 16M
slow-query-log-file = /usr/local/mysql-db2/slow-queries.log
concurrent_insert=2
myisam_stats_method=nulls_equal
key_cache_division_limit=75
optimizer_search_depth=10

[mysqldump]
quick

How to repeat:
1

Thank you for the report.

But version 5.5.4 is a bit old. Please try current version 5.5.5-m3 and if problem still exists try to turn query cache to off and inform us about results.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".