Optimiser les paramètres MySQL

Optimiser les paramètres MySQL - appel aux experts

Je m'occupe d'un site qui a maintenant une assez forte fréquentation.

Ce site est développé en php et utilise un serveur de base de données MySQL.
Pour la gestion, j'utilise l'outil phpmyadmin.
Ce dernier possède un onglet status dans lequel on a plusieurs indicateurs sur le SGBD.
Je ne connais pas bien tous ces indicateurs et leur signification, bien qu'il y ait une petite explication à coté.
Certains indicateur ont un nombre en rouge, il semble que ce soit un warning, indicant que l'on peut optimiser certaines variables ou requêtes.

En ce qui concerne les requêtes, je n'ai actuellement pas le temps de les modifier pour les optimiser, de plus, je pense qu'elles le sont déjà assez. Il se peut cependant qu'il en reste quelques unes vraiment lentes, mais je vais regarder dans le log_slow_query pour avoir plus d'indication.

J'ai surtout besoin de savoir quelles sont les paramètres du serveur que je dois optimiser sur mon serveur MySQL.
Voici les indicateur qui sont en rouge dans phpmyadmin :

Code:

1
2
 
Slow_queries  	15 M  	 The number of queries that have taken more than long_query_time seconds.

Ca, je vais aller voir dans le log_slow_query

Code:

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
 
Innodb_buffer_pool_pages_dirty  	4  	 The number of pages currently dirty.
 
Innodb_buffer_pool_reads  	3 M  	 The number of logical reads that InnoDB could not satisfy from buffer pool and had to do a single-page read.
 
Innodb_log_waits  	14  	The number of waits we had because log buffer was too small and we had to wait for it to be flushed before continuing.
 
Innodb_row_lock_time_avg  	252  	 The average time to acquire a row lock, in milliseconds.
 
Innodb_row_lock_time_max 	5 k 	The maximum time to acquire a row lock, in milliseconds.
 
Innodb_row_lock_waits 	255 	The number of times a row lock had to be waited for.
 
Handler_read_rnd  	87 M  	The number of requests to read a row based on a fixed position. This is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan whole tables or you have joins that don't use keys properly.
 
Handler_read_rnd_next  	3.73 G  	The number of requests to read the next row in the data file. This is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.
 
Qcache_lowmem_prunes  	103 k  	The number of queries that have been removed from the cache to free up memory for caching new queries. This information can help you tune the query cache size. The query cache uses a least recently used (LRU) strategy to decide which queries to remove from the cache.
 
Slow_launch_threads  	15  	 The number of threads that have taken more than slow_launch_time seconds to create.
 
Binlog_cache_disk_use  	30  	The number of transactions that used the temporary binary log cache but that exceeded the value of binlog_cache_size and used a temporary file to store statements from the transaction.
 
Created_tmp_disk_tables  	7 M  	The number of temporary tables on disk created automatically by the server while executing statements. If Created_tmp_disk_tables is big, you may want to increase the tmp_table_size value to cause temporary tables to be memory-based instead of disk-based.
 
Key_reads  	19 M  	The number of physical reads of a key block from disk. If Key_reads is big, then your key_buffer_size value is probably too small. The cache miss rate can be calculated as Key_reads/Key_read_requests.
 
Select_full_join  	4 M  	The number of joins that do not use indexes. If this value is not 0, you should carefully check the indexes of your tables.
 
Sort_merge_passes  	45  	The number of merge passes the sort algorithm has had to do. If this value is large, you should consider increasing the value of the sort_buffer_size system variable.
 
Opened_tables  	24 k  	 The number of tables that have been opened. If opened tables is big, your table cache value is probably too small.
 
Table_locks_waited  	18 k  	The number of times that a table lock could not be acquired immediately and a wait was needed. If this is high, and you have performance problems, you should first optimize your queries, and then either split your table or tables or use replication.

Liste des variables serveur MySQL

Code:

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
 
 auto increment increment  	1
