show processlist sleep线程多，导致页面访问慢!

老男孩不老

64位centos系统.

现象:每天有1-2次 数据库Sleep线程特别多，多的时候到200, 多的时候 页面访问就会慢.
平时很快.基本show 不超过10个查询.
show processlist;如下 请高手给点解决办法和排查思路:
| 4411958 | mysql  | 192.168.1.23:59688 | dz_bbs     | Sleep       |    37 |                                                                | NULL             |
| 4411993 | mysql  | 192.168.1.23:59736 | dz_bbs     | Sleep       |    35 |                                                                | NULL             |
| 4411994 | mysql  | 192.168.1.23:59737 | dz_bbs     | Sleep       |    35 |                                                                | NULL             |
| 4411995 | mysql  | 192.168.1.23:59738 | dz_bbs     | Sleep       |    35 |                                                                | NULL             |
| 4411997 | mysql  | 192.168.1.23:59740 | dz_bbs     | Sleep       |    35 |                                                                | NULL             |
| 4411996 | mysql  | 192.168.1.23:59739 | dz_bbs     | Sleep       |    35 |                                                                | NULL             |
| 4411998 | mysql  | 192.168.1.23:59741 | dz_bbs     | Sleep       |    35 |                                                                | NULL             |
| 4411999 | mysql  | 192.168.1.23:59742 | dz_bbs     | Sleep       |    35 |                                                                | NULL             |
| 4412000 | mysql  | 192.168.1.23:59743 | dz_bbs     | Sleep       |    35 |                                                                | NULL             |
| 4412004 | mysql  | 192.168.1.23:59750 | dz_bbs     | Sleep       |    35 |                                                                | NULL             |
| 4412007 | mysql  | 192.168.1.23:59754 | dz_bbs     | Sleep       |    34 |                                                                | NULL             |
| 4412008 | mysql  | 192.168.1.23:59755 | dz_bbs     | Sleep       |    34 |                                                                | NULL             |
| 4412009 | mysql  | 192.168.1.23:59758 | dz_bbs     | Sleep       |    34 |                                                                | NULL             |
| 4412046 | mysql  | 192.168.1.23:59815 | dz_bbs     | Sleep       |    27 |                                                                | NULL             |
| 4412047 | mysql  | 192.168.1.23:59816 | dz_bbs     | Sleep       |    32 |                                                                | NULL             |
| 4412048 | mysql  | 192.168.1.23:59817 | dz_bbs     | Sleep       |    32 |                                                                | NULL             |
| 4412084 | mysql  | 192.168.1.23:59863 | dz_bbs     | Sleep       |    25 |                                                                | NULL             |
| 4412094 | mysql  | 192.168.1.23:59869 | dz_bbs     | Sleep       |    30 |                                                                | NULL             |
| 4412106 | mysql  | 192.168.1.23:59877 | dz_bbs     | Sleep       |    29 |                                                                | NULL             |
| 4412107 | mysql  | 192.168.1.23:59878 | dz_bbs     | Sleep       |    29 |                                                                | NULL             |
| 4412134 | mysql  | 192.168.1.23:59910 | dz_bbs     | Sleep       |    28 |                                                                | NULL             |
| 4412155 | mysql  | 192.168.1.23:59942 | dz_bbs     | Sleep       |    27 |                                                                | NULL             |
| 4412164 | mysql  | 192.168.1.23:59958 | dz_ucenter | Sleep       |    27 |                                                                | NULL             |
| 4412174 | mysql  | 192.168.1.23:59982 | dz_bbs     | Sleep       |    26 |                                                                | NULL             |
| 4412188 | mysql  | 192.168.1.23:59994 | dz_bbs     | Sleep       |    25 |                                                                | NULL             |
| 4412191 | mysql  | 192.168.1.23:59998 | dz_bbs     | Sleep       |    25 |                                                                | NULL             |
| 4412194 | mysql  | 192.168.1.23:60002 | dz_ucenter | Sleep       |    25 |                                                                | NULL             |
| 4412237 | mysql  | 192.168.1.23:60061 | dz_bbs     | Sleep       |    23 |                                                                | NULL             |
| 4412247 | mysql  | 192.168.1.23:60073 | dz_bbs     | Sleep       |    23 |                                                                | NULL             |
| 4412250 | mysql  | 192.168.1.23:60078 | dz_bbs     | Sleep       |    23 |                                                                | NULL             |
| 4412253 | mysql  | 192.168.1.23:60083 | dz_bbs     | Sleep       |    23 |                                                                | NULL             |
| 4412269 | mysql  | 192.168.1.23:60105 | dz_bbs     | Sleep       |    21 |                                                                | NULL             |
| 4412276 | mysql  | 192.168.1.23:60114 | dz_bbs     | Sleep       |    20 |                                                                | NULL             |
| 4412278 | mysql  | 192.168.1.23:60116 | dz_bbs     | Sleep       |    20 |                                                                | NULL             |
| 4412279 | mysql  | 192.168.1.23:60117 | dz_bbs     | Sleep       |    20 |                                                                | NULL             |
| 4412280 | mysql  | 192.168.1.23:60118 | dz_bbs     | Sleep       |    20 |                                                                | NULL             |
| 4412302 | mysql  | 192.168.1.23:60144 | dz_bbs     | Sleep       |    19 |                                                                | NULL             |
| 4412305 | mysql  | 192.168.1.23:60148 | dz_bbs     | Sleep       |    19 |

