Discussion :

[maty2006]

Salut à tous,
Voilà mon serveur MySQL 5 sur Windows me bouffe toutes les ressources du processeur, savez-vous pourquoi ?
Merci d'avance.

[Maximil ian]

Salut

Ca le fait même quand tu le redémarres ?

[maty2006]

Non quant j'ai à peux prêt 50 connectés sur mon site, par contre apache 2 lui tout va bien.

[Maximil ian]

C'est quel type de serveur ? Quel OS ? Tu utilises des connexions persistantes ?

[maty2006]

C'est quel type de serveur ?
Serveur WEB :
- http://www.tchatteurs.net
- http://www.allactu.com
DELL :
- Pentium4 2000 Mhz.
- 1 Giga DDR PC 3200 Kingston.
Quel OS ?
- Windows XP PRO SP2.
Tu utilises des connexions persistantes ?
- J'utilise toujours "mysql_close();" à la fin de mes scripts PHP.

Merci d'avance.

[Maximil ian]

Essaie de voir s'il n'y a pas des requêtes ou des procédures qui monopolisent les ressources : SHOW STATUS, SHOW PROCESSLIST, activation du slow query log...

[maty2006]

Salut Maximilian,
Voici les résultats des requetes que tu ma proposé :
Pour SHOW STATUS; il m'affiche ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
Variable_name  	 Value
Aborted_clients 	216
Aborted_connects 	0
Binlog_cache_disk_use 	0
Binlog_cache_use 	0
Bytes_received 	462
Bytes_sent 	7668
Com_admin_commands 	0
Com_alter_db 	0
Com_alter_table 	0
Com_analyze 	0
Com_backup_table 	0
Com_begin 	0
Com_change_db 	2
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_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 	2
Com_set_option 	4
Com_show_binlog_events 	0
Com_show_binlogs 	0
Com_show_charsets 	1
Com_show_collations 	1
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 	1
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 	0
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 	2
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
Variable_name 	Value
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 	616902
Created_tmp_disk_tables 	0
Created_tmp_files 	13280
Created_tmp_tables 	5
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 	168
Handler_rollback 	0
Handler_savepoint 	0
Handler_savepoint_rollback 	0
Handler_update 	0
Handler_write 	294
Innodb_buffer_pool_pages_data 	0
Innodb_buffer_pool_pages_dirty 	0
Innodb_buffer_pool_pages_flushed 	0
Innodb_buffer_pool_pages_free 	0
Innodb_buffer_pool_pages_latched 	0
Innodb_buffer_pool_pages_misc 	0
Innodb_buffer_pool_pages_total 	0
Innodb_buffer_pool_read_ahead_rnd 	0
Innodb_buffer_pool_read_ahead_seq 	0
Innodb_buffer_pool_read_requests 	0
Innodb_buffer_pool_reads 	0
Innodb_buffer_pool_wait_free 	0
Innodb_buffer_pool_write_requests 	0
Innodb_data_fsyncs 	0
Innodb_data_pending_fsyncs 	0
Innodb_data_pending_reads 	0
Innodb_data_pending_writes 	0
Innodb_data_read 	0
Innodb_data_reads 	0
Innodb_data_writes 	0
Innodb_data_written 	0
Innodb_dblwr_pages_written 	0
Innodb_dblwr_writes 	0
Innodb_log_waits 	0
Innodb_log_write_requests 	0
Innodb_log_writes 	0
Innodb_os_log_fsyncs 	0
Innodb_os_log_pending_fsyncs 	0
Innodb_os_log_pending_writes 	0
Innodb_os_log_written 	0
Innodb_page_size 	0
Innodb_pages_created 	0
Innodb_pages_read 	0
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 	0
Innodb_rows_updated 	0
Key_blocks_not_flushed 	0
Key_blocks_unused 	9069
Key_blocks_used 	4939
Key_read_requests 	58913823
Key_reads 	235449
Key_write_requests 	32867
Key_writes 	24969
Last_query_cost 	0.000000
Max_used_connections 	79
Not_flushed_delayed_rows 	0
Open_files 	46
Open_streams 	0
Open_tables 	29
Opened_tables 	0
Qcache_free_blocks 	0
Qcache_free_memory 	0
Qcache_hits 	0
Qcache_inserts 	0
Qcache_lowmem_prunes 	0
Qcache_not_cached 	0
Qcache_queries_in_cache 	0
Qcache_total_blocks 	0
Questions 	5707107
Rpl_status 	NULL
Select_full_join 	0
Select_full_range_join 	0
Variable_name 	Value
Select_range 	0
Select_range_check 	0
Select_scan 	5
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_accept_renegotiates 	0
Ssl_accepts 	0
Ssl_callback_cache_hits 	0
Ssl_cipher 	 
Ssl_cipher_list 	 
Ssl_client_connects 	0
Ssl_connect_renegotiates 	0
Ssl_ctx_verify_depth 	0
Ssl_ctx_verify_mode 	0
Ssl_default_timeout 	0
Ssl_finished_accepts 	0
Ssl_finished_connects 	0
Ssl_session_cache_hits 	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 	4632683
Table_locks_waited 	82
Tc_log_max_pages_used 	0
Tc_log_page_size 	0
Tc_log_page_waits 	0
Threads_cached 	6
Threads_connected 	65
Threads_created 	479
Threads_running 	2
Uptime 	283097

