Les shared buffers et pg_buffercache

Je viens de l’écosystème MySQL et j’ai souvent l’habitude d’étudier l’utilisation de l’innodb_buffer_pool qui contient de nombreuses zones mémoires partagées et surtout les données disque les plus accédées. Chez PostgreSQL, il existe la zone mémoire shared_buffers qui a une fonction similaire.

Récemment, l’espace disque d’un serveur d’un de nos clients s’est approché de la saturation. Tout d’abord, je me suis penché sur la question du VACUUM FULL qui récupère l’espace disque du système d’exploitation mais cette option n’est pas efficace. En effet, il s’agit de tables de plusieurs dizaines de GB souvent accédées en écriture ce qui limite beaucoup toute opération en production. De plus, ces tables grossissent sur le long terme. Un VACUUM FULL va récupérer au plus 1GB sur le moment mais l’espace va être ré-alloué peu de temps après.

Dans l’optique commerciale de faire évoluer son serveur, on m’a donc sollicité pour l’élection de configurations de disques pour permettre d’avoir aussi bien de la performance que de l’espace disque, pour un budget limité. Le cas classique de tout client qui se respecte. Le but est donc d’étudier les I/O disques et de les limiter au maximum pour pouvoir proposer du stockage moins performant que du SSD (trop cher pour le client) mais tout à fait correct en termes de performances. Grâce aux tablespaces, on peut même envisager de déplacer certaines bases de données sur un espace de stockage plus performant de type SSD à volumétrie réduite.

Je me suis donc penché sur l’utilisation de la zone mémoire des shared_buffers pour voir si nous pouvions augmenter la capacité de mémoire vive, d’utiliser plus efficacement cette zone importante et d’éviter des opérations disques inutiles afin de pouvoir proposer des disques moins performants mais respectant le budget de notre client.

Il existe l’outil disponible via les modules contrib qui se nomme pg_buffercache et qui s’installe aussi simplement qu’avec un :

CREATE EXTENSION pg_buffercache;

Pour voir les bases de données qui utilisent le plus cette zone mémoire :

  SELECT d.datname as "Database",
         count(*) as "# buffers",
         pg_size_pretty(count(*)*8192) as "Size",
         count(*) * 100 / (select count(*) from pg_buffercache) AS "%"
    FROM pg_buffercache b JOIN pg_database d ON (b.reldatabase = d.oid)
GROUP BY d.datname
ORDER BY 2 DESC
   LIMIT 10;

Ce qui donne un résultat semblable à celui-ci :

    Database     | # buffers |  Size   | %
-----------------+-----------+---------+----
 db1             |    426031 | 3328 MB | 65
 db2             |    173944 | 1359 MB | 26
 db3             |     22018 | 172 MB  |  3
 db4             |      9219 | 72 MB   |  1
 db5             |      4685 | 37 MB   |  0
 db6             |      4595 | 36 MB   |  0
 db7             |      3832 | 30 MB   |  0
 db8             |      2966 | 23 MB   |  0
 db9             |      1331 | 10 MB   |  0
 db10            |      1185 | 9480 kB |  0

A combien de % cette zone est-elle utilisée ?

SELECT count(*) * 100 / (select count(*) from pg_buffercache) AS "Shared Buffers Used (%)"
  FROM pg_buffercache
 WHERE relfilenode IS NOT NULL;

Ce qui donne :

 Shared Buffers Used (%)
-------------------------
                     100

Dans notre cas, il y a peut-être un problème de dimensionnement de cette zone mémoire. Ce n’est pas si grave que ça puisque le cache du filesystem prend le relais mais nous ne pouvons pas avoir de statistiques en termes de base de données sur son utilisation.

On peut même déterminer sa taille via la requête suivante :

SELECT pg_size_pretty(count(*)*8192) AS "shared_buffers"
  FROM pg_buffercache;

Exemple :

 shared_buffers
----------------
 5120 MB

Dans la toute première requête, avec les taux d’utilisation des shared buffers par base de données, on peut voir que notre db1 consomme une majeure partie de cette zone. Si nous voulons de la performance, il serait intéressant de voir quels sont les objets de cette base qui sont en mémoire :

  SELECT c.relname as "Name", count(*) as "buffers", count(*)*100/(SELECT count(*) FROM pg_buffercache) as "%"
    FROM pg_buffercache b INNER JOIN pg_class c
      ON b.relfilenode = pg_relation_filenode(c.oid)
     AND b.reldatabase IN (0, (SELECT oid FROM pg_database WHERE datname = current_database()))