mysqlreport结果：

MySQL 5.0.51a-log        uptime 3 13:42:39      Thu Dec  4 09:43:26 2008

__ Key _________________________________________________________________
Buffer used   258.14M of   1.17G  %Used:  21.51
  Current     430.31M            %Usage:  35.86
Write hit      81.51%
Read hit       99.96%

__ Questions ___________________________________________________________
Total          41.70M   135.1/s
  DMS          16.47M    53.4/s  %Total:  39.51
  Com_         14.42M    46.7/s           34.58
  COM_QUIT      5.82M    18.8/s           13.95
  QC Hits       4.98M    16.1/s           11.93
  +Unknown      9.00k     0.0/s            0.02
Slow 4 s        1.53M     5.0/s            3.68  %DMS:   9.31  Log:  ON
DMS            16.47M    53.4/s           39.51
  SELECT        7.88M    25.6/s           18.91         47.86
  UPDATE        4.19M    13.6/s           10.04         25.40
  INSERT        1.85M     6.0/s            4.43         11.22
  DELETE        1.48M     4.8/s            3.54          8.95
  REPLACE       1.08M     3.5/s            2.59          6.56
Com_           14.42M    46.7/s           34.58
  set_option    9.23M    29.9/s           22.15
  change_db     5.17M    16.8/s           12.41
  show_status   5.32k     0.0/s            0.01

__ SELECT and Sort _____________________________________________________
Scan           77.00k     0.2/s %SELECT:   0.98
Range         588.62k     1.9/s            7.47
Full join           0       0/s            0.00
Range check         0       0/s            0.00
Full rng join       0       0/s            0.00
Sort scan      57.31k     0.2/s
Sort range    296.54k     1.0/s
Sort mrg pass   4.61k     0.0/s

__ Query Cache _________________________________________________________
Memory usage   23.33M of 256.00M  %Used:   9.11
Block Fragmnt  24.00%
Hits            4.98M    16.1/s
Inserts         7.80M    25.3/s
Insrtrune   7.80M:1    25.3/s
Hit:Insert     0.64:1

__ Table Locks _________________________________________________________
Waited        142.25k     0.5/s  %Total:   0.67
Immediate      21.16M    68.6/s

__ Tables ______________________________________________________________
Open             2029 of 2048    %Cache:  99.07
Opened          2.63k     0.0/s

__ Connections _________________________________________________________
Max used          501 of  500      %Max: 100.20
Total           5.82M    18.9/s

__ Created Temp ________________________________________________________
Disk table      1.58k     0.0/s
Table          36.40k     0.1/s    Size: 256.0M
File            7.50k     0.0/s

