MySQL Forums :: Performance :: Please help with slow easy query


Advanced Search

Please help with slow easy query
Posted by: Ilya Cheburaev ()
Date: April 21, 2009 11:58PM

There is a little query wich works sometimes very slow:
SELECT SQL_CACHE name FROM category WHERE id = ?

Table:

CREATE TABLE `category` (
`id` int(11) NOT NULL default '0',
`name` varchar(50) default NULL,
`description` text,
`visible` int(11) default NULL,
`level` int(11) default NULL,
`sortorder` int(11) default NULL,
`alt_name` varchar(250) default NULL,
PRIMARY KEY (`id`),
KEY `I_sortorder` (`sortorder`),
KEY `id` (`name`,`id`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

Explain plan:
id / select_type / table / type / possible_keys / key / key_len / ref / rows / Extra
1 / SIMPLE / category / const / PRIMARY / PRIMARY / 4 / const / 1


MySQL Query Analizer say:

Execution Time Statistics
Max Time / Min Time / Avg Time / Total Time / Standard Deviation
2,189 / 0,000 / 0,021 / 3:26,238 / 0,064

Row Statistics
Max Rows / Min Rows / Avg Rows / Total Rows / Standard Deviation / Total Size / Max Size
1 / 0 / 1 / 9 / 664 0 / 149,78 KB / 47 B

Number of Executions
9 918

It's strange because table have index and usualy it's exec time is 0,005 sec.

I think there is no difference between SQL_CACHE and SQL_NOCACHE
QueryCache status:
__ Query Cache _________________________________________________________
Memory usage 19.40M of 32.00M %Used: 60.61
Block Fragmnt 10.67%
Hits 3.93M 59.8/s
Inserts 554.29k 8.4/s
Insrt:Prune 1.55:1 3.0/s
Hit:Insert 7.09:1



It's become to slow at the evening when search spiders starts to crawl web site and some other cron scripts start to work.
But it's not too havy.
And another idea may be it's something about procesess in the unix because there is a lot of open files by Mysql at this time and login in to the shell takes a lot of time and opening some files takes a lot of time too (it happened after changing table_cache to 1024 instead of 80 because 80 was always used by mysql).

There is a lot of open descriptors by mysql (at start it's about 5000):
gaia:/home/ilya/scripts # lsof | grep mysql -c
132852

gaia:/home/ilya/scripts # cat /proc/sys/fs/file-max
65536
gaia:/home/ilya/scripts # cat /proc/sys/fs/file-nr
2220 0 65536

Another system info:
gaia:/home/ilya/scripts # ulimit -a
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
max locked memory (kbytes, -l) 32
max memory size (kbytes, -m) unlimited
open files (-n) 16384
pipe size (512 bytes, -p) 8
stack size (kbytes, -s) unlimited
cpu time (seconds, -t) unlimited
max user processes (-u) 16384
virtual memory (kbytes, -v) unlimited


gaia:/home/ilya/scripts # cat /etc/my.cnf
# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 100M
max_allowed_packet = 1M
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
long_query_time=1
max_connections = 100
log_slow_queries = gaia-slow.log

table_cache = 1024
thread_cache_size=100
query_cache_size=32M
query_cache_type=2


System config:
MySQL 4.1.13-standard-log
Up Since 21.04.2009 15:42:54
Agent 2.0.5.7153
Rules 13 scheduled.
Last MySQL Contact 22.04.2009 10:01:00
Last Agent Contact 22.04.2009 9:47:27
OS SuSE 9.3 (Linux 2.6.11.4-20a-default)
CPU Pentium 4

RAM 0,98 GB
Disk Space / 3 GB (2,03 GB free)
/usr 7 GB (5,09 GB free)
/data 57,34 GB (24,59 GB free) (where mysql works)

And mysqlreport script output with mysqlstate information (script from Daniel Nichter hackmysql.com):
__ Key _________________________________________________________________
Buffer used 27.36M of 100.00M %Used: 27.36
Current 37.92M %Usage: 37.92
Write hit 91.36%
Read hit 99.87%

__ Questions ___________________________________________________________
Total 27.56M 419.6/s
Com_ 18.10M 275.6/s %Total: 65.69
DMS 5.05M 77.0/s 18.34
QC Hits 3.93M 59.8/s 14.26
COM_QUIT 472.68k 7.2/s 1.72
-Unknown 813 0.0/s 0.00
Slow 1 s 2.67k 0.0/s 0.01 %DMS: 0.05 Log: ON
DMS 5.05M 77.0/s 18.34
SELECT 3.04M 46.3/s 11.04 60.19
INSERT 2.00M 30.5/s 7.27 39.63
UPDATE 7.43k 0.1/s 0.03 0.15
DELETE 1.35k 0.0/s 0.00 0.03
REPLACE 0 0/s 0.00 0.00
Com_ 18.10M 275.6/s 65.69
change_db 13.62M 207.3/s 49.41
set_option 4.48M 68.1/s 16.24
show_tables 2.70k 0.0/s 0.01

__ SELECT and Sort _____________________________________________________
Scan 245.01k 3.7/s %SELECT: 8.05
Range 2.71k 0.0/s 0.09
Full join 45.20k 0.7/s 1.49
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 477.71k 7.3/s
Sort range 11.30k 0.2/s
Sort mrg pass 414 0.0/s

__ Query Cache _________________________________________________________
Memory usage 19.40M of 32.00M %Used: 60.61
Block Fragmnt 10.67%
Hits 3.93M 59.8/s
Inserts 554.29k 8.4/s
Insrt:Prune 1.55:1 3.0/s
Hit:Insert 7.09:1

__ Table Locks _________________________________________________________
Waited 54 0.0/s %Total: 0.00
Immediate 8.74M 133.1/s

__ Tables ______________________________________________________________
Open 692 of 1024 %Cache: 67.58
Opened 5.85k 0.1/s

__ Connections _________________________________________________________
Max used 101 of 100 %Max: 101.00
Total 472.75k 7.2/s

__ Created Temp ________________________________________________________
Disk table 45.68k 0.7/s
Table 476.20k 7.2/s Size: 32.0M
File 831 0.0/s

__ Threads _____________________________________________________________
Running 3 of 50
Cached 51 of 100 %Hit: 99.98
Created 101 0.0/s
Slow 36 0.0/s

__ Aborted _____________________________________________________________
Clients 123 0.0/s
Connects 942 0.0/s

__ Bytes _______________________________________________________________
Sent 2.54G 38.7k/s
Received 1.43G 21.7k/s


I think it's something about processes in linux or max file open but not sure. I'll be thankful for any help.

Options: ReplyQuote


Subject Views Written By Posted
Please help with slow easy query 3958 Ilya Cheburaev 04/21/2009 11:58PM
Re: Please help with slow easy query 1977 Aftab Khan 04/22/2009 02:10AM
Re: Please help with slow easy query 2050 Ilya Cheburaev 04/22/2009 03:27AM
Re: Please help with slow easy query 1982 Ilya Cheburaev 04/22/2009 07:02AM
Re: Please help with slow easy query 1996 Aftab Khan 04/22/2009 09:37AM
Re: Please help with slow easy query 1967 Ilya Cheburaev 04/22/2009 10:31AM
Re: Please help with slow easy query 1968 Ilya Cheburaev 04/22/2009 12:22PM
Re: Please help with slow easy query 1882 Rick James 04/23/2009 12:16AM
Re: Please help with slow easy query 1870 Ilya Cheburaev 04/23/2009 03:27AM
Re: Please help with slow easy query 1920 Aftab Khan 04/23/2009 02:20AM
Re: Please help with slow easy query 2481 Ilya Cheburaev 04/23/2009 03:25AM
Re: Please help with slow easy query 1990 Ilya Cheburaev 04/24/2009 07:18AM
Re: Please help with slow easy query 1869 Ilya Cheburaev 04/25/2009 01:42PM
Re: Please help with slow easy query 1841 Rick James 04/25/2009 04:34PM
Re: Please help with slow easy query 1972 Ilya Cheburaev 04/27/2009 09:28AM
Re: Please help with slow easy query 1863 Rick James 04/27/2009 09:47PM
Re: Please help with slow easy query 1729 Ilya Cheburaev 04/28/2009 09:16AM
Re: Please help with slow easy query 1940 Ilya Cheburaev 04/28/2009 09:34AM
Re: Please help with slow easy query 1869 Rick James 04/29/2009 09:44AM
Re: Please help with slow easy query 2288 Ilya Cheburaev 04/30/2009 02:07PM
Re: Please help with slow easy query 1841 Rick James 04/30/2009 10:54PM
Re: Please help with slow easy query 1819 Ilya Cheburaev 05/01/2009 11:30AM
Re: Please help with slow easy query 1861 Rick James 05/01/2009 09:52PM
Re: Please help with slow easy query 1844 Ilya Cheburaev 05/04/2009 12:46PM
Re: Please help with slow easy query 2014 Ilya Cheburaev 04/22/2009 10:46AM


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.