GROUP BY c.relname
ORDER BY 2 DESC
   LIMIT 10;

Cette requête est à lancer depuis une base de données spécifique (à cause de la fonction current_database()). En remplaçant cette fonction, seules les données de la base courante sont remontées. L’extension a besoin d’être créée sur une base de données précise.

Un exemple de résultat :

     Name      | buffers | %
---------------+---------+----
 table1        |  267458 | 40
 table2        |   37010 |  5
 index1        |   24366 |  3
 index2        |   23421 |  3
 primarykey1   |   22452 |  3
 index3        |   21640 |  3
 index4        |   16137 |  2
 primarykey2   |    4359 |  0
 index5        |    3990 |  0
 index6        |    2949 |  0

La table table1 semble être beaucoup accédée et représente 40% du cache à elle seule. Il ne faut pas hésiter à répéter la requête pour voir l’évolution du nombre de buffers utilisés. Ceci est un bon indicateur pour voir une potentielle saturation de cette zone. On pourrait éventuellement envisager de placer cette table volumineuse sur un stockage plus performant.

Pour conclure, pg_buffercache est un bon outil pour détecter de nombreux problèmes de dimensionnement de la zone shared_buffers. Grâce à pg_buffercache, vous pourrez prévenir ou détecter la cause d’I/O disques pénalisants.

Je vous conseille de jeter un coup d’oeil à la documentation qui est un bon début pour découvrir cet outil ! J’ai pu tester ce module sur une instance 9.1.

Automatic Memory Management : comment évoluent vos buffers ?

L’Automatic Memory Management (ou AMM) vous permet de déléguer la gestion du dimensionnement de vos zones mémoire, donc aussi bien la SGA que la PGA, au SGBDR Oracle Database. Vous n’avez rien à faire, Oracle s’occupe de tout. Vous définissez juste un niveau initial de mémoire allouée avec le paramètre MEMORY_TARGET et une taille maximale avec MEMORY_MAX_TARGET. D’un point de vue système, votre instance prendra une taille de mémoire oscillant entre ces deux valeurs.

Cependant, il est souvent intéressant de fouiller un peu dans cette boite noire pour voir ce que fait Oracle pour vous et, éventuellement, de détecter des problèmes d’allocation de mémoire. Par exemple, si vous voyez que le db_cache_size grandit et rétrécit sans arrêt et que la shared_pool_size fait de même, il se peut que vous ayez un problème d’allocation de mémoire. L’opération d’allocation/désallocation est coûteuse. C’est mieux d’éviter ce genre de problème.

Pour surveiller ce phénomène, une vue dynamique existe. Il s’agit de la vue v$sga_resize_ops. La requête suivante vous permet de tracer les opérations réalisées automatiquement par Oracle sur ces zones mémoires sur une semaine :

SELECT
   component,
   parameter,
   oper_type,
   initial_size-target_size as "Difference (bytes)",
   round((initial_size-target_size)/1024/1024, 2) as "Difference (MB)",
   final_size,
   round(final_size/1024/1024, 2) as "Final Size (MB)",
   to_char(end_time, 'DD-MM-YYYY HH24:MI:SS') as "End Time"
FROM     v$sga_resize_ops
WHERE    end_time > (sysdate - 7)
ORDER BY end_time DESC;

Ceci vous donnera le nom du composant, le nom du paramètre associé, s’il s’agit d’une allocation (« GROW ») ou d’une désallocation (« SHRINK »), la taille de l’allocation en byte et en MB, la taille finale du buffer et la date de fin de l’allocation.

Avec ces données, il est possible de réaliser de jolis graphiques pour surveiller leur évolution et de détecter d’éventuels problèmes.

Et vous, êtes-vous plutôt AMM ou allocation manuelle ?

Testé sur une instance 11gR2

MySQL, latin1 et utf8

Prenons l’exemple d’un client qui a une base de données SPIP qui a très mal évolué au cours du temps. Des modifications de colonne, des insertions de caractères dans un format dans une table d’un autre format. Bref, un peu n’importe quoi. Le but de la manœuvre est de fournir un accès phpMyAdmin qui permet d’exploiter les données de cette base afin d’aider une nouvelle équipe de développement qui doit tout refaire de zéro.

