PostgreSQL
SQL
Big Data
July 2015 29

PostgreSQL: Приемы на продакшене

Можно прочитать много книг по базам данных, написать кучу приложений на аутсорс или для себя. Но при этом невозможно не наступить на грабли, при работе с действительно большими базами/таблицами особенно, когда downtime на большом проекте хочется свести к минимуму, а еще лучше совсем избежать. Вот здесь самые простые операции, как например изменение структуры таблицы может стать более сложной задачей. Наиболее интересные случаи, проблемы, грабли и их решения из личного опыта с которыми нам на проекте Pushwoosh пришлось столкнуться описаны под катом. В статье нет красивых картинок, зато есть много сухого текста.

image


Добавление нового столбца в существующую таблицу


Вроде бы обычная операция, нужно сделать новую фичу и расширить уже существующую таблицу. Но что делать, если в таблице на продакшене 50Gb данных, и к ней каждую секунду происходит несколько тысяч запросов от живых клиентов, для клиентов это должно быть незаметно.

Задача: добавить новый столбец для существующей таблицы на продакшене.
Проблема: добавление нового столбца блокирует таблицу.
Решение: добавление нового столбца не блокирует таблицу если DEFAULT опущен или используется DEFAULT NULL.

Для тех кому, интересно можно почитать полную документацию .
Основной отрывок из документации
When a column is added with ADD COLUMN, all existing rows in the table are initialized with the column's default value (NULL if no DEFAULT clause is specified). If there is no DEFAULT clause, this is merely a metadata change and does not require any immediate update of the table's data; the added NULL values are supplied on readout, instead.

Adding a column with a DEFAULT clause or changing the type of an existing column will require the entire table and its indexes to be rewritten. As an exception when changing the type of an existing column, if the USING clause does not change the column contents and the old type is either binary coercible to the new type or an unconstrained domain over the new type, a table rewrite is not needed; but any indexes on the affected columns must still be rebuilt. Adding or removing a system oid column also requires rewriting the entire table. Table and/or index rebuilds may take a significant amount of time for a large table; and will temporarily require as much as double the disk space.


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

Добавление нового индекса в существующую таблицу


Теперь уже к нам пришел отдел аналитики, и хочет получать интересующие их данные каждый час, для этого нам нужен новый sql-запрос, но он сложный, а индекса у нас нет.

Задача: добавить индекс для существующей таблицы на продакшене
Проблема: добавление индекса блокирует запись(insert/update/delete) в таблицу. Читать из такой таблицы по-прежнему можно.
Решение: использовать CREATE INDEX CONCURRENTLY, который не блокирует таблицу, но работает примерно в два раза дольше и требует больше системных ресурсов.

Для тех, кому интересно можно почитать полную документацию.

Основной отрывок из документации
Creating an index can interfere with regular operation of a database. Normally PostgreSQL locks the table to be indexed against writes and performs the entire index build with a single scan of the table. Other transactions can still read the table, but if they try to insert, update, or delete rows in the table they will block until the index build is finished. This could have a severe effect if the system is a live production database. Very large tables can take many hours to be indexed, and even for smaller tables, an index build can lock out writers for periods that are unacceptably long for a production system.

PostgreSQL supports building indexes without locking out writes. This method is invoked by specifying the CONCURRENTLY option of CREATE INDEX. When this option is used, PostgreSQL must perform two scans of the table, and in addition it must wait for all existing transactions that could potentially use the index to terminate. Thus this method requires more total work than a standard index build and takes significantly longer to complete. However, since it allows normal operations to continue while the index is built, this method is useful for adding new indexes in a production environment. Of course, the extra CPU and I/O load imposed by the index creation might slow other operations.


Мониторинг текущих запросов к базе


Важно знать какие запросы выполняются в данный момент, какой процесс их выполняет и сколько запрос уже работает. У PostgreSQL есть отличная служебная табличка pg_stat_activity полное ее описание можно найти в документации. Приведу только наиболее интересные поля
pid integer Process ID of this backend
query_start timestamp with time zone Time when the currently active query was started, or if state is not active, when the last query was started
query text Text of this backend's most recent query. If state is active this field shows the currently executing query. In all other states, it shows the last query that was executed.

В итоге раз у нас есть время начала запроса query_start, то можно легко вычислить, сколько запрос работает и отсортировать запросы по времени выполнения.

