Optimisation de Mysql

Version imprimable

Bonjour,

J'ai la version 5.5.53 de mysql et voilà la config qui tourne depuis 15 jours:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 slow_query_log = 1 query_cache_type = 1 query_cache_size = 512M query_cache_limit = 4M key_buffer_size = 512M table_open_cache = 10000 table_definition_cache = 10000 tmp_table_size = 64M max_heap_table_size = 64M open_files_limit = 20000 innodb_file_per_table = 1 innodb_buffer_pool_size = 512M innodb_log_file_size = 128M innodb_flush_method=O_DIRECT join_buffer_size = 4M wait_timeout=600 max_connections = 400
Aujourd'hui, je lance le script mysqltuner.pl ( https://raw.githubusercontent.com/ma.../mysqltuner.pl ).
il me suggère de désactiver le query cache
Variables to adjust:
query_cache_type (=0) => [!!] Query cache may be disabled by default due to mutex contention.

J'ai donc mis
Code:

1 2 query_cache_type = 0 query_cache_size = 0
Mais je trouve cela étrange . Quelqu'un a -t'il une explication simple ?

Merci,

Eric

Salut seabird.

Vous devriez songer à passer à la version MySql 5.5.54.
Voici mon fichier "my.ini" pour cette version :

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
# ********************* #
#                       #
#     Sphere "Vert"     #
#                       #
# ********************* #
#
[client]
#password	= your_password
port                  = 3306
socket                = /tmp/mysql.sock
default-character-set = latin1
 
# ----------------------- #
#     access features     #
# ----------------------- #
 
user     = root
password = root
host     = localhost
 
# ================ #
# The MySQL server #
# ================ #
 
[wampmysqld]
port                    = 3306
socket                  = /tmp/mysql.sock
default-time-zone       = SYSTEM
net-buffer-length       = 64K
read_rnd_buffer_size    = 1M
 
basedir   = f:/Wamp/bin/mysql/mysql5.5.54/
datadir   = f:/Wamp/bin/mysql/mysql5.5.54/data
 
server-id = 1
 
skip-federated
skip-slave-start
 
lc-messages-dir = f:/Wamp/bin/mysql/mysql5.5.54/share
lc-messages     = fr_FR
 
date_format     = '%d-%m-%Y'
datetime_format = '%d-%m-%Y %H:%i:%s'
 
# --------------#
# fichier 'log' #
# --------------#
 
#skip-log-warnings
 
log-error = f:/Wamp/logs/mysql_error.log
 
# ----------------------------#
# Journalisation Requetes SQL #
# ----------------------------#
 
log-queries-not-using-indexes
 
slow_query_log      = 0
slow_query_log_file = mysql_slow.log
 
# --------------#
# moteur MyIsam #
# --------------#
 
myisam_sort_buffer_size = 64M
 
key_buffer_size   = 64M
read_buffer_size  = 2M
sort_buffer_size  = 64M
 
# --------------#
# moteur InnoDB #
# --------------#
 
default-storage-engine         = InnoDB
 
innodb_buffer_pool_size        = 128M
innodb_buffer_pool_instances   = 2
 
innodb_data_home_dir           = f:/Wamp/bin/mysql/mysql5.5.54/data/
innodb_data_file_path          = ibdata1:10M:autoextend
innodb_doublewrite             = 1
 
innodb_file_per_table
innodb_flush_log_at_trx_commit = 1
#innodb_force_recovery          = 1
 
innodb_io_capacity             = 2000
innodb_max_dirty_pages_pct     = 75
innodb_lock_wait_timeout       = 50
 
innodb_log_buffer_size         = 8M
innodb_log_file_size           = 256M
innodb_log_group_home_dir      = f:/Wamp/bin/mysql/mysql5.5.54/data/
 
innodb_read_io_threads         = 32
innodb_stats_on_metadata       = 0
innodb_support_xa              = 1
innodb_thread_concurrency      = 0
innodb_write_io_threads        = 32
 
# --------- #
# character #
# --------- #
 
character-set-server = latin1
collation-server     = latin1_general_ci
 
# ----------- #
# Performance #
# ----------- #
 
skip-external-locking
 
concurrent_insert            = 2
connect_timeout              = 10
 
