Category Archives: PostgreSQL

Django, PostgreSQL

Ubuntu & Debian PostgreSQL setup

Leave a comment 
#File Locations:
Configuration files: /etc/postgresql/[version]/[cluster]/
Binaries: /usr/lib/postgresql/[version]
Data files: /var/lib/postgresql/[version]/[cluster]
# Instaliraj server i dodatni softvare
apt-get install postgresql
apt-get install postgresql-contrib
# Generiraj hr_HR locale
sudo locale-gen hr_HR.UTF-8
# Postavi da se na bazu može spojiti i lokalno pomoću šifre
vi /etc/postgresql/9.1/main/pg_hba.conf
local   all             all                                     password
# Za development, da se može spojiti na server preko TCP/IP protokola
host   all   all  0.0.0.0/0   password
i u postgresql.conf: listen_address = '*'
# Kreiraj superuser korisnika, pita za šifru
createuser -P -s -e myuser
# Kreiraj bazu
createdb -O myuser -E UTF8 -T template0 --locale=hr_HR.utf8 mydb
# Napravi restart PostgreSQL servisa
sudo /etc/init.d/postgresql restart
PostgreSQL

Postgres Performance

Leave a comment

Vrijedi za PG v9.2
CREATE extension pg_stat_statements;

Ukljući sakupljane statistike o upitima.

SELECT (total_time / 1000 / 60) as total_minutes,
       (total_time/calls) as average_time,
       query
FROM pg_stat_statements
ORDER BY 1 DESC
LIMIT 100;

Upit prikazuje:
1. Koliko se dugo upit odvijao na serveru (ukupno)
2. Koliko se prosjećno upit odvijao na serveru (milisekunde)
3. Sam upit

SELECT sum(heap_blks_read) as heap_read,
       sum(heap_blks_hit) as heap_hit,
       (sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit) as ratio
FROM pg_statio_user_tables;

Upit prikazuje Cache hit rate, 99% je ciljano.

SELECT relname,
       100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used,
       n_live_tup rows_in_table
FROM pg_stat_user_tables
WHERE seq_scan + idx_scan > 0
ORDER BY n_live_tup DESC;

Korištenje Indexa na tablici. 99% na tablici sa preko 10.000 redova, sve ispod toga treba istražiti i dodati nove indexe.

Izvori:
http://craigkerstiens.com/2012/10/01/understanding-postgres-performance/
http://craigkerstiens.com/2013/01/10/more-on-postgres-performance/