Mysql tunning - Historique des versions

217.174.199.129 le 6 mai 2013 à 16:59

2013-05-06T16:59:15Z

← Version précédente		Version du 6 mai 2013 à 16:59
Ligne 1 :		Ligne 1 :
	Voici un [https://~~wiki.blaxeenprod.com~~/BENPERSO/doc-manuel/system/software/Rapport_tuning_mysql-agarik.pdf rapport] concernant l'optimisation de mysql suite à des lenteurs (Doc fournie à Agarik		Voici un [https://{{SERVERNAME}}/BENPERSO/doc-manuel/system/software/Rapport_tuning_mysql-agarik.pdf rapport] concernant l'optimisation de mysql suite à des lenteurs (Doc fournie à Agarik
	par le prestataire d'un client)		par le prestataire d'un client)

217.174.199.129 le 3 décembre 2012 à 12:30

2012-12-03T12:30:12Z

← Version précédente		Version du 3 décembre 2012 à 12:30
Ligne 1 :		Ligne 1 :
	Voici un [https://wiki.blaxeenprod.com/BENPERSO/doc-manuel/system/Rapport_tuning_mysql-agarik.pdf rapport] concernant l'optimisation de mysql suite à des lenteurs (Doc fournie à Agarik		Voici un [https://wiki.blaxeenprod.com/BENPERSO/doc-manuel/system/software/Rapport_tuning_mysql-agarik.pdf rapport] concernant l'optimisation de mysql suite à des lenteurs (Doc fournie à Agarik
	par le prestataire d'un client)		par le prestataire d'un client)

Admin le 28 mars 2012 à 17:37

2012-03-28T17:37:29Z

← Version précédente		Version du 28 mars 2012 à 17:37
Ligne 62 :		Ligne 62 :
	(SELECT id_cat FROM tutoriaux_categorie WHERE id_cat_mere=2)		(SELECT id_cat FROM tutoriaux_categorie WHERE id_cat_mere=2)

	Dans cette requête, nous recherchons des tutoriaux dont la catégorie appartient à la catégorie “mère” 2. Cette requête est très belle mais peut rapidement poser d’énormes problèmes de temps si elle n’est pas optimisée. Déjà, oublier un index sur id_cat par exemple ou encore oublier un index sur id_cat_mere surtout que cette dernière table sera plutôt fixe (nous ne rajoutons pas des catégories tous les jours) avec une table assez conséquente de plusieurs milliers d’enregistrements conduira à une requête s’exécutant très lentement…pouvant prendre plusieurs secondes !		Dans cette requête, nous recherchons des tutoriaux dont la catégorie appartient à la catégorie “mère” 2. Cette requête est très belle mais peut rapidement poser d’énormes problèmes de
			temps si elle n’est pas optimisée. Déjà, oublier un index sur id_cat par exemple ou encore oublier un index sur id_cat_mere surtout que cette dernière table sera plutôt fixe (nous ne
			rajoutons pas des catégories tous les jours) avec une table assez conséquente de plusieurs milliers d’enregistrements conduira à une requête s’exécutant très lentement…pouvant prendre
			plusieurs secondes !

	Toutefois on peut faire une chose pour optimiser tout cela. Pourquoi ne pas mettre l’information id_cat_mere dans la table tutoriaux ? D’accord, c’est moins joli, c’est moins conceptuel et on devra faire plus de mise à jour si on change la table tutoriaux_categorie MAIS ALORS que de temps de gagner ! Puisqu’au final on arrive à ça		Toutefois on peut faire une chose pour optimiser tout cela. Pourquoi ne pas mettre l’information id_cat_mere dans la table tutoriaux ? D’accord, c’est moins joli, c’est moins
			conceptuel et on devra faire plus de mise à jour si on change la table tutoriaux_categorie MAIS ALORS que de temps de gagner ! Puisqu’au final on arrive à ça

	SELECT * FROM tutoriaux WHERE id_cat_mere=2		SELECT * FROM tutoriaux WHERE id_cat_mere=2
