MySQL Forums
Forum List  »  Performance

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
4051
April 21, 2009 11:58PM
1996
April 22, 2009 02:10AM
2007
April 22, 2009 09:37AM
1898
April 23, 2009 12:16AM
1947
April 23, 2009 02:20AM
1851
April 25, 2009 04:34PM
1878
April 27, 2009 09:47PM
1890
April 29, 2009 09:44AM
1858
April 30, 2009 10:54PM


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.