{"id":5480,"date":"2016-05-24T06:52:56","date_gmt":"2016-05-24T05:52:56","guid":{"rendered":"http:\/\/blog.capdata.fr\/?p=5480"},"modified":"2019-09-13T13:06:31","modified_gmt":"2019-09-13T12:06:31","slug":"requetes-consommatrices-sous-postgresql-episode-1","status":"publish","type":"post","link":"https:\/\/blog.capdata.fr\/index.php\/requetes-consommatrices-sous-postgresql-episode-1\/","title":{"rendered":"Requ\u00eates consommatrices sous PostgreSQL (\u00e9pisode 1)"},"content":{"rendered":"\"twitter\"<\/a>\"linkedin\"<\/a>\"mail\"<\/a>

Lors de l’\u00e9criture de l’agent AllDB<\/a> pour PostgreSQL je me suis pas mal frott\u00e9 avec les solutions de remont\u00e9e de requ\u00eates lentes donc je me suis dit qu’un petit post sur le sujet ne ferait pas de mal.<\/p>\n

En gros il y a 2 fa\u00e7ons de r\u00e9cup\u00e9rer les requ\u00eates ‘consommatrices’ sous PostgreSQL, via un log ou via une extension.<\/p>\n

Via le log g\u00e9n\u00e9ral:<\/h2>\n

C’est un peu le m\u00eame fonctionnement que sous MySQL o\u00f9 on va indiquer une dur\u00e9e de requ\u00eate minimale et toutes les requ\u00eates au-dessus de ce seuil seront logg\u00e9es dans le fichier de log de l’instance PG.<\/p>\n

On active ce principe via un param\u00e8tre instance dans postgresl.conf:<\/p>\n

–\u00a0log_min_duration_statement = <valeur en ms><\/em><\/p>\n

0 pour tout r\u00e9cup\u00e9rer, -1 par d\u00e9faut pour ne rien r\u00e9cup\u00e9rer, ou tout autre valeur en millisecondes. <\/p>\n

(postgres@[local]:5432) [postgres] > \\x\r\nExpanded display is on.\r\n(postgres@[local]:5432) [postgres] > select * from pg_settings where name = 'log_min_duration_statement' ;\r\n-[ RECORD 1 ]----------------------------------------------------------------------\r\nname | log_min_duration_statement\r\nsetting | -1\r\nunit | ms\r\ncategory | Reporting and Logging \/ When to Log\r\nshort_desc | Sets the minimum execution time above which statements will be logged.\r\nextra_desc | Zero prints all queries. -1 turns this feature off.\r\ncontext | superuser\r\nvartype | integer\r\nsource | default\r\nmin_val | -1\r\nmax_val | 2147483647\r\nenumvals | NULL\r\nboot_val | -1\r\nreset_val | -1\r\nsourcefile | NULL\r\nsourceline | NULL<\/pre>\n
Par exemple:<\/p>\n
(postgres@[local]:5432) [FRCITY] > SET log_min_duration_statement = 30 ;\r\nSET\r\nTime: 0.901 ms\r\n(postgres@[local]:5432) [FRCITY] > select * from towns ;\r\n(...)\r\n(36684 rows)\r\nTime: 56.471 ms<\/pre>\n
Et dans le fichier de log (sous $PGDATA\/pg_log) par d\u00e9faut:<\/p>\n
(...)\r\n2016-05-18 11:15:52 CEST LOG: duration: 56.297 ms statement: select * from towns ;\r\n(...)<\/pre>\n
La diff\u00e9rence de temps vient du fait que dans le log n’est report\u00e9 que le temps pass\u00e9 dans le postmaster, alors que le temps remont\u00e9 par psql est logiquement le temps serveur + client.<\/p>\n
On peut continuer de jouer en modifiant log_line_prefix pour enrichir le contenu de la ligne qui est logg\u00e9e. Par contre il faut modifier dans le fichier postgresql.conf et reloader via pg_ctl reload ou pg_reload_conf():<\/p>\n
log_line_prefix = '%t [%p]: [%l-1] db=%d,user=%u,app=%a,client=%h '<\/pre>\n
R\u00e9sultat dans le fichier:<\/p>\n
16-05-18 11:26:49 CEST [22945]: [3-1] db=FRCITY,user=postgres,app=psql.bin,client=[local] LOG: duration: 62.788 ms statement: select * from towns ;<\/pre>\n
C’est un peu mieux mais quand on vient d’Oracle ou SQL Server, on reste un peu sur sa faim (pas de nombre de blocs logiques \/ physiques lus, de nombre de lignes, de temps CPU vs<\/em> elapsed, etc…)<\/p>\n
Via pg_stat_statements:<\/h2>\n
Pour trouver une alternative, on devra se tourner vers des extensions. <\/p>\n
Pg_stat_statements<\/a>\u00a0(pgss<\/em> pour faire court) est une extension native qui est livr\u00e9e sous la forme d’une librairie dynamique (pg_stat_statements.so<\/em>). Elle logge en fonction de la version diff\u00e9rents aspects op\u00e9rationnels de l’ex\u00e9cution des requ\u00eates, comme le nombre de lignes, le nombre d’appels, le nombre de blocs lus par type, etc… Ces donn\u00e9es sont cumul\u00e9es sur la dur\u00e9e de vie de l’instance, et peuvent \u00eatre m\u00eame r\u00e9initialis\u00e9es \u00e0 la main via une fonction pg_stat_statements_reset().<\/p>\n
Si elle ne se trouve pas dans $(pg_config –libdir)\/postgresql<\/em> , il faut installer les contribs. Par exemple en 9.4:<\/p>\n
$ yum search postgresql | grep contrib\r\nRepo rhel-7-server-rpms forced skip_if_unavailable=True due to: \/etc\/pki\/entitlement\/1652329269877919303-key.pem\r\npostgresql-contrib.x86_64 : Extension modules distributed with PostgreSQL\r\npostgresql94-contrib.x86_64\u00a0: Contributed source and binaries distributed with\r\n\r\n$ yum install\u00a0postgresql94-contrib.x86_64\r\n(...)<\/pre>\n
A partir de l\u00e0, on retrouve la librairie :<\/div>\n
\n
$ ls -lrat \/opt\/PostgreSQL\/9.4\/lib\/postgresql\/pg_stat_statements.so\r\n-rwxr-xr-x. 1 postgres postgres 26298 Oct 6 2015 \/opt\/PostgreSQL\/9.4\/lib\/postgresql\/pg_stat_statements.so<\/pre>\n<\/div>\n
Plusieurs lignes \u00e0 ajouter dans postgresql.conf:<\/p>\n
#-----------------------------------------------------------\r\nshared_preload_libraries = '$libdir\/pg_stat_statements.so'\r\n(...)\r\n\r\n#-----------------------------------------------------------\r\ncustom_variable_classes = 'pg_stat_statements'\r\n\r\npg_stat_statements.max = 4000\r\npg_stat_statements.track = all\r\npg_stat_statements.track_utility = on\r\npg_stat_statements.save = on<\/pre>\n
Sachant que depuis la 9.2 custom_variable_classes n’est plus n\u00e9cessaire<\/a>. Shared_preload_libraries impose un restart de l’instance. Concernant les param\u00e8tres:<\/p>\n