Le blog de Jean David TECHER, un Réunionnais à Saint-Priest/Lyon

Souvent on me pose la question à quoi sert l'option FULL du VACUUM? Je ne vais pas décortiquer ici tout ce que fais le FULL mais juste montrer un exemple pratique d'un UPDATE sur toute une table.

Pour se faire, je vais partir d'un exemple pris du bouquin de référence (que j'avais déjà introduis dans un précédent billet)...

CHARGEMENT DES DONNEES

On va commencer par télécharger les sources en faisant

wget http://www.conjectrix.com/pgbook/source2/bookdata.tar.gz

On les décompresse ensuite

tar xvzf bookdata.tar.gz

Pour rester en conformité avec les données, on va se créer une base performance et un utilisateur korry

postgres@bremko:~$ createdb performance
CREATE DATABASE
postgres@bremko:~$ createuser -s korry
CREATE USER

On se rend dans le sous-répertoire chapter04 qui contient un fichier sql recalls.sql contenant la structure de la table recalls qui va nous servir. On en profite par la même occasion de charger le fichier. Au passage, on notera que le fichier fait 236Mo

postgres@bremko:~/chapter04$ du -hs recalls.sql
236M    recalls.sql
postgres@bremko:~/chapter04$ psql -d performance -f recalls.sql
You are now connected to database "performance" as user "korry".
CREATE TABLE
CREATE INDEX
CREATE INDEX
postgres@bremko:~/chapter04$

Au passage, on se connecte à la base performance pour la suite, on obtient le descriptif suivant

performance=# \d recalls
              Table "public.recalls"
   Column    |          Type           | Modifiers
-------------+-------------------------+-----------
 record_id   | numeric(9,0)            |
 campno      | character(9)            |
 maketxt     | character(25)           |
 modeltxt    | character(25)           |
 yeartxt     | character(4)            |
 mfgcampno   | character(10)           |
 compdesc    | character(75)           |
 mgftxt      | character(30)           |
 bgman       | character(8)            |
 endman      | character(8)            |
 vet         | character(1)            |
 potaff      | numeric(9,0)            |
 ndate       | character(8)            |
 odate       | character(8)            |
 influenced  | character(4)            |
 mfgname     | character(30)           |
 rcdate      | character(8)            |
 datea       | character(8)            |
 rpno        | character(3)            |
 fmvss       | character(3)            |
 desc_defect | character varying(2000) |
 con_defect  | character varying(2000) |
 cor_action  | character varying(2000) |
Indexes:
    "recall_potaff" btree (potaff)
    "recall_record_id" btree (record_id)

CAS D'UN SEUL UPDATE ET ETUDE

Examinons maintenant la taille de la table

performance=# SELECT relname,reltuples,relpages,pg_relation_size(relname)  FROM pg_class WHERE relname='recalls';
 relname | reltuples | relpages | pg_relation_size
---------+-----------+----------+------------------
 recalls |     39241 |     4400 |         36044800
(1 row)

Une page pour PostgreSQL est de 8192 bytes. Ce que nous confirme la commande suivante

performance=# show block_size ;
 block_size
------------
 8192
(1 row)

La table a 39241 tuples et 4400 pages. Ce qui donne donc 4400x8192=36044800.Ce que confirme la fonctionnalité pg_relation_size(). Ici bien sûr, je n'ai pas tenu compte de la place qu'occupait les index. On peut aussi le vérifier sur le disque mais pour celà, il faut remonter l'OID associé à la table recalls. Ce qui s'obtient en faisant

performance=# SELECT relname,relfilenode  FROM pg_class WHERE relname='recalls';
 relname | relfilenode
---------+-------------
 recalls |      188438
(1 row)

Par la suite, j'ai

postgres@bremko:~/chapter04$ ls -al /mnt/pgdata/base/188437/188438
-rw------- 1 postgres postgres 36044800 2008-04-12 10:50 /mnt/pgdata/base/188437/188438

Maintenant si l'option stats_row_level est active depuis le démarrage du serveur (à ne pas faire dans un environnement de prod...), on a aussi

performance=# \x
Expanded display is on.
performance=# Select * from pg_stat_user_tables ;
-[ RECORD 1 ]----+--------
relid            | 188438
schemaname       | public
relname          | recalls
seq_scan         | 2
seq_tup_read     | 78482
idx_scan         | 0
idx_tup_fetch    | 0
n_tup_ins        | 39241
n_tup_upd        | 0
n_tup_del        | 0
last_vacuum      |
last_autovacuum  |
last_analyze     |
last_autoanalyze |

