Перенимаем NoSQL

Перевод Learning from NoSQL с Bruce Momjian: Postgres Blog

Я думаю, что первый урок, который PostgreSQL сообщество может вынести из NoSQL, в том, что не всем нужны все наши возможности, и, если это не будет сложно, нам необходимо дать пользователям возможность отказаться от некоторых из них, в пользу желаемых NoSQL преимуществ. В некоторых случаях мы уже можем это сделать:

- Вы можете улучшить скорость за счёт откладывания или отказа от надежности (synchronous_commit, fsync)
- Вы можете убрать затраты на целостность не устанавливая ограничения (constraints)
- Можете использовать подготовленные (prepared) запросы для устранения затрат на парсера и оптимизатора
- Массивы часто могут быть использованы для ухода от затрат на join
- Можно хранить не структурированные данные в hstore
- Устаревшие данные можно обслуживать с помощью асинхронной мульти-мастер репликации (Bucardo)

Однако есть некоторые вещи, которые будет сложно осуществить:

- Доступ к данным без SQL
- Снижение затрат за счёт отказа от атомарности и изоляции

Я думаю о новых опциональных возможностях, которые мы могли бы предоставлять потенциальным NoSQL пользователям, но тут надо всё хорошо продумывать.

Дополнение: Один из пунктов в нашем TODO-листе, который может помочь потенциальным NoSQL пользователям, это реализация встроенного типа данных JSON. JSON используется в качестве формата хранилища во многих NoSQL базах.

Выполнение произвольных запросов с помощью PL/Proxy

Опишу некоторые мысли по поводу организации шардинга. Это ни в коем случае не претендует на общее правило, но в некоторых ситуациях может оказаться полезным. Перед прочтением необходимо ознакомиться с концепцией PL/Proxy:

https://developer.skype.com/SkypeGarage/DbProjects/PlProxy

Допустим необходимо сделать шардинг на основе PL/Proxy. Также есть приложение, типа ORM генерирующее SQL-запросы, которое тяжело переделать на работу с БД через хранимые функции, что является необходимым для PL/Proxy. Это приложение позволяет с определёнными усилиями в обозримые сроки переработать места инициации запросов, указав как дополнительный параметр критерии шардинга, а структура БД допускает существование таких критериев для каждой выборки.

Read more »

Не оптимальная запись

Если необходимо локализовать проблемы связанные с не оптимальной записью, а это очень важные проблемы, т.к. предел возможностей БД в основном упирается в дисковые операции, и начать решать их с более тяжёлой, то этот запрос поможет вам. Он выводит статистику по таблицам в обратном порядке по сумме операций записи. Сверху будут таблицы с наиболее интенсивной записью. В условиях осуществляется фильтрация по схемам, чьи таблицы необходимо исключить из статистики.

Т.о. выявляются счётчики, лишние или временные вставки и т.п. Далее, интенсивность записи счётчиков можно снизить за счёт, например, накопления их в памяти (допустим в memcached) по 10 штук и одного сброса на диск. Где-то можно просто отказаться от некоторых операций, а где-то выявится необходимость рефакторинга приложения.

SELECT
schemaname AS sch, -- схема
relname AS tab, -- таблица
pg_size_pretty(pg_relation_size(relid)) AS tsize, -- размер
n_tup_upd + n_tup_ins + n_tup_del AS wr, -- операций записи
seq_scan + idx_scan AS re, -- всего чтений
n_tup_ins AS i, n_tup_upd AS u, n_tup_del AS d -- I/U/D
FROM
pg_stat_user_tables
WHERE schemaname NOT IN ('pgq')
ORDER BY
n_tup_upd + n_tup_ins + n_tup_del DESC
LIMIT 40;

Не оптимальное чтение

Запрос предназначен для определения таблиц по которым производятся выборки использующие последовательные сканирования. Таблицы в списке сортируются реверсивно по порядку (числу разрядов) величины последовательных чтений и размеру таблицы. Т.о. для каждого порядка сверху будет самая тяжелая таблица, запросы к которой более приоритетны к оптимизации.