Ligne 74 :		Ligne 78 :
	SELECT *,count(c.nb_com) as nb FROM tutoriaux t LEFT JOIN tutoriaux_commentaire c WHERE t.id=c.id_tutoriaux GROUP BY t.id		SELECT *,count(c.nb_com) as nb FROM tutoriaux t LEFT JOIN tutoriaux_commentaire c WHERE t.id=c.id_tutoriaux GROUP BY t.id

	Ici cette requête recherche les tutoriaux avec une information supplémentaire : le nombre de commentaire sur chaque tutoriel. Comme les commentaires sont dans une autre table Mysql, une jointure est effectué permettant de récupérer le nombre d’enregistrements (donc de commentaires) concernant le tutorial. Si vous avez 10 000 tutoriaux et des centaines de milliers de commentaires, cette requête est totalement explosive (pour le serveur !)		Ici cette requête recherche les tutoriaux avec une information supplémentaire : le nombre de commentaire sur chaque tutoriel. Comme les commentaires sont dans une autre table Mysql,
			une jointure est effectué permettant de récupérer le nombre d’enregistrements (donc de commentaires) concernant le tutorial. Si vous avez 10 000 tutoriaux et des centaines de milliers
			de commentaires, cette requête est totalement explosive (pour le serveur !)

	Hors nous pouvons faire une chose très simple. Inclure dans la table tutoriaux un champ nb_commentaire. Alors bien sur chaque fois qu’un commentaire est ajouté, il faudra incrémenter le champ nb_commentaire mais au final que de temps gagné car la requête devient :		Hors nous pouvons faire une chose très simple. Inclure dans la table tutoriaux un champ nb_commentaire. Alors bien sur chaque fois qu’un commentaire est ajouté, il faudra incrémenter
			le champ nb_commentaire mais au final que de temps gagné car la requête devient :

	SELECT * FROM tutoriaux		SELECT * FROM tutoriaux

Admin le 28 mars 2012 à 17:36

2012-03-28T17:36:50Z

← Version précédente		Version du 28 mars 2012 à 17:36
Ligne 81 :		Ligne 81 :

	Ce n’est pas plus simple ?		Ce n’est pas plus simple ?
	<pre>		</pre>


	[[Catégorie:Software]]		[[Catégorie:Software]]

Admin le 28 mars 2012 à 17:36

2012-03-28T17:36:26Z

← Version précédente		Version du 28 mars 2012 à 17:36
Ligne 8 :		Ligne 8 :


	* Detecter les requêtes non optimisés		==== Detecter les requêtes non optimisés ====
	<pre>		<pre>
	Le premier but va être de détecter vos requêtes non optimisées. Pour cela vous avez déjà un moyen visuel trés simple. Vous cliquez sur une rubrique de votre site et le chargement est		Le premier but va être de détecter vos requêtes non optimisées. Pour cela vous avez déjà un moyen visuel trés simple. Vous cliquez sur une rubrique de votre site et le chargement est
Ligne 38 :		Ligne 38 :
	</pre>		</pre>

	* Clef et index		==== Clef et index ====
	<pre>		<pre>
	Une erreur classique consiste à négliger les clefs et les index. Dans une requête Mysql, lorsque vous utilisez la condition WHERE, celle-ci pourra être extrêmement améliorée par		Une erreur classique consiste à négliger les clefs et les index. Dans une requête Mysql, lorsque vous utilisez la condition WHERE, celle-ci pourra être extrêmement améliorée par
Ligne 55 :		Ligne 55 :
	</pre>		</pre>

	* Jointures et inclusions		==== Jointures et inclusions ====
	<pre>		<pre>
	Il faut faire attention à ne pas utiliser des requêtes trop complexe. Et surtout faire attention aux jointures et aux inclusions. Voici un exemple de requête :		Il faut faire attention à ne pas utiliser des requêtes trop complexe. Et surtout faire attention aux jointures et aux inclusions. Voici un exemple de requête :

Admin le 28 mars 2012 à 17:34

2012-03-28T17:34:53Z

