Разделы:

• Главная
• Блог
• Контакты

Вещи про pgsql, о которых не любят рассказывать

Начну издалека. Какие три самые используемые базы в никсах? Очевидно sqlite, mysql и pgsql.

У первых двух - совершенно разные ниши, sqlite предназначена для встраивания, несложных баз типа "конфиг", "логи", "{black,white}-листы" и прочее, где хотелось бы иметь стандартизированный механизм запросов и, возможно, чуть статистики уровня GROUP BY с условием.

mysql - уже более "серьёзное" применение, комфортная работа возможна с базами до десятков гигабайт. Сфера применения самая разная - аналитика, accounting, тупо-хранилище записей и т.д. База простая и достаточно неприхотливая в обслуживании. Однако, по мере роста базы и/или сложности запросов вы в какой-то момент обязательно вылезут её недоработки: бэкап1, дубовость оптимизатора запросов2, репликация3, партиционирование4, дилемма выбора движка5 и т.д. Всё это хорошо расписано здесь6 и я не вижу смысла повторяться.

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

Хрупкость базы

Суть проблемы происходит из выбранной архитектуры pgsql -- версионник (mysql - блокировочник). Это означает 2 вещи:

Первая - в базе присутствует какое-то количество "старых" версий данных. Размер их может быть произвольным, в зависимости от типа нагрузки. Общий размер занимаемого базой места, соответственно, тоже. Будьте готовы к тому, что в некоторых случаях данных у вас всего на гигабайт, а размер базы, внезапно -- 5-6.

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

В mysql с этим намного проще - два наиболее используемых движка myisam и innodb. Таблица в mysql - это просто два файлика: MYD -- данные, MYI -- индексы. В InnoDB -- сложнее, по дефолту оно всё пытается лепить в один файл7, но можно включить опцию innodb_file_per_table и у нас опять станет по 2 файла на таблицу: frm -- определение таблицы, ibd -- данные+индексы. Данные оттуда можно вытащить, хоть и придётся немного повозиться. Для файлового бэкапа вполне годится.

А вот в pgsql "не всё так однозначно!"©. Файлы-то забэкапить можно, однако определить "что где лежит" - задача весьма нетривиальная. И даже если найти основной массив данных, далеко не факт, что там будут последние данные.

Более того, потеря одного-единственного файла лога из pg_clog -- рушит всю базу. И нет не то что гарантированного, но даже рекомендованного способа её починить. В рассылках рекомендуют забить файл нулями, выставить zero_damaged_pages = true и молиться.

fsync, который периодически ломают

Вот на это наткнулся ещё в самом начале использования. Занятно, правда? Этакая база шредингера, вы не можете быть уверены, что она запустится, пока не попробуете. :-P Аж целую страничку по этому поводу завели, но shit, как говорится, уже happens.

А почему "периодически"? Ну, примеров вспомнить недолго, вот например. Или вот. На случай, если ссылка протухнет, перечислю блок "похожие темы" оттуда:

• turning fsync off for WAL
• fsync=off & shutdown => crash?
• Postgresql INSERT speed (how to improve performance)?
• Dangers of fsync = off
• Running with fsync=off
• Fsync on/off For Various Filesystems/Platforms

Я не говорю, что в этом месте у mysql проблем вообще нет, однако здесь у pgsql, похоже, больное и критически важное место.

Распухание базы

Перекликается с предыдущим пунктом. Старые версии данных, которые ещё кому-то нужны или которые не успевает почистить vacuum(). Поясняю на примере заббикса, как широко распространённый use-case. Запись в базу там идёт постоянно и практически с одинаковой скоростью. autovacuum же запускается периодически, по достижению какого-то предела обновлённых/удалённых строк. В период когда тот не работает - база пухнет, ей же надо где-то хранить новые и обновлённые версии строк. Место же реально освобождает только vacuum full, который лочит всю базу.

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

Репликация

