Archive for the ‘optimisation’ Category

oct

MySQL Query cache

Posted by: freshdaz Tags: query cache

En tant que boulimique de MySQL, je me promène souvent sur la toile à la recherche d’informations, de bonnes et de moins bonnes…

Je suis tombé sur un article traitant du cache de requêtes de MySQL (MySQL Query Cache) sur le blogue de Patrick Lafontaine (http://www.noidea.ca/)

Je me permet de faire quelques commentaires ici.

En préambule, quelques informations nécessaires sur le cache de requêtes:

Système de cache interne à MySQL qui ne stocke que les requêtes SELECT et leur résultat ie pas d’INSERT, UPDATE, DELETE…

Les requêtes ( SELECT donc) doivent être strictement identiques ie même casse, mêmes espaces entre les mots,…

Ex 3 requêtes différentes pour le cache :

SELECT nom, prenom FROM client WHERE client_id=123;

select nom, prenom FROM client WHERE client_id=123; /* la casse du select*/

SELECT nom, prenom FROM client WHERE client_id=123; /*plusieurs espaces entre client et WHERE*/

Le cache est toujours à jour car en cas de modification d’une table, toutes les requêtes en relations avec cette table sont invalidées.

Le cache de requêtes est en général utile lorsque:

Les modifications sur les tables ne sont pas très fréquentes
Beaucoup de requêtes de lectures identiques
Utilisation de tables MyISAM. Moins intéressant pour InnoDB

Pour rebondir sur l’article de Patrick Lafontaine

« puisqu’elle est activée par défaut. »

Le cache de requêtes n’est pas activé par défaut, car la variable query_cache_size vaut 0. Si vous voulez vous en servir, il faut lui donner une taille en octet. Mettez le tout dans votre fichier de configuration. Assurez vous également que la variable query_cache_type est différente de OFF

« c’est-à-dire que la ou les applications qui s’en servent n’ont pas besoin d’être modifiées »

Pour une optimisation optimale, il est parfois nécessaire de modifier les requêtes SELECT avec SQL_CACHE ou SQL_NO_CACHE. On choisit alors quelles requêtes seront mis en cache.

« Si quelqu’un modifie la valeur directement dans MySQL, la cache possèdera la vieille valeur jusqu’à ce qu’un processus l’invalide. »

Sur une machine de production, ce type de manipulation reste quand même exceptionnel, sinon c’est qu’il y a des choses à revoir dans les process.

« Puisque les données ne changent pratiquement jamais, je ne me casserais pas la tête à réinventer la roue. MySQL fait déjà pour vous ce que APC ferait, sans le moindre effort. »

Si le contenu ne change JAMAIS, il n’a à priori rien à faire en base ! Il vaut mieux utiliser un menu statique et laisser la base faire son boulot avec du contenu dynamique. Dans le même ordre d’idée, plus le cache est éloigné du disque plus il est performant. En d’autres termes, le goulet d’étranglement est souvent (parfois) la base de données, de plus elle est souvent (parfois) plus difficilement scalable que le reste. L’utilisation d’un cache applicatif est rarement une mauvaise idée (il suffit de connaître l’identité du plus gros consommateur de memcached au monde http://www.facebook.com/note.php?note_id=39391378919)

« Il est donc plus avantageux de cacher les processus lourds que les légers. »

Malheureusement, avec le cache de requêtes ce n’est pas aussi simple. Admettons qu’une requête renvoyant un gros résultat prenne plus de temps qu’une renvoyant un plus petit. Si cette grosse requête vire toutes les autres requêtes du cache, l’apport du cache pour les autres requêtes est perdu, elle devront être misent à nouveau dans le cache ca qui implique des recherches inutiles dans le caches et de nouveaux accès disque…

« Lorsque la Query Cache de MySQL est activée, le processus de cacher les résultats et de les invalider s’effectue tout seul de manière invisible. Ainsi, d’autres requêtes que vous ne soupçonnez même pas bénéficient de la cache »

L’efficacité du cache de requêtes est vraiment lié à l’application. Il dépend du type de requêtes SELECT, de leur fréquence et de la fréquence des écritures dans les tables. Le gain n’est pas évident et est loin d’être immédiat, car pour chaque requête « cachable » MySQL devra l’analyser, devra la hacher, devra vérifier s’il elle est dans le cache ou non. Et ceci à un coût…

Vous pouvez calculer le taux d’efficacité du cache de requêtes avec la formule suivant:

Qcache_hits / (Qcache_hits + Com_select )

Pour finir, quelques paramètres et commandes

Paramètres principaux:

query_cache_size: Doit être différent de zéro pour activer le cache

query_cache_type:

ON: les requêtes select sont misent en cache
- Sauf (SQL_NO_CACHE, result set trop grand, fonction non déterministe..)
DEMAND: SELECT SQL_CACHE…
OFF

Commandes principales:

FLUSH QUERY CACHE

Défragmente le cache de requêtes
Ne vide pas le cache

Vider le cache de requêtes:

RESET QUERY CACHE
FLUSH TABLES
Redémarrer le serveur

Variables d’état: SHOW STATUS LIKE ‘Qcache%’ ;

Qcache_free_blocks : nombre de blocs libres

Qcache_free_memory : mémoire libre

Qcache_hits : nombre de fois qu’il a servi

Qcache_inserts : nombre de requêtes insérées

Qcache_lowmem_prunes : nombre de requêtes supprimées car plus de place

Qcache_not_cached : nombre de requêtes non « cachables »

Qcache_queries_in_cache : nombre de requêtes dans le cache

Qcache_total_blocks : nombre de blocs de mémoire

12 Comments

fév

Optimisation de requêtes: comprendre l’optimiseur de MySQL

Posted by: freshdaz Tags: group by, optimisation

Le but de cet article est d’optimiser une simple requête (SELECT avg(Population) FROM city GROUP BY CountryCode) et surtout de comprendre comment l’optimiseur procède, en étudiant les résultats donnés par les variables qui permettent de surveiller le serveur MySQL.

Le schéma utilisé est le schéma world téléchargeable ici

Voici la structure de la table city:

SHOW CREATE TABLE city\G
*************************** 1. row ***************************
Table: city
Create Table: CREATE TABLE city (
ID int(11) NOT NULL AUTO_INCREMENT,
Name char(35) NOT NULL DEFAULT »,
CountryCode char(3) NOT NULL DEFAULT »,
District char(20) NOT NULL DEFAULT »,
Population int(11) NOT NULL DEFAULT ‘0′,
PRIMARY KEY (ID)
) ENGINE=MyISAM AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1

La commande EXPLAIN, permet d’avoir le plan d’exécution:

EXPLAIN SELECT avg(Population) FROM city GROUP BY CountryCode\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: c
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4079
Extra: Using temporary; Using filesort

L’optimiseur fait un full table scan (type: ALL) ce qui n’est pas forcement une bonne nouvelle. De plus aucun index n’est utilisé (key: NULL), ce qui est logique car la table n’en contient pas (sic !)
Using temporary; Using filesort indiquent la création d’un table temporaire et le tri des données (pas très bon pour les performances surtout si la table temporaire est créée sur le disque)

La variable Last_query_cost permet de récupérer le coût de la requête:

SHOW STATUS LIKE ‘Last_query_cost’\G

*************************** 1. row ***************************
Variable_name: Last_query_cost
Value: 4963.520924

Ajoutons un index sur la colonne countrycode:

ALTER TABLE city ADD INDEX Idx_cc(CountryCode);

SHOW CREATE TABLE city\G

*************************** 1. row ***************************

Table: city

Create Table: CREATE TABLE city (

ID int(11) NOT NULL AUTO_INCREMENT,

Name char(35) NOT NULL DEFAULT »,

CountryCode char(3) NOT NULL DEFAULT »,

District char(20) NOT NULL DEFAULT »,

Population int(11) NOT NULL DEFAULT ‘0′,

PRIMARY KEY (ID),

KEY Idx_cc (CountryCode)

) ENGINE=MyISAM AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1

EXPLAIN SELECT avg(Population) FROM city GROUP BY CountryCode\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: city

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 4079

Extra: Using temporary; Using filesort

L’index Idx_cc ne sert à rien

Ajoutons alors un index sur la colonne population

ALTER TABLE city ADD INDEX Idx_population(Population);

SHOW CREATE TABLE city\G

*************************** 1. row ***************************

Table: city

Create Table: CREATE TABLE city (

ID int(11) NOT NULL AUTO_INCREMENT,

Name char(35) NOT NULL DEFAULT »,

CountryCode char(3) NOT NULL DEFAULT »,

District char(20) NOT NULL DEFAULT »,

Population int(11) NOT NULL DEFAULT ‘0′,

PRIMARY KEY (ID),

KEY Idx_cc (CountryCode),

KEY Idx_population (Population)

) ENGINE=MyISAM AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

EXPLAIN SELECT avg(Population) FROM city GROUP BY CountryCode\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: c

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 4079

Extra: Using temporary; Using filesort

Pas mieux ! L’index Idx_population ne sert à rien.

Effacement des 2 index:

ALTER TABLE city DROP INDEX Idx_cc, DROP INDEX Idx_population;

Ajoutons un index composite sur les colonnes population, countrycode:

ALTER TABLE city ADD INDEX Idx_population_cc(Population, CountryCode);

SHOW CREATE TABLE city\G

*************************** 1. row ***************************

Table: city

Create Table: CREATE TABLE `city` (

`ID` int(11) NOT NULL AUTO_INCREMENT,

`Name` char(35) NOT NULL DEFAULT »,

`CountryCode` char(3) NOT NULL DEFAULT »,

`District` char(20) NOT NULL DEFAULT »,

`Population` int(11) NOT NULL DEFAULT ‘0′,

PRIMARY KEY (`ID`),

KEY `Idx_population_cc` (`Population`,`CountryCode`)

) ENGINE=MyISAM AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1

EXPLAIN SELECT avg(Population) FROM city GROUP BY CountryCode\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: c

type: index

possible_keys: NULL

key: Idx_population_cc

key_len: 7

ref: NULL

rows: 4079

Extra: Using index; Using temporary; Using filesort

Çaparait un peu meilleur, l’index Idx_population_cc est utilisé.
Extra: using index nous indique que MySQL utilise un index couvrant (covering index), c’est à dire que l’information est entièrement accessible en parcourant l’index (pas d’accès aux données).
De plus l’optimiseur fait un full index scan: type: index

Voyons le coût de la requête:

SHOW STATUS LIKE ‘Last_query_cost’\G

*************************** 1. row ***************************
Variable_name: Last_query_cost
Value: 4963.520924

La valeur de (l’obscure) Last_query_cost est cependant le même que pour les requêtes précédentes…

Ajoutons un index composite sur les colonnes countrycode,population

ALTER TABLE test2.city ADD INDEX Idx_cc_population(CountryCode,Population);

SHOW CREATE TABLE city\G

*************************** 1. row ***************************

Table: city

Create Table: CREATE TABLE `city` (

`ID` int(11) NOT NULL AUTO_INCREMENT,

`Name` char(35) NOT NULL DEFAULT »,

`CountryCode` char(3) NOT NULL DEFAULT »,

`District` char(20) NOT NULL DEFAULT »,

`Population` int(11) NOT NULL DEFAULT ‘0′,

PRIMARY KEY (`ID`),

KEY `Idx_population_cc` (`Population`,`CountryCode`),

KEY `Idx_cc_population` (`CountryCode`,`Population`)

) ENGINE=MyISAM AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1

EXPLAIN SELECT AVG(Population) FROM city GROUP BY CountryCode\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: c

type: index

possible_keys: NULL

key: Idx_cc_population

key_len: 7

ref: NULL

rows: 4079

Extra: Using index

1 row in set (0.00 sec)

SHOW STATUS LIKE ‘Last_query_cost’\G

*************************** 1. row ***************************
Variable_name: Last_query_cost
Value: 4963.520924

L’optimiseur estime que le coût de la requête est toujours le même, cependant l’index Idx_cc_population(CountryCode,Population) optimise les performance de notre requête car il n’y a plus de Using temporary ni de Using filesort.
Il suffit de pousser encore un peu plus loin notre analyse pour en être définitivement (?) convaincu…

Initialisation des variables de sessions

FLUSH STATUS;

En utilisant l’index idx_population_cc (le mauvais index)

SELECT AVG(Population) FROM city use index(idx_population_cc) GROUP BY CountryCode;

…

232 rows in set (0.00 sec)

SHOW STATUS LIKE ‘handler%’;

 +----------------------------+-------+

 | Variable_name              | Value |

 +----------------------------+-------+

 | Handler_commit             | 0     |

 | Handler_delete             | 0     |

 | Handler_discover           | 0     |

 | Handler_prepare            | 0     |

 | Handler_read_first         | 1     |

 | Handler_read_key           | 4079  |

 | Handler_read_next          | 4079  |

 | Handler_read_prev          | 0     |

 | Handler_read_rnd           | 232   |

 | Handler_read_rnd_next      | 233   |

 | Handler_rollback           | 0     |

 | Handler_savepoint          | 0     |

 | Handler_savepoint_rollback | 0     |

 | Handler_update             | 3847  |

 | Handler_write              | 232   |

 +----------------------------+-------+

Les handler sont des indicateurs liés au moteur de stockage.
Handler_read_first comptabilise le nombre de fois que la première valeur de l’index est lue.
Handler_read_key indique le nombre d’enregistrement récupéré graçe à l’index.
Handler_read_next indique une lecture ordonnée de l’index (une valeur, puis la suivante, puis la suivante…).
Handler_read_first, Handler_read_key & Handler_read_next, indiquent là, un full index scan

Handler_read_rnd & Handler_read_rnd_next indiquent un full table scan sur la table temporaire

Handler_update nous donne une indication sur le nombre de mise à jours dans la table temporaire (à cause du tri)
Handler_write indique le nombre de lignes insérées dans la table temporaire

Ces 2 derniers paramètres confirme donc la création de la table temporaire et l’opération de tri

Pour avoir plus d’informations sur le tri:

SHOW SESSION STATUS LIKE ’sort%’;

 +-------------------+-------+

 | Variable_name     | Value |

 +-------------------+-------+

 | Sort_merge_passes | 0     |

 | Sort_range        | 0     |

 | Sort_rows         | 232   |

 | Sort_scan         | 1     |

 +-------------------+-------+

Sort_rows: nombre de lignes triées (nombre d’enregistrements de la table temporaire).
Sort_scan: nombre de tri.

SHOW SESSION STATUS LIKE ‘created%’;

 +-------------------------+-------+

 | Variable_name           | Value |

 +-------------------------+-------+

 | Created_tmp_disk_tables | 0     |

 | Created_tmp_files       | 0     |

 | Created_tmp_tables      | 1     |

 +-------------------------+-------+

Created_tmp_tables: nombre de table temporaire crée. La bonne nouvelle est que la table temporaire est créee en mémoire (Created_tmp_disk_tables=0)

réinitialise les variables de sessions

FLUSH STATUS;

En utilisant l’index idx_cc_population (le bon index)

SELECT AVG(Population) FROM city use index(idx_population_cc) GROUP BY CountryCode;

…

232 rows in set (0.00 sec)

SHOW STATUS LIKE ‘handler%’;

 +----------------------------+-------+

 | Variable_name              | Value |

 +----------------------------+-------+

 | Handler_commit             | 0     |

 | Handler_delete             | 0     |

 | Handler_discover           | 0     |

 | Handler_prepare            | 0     |

 | Handler_read_first         | 1     |

 | Handler_read_key           | 0     |

 | Handler_read_next          | 4079  |

 | Handler_read_prev          | 0     |

 | Handler_read_rnd           | 0     |

 | Handler_read_rnd_next      | 0     |

 | Handler_rollback           | 0     |

 | Handler_savepoint          | 0     |

 | Handler_savepoint_rollback | 0     |

 | Handler_update             | 0     |

 | Handler_write              | 0     |

 +----------------------------+-------+

Handler_read_first & Handler_read_next valident le full index scan et puis c’est tout

SHOW SESSION STATUS LIKE ’sort%’;

 +-------------------+-------+

 | Variable_name     | Value |

 +-------------------+-------+

 | Sort_merge_passes | 0     |

 | Sort_range        | 0     |

 | Sort_rows         | 0     |

 | Sort_scan         | 0     |

 +-------------------+-------+

pas de tri

SHOW SESSION STATUS LIKE ‘created%’;

 +-------------------------+-------+

 | Variable_name           | Value |

 +-------------------------+-------+

 | Created_tmp_disk_tables | 0     |

 | Created_tmp_files       | 0     |

 | Created_tmp_tables      | 0     |

 +-------------------------+-------+

pas de table(s) temporaire(s)

cqfd

3 Comments

jan

Influencer l’optimiseur de MySQL

Posted by: freshdaz Tags: index, Last_query_cost, mysqlslap, optimiser

Il est possible d’influencer l’optimiseur pour qu’il choisisse d’utiliser ou de ne pas utiliser un index particulier. Les clauses à placer dans votre requête SELECT sont les suivantes:

USE INDEX : utilise l’index passé en argument (MySQL ne l’utilisera pas si l’index est plus couteux qu’un full table scan)

FORCE INDEX : utilise l’index passé en argument (MySQL ne l’utilisera pas …s’il ne peut pas l’utiliser )

IGNORE INDEX : n’utilise pas l’index passé en argument

La plus part du temps, il se débrouille trés bien sans indications, mais parfois…

Dans cet exemple, j’utilise une table rental_daz inspirée de la table rental de la base de donnée sakila, voici sa structure:

12:14 daz$sakila> SHOW CREATE TABLE rental_daz\G
*************************** 1. row ***************************
       Table: rental_daz
Create Table: CREATE TABLE `rental_daz` (
  `rental_id` int(11) NOT NULL AUTO_INCREMENT,
  `rental_date` datetime NOT NULL,
  `inventory_id` mediumint(8) unsigned NOT NULL,
  `customer_id` smallint(5) unsigned NOT NULL,
  `return_date` datetime DEFAULT NULL,
  `staff_id` tinyint(3) unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
       ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`rental_id`),
  UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
  KEY `idx_fk_inventory_id` (`inventory_id`),
  KEY `idx_fk_customer_id` (`customer_id`),
  KEY `idx_fk_staff_id` (`staff_id`)
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8

Voici ma requête:

SELECT * FROM rental_daz WHERE rental_date > SUBDATE(now(), INTERVAL 3 YEAR);

La commande EXPLAIN me permet de connaitre son plan d’exécution:

12:14 daz$sakila> EXPLAIN SELECT * FROM rental_daz
WHERE  rental_date > SUBDATE(now(), INTERVAL  3 YEAR)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental_daz
         type: range
possible_keys: rental_date
          key: rental_date
      key_len: 8
          ref: NULL
         rows: 2744
        Extra: Using where

L’optimiseur voit l’index composite rental_date et l’utilise. Ça à l’air pas mal…

Voyons le coût de cette requête,grâce à au paramètre LAST_QUERY_COST :

12:19 daz$sakila> SHOW STATUS LIKE 'Last_query_cost';
+-----------------+-------------+
| Variable_name   | Value       |
+-----------------+-------------+
| Last_query_cost | 3842.609000 |
+-----------------+-------------+

Empêchons l’optimiseur d’utiliser l’index rental_date:

12:20 daz$sakila> EXPLAIN SELECT * FROM rental_daz IGNORE INDEX(rental_date)
WHERE  rental_date > SUBDATE(now(), INTERVAL 3 YEAR)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental_daz
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16298
        Extra: Using where

L’optimiseur n’utilise donc pas l’index (il ne le voit même pas). Un full table scan est donc effectué. C’est à priori plus coûteux que d’utiliser l’index. Regardons le coût de cette requêtes.

16:18 daz$sakila> SHOW STATUS LIKE 'Last_query_cost';
+-----------------+-------------+
| Variable_name   | Value       |
+-----------------+-------------+
| Last_query_cost | 3356.599000 |
+-----------------+-------------+

Malgré le full table scan, cette requête est moins coûteuse que celle qui utilise l’index !!!

Essayons de vérifier cela en durée. Le client mysqlslap est tout indiqué :

mysqlslap -uroot -proot –create-schema=sakila -i50
-q »SELECT * FROM rental_daz WHERE rental_date > SUBDATE(now(), INTERVAL 3 YEAR); »

Benchmark

        Average number of seconds to run all queries: 0.287 seconds

        Minimum number of seconds to run all queries: 0.140 seconds

        Maximum number of seconds to run all queries: 1.172 seconds

        Number of clients running queries: 1

        Average number of queries per client: 1

mysqlslap -uroot -proot –create-schema=sakila -i50
-q »SELECT * FROM rental_daz ignore index(rental_date) WHERE rental_date > SUBDATE(now(), INTERVAL 3 YEAR); »

Benchmark

        Average number of seconds to run all queries: 0.167 seconds

        Minimum number of seconds to run all queries: 0.078 seconds

        Maximum number of seconds to run all queries: 1.094 seconds

        Number of clients running queries: 1

        Average number of queries per client: 1

mysqlslap confirme bien que le full table scan est, dans ce cas précis, plus performant que la recherche indexée par intervalle. On se trouve bien dans un cas où l’optimiseur se trompe.

3 Comments

dasini.net – Journal d’un expert MySQL

Archive for the ‘optimisation’ Category

MySQL Query cache

Optimisation de requêtes: comprendre l’optimiseur de MySQL

Influencer l’optimiseur de MySQL

Articles récents

Catégories

Archives

Blogoliste

Divers

MySQL