← Version précédente		Version du 28 mars 2012 à 17:34
Ligne 3 :		Ligne 3 :

	On peut aussi utiliser un script pour tunner mysql : http://mysqltuner.pl/mysqltuner.pl		On peut aussi utiliser un script pour tunner mysql : http://mysqltuner.pl/mysqltuner.pl


			=== Piste pour l'optimisation ===


			* Detecter les requêtes non optimisés
			<pre>
			Le premier but va être de détecter vos requêtes non optimisées. Pour cela vous avez déjà un moyen visuel trés simple. Vous cliquez sur une rubrique de votre site et le chargement est
			extrêmement long ? La page d’entrée du site se charge par étape ? Demandez-vous ce qui peut causer ces soucis, cela peut être une page trop lourde à charger, des images trop
			volumineuses ou des requêtes SQL non optimisés !

			Un autre moyen consiste à utiliser aussi le calcul du temps pris par les requêtes Mysql avec PHP, voir par exemple ce tutoriel. Cela consiste à utiliser une fonction temps de PHP qui
			donnera des indications trés précises sur le temps utilisé. Vous mettez cette fonction avant et après la requête et vous affichez le résultat !


			function utime_temps()
			{
			$t=microtime();
			$t=((double)strstr($t, ' ')+(double)substr($t,0,strpos($t,' ')));
			return $t;
			}

			Pour ceux qui sont en dédiés ou qui ont accès au fichier my.cnf dans /etc/mysql, vous pouvez rajouter ces lignes

			log-slow-queries = /var/log/mysql/mysql-slow.log
			long_query_time = 1

			Dans le fichier my.cnf (et faire un restart de Mysql par exemple avec /etc/init.d/mysql restart). Avec ces paramètres les requêtes Mysql trop longue d’une seconde (paramètre
			long_query_time) seront indiquées dans le fichier mysql-slow.log. Ainsi la consultation de ce fichier vous permet simplement de cibler précisément vos optimisations.

			Une autre fonction Mysql est aussi pratique : la fonction EXPLAIN. Il suffit par exemple dans PhpMyAdmin de positionner EXPLAIN devant la requête SQL que vous voulez analyser.
			Celle-ci retourne un résultat expliquant exactement ce que fait Mysql concernant cette requête. Et notamment la colonne rows indique le nombre de ligne parcouru (celui-ci doit être le
			plus petit possible) et la colonne key indique les clefs utilisées (ci c’est NULL alors aucune clef est utilisée pour cette requête)
			</pre>

			* Clef et index
			<pre>
			Une erreur classique consiste à négliger les clefs et les index. Dans une requête Mysql, lorsque vous utilisez la condition WHERE, celle-ci pourra être extrêmement améliorée par
			l’utilisation d’un index dans la condition WHERE. Par exemple :
			SELECT * FROM tutoriaux WHERE categorie=1

			Dans cette requête ci-dessous, si la table tutoriaux contient 100 000 enregistrements, Mysql va passer les 100 000 enregistrements pour chercher les tutoriaux de catégorie 1 !! C’est
			du temps perdu. Il suffit de mettre catégorie en index pour que de 100 000 enregistrements examinés on passe à …1 !

			Vous allez me dire, dans ce cas là, pourquoi ne pas mettre des index partout ? Il y a plusieurs raisons à cela : d’abord un index prend de la place mémoire dans la table Mysql et si
			vous avez trop d’index, cette place mémoire peut être plus importante que la place que prend les données. Ensuite lorsque vous faites une insertion dans la table, Mysql est obligé de
			mettre à jour tous les index, ce qui prend du temps aussi !

			Vous devez donc calculer ce qui est le plus intéressant pour vous. Si la requête est exécutée très souvent, il sera intéressant de mettre des index sur les conditions WHERE MAIS si
			vous faites beaucoup d’insertions dans la table, il faudra limiter les index au strict necessaire et calculer au plus juste avec EXPLAIN le gain réalisé en mettant un index.
			</pre>

			* Jointures et inclusions
			<pre>
			Il faut faire attention à ne pas utiliser des requêtes trop complexe. Et surtout faire attention aux jointures et aux inclusions. Voici un exemple de requête :

			SELECT * FROM tutoriaux WHERE id_cat IN
			(SELECT id_cat FROM tutoriaux_categorie WHERE id_cat_mere=2)

			Dans cette requête, nous recherchons des tutoriaux dont la catégorie appartient à la catégorie “mère” 2. Cette requête est très belle mais peut rapidement poser d’énormes problèmes de temps si elle n’est pas optimisée. Déjà, oublier un index sur id_cat par exemple ou encore oublier un index sur id_cat_mere surtout que cette dernière table sera plutôt fixe (nous ne rajoutons pas des catégories tous les jours) avec une table assez conséquente de plusieurs milliers d’enregistrements conduira à une requête s’exécutant très lentement…pouvant prendre plusieurs secondes !

			Toutefois on peut faire une chose pour optimiser tout cela. Pourquoi ne pas mettre l’information id_cat_mere dans la table tutoriaux ? D’accord, c’est moins joli, c’est moins conceptuel et on devra faire plus de mise à jour si on change la table tutoriaux_categorie MAIS ALORS que de temps de gagner ! Puisqu’au final on arrive à ça

			SELECT * FROM tutoriaux WHERE id_cat_mere=2

			Avec un index sur id_cat_mere, la requête est exécutée immédiatement !

			Concernant les jointures, c’est la même chose…Par exemple :

			SELECT *,count(c.nb_com) as nb FROM tutoriaux t LEFT JOIN tutoriaux_commentaire c WHERE t.id=c.id_tutoriaux GROUP BY t.id

			Ici cette requête recherche les tutoriaux avec une information supplémentaire : le nombre de commentaire sur chaque tutoriel. Comme les commentaires sont dans une autre table Mysql, une jointure est effectué permettant de récupérer le nombre d’enregistrements (donc de commentaires) concernant le tutorial. Si vous avez 10 000 tutoriaux et des centaines de milliers de commentaires, cette requête est totalement explosive (pour le serveur !)

			Hors nous pouvons faire une chose très simple. Inclure dans la table tutoriaux un champ nb_commentaire. Alors bien sur chaque fois qu’un commentaire est ajouté, il faudra incrémenter le champ nb_commentaire mais au final que de temps gagné car la requête devient :

			SELECT * FROM tutoriaux

			Ce n’est pas plus simple ?
			<pre>


	[[Catégorie:Software]]		[[Catégorie:Software]]

