Skip to content

Requetes systèmes

DBPostgreSQL

Pour des raisons diverses et variées il se peut que l'on soit obligé de compiler les sources d'un package plutot que d'utiliser le package d'origine.

J'ai besoin d'utiliser le démon nrpe avec le passage d'argument, pour des raisons de sécurité Debian à choisi de fournir le package sans cette option. Il faut donc recompiler en y intégrant cette option (--enable-command-args)

Quelques requêtes utiles :

 Taille des tables et index avec proportion:


SELECT
    e1.relname AS table,
    pg_size_pretty(pg_relation_size(e1.oid)) AS "table size",
    CASE
        WHEN e3.oid IS NULL THEN 'O bytes'
        WHEN e3.oid IS NOT NULL THEN pg_size_pretty(pg_total_relation_size(e3.oid))
    END AS "TOAST size",
    pg_size_pretty(sum(pg_relation_size(e2.oid))) AS "Index size",
    pg_size_pretty(pg_total_relation_size(e1.oid)) AS "total table size",
    round(sum(pg_relation_size(e2.oid))*100/pg_total_relation_size(e1.oid)) AS "ratio"
FROM pg_class e1
    JOIN pg_index i ON e1.oid=i.indrelid
    JOIN pg_class e2 ON i.indexrelid=e2.oid
    LEFT JOIN pg_class e3 ON e1.reltoastrelid=e3.oid
WHERE
    (e1.relkind='r' OR e1.relkind='t')
    AND pg_relation_size(e1.oid) != 0
GROUP BY e1.relname, e1.oid, e3.oid
ORDER BY sum(pg_relation_size(e2.oid)) DESC;


Taille des tables avec taille des index non compris


SELECT
    e1.relname AS table,
    e2.relname AS "index name",
    pg_size_pretty(pg_relation_size(e1.oid)) AS "table size",
    CASE
        WHEN e3.oid IS NULL THEN '0 bytes'
        WHEN e3.oid IS NOT NULL THEN pg_size_pretty(pg_relation_size(e3.oid))
    END AS "TOAST size",
    CASE
        WHEN e2.oid IS NULL THEN '0 bytes'
        WHEN e2.oid IS NOT NULL THEN pg_size_pretty(pg_relation_size(e2.oid))
    END AS "Index size",
    pg_size_pretty(pg_total_relation_size(e1.oid)) AS "total table size"
FROM pg_class e1
    JOIN pg_index i ON e1.oid=i.indrelid
    JOIN pg_class e2 ON i.indexrelid=e2.oid
    LEFT JOIN pg_class e3 ON e1.reltoastrelid=e3.oid
WHERE
    (e1.relkind='r' OR e1.relkind='t')
    AND pg_relation_size(e1.oid) != 0
ORDER BY pg_total_relation_size(e1.oid) DESC;

 

Les 5 plus grosses tables :

 

SELECT nspname || '.' || relname AS "relation",pg_size_pretty(pg_relation_size(C.oid)) AS "size"
  FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  ORDER BY pg_relation_size(C.oid) DESC
  LIMIT 5;

 

Tables avec des lignes mortes dans la base

 

select relname,n_dead_tup,n_live_tup from pg_stat_all_tables order by n_dead_tup desc;

 

Taille des lignes mortes pour chaque table (postgres superieur à 9)

CREATE EXTENSION pgstattuple;
SELECT relname as table_name, pg_size_pretty(dead_tuple_len) AS dead_tuple_size
FROM
    (SELECT relname, (pgstattuple(oid)).dead_tuple_len AS dead_tuple_len FROM pg_class WHERE relkind='r')
    AS stattuple
ORDER BY dead_tuple_len DESC;

 

Taille des lignes mortes pour la base (postgres superieur à 9)

SELECT pg_size_pretty(sum((pgstattuple(oid)).dead_tuple_len)) AS total_dead_tuple_len FROM pg_class WHERE relkind='r';

 

Taille des tables de TOAST 

SELECT a, n, pg_relation_size(t), pg_size_pretty(pg_relation_size(t))
FROM   (SELECT c.relname, c.reltoastrelid, d.relname
        FROM   pg_class c JOIN pg_class d ON c.reltoastrelid = d.oid
       ) AS x(a, t, n)
WHERE  t > 0 AND pg_relation_size(t) > 0
ORDER BY 3 DESC;

 

Liste de toutes les fonctions 

SELECT n.nspname as "Schema",
  p.proname as "Name",
  pg_catalog.pg_get_function_result(p.oid) as "Result data type",
  pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
 CASE
  WHEN p.proisagg THEN 'agg'
  WHEN p.proiswindow THEN 'window'
  WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger'
  ELSE 'normal'
 END as "Type"
FROM pg_catalog.pg_proc p
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE pg_catalog.pg_function_is_visible(p.oid)
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
ORDER BY 1, 2, 4;

          autre possibilité :

\df <nom_schema>.*

ou

\dfS <nom_schema>.*

ou

\dfS+ <nom_schema>.*

pour les fonction interne de postgres 

\df pg_* 

Rétroliens

Pas de rétroliens

Commentaires

Afficher les commentaires en Vue non groupée | Vue groupée

Pas de commentaires

Ajouter un commentaire

Marquer un texte en gras: *mot*, souligner un texte: _mot_.
Les smilies standard comme :-) et ;-) sont convertis en images.
Les adresses Email ne sont pas affichées, et sont seulement utilisées pour la communication.
Syntaxe BBCode autorisée
Form options