Voici ce qu’on peut voir avant toute opération :

mysql> select titre from spip_articles where titre != '' limit 5;
+-------------------+
| titre             |
+-------------------+
| 1. Introduction   |
| 1. Accueil        |
| 2. Historique     |
| Mentions légales |
| Liens             |
+-------------------+
5 rows in set (0.00 sec)

On remarque bien le problème de charset encoding.

Lorsqu’on dump cette table, on s’aperçoit que les données sont encodées en utf8 mais dans une table latin1.

Définition de la table :

CREATE TABLE `spip_articles` (
[...]
) ENGINE=InnoDB AUTO_INCREMENT=155 DEFAULT CHARSET=latin1

Dump de la table :

# mysqldump -v -f -a -c -e --add-drop-table --default-character-set=latin1 mydatabase spip_articles > mydatabase.spip_articles.sql

Et on peut remarquer qu’il s’agit d’un fichier texte encodé en UTF-8 :

# file mydatabase.spip_articles.sql
mydatabase.spip_articles.sql: UTF-8 Unicode C program text, with very long lines

Pour chaque fichier en UTF-8, le but est de changer le charset de la table de latin1 à utf8 :

# sed -i 's/CHARACTER SET utf8 COLLATE utf8_unicode_ci//g' spip_articles.sql
# sed -i 's/COLLATE=utf8_unicode_ci//g' spip_articles.sql
# sed -i 's/COLLATE utf8_unicode_ci//g' spip_articles.sql
# sed -i 's/latin1/utf8/g' spip_articles.sql

Une fois le dump modifié, on peut l’importer :

# mysql mydatabase < mydatabase.spip_articles.sql

La même requête renvoie maintenant des données correctes :

mysql> select titre from mydatabase.spip_articles where titre != '' limit 5;
+------------------+
| titre            |
+------------------+
| 1. Introduction  |
| 1. Accueil       |
| 2. Historique    |
| Mentions légales |
| Liens            |
+------------------+
5 rows in set (0.00 sec)

L’accès phpMyAdmin renvoie aussi les caractères dans le bon format :

mention-legales-after

Pour généraliser les étapes à l’ensemble de la base, il suffit de suivre le script :

#!/bin/bash
DATABASE=mydatabase
NEWDATABASE=mynewdatabase
for table in $(mysql -ss -e 'show tables;' ${DATABASE})
do
   echo "Dump de la table ${table}..."
   mysqldump -v -f -a -c -e --add-drop-table --default-character-set=latin1 ${DATABASE} ${table} > ${table}.sql
   sed -i 's/CHARACTER SET utf8 COLLATE utf8_unicode_ci//g' ${table}.sql
   sed -i 's/COLLATE=utf8_unicode_ci//g' ${table}.sql
   sed -i 's/COLLATE utf8_unicode_ci//g' ${table}.sql
done

for file in $(file *sql | grep UTF-8 | awk '{ print $1 }' | tr -d ':')
do
   sed -i 's/latin1/utf8/g' ${file}
done

for file in $(ls *.sql)
do
   echo "Importing file ${file}..."
   mysql ${NEWDATABASE} < ${file}
done

Ce script se passe en 3 étapes :

  • Dump de chaque table dans un fichier séparé et en retirant certains charset inutiles
  • Modifier le format de la table et de la communication lors de l’import du latin1 vers l’utf8 pour chaque fichier utf8
  • Et, finalement, importer les données.

Les données sont maintenant exploitables.

PostgreSQL, PostGIS et migration de données

PostgreSQL_logoRécemment, j’ai eu l’occasion de m’amuser avec PostgreSQL et le module contrib PostGIS qui ajoute de nombreuses fonctions de calculs géométriques. Cependant, il fallait réaliser un import/export de données entre deux environnements totalement différents. D’un côté, nous avions PostgreSQL en version 8.4 avec PostGIS 1.3.6 et de l’autre, PostgreSQL 9.1 et PostGIS 2.0. Les librairies systèmes liées à PostGIS étaient totalement différentes et certaines fonctions ne sont plus présentes même dans le script legacy.sql (qui ajoute les anciens objets du module pour la rétrocompatibilité). Par cette simple demande client à l’apparence innocente comme il peu y en avoir des dizaines par jour, il a fallu trouver des astuces pour la mener à bien.