pushwoosh=# select now() - query_start, pid, waiting, query from pg_stat_activity where state != 'idle' order by 1 desc;
    ?column?     |  pid  | waiting |  query
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 23:42:13.468115 | 6877  | f       | DELETE FROM application_goals WHERE applicationid = '9254'
 00:30:51.943691 | 24106 | f       | SELECT applicationid, pushtoken FROM application_devices WHERE hwid in ('1abd5e5fd79318cd','1abd5f3eda7acbca','1abd601f2bafabf8','1abd62a0a092ac17','1abd6303b83accf9','1abd64726a98fb63','1abd676c087c3617','1abd67ebecb6f3ce','1abd68a3b78fb730','1abd697af6bc8552','1abd70ebb654aeb2','1abd7114a8576a67','1abd729a385caff8','1abd731ff62c4521','1abd738bd2d457eb','1abd7760f7210155','1abd79dbc085c2c0','1abd7ab46dc24304','1abd7d48bd5e04ab','1abd7e7aee3c0e58','1abd7e8129a53ab3','1abd827c8c21630','1abd82cd204c69a9','1abd843ee3dedb1','1abd88d346c74d67','1abd88e8bd01c168','1abd8ceac00808cc','1abd8d3b2cb72de3','1abd8e139f267260','1abd8e74a288204c','1abd8f00bb4a0433','1abd8fd7e8f4f125','1abd91c193455ada','1abd92448396a9bf','1abd946ac4cf0e22','1abd9594ed1bd791','1abd96cc0df2202b','1abd975a98849a0b','1abd997c96d3c9b1','1abd9b3cfb66852c','1abd9bead472be5','1abd9f5bed3cbbd8','1abd9f73b8122bf1','1abda233b9a00633','1abda2ee3db5bccb','1abda486901c3a14','1abdac09e0e3267b','1abdae8235cf19dd','1abdaf9e3a143041','1abdb54fe96'
 00:04:49.592503 | 18899 | f       | autovacuum: ANALYZE public.device_tags_values
 00:00:00.040265 | 11748 | f       | INSERT INTO device_tags_values (hwid,valueid) VALUES ('27976b81cc72c7ac','8470317') RETURNING uid


Для PosgreSQL 9.1 запрос можно переписать так
select now() - query_start, procpid, waiting, current_query from pg_stat_activity where current_query != '<IDLE>' order by 1 desc;


Из данного вывода мы видим, что процесс с PID'ом 6877 уже почти сутки исполняет запрос, который скорее вероятно не оптимален и нуждается в более детальном профилировании. Так же мы видим, что второй запрос исполняется полчаса и скорее всего тоже не оптимален, но мы не видим запрос полностью, он обрезан, а нам же интересен запрос целиком.
Задача: увидеть какие запросы в данный момент исполняются в базе данных.
Проблема: pg_stat_activity показывает текущие запросы не полностью(обрезает).
Решение: gdb.

Возможно, у этой задачки есть решение проще, но мы его не нашли. Берем PID из запроса выше и подключаемся к нему
gdb [path_to_postgres] [pid]

и после того как подключились к процессу выполняем
printf "%s\n", debug_query_string


В нашем случае
bash$ gdb postgres 24106
GNU gdb (GDB) Red Hat Enterprise Linux 7.6.1-64.el7
Copyright 2013 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <gnu.org/licenses/gpl.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law. Type «show copying»
and «show warranty» for details.
This GDB was configured as «x86_64-redhat-linux-gnu».
For bug reporting instructions, please see:
<www.gnu.org/software/gdb/bugs>…
Reading symbols from /usr/bin/postgres...Reading symbols from /usr/bin/postgres...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Attaching to program: /bin/postgres, process 24106
Reading symbols from /lib64/libxml2.so.2...Reading symbols from /lib64/libxml2.so.2...(no debugging symbols found)...done.
(no debugging symbols found)...done.

(gdb) printf "%s\n", debug_query_string


Slow log


Для базы всегда нужно иметь мониторинг и видеть, какие запросы исполняются медленно. Можно решить это кодом, и в ORM или где-то глубже измерять время запроса и если оно больше пороговой величины записывать данный запрос в лог. Но всегда лучше не писать велосипед.
Задача: Мониторить медленные запросы
Проблема: Хочется сделать это на уровне базы данных
Решение: log_min_duration_statement

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

Давайте поправим конфиг PostgreSQL vim /var/lib/pgsql/9.4/data/posgresql.conf и поставим в нем 3 секунды как пороговое значение
log_min_duration_statement = 3000       # -1 is disabled, 0 logs all statements
                                        # and their durations, > 0 logs only
                                        # statements running at least this number
                                        # of milliseconds


Чтобы изменения вступили в силу не обязательно перезагружать базу, достаточно выполнить команду из psql, pgadmin или другого интерфейса к базе
SELECT pg_reload_conf();


или выполнить из командной строки
su - postgres
/usr/bin/pg_ctl reload


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

И после этого можно посмотреть в лог PostgreSQL, который у нас находится по такому пути /var/lib/pgsql/9.4/data/pg_log/postgresql-2015-07-07.log и здесь видно, что есть запрос который исполняется почти 6 секунд.
2015-07-07 09:39:30 UTC 192.168.100.82(45276) LOG:  duration: 5944.540 ms  statement: SELECT * FROM application_devices WHERE applicationid='1234' AND hwid='95ea842e368f6a64' LIMIT 1


Как вариант в дальнейшем, чтобы мониторить лог-файл можно сделать связку logstash+elasticsearch+kibana и сразу слать через zabbix уведомление о появление медленных запросов, если это является критичным для проекта.

Узнать какие запросы в данный момент делает процесс на продакшене


Если у вас много демонов, которые часто общаются с базой, и в один обычный день демон начал работать медленно или стало не понятно, что он делает, то на помощь придет strace, который покажет запросы к базе и время их выполнения, без остановки процесса, добавления логов в программу и ожидания следующего возникновения проблемы — неважно, на чем вы пишите php, python, ruby, etc. — strace подходит для всего.
Задача: узнать, что делает процесс(как пример какие запросы шлет в базу)
Проблема: процесс нельзя прерывать или останавливать.
Решение: strace

Для этого достаточно взять pid процесса указать длину и добавить опцию -T. В итоге вывод strace может быть примерно таким

strace -p 27345 -s 1024 -T 2> out
gettimeofday({1437846841, 447186}, NULL) = 0 <0.000004>
sendto(8, "Q\0\0\0005SELECT * FROM accounts WHERE uid='25143' LIMIT 1\0", 54, MSG_NOSIGNAL, NULL, 0) = 54 <0.000013>
poll([{fd=8, events=POLLIN|POLLERR}], 1, -1) = 1 ([{fd=8, revents=POLLIN}]) <0.000890>


NULL и уникальные индексы


Этот пример не имеет отношения к продакшен среде. Начнем с простого факта NULL неравен NULL.
Документация
Do not write expression = NULL because NULL is not «equal to» NULL. (The null value represents an unknown value, and it is not known whether two unknown values are equal.) This behavior conforms to the SQL standard.

Предположим, что мы хотим создать составной уникальный индекс, в котором одно из полей может иметь значение NULL, а может быть числом. При этом уникальный индекс не сработает для полей содержащий NULL, но очень хочется фильтровать такие записи индексом.
Документация
When an index is declared unique, multiple table rows with equal indexed values are not allowed. Null values are not considered equal. A multicolumn unique index will only reject cases where all indexed columns are equal in multiple rows.


Рассмотрим пример

psql=# create table test (
psql(#     a varchar NOT NULL,
psql(#     b varchar default null
psql(# );
CREATE TABLE
psql=# create unique index on test (a, b);
CREATE INDEX
psql=# insert into test values (1, null);
INSERT 0 1
psql=# insert into test values (1, null);
INSERT 0 1
psql=# select * from test;
 a | b
---+---
 1 |
 1 |
(2 rows)

Несмотря на то, что мы создали уникальный индекс, запись (1, null) вставилась дважды.

Задача: сделать составной уникальный индекс, одно из полей может быть null
Проблема: уникальный индекс не сравнивает null поля
Решение: разбить индекс на 2 разных индекса.

Для того чтобы избежать такого поведения, можно разбить индекс на 2 индекса.
sql=# create table test (
sql(#     a varchar NOT NULL,
sql(#     b varchar default null
sql(# );
CREATE TABLE
sql=# create unique index on test (a, b) where b is not null;
CREATE INDEX
sql=# create unique index on test (a) where b is null;
CREATE INDEX
sql=# insert into test values (1, null);
INSERT 0 1
sql=# insert into test values (1, null);
ERROR:  duplicate key value violates unique constraint "test_a_idx"
DETAIL:  Key (a)=(1) already exists.


Обработка дубликатов


Этот пример так же не имеет отношения к продакшен среде.
Задача: Уметь обрабатывать дубликаты, которые не позволил создать уникальный индекс.
Проблема: Нужно ловить эксепшены, парсить коды ошибок
Решение: keep calm and catch exception и ждать пока не выйдет PostgreSQL 9.5 в котором будет upsert
Оцените полезность статьи
56.6% Я узнал много нового 358
36% Я узнал немного нового 228
7.2% Я не узнал ничего нового из этой статьи 46
Voted 632 users. Passed 137 users.
+65
66k 667
Comments 19
Top of the day