# SELECT c.relname, count(*) AS buffers FROM pg_buffercache b INNER JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid) AND b.reldatabase IN (0, (SELECT oid FROM pg_database WHERE datname = current_database())) GROUP BY c.relname ORDER BY 2 DESC LIMIT 10; relname | buffers ---------------------------------+--------- pgbench_accounts | 4082 pgbench_history | 53 pg_attribute | 23 pg_proc | 14 pg_operator | 11 pg_proc_oid_index | 9 pg_class | 8 pg_attribute_relid_attnum_index | 7 pg_proc_proname_args_nsp_index | 6 pg_class_oid_index | 5 (10 rows)
Этот запрос показывает объекты (таблицы и индексы) в кэше:
# SELECT c.relname, count(*) AS buffers,usagecount FROM pg_class c INNER JOIN pg_buffercache b ON b.relfilenode = c.relfilenode INNER JOIN pg_database d ON (b.reldatabase = d.oid AND d.datname = current_database()) GROUP BY c.relname,usagecount ORDER
BY c.relname,usagecount; relname | buffers | usagecount ----------------------------------+---------+------------ pg_rewrite | 3 | 1 pg_rewrite_rel_rulename_index | 1 | 1 pg_rewrite_rel_rulename_index | 1 | 2 pg_statistic | 1 | 1 pg_statistic | 1 | 3 pg_statistic | 2 | 5 pg_statistic_relid_att_inh_index | 1 | 1 pg_statistic_relid_att_inh_index | 3 | 5 pgbench_accounts | 4082 | 2 pgbench_accounts_pkey | 1 | 1 pgbench_history | 53 | 1 pgbench_tellers | 1 | 1
Это запрос показывает какой процент общего буфера используют обьекты (таблицы и индексы) и на сколько процентов объекты находятся в самом кэше (буфере):
# SELECT c.relname, pg_size_pretty(count(*) * 8192) as buffered, round(100.0 * count(*) / (SELECT setting FROM pg_settings WHERE name='shared_buffers')::integer,1) AS buffers_percent, round(100.0 * count(*) * 8192 / pg_table_size(c.oid),1) AS percent_of_relation FROM pg_class c INNER JOIN pg_buffercache b ON b.relfilenode = c.relfilenode INNER JOIN pg_database d ON (b.reldatabase = d.oid AND d.datname = current_database()) GROUP BY c.oid,c.relname ORDER BY 3 DESC LIMIT 20; -[ RECORD 1 ]-------+--------------------------------- relname | pgbench_accounts buffered | 32 MB buffers_percent | 24.9 percent_of_relation | 99.9 -[ RECORD 2 ]-------+--------------------------------- relname | pgbench_history buffered | 424 kB buffers_percent | 0.3 percent_of_relation | 94.6 -[ RECORD 3 ]-------+--------------------------------- relname | pg_operator buffered | 88 kB buffers_percent | 0.1 percent_of_relation | 61.1 -[ RECORD 4 ]-------+--------------------------------- relname | pg_opclass_oid_index buffered | 16 kB buffers_percent | 0.0 percent_of_relation | 100.0 -[ RECORD 5 ]-------+--------------------------------- relname | pg_statistic_relid_att_inh_index buffered | 32 kB buffers_percent | 0.0 percent_of_relation | 100.0
Используя эти данные можно проанализировать для каких объектов не хватает памяти или какие из них потребляют основную часть общего буфера. На основе этих данных можно более правильно делать тюнинг shared_buffers параметра для PostgreSQL.
Оптимизация БД и приложения
Для быстрой работы каждого запроса в вашей базе в основном требуется следующее:
Отсутствие в базе мусора, мешающего добраться до актуальных данных. Можно сформулировать две подзадачи:
Грамотное проектирование базы. Освещение этого вопроса выходит далеко за рамки этой книги;
Сборка мусора, возникающего при работе СУБД;
Наличие быстрых путей доступа к данным индексов;
Возможность использования оптимизатором этих быстрых путей;
Обход известных проблем.
Поддержание базы в порядке
В данном разделе описаны действия, которые должны периодически выполняться для каждой базы. От разработчика требуется только настроить их автоматическое выполнение (при помощи cron) и опытным путём подобрать оптимальную частоту.
Команда ANALYZE
Служит для обновления информации о распределении данных в таблице. Эта информация используется оптимизатором для выбора наиболее быстрого плана выполнения запроса.
Обычно команда используется в связке с VACUUM ANALYZE. Если в базе есть таблицы, данные в которых не изменяются и не удаляются, а лишь добавляются, то для таких таблиц можно использовать отдельную команду ANALYZE. Также стоит использовать эту команду для отдельной таблицы после добавления в неё большого количества записей.
Команда REINDEX
Команда REINDEX используется для перестройки существующих индексов. Использовать её имеет смысл в случае:
порчи индекса;
постоянного увеличения его размера.
Второй случай требует пояснений. Индекс, как и таблица, содержит блоки со старыми версиями записей. PostgreSQL не всегда может заново использовать эти блоки, и поэтому файл с индексом постепенно увеличивается в размерах. Если данные в таблице часто меняются, то расти он может весьма быстро.
Если вы заметили подобное поведение какого-то индекса, то стоит настроить для него периодическое выполнение команды REINDEX. Учтите: команда REINDEX, как и VACUUM FULL, полностью блокирует таблицу, поэтому выполнять её надо тогда, когда загрузка сервера минимальна.
Использование индексов
Опыт показывает, что наиболее значительные проблемы с производительностью вызываются отсутствием нужных