Также удобно бывает использовать ограничение по размеру (в примере ниже оно закомментировано). Оно помогает отфильтровывать таблицы, по которым последовательные чтения потенциально выгодны. Тут необходимо смотреть на величину, и вообще на факт, необходимости использования по ситуации, проведя несколько экспериментов.

SELECT
schemaname AS sch, -- схема
relname AS tab, -- таблица
pg_size_pretty(pg_relation_size(relid)) AS tsize, -- размер
seq_scan AS ss, -- последовательных чтений
idx_scan AS is, -- индексных чтений
seq_scan + idx_scan AS re, -- всего чтений
n_tup_upd + n_tup_ins + n_tup_del AS wr, -- операций записи
n_tup_ins AS i, n_tup_upd AS u, n_tup_del AS d -- I/U/D
FROM
pg_stat_user_tables
-- WHERE pg_relation_size(relid) > 1 * 1024 * 1024 -- ограничение по размеру
ORDER BY
length(seq_scan::text) DESC,
pg_relation_size(relid) DESC
LIMIT 40;

Не используемые индексы

Данный запрос предназначен для выявления индексов претендентов на удаление. В начале отображаются наиболее редко используемые большие индексы.

Запрос действует только по пользовательским индексам и не принимает во внимание уникальные, т.к. они используются как ограничения (как часть логики хранения данных). Тем не менее прежде чем принять решение об удалении хорошо подумайте.

SELECT
idstat.schemaname AS sch, -- схема
idstat.relname AS tab, -- таблица
indexrelname AS idx, -- индекс
idstat.idx_scan AS iis, -- число сканирований по этому индексу
pg_size_pretty(pg_relation_size(indexrelid)) AS isize, -- размер индекса
tabstat.idx_scan AS tis, -- индексных чтений по таблице
tabstat.seq_scan AS tss, -- последовательных чтений по таблице
tabstat.seq_scan + tabstat.idx_scan AS tre, -- чтений по таблице
n_tup_upd + n_tup_ins + n_tup_del AS twr, -- операций записи
pg_size_pretty(pg_relation_size(idstat.relid)) AS tsize -- размер таблицы
FROM
pg_stat_user_indexes AS idstat
JOIN pg_indexes ON
indexrelname = indexname AND
idstat.schemaname = pg_indexes.schemaname
JOIN pg_stat_user_tables AS tabstat ON
idstat.relid = tabstat.relid
WHERE
indexdef !~* 'unique'
ORDER BY
idstat.idx_scan,
pg_relation_size(indexrelid) DESC
LIMIT 20;

plpgsql и временные таблицы

Перевод plpgsql and temp. tables с Pavel Stehule's blog

Я провёл тестирование скорости трёх возможных стилей работы с временными таблицами в хранимых процедурах. Обычно я предпочитаю использовать проверку существования таблицы с помощью перехвата сообщения об ошибке. Я не ожидал этого, но перехват ошибок показал себя лучше всех.

create or replace function test1() 
returns void as $$
begin 
  drop table if exists omega; 
  create temp table omega(a integer); 
  insert into omega values(10); 
  if exists(select * from omega) then end if; 
end;
$$ language plpgsql;

create or replace function test2() 
returns void as $$
begin 
  if exists(select * from pg_class where relname='omega' and pg_table_is_visible(oid)) then 
    delete from omega; 
  else 
    create temp table omega(a integer); 
  end if; 
  insert into omega values(10); 
  if exists(select * from omega) then end if; 
end;
$$ language plpgsql;

create or replace function test3() 
returns void as $$
begin 
  begin 
    delete from omega; 
  exception 
    when others then 
      create temp table omega(a integer); 
  end; 
  insert into omega values(10); 
  if exists(select * from omega) then end if; 
end;
$$ language plpgsql;

тест с помощью pgbench

Test1:
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 10
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
tps = 339.780441 (including connections establishing)
tps = 340.172513 (excluding connections establishing)

Test2:
scaling factor: 1
query mode: simple
number of clients: 10
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
tps = 1891.021562 (including connections establishing)
tps = 1907.533096 (excluding connections establishing)

Test3:
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 10
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
tps = 2664.756569 (including connections establishing)
tps = 2698.289177 (excluding connections establishing