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 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 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;

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)

SELECT a.datname,
c.relname,
l.transactionid,
l.mode,
l.granted,
a.usename,
a.current_query,
a.query_start,
age(now(), a.query_start) AS "age",
a.procpid
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.procpid
JOIN pg_class c ON c.oid = l.relation
WHERE mode='RowExclusiveLock' ORDER BY a.query_start;

Avvio manuale di postgres

se service postgresql start offre poca flessibilità usare

pg_ctlcluster 8.4 main start -- -w

o ancora più a basso livello, da utente postgres eseguire:

/usr/lib/postgresql/8.4/bin/pg_ctl start -D /var/lib/postgresql/8.4/main -w -o '-c config_file="/etc/postgresql/8.4/main/postgresql.conf"'