auto increment offset 	1
automatic sp privileges 	ON
back log 	50
basedir 	/usr/
binlog cache size 	524,288
bulk insert buffer size 	8,388,608
character set client 	latin1
character set connection 	utf8
(Global value) 	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 	utf8_unicode_ci
(Global value) 	latin1_swedish_ci
collation database 	latin1_swedish_ci
collation server 	latin1_swedish_ci
completion type 	0
concurrent insert 	1
connect timeout 	5
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 	1,000
div precision increment 	4
engine condition pushdown 	OFF
expire logs days 	20
flush 	OFF
flush time 	0
ft boolean syntax 	+ -><()~*:""&|
ft max word len 	84
ft min word len 	4
ft query expansion limit 	20
ft stopword file 	(built-in)
group concat max len 	1,024
have archive 	YES
have bdb 	NO
have blackhole engine 	NO
have compress 	YES
have crypt 	YES
have csv 	YES
have example engine 	NO
have federated engine 	YES
have geometry 	YES
have innodb 	YES
have isam 	NO
have ndbcluster 	DISABLED
have openssl 	NO
have query cache 	YES
have raid 	NO
have rtree keys 	YES
have symlink 	YES
init connect 	
init file 	
init slave 	
innodb additional mem pool size 	1,048,576
innodb autoextend increment 	8
innodb buffer pool awe mem mb 	0
innodb buffer pool size 	8,388,608
innodb checksums 	ON
innodb commit concurrency 	0
innodb concurrency tickets 	500
innodb data file path 	ibdata1:10M:autoextend
innodb data home dir 	
innodb doublewrite 	ON
innodb fast shutdown 	1
innodb file io threads 	4
innodb file per table 	OFF
innodb flush log at trx commit 	1
innodb flush method 	
innodb force recovery 	0
innodb lock wait timeout 	50
innodb locks unsafe for binlog 	OFF
innodb log arch dir 	
innodb log archive 	OFF
innodb log buffer size 	1,048,576
innodb log file size 	5,242,880
innodb log files in group 	2
innodb log group home dir 	./
innodb max dirty pages pct 	90
innodb max purge lag 	0
innodb mirrored log groups 	1
innodb open files 	300
innodb support xa 	ON
innodb sync spin loops 	20
innodb table locks 	ON
innodb thread concurrency 	8
innodb thread sleep delay 	10,000
interactive timeout 	28,800
join buffer size 	520,192
key buffer size 	16,777,216
key cache age threshold 	300
key cache block size 	1,024
key cache division limit 	100
language 	/usr/share/mysql/english/
large files support 	ON
large page size 	0
large pages 	OFF
license 	GPL
local infile 	ON
locked in memory 	OFF
log 	ON
log bin 	ON
log bin trust function creators 	OFF
log error 	
log slave updates 	OFF
log slow queries 	ON
log warnings 	1
long query time 	10
low priority updates 	OFF
lower case file system 	OFF
lower case table names 	0
max allowed packet 	16,776,192
max binlog cache size 	4,294,967,295
max binlog size 	104,857,600
max connect errors 	10
max connections 	1,000
max delayed threads 	20
max error count 	64
max heap table size 	16,777,216
max insert delayed threads 	20
max join size 	18,446,744,073,709,552,000
max length for sort data 	1,024
max prepared stmt count 	16,382
max relay log size 	0
max seeks for key 	4,294,967,295
max sort length 	1,024
max sp recursion depth 	0
max tmp tables 	32
max user connections 	0
max write lock count 	4,294,967,295
multi range count 	256
myisam data pointer size 	6
myisam max sort file size 	2,147,483,647
myisam recover options 	OFF
myisam repair threads 	1
myisam sort buffer size 	8,388,608
myisam stats method 	nulls_unequal
ndb autoincrement prefetch sz 	32
ndb force send 	ON
ndb use exact count 	ON
ndb use transactions 	ON
(Global value) 	OFF
ndb cache check time 	0
net buffer length 	16,384
net read timeout 	30
net retry count 	10
net write timeout 	60
new 	OFF
old passwords 	ON
open files limit 	5,010
optimizer prune level 	1
optimizer search depth 	62
pid file 	/var/run/mysqld/mysqld.pid
prepared stmt count 	0
port 	3,306
preload buffer size 	32,768
protocol version 	10
query alloc block size 	8,192
query cache limit 	1,048,576
query cache min res unit 	4,096
query cache size 	67,108,864
query cache type 	ON
query cache wlock invalidate 	OFF
query prealloc size 	8,192
range alloc block size 	2,048
read buffer size 	131,072
read only 	OFF
read rnd buffer size 	262,144
relay log purge 	ON
relay log space limit 	0
rpl recovery rank 	0
secure auth 	OFF
server id 	1
skip external locking 	ON
skip networking 	OFF
skip show database 	OFF
slave compressed protocol 	OFF
slave load tmpdir 	/tmp/
slave net timeout 	3,600
slave skip errors 	OFF
slave transaction retries 	10
slow launch time 	2
socket 	/var/run/mysqld/mysqld.sock
sort buffer size 	16,777,208
sql mode 	
sql notes 	ON
sql warnings 	ON
storage engine 	MyISAM
sync binlog 	0
sync frm 	ON
system time zone 	CEST
table cache 	1,024
table lock wait timeout 	50
table type 	MyISAM
thread cache size 	64
thread stack 	131,072
time format 	%H:%i:%s
time zone 	SYSTEM
timed mutexes 	OFF
tmp table size 	134,217,728
tmpdir 	/tmp
transaction alloc block size 	8,192
transaction prealloc size 	4,096
tx isolation 	REPEATABLE-READ
updatable views with limit 	YES
version 	5.0.22-Debian_0ubuntu6.06-log
version comment 	Debian Etch distribution
version compile machine 	i486
version compile os 	pc-linux-gnu
wait timeout 	28,800

