sleep query

[morteza3245]

Hello
when i did run this command:

mysqladmin processlist;

i saw some database is sleep, that is normall? havent problem?

Thanks!

 

[HostingH]

Hi,

It will not affect on mysql service performance. Just keep eye on it if you are facing server load issue due to mysql.

Thanks,

 

[cPanelMichael]

Hello

There is a good answer on the "sleep" processes at the following third-party URL:

php - MySql Proccesslist filled with "Sleep" Entries leading to "To many Connections"? - Stack Overflow

It should not be a problem unless you notice it effecting your MySQL performance or notice excessively high number of MySQL processes sleeping.

Thank you.

 

[morteza3245]

my output mysqltuner:

root@host [/usr/local/bin]# /usr/local/bin/mysqltuner.pl

 >>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at [url]http://mysqltuner.com/[/url]
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.32-cll-lve
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 8G (Tables: 15008)
[--] Data in InnoDB tables: 154M (Tables: 1629)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 1M (Tables: 90)
[!!] Total fragmented tables: 1772

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 18h 4m 4s (28M q [188.476 qps], 737K conn, TX: 751B, RX: 4B)
[--] Reads / Writes: 60% / 40%
[--] Total buffers: 5.3G global + 5.4M per thread (151 max threads)
[OK] Maximum possible memory usage: 6.1G (12% of installed RAM)
[OK] Slow queries: 0% (187K/28M)
[OK] Highest usage of available connections: 32% (49/151)
[OK] Key buffer size / total MyISAM indexes: 5.0G/1.2G
[OK] Key buffer hit rate: 100.0% (5B cached / 857K reads)
[OK] Query cache efficiency: 72.8% (14M cached / 20M selects)
[!!] Query cache prunes per day: 610509
[OK] Sorts requiring temporary tables: 0% (2K temp sorts / 951K sorts)
[!!] Joins performed without indexes: 22342
[!!] Temporary tables created on disk: 41% (938K on disk / 2M total)
[OK] Thread cache hit rate: 99% (58 created / 737K connections)
[!!] Table cache hit rate: 1% (5K open / 250K opened)
[OK] Open file limit used: 18% (9K/50K)
[OK] Table locks acquired immediately: 99% (11M immediate / 11M locks)
[OK] InnoDB data size / buffer pool: 154.0M/200.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (> 50M)
    join_buffer_size (> 1.0M, or always use indexes with joins)
    tmp_table_size (> 50M)
    max_heap_table_size (> 50M)
    table_cache (> 5000)

root@host [/usr/local/bin]#

my config from my.cnf now:

[mysqld]
local-infile=0
myisam_use_mmap=1

join_buffer_size=1M
read_rnd_buffer_size=2M

query_cache_type = 1
query_cache_size=50M
query_cache_limit=1M

tmp_table_size=50M
max_heap_table_size=50M

thread_cache_size=30
table_open_cache = 5000
table_definition_cache = 2500

max_allowed_packet=32M
key_buffer_size=5G

open_files_limit=50000

slow_query_log=1
slow_query_log_file=mysql-slow.log
long_query_time=0.1


innodb_buffer_pool_size = 200M

in top -c i see below command have high usage:

253970 mysql 20 0 12.4g 3.7g 1.6g S 144.3 7.8 3458:46 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql

Please help me.

Thanks!

 

[cPanelMichael]

I have moved this thread to the "Optimization" forum. You are likely to receive more user feedback here.

Thank you.