Re: Please help with slow easy query
Aftab Khan Wrote:
-------------------------------------------------------
> Do check following
>
> 1) SHOW PROCESSLIST\G # durning busy time, that
> will show you long running SQL
>
No long running =((
Just connecting processess Id changes very fast so there is a lot of connections;
mysql> SHOW PROCESSLIST;
+--------+----------+-----------+--------------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+----------+-----------+--------------+---------+------+-------+------------------+
| 524601 | root | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST |
| 525274 | passport | localhost | passport | Sleep | 7 | | NULL |
| 525282 | allshops | localhost | allshops | Sleep | 4 | | NULL |
| 525363 | passport | localhost | passport | Sleep | 1 | | NULL |
| 525364 | passport | localhost | passport | Sleep | 1 | | NULL |
| 525368 | allshops | localhost | karkusha | Sleep | 0 | | NULL |
| 525369 | allshops | localhost | podarkoff | Sleep | 0 | | NULL |
| 525370 | passport | localhost | passport | Sleep | 1 | | NULL |
| 525372 | passport | localhost | passport | Sleep | 1 | | NULL |
| 525373 | passport | localhost | passport | Sleep | 1 | | NULL |
| 525374 | passport | localhost | passport | Sleep | 1 | | NULL |
| 525375 | passport | localhost | passport | Sleep | 1 | | NULL |
| 525377 | passport | localhost | passport | Sleep | 0 | | NULL |
| 525380 | allshops | localhost | vse_igrushki | Sleep | 0 | | NULL |
| 525381 | allshops | localhost | futuron | Sleep | 0 | | NULL |
| 525378 | allshops | localhost | vdar | Sleep | 0 | | NULL |
| 525382 | allshops | localhost | vdar | Sleep | 0 | | NULL |
| 525383 | passport | localhost | passport | Sleep | 0 | | NULL |
| 525384 | allshops | localhost | podarkoff | Sleep | 0 | | NULL |
| 525385 | passport | localhost | passport | Sleep | 0 | | NULL |
| 525386 | passport | localhost | passport | Sleep | 0 | | NULL |
| 525387 | passport | localhost | passport | Sleep | 0 | | NULL |
+--------+----------+-----------+--------------+---------+------+-------+------------------+
22 rows in set (0.13 sec)
> 2) | Threads_connected | 23 | # it's high looks
> like your application doesn't colse the connection
> properly or running very long query??
>
> 3) load average: 13.50, 13.25, 11.97 # load is too
> high, is it dedicated server, I mean only mysql
> run on this machine.
>
Yes only mysql and apache.
Now (top) (uptime for mysql is 0 13:35:22)
top - 13:03:03 up 1 day, 21:21, 3 users, load average: 24.92, 24.65, 24.09
Tasks: 167 total, 6 running, 161 sleeping, 0 stopped, 0 zombie
Cpu(s): 83.3% us, 14.4% sy, 0.0% ni, 0.0% id, 1.3% wa, 0.0% hi, 1.0% si
Mem: 1028036k total, 1008952k used, 19084k free, 138744k buffers
Swap: 2104432k total, 2716k used, 2101716k free, 322824k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
6975 mysql 15 0 487m 131m 5332 S 33.6 13.1 192:31.24 mysqld
4927 nobody 15 0 16444 10m 2660 S 5.1 1.0 0:32.15 httpd
31429 nobody 15 0 15692 9660 2716 S 3.6 0.9 0:45.08 httpd
3802 nobody 15 0 16824 10m 2660 S 3.3 1.0 0:48.09 httpd
4936 nobody 17 0 16296 9m 2664 R 3.3 1.0 0:36.57 httpd
9166 nobody 16 0 16604 10m 2656 S 3.3 1.0 0:16.98 httpd
10028 nobody 15 0 15964 9856 2660 S 3.3 1.0 0:08.97 httpd
2327 nobody 15 0 16392 10m 2724 S 3.0 1.0 0:50.45 httpd
8221 nobody 15 0 16528 10m 2656 S 3.0 1.0 0:20.81 httpd
31238 nobody 15 0 16592 10m 2660 S 2.7 1.0 0:53.46 httpd
32366 nobody 15 0 16560 10m 2664 S 2.7 1.0 0:51.76 httpd
32611 nobody 15 0 16860 10m 2720 S 2.7 1.1 1:14.31 httpd
11236 nobody 15 0 16660 10m 2656 S 2.7 1.0 0:11.87 httpd
4922 nobody 16 0 16424 10m 2660 S 2.4 1.0 0:35.62 httpd
11233 nobody 16 0 16696 10m 2656 S 2.4 1.0 0:13.26 httpd
11239 nobody 15 0 16560 10m 2656 S 2.4 1.0 0:05.40 httpd
11450 nobody 15 0 16604 10m 2656 S 2.4 1.0 0:06.63 httpd
10024 nobody 16 0 15648 9540 2660 S 2.1 0.9 0:16.77 httpd
> 4) Check your slow query log if its enabled.
yes, it's enabled. I've got time-trial of mysql-enterprise so it shows some queries wich take a lot of process time. I fixed some of them and now it's a little bit faster but not enough and it's some strange kind of slownes:
after start of apache and mysqld queries run fast and top shows load avarange < 1.00 then after load goes up queries become run slower and system starts to work slow. Load averange rising up to 5.00 - 20.00.
And another thing i've been upgraded mysql to 5.1.34-log
after that two things changed:
1) lsof | grep mysql -c
shows ~1000 descriptors
2) mysql5 uses only 1 process for handling requests so there is not a lot of processes with ps ax | grep mysql
it's good.
but problem still there
so about query log there is some statements but their lock time is low (< 0.000198)
and almost only 1 statement wich i don't know how to optimize =(
And i've got new interesting information after restarting of mysql loadaverange did not falled but then i tried to restart apache and it's falled to 0.5!
i think there is some thing about apache wich overload it. but why it's groing up not immediatly but only after a couple of hours...