join_buffer_size             = 512K
long_query_time              = 1
 
max_allowed_packet           = 32M
max_connections              = 4
max_heap_table_size          = 64M
 
open_files_limit             = 2048
 
query_alloc_block_size       = 8M
query_cache_limit            = 2M
query_cache_min_res_unit     = 512
query_cache_size             = 128M
query_cache_type             = 1
query_cache_wlock_invalidate = On
 
table_open_cache             = 2048
 
thread_stack                 = 1M
thread_cache_size            = 8
 
tmp_table_size               = 64M
 
wait_timeout                 = 30
 
# -----------------------------------------------------------------------
 
[mysqldump]
quick
max_allowed_packet = 16M
 
[mysql]
no-auto-rehash
 
[isamchk]
key_buffer_size  = 256M
sort_buffer_size = 256M
read_buffer      = 2M
write_buffer     = 2M
 
[myisamchk]
key_buffer_size  = 256M
sort_buffer_size = 256M
read_buffer      = 2M
write_buffer     = 2M
 
[mysqlhotcopy]
interactive-timeout
 
[mysqld]
port = 3306

Pour que le test avec "mysqltuner" fonctionne correctement, vous devez faire le test au moins sur une journée, sinon les résultats n'auront pas de sens.

@+

14/01/2017, 18h43
seabird
Bonjour Artemus24,

Merci pour la réponse et la configuration. Sachant que de mon côté, je tourne régulièrement avec aux alentours de 200-300 connexions simultanées d’où mon max_connections = 400
J'utilise un serveur mysql dédié en lan avec 3 autres serveurs applicatifs.

Ce que j'aimerais comprendre , c'est pourquoi mysqltuner me suggère query_cache_type (=0) et ce quelque-soit la valeur de query_cache_size, sachant que j'ai des tables innodb et myisam . Est ce un bug de sa part?
J'ai bien sur laissé le serveur tourner quelque jours ( 15 jours) avant de lancer mysqltuner.

Est ce qu'il est judicieux que je laisse ?
Code:

1 2 query_cache_type = 1 query_cache_size = 512M
Merci,

Eric
15/01/2017, 12h45
Artemus24

Salut seabird.

Citation:

Envoyé par seabird

Est ce un bug de sa part?

Vous me poser une colle ???

Si MysqlTuner vous conseille de mettre "query_cache_type" à zéro, c'est que le stockage dans les buffers de mysql ne sert à rien.
Les buffers servent qu'aux select et pas aux insert, update et delete de vos applications.

La première idée qui me vient à l'esprit est que vous faites des select qui ne sont jamais identiques.
D'où l'inutilité du stockage dans les buffers de MySql car le résultat précédent ne sert à rien.
Cela peut provenir aussi du nombre de connexions qui sont autant de select différents.

Autre idée, entre deux select identiques, vous faites des mises à jour (update, insert ou delete) de vos tables, ce qui invalide le stockage dans les buffers.

Il y a une chose que vous pouvez faire dans vos select, c'est de faire le tri entre celle dont le résultat peut être stocké dans les buffers, et celle où le stockage ne sert à rien.
Pour cela, il suffit d'ajouter après le select "SQL_NO_CACHE".
--> http://dev.mysql.com/doc/refman/5.7/...in-select.html
Ou si vous préférez, privilégiez les requêtes lourdes dans la mise en cache, vis-à-vis des légères.

Il ne faut pas oublier aussi que le stockage dans les buffers, a pour résultat de virer le stockage précédent.
Je ne sais pas trop comment mysql gère les priorités du stockage mais cela peut aussi être un raison de l'inutilité des buffers.

Ne pas oublier que MySql vérifie les buffers afin de trouver le résultat et si celui-ci ne s'y trouve pas, refait l'exécution du select.
Mettre un cache inutile revient à rallonger le temps d'exécution des select.

@+

Salut Artemus,

Citation:

Envoyé par Artemus24

Si MysqlTuner vous conseille de mettre "query_cache_type" à zéro, c'est que le stockage dans les buffers de mysql ne sert à rien.
Les buffers servent qu'aux select et pas aux insert, update et delete de vos applications.