Pour SHOW PROCESSLIST; il m'affiche ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
405  	root  	localhost:1436  	allactu  	Sleep  	109  	---  	---
606 	root 	localhost:1636 	allactu 	Sleep 	23 	--- 	---
49150 	root 	localhost:3485 	allactu 	Sleep 	6608 	--- 	---
120830 	root 	localhost:4992 	allactu 	Sleep 	16321 	--- 	---
127944 	root 	localhost:4297 	allactu 	Sleep 	4998 	--- 	---
138917 	root 	localhost:3577 	allactu 	Sleep 	7716 	--- 	---
221896 	root 	localhost:4761 	allactu 	Sleep 	1154 	--- 	---
226841 	root 	localhost:1875 	allactu 	Sleep 	2087 	--- 	---
299219 	root 	localhost:4431 	allactu 	Sleep 	999 	--- 	---
299248 	root 	localhost:4462 	allactu 	Sleep 	1600 	--- 	---
305876 	root 	localhost:3287 	allactu 	Sleep 	1414 	--- 	---
307719 	root 	localhost:3672 	allactu 	Sleep 	3231 	--- 	---
307732 	root 	localhost:3694 	allactu 	Sleep 	18723 	--- 	---
307736 	root 	localhost:3698 	allactu 	Sleep 	2154 	--- 	---
308928 	root 	localhost:1627 	allactu 	Sleep 	323 	--- 	---
309234 	root 	localhost:2054 	allactu 	Sleep 	17419 	--- 	---
309481 	root 	localhost:2451 	allactu 	Sleep 	3749 	--- 	---
309905 	root 	localhost:3167 	allactu 	Sleep 	7510 	--- 	---
310402 	root 	localhost:3942 	allactu 	Sleep 	5866 	--- 	---
313329 	root 	localhost:2993 	allactu 	Sleep 	2004 	--- 	---
313461 	root 	localhost:3128 	allactu 	Sleep 	16294 	--- 	---
318624 	root 	localhost:4446 	allactu 	Sleep 	422 	--- 	---
321704 	root 	localhost:3642 	allactu 	Sleep 	878 	--- 	---
422406 	root 	localhost:3741 	allactu 	Sleep 	3639 	--- 	---
425319 	root 	localhost:2769 	allactu 	Sleep 	17 	--- 	---
470285 	root 	localhost:4941 	allactu 	Sleep 	998 	--- 	---
471587 	root 	localhost:2299 	allactu 	Sleep 	535 	--- 	---
471608 	root 	localhost:2320 	allactu 	Sleep 	247 	--- 	---
471627 	root 	localhost:2339 	allactu 	Sleep 	2140 	--- 	---
471638 	root 	localhost:2350 	allactu 	Sleep 	1295 	--- 	---
474103 	root 	localhost:4877 	allactu 	Sleep 	3639 	--- 	---
475587 	root 	localhost:2421 	allactu 	Sleep 	814 	--- 	---
476170 	root 	localhost:3014 	allactu 	Sleep 	3847 	--- 	---
476193 	root 	localhost:3038 	allactu 	Sleep 	6384 	--- 	---
485680 	root 	localhost:4812 	allactu 	Sleep 	8891 	--- 	---
486430 	root 	localhost:1619 	allactu 	Sleep 	1333 	--- 	---
486753 	root 	localhost:1953 	allactu 	Sleep 	14921 	--- 	---
486871 	root 	localhost:2072 	allactu 	Sleep 	1331 	--- 	---
487508 	root 	localhost:2735 	allactu 	Sleep 	8894 	--- 	---
487605 	root 	localhost:2837 	allactu 	Sleep 	14469 	--- 	---
488471 	root 	localhost:3736 	allactu 	Sleep 	6146 	--- 	---
489205 	root 	localhost:4491 	allactu 	Sleep 	4745 	--- 	---
491253 	root 	localhost:2621 	allactu 	Sleep 	16537 	--- 	---
491741 	root 	localhost:3118 	allactu 	Sleep 	98 	--- 	---
492302 	root 	localhost:3702 	allactu 	Sleep 	1884 	--- 	---
492837 	root 	localhost:4250 	allactu 	Sleep 	243 	--- 	---
493387 	root 	localhost:4810 	allactu 	Sleep 	177 	--- 	---
494573 	root 	localhost:2053 	allactu 	Sleep 	551 	--- 	---
497852 	root 	localhost:1465 	allactu 	Sleep 	756 	--- 	---
497907 	root 	localhost:1522 	allactu 	Sleep 	3223 	--- 	---
498540 	root 	localhost:2175 	allactu 	Sleep 	615 	--- 	---
499956 	root 	localhost:3632 	allactu 	Sleep 	1463 	--- 	---
499967 	root 	localhost:3645 	allactu 	Sleep 	4118 	--- 	---
500193 	root 	localhost:3881 	allactu 	Sleep 	735 	--- 	---
500966 	root 	localhost:4666 	allactu 	Sleep 	1463 	--- 	---
501313 	root 	localhost:1047 	allactu 	Sleep 	1793 	--- 	---
501998 	root 	localhost:1748 	allactu 	Sleep 	19 	--- 	---
502410 	root 	localhost:2170 	allactu 	Sleep 	12371 	--- 	---
562428 	root 	localhost:4005 	allactu 	Sleep 	7727 	--- 	---
567753 	root 	localhost:1515 	allactu 	Sleep 	1463 	--- 	---
575707 	root 	localhost:1835 	allactu 	Sleep 	638 	--- 	---
579468 	root 	localhost:1740 	allactu 	Sleep 	7785 	--- 	---
584522 	root 	localhost:2967 	allactu 	Sleep 	9678 	--- 	---
617552  root  	localhost:1130  tchatteurs  	Query  	0  	freeing items  	UPDATE membres SET `Statut` = 'Hors ligne',`DerniereConnexion` = '12-06-2006 � 23:57:31', `Online `
617553 	root 	localhost:1132 	mysql 	Query 	0 	--- 	SHOW PROCESSLIST
617554 	root 	localhost:1131 	tchatteurs 	Query 	0 	freeing items 	SELECT SUM( `Pseudo2` = 'benxbox' AND `Vue` LIKE '0' AND `Sup` LIKE '0' ) AS Total FROM messages