PostGIS se compose de plusieurs lib systèmes et d’objets directement importés dans une base de données. Ceci veut dire que les fonctions et les types géométriques se retrouvent dans les dumps. Lorsqu’on importe ces données sur un autre environnements, il est fort probable que les anciennes fonctions utilisent des lib qui ne sont pas compatibles (avec une version de PostGIS supérieure par exemple). Pour cela, j’ai utilisé des options sympathiques des outils pg_dump et pg_restore.

Le processus est le suivant :

old$ pg_dump -v -Fc -f sig.pgcustom sig

Ceci va réaliser un dump au format custom de la base sig. Le format custom est très important puisqu’il est spécifique à PostgreSQL et nous permettra une meilleure manipulation du dump par la suite.

new$ createdb -T template0 sig

Nous avons besoin de créer la base de données sur le nouvel environnement. Ensuite, il faut injecter les nouvelles fonctions de PostGIS dans la nouvelle base toute fraîche.

new$ cd /usr/pgsql-9.1/share/contrib/postgis-2.0/
new$ psql -d sig < postgis.sql
new$ psql -d sig < spatial_ref_sys.sql
new$ psql -d sig < rtpostgis.sql
new$ psql -d sig < topology.sql
new$ psql -d sig < legacy.sql

Une fois que le nouvel environnement est créé, il faut sélectionner ce qu’on veut importer depuis le dump qu’on a créé précédemment. L’outil pg_restore nous permet de créer une liste des objets qu’il contient, d’éditer cette liste avec un éditeur de texte et d’importer les objets de la liste modifiée.

new$ pg_restore -l sig.pgcustom > sig.list

La liste à modifier se trouve dans le fichier sig.list. Il faut supprimer tous les anciens objets liés à PostGIS comme les fonctions, les types, etc… géométriques.

new$ pg_restore -v -L sig.list -d sig sig.pgcustom

Et la restore des données se réalise. Il se peut que des fonctions ne soient plus du tout compatibles car elles ont été renommées ou que les arguments ne correspondent plus. Dans ce cas, il faudra modifier les objets qui utilisent ces fonctions pour qu’ils utilisent les nouvelles.

Bon courage avec PostgreSQL et PostGIS !

UPDATE 24/07/2013 : une autre approche plus efficace, comme nous sommes en version 9.0, aurait été de passer par la commande CREATE EXTENSION au lieu d’injecter les script SQL de l’extension PostGIS. Ceci nous aurait permis de nous libérer un peu plus des dépendances fortement liées au système (pas de chemin d’extension en dur dans les données).

Sources : PostGIS manual (2.0), PostgreSQL documentation (9.1).

Informations utiles sur MySQL 5 avec la base information_schema

Vous êtes connecté sur une instance MySQL 5 et vous êtes un peu perdu ? Vous connaissez que quelques commandes SHOW de base mais vous voulez en savoir plus sur votre serveur MySQL ? La base de données  information_schema a été ajoutée afin de permettre de réaliser des requêtes respectant le standard SQL (AINSI/ISO). Il existe la commande SHOW qui permet de réaliser un bon nombre d’opérations mais la base de données information_schema est en train de prendre le dessus.

Pour commencer, il est toujours utile de savoir la volumétrie des différentes bases de données, le default charset et éventuellement le nombre de routines liées à chaque base. Pour cela, la commande suivante vous sera utile :

SELECT
  s.SCHEMA_NAME                                              AS "Database",
  s.DEFAULT_CHARACTER_SET_NAME                               AS "Charset",
  COUNT(t.TABLE_NAME)                                        AS "Tables",
  (  SELECT COUNT(*)
     FROM information_schema.ROUTINES AS r
	 WHERE r.routine_schema = s.SCHEMA_NAME)                 AS "Routines",
  ROUND(SUM(t.DATA_LENGTH + t.INDEX_LENGTH) / 1048576, 2)    AS "Size MB"
FROM information_schema.SCHEMATA AS s
LEFT JOIN information_schema.TABLES t ON s.schema_name = t.table_schema
WHERE s.SCHEMA_NAME NOT IN ('information_schema', 'performance_schema')
GROUP BY s.schema_name
ORDER BY 5 DESC;

Ce qui donne comme résultat :

