MySQL Performance: MySQL => simple query occasionally stalls

simple query occasionally stalls [message #3100]

Mon, 19 May 2008 18:34

Gromph
Messages: 4
Registered: May 2008

Junior Member

I've been trying to resolve this problem off and on for years. I have a table with about 17 million records. A simple query like select count(*) from Users where UserID='XXX'; usually takes less then 0.01 seconds. Sometimes it'll take 2-5 seconds. I've turned on log-slow-queries and it has logged 92 longer then 1 second in the last few hours:

Count: 92 Time=2.99s (275s) Lock=0.00s (0s) Rows=1.0 (92)
select count(*) from Users where UserID='S'

My server is a dual Quad Core Xeon E5345, with 8 gigs ram, sas 15k drives. It is CentOS5 running under xen virtualization, with 4gigs of ram and 4 cores assigned to it. The other xen guests on this machine shouldn't be doing anything to cause these slow downs. We had similar stalls before moving to xen.

The question I have is how to I go about trying to determine the cause of these stalls?

Users table create query:
CREATE TABLE `Users` (
`ID` int(11) NOT NULL auto_increment,
`UserID` char(64) NOT NULL default '0',
`DateAdded` datetime default NULL,
`DateLastEvent` datetime default NULL,
`FoundFirst` tinyint(3) unsigned default '0',
PRIMARY KEY (`ID`),
KEY `UserID` (`UserID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

my.cnf:
[client]
port= 3306
socket= /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket= /var/run/mysqld/mysqld.sock
nice= 0

[mysqld]
server-id=32
user= mysql
pid-file= /var/run/mysqld/mysqld.pid
socket= /var/run/mysqld/mysqld.sock
port= 3306
log-error= /var/log/mysql/mysql.err
basedir= /usr
datadir= /var/lib/mysql
tmpdir= /tmp
language= /usr/share/mysql/english
skip-external-locking
key_buffer= 16M
max_allowed_packet= 16M
thread_stack= 128K
query_cache_limit= 1048576
query_cache_size = 26214400
query_cache_type = 1
sort_buffer_size = 256M
key_buffer_size = 1024M
table_cache = 256
thread_cache_size = 32
log-slow-queries= /var/lib/mysql/db-slow.log
long_query_time = 1
log-bin= /var/lib/mysql/mysql-bin.log

[mysqldump]
quick
quote-names
max_allowed_packet= 16M

[isamchk]
key_buffer= 16M

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M

Thanks for any help!

Report message to a moderator