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

    Только зарегистрированные пользователи могут участвовать в опросе. Войдите, пожалуйста.

    Оцените полезность статьи

    Поделиться публикацией

    Похожие публикации

    Комментарии 19

      +7
      На PGDay 15 ребята демонстрировали OKmeter довольно интересная вещь для мониторинга PostgreSQL, за 25$ в месяц с сервера.

      Для анализа можно еще взять pgBadger, но тут нужно смотреть на объем логов. На том же PGDay упоминали, что 200 Gb логов в несколько потоков переваривает за 6 часов.

      Можно еще pgCluu, он работает через разные view и расширения PostgreSQL, а не через лог.
        0
        От одного из участников PGDays слышал, что видеозаписи конференции выложат, особенно интересно про системную часть — барьеры, noatime и тп, очень жду, а за ссылку на pgCluu спасибо, сейчас все эти метрики руками собираем из системы или из системных табличек =(
          0
          Про системную часть можете глянуть презентацию www.slideshare.net/alexeylesovsky/linux-tuning-for-postgresql-at-secon-2015
          Там тезисно, но общие направления куда копать вполне понятны.
            0
            Видео конфы наконец-то появилось pgday.ru/ru/news/52. В итоге заюзали OKmeter, графики и язык для форматирования графиков у них шикарные
          0
          Еще можно взять pg_stat_statements — тоже без файловых логов (расширение) и просто взять информацию через SQL.
            0
            Спасибо за перевод книги.
            В зависимости от версии PostgreSQL запрос может обрезаться..
            По логам мы получаем отдельное время выполнения каждого запроса, по pg_stat_statements только среднее. PostgreSQL еще не все запросы хорошо нормирует, и люди пытаются при помощи регекспов это подправить.
              0
              > Спасибо за перевод книги.
              Да не за что, но я ничего не переводил.

              > В зависимости от версии PostgreSQL запрос может обрезаться…
              Есть такие проблемы. До версии 9.2 он не умел нормализировать запросы.
            +2
            OKmeter хорош да, постгресовый плагин там очень годный за счет агрегации данных с pg_stat_statements — очень хорошо видно запросы которые вдруг начинают выпирать (cpu/disk usage, row returned).
            image
            0
            gdb — неожиданное решение. Запомню на будущее.
            +2
            Если кому-то интересно то же самое для MySQL:

            1) Добавление столбца в существующую таблицу:

            существует утилита от Percona под названием pt-online-schema-change, которая позволяет в неблокирующем режиме альтерить таблицы — www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html

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

            2) мониторинг запросов к базе

            «show full processlist» или «select * form information_schema.PROCESSLIST», если нужна какая-то особенная информация

            3) slow_log точно также есть в MySQL из коробки — dev.mysql.com/doc/refman/5.0/en/slow-query-log.html

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

            точно также — в strace будут видны запросы, которые исполняются на сервере

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

            ALTER IGNORE для удаления дубликатов из таблицы при создании уникального индекса
            INSERT IGNORE / INSERT ON DUPLICATE KEY UPDATE / REPLACE для вставки с игнорированием вставки в случае дубля / обновлением строки / заменой строки целиком соответственно
              +3
              Задача: сделать составной уникальный индекс, одно из полей может быть null
              Проблема: уникальный индекс не сравнивает null поля
              Решение: разбить индекс на 2 разных индекса.

              Так же можно сделать индекс по выражению:

              evgenykokovikhin=# create unique index test_unique_idx on test(a, coalesce(b, ''));
              CREATE INDEX
              evgenykokovikhin=# insert into test (a,b) values ('foo', null);
              INSERT 0 1
              evgenykokovikhin=# insert into test (a,b) values ('foo', null);
              ERROR:  duplicate key value violates unique constraint "test_unique_idx"
              DETAIL:  Key (a, (COALESCE(b, ''::character varying)))=(foo, ) already exists.
              
                0
                зачетно, в таком случае еще лучше можно одним индексом обойтись
                  0
                  Это от целей зависит. Уникальный индекс — это же в первую очередь индекс. Поэтому примеры не равнозначные.
                  Например, в вашем случае запрос типа
                  select * from test where b isnull order by a limit 42;

                  будет выполнятья по индексу. В моем — не будет.
                +3
                Мониторинг текущих запросов к базе
                Хочу отметить сущестование параметра track_activity_query_size, который увеличивает размер, отводимый под хранение выполняющегося запроса
                выдержка из документации
                Specifies the number of bytes reserved to track the currently executing command for each active session, for the pg_stat_activity.query field. The default value is 1024. This parameter can only be set at server start.

                NULL и уникальные индексы
                Также, если количество столбцов превышает максимально допустимое для индекса (по умолчанию, 32), уникальный индекс можно построить по хешу результата их конкатенации:
                CREATE UNIQUE INDEX idx_name_unique ON foobar USING btree ( md5 ( field_one::text || field_two::text ) );

                Обработка дубликатов
                Если необходимо избавится от уже существующих дубликатов, может помочь такой запрос (id — PK, field_one, field_two — столбцы, по которым необходимо построить уникальный индекс):
                DELETE FROM foobar
                WHERE id IN
                (
                	SELECT unnest ( ( array_agg ( id ) ) [2:9999999] )
                	FROM foobar
                	GROUP BY field_one, field_two
                    HAVING count ( * ) > 1
                );

                  0
                  важно помнить что приём с конкатенацией полей в строку работает только до момента пока вам не встретится null :)
                    0
                    В приведенном выше комментарии указан один из вариантов работы с NULL-значениям.
                  0
                  Случайно нашел эту статью поисковиком.

                  Насчет решения с gdb:

                  Можно просто сделать pg_cancel_backend, а потом посмотреть в логах что за STATE мы убили (будет сразу после сообщения о том, что произошел cancel).

                  Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                  Самое читаемое