+----------------+---------+--------+----------+---------+
| Database       | Charset | Tables | Routines | Size MB |
+----------------+---------+--------+----------+---------+
| database1      | utf8    |    390 |        4 | 9194.88 |
| database3      | latin1  |     96 |        0 |  506.67 |
| database4      | latin1  |     29 |        0 |  160.08 |
| database2      | latin1  |     63 |        0 |   24.23 |
| database6      | latin1  |     25 |        0 |    2.37 |
| mysql          | latin1  |     24 |        0 |    0.57 |
| database5      | latin1  |      2 |        0 |    0.13 |
| database7      | latin1  |      0 |        0 |    NULL |
+----------------+---------+--------+----------+---------+

On peut voir que la base de données database7 est vide. Elle vient probablement tout juste d’être créée. Au contraire, nous pouvons voir que la base de données database1 est la plus volumineuse, elle contient beaucoup de tables et quelques routines. Elle est donc probablement la plus importante et probablement la plus utilisée sur ce serveur. Ce ne sont que des suppositions.

Ensuite, il m’est souvent arrivé de faire de l’optimisation et de devoir définir la taille optimale de certains buffers liés au storage engine tel que MyISAM ou InnoDB pour ne citer que les plus connus. Les buffers les plus connus de ces deux storage engine sont l’innodb_buffer_pool qui est relativement comparable au database_buffer_cache si vous connaissez un peu Oracle et le key_buffer de MyISAM qui met en mémoire les index des tables MyISAM. Avant même de décider de la taille de ces buffers, nous pouvons lancer cette requête pour avoir une idée de la volumétrie par moteur de stockage.

SELECT
   ENGINE                                               AS "Engines",
   COUNT(TABLE_NAME)                                    AS "Tables",
   ROUND(SUM(DATA_LENGTH + INDEX_LENGTH) / 1048576, 2)  AS "Size MB"
FROM information_schema.TABLES
GROUP BY ENGINE
ORDER BY 3 DESC;

Ce qui donne comme résultat :

+--------------------+--------+---------+
| Engines            | Tables | Size MB |
+--------------------+--------+---------+
| InnoDB             |    387 | 8209.61 |
| MyISAM             |    249 | 1679.41 |
| CSV                |      2 |    0.00 |
| MEMORY             |     28 |    0.00 |
| PERFORMANCE_SCHEMA |     17 |    0.00 |
+--------------------+--------+---------+

Dans notre cas, on dispose d’un serveur dédié avec 24GB de mémoire RAM donc on peut facilement positionner l’innodb_buffer_pool_size à 9GB ce qui correspond à la taille totale des données des tables InnoDB + index et avec une marge pour des zones mémoires internes à InnoDB qui se placent dans ce buffer. Si vous êtes un peu limite en mémoire, vous devrez trouver des astuces afin de connaître quelles sont les données les plus importantes à mettre en cache et identifier les données les moins sollicitées. Ce n’est pas notre cas ici.

Afin de définir la taille du key_buffer spécifique à MyISAM, nous avons besoin de savoir quelle est la volumétrie des indexes spécifiques à ce moteur de stockage. Pour cela, nous pouvons utiliser la requête suivante :

SELECT
   ENGINE                                AS "Engine",
   SUM(INDEX_LENGTH)                     AS "Size bytes",
   ROUND(SUM(INDEX_LENGTH) / 1048576, 2) AS "Size MB"
FROM information_schema.TABLES
WHERE ENGINE = 'MyISAM';

Ce qui donne pour résultat :

+--------+------------+---------+
| Engine | Size bytes | Size MB |
+--------+------------+---------+
| MyISAM |  696436736 |  664.17 |
+--------+------------+---------+

La quantité de mémoire disponible sur le serveur nous permet de faire entrer l’ensemble des indexes en mémoire vive et de positionner le key_buffer_size à 696436736 voire 665M pour laisser un peu de marge.

Pour les plus curieux d’entre vous, vous pouvez jeter un coup d’oeil aux tables présentes dans la base de données information_schema. Le nombre d’entre elles croit de version en version et ajoutent des fonctionnalités très intéressantes comme de nouvelles tables spécifiques à InnoDB dans la 5.6 et beaucoup d’autres ! Ceci rassurera peut-être les professionnels d’Oracle pour qui les vues du data dictionary leur manque.

Enjoy !

Testé sur MySQL 5.5, 5.1 et 5.0

Next Posts