Loading presentation...

Present Remotely

Send the link below via email or IM

Copy

Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.

DeleteCancel

Make your likes visible on Facebook?

Connect your Facebook account to Prezi and let your likes appear on your timeline.
You can change this under Settings & Account at any time.

No, thanks

Dive into PostgreSQL

No description
by

Nikolay Telepenin

on 14 October 2013

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of Dive into PostgreSQL

Dive into PostgreSQL
Телепенин Николай

Барс Груп
@_prefer
telepenin.nikolay@gmail.com
pgFouine
Оптимизация запросов
Репликация
Partitioning
Sharding
Analyze
Vacuum
Vacuum FULL
Autovacuum
pgBouncer
pg-pool
Типы блокировок
Greenplum Database23
GridSQL for EnterpriseDB
Advanced Server24
Sequoia25
PL/Proxy26
HadoopDB2
Connection Pooling
Оптимизация
postgresql.conf
maintenance_work_mem (VACUUM, ANALYZE)
effective_cache_size
work_mem
wal_buffers
checkpoint_segments
shared_buffers
max_connections
pgtune
pg_locks
select
pg_stat_activity.datname,pg_class.relname,pg_locks.transactionid, pg_locks.mode, pg_locks.granted,
pg_stat_activity.usename,substr(pg_stat_activity.current_query,1,30), pg_stat_activity.query_start,
age(now(),pg_stat_activity.query_start) as "age", pg_stat_activity.procpid
from pg_stat_activity,pg_locks left
outer join pg_class on (pg_locks.relation = pg_class.oid)
where pg_locks.pid=pg_stat_activity.procpid order by query_start;
http://wiki.postgresql.org/wiki/Lock_Monitoring
Индексы
B-tree
Hash
GiST
GIN
Partial index
Function-based index
Analyze
Индексы в django - south
http://south.readthedocs.org/en/0.7.6/databaseapi.html#db-create-index
db.create_index(table_name, column_names,
unique=False, db_tablespace='')
db.execute(sql, params=[])
Журнал транзакций
pg_log, pg_cloc, pg_xlog
Перенос на отдельный диск!
Не используйте настройки по умолчанию!
SET enable_seqscan=false;
ALTER TABLE SET STATISTICS
pg_stats
План запроса!
explain
explain analyze
explain (analyze, buffers)
pg_stat_user_tables
pg_stat_user_indexes
pg_statio_user_tables
pg_statio_user_indexes
Отношение hit / read
Количество модификаций
Статистика seq scan / index scan
Статистика по индексам
Размер объектов в БД
SELECT pg_size_pretty(pg_database_size('db'));
SELECT pg_size_pretty(pg_total_relation_size('big_table'));
SELECT pg_size_pretty(pg_relation_size('big_table'));
SELECT
datname,
CASE
WHEN blks_read = 0 THEN 0
ELSE blks_hit / blks_read
END AS ratio
FROM
pg_stat_database;
SELECT
relname,
n_tup_ins,
n_tup_upd,
n_tup_del
FROM
pg_stat_user_tables
ORDER BY
n_tup_upd DESC;
SELECT
relname,
seq_scan,
idx_scan,
CASE
WHEN idx_scan = 0 THEN 100
ELSE seq_scan / idx_scan
END AS ratio
FROM
pg_stat_user_tables
ORDER BY
ratio DESC;
SELECT
indexrelname,
idx_tup_read,
idx_tup_fetch,
(idx_tup_read - idx_tup_fetch),
CASE WHEN idx_tup_read = 0 THEN 0 ELSE (idx_tup_read::float4 -
idx_tup_fetch) / idx_tup_read END as r
FROM
pg_stat_user_indexes
ORDER BY r desc;
Выполняющиеся запросы и блокировки
Полезная информация
http://www.postgresql.org/docs/9.1/static/index.html
https://redmine.bars-open.ru/bg/projects/m3-iaas/wiki
http://postgresmen.ru/articles/view/48
http://wiki.postgresql.org/wiki/Index_Maintenance
http://www.thegeekstuff.com/2009/05/15-advanced-postgresql-commands-with-examples/
http://www.phpconf.ru/u/_files/files/0/30.pdf
Спасибо за внимание
Телепенин Николай
@_prefer

telepenin@bars-open.ru
telepenin.nikolay@gmail.com
RAID
Full transcript