__ Threads _____________________________________________________________
Running             2 of    3
Cached            126 of  128      %Hit:  99.94
Created         3.23k     0.0/s
Slow                0       0/s

__ Aborted _____________________________________________________________
Clients         1.30k     0.0/s
Connects        1.44k     0.0/s

__ Bytes _______________________________________________________________
Sent           52.39G  169.8k/s
Received        4.95G   16.1k/s

__ InnoDB Buffer Pool __________________________________________________
Usage          16.61M of 256.00M  %Used:   6.49
Read hit       97.64%
Pages
  Free         15.32k            %Total:  93.51
  Data          1.06k                      6.49 %Drty:   0.09
  Misc              0                      0.00
  Latched           0                      0.00
Reads          30.30k     0.1/s
  From file       716     0.0/s            2.36
  Ahead Rnd         7     0.0/s
  Ahead Sql         0       0/s
Writes              1     0.0/s
Flushes             0       0/s
Wait Free           0       0/s

__ InnoDB Lock _________________________________________________________
Waits               0       0/s
Current             0
Time acquiring
  Total             0 ms
  Average           0 ms
  Max               0 ms

__ InnoDB Data, Pages, Rows ____________________________________________
Data
  Reads           786     0.0/s
  Writes            4     0.0/s
  fsync             4     0.0/s
  Pending
    Reads           0
    Writes          0
    fsync           0

Pages
  Created           0       0/s
  Read          1.06k     0.0/s
  Written           0       0/s

Rows
  Deleted           0       0/s
  Inserted          0       0/s
  Read             17     0.0/s
  Updated           0       0/s

老男孩不老

=====接上===============================================
tuning-primer.sh 结果：

mysqld is alive

        -- MYSQL PERFORMANCE TUNING PRIMER --
             - By: Matthew Montgomery -

MySQL Version 5.0.51a-log x86_64

Uptime = 3 days 17 hrs 21 min 43 sec
Avg. qps = 139
Total Questions = 44727513
Threads Connected = 2

Server has been running for over 48hrs.
It should be safe to follow these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/ ... stem-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
The slow query log is enabled.
Current long_query_time = 4 sec.
You have 1638647 out of 44727566 that take longer than 4 sec. to complete
Your long_query_time seems to be fine

BINARY UPDATE LOG
The binary update log is enabled

WORKER THREADS
Current thread_cache_size = 128
Current threads_cached = 124
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 500
Current threads_connected = 2
Historic max_used_connections = 501
The number of used connections is 100% of the configured maximum.
You should raise max_connections

MEMORY USAGE
Max Memory Ever Allocated : 12 G
Configured Max Per-thread Buffers : 10 G
Configured Max Global Buffers : 1 G
Configured Max Memory Limit : 12 G
Physical Memory : 5.82 G

Max memory limit exceeds 90% of physical memory

KEY BUFFER
Current MyISAM index space = 1 G
Current key_buffer_size = 1 G
Key cache miss rate is 1 : 2529
Key buffer fill ratio = 25.00 %
Your key_buffer_size seems to be too high.
Perhaps you can use these resources elsewhere

QUERY CACHE
Query cache is enabled
Current query_cache_size = 256 M
Current query_cache_used = 25 M
Current query_cache_limit = 2 M
Current Query cache Memory fill ratio = 9.89 %
Current query_cache_min_res_unit = 4 K
Query Cache is 22 % fragmented
Run "FLUSH QUERY CACHE" periodically to defragment the query cache memory
If you have many small queries lower 'query_cache_min_res_unit' to reduce fragmentation.
Your query_cache_size seems to be too high.
Perhaps you can use these resources elsewhere
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 4 M
Current read_rnd_buffer_size = 7 M
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 4.00 M
You have had 0 queries where a join could not use an index properly
Your joins seem to be using indexes properly
join_buffer_size >= 4 M
This is not advised

OPEN FILES LIMIT
Current open_files_limit = 8192 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_cache value = 2048 tables
You have a total of 329 tables
You have 2031 open tables.
Current table_cache hit rate is 76%, while 99% of your table cache is in use
You should probably increase your table_cache