Le champs n_tup_ins indique le nombre de tuples inséré depuis le démarrage du serveur soit 39241 tuples. On va maintenant modifier la table en faisant un simple UPDATE sur le champs potaff

performance=# START TRANSACTION ;
START TRANSACTION
performance=# UPDATE recalls SET potaff = potaff +1;
UPDATE 39241
performance=# END TRANSACTION ;
COMMIT

Revenons à une de nos précédentes requêtes

performance=# SELECT relname,reltuples,relpages,pg_relation_size(relname)  FROM pg_class WHERE relname='recalls';
 relname | reltuples | relpages | pg_relation_size
---------+-----------+----------+------------------
 recalls |     39241 |     4400 |         71868416
(1 row)

On s'apercoit qu'un simple UPDATE sur la table entière à suffit pour doubler la taille de la table! Ce que confirme aussi la commande suivante

postgres@bremko:~/chapter04$ ls -al /mnt/pgdata/base/188437/188438
-rw------- 1 postgres postgres 71868416 2008-04-12 10:50 /mnt/pgdata/base/188437/188438

En revanche le nombre de pages n'est pas à jour. Un simple ANALYZE sur la table suffira pour le mettre à jour

performance=# ANALYZE recalls ;
ANALYZE
performance=# SELECT relname,reltuples,relpages,pg_relation_size(relname)  FROM pg_class WHERE relname='recalls';
 relname | reltuples | relpages | pg_relation_size
---------+-----------+----------+------------------
 recalls |     39493 |     8773 |         71868416
(1 row)

Le nombre de pages a aussi quasiment doublé! C'est maintenant qu'intervient le FULL! Bien sûr, on supposera aussi qu'aucune autre activité n'a lieu pendant ce temps sur la base. PostgreSQL en fait garde une trace des lignes qui sont sujet à modification et le rend non accessibles. Tout celà repose en grande partie sur le principe du MVCC et isolation de transaction qu'à choisi PostgreSQL qui par défaut est en REAL COMMITTED. D'ailleurs PostgreSQL ne supporte que deux niveaux d'isolation

performance=# show transaction_isolation ;
 transaction_isolation
-----------------------
 read committed
(1 row)

So let's go now for notre FULL de la mort qui tue le diable

performance=# VACUUM FULL recalls ;
VACUUM
performance=# SELECT relname,reltuples,relpages,pg_relation_size(relname)  FROM pg_class WHERE relname='recalls';
 relname | reltuples | relpages | pg_relation_size
---------+-----------+----------+------------------
 recalls |     39241 |     4248 |         34799616
(1 row)

Or mon remarquera que par rapport à la taille initiale on a 34799616 < 36044800 ce qui est tout à fait normal car le FULL essait de "compresser/réagencer au mieux" l'espace disque sur les blocks de la table (déplace etc...), un peu comme le principe de la défragmentation sous Windaube. Dès fois le gain n'est pas important mais parfois il peut le devenir en fonction de ce qui a dans la base.

Donc des updates fréquents sur une table font grossir la table . Voilà une des raisons pour laquelle un FULL s'avère efficace pour récupérer l'espace disque lorsque ce dernier arrive à moins de 10 à 20 % de sa capacité.

Actuellement j'ai donc en utilisant pg_size_pretty () et pg_total_relation_size()

performance=# SELECT relname,reltuples,relpages,pg_size_pretty(pg_total_relation_size(relname))  FROM pg_class WHERE relname='recalls';
 relname | reltuples | relpages | pg_size_pretty
---------+-----------+----------+----------------
 recalls |     39241 |     4248 | 39 MB
(1 row)

Ma table occupe donc en y incluant (les index etc...) 39 MB sur le disque. Imaginons maintenant 10 UPDATES successifs comme suit

...ET POUR 10 UPDATES IDENTIQUES???

START TRANSACTION;
UPDATE recalls SET potaff=potaff+1;
UPDATE recalls SET potaff=potaff+1;
UPDATE recalls SET potaff=potaff+1;
UPDATE recalls SET potaff=potaff+1;
UPDATE recalls SET potaff=potaff+1;
UPDATE recalls SET potaff=potaff+1;
UPDATE recalls SET potaff=potaff+1;
UPDATE recalls SET potaff=potaff+1;
UPDATE recalls SET potaff=potaff+1;
UPDATE recalls SET potaff=potaff+1;
END TRANSACTION;

Dans un environnement de production, si le serveur tient la charge, on peut imaginer que celà correspond à 10 connexions au serveur de manière séquentiels (ce qui est encore l'un des meilleurs cas par rapport à ce accès concurrents faisant monté en charge le CPU) répétant la même requête...Essayez d'imaginer la taille de votre table par la suite! Sans faire de FULL au bout de 6 UPDATES, on est passé de 39 M à plus de 260M. :(. Et encore on en se plaindra pas car il s'agit ici d'un table de taille très raisonnable.

Faisons donc les tests! On en profitera pour créer une table qui stockera au fur et à mesure l'avancée suite à un requête de mise jour

START TRANSACTION;
CREATE TABLE stats_size(id serial,dtsize numeric);
INSERT INTO stats_size(dtsize) VALUES ((SELECT trim(both ' MB' from pg_size_pretty(pg_total_relation_size('recalls')))::numeric));
UPDATE recalls SET potaff=potaff+1;
INSERT INTO stats_size(dtsize) VALUES ((SELECT trim(both ' MB' from pg_size_pretty(pg_total_relation_size('recalls')))::numeric));
UPDATE recalls SET potaff=potaff+1;
INSERT INTO stats_size(dtsize) VALUES ((SELECT trim(both ' MB' from pg_size_pretty(pg_total_relation_size('recalls')))::numeric));
UPDATE recalls SET potaff=potaff+1;
INSERT INTO stats_size(dtsize) VALUES ((SELECT trim(both ' MB' from pg_size_pretty(pg_total_relation_size('recalls')))::numeric));
UPDATE recalls SET potaff=potaff+1;
INSERT INTO stats_size(dtsize) VALUES ((SELECT trim(both ' MB' from pg_size_pretty(pg_total_relation_size('recalls')))::numeric));
UPDATE recalls SET potaff=potaff+1;
INSERT INTO stats_size(dtsize) VALUES ((SELECT trim(both ' MB' from pg_size_pretty(pg_total_relation_size('recalls')))::numeric));
UPDATE recalls SET potaff=potaff+1;
INSERT INTO stats_size(dtsize) VALUES ((SELECT trim(both ' MB' from pg_size_pretty(pg_total_relation_size('recalls')))::numeric));
UPDATE recalls SET potaff=potaff+1;
INSERT INTO stats_size(dtsize) VALUES ((SELECT trim(both ' MB' from pg_size_pretty(pg_total_relation_size('recalls')))::numeric));
UPDATE recalls SET potaff=potaff+1;
INSERT INTO stats_size(dtsize) VALUES ((SELECT trim(both ' MB' from pg_size_pretty(pg_total_relation_size('recalls')))::numeric));
UPDATE recalls SET potaff=potaff+1;
INSERT INTO stats_size(dtsize) VALUES ((SELECT trim(both ' MB' from pg_size_pretty(pg_total_relation_size('recalls')))::numeric));
UPDATE recalls SET potaff=potaff+1;
INSERT INTO stats_size(dtsize) VALUES ((SELECT trim(both ' MB' from pg_size_pretty(pg_total_relation_size('recalls')))::numeric));
END TRANSACTION;

Ce qui donne donc

performance=# select * from stats_size;
 id | dtsize
----+--------
  1 |     36
  2 |     74
  3 |    111
  4 |    147
  5 |    185
  6 |    222
  7 |    258
  8 |    295
  9 |    333
 10 |    369
 11 |    407
(11 rows)

En effectuant 10 fois de suite la même requête on est passé de 36 à 407 soit un facteur de 10 pour la totalité de la table qui augmente de manière quasi linéraire comme le montre le graphique suivant
Taille de la table suite à 10 UPDATE successifs Faisons maintenant un VACUUM FULL ANALYZE et insérons la nouvelle valeur dans la table stats_size en faisant;

performance=# VACUUM FULL ANALYZE recalls ;
VACUUM
performance=# INSERT INTO stats_size(dtsize) VALUES ((SELECT trim(both ' MB' from pg_size_pretty(pg_total_relation_size('recalls')))::numeric));
INSERT 0 1

Et on obtient
Taille de la table suite à 10 UPDATE successifs + VACUUM FULL ANALYZE Et hop! On ne s'affolera pas ici mais ce simple exemple montre au combien il est nécessaire de penser à faire un FULL sur sa base lorsque des mises à jours sont fréquents sur une ou plusieurs tables de la base en production. On voit donc que lorsqu'on a des mises à jour fréquentes sur sa/ses tables(s), il est nécessaire de planifier un FULL. En revanche, il ne faut pas non plus oublier qu'un FULL est bien plus long qu'un simple VACUUM, que dans l'usage on ne le fait pas souvent non plus....Pour de plus amples informations, on pourra toujours se reporter à la documentation de PostgreSQLFR sur le VACUUM

Cette aprem', comme je dois me remettre à flot pour PostgreSQL, je devais réviser les BLOBS (Binary Large OBject)...Pour se faire, 'j'ai récupérer une image sur le Web d'Ewa Sonnet dont j'ai profité pour mettre en fond d'écran (ce qui m'a bien sûr value une remarque des plus .... d'une de mes collègues Pourquoi vous les mecs vous ne mettez que des photos de femme à poil...! )

No comment!

My WallPaper used for test on Binary Large OBject

Let's go!...On commence par télécharger l'image

 wget http://img.wallpapers-zone.com/wallpapers/stars_et_top_models_femmes/ewa_sonnet/ewa_sonnet_001.jpg

Ensuite on crée la table

postgres@bremko:~$ psql testgis
Welcome to psql 8.2.3, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

testgis=# create table testblob(numero serial,nom text,source oid)with oids;
NOTICE:  CREATE TABLE will create implicit sequence "testblob_numero_seq" for serial column "testblob.numero"
CREATE TABLE
testgis=#

Ensuite on insère grâce à lo_import()

testgis=# START TRANSACTION;
START TRANSACTION
testgis=# INSERT INTO testblob(nom,source) VALUES ('Ewa Sonnet',lo_import('/home/postgres/ewa_sonnet_001.jpg'));
INSERT 188428 1
testgis=# END TRANSACTION;
COMMIT
testgis=#

Pour l'exportation, on utilise tout simplement lo_export()

testgis=# START TRANSACTION ;
START TRANSACTION
testgis=# SELECT lo_export(source,'/home/postgres/ewa_sonnet.jpg') from testblob;
 lo_export
-----------
         1
(1 row)

testgis=# END TRANSACTION;
COMMIT
testgis=#

On peut vérifier que le fichier à bien été créé sans sortir de psql

testgis=# \! ls -al '/home/postgres/ewa_sonnet.jpg'
-rw-r--r-- 1 postgres postgres 104073 2008-04-11 22:53 /home/postgres/ewa_sonnet.jpg

Ca va je n'ai pas trop rouillé au niveau des fonctionnolités.

Remarque: ici les début et fin de transaction ne sont pas obligatoire mais celà vient de mon habitude avec les BLOB.

Aya j'ai commencé depuis lundi 31 mars chez F.T et intégré ma nouvelle équipe de travail...Les horaires sont les heures de bureaux classique. Le travail est plaisant, bonne ambiance dans le bureau actuel!

Cette semaine j'ai préféré ramené ma gamelle de bouffe le midi au taf, histoire de remmettre à zéro mes finances après mes deux mois de fête...

Cette semaine j'ai appris qu'un de mes coloc's allait s'installé avec dulciné sur Nîmes..La vie continue...Lui et moi, on est sorti une dernière fois ensemble la semaine dernière (histoire de marquer le coup)...Je sentais bien qu'il allait partir :)...C'est comme ça les destinées se croisent et se décroisent! Très très bonne semaine!

En tout cas Ludo mon pote ce soir-là, tu m'as sorti une phrase à mon retour quand tu m'attendais aors que je courais couru après les 3 suédoises (avec qui j'ai dansé et ??? plus que dansé...)...Tu m'as dis

"Moi je regarde sur la droite et mercenaire qui arrive sur ma gauche! LOL"

Allez bandit! Bon courage à toi dans ta nouvelle vie...Mercenaire sera toujours là pour toi

P.S: je te ramène une partie de tes affaires comme prévu ce week-end en remontant sur Montpellier

Ceci est une petite astuce de PostgreSQL paru dans le cookbook de PostgreSQLFR qui permet de remonter l'oid par exemple associé à une table

On se connecte à la base

psql igo

On remontre l'OID et le nom de la table 'article';

igo=# select relname,relfilenode from pg_class where relname='article';
 relname | relfilenode 
---------+-------------
 article |       42859
(1 row)

Comme son oid vaut 42859 qui est supérieur à 16384 (valeur max des OID lors d'un initdb), pourr remonter l'OID

igo=# select 42859::regclass;
 regclass 
----------
 article
(1 row)

Très pratique comme requête car très comme dans sa formulation.