Admin le 28 mars 2012 à 17:29

2012-03-28T17:29:49Z

← Version précédente		Version du 28 mars 2012 à 17:29
Ligne 1 :		Ligne 1 :
	Voici un [https://wiki.blaxeenprod.com/BENPERSO/doc-manuel/system/Rapport_tuning_mysql-agarik.pdf rapport] concernant l'optimisation de mysql suite à des lenteurs.		Voici un [https://wiki.blaxeenprod.com/BENPERSO/doc-manuel/system/Rapport_tuning_mysql-agarik.pdf rapport] concernant l'optimisation de mysql suite à des lenteurs (Doc fournie à Agarik
			par le prestataire d'un client)

	On peut aussi utiliser un script pour tunner mysql : http://mysqltuner.pl/mysqltuner.pl		On peut aussi utiliser un script pour tunner mysql : http://mysqltuner.pl/mysqltuner.pl

Admin le 28 mars 2012 à 17:29

2012-03-28T17:29:23Z

← Version précédente		Version du 28 mars 2012 à 17:29
Ligne 1 :		Ligne 1 :
			Voici un [https://wiki.blaxeenprod.com/BENPERSO/doc-manuel/system/Rapport_tuning_mysql-agarik.pdf rapport] concernant l'optimisation de mysql suite à des lenteurs.

	~~<pre>~~		On peut aussi utiliser un script pour tunner mysql : http://mysqltuner.pl/mysqltuner.pl
	~~key buffer sous utilisé => on le diminue de moitié (passage à 128M)~~
	~~Query cache prunes per day~~: ~~1628162 => on double le query_cache_size (passage à 256M)~~
	~~Tables fragmentées~~ : ~~need optimize tables;~~
	~~max conns atteint => phenomène a priori, rare : les graphes indiquent une moyenne à 23 avec des pics aux alentours de 100~~
	~~passage de slow_query time à 1 => permettra d'avoir plus d'infos sur les requetes "lentes"~~
	~~Temporary tables created on disk: 45% => augmentation tmp_table_size et max_heap_table_size (passage à 128M)~~
	~~open tables 2048~~/~~2048 => passage de table_cache et open_files à 4096 (actuellement 2048) et ajout d'un ulimit -n 4096 dans le init~~.~~d de mysqld~~
	~~buffer_pool_size : trop bas mais pas assez de RAM disponible (6GB+ de data InnoDB donc préco de passage à 8GB pour anticiper et être tranquille)~~

	~~=====================~~

	~~Utilisation du Query cache reste faible => augmentation de la taille max d'une query en cache (4M -> 6M)~~
	~~Upgrade RAM => augmentation innodb buffer pool size à 8G~~

	~~======================~~

	~~J+7 (en fait J+15)~~

	~~Diminution timeout pour éviter les connexion en sleep : passage de wait et interactive_timeout à 45s (max conn atteint en prod)~~
	~~Augmentation de innodb_buffer_pool_size à 10 GB (déjà plein et evite double cache par l'OS)~~
	~~Augmentation de join-buffer_size à 4M~~
	~~top score des slow queries : SELECT * FROM `atome`.`JobOfferSearchView` WHERE (MODIFICATIONDATE >= {ts '2011-06-12 01:40:01.281'} OR MODIFICATIONDATE IS NULL)\G (87% du temps de~~
	~~toutes les slow queries)~~
	~~</pre>~~
	[[Catégorie:Software]]		[[Catégorie:Software]]

Admin : Page créée avec «

 key buffer sous utilisé => on le diminue de moitié (passage à 128M) Query cache prunes per day: 1628162 => on double le query_cache_size (passage à 256M) Tables... »

2012-03-28T17:24:10Z

Page créée avec « <pre> key buffer sous utilisé => on le diminue de moitié (passage à 128M) Query cache prunes per day: 1628162 => on double le query_cache_size (passage à 256M) Tables... »

Nouvelle page

<pre>
key buffer sous utilisé => on le diminue de moitié (passage à 128M)
Query cache prunes per day: 1628162 => on double le query_cache_size (passage à 256M)
Tables fragmentées : need optimize tables;
max conns atteint => phenomène a priori, rare : les graphes indiquent une moyenne à 23 avec des pics aux alentours de 100
passage de slow_query time à 1 => permettra d'avoir plus d'infos sur les requetes "lentes"
Temporary tables created on disk: 45% => augmentation tmp_table_size et max_heap_table_size (passage à 128M)
open tables 2048/2048 => passage de table_cache et open_files à 4096 (actuellement 2048) et ajout d'un ulimit -n 4096 dans le init.d de mysqld
buffer_pool_size : trop bas mais pas assez de RAM disponible (6GB+ de data InnoDB donc préco de passage à 8GB pour anticiper et être tranquille)

=====================

Utilisation du Query cache reste faible => augmentation de la taille max d'une query en cache (4M -> 6M)
Upgrade RAM => augmentation innodb buffer pool size à 8G

======================

J+7 (en fait J+15)

Diminution timeout pour éviter les connexion en sleep : passage de wait et interactive_timeout à 45s (max conn atteint en prod)
Augmentation de innodb_buffer_pool_size à 10 GB (déjà plein et evite double cache par l'OS)
Augmentation de join-buffer_size à 4M
top score des slow queries : SELECT * FROM `atome`.`JobOfferSearchView` WHERE (MODIFICATIONDATE >= {ts '2011-06-12 01:40:01.281'} OR MODIFICATIONDATE IS NULL)\G (87% du temps de
toutes les slow queries)
</pre>
[[Catégorie:Software]]