TEMP TABLES
Current max_heap_table_size = 256 M
Current tmp_table_size = 256 M
Of 41199 temp tables, 4% were created on disk
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 3 M
Current table scan ratio = 2099 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 135
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'
If you have a high concurrency of inserts on Dynamic row-length tables
consider setting 'concurrent_insert=2'.


show status结果：

show status;
+-----------------------------------+------------+
| Variable_name                     | Value      |
+-----------------------------------+------------+
| Aborted_clients                   | 1436       |
| Aborted_connects                  | 1436       |
| Binlog_cache_disk_use             | 0          |
| Binlog_cache_use                  | 0          |
| Bytes_received                    | 105        |
| Bytes_sent                        | 798        |
| Com_admin_commands                | 0          |
| Com_alter_db                      | 0          |
| Com_alter_table                   | 0          |
| Com_analyze                       | 0          |
| Com_backup_table                  | 0          |
| Com_begin                         | 0          |
| Com_call_procedure                | 0          |
| Com_change_db                     | 0          |
| Com_change_master                 | 0          |
| Com_check                         | 0          |
| Com_checksum                      | 0          |
| Com_commit                        | 0          |
| Com_create_db                     | 0          |
| Com_create_function               | 0          |
| Com_create_index                  | 0          |
| Com_create_table                  | 0          |
| Com_create_user                   | 0          |
| Com_dealloc_sql                   | 0          |
| Com_delete                        | 0          |
| Com_delete_multi                  | 0          |
| Com_do                            | 0          |
| Com_drop_db                       | 0          |
| Com_drop_function                 | 0          |
| Com_drop_index                    | 0          |
| Com_drop_table                    | 0          |
| Com_drop_user                     | 0          |
| Com_execute_sql                   | 0          |
| Com_flush                         | 0          |
| Com_grant                         | 0          |
| Com_ha_close                      | 0          |
| Com_ha_open                       | 0          |
| Com_ha_read                       | 0          |
| Com_help                          | 0          |
| Com_insert                        | 0          |
| Com_insert_select                 | 0          |
| Com_kill                          | 0          |
| Com_load                          | 0          |
| Com_load_master_data              | 0          |
| Com_load_master_table             | 0          |
| Com_lock_tables                   | 0          |
| Com_optimize                      | 0          |
| Com_preload_keys                  | 0          |
| Com_prepare_sql                   | 0          |
| Com_purge                         | 0          |
| Com_purge_before_date             | 0          |
| Com_rename_table                  | 0          |
| Com_repair                        | 0          |
| Com_replace                       | 0          |
| Com_replace_select                | 0          |
| Com_reset                         | 0          |
| Com_restore_table                 | 0          |
| Com_revoke                        | 0          |
| Com_revoke_all                    | 0          |
| Com_rollback                      | 0          |
| Com_savepoint                     | 0          |
| Com_select                        | 0          |
| Com_set_option                    | 0          |
| Com_show_binlog_events            | 0          |
| Com_show_binlogs                  | 0          |
| Com_show_charsets                 | 0          |
| Com_show_collations               | 0          |
| Com_show_column_types             | 0          |
| Com_show_create_db                | 0          |
| Com_show_create_table             | 0          |
| Com_show_databases                | 0          |
| Com_show_errors                   | 0          |
| Com_show_fields                   | 0          |
| Com_show_grants                   | 0          |
| Com_show_innodb_status            | 0          |
| Com_show_keys                     | 0          |
| Com_show_logs                     | 0          |
| Com_show_master_status            | 0          |
| Com_show_ndb_status               | 0          |
| Com_show_new_master               | 0          |
| Com_show_open_tables              | 0          |
| Com_show_privileges               | 0          |
| Com_show_processlist              | 1          |
| Com_show_slave_hosts              | 0          |
| Com_show_slave_status             | 0          |
| Com_show_status                   | 1          |
| Com_show_storage_engines          | 0          |
| Com_show_tables                   | 0          |
| Com_show_triggers                 | 0          |
| Com_show_variables                | 0          |
| Com_show_warnings                 | 0          |
| Com_slave_start                   | 0          |
| Com_slave_stop                    | 0          |
| Com_stmt_close                    | 0          |
| Com_stmt_execute                  | 0          |
| Com_stmt_fetch                    | 0          |
| Com_stmt_prepare                  | 0          |
| Com_stmt_reset                    | 0          |
| Com_stmt_send_long_data           | 0          |
| Com_truncate                      | 0          |
| Com_unlock_tables                 | 0          |
| Com_update                        | 0          |
| Com_update_multi                  | 0          |
| Com_xa_commit                     | 0          |
| Com_xa_end                        | 0          |
| Com_xa_prepare                    | 0          |
| Com_xa_recover                    | 0          |
| Com_xa_rollback                   | 0          |
| Com_xa_start                      | 0          |
| Compression                       | OFF        |
| Connections                       | 6492841    |
| Created_tmp_disk_tables           | 0          |
| Created_tmp_files                 | 8510       |
| Created_tmp_tables                | 1          |
| Delayed_errors                    | 0          |
| Delayed_insert_threads            | 0          |
| Delayed_writes                    | 0          |
| Flush_commands                    | 1          |
| Handler_commit                    | 0          |
| Handler_delete                    | 0          |
| Handler_discover                  | 0          |
| Handler_prepare                   | 0          |
| Handler_read_first                | 0          |
| Handler_read_key                  | 0          |
| Handler_read_next                 | 0          |
| Handler_read_prev                 | 0          |
| Handler_read_rnd                  | 0          |
| Handler_read_rnd_next             | 0          |
| Handler_rollback                  | 0          |
| Handler_savepoint                 | 0          |
| Handler_savepoint_rollback        | 0          |
| Handler_update                    | 0          |
| Handler_write                     | 132        |
| Innodb_buffer_pool_pages_data     | 1137       |
| Innodb_buffer_pool_pages_dirty    | 1          |
| Innodb_buffer_pool_pages_flushed  | 0          |
| Innodb_buffer_pool_pages_free     | 15247      |
| Innodb_buffer_pool_pages_latched  | 0          |
| Innodb_buffer_pool_pages_misc     | 0          |
| Innodb_buffer_pool_pages_total    | 16384      |
| Innodb_buffer_pool_read_ahead_rnd | 7          |
| Innodb_buffer_pool_read_ahead_seq | 0          |
| Innodb_buffer_pool_read_requests  | 40488      |
| Innodb_buffer_pool_reads          | 790        |
| Innodb_buffer_pool_wait_free      | 0          |
| Innodb_buffer_pool_write_requests | 1          |
| Innodb_data_fsyncs                | 4          |
| Innodb_data_pending_fsyncs        | 0          |
| Innodb_data_pending_reads         | 0          |
| Innodb_data_pending_writes        | 0          |
| Innodb_data_read                  | 20811776   |
| Innodb_data_reads                 | 860        |
| Innodb_data_writes                | 4          |
| Innodb_data_written               | 2048       |
| Innodb_dblwr_pages_written        | 0          |
| Innodb_dblwr_writes               | 0          |
| Innodb_log_waits                  | 0          |
| Innodb_log_write_requests         | 0          |
| Innodb_log_writes                 | 2          |
| Innodb_os_log_fsyncs              | 4          |
| Innodb_os_log_pending_fsyncs      | 0          |
| Innodb_os_log_pending_writes      | 0          |
| Innodb_os_log_written             | 1024       |
| Innodb_page_size                  | 16384      |
| Innodb_pages_created              | 0          |
| Innodb_pages_read                 | 1137       |
| Innodb_pages_written              | 0          |
| Innodb_row_lock_current_waits     | 0          |
| Innodb_row_lock_time              | 0          |
| Innodb_row_lock_time_avg          | 0          |
| Innodb_row_lock_time_max          | 0          |
| Innodb_row_lock_waits             | 0          |
| Innodb_rows_deleted               | 0          |
| Innodb_rows_inserted              | 0          |
| Innodb_rows_read                  | 17         |
| Innodb_rows_updated               | 0          |
| Key_blocks_not_flushed            | 0          |
| Key_blocks_unused                 | 778162     |
| Key_blocks_used                   | 264339     |
| Key_read_requests                 | 1528948458 |
| Key_reads                         | 585858     |
| Key_write_requests                | 6374528    |
| Key_writes                        | 1276182    |
| Last_query_cost                   | 0.000000   |
| Max_used_connections              | 501        |
| Not_flushed_delayed_rows          | 0          |
| Open_files                        | 2106       |
| Open_streams                      | 0          |
| Open_tables                       | 2034       |
| Opened_tables                     | 0          |
| Prepared_stmt_count               | 0          |
| Qcache_free_blocks                | 13239      |
| Qcache_free_memory                | 243810560  |
| Qcache_hits                       | 5598867    |
| Qcache_inserts                    | 8692622    |
| Qcache_lowmem_prunes              | 0          |
| Qcache_not_cached                 | 45003      |
| Qcache_queries_in_cache           | 20357      |
| Qcache_total_blocks               | 54159      |
| Questions                         | 46565932   |
| Rpl_status                        | NULL       |
| Select_full_join                  | 0          |
| Select_full_range_join            | 0          |
| Select_range                      | 0          |
| Select_range_check                | 0          |
| Select_scan                       | 1          |
| Slave_open_temp_tables            | 0          |
| Slave_retried_transactions        | 0          |
| Slave_running                     | OFF        |
| Slow_launch_threads               | 0          |
| Slow_queries                      | 0          |
| Sort_merge_passes                 | 0          |
| Sort_range                        | 0          |
| Sort_rows                         | 0          |
| Sort_scan                         | 0          |
| Table_locks_immediate             | 23537633   |
| Table_locks_waited                | 177764     |
| Tc_log_max_pages_used             | 0          |
| Tc_log_page_size                  | 0          |
| Tc_log_page_waits                 | 0          |
| Threads_cached                    | 124        |
| Threads_connected                 | 5          |
| Threads_created                   | 3460       |
| Threads_running                   | 3          |
| Uptime                            | 330016     |
| Uptime_since_flush_status         | 330016     |
+-----------------------------------+------------+
226 rows in set (0.00 sec)

