Java项目调优

在大数据计算的项目中，启动300个线程测试。线程可以使用完毕，但是会爆出连接池的链接为空。

configration

1. Druid

## 日志配置
filters=stat,slf4j
## 最大连接数
maxActive=800
## 最小空闲连接数
minIdle=20
## 初始化连接大小
initialSize=10
## 等待500 ms
maxWait=500
## sql 关闭
logSlowSql=false
## 毫秒数
slowSqlMillis=3000
timeBetweenEvictionRunsMillis=100
minEvictableIdleTimeMillis=300000
poolPreparedStatements=true
maxOpenPreparedStatements=100

2. MySQL

[mysqld]
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
datadir     = /var/lib/mysql
# log
log-error   = /var/log/mysql/error.log
#log = /var/log/mysql/log.log
#long_query_time = 3

wait_timeout    = 100
interactive_timeout = 100
connect_timeout = 20

max_connections = 1500
max_connect_errors = 128
# By default we only accept connections from localhost
#bind-address   = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

MySQL现状

1. Aborted_clients=38
2. Aborted_connects=1
3. Binlog_cache_disk_use=0
4. Binlog_cache_use=0
5. Binlog_stmt_cache_disk_use=0
6. Binlog_stmt_cache_use=0
7. Bytes_received=237
8. Bytes_sent=181
9. Com_admin_commands=0
10. Com_assign_to_keycache=0
11. Com_alter_db=0
12. Com_alter_db_upgrade=0
13. Com_alter_event=0
14. Com_alter_function=0
15. Com_alter_instance=0
16. Com_alter_procedure=0
17. Com_alter_server=0
18. Com_alter_table=0
19. Com_alter_tablespace=0
20. Com_alter_user=0
21. Com_analyze=0
22. Com_begin=0
23. Com_binlog=0
24. Com_call_procedure=0
25. Com_change_db=0
26. Com_change_master=0
27. Com_change_repl_filter=0
28. Com_check=0
29. Com_checksum=0
30. Com_commit=0
31. Com_create_db=0
32. Com_create_event=0
33. Com_create_function=0
34. Com_create_index=0
35. Com_create_procedure=0
36. Com_create_server=0
37. Com_create_table=0
38. Com_create_trigger=0
39. Com_create_udf=0
40. Com_create_user=0
41. Com_create_view=0
42. Com_dealloc_sql=0
43. Com_delete=0
44. Com_delete_multi=0
45. Com_do=0
46. Com_drop_db=0
47. Com_drop_event=0
48. Com_drop_function=0
49. Com_drop_index=0
50. Com_drop_procedure=0
51. Com_drop_server=0
52. Com_drop_table=0
53. Com_drop_trigger=0
54. Com_drop_user=0
55. Com_drop_view=0
56. Com_empty_query=0
57. Com_execute_sql=0
58. Com_explain_other=0
59. Com_flush=0
60. Com_get_diagnostics=0
61. Com_grant=0
62. Com_ha_close=0
63. Com_ha_open=0
64. Com_ha_read=0
65. Com_help=0
66. Com_insert=0
67. Com_insert_select=0
68. Com_install_plugin=0
69. Com_kill=0
70. Com_load=0
71. Com_lock_tables=0
72. Com_optimize=0
73. Com_preload_keys=0
74. Com_prepare_sql=0
75. Com_purge=0
76. Com_purge_before_date=0
77. Com_release_savepoint=0
78. Com_rename_table=0
79. Com_rename_user=0
80. Com_repair=0
81. Com_replace=0
82. Com_replace_select=0
83. Com_reset=0
84. Com_resignal=0
85. Com_revoke=0
86. Com_revoke_all=0
87. Com_rollback=0
88. Com_rollback_to_savepoint=0
89. Com_savepoint=0
90. Com_select=1
91. Com_set_option=0
92. Com_signal=0
93. Com_show_binlog_events=0
94. Com_show_binlogs=0
95. Com_show_charsets=0
96. Com_show_collations=0
97. Com_show_create_db=0
98. Com_show_create_event=0
99. Com_show_create_func=0
100. Com_show_create_proc=0
101. Com_show_create_table=0
102. Com_show_create_trigger=0
103. Com_show_databases=0
104. Com_show_engine_logs=0
105. Com_show_engine_mutex=0
106. Com_show_engine_status=0
107. Com_show_events=0
108. Com_show_errors=0
109. Com_show_fields=0
110. Com_show_function_code=0
111. Com_show_function_status=0
112. Com_show_grants=0
113. Com_show_keys=0
114. Com_show_master_status=0
115. Com_show_open_tables=0
116. Com_show_plugins=0
117. Com_show_privileges=0
118. Com_show_procedure_code=0
119. Com_show_procedure_status=0
120. Com_show_processlist=0
121. Com_show_profile=0
122. Com_show_profiles=0
123. Com_show_relaylog_events=0
124. Com_show_slave_hosts=0
125. Com_show_slave_status=0
126. Com_show_status=1
127. Com_show_storage_engines=0
128. Com_show_table_status=0
129. Com_show_tables=0
130. Com_show_triggers=0
131. Com_show_variables=0
132. Com_show_warnings=0
133. Com_show_create_user=0
134. Com_shutdown=0
135. Com_slave_start=0
136. Com_slave_stop=0
137. Com_group_replication_start=0
138. Com_group_replication_stop=0
139. Com_stmt_execute=0
140. Com_stmt_close=0
141. Com_stmt_fetch=0
142. Com_stmt_prepare=0
143. Com_stmt_reset=0
144. Com_stmt_send_long_data=0
145. Com_truncate=0
146. Com_uninstall_plugin=0
147. Com_unlock_tables=0
148. Com_update=0
149. Com_update_multi=0
150. Com_xa_commit=0
151. Com_xa_end=0
152. Com_xa_prepare=0
153. Com_xa_recover=0
154. Com_xa_rollback=0
155. Com_xa_start=0
156. Com_stmt_reprepare=0
157. Compression=OFF
158. Connection_errors_accept=0
159. Connection_errors_internal=0
160. Connection_errors_max_connections=0
161. Connection_errors_peer_address=0
162. Connection_errors_select=0
163. Connection_errors_tcpwrap=0
164. Connections=74
165. Created_tmp_disk_tables=0
166. Created_tmp_files=6
167. Created_tmp_tables=0
168. Delayed_errors=0
169. Delayed_insert_threads=0
170. Delayed_writes=0
171. Flush_commands=1
172. Handler_commit=0
173. Handler_delete=0
174. Handler_discover=0
175. Handler_external_lock=0
176. Handler_mrr_init=0
177. Handler_prepare=0
178. Handler_read_first=0
179. Handler_read_key=0
180. Handler_read_last=0
181. Handler_read_next=0
182. Handler_read_prev=0
183. Handler_read_rnd=0
184. Handler_read_rnd_next=0
185. Handler_rollback=0
186. Handler_savepoint=0
187. Handler_savepoint_rollback=0
188. Handler_update=0
189. Handler_write=0
190. Innodb_buffer_pool_dump_status=Dumping of buffer pool not started
191. Innodb_buffer_pool_load_status=Buffer pool(s) load completed at 171014 19:58:34
192. Innodb_buffer_pool_resize_status=
193. Innodb_buffer_pool_pages_data=470
194. Innodb_buffer_pool_bytes_data=7700480
195. Innodb_buffer_pool_pages_dirty=0
196. Innodb_buffer_pool_bytes_dirty=0
197. Innodb_buffer_pool_pages_flushed=37
198. Innodb_buffer_pool_pages_free=7718
199. Innodb_buffer_pool_pages_misc=3
200. Innodb_buffer_pool_pages_total=8191
201. Innodb_buffer_pool_read_ahead_rnd=0
202. Innodb_buffer_pool_read_ahead=0
203. Innodb_buffer_pool_read_ahead_evicted=0
204. Innodb_buffer_pool_read_requests=95546
205. Innodb_buffer_pool_reads=436
206. Innodb_buffer_pool_wait_free=0
207. Innodb_buffer_pool_write_requests=515
208. Innodb_data_fsyncs=7
209. Innodb_data_pending_fsyncs=0
210. Innodb_data_pending_reads=0
211. Innodb_data_pending_writes=0
212. Innodb_data_read=7213568
213. Innodb_data_reads=474
214. Innodb_data_writes=54
215. Innodb_data_written=641024
216. Innodb_dblwr_pages_written=2
217. Innodb_dblwr_writes=1
218. Innodb_log_waits=0
219. Innodb_log_write_requests=0
220. Innodb_log_writes=2
221. Innodb_os_log_fsyncs=4
222. Innodb_os_log_pending_fsyncs=0
223. Innodb_os_log_pending_writes=0
224. Innodb_os_log_written=1024
225. Innodb_page_size=16384
226. Innodb_pages_created=35
227. Innodb_pages_read=435
228. Innodb_pages_written=37
229. Innodb__lock_current_waits=0
230. Innodb__lock_time=0
231. Innodb__lock_time_avg=0
232. Innodb__lock_time_max=0
233. Innodb__lock_waits=0
234. Innodb_s_deleted=0
235. Innodb_s_inserted=0
236. Innodb_s_read=721794
237. Innodb_s_updated=0
238. Innodb_num_open_files=30
239. Innodb_truncated_status_writes=0
240. Innodb_available_undo_logs=128
241. Key_blocks_not_flushed=0
242. Key_blocks_unused=7245
243. Key_blocks_used=3
244. Key_read_requests=6
245. Key_reads=3
246. Key_write_requests=0
247. Key_writes=0
248. Last_query_cost=0.000000
249. Last_query_partial_plans=0
250. Locked_connects=0
251. Max_execution_time_exceeded=0
252. Max_execution_time_set=0
253. Max_execution_time_set_failed=0
254. Max_used_connections=40
255. Max_used_connections_time=2017-10-14 19:59:48
256. Not_flushed_delayed_s=0
257. Ongoing_anonymous_transaction_count=0
258. Open_files=14
259. Open_streams=0
260. Open_table_definitions=257
261. Open_tables=161
262. Opened_files=391
263. Opened_table_definitions=0
264. Opened_tables=0
265. Performance_schema_accounts_lost=0
266. Performance_schema_cond_classes_lost=0
267. Performance_schema_cond_instances_lost=0
268. Performance_schema_digest_lost=0
269. Performance_schema_file_classes_lost=0
270. Performance_schema_file_handles_lost=0
271. Performance_schema_file_instances_lost=0
272. Performance_schema_hosts_lost=0
273. Performance_schema_index_stat_lost=0
274. Performance_schema_locker_lost=0
275. Performance_schema_memory_classes_lost=0
276. Performance_schema_metadata_lock_lost=0
277. Performance_schema_mutex_classes_lost=0
278. Performance_schema_mutex_instances_lost=0
279. Performance_schema_nested_statement_lost=0
280. Performance_schema_prepared_statements_lost=0
281. Performance_schema_program_lost=0
282. Performance_schema_rwlock_classes_lost=0
283. Performance_schema_rwlock_instances_lost=0
284. Performance_schema_session_connect_attrs_lost=0
285. Performance_schema_socket_classes_lost=0
286. Performance_schema_socket_instances_lost=0
287. Performance_schema_stage_classes_lost=0
288. Performance_schema_statement_classes_lost=0
289. Performance_schema_table_handles_lost=0
290. Performance_schema_table_instances_lost=0
291. Performance_schema_table_lock_stat_lost=0
292. Performance_schema_thread_classes_lost=0
293. Performance_schema_thread_instances_lost=0
294. Performance_schema_users_lost=0
295. Prepared_stmt_count=0
296. Qcache_free_blocks=1
297. Qcache_free_memory=1040184
298. Qcache_hits=0
299. Qcache_inserts=0
300. Qcache_lowmem_prunes=0
301. Qcache_not_cached=1316
302. Qcache_queries_in_cache=0
303. Qcache_total_blocks=1
304. Queries=1823
305. Questions=2
306. Select_full_join=0
307. Select_full_range_join=0
308. Select_range=0
309. Select_range_check=0
310. Select_scan=0
311. Slave_open_temp_tables=0
312. Slow_launch_threads=0
313. Slow_queries=0
314. Sort_merge_passes=0
315. Sort_range=0
316. Sort_s=0
317. Sort_scan=0
318. Ssl_accept_renegotiates=0
319. Ssl_accepts=0
320. Ssl_callback_cache_hits=0
321. Ssl_cipher=
322. Ssl_cipher_list=
323. Ssl_client_connects=0
324. Ssl_connect_renegotiates=0
325. Ssl_ctx_verify_depth=0
326. Ssl_ctx_verify_mode=0
327. Ssl_default_timeout=0
328. Ssl_finished_accepts=0
329. Ssl_finished_connects=0
330. Ssl_server_not_after=Jul 29 01:20:13 2027 GMT
331. Ssl_server_not_before=Jul 31 01:20:13 2017 GMT
332. Ssl_session_cache_hits=0
333. Ssl_session_cache_misses=0
334. Ssl_session_cache_mode=Unknown
335. Ssl_session_cache_overflows=0
336. Ssl_session_cache_size=0
337. Ssl_session_cache_timeouts=0
338. Ssl_sessions_reused=0
339. Ssl_used_session_cache_entries=0
340. Ssl_verify_depth=0
341. Ssl_verify_mode=0
342. Ssl_version=
343. Table_locks_immediate=168
344. Table_locks_waited=0
345. Table_open_cache_hits=0
346. Table_open_cache_misses=0
347. Table_open_cache_overflows=0
348. Tc_log_max_pages_used=0
349. Tc_log_page_size=0
350. Tc_log_page_waits=0
351. Threads_cached=0
352. Threads_connected=30
353. Threads_created=47
354. Threads_running=1
355. Uptime=695
356. Uptime_since_flush_status=695

配置解析

1. Druid
   DruidDataSource配置兼容DBCP，但个别配置的语意有所区别。