# simple query occasionally stalls **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [May 19, 2008, 5:04pm UTC](https://forums.percona.com/t/simple-query-occasionally-stalls/765 "2008-05-19T17:04:22Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![Gromph](https://avatars.discourse-cdn.com/v4/letter/g/a698b9/32.png) [@Gromph](https://forums.percona.com/u/Gromph)\ **Post date:** [May 19, 2008, 5:04pm UTC](https://forums.percona.com/t/simple-query-occasionally-stalls/765/1 "2008-05-19T17:04:22Z")
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! ---
**Author:** ![stark](https://avatars.discourse-cdn.com/v4/letter/s/dbc845/32.png) [@stark](https://forums.percona.com/u/stark)\ **Post date:** [May 22, 2008, 5:54am UTC](https://forums.percona.com/t/simple-query-occasionally-stalls/765/2 "2008-05-22T05:54:00Z")
The first question that comes to my mind is: why is your key\_buffer set to 16MB when your box has 8GB? Set it to 1GB and see what happens. The other values also don’t look reasonable - they seem to be default values. ---
**Author:** ![Gromph](https://avatars.discourse-cdn.com/v4/letter/g/a698b9/32.png) [@Gromph](https://forums.percona.com/u/Gromph)\ **Post date:** [May 22, 2008, 11:50am UTC](https://forums.percona.com/t/simple-query-occasionally-stalls/765/3 "2008-05-22T11:50:36Z")
It looks like key\_buffer is a left over from older versions of mysql. I set key\_buffer\_size to 1024M a little lower. I tried setting key\_buffer with this line: set global key\_buffer=1024M; and got this error: ERROR 1193 (HY000): Unknown system variable ‘key\_buffer’ Thanks for spotting that though! ---
**Author:** ![stark](https://avatars.discourse-cdn.com/v4/letter/s/dbc845/32.png) [@stark](https://forums.percona.com/u/stark)\ **Post date:** [May 23, 2008, 3:45am UTC](https://forums.percona.com/t/simple-query-occasionally-stalls/765/4 "2008-05-23T03:45:46Z")
Sorry - I just had a brief look… I think you probably can raise the key\_buffer\_size. And your sort\_buffer\_size is to big - this is a per connection setting. But we cannot give you real hints without more details: - show global status; (or mysqlreport) - vmstat or top when the server is under load. Cheers ---
**Author:** ![Gromph](https://avatars.discourse-cdn.com/v4/letter/g/a698b9/32.png) [@Gromph](https://forums.percona.com/u/Gromph)\ **Post date:** [May 23, 2008, 12:16pm UTC](https://forums.percona.com/t/simple-query-occasionally-stalls/765/5 "2008-05-23T12:16:00Z")
I changed key\_buffer\_size to 2048M and sort\_buffer\_size to 64M. But I’m still see the stalls. [root@db spades]# vmstatprocs -----------memory---------- —swap-- -----io---- --system-- -----cpu------ r b swpd free buff cache si so bi bo in cs us sy id wa st 8 0 40 287432 153536 3115944 0 0 4 58 7 2 3 4 92 1 0 [root@db spades]# toptop - 10:40:40 up 17 days, 19:32, 2 users, load average: 1.78, 0.76, 0.64 Tasks: 107 total, 6 running, 101 sleeping, 0 stopped, 0 zombie Cpu0 : 8.1%us, 12.0%sy, 0.0%ni, 77.1%id, 1.4%wa, 0.1%hi, 1.1%si, 0.1%st Cpu1 : 1.0%us, 1.2%sy, 0.0%ni, 96.7%id, 0.9%wa, 0.0%hi, 0.2%si, 0.0%st Cpu2 : 0.9%us, 1.0%sy, 0.0%ni, 97.3%id, 0.7%wa, 0.0%hi, 0.1%si, 0.0%st Cpu3 : 0.9%us, 1.0%sy, 0.0%ni, 97.3%id, 0.7%wa, 0.0%hi, 0.1%si, 0.0%st Mem: 4194472k total, 3912168k used, 282304k free, 153640k buffers Swap: 1044184k total, 40k used, 1044144k free, 3119000k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 18257 lobby 15 0 58848 40m 3048 S 12 1.0 3729:40 spades1 10897 mysql 15 0 2210m 290m 4732 S 8 7.1 1:35.29 mysqld 20558 lobby 15 0 32864 15m 2984 R 3 0.4 723:30.54 hearts1 20617 lobby 15 0 36932 19m 2976 R 2 0.5 585:48.75 euchre1 14829 lobby 15 0 29228 9356 4000 R 0 0.2 2:03.77 solitaire1 27292 lobby 15 0 125m 107m 2996 R 0 2.6 233:15.73 backgammon1 1 root 15 0 2060 644 544 S 0 0.0 0:13.23 init 2 root RT 0 0 0 0 S 0 0.0 0:04.28 migration/0 3 root 34 19 0 0 0 S 0 0.0 0:00.42 ksoftirqd/0 mysql\> show global status;±----------------------------------±---------+| Variable\_name | Value |±----------------------------------±---------+| Aborted\_clients | 6 || Aborted\_connects | 49 || Binlog\_cache\_disk\_use | 0 || Binlog\_cache\_use | 0 || Bytes\_received | 12255911 || Bytes\_sent | 42885115 || Com\_admin\_commands | 102 || Com\_alter\_db | 0 || Com\_alter\_table | 0 || Com\_analyze | 0 || Com\_backup\_table | 0 || Com\_begin | 0 || Com\_change\_db | 15409 || Com\_change\_master | 0 || Com\_check | 0 || Com\_checksum | 0 || Com\_commit | 24 || Com\_create\_db | 0 || Com\_create\_function | 0 || Com\_create\_index | 0 || Com\_create\_table | 0 || Com\_dealloc\_sql | 0 || Com\_delete | 35 || Com\_delete\_multi | 0 || Com\_do | 0 || Com\_drop\_db | 0 || Com\_drop\_function | 0 || Com\_drop\_index | 0 || Com\_drop\_table | 0 || Com\_drop\_user | 0 || Com\_execute\_sql | 0 || Com\_flush | 0 || Com\_grant | 0 || Com\_ha\_close | 0 || Com\_ha\_open | 0 || Com\_ha\_read | 0 || Com\_help | 0 || Com\_insert | 11750 || Com\_insert\_select | 20 || Com\_kill | 0 || Com\_load | 0 || Com\_load\_master\_data | 0 || Com\_load\_master\_table | 0 || Com\_lock\_tables | 0 || Com\_optimize | 0 || Com\_preload\_keys | 0 || Com\_prepare\_sql | 0 || Com\_purge | 0 || Com\_purge\_before\_date | 0 || Com\_rename\_table | 0 || Com\_repair | 0 || Com\_replace | 0 || Com\_replace\_select | 0 || Com\_reset | 0 || Com\_restore\_table | 0 || Com\_revoke | 0 || Com\_revoke\_all | 0 || Com\_rollback | 19 || Com\_savepoint | 0 || Com\_select | 24480 || Com\_set\_option | 687 || Com\_show\_binlog\_events | 0 || Com\_show\_binlogs | 0 || Com\_show\_charsets | 0 || Com\_show\_collations | 2 || Com\_show\_column\_types | 0 || Com\_show\_create\_db | 0 || Com\_show\_create\_table | 0 || Com\_show\_databases | 1 || Com\_show\_errors | 0 || Com\_show\_fields | 40 || Com\_show\_grants | 0 || Com\_show\_innodb\_status | 0 || Com\_show\_keys | 10 || Com\_show\_logs | 0 || Com\_show\_master\_status | 0 || Com\_show\_ndb\_status | 0 || Com\_show\_new\_master | 0 || Com\_show\_open\_tables | 0 || Com\_show\_privileges | 0 || Com\_show\_processlist | 7 || Com\_show\_slave\_hosts | 3 || Com\_show\_slave\_status | 0 || Com\_show\_status | 13 || Com\_show\_storage\_engines | 0 || Com\_show\_tables | 31 || Com\_show\_triggers | 0 || Com\_show\_variables | 13 || Com\_show\_warnings | 1 || Com\_slave\_start | 0 || Com\_slave\_stop | 0 || Com\_stmt\_close | 0 || Com\_stmt\_execute | 0 || Com\_stmt\_fetch | 0 || Com\_stmt\_prepare | 0 || Com\_stmt\_reset | 0 || Com\_stmt\_send\_long\_data | 0 || Com\_truncate | 0 || Com\_unlock\_tables | 0 || Com\_update | 25069 || Com\_update\_multi | 0 || Com\_xa\_commit | 0 || Com\_xa\_end | 0 || Com\_xa\_prepare | 0 || Com\_xa\_recover | 0 || Com\_xa\_rollback | 0 || Com\_xa\_start | 0 || Compression | OFF || Connections | 6593 || Created\_tmp\_disk\_tables | 40 || Created\_tmp\_files | 5 || Created\_tmp\_tables | 137 || Delayed\_errors | 0 || Delayed\_insert\_threads | 4 || Delayed\_writes | 7998 || Flush\_commands | 1 || Handler\_commit | 0 || Handler\_delete | 12 || Handler\_discover | 0 || Handler\_prepare | 0 || Handler\_read\_first | 24 || Handler\_read\_key | 47251 || Handler\_read\_next | 3870660 || Handler\_read\_prev | 0 || Handler\_read\_rnd | 450 || Handler\_read\_rnd\_next | 39869350 || Handler\_rollback | 0 || Handler\_savepoint | 0 || Handler\_savepoint\_rollback | 0 || Handler\_update | 18420 || Handler\_write | 17598 || Innodb\_buffer\_pool\_pages\_data | 19 || Innodb\_buffer\_pool\_pages\_dirty | 0 || Innodb\_buffer\_pool\_pages\_flushed | 0 || Innodb\_buffer\_pool\_pages\_free | 493 || Innodb\_buffer\_pool\_pages\_latched | 0 || Innodb\_buffer\_pool\_pages\_misc | 0 || Innodb\_buffer\_pool\_pages\_total | 512 || Innodb\_buffer\_pool\_read\_ahead\_rnd | 1 || Innodb\_buffer\_pool\_read\_ahead\_seq | 0 || Innodb\_buffer\_pool\_read\_requests | 77 || Innodb\_buffer\_pool\_reads | 12 || Innodb\_buffer\_pool\_wait\_free | 0 || Innodb\_buffer\_pool\_write\_requests | 0 || Innodb\_data\_fsyncs | 3 || Innodb\_data\_pending\_fsyncs | 0 || Innodb\_data\_pending\_reads | 0 || Innodb\_data\_pending\_writes | 0 || Innodb\_data\_read | 2494464 || Innodb\_data\_reads | 25 || Innodb\_data\_writes | 3 || Innodb\_data\_written | 1536 || Innodb\_dblwr\_pages\_written | 0 || Innodb\_dblwr\_writes | 0 || Innodb\_log\_waits | 0 || Innodb\_log\_write\_requests | 0 || Innodb\_log\_writes | 1 || Innodb\_os\_log\_fsyncs | 3 || Innodb\_os\_log\_pending\_fsyncs | 0 || Innodb\_os\_log\_pending\_writes | 0 || Innodb\_os\_log\_written | 512 || Innodb\_page\_size | 16384 || Innodb\_pages\_created | 0 || Innodb\_pages\_read | 19 || Innodb\_pages\_written | 0 || Innodb\_row\_lock\_current\_waits | 0 || Innodb\_row\_lock\_time | 0 || Innodb\_row\_lock\_time\_avg | 0 || Innodb\_row\_lock\_time\_max | 0 || Innodb\_row\_lock\_waits | 0 || Innodb\_rows\_deleted | 0 || Innodb\_rows\_inserted | 0 || Innodb\_rows\_read | 0 || Innodb\_rows\_updated | 0 || Key\_blocks\_not\_flushed | 0 || Key\_blocks\_unused | 1833280 || Key\_blocks\_used | 22402 || Key\_read\_requests | 925022 || Key\_reads | 22402 || Key\_write\_requests | 57383 || Key\_writes | 57113 || Last\_query\_cost | 0.000000 || Max\_used\_connections | 40 || Not\_flushed\_delayed\_rows | 0 || Open\_files | 218 || Open\_streams | 0 || Open\_tables | 103 || Opened\_tables | 109 || Qcache\_free\_blocks | 403 || Qcache\_free\_memory | 20899056 || Qcache\_hits | 3540 || Qcache\_inserts | 21207 || Qcache\_lowmem\_prunes | 0 || Qcache\_not\_cached | 3390 || Qcache\_queries\_in\_cache | 4207 || Qcache\_total\_blocks | 8845 || Questions | 87683 || Rpl\_status | NULL || Select\_full\_join | 0 || Select\_full\_range\_join | 0 || Select\_range | 377 || Select\_range\_check | 0 || Select\_scan | 3910 || Slave\_open\_temp\_tables | 0 || Slave\_retried\_transactions | 0 || Slave\_running | OFF || Slow\_launch\_threads | 0 || Slow\_queries | 41 || Sort\_merge\_passes | 0 || Sort\_range | 4 || Sort\_rows | 859 || Sort\_scan | 93 || Ssl\_accept\_renegotiates | 0 || Ssl\_accepts | 0 || Ssl\_callback\_cache\_hits | 0 || Ssl\_cipher | || Ssl\_cipher\_list | || Ssl\_client\_connects | 0 || Ssl\_connect\_renegotiates | 0 || Ssl\_ctx\_verify\_depth | 0 || Ssl\_ctx\_verify\_mode | 0 || Ssl\_default\_timeout | 0 || Ssl\_finished\_accepts | 0 || Ssl\_finished\_connects | 0 || Ssl\_session\_cache\_hits | 0 || Ssl\_session\_cache\_misses | 0 || Ssl\_session\_cache\_mode | NONE || Ssl\_session\_cache\_overflows | 0 || Ssl\_session\_cache\_size | 0 || Ssl\_session\_cache\_timeouts | 0 || Ssl\_sessions\_reused | 0 || Ssl\_used\_session\_cache\_entries | 0 || Ssl\_verify\_depth | 0 || Ssl\_verify\_mode | 0 || Ssl\_version | || Table\_locks\_immediate | 81801 || Table\_locks\_waited | 226 || Tc\_log\_max\_pages\_used | 0 || Tc\_log\_page\_size | 0 || Tc\_log\_page\_waits | 0 || Threads\_cached | 21 || Threads\_connected | 23 || Threads\_created | 40 || Threads\_running | 4 || Uptime | 1692 |±----------------------------------±---------+ ---
**Author:** ![stark](https://avatars.discourse-cdn.com/v4/letter/s/dbc845/32.png) [@stark](https://forums.percona.com/u/stark)\ **Post date:** [May 23, 2008, 3:06pm UTC](https://forums.percona.com/t/simple-query-occasionally-stalls/765/6 "2008-05-23T15:06:38Z")
One idea I first had is that these stalls could be a locking problems - but you have very few Table\_locks\_waited. Still it could be… Your applications seem to be write intensive - the query cache is not really useful. You could try to disable it, but it should not be responsible for the stalls. How much memory do your databases take in total? ---
**Author:** ![Gromph](https://avatars.discourse-cdn.com/v4/letter/g/a698b9/32.png) [@Gromph](https://forums.percona.com/u/Gromph)\ **Post date:** [May 23, 2008, 3:22pm UTC](https://forums.percona.com/t/simple-query-occasionally-stalls/765/7 "2008-05-23T15:22:19Z")
Ya I have a slave database server that is for longer reads to avoid locking the master. Also it seems if other queries were locking the table they would show up in the slow queries log. I’ll try turning off the query cache. doing a du -h /var/lib/mysql is 19gigs. Most of the data is archived logs. The Users table that the query stalls on is 1.5 gigs MYD + 729M MYI.