Merci d'avance.

[Maximil ian]

Il faudrait que tu fasses le SHOW PROCCESLIST au moment où le problème de ressources se manifeste.

Si tu killes tous les threads qui sont à sleep (cf KILL) ça change quelque chose ?

[maty2006]

Non pas vraiment

[silef]

J'ai le même soucis avec un MySQL 5.0.37-community-nt sur Windows 2003, xeon 2.8 avec 4Go de Ram.

Par contre comme le serveur est en prod j'ai du déporter les bases rapidement et n'ai pas eu le temps de faire de SHOW STATUS, SHOW PROCESSLIST.

Par contre le slow query log étant activé j'ai pu regarder, et c'est vrai que j'en ai pas mal qui ont été mises dedans.

Merci pour votre aide.

[silef]

J'apporte quelques nouveaux éléments :

- le slow query log contient ce genre de ligne

# Time: 070412 21:13:21
# User@Host: *#loginSQL#*[*#loginSQL#*] @ localhost [127.0.0.1]
# Query_time: 11 Lock_time: 0 Rows_sent: 5 Rows_examined: 395292

# Time: 070412 21:13:23
# User@Host: *#loginSQL#*[*#loginSQL#*] @ localhost [127.0.0.1]
# Query_time: 14 Lock_time: 0 Rows_sent: 5 Rows_examined: 395292

Sachant que j'ai executé les requêtes directement via phpmyadmin et que ça s'execute en moins d'une seconde.

- sinon la base est actuellement sur un autre serveur moins cotaud avec mysql 4 par contre et avec les mêmes requêtes ça tourne nikel...

Merci de m'éclairer