7 заметок с тегом

постгрес

Сдутие таблиц без блокировки

Братишка прислал ссылку на интересную статью — «Reducing bloat without locking». Речь идёт о так называемом «распухании» (bloating) таблиц в «Постгресе».

Подробно суть в трёх предложениях не объяснить, если вкратце, то механизм изоляции в транзакциях у «Постгреса» устроен так, что при обновлении и удалении строк в таблицах могут образовываться «дыры», которые увеличивают общий объём таблицы. Если происходят вставки или обновления и новые данные помещаются в неиспользуемое пространство, то «дыры» могут со временем заполняться. Но иногда «дыр» остаётся много и это проблема с которой надо что-то делать.

Вот например создадим таблицу из тысячи строк, посмотрим сколько страниц она занимает, потом удалим половину строк и снова посмотрим количество занятых страниц:

test=> CREATE TABLE test AS SELECT generate_series(1, 1000) id;
SELECT 1000
test=> SELECT MAX(ctid) FROM test;
  max
--------
 (4,96)
(1 row)
test=> DELETE FROM test WHERE id & 1 = 1;
DELETE 500
test=> SELECT MAX(ctid) FROM test;
  max
--------
 (4,96)
(1 row)

ctid — это специальный внутренний столбец «Постгреса», который отображает физическое расположение строки. Для каждой строки в нём записано два числа — номер страницы, где располагается строка и порядковый номер строки в странице.

Как видим, таблица «распухла» — половина строк удалена, а количество занимаемых страниц не изменилось. Что же можно сделать? Давайте попробуем команду VACUUM:

test=> VACUUM VERBOSE test;
INFO:  vacuuming "public.test"
INFO:  "test": found 0 removable, 500 nonremovable row versions in 5 out of 5 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 500 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

Увы, ничего не изменилось — она умеет урезать страницы только если они находятся в конце и полностью свободны.

Что же делать? Можно выполнить VACUUM FULL — эта команда полностью перельёт таблицу на новое место, избавив нас от «дыр», но во время работы таблица будет эксклюзивно заблокирована, для больших таблиц, да ещё в продуктиве это непозволительно.

Другой способ — перенести строки с последних страниц в «дыры» на первых, освободив таким образом несколько страниц в конце. Попробуем:

test=> BEGIN;
BEGIN
test=> UPDATE test SET id=id WHERE ctid>='(3,0)';
UPDATE 161
test=> UPDATE test SET id=id WHERE ctid>='(3,0)';
UPDATE 161
test=> UPDATE test SET id=id WHERE ctid>='(3,0)';
UPDATE 48
test=> UPDATE test SET id=id WHERE ctid>='(3,0)';
UPDATE 48
test=> UPDATE test SET id=id WHERE ctid>='(3,0)';
UPDATE 34
test=> UPDATE test SET id=id WHERE ctid>='(3,0)';
UPDATE 0
test=> COMMIT;
COMMIT

В одной транзакции мы обновили все строки, которые располагаются на первой странице и дальше. Попробуем теперь сделать VACUUM:

test=> VACUUM VERBOSE test;
INFO:  vacuuming "public.test"
INFO:  "test": removed 452 row versions in 2 pages
INFO:  "test": found 452 removable, 500 nonremovable row versions in 5 out of 5 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 469 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "test": truncated 5 to 3 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

Как видимо команда удалила две неиспользуемые страницы и уменьшила общее количество страниц с пяти до трёх.

Идея, описанная в статье, лежит в основе скрипта pgcompacttable из инструментария компании «Дейта Игрет».

 4 комментария    67   2018   постгрес   программирование

Проблема при апгрейде Постгреса

Несколько месяцев назад, после прогона на тестовом стенде очередного мажорного обновления Постгреса на одном из проектов, нагрузочные испытания дали плохую картину.

Я в обновлении не участвовал, но наблюдал вполглаза. Заинтересовался что дало такой результат — что-то перекрутили в Посгресе или какие-то наши поздние коммиты так ухудшили ситуацию?

Посмотрел какие запросы вылезли наверх в «Барсуке», глазам не поверил — запросы довольно простые, с чего бы им тормозить, посмотрел планы и причину нашёл довольно быстро. Но обо всём по порядку.

Миграцию с одной версии на другую делали полным дампом — идея была под шумок избавиться ещё и от распухания (bloat) таблиц. Не в первый раз такое делали, да и способ довольно обычный.

Но оказалось, что вместе с обновлением админы кое-что потюнили, подкрутили кое-какие настройки для улучшения производительности, в частности уменьшили temp_file_limit.

А когда восстанавливались из дампа, вот что пошло не так:

sed=> CREATE INDEX du_uid_did ON document_user USING btree (user_id, document_id);
ERROR: temporary file size exceeds temp_file_limit (4194304kB)

Так как проблем никто не ожидал, в лог наливки данных не заглянули, а там процесс прервался на попытке создания одного из индексов. Как результат — не создались этот индекс и все идущие за ним (ещё две штуки), сканирование по индексам в некоторых запросах сменилось на последовательный скан по таблице и всё стало плохо.

 2 комментария    18   2018   постгрес   программирование

PostgreSQL и PHP — слон слону не товарищ

Продолжаю серию удивительных открытий в мире перехода на «Постгрес». В документации к функции pg_execute есть малозаметное примечание к последнему параметру — в нём передаются значения для запроса:

Warning Elements are converted to strings by calling this function.

Думаю мало кто обращает на него внимания, собственно, я тоже не обращал. Прежде чем двинуться дальше, разберёмся — что же здесь написано?

Перевод такой: все значения, которые передаются, приводятся к строкам. Код, который это выполняется выглядит так (взял из ПХП 7.2):

if (num_params > 0) {
        int i = 0;
        params = (char **)safe_emalloc(sizeof(char *), num_params, 0);

        ZEND_HASH_FOREACH_VAL(Z_ARRVAL_P(pv_param_arr), tmp) {
                ZVAL_DEREF(tmp);
                if (Z_TYPE_P(tmp) == IS_NULL) {
                        params[i] = NULL;
                } else {
                        zval tmp_val;

                        ZVAL_COPY(&tmp_val, tmp);
                        convert_to_cstring(&tmp_val);
                        params[i] = estrndup(Z_STRVAL(tmp_val), Z_STRLEN(tmp_val));
                        zval_ptr_dtor(&tmp_val);
                }
                i++;
        } ZEND_HASH_FOREACH_END();
}

pgsql_result = PQexecParams(pgsql, query, num_params,
                                NULL, (const char * const *)params, NULL, NULL, 0);

Вышеупомянутое примечание есть только у этой функции, но на деле в любом месте, где привязываются значения, всё выглядит примерно так же (это касается и модуля ПДО).

Думаю, это связано с типизацией «Постгреса». Взять к примеру числа — два числовых типа ПХП нельзя адекватно преобразовать в россыпь типов «Постгреса», а если привести к неверному типу будут проблемы — в этой СУБД есть понятие перерузки функций, то есть функция выбирается не только по имени, но и по числу и типам параметров.

Поэтому и выбраны строки — они приведутся к нужному числовому типу сами собой, со строками это работает. К сожалению в этом преобразовании кроются и проблемы.

Ещё когда мы работали только с «Ораклом», заметили, что если вместо чисел привязывать строки, то иногда планы выполнения запросов меняются в худшую сторону. Лёгкость обращения с типами в ПХП иногда к этому приводит — переменная, используемая для хранения числа, имеет строковый тип.

К счастью, в оракловом модуле это легко решается — при привязке надо всего лишь указать, что тут мы имеем ввиду число, сами собой совершатся нужные преобразования и «Оракл» так устроен, что никаких проблем это не породит.

В «Постгресе» проблемы будут, я их описал выше, так ещё и способа хорошего нет — все функции любого модуля для работы с этой базой, доступные в ПХП любой версии, преобразуют все числа в строки. К несчастью, в «Постгресе» проблема изменения планов тоже имеет место — недавно наткнулись на запрос, который через ПХП выполняется почти полторы секунды, а через консольный клиент — меньше миллисекунды.

Мой братишка придумал оригинальное решение — определять позиции на которых мы привязываем числа и автоматически внутри нашего фреймворка в этом месте запроса указывать тип bigint явным образом. То есть добавлять после плейсхолдера параметра конструкцию «::bigint».

Пришлось изменить несколько наших хранимых процедур, но в целом всё плошло довольно гладко.

 1 комментарий    22   2017   php   постгрес   программирование

Ускорение пользовательских функций в PostgreSQL

Мы ещё в процессе перевода последних наших проектов с «Оракла» на «Постгрес», поэтому всё ещё не закончили избавляться от характерных для диалекта «Оракла» функций, тащим их с собой — пока так проще.

В попытке как-то их ускорить, наткнулся на странность. Возможно это где-то описано, я пока не нашёл это место в руководстве.

Ниже две функции на разных языках, поддерживаемых «Постгресом», по смыслу идентичные — обе представляют собой замену функции NVL2 «Оракла» над целыми числами:

CREATE FUNCTION nvl2_plpgsql(a numeric, b numeric, c numeric) RETURNS numeric AS '
BEGIN
  IF a IS NULL
    THEN RETURN c;
    ELSE RETURN b;
  END IF;
END;
' LANGUAGE plpgsql IMMUTABLE;

CREATE FUNCTION nvl2_sql(a numeric, b numeric, c numeric) RETURNS numeric AS '
SELECT CASE WHEN a IS NULL THEN c ELSE b END;
' LANGUAGE SQL IMMUTABLE;

Будет ли какая-то разница в производительности? Посмотрим на тесты:

 p95=# EXPLAIN ANALYZE SELECT nvl2_plpgsql(g, 0, 0) FROM generate_series(1, 10000000) _(g);

 Planning time: 0.066 ms  Execution time: 38881.927 ms

p95=# EXPLAIN ANALYZE SELECT nvl2_sql(g, 0, 0) FROM generate_series(1, 10000000) _(g);

 Planning time: 0.253 ms  Execution time: 15435.231 ms

Как ни странно, функция на чистом ЭсКуЭле быстрее. Судя по моим экспериментам, так всегда. Иногда результаты различаются вдвое, иногда на порядок — зависит от того, что она делает, но как правило быстрее, обратных примеров я ещё не встречал.

Это неплохое подспорье, так как у нас такие функции встречаются не только в самих запросах, но и в функциональных индексах, значит ускорятся не только чтения, но и вставки.

Запрос на pg_field_type

Страшно удивился, когда увидел в логах «Постгреса» запрос, которого не было в коде нашего продукта:

select oid,typname from pg_type

Как-то сразу догадался, что он порождается вызовом pg_field_type, который у нас используется, чтобы прозрачно для верхнего уровня обрабатывать тип bytea. Заменил вызов на pg_field_type_oid и запрос пропал.

Проблемы, конечно, особой в этом нет — запрос плёвый, тем более он выполняется только при первом вызове pg_field_type, но как-то неприятно, когда на каждый запуск прилетает полторы тысячи с гаком строк, которые я не заказывал, люблю контролировать такие вещи.

Особенности смены типа в PostgreSQL: text → bytea

При обыденной смене типа колонки в «Постгресе» с текстового типа на бинарный получил ошибку «ERROR: invalid input syntax for type bytea». Запрос довольно рядовой и сюрпризов я не ожидал:

ALTER TABLE tbl ALTER COLUMN colmn TYPE bytea USING colmn::bytea;

Решения нагуглить не удалось, поэтому начал исследовать проблему сам. Бинарным поиском нашёл строку на которой запрос запнулся, потом так же вычислил символ. Им оказался обратный слэш. Заэкранировал его и ошибка пропала:

ALTER TABLE tbl ALTER COLUMN colmn TYPE bytea USING REPLACE(colmn, '\', '\\')::bytea;

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

 3 комментария    89   2017   postgres   постгрес   программирование

✨ Двойные миграции (Оракл+Постгрес)

У нас идёт замена в наших продуктах сильно платного «Оракла» (это база данных) на бесплатный «Постргес». Несмотря на ударные темпы, продолжаться это ещё будет долго. Продуктов много, несмотря на то, что начали мы ещё в прошлом году, весь этот год будем жить сразу на двух базах.

В этой связи, когда есть различия, приходится миграции писать в двух комплектах — под каждый диалект. Я придумал как писать их так, чтобы каждая БД видела куски кода, предназначенные только ей.

Например:

CREATE UNIQUE INDEX dn_part_num_org_n_cat ON document_n(/*/**/
CASE WHEN d_deleted = 0 AND num IS NULL AND n=0 AND category=0 THEN id END,
CASE WHEN d_deleted = 0 AND num IS NULL AND n=0 AND category=0 THEN org_id END);
--*/ id, org_id) WHERE d_deleted=0 AND num IS NULL AND n=0 AND category = 0;

Часть с /*/**/ и до —*/ видит только «Оракл» — для него это выглядит так: комментарий открывается, сразу закрывается, идёт код, который он и воспринимает, а последняя строка закоментирована при помощи двух минусов — это стандартный коментарий в эскуэле.

«Постгрес» эту часть не видит — он поддерживает вложенные коментарии, поэтому его интерпретация другая: открываются два комментария, первый закрывается сразу, а второй — на последней строке, остаток которой «Постгрес» воспринимает как часть кода.

 30 комментариев    27   2016   oracle   postgres   постгрес   программирование