Je voulais savoir s'il y a raison de s'inquiêter ou si ce n'est pas si grave. Le site n'est pas lent, mais s'il peut être plus rapide, ce serait évidemment bien mieux.

Avez-vous des conseils sur l'optimisations des paramètres?
Le serveur dispose de Go de mémoire vive, il n'y a que MySQL qui tourne dessus. Le site tourne sur d'autres serveurs.

Merci!

Le nombre de requêtes lentes et donc consommatrices de ressources est important, très important même. Vous devriez archivez ces requêtes lentes. Ensuite une fois identifiée, tentez de les optimiser au niveau de la syntaxe SQL puis au niveau de l'indexation de vos tables.

5.9.5. Le log des requêtes lentes

Nous longuons déjà ces requêtes lentes.
Je n'ai pas encore accès à ces fichiers fichiers le log, mais ça se fera dans la semaine je pense.

Ma question sur portait plutôt sur les paramètres de cache MySQL.

En ce qui concerne les requêtes lentes, je les analyserai plus tard dès que j'aurai les logs. Cependant, dans mon cas, il s'agit de bien différencier les requêtes provenant du front office, du backoffice ou depuis les batch.

En effet, les requêtes provenant du back office ou des batch peuvent se permettre d'être lente car elle ne sont pas souvent appelées. Par contre, depuis le front, les requêtes doivent être optimisées au maximum.
En ce qui concerne les index, je me heurte à un problème conséquent. En fait, j'ai des prix d'articles qui change selon l'heure (happy hour, etc).
Je doit donc utiliser NOW() dans la plupart de mes requêtes de demander d'article et de prix afin de situer dans quelle plage d'heure et de prix on est.
J'ai lu que les index n'étaient pas utilisés lorsque l'on faisait appel à cette fonction.

Quelle est la solution?

Merci dans tous les cas, et je reviendrai sur le forum quand j'aurai plus d'informations sur mes requêtes lentes et sur les requêtes n'utilisant pas d'index (nous avons logué ça aussi).

Je continue sur mes questions d'optimisation.
Je logue les requêtes lentes et les reqêtes n'utilisant pas d'index.
En ce qui concerne les reqêtes lentes, je n'en ai pas à plus de 5 secondes, c'est donc ok.
Par contre, j'ai un bon paquet de requêtes qui n'utilisent pas les index.
J'ai fait un explain sur celle-ci :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 EXPLAIN SELECT DISTINCT p.products_id, p.manufacturers_id, p.products_traced, pd.products_price, pd.products_name, m.manufacturers_name, spe.specials_new_products_price, hh.specials_price_happy_hours FROM products p JOIN manufacturers m ON ( p.manufacturers_id = m.manufacturers_id ) JOIN products_stock st ON ( p.products_id = st.products_id ) JOIN products_style s ON ( p.products_id = s.products_id ) JOIN products_tribu pt ON ( p.products_id = pt.products_id ) JOIN products_description pd ON ( p.products_id = pd.products_id ) LEFT OUTER JOIN specials spe ON ( p.products_id = spe.products_id AND spe.status = '1' ) LEFT OUTER JOIN happy_hours_specials_basic hh ON ( p.products_id = hh.products_id AND hh.status_happy_hours = '1' ) WHERE p.products_status = '1' AND p.products_quantity > '0' AND p.products_type_id = '1' AND st.products_stock_quantity > '0' AND pd.language_id = '1' AND pt.tribu = '1' AND s.style_id = '9' AND pt.tribu = '1' ORDER BY s.style_potent LIMIT 30 , 9;
Le résultat intéressant est celui-ci :
1 SIMPLE m ALL PRIMARY NULL NULL NULL 68 Using temporary; Using filesort

En fait, il semble que sur la table manufacturers, il fasse une recherche sur 68 lignes et qu'il n'utilise pas d'index mais il fait un tri.
C'est très étrange car il n'y a pas de tri sur cette table.
Alors, j'ai pensé qu'il pouvait s'agir plutot du tri sur style_potent qui est à la fin.
Cependant, j'ai bien un index sur ce champ.
Je ne comprends donc pas bien d'ou vient le fait qu'il n'utilise pas d'index.

Vous avez une idée? Merci!!!

Après moultes réflexion, il s'agit certainement plutôt du distinct qui génère une recherche séquentielle sur la table temporaire générée.

Il n'y a pas vraiment d'autre moyen de tester le distinct que d'analyser ligne par ligne les résultats.

Donc je comprends mieux le problème. En fait, il faudrait que je n'utilise pas de mot clef distinct. Mais alors, mes résultats seront faux...