Mysql 有大量的 3306 Timewait 怎么优化？

Question

Mysql 有大量的 3306 Timewait 怎么优化？配置如下kip-name-resolve
max_connections=1500
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
connect_timeout=5
wait_timeout=5
server-id = 1 
[isamchk] 
set-variable = key_buffer=128M 
set-variable = sort_buffer=128M 
set-variable = read_buffer=2M 
set-variable = write_buffer=2M 
[myisamchk] 
set-variable = key_buffer=128M 
set-variable = sort_buffer=128M 
set-variable = read_buffer=2M 
set-variable = write_buffer=2M 

Answer 1

你的wait_timeout 已经很小了！确认你的程序是不是合理的关闭了数据库链接！

Answer 2

我修改了服务器的Timeout 值 有所好转 我内存1Gkey_buffer = 384M 
 会有我问题吗

Answer 3

+-----------------------------------+----------+
| Variable_name                     | Value    |
+-----------------------------------+----------+
| Aborted_clients                   | 250      |
| Aborted_connects                  | 0        |
| Binlog_cache_disk_use             | 0        |
| Binlog_cache_use                  | 0        |
| Bytes_received                    | 115      |
| Bytes_sent                        | 180      |
| Com_admin_commands                | 0        |
| Com_show_processlist              | 0        |
| Com_show_slave_hosts              | 0        |
| Com_show_slave_status             | 0        |
| Com_show_status                   | 1        |
| Com_show_storage_engines          | 0        |
| Compression                       | OFF      |
| Connections                       | 20234    |
| Created_tmp_disk_tables           | 0        |
| Created_tmp_files                 | 4        |
| Created_tmp_tables                | 1        |
| Delayed_errors                    | 0        |
| Delayed_insert_threads            | 0        |
| Delayed_writes                    | 0        |
| Flush_commands                    | 1        |
| Handler_commit                    | 0        |
| Handler_update                    | 0        |
| Handler_write                     | 132      |
| Innodb_buffer_pool_pages_data     | 0        |
| Innodb_buffer_pool_pages_dirty    | 0        |
| Innodb_buffer_pool_pages_flushed  | 0        |
| Innodb_buffer_pool_pages_free     | 512      |
| Innodb_buffer_pool_pages_latched  | 0        |
| Innodb_buffer_pool_pages_misc     | 0        |
| Innodb_buffer_pool_pages_total    | 512      |
| Innodb_buffer_pool_read_ahead_rnd | 0        |
| Innodb_page_size                  | 16384    |
| Innodb_pages_created              | 0        |
| Innodb_pages_read                 | 0        |
| Innodb_pages_written              | 0        |
| Innodb_rows_updated               | 0        |
| Key_blocks_not_flushed            | 0        |
| Key_blocks_unused                 | 229069   |
| Key_blocks_used                   | 1116     |
| Key_read_requests                 | 31598980 |
| Key_reads                         | 5115     |
| Key_write_requests                | 5276     |
| Key_writes                        | 4939     |
| Last_query_cost                   | 0.000000 |
| Max_used_connections              | 12       |
| Not_flushed_delayed_rows          | 0        |
| Open_files                        | 205      |
| Open_streams                      | 0        |
| Open_tables                       | 122      |
| Opened_tables                     | 0        |
| Prepared_stmt_count               | 0        |
| Qcache_free_blocks                | 3919     |
| Qcache_free_memory                | 10512464 |
| Qcache_hits                       | 17266262 |
| Qcache_inserts                    | 2953620  |
| Qcache_lowmem_prunes              | 245225   |
| Qcache_not_cached                 | 226081   |
| Qcache_queries_in_cache           | 5099     |
| Qcache_total_blocks               | 14169    |
| Questions                         | 20980543 |
| 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        |
| Ssl_session_cache_misses          | 0        |
| Ssl_session_cache_mode            | NONE     |
| Ssl_session_cache_overflows       | 0        |
| Ssl_session_cache_size            | 0        |
| Ssl_session_cache_timeouts        | 0        |
| Ssl_sessions_reused               | 0        |
| Ssl_used_session_cache_entries    | 0        |
| Ssl_verify_depth                  | 0        |
| Ssl_verify_mode                   | 0        |
| Ssl_version                       |          |
| Table_locks_immediate             | 3585381  |
| Table_locks_waited                | 45991    |
| Tc_log_max_pages_used             | 0        |
| Tc_log_page_size                  | 0        |
| Tc_log_page_waits                 | 0        |
| Threads_cached                    | 7        |
| Threads_connected                 | 1        |
| Threads_created                   | 24       |
| Threads_running                   | 1        |
| Uptime                            | 5792     |
| Uptime_since_flush_status         | 5792     |
+-----------------------------------+----------+
249 rows in set (0.00 sec)

Answer 4

| Table_locks_immediate | 4073691 |
| Table_locks_waited    | 47567   |

Answer 5

看出来两点：
1、query_cache_size 太小。
2、SQL语句不够优化！

Answer 6

SQL语句不够优化   这个我就无力了。。