Штатно - в комплекте есть только row-based, она прмолинейная, надёжная, но в некоторых конфигурациях может вызывать проблемы. Физическая репликация подразумевает передачу всего объёма изменённых данных, который может достигать больших объёмов. Вам может не хватить ширины канала, при географически разнесённых репликах может играть роль задержка ответа, за счёт чего slave может просто не успевать реплицировать данные, в конце концов выйти за пределы лога и отвалиться. А с логической репликацией - у нас тут целый зоопарк, оно как бы работает, но... используйте на свой страх и риск. Примерно та же ситуация, что и со сторонними "engines" в mysql.

Планировщик

...который иногда ведёт себя минимум странно

test=> EXPLAIN ANALYZE SELECT * FROM test WHERE st = 17 ORDER BY id LIMIT 15;
                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..327.28 rows=15 width=96) (actual time=2427.544..2427.695 rows=15 loops=1)
   ->  Index Scan using test_pkey on test  (cost=0.43..214759.24 rows=9856 width=96) (actual time=2427.539..2427.672 rows=15 loops=1)
         Filter: (st = 17)
         Rows Removed by Filter: 4784768
 Planning time: 0.232 ms
 Execution time: 2427.769 ms

Вся таблица - 5kk записей, индексы есть как на id (pkey), так и на st. mysql такое выполняет мгновенно, а тут - 2.5 секунды. Очевидно, дополнительный индекс как-то сгладит этот провал, но это опять место. Ну и кроме того - на все возможные случаи индексов не напасёшься. Запросы переписывать - это тоже плохой вариант, я считаю должно работать и в таком виде, поскольку он самый простой и очевидный.

Вот время с индексом по (id, st):

test=> EXPLAIN ANALYZE SELECT * FROM test WHERE st = 17 ORDER BY id LIMIT 15;
                                                                 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..159.96 rows=15 width=96) (actual time=109.948..109.971 rows=15 loops=1)
   ->  Index Scan using test_id_st on test  (cost=0.43..104811.63 rows=9855 width=96) (actual time=109.945..109.960 rows=15 loops=1)
         Index Cond: (st = 17)
 Planning time: 0.329 ms
 Execution time: 110.011 ms

test=> \di+ test_id_st
                              Список отношений
 Схема  |    Имя     |  Тип   | Владелец | Таблица | Размер | Описание
--------+------------+--------+----------+---------+--------+----------
 public | test_id_st | индекс | test     | test    | 103 MB |

А в это время в mysql:

mysql> EXPLAIN SELECT * FROM test WHERE st = 17 ORDER BY id LIMIT 15;
+----+-------------+--------+------+---------------+------------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys | key        | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+---------------+------------+---------+-------+------+-------------+
|  1 | SIMPLE      | test   | ref  | fk_test_st    | fk_test_st | 1       | const |  131 | Using where |
+----+-------------+--------+------+---------------+------------+---------+-------+------+-------------+
1 row in set (0.01 sec)

Используется 1 (один) индекс.

Дорогие коннекты

Связано с выбранной архитектурой базы - "по процессу на соединение". Собственно, недостатки такого подхода очевидны: cgi (не fast-), например, при всех его недостатках, становится ещё дороже. апач/prefork или любой вариант, который плодит много процессов для посгре противопоказан.

Прочее

Из прочего - sqlite3/mysql как-то живут с сортировкой "по-умолчанию" для id в таблице. В pgsql - явно сортировать вручную. Да, это логично, но, блжад, неудобно. select('table', '*', undef, {-asc => 'id'}).

Какая-то дикая херня с SERIAL. sqlite3/mysql - как-то обходятся без внешнего явного sequence, а здесь - хер вам. Пример:

INSERT INTO users (id, name, pass) VALUES (1, 'admin', 'abcd'); -- дефолтная учётка
INSERT INTO users     (name, pass) VALUES ('new_user', 'efgh'); -- бабах, "такой номер уже занят"

Архитектурно - правильно, на практике - неудобно.