[x]
Posted via EE Mobile

Search, ask, and monitor your questions on the go with EE Mobile. Visit Experts Exchange from your mobile device and never be out of touch again.
Question
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!
9.3

MySQL - kill persistent connections (or timeout)

Asked by lbdev in MySQL Server

Tags: mysql

How can I kill sleeping connections to MySQL 5.1?  Obviously the application code needs to close the connection, but on a server level how can I ensure that sleeping connections are kept to a minimum?

Here is my.cnf: 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:
194:
195:
196:
197:
198:
199:
200:
201:
202:
203:
204:
205:
206:
207:
208:
209:
210:
211:
212:
213:
214:
215:
216:
217:
218:
219:
220:
221:
222:
223:
224:
225:
226:
227:
228:
229:
230:
231:
232:
233:
234:
235:
236:
237:
238:
239:
240:
241:
242:
243:
244:
245:
246:
247:
248:
249:
250:
251:
252:
253:
254:
255:
256:
257:
258:
259:
260:
261:
262:
263:
264:
265:
266:
267:
268:
269:
270:
271:
272:
273:
274:
275:
276:
277:

mysql> show variables;
+-----------------------------------------+-------------------------------------------------------------------------------------------+
| Variable_name                           | Value                                                                                     |
+-----------------------------------------+-------------------------------------------------------------------------------------------+
| auto_increment_increment                | 1                                                                                         | 
| auto_increment_offset                   | 1                                                                                         | 
| autocommit                              | ON                                                                                        | 
| automatic_sp_privileges                 | ON                                                                                        | 
| back_log                                | 50                                                                                        | 
| basedir                                 | /                                                                                         | 
| big_tables                              | OFF                                                                                       | 
| binlog_cache_size                       | 32768                                                                                     | 
| binlog_format                           | STATEMENT                                                                                 | 
| bulk_insert_buffer_size                 | 8388608                                                                                   | 
| character_set_client                    | latin1                                                                                    | 
| character_set_connection                | latin1                                                                                    | 
| character_set_database                  | latin1                                                                                    | 
| character_set_filesystem                | binary                                                                                    | 
| character_set_results                   | latin1                                                                                    | 
| character_set_server                    | latin1                                                                                    | 
| character_set_system                    | utf8                                                                                      | 
| character_sets_dir                      | /usr/share/mysql/charsets/                                                                | 
| collation_connection                    | latin1_swedish_ci                                                                         | 
| collation_database                      | latin1_swedish_ci                                                                         | 
| collation_server                        | latin1_swedish_ci                                                                         | 
| completion_type                         | 0                                                                                         | 
| concurrent_insert                       | 1                                                                                         | 
| connect_timeout                         | 10                                                                                        | 
| datadir                                 | /var/lib/mysql/                                                                           | 
| date_format                             | %Y-%m-%d                                                                                  | 
| datetime_format                         | %Y-%m-%d %H:%i:%s                                                                         | 
| default_week_format                     | 0                                                                                         | 
| delay_key_write                         | ON                                                                                        | 
| delayed_insert_limit                    | 100                                                                                       | 
| delayed_insert_timeout                  | 300                                                                                       | 
| delayed_queue_size                      | 1000                                                                                      | 
| div_precision_increment                 | 4                                                                                         | 
| engine_condition_pushdown               | ON                                                                                        | 
| error_count                             | 0                                                                                         | 
| event_scheduler                         | OFF                                                                                       | 
| expire_logs_days                        | 0                                                                                         | 
| flush                                   | OFF                                                                                       | 
| flush_time                              | 0                                                                                         | 
| foreign_key_checks                      | ON                                                                                        | 
| ft_boolean_syntax                       | + -><()~*:""&|                                                                            | 
| ft_max_word_len                         | 84                                                                                        | 
| ft_min_word_len                         | 4                                                                                         | 
| ft_query_expansion_limit                | 20                                                                                        | 
| ft_stopword_file                        | (built-in)                                                                                | 
| general_log                             | OFF                                                                                       | 
| general_log_file                        | /var/lib/mysql/sloth.log                                                                  | 
| group_concat_max_len                    | 1024                                                                                      | 
| have_community_features                 | YES                                                                                       | 
| have_compress                           | YES                                                                                       | 
| have_crypt                              | YES                                                                                       | 
| have_csv                                | YES                                                                                       | 
| have_dynamic_loading                    | YES                                                                                       | 
| have_geometry                           | YES                                                                                       | 
| have_innodb                             | YES                                                                                       | 
| have_ndbcluster                         | NO                                                                                        | 
| have_openssl                            | DISABLED                                                                                  | 
| have_partitioning                       | YES                                                                                       | 
| have_query_cache                        | YES                                                                                       | 
| have_rtree_keys                         | YES                                                                                       | 
| have_ssl                                | DISABLED                                                                                  | 
| have_symlink                            | YES                                                                                       | 
| hostname                                | sloth.domain.com                                                                       | 
| identity                                | 0                                                                                         | 
| ignore_builtin_innodb                   | OFF                                                                                       | 
| init_connect                            |                                                                                           | 
| init_file                               |                                                                                           | 
| init_slave                              |                                                                                           | 
| innodb_adaptive_hash_index              | ON                                                                                        | 
| innodb_additional_mem_pool_size         | 31457280                                                                                  | 
| innodb_autoextend_increment             | 8                                                                                         | 
| innodb_autoinc_lock_mode                | 1                                                                                         | 
| innodb_buffer_pool_size                 | 1048576000                                                                                | 
| innodb_checksums                        | ON                                                                                        | 
| innodb_commit_concurrency               | 0                                                                                         | 
| innodb_concurrency_tickets              | 500                                                                                       | 
| innodb_data_file_path                   | ibdata1:10M:autoextend                                                                    | 
| innodb_data_home_dir                    | /var/lib/mysql/                                                                           | 
| innodb_doublewrite                      | ON                                                                                        | 
| innodb_fast_shutdown                    | 1                                                                                         | 
| innodb_file_io_threads                  | 4                                                                                         | 
| innodb_file_per_table                   | ON                                                                                        | 
| innodb_flush_log_at_trx_commit          | 0                                                                                         | 
| innodb_flush_method                     | O_DIRECT                                                                                  | 
| innodb_force_recovery                   | 0                                                                                         | 
| innodb_lock_wait_timeout                | 50                                                                                        | 
| innodb_locks_unsafe_for_binlog          | ON                                                                                        | 
| innodb_log_buffer_size                  | 12582912                                                                                  | 
| innodb_log_file_size                    | 134217728                                                                                 | 
| innodb_log_files_in_group               | 4                                                                                         | 
| innodb_log_group_home_dir               | /var/lib/mysql/                                                                           | 
| innodb_max_dirty_pages_pct              | 80                                                                                        | 
| innodb_max_purge_lag                    | 0                                                                                         | 
| innodb_mirrored_log_groups              | 1                                                                                         | 
| innodb_open_files                       | 300                                                                                       | 
| innodb_rollback_on_timeout              | OFF                                                                                       | 
| innodb_stats_on_metadata                | ON                                                                                        | 
| innodb_support_xa                       | ON                                                                                        | 
| innodb_sync_spin_loops                  | 20                                                                                        | 
| innodb_table_locks                      | ON                                                                                        | 
| innodb_thread_concurrency               | 32                                                                                        | 
| innodb_thread_sleep_delay               | 10000                                                                                     | 
| innodb_use_legacy_cardinality_algorithm | ON                                                                                        | 
| insert_id                               | 0                                                                                         | 
| interactive_timeout                     | 28800                                                                                     | 
| join_buffer_size                        | 8388608                                                                                   | 
| keep_files_on_create                    | OFF                                                                                       | 
| key_buffer_size                         | 1048576000                                                                                | 
| key_cache_age_threshold                 | 300                                                                                       | 
| key_cache_block_size                    | 1024                                                                                      | 
| key_cache_division_limit                | 100                                                                                       | 
| language                                | /usr/share/mysql/english/                                                                 | 
| large_files_support                     | ON                                                                                        | 
| large_page_size                         | 0                                                                                         | 
| large_pages                             | OFF                                                                                       | 
| last_insert_id                          | 0                                                                                         | 
| lc_time_names                           | en_US                                                                                     | 
| license                                 | GPL                                                                                       | 
| local_infile                            | ON                                                                                        | 
| locked_in_memory                        | OFF                                                                                       | 
| log                                     | OFF                                                                                       | 
| log_bin                                 | OFF                                                                                       | 
| log_bin_trust_function_creators         | OFF                                                                                       | 
| log_bin_trust_routine_creators          | OFF                                                                                       | 
| log_error                               | /var/log/mysqld.log                                                                       | 
| log_output                              | FILE                                                                                      | 
| log_queries_not_using_indexes           | OFF                                                                                       | 
| log_slave_updates                       | OFF                                                                                       | 
| log_slow_queries                        | ON                                                                                        | 
| log_warnings                            | 1                                                                                         | 
| long_query_time                         | 30.000000                                                                                 | 
| low_priority_updates                    | OFF                                                                                       | 
| lower_case_file_system                  | OFF                                                                                       | 
| lower_case_table_names                  | 0                                                                                         | 
| max_allowed_packet                      | 1048576                                                                                   | 
| max_binlog_cache_size                   | 4294963200                                                                                | 
| max_binlog_size                         | 1073741824                                                                                | 
| max_connect_errors                      | 10                                                                                        | 
| max_connections                         | 2048                                                                                      | 
| max_delayed_threads                     | 20                                                                                        | 
| max_error_count                         | 64                                                                                        | 
| max_heap_table_size                     | 16777216                                                                                  | 
| max_insert_delayed_threads              | 20                                                                                        | 
| max_join_size                           | 18446744073709551615                                                                      | 
| max_length_for_sort_data                | 1024                                                                                      | 
| max_prepared_stmt_count                 | 16382                                                                                     | 
| max_relay_log_size                      | 0                                                                                         | 
| max_seeks_for_key                       | 4294967295                                                                                | 
| max_sort_length                         | 1024                                                                                      | 
| max_sp_recursion_depth                  | 0                                                                                         | 
| max_tmp_tables                          | 32                                                                                        | 
| max_user_connections                    | 0                                                                                         | 
| max_write_lock_count                    | 4294967295                                                                                | 
| min_examined_row_limit                  | 0                                                                                         | 
| multi_range_count                       | 256                                                                                       | 
| myisam_data_pointer_size                | 6                                                                                         | 
| myisam_max_sort_file_size               | 2146435072                                                                                | 
| myisam_recover_options                  | OFF                                                                                       | 
| myisam_repair_threads                   | 1                                                                                         | 
| myisam_sort_buffer_size                 | 8388608                                                                                   | 
| myisam_stats_method                     | nulls_unequal                                                                             | 
| myisam_use_mmap                         | OFF                                                                                       | 
| net_buffer_length                       | 16384                                                                                     | 
| net_read_timeout                        | 30                                                                                        | 
| net_retry_count                         | 10                                                                                        | 
| net_write_timeout                       | 60                                                                                        | 
| new                                     | OFF                                                                                       | 
| old                                     | OFF                                                                                       | 
| old_alter_table                         | OFF                                                                                       | 
| old_passwords                           | OFF                                                                                       | 
| open_files_limit                        | 10250                                                                                     | 
| optimizer_prune_level                   | 1                                                                                         | 
| optimizer_search_depth                  | 62                                                                                        | 
| optimizer_switch                        | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on | 
| pid_file                                | /var/lib/mysql/sloth.pid                                                    | 
| plugin_dir                              | /usr/lib/mysql/plugin                                                                     | 
| port                                    | 3306                                                                                      | 
| preload_buffer_size                     | 32768                                                                                     | 
| profiling                               | OFF                                                                                       | 
| profiling_history_size                  | 15                                                                                        | 
| protocol_version                        | 10                                                                                        | 
| pseudo_thread_id                        | 28657                                                                                     | 
| query_alloc_block_size                  | 8192                                                                                      | 
| query_cache_limit                       | 2097152                                                                                   | 
| query_cache_min_res_unit                | 4096                                                                                      | 
| query_cache_size                        | 134217728                                                                                 | 
| query_cache_type                        | ON                                                                                        | 
| query_cache_wlock_invalidate            | OFF                                                                                       | 
| query_prealloc_size                     | 8192                                                                                      | 
| rand_seed1                              |                                                                                           | 
| rand_seed2                              |                                                                                           | 
| range_alloc_block_size                  | 4096                                                                                      | 
| read_buffer_size                        | 12582912                                                                                  | 
| read_only                               | OFF                                                                                       | 
| read_rnd_buffer_size                    | 262144                                                                                    | 
| relay_log                               |                                                                                           | 
| relay_log_index                         |                                                                                           | 
| relay_log_info_file                     | relay-log.info                                                                            | 
| relay_log_purge                         | ON                                                                                        | 
| relay_log_space_limit                   | 0                                                                                         | 
| report_host                             |                                                                                           | 
| report_password                         |                                                                                           | 
| report_port                             | 3306                                                                                      | 
| report_user                             |                                                                                           | 
| rpl_recovery_rank                       | 0                                                                                         | 
| secure_auth                             | OFF                                                                                       | 
| secure_file_priv                        |                                                                                           | 
| server_id                               | 0                                                                                         | 
| skip_external_locking                   | ON                                                                                        | 
| skip_networking                         | OFF                                                                                       | 
| skip_show_database                      | OFF                                                                                       | 
| slave_compressed_protocol               | OFF                                                                                       | 
| slave_exec_mode                         | STRICT                                                                                    | 
| slave_load_tmpdir                       | /tmp                                                                                      | 
| slave_net_timeout                       | 3600                                                                                      | 
| slave_skip_errors                       | OFF                                                                                       | 
| slave_transaction_retries               | 10                                                                                        | 
| slow_launch_time                        | 2                                                                                         | 
| slow_query_log                          | ON                                                                                        | 
| slow_query_log_file                     | /var/log/mysql_slow_queries.log                                                           | 
| socket                                  | /var/lib/mysql/mysql.sock                                                                 | 
| sort_buffer_size                        | 8388608                                                                                   | 
| sql_auto_is_null                        | ON                                                                                        | 
| sql_big_selects                         | ON                                                                                        | 
| sql_big_tables                          | OFF                                                                                       | 
| sql_buffer_result                       | OFF                                                                                       | 
| sql_log_bin                             | ON                                                                                        | 
| sql_log_off                             | OFF                                                                                       | 
| sql_log_update                          | ON                                                                                        | 
| sql_low_priority_updates                | OFF                                                                                       | 
| sql_max_join_size                       | 18446744073709551615                                                                      | 
| sql_mode                                |                                                                                           | 
| sql_notes                               | ON                                                                                        | 
| sql_quote_show_create                   | ON                                                                                        | 
| sql_safe_updates                        | OFF                                                                                       | 
| sql_select_limit                        | 18446744073709551615                                                                      | 
| sql_slave_skip_counter                  |                                                                                           | 
| sql_warnings                            | OFF                                                                                       | 
| ssl_ca                                  |                                                                                           | 
| ssl_capath                              |                                                                                           | 
| ssl_cert                                |                                                                                           | 
| ssl_cipher                              |                                                                                           | 
| ssl_key                                 |                                                                                           | 
| storage_engine                          | InnoDB                                                                                    | 
| sync_binlog                             | 0                                                                                         | 
| sync_frm                                | ON                                                                                        | 
| system_time_zone                        | CDT                                                                                       | 
| table_definition_cache                  | 256                                                                                       | 
| table_lock_wait_timeout                 | 50                                                                                        | 
| table_open_cache                        | 4096                                                                                      | 
| table_type                              | InnoDB                                                                                    | 
| thread_cache_size                       | 64                                                                                        | 
| thread_handling                         | one-thread-per-connection                                                                 | 
| thread_stack                            | 196608                                                                                    | 
| time_format                             | %H:%i:%s                                                                                  | 
| time_zone                               | SYSTEM                                                                                    | 
| timed_mutexes                           | OFF                                                                                       | 
| timestamp                               | 1250107638                                                                                | 
| tmp_table_size                          | 67108864                                                                                  | 
| tmpdir                                  | /tmp                                                                                      | 
| transaction_alloc_block_size            | 8192                                                                                      | 
| transaction_prealloc_size               | 4096                                                                                      | 
| tx_isolation                            | REPEATABLE-READ                                                                           | 
| unique_checks                           | ON                                                                                        | 
| updatable_views_with_limit              | YES                                                                                       | 
| version                                 | 5.1.35-community-log                                                                      | 
| version_comment                         | MySQL Community Server (GPL)                                                              | 
| version_compile_machine                 | i686                                                                                      | 
| version_compile_os                      | pc-linux-gnu                                                                              | 
| wait_timeout                            | 28800                                                                                     | 
| warning_count                           | 0                                                                                         | 
+-----------------------------------------+-------------------------------------------------------------------------------------------+
271 rows in set (0.00 sec)
 
Related Solutions
Keywords: MySQL - kill persistent connections (…
Title
1 freebsd 6.2 randomly locks up
 
Loading Advertisement...
 
[+][-]08/12/09 01:28 PM, ID: 25082572Accepted Solution

View this solution now by starting your 30-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

About this solution

Zone: MySQL Server
Tags: mysql
Sign Up Now!
Solution Provided By: nemws1
Participating Experts: 1
Solution Grade: A
 
 
Loading Advertisement...
20091021-EE-VQP-81 - Hierarchy / EE_QW_3_20080625