Sur les sites , il y a une majorité de Select ( je fais une grosse mise à jour , 1 fois par semaine, je delete 1 x par jour des évènements qui n'ont plus lieu d'être -> date dépassée)

Voilà en fait le résultat, de mysqltuner.
Code:

1 2 3 4 5 Variables to adjust: query_cache_type (=0) query_cache_size (> 512M) [see warning above] join_buffer_size (> 4.0M, or always use indexes with joins) innodb_log_file_size should be equals to 1/4 of buffer pool size (=128M) if possible.
Il me dit en même temps de mettre query_cache_type=0 et en même temps de mettre query_cache_size>512M -> étrange
Il me semble avoir lu aussi , qu'il fallait éviter de mettre query_cache_size trop grand > 256M .

Je ne sais pas ce que tu en penses. Je vais donc tenter de mettre query_cache_type=0 , même si dans l'absolu , cela me dérange vu ma majorité de select.

Merci,

Eric

15/01/2017, 14h04
Artemus24

Salut seabird.

Citation:

Envoyé par seabird

Sur les sites , il y a une majorité de Select ...

Oui, je veux bien, mais j'ignore ce que représente un select dans votre application.
S'il y a autant de select différents qu'il y a de connexion, les buffers ne servent à rien.
Inversement si c'est le même select, donc le même paramétrage que chaque connexion utilise, alors oui, les buffers ont un rôle à jouer.

Je rappelle que les buffers servent juste à gagner du temps donc de la performance sur vos select identiques.
Ce n'est pas quelque chose d'indispensable dans le fonctionnement de MySql.
Le mieux pour vous rendre compte de l'impact des buffers dans votre application est de suivre la recommandation de MySql et de refaire les tests.

Optimiser vos requêtes prend beaucoup de temps et ce n'est pas en ajustant une variable système que vous obtiendrez le bon paramétrage.
C'est bien plus compliqué que cela et MysqlTuner est un outil parmi d'autres qui peut vous rendre ce service.

D'autant que cela peut aussi dépendre de la surcharge de votre serveur, du nombre de connexions, et de ce que vous faites.
C'est à l'usage que l'on se rend compte des requêtes qui posent problèmes mais il n'y a pas de solutions toutes faites.

Citation:

Envoyé par seabird

Je vais donc tenter de mettre query_cache_type=0 , même si dans l'absolu , cela me dérange vu ma majorité de select.

Le mieux est de suivre la recommandation de MysqlTuner et de constater s'il y a un ralentissement ou pas.
Il est fort probable que cela ne change rien en terme de performance.

Il ne faut pas croire que si vous faites à 99% que des select, le fait de mettre des buffers va vous faire gagner en performance.
D'après ce que j'ai compris de votre application, il n'y a pas de select identiques ou bien très peu.
Et à l'usage, MysqlTuner se rend compte que les select identiques représentent un pourcentage trop faible. Du coup, les buffers sont inutiles.

Je vous conseille de mettre "[c]SQL_NO_CACHE[c]" sur les select qui sont très rapides en terme de performance et de laisser en l'état pour les select lourds.
Et il se peut que sur les lourdes, MysqlTuner vous demande d'activer la variable "query_cache_type"

Je vous conseille de lire ceci : https://dev.mysql.com/doc/refman/5.6...ery_cache_type

@+
15/01/2017, 16h06
seabird
Salut Artemus,

Merci pour votre réponse, je vais donc suivre les recommandations de MysqlTuner et mettre
Code:

1 2 query_cache_type = 0 query_cache_size = 0
et voir ce qui se passe. D'autant plus que j'ai aussi un cache côté applicatif.

Eric
15/01/2017, 16h10
Artemus24

Salut seabird.

Citation:

Envoyé par seabird

D'autant plus que j'ai aussi un cache côté applicatif.

Et qui se nomme comment ce cache applicatif ?

Est-ce au niveau de php ? Peut-être "Zend Opcache" ?

@+
15/01/2017, 16h26
seabird

Oui sur certains sites au niveau php, c'est tout simplement un cache par fichier dans joomla par exemple ou par memcached ( en fait il n'y avait pas grande différence quand j'ai testé).
Mais il n'y a pas de cache sur tous les sites des trois serveurs..