yueliangdao0608

不知道你的前端程序怎么做的

williams2222

我的也会，
是ucenter导致的。。。
mysql> show processlist;
+------+---------+-----------+------+---------+-------+-------+------------------+
| Id   | User    | Host      | db   | Command | Time  | State | Info             |
+------+---------+-----------+------+---------+-------+-------+------------------+
| 4977 | UCenter | localhost | NULL | Sleep   | 22885 |       | NULL             |
| 5137 | UCenter | localhost | NULL | Sleep   |  2183 |       | NULL             |
| 5165 | UCenter | localhost | NULL | Sleep   |   409 |       | NULL

老男孩不老

williams2222 ---》你的休眠线程也很多么？
yueliangdao-->就是DZ的程序经过改动一些，在线人数高峰10000左右/60MIN

interactive_timeout=100
wait_timeout=100
用的是普通的数据库连接，不是P连接！
问了程序员 基本链接都没有关闭。
杨涛兄弟，还想了解什么？
对了，还有采用的是myisam表引擎。
原来用的innodb引擎用的挺好，后来升级的DZ改了MYISAM引擎了。就出现了这问题。

[ 本帖最后由 老男孩不老 于 2008-12-4 23:13 编辑 ]

yueliangdao0608

建议一些表改为INNODB。
我记得我原来公司的DISCUZ库里面的大概有三分之一的表引擎我改了成INNODB.
其他的是MYISAM. 然后在线人数9000多。

不知道你们是怎么修改的程序。

不行的话就设置一下wait_timeout参数吧。

老男孩不老

感谢感谢!timiout改了50了
interactive_timeout=50
wait_timeout=50
今天在观察下吧.
另外:哪些表改INNODB了?还记得么?

willko

我想問的是你的dz是不是用ucenter的？

是的話在頭文件加上這句。。
define('UC_DBCONNECT', false);

willko

在config.inc.php里。。
順便看下
$pconnect是不是0？

老男孩不老

谢谢 willko ! 我看一看