MySQL Sleep

[DigiCrime]

How can you lower the amount of MySQL sleep? When I run top, sort by memory usage its no wonder the load is high you see queries in the 1000's that are sleeping, which tells me this is what is taking up so much of the ram.

Thanks

[Scott MacVicar]

Sleeping means they are usually waiting on a lock to be released from another long running query.

Run

SHOW FULL PROCESSLIST

It can also indicate that the client is waiting for a process to send another statement, this occurs if PHP dies before closing the connection.

Do you have a dedicated server?

[DigiCrime]

Yep. Several actually I find the best mysql performance tips here so I thought I would look over here first :D I had a problem that I was about to pull my hair out, several mysql processes taking up about 164M of memory till I seen this.

wait_timeout = 14440 that is way to high, I changed it to 60 and my problem went away. I use this now


[mysqld]
max_connections = 500
key_buffer = 200M
myisam_sort_buffer_size = 120M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 3M
table_cache = 2000
thread_cache_size = 150
wait_timeout = 60
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 1500
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
thread_concurrency=2
# skip-innodb
[mysqld_safe]
open_files_limit = 8192
[mysql]
no-auto-rehash
# safe-updates
[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M
[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M
[mysqlhotcopy]
interactive-timeout


Seems to be ok now but the problem I had before was just dozens of mysql queries all ranging from 1000-1600 so i had assumed this was the problem

This is where mysql stands now

29204 mysql 13 6 112M 112M 1640 S N 0.1 11.1 0:03 mysqld
29206 mysql 13 6 112M 112M 1640 S N 0.0 11.1 0:00 mysqld
29207 mysql 13 6 112M 112M 1640 S N 0.0 11.1 0:06 mysqld
29209 mysql 13 6 112M 112M 1640 S N 0.0 11.1 0:23 mysqld
29211 mysql 13 6 112M 112M 1640 S N 0.0 11.1 0:24 mysqld
29213 mysql 13 6 112M 112M 1640 S N 0.0 11.1 0:24 mysqld
29219 mysql 13 6 112M 112M 1640 S N 0.0 11.1 0:23 mysqld
29220 mysql 13 6 112M 112M 1640 S N 0.0 11.1 0:26 mysqld
29225 mysql 13 6 112M 112M 1640 S N 0.0 11.1 0:27 mysqld
29226 mysql 13 6 112M 112M 1640 S N 0.0 11.1 0:27 mysqld
29227 mysql 13 6 112M 112M 1640 S N 0.0 11.1 0:27 mysqld
29228 mysql 13 6 112M 112M 1640 S N 0.0 11.1 0:26 mysqld
29231 mysql 13 6 112M 112M 1640 S N 0.1 11.1 0:24 mysqld
29234 mysql 13 6 112M 112M 1640 S N 0.0 11.1 0:25 mysqld
29239 mysql 13 6 112M 112M 1640 S N 0.1 11.1 0:22 mysqld
29240 mysql 13 6 112M 112M 1640 S N 0.7 11.1 0:22 mysqld
29242 mysql 13 6 112M 112M 1640 S N 0.0 11.1 0:24 mysqld
29245 mysql 13 6 112M 112M 1640 S N 0.0 11.1 0:24 mysqld
29247 mysql 13 6 112M 112M 1640 S N 0.0 11.1 0:24 mysqld
29261 mysql 13 6 112M 112M 1640 S N 0.0 11.1 0:25 mysqld
29263 mysql 13 6 112M 112M 1640 S N 0.0 11.1 0:26 mysqld
31782 mysql 13 6 112M 112M 1640 S N 0.0 11.1 0:24 mysqld
31785 mysql 13 6 112M 112M 1640 S N 0.0 11.1 0:22 mysqld
31795 mysql 13 6 112M 112M 1640 S N 0.0 11.1 0:22 mysqld
32255 mysql 13 6 112M 112M 1640 S N 0.0 11.1 0:22 mysqld
32258 mysql 13 6 112M 112M 1640 S N 0.0 11.1 0:23 mysqld
32263 mysql 13 6 112M 112M 1640 S N 0.0 11.1 0:23 mysqld
32267 mysql 13 6 112M 112M 1640 S N 0.1 11.1 0:24 mysqld
32268 mysql 13 6 112M 112M 1640 S N 0.0 11.1 0:24 mysqld

And, at the time I quit top and look at the processlist

mysql> SHOW FULL PROCESSLIST;
+-------+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+------+-----------+------+---------+------+-------+-----------------------+
| 45836 | root | localhost | NULL | Query | 0 | NULL | SHOW FULL PROCESSLIST |
+-------+------+-----------+------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)

mysql> STATUS;
--------------
mysql Ver 12.22 Distrib 4.0.25, for pc-linux-gnu (i686)
Connection id: 45836
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Server version: 4.0.25-standard
Protocol version: 10
Connection: Localhost via UNIX socket
Client characterset: latin1
Server characterset: latin1
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 4 hours 20 min 39 sec
Threads: 1 Questions: 1177012 Slow queries: 3 Opens: 1943 Flush tables: 1 Open tables: 1806 Queries per second avg: 75.261
--------------


So i dunno, mysql still taking up a bit of memory..gig of ram in the system, ram isn't cheap to buy so I cant really stick anymore ram in it right now. I doubt mysql is causing the problem at the moment might be something else just hogging the system resources.

Thanks as always Steve.

[eva2000]

yes lowering wait_timeout will help just be sure not to lowering too far otherwise you'd get 'lost connection to mysql' error messages

[Scott MacVicar]

Have you tried mysqltop? its like top but for MySQL. Quite handy.

[DigiCrime]

Yep, I have it installed already...not a whole lot going at the moment