Differences

This shows you the differences between two versions of the page.

--- sysadmin:postgresql [2014/08/27 15:07] – articolo da andreaf feroda
+++ sysadmin:postgresql [2014/11/25 17:17] – occupazione db feroda
@@ Line 1: / Line 1: @@
+====== PostgreSQL - Basi di configurazione ======
+  - eseguire ''/usr/local/sbin/postgresql_shmall_shmmax.sh >> /etc/sysctl.conf''
+  - installare ''pgtune'' per un tuning grezzo di PostgreSQL sulla macchina
+  - eseguire ''pgtune -i /etc/postgresql/9.1/main/postgresql.conf | grep pgtune >> /etc/postgresql/9.1/main/postgresql.conf''
+  - abilitare i log su stderr seguendo la documentazione di [[http://dalibo.github.io/pgbadger/|pgbadger]]
+  - NOTA LOG: attualmente su befair2 i log sono in ''/var/lib/postgresql/9.1/main/pg_log/'' vediamo come si comporta postgresql con rotazione e compressione
+====== Performance ======
   * Performance Tuning: http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
   * Ottimo articolo segnalato da AndreaF http://www.revsys.com/writings/postgresql-performance.html
+  * Index Maintenance https://wiki.postgresql.org/wiki/Index_Maintenance
+===== Query utili su indici e loro utilizzo  =====
+Indici presenti
+    [0] SELECT * FROM pg_indexes;
+==== Ricerca indici doppi ====
+Query "grezza" per noi
+  acs_service=# SELECT tablename,indexname,indexdef from pg_indexes WHERE schemaname='public';
+Query appropriata da http://www.xzilla.net/blog/2008/Jul/Index-pruning-techniques.html
+  [1] select indrelid::regclass, array_accum(indexrelid::regclass) from pg_index group by indrelid, indkey having count(*) > 1;
+Nota: richiede di definire l'aggregato [[http://www.postgresql.org/docs/8.4/static/xaggr.html|array_accum]]
+==== Utilizzo degli indici ====
+Utilizzo degli indici
+    SELECT relid,schemaname,relname,idx_scan,idx_tup_read,idx_tup_fetch FROM pg_stat_all_indexes ORDER BY idx_scan desc;
+Filtra solo quelli utilizzati almeno una volta
+    SELECT * from pg_stat_all_indexes WHERE relname='table' AND idx_scan > 0 ;
+Filtra solo quelli che sono stati efficaci almeno una volta
+    SELECT * from pg_stat_all_indexes WHERE schemaname='public' AND (idx_tup_read > 0 OR idx_tup_fetch > 0);
+Query più appropriata, eseguita su tutti gli schemi (da http://www.xzilla.net/blog/2008/Jul/Index-pruning-techniques.html)
+    [2] select indexrelid::regclass as index, relid::regclass as table from pg_stat_user_indexes JOIN pg_index USING (indexrelid) where idx_scan = 0 and indisunique is false;
+Ho modificato la query per trovare gli indici che sono stati efficaci almeno una volta:
+    [3] select indexrelid::regclass as index, relid::regclass as table from pg_stat_user_indexes JOIN pg_index USING (indexrelid) where (idx_tup_read > 0 OR idx_tup_fetch > 0) and indisunique is false;
+Invece questa la query per indici usati senza essere efficaci
+    [4] select indexrelid::regclass as index, relid::regclass as table from pg_stat_user_indexes JOIN pg_index USING (indexrelid) where (idx_scan >0 and (idx_tup_read = 0 OR idx_tup_fetch = 0)) and indisunique is false;
+===== Query per tabelle =====
+Tabelle vuote (stima. Per una risposta esaustiva vedere http://stackoverflow.com/questions/2596670/how-do-you-find-the-row-count-for-all-your-tables-in-postgres)
+    [5] SELECT schemaname,relname,n_live_tup FROM pg_stat_user_tables WHERE n_live_tup=0;
+===== Esecuzione query al ... =====
+    * [0] = ... indici totali
+    * [1] = ... indici doppi
+    * [2] = ... indici non usati
+    * [3] = ... indici che sono stati efficaci
+    * [4] = ... indici usati ma non efficaci
+    * [5] = ... tabelle vuote
+====== Occupazione disco ======
+===== Database =====
+Lo restituisce pure in formato carino :)
+# select (pg_database_size(current_database())/(1024*1024))::text || ' MB' as db_size;
+===== Tabelle =====
+Restituisce un elenco ordinato in modo discendente di:
+  * schema (aka "directory")
+  * tabella
+  * occupazione su disco
+  * occupazione totale compresi indici
+  * numero stimato di righe
+    SELECT table_schema, table_name,
+      (size/1024)::varchar || ' kB' as size,
+      (total_size/1024)::varchar || ' kB' as total_size,
+      (CASE WHEN pg_class.reltuples > 0 THEN
+          basic_infos.size/(8192*relpages*reltuples)
+          ELSE 0
+          END
+      )::bigint AS estimated_row_count
+      FROM (
+        SELECT table_schema, table_name,
+            pg_relation_size('"' || table_schema || '"."' || table_name || '"') as size,
+            pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') as total_size
+            FROM information_schema.tables
+            WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
+        ORDER BY size DESC, total_size DESC
+    ) AS basic_infos
+        JOIN pg_class
+        ON ('"' || table_schema || '"."' || table_name || '"')::regclass = pg_class.oid
+Questa query funziona su PostgreSQL 8.4. E credo valga anche per PostgreSQL >= 9.0. Potrebbe essere interessante pubblicarla da qualche parte.
+====== Informazioni varie... ======
 ===== Query comode x debug =====
   * Processi attivi: ''select * from pg_stat_activity;''
-  * Indici presenti: ''SELECT * FROM pg_indexes;''
-  * Utilizzo degli indici: ''SELECT * FROM pg_stat_all_indexes;''
   * Utilizzo delle tabelle: ''SELECT * FROM pg_stat_all_tables;''
   * Lock da https://wiki.postgresql.org/wiki/Lock_Monitoring (ho aggiunto il WHERE nel mio caso)