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 из инструментария компании «Дейта Игрет».

14 марта   постгрес   программирование

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

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

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

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

Миграцию с одной версии на другую делали полным дампом — идея была под шумок избавиться ещё и от распухания (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)

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

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».

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

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

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

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

2017   постгрес   программирование

Запрос на pg_field_type

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

select oid,typname from pg_type

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

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

2017   постгрес   программирование

Особенности смены типа в 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;

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

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;

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

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

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