Suite à mes précédents billets, qui étaient assez empirique il est temps de se poser la question: pour une stratégie de partionnement de table, qu'est-ce qui s'avérer le plus coûteux?
Nous allons ici exploiter la possibilité qu'offre le formattage des fichiers de log de PostgreSQL vers le format CSV pour proposer ici un exemple d'illustration.
Serveur PostgreSQL et configuration
On prendra un serveur PostgreSQL 8.3
david@bremko:/var/lib/david$ pg_config --version
PostgreSQL 8.3.3
On a la configuration suivante
david@bremko:/var/lib/david$ grep -v '^#' $(PGDATABASE=test psql -At -p 5432 -h localhost -c "show config_file")|sed -e "s:#.*$::g"|grep '^[a-z]'
max_connections = 100
shared_buffers = 32MB
max_fsm_pages = 204800
log_destination = 'csvlog'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_min_duration_statement = 0
log_checkpoints = on
log_connections = on
log_disconnections = on
log_duration = on
log_hostname = on
log_line_prefix = '%u'
log_statement = 'all'
datestyle = 'iso, dmy'
lc_messages = 'fr_FR.UTF-8'
lc_monetary = 'fr_FR.UTF-8'
lc_numeric = 'fr_FR.UTF-8'
lc_time = 'fr_FR.UTF-8'
default_text_search_config = 'pg_catalog.french'
Remarque:Il faut penser à créer ou vider le répertoire $PGDATA/pg_log si nécessaire
export PATH=/opt/postgresql-8.3.3/bin:$PATH
export PGDATA=/var/lib/david
rm -rf $PGDATA/pg_log;mkdir $PGDATA/pg_log
La structure de la base sera la suivante contenu du fichier mesure_schema.sql
BEGIN;
CREATE TABLE mesure(value float,repetition integer default 0);
CREATE TABLE mesure_100000 ( check (value >= 0 and value<100000) ) INHERITS (mesure);
CREATE TABLE mesure_200000 ( check (value >= 100000 and value<200000)) INHERITS (mesure);
CREATE TABLE mesure_300000 ( check (value >= 200000 and value<300000)) INHERITS (mesure);
CREATE TABLE mesure_400000 ( check (value >= 300000 and value<400000)) INHERITS (mesure);
CREATE TABLE mesure_500000 (check (value >= 400000 and value<500000) ) INHERITS (mesure);
CREATE TABLE mesure_600000 ( check (value >= 500000 and value<600000)) INHERITS (mesure);
CREATE TABLE mesure_700000 ( check (value >= 600000 and value<700000)) INHERITS (mesure);
CREATE TABLE mesure_800000 ( check (value >= 700000 and value<800000)) INHERITS (mesure);
CREATE TABLE mesure_900000 ( check (value >= 800000 and value<900000)) INHERITS (mesure);
CREATE TABLE mesure_1000000 (check (value >= 900000 and value<1000000) ) INHERITS (mesure);
CREATE TABLE mesure_out ( check (value < 0 or value>1000000)) INHERITS (mesure);
CREATE OR REPLACE RULE mesure_insert_100000 AS ON INSERT TO mesure WHERE ( NEW.value>=0 and NEW.value<100000) DO INSTEAD INSERT INTO mesure_100000 VALUES (NEW.value);
CREATE OR REPLACE RULE mesure_insert_200000 AS ON INSERT TO mesure WHERE ( NEW.value>=100000 and NEW.value<200000) DO INSTEAD INSERT INTO mesure_200000 VALUES (NEW.value);
CREATE OR REPLACE RULE mesure_insert_300000 AS ON INSERT TO mesure WHERE ( NEW.value>=200000 and NEW.value<300000) DO INSTEAD INSERT INTO mesure_300000 VALUES (NEW.value);
CREATE OR REPLACE RULE mesure_insert_400000 AS ON INSERT TO mesure WHERE ( NEW.value>=300000 and NEW.value<400000) DO INSTEAD INSERT INTO mesure_400000 VALUES (NEW.value);
CREATE OR REPLACE RULE mesure_insert_500000 AS ON INSERT TO mesure WHERE ( NEW.value>=400000 and NEW.value<500000) DO INSTEAD INSERT INTO mesure_500000 VALUES (NEW.value);
CREATE OR REPLACE RULE mesure_insert_600000 AS ON INSERT TO mesure WHERE ( NEW.value>=500000 and NEW.value<600000) DO INSTEAD INSERT INTO mesure_600000 VALUES (NEW.value);
CREATE OR REPLACE RULE mesure_insert_700000 AS ON INSERT TO mesure WHERE ( NEW.value>=600000 and NEW.value<700000) DO INSTEAD INSERT INTO mesure_700000 VALUES (NEW.value);
CREATE OR REPLACE RULE mesure_insert_800000 AS ON INSERT TO mesure WHERE ( NEW.value>=700000 and NEW.value<800000) DO INSTEAD INSERT INTO mesure_800000 VALUES (NEW.value);
CREATE OR REPLACE RULE mesure_insert_900000 AS ON INSERT TO mesure WHERE ( NEW.value>=800000 and NEW.value<900000) DO INSTEAD INSERT INTO mesure_900000 VALUES (NEW.value);
CREATE OR REPLACE RULE mesure_insert_1000000 AS ON INSERT TO mesure WHERE ( NEW.value>=900000 and NEW.value<1000000) DO INSTEAD INSERT INTO mesure_1000000 VALUES (NEW.value);
CREATE OR REPLACE RULE mesure_insert_out AS ON INSERT TO mesure WHERE ( NEW.value < 0 or NEW.value>1000000) DO INSTEAD INSERT INTO mesure_out VALUES (NEW.value);
CREATE INDEX m_idx ON mesure(value);
CREATE INDEX m_idx_100000 ON mesure_100000(value);
CREATE INDEX m_idx_200000 ON mesure_200000(value);
CREATE INDEX m_idx_300000 ON mesure_300000(value);
CREATE INDEX m_idx_400000 ON mesure_400000(value);
CREATE INDEX m_idx_500000 ON mesure_500000(value);
CREATE INDEX m_idx_600000 ON mesure_600000(value);
CREATE INDEX m_idx_700000 ON mesure_700000(value);
CREATE INDEX m_idx_800000 ON mesure_800000(value);
CREATE INDEX m_idx_900000 ON mesure_900000(value);
CREATE INDEX m_idx_1000000 ON mesure_1000000(value);
CREATE INDEX m_idx_out ON mesure_out(value);
CREATE VIEW mesure_all as
SELECT * FROM mesure_100000
UNION ALL SELECT * FROM mesure_200000
UNION ALL SELECT * FROM mesure_300000
UNION ALL SELECT * FROM mesure_400000
UNION ALL SELECT * FROM mesure_500000
UNION ALL SELECT * FROM mesure_600000
UNION ALL SELECT * FROM mesure_700000
UNION ALL SELECT * FROM mesure_800000
UNION ALL SELECT * FROM mesure_900000
UNION ALL SELECT * FROM mesure_1000000
UNION ALL SELECT * FROM mesure_out;
CREATE TABLE t_time(t_id serial,t_cumul timestamp);
CREATE VIEW t_time_test AS SELECT b.t_id-1,date_part('second',b.t_cumul-a.t_cumul) FROM t_time a JOIN t_time b ON (b.t_id-a.t_id=1);
CREATE TABLE postgres_log
(
log_time timestamp(3) with time zone,
user_name text,
database_name text,
process_id integer,
connection_from text,
session_id text,
session_line_num bigint,
command_tag text,
session_start_time timestamp with time zone,
virtual_transaction_id text,
transaction_id bigint,
error_severity text,
sql_state_code text,
message text,
detail text,
hint text,
internal_query text,
internal_query_pos integer,
context text,
query text,
query_pos integer,
location text,
PRIMARY KEY (session_id, session_line_num)
);
END;
Préparation des données à tester - échantillon de 450 000 valeurs
Ici on a maintenant 200 000 lignes dans notre fichier
david@bremko:/var/lib/david$ wc -l values_all.data
200000 values_all.data
On en ajoute (au moins) quelques 150 500 de plus
echo "Début de la génération := $(date)";
export NbData=150000;
for((i=0;i<=${NbData};i++));
do psql -At -F" " -c "SELECT (random()*1500000)::integer" -d test >> values_all.data;
done;
On a le rendu suivant
On va émettre un son quand le fichier sera remplit en faisant
while [ $(wc -l values_all.data|awk {'print $1'}) -lt 262000 ];
do
echo > /dev/null;
done;
while true;
do cat /usr/share/doc/libsdl-erlang/examples/Beep.wav > /dev/audio;
done
Ce qui m'a permis d'aller faire une sieste en attendant 
...A mon retour ceci tournait encore, j'ai donc arrêté la génératione faisant [CRTL]+[C]. Il me manquait une centaines de milliers de lignes
Astuce!: sinon pour aller bien plus vite
psql -d test -At -F" " -c "select (random()*1500000)::integer from generate_series(1,127045)" >> values_all.data
J'ai donc obtenu
david@bremko:/var/lib/david$ wc -l values_all.data
450000 values_all.data
Nickel 
!
Exécution du script
....
dropdb test
createdb test
psql -d test -f mesure_schema.sql
date;PGHOST=localhost ./pg_exec.pl values_all.data ;date
pg_ctl stop
mv data/pg_log data/pg_log.old
Génération des graphes
Population de postgres_log
<
Onn redémarre le serveur et on on charge les données dans la table postgres_log
pg_ctl start
mkdir $PGDATA/pg_log
cd $PGDATA/pg_log.old
for file in $(ls *.csv);do psql -d test -c "COPY postgres_log FROM '$PWD/$file' WITH csv;";done
Il faut ensuite par créer et populer les tables nécessaires pour les trois opérations (SELECT, UPDATE,INSERT) en chargeant le contenu du fichier mesure_schema_2.sql
begin;
create table s_selects(id serial,s_duration real);
create table s_inserts(id serial,s_duration real);
create table s_updates(id serial,s_duration real) ;
insert into s_selects(s_duration) select case when command_tag='SELECT' then replace(replace(message,'duration: ',''),' ms','')::real else 0::real end as s_duration from postgres_log where command_tag in ('UPDATE','INSERT','SELECT') and message ~ 'duration';
insert into s_inserts(s_duration) select case when command_tag='INSERT' then replace(replace(message,'duration: ',''),' ms','')::real else 0::real end as s_duration from postgres_log where command_tag in ('UPDATE','INSERT','SELECT') and message ~ 'duration';
insert into s_updates(s_duration) select case when command_tag='UPDATE' then replace(replace(message,'duration: ',''),' ms','')::real else 0::real end as s_duration from postgres_log where command_tag in ('UPDATE','INSERT','SELECT') and message ~ 'duration';
end;
Ce qui se fait en faisant
psql -d test -f mesure_schema_2.sql
Nous sommes maintenant prêt pour générer nos graphes. Pour se faire, nous pouvons utiliser GnuPlot.
Il faut générer les fichiers qui contiendront les données à grapher
for query in selects inserts updates;do psql -At -F" " -d test -c "SELECT * FROM s_$query" > values.$query.data;done
Avec GnuPlot, on fait
gnuplot>set yrange [0:40]
gnuplot> set xlabel "Numero operation"
gnuplot> set ylabel "Temps en millisecondes"
gnuplot> plot 'values.selects.data' w lines lc 2 title 'SELECT count(*) FROM mesure WHERE value=?'
gnuplot> plot 'values.inserts.data' w lines lc 3 title 'INSERT INTO mesure(value) VALUES (?)'
gnuplot> plot 'values.updates.data' w lines lc 1 title 'UPDATE mesure SET repetition=repetition+1 WHERE value=?'
On a alors les graphes suivants
Fig.1 - Opérations sur INSERT
Fig.2 - Opérations sur SELECT
Fig.3 - Opérations sur UPDATE
Mais attention, il ne faut pas sauter au plafon de suite. Par exemple en zoomant de l'opération 174200 à 174300, on a le graphe suivant
Fig.4 - Opérations de l'opération 174200 à 174300.
En zoomant de l'opération 150000 à 20000, on a le fichier suivant
Mais attention, il ne faut pas sauter au plafon de suite. Par exemple en zoomant de l'opération 174200 à 174300, on a le graphe suivant
Fig.5 - Opérations de l'opération 150000 à 200000.
!!!
... Du coup j'en ai profité pour prendre un café à la caféteria.On m'a rappelé pour aller les rejoindre sur mon porable. Sur la route malgré les indication fournies, je me suis gourré de route! J'ai du rappeler pour retrouver ma route. Je suis arrivé finalement à destination mais avec 45 minutes de retard...
