Задача
Наша цель состоит в том, чтобы сделать выполнение фиктивного запроса в базе данных PostgreSQL быстрее, используя только встроенные инструменты, доступные в базе данных.
Операционная система и версии программного обеспечения
- Операционная система: Red Hat Enterprise Linux 7.5
- Программное обеспечение: сервер PostgreSQL 9.2
Требования
Установка и запуск сервера PostgreSQL. Доступ к инструменту командной строки psql
Введение
PostgreSQL – это надежная база данных с открытым исходным кодом, доступная во многих современных хранилищах дистрибутива.
Простота использования, возможность использования расширений и стабильность, которые он обеспечивает, все добавляют к его популярности. Предоставляя базовую функциональность, например, отвечая на запросы SQL, последовательно сохраняйте вставленные данные, обрабатывайте транзакции и т. д.
Большинство зрелых решений для баз данных предоставляют инструменты и ноу-хау по настройке базы данных, определению возможных узких мест и решению проблем производительности связанный с тем, что система, работающая от данного решения, растет.
PostgreSQL не является исключением, и в этом руководстве мы будем использовать встроенный инструмент, чтобы сделать медленный запрос завершенным быстрее.
Это далека от базы данных реального мира, но можно прибегнуть к использованию встроенных инструментов. Мы будем использовать сервер PostgreSQL версии 9.2 в Red Hat Linux 7.5, но инструменты, показанные в этом руководстве, представлены в более старых версиях базы данных и операционной системы.
Проблема, которая будет решена
Рассмотрим эту простую таблицу (имена столбцов не требуют пояснений):
foobardb=# \d+ employees
Table "public.employees"
Column | Type | Modifiers | Storage | Stats target | Description
------------------+---------+-----------------------------------------------------+----------+--------------+-------------
emp_id | numeric | not null default nextval('employees_seq'::regclass) | main | |
first_name | text | not null | extended | |
last_name | text | not null | extended | |
birth_year | numeric | not null | main | |
birth_month | numeric | not null | main | |
birth_dayofmonth | numeric | not null | main | |
Indexes:
"employees_pkey" PRIMARY KEY, btree (emp_id)
Has OIDs: no
С такими записями как:
foobardb=# select * from employees limit 2; emp_id | first_name | last_name | birth_year | birth_month | birth_dayofmonth --------+------------+-----------+------------+-------------+------------------ 1 | Emily | James | 1983 | 3 | 20 2 | John | Smith | 1990 | 8 | 12
В этом примере мы являемся компанией и развернули приложение под названием HBapp, которое отправляет письмо «Happy Birthday» сотруднику в день его рождения.
Приложение запрашивает базу данных каждое утро, чтобы найти получателей в течение дня (до работы, мы не хотим убивать нашу базу данных HR из доброты).
Приложение ищет следующий запрос для поиска получателей:
foobardb=# select emp_id, first_name, last_name from employees where birth_month = 3 and birth_dayofmonth = 20; emp_id | first_name | last_name --------+------------+----------- 1 | Emily | James
Все работает нормально, пользователи получают свою почту.
Многие другие приложения используют базу данных и таблицу сотрудников внутри, например, учет и BI.
Компания растет, и поэтому растет штат сотрудников.
Со временем приложение работает слишком долго, а выполнение перекрывается с началом рабочего времени, что приводит к медленному времени отклика базы данных в критически важных приложениях.
Мы должны что-то сделать, чтобы этот запрос выполнялся быстрее, или приложение будет неработоспособным, и с ним в Nice Company будет меньше внимания.
В этом примере мы не будем использовать какие-либо дополнительные инструменты для решения проблемы, только один из них предоставляется базовой установкой. Посмотрим, как разработчик базы данных выполнит запрос с объяснением.
Мы не тестируем на производстве; мы создаем базу данных для тестирования, создаем таблицу и вставляем в нее двух сотрудников, упомянутых выше.
Мы используем те же самые значения для запроса в этом учебном пособии, поэтому при любом запуске только одна запись будет соответствовать запросу: Emily James.
Затем мы запускаем запрос с предыдущим объяснением, чтобы увидеть, как он выполняется с минимальными данными в таблице:
foobardb=# explain analyze select emp_id, first_name, last_name from employees where birth_month = 3 and birth_dayofmonth = 20; QUERY PLAN ----------------------------------------------------------------------------------------------------- Seq Scan on employees (cost=0.00..15.40 rows=1 width=96) (actual time=0.023..0.025 rows=1 loops=1) Filter: ((birth_month = 3::numeric) AND (birth_dayofmonth = 20::numeric)) Rows Removed by Filter: 1 Total runtime: 0.076 ms (4 rows)
Это очень быстро.
Возможно, так быстро, как только компания сначала развернула HBapp.
Давайте подражать состоянию текущего производства foobardb, загрузив в базу данных как можно больше (фальшивых) сотрудников в нашей базе данных (обратите внимание: нам потребуется тот же размер хранилища в тестовой базе данных, что и в производстве).
Мы просто используем bash для заполнения тестовой базы данных (если у нас есть 500 000 сотрудников в производстве):
$ for j in {1..500000} ; do echo "insert into employees (first_name, last_name, birth_year, birth_month, birth_dayofmonth) values ('user$j','Test',1900,01,01);"; done | psql -d foobardb
Сейчас у нас 500002 сотрудников:
foobardb=# select count(*) from employees; count -------- 500002 (1 row)
Давайте снова запустим запрос explain:
foobardb=# explain analyze select emp_id, first_name, last_name from employees where birth_month = 3 and birth_dayofmonth = 20; QUERY PLAN ---------------------------------------------------------------------------------------------------------- Seq Scan on employees (cost=0.00..11667.63 rows=1 width=22) (actual time=0.012..150.998 rows=1 loops=1) Filter: ((birth_month = 3::numeric) AND (birth_dayofmonth = 20::numeric)) Rows Removed by Filter: 500001 Total runtime: 151.059 ms
У нас все еще есть только одно совпадение, но запрос значительно медленнее.
Мы должны заметить первый узел планировщика: Seq Scan, который означает последовательное сканирование – база данных считывает всю таблицу, а нам нужна только одна запись, например, grep в bash.
Фактически, это может быть на самом деле медленнее, чем grep.
Если мы экспортируем таблицу в файл csv с именем /tmp/exp500k.csv:
foobardb=# copy employees to '/tmp/exp500k.csv' delimiter ',' CSV HEADER; COPY 500002
И grepнем информацию, которая нам нужна (мы ищем 20-й день третьего месяца, последние два значения в файле csv в каждой строке):
$ time grep ",3,20" /tmp/exp500k.csv 1,Emily,James,1983,3,20 real 0m0.067s user 0m0.018s sys 0m0.010s
Решение является причиной индексации
. Ни один сотрудник не может иметь более одной даты рождения, которая состоит из ровно одного birth_year, birth_month и birth_dayofmonth – поэтому эти три поля предоставляют уникальное значение для этого конкретного пользователя.
И пользователь идентифицирует его / ее emp_id (в компании с таким же именем может быть более одного сотрудника).
Если мы объявим ограничение для этих четырех полей, будет создан неявный индекс:
foobardb=# alter table employees add constraint birth_uniq unique (emp_id, birth_year, birth_month, birth_dayofmonth); NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "birth_uniq" for table "employees"
Итак, мы получили индекс для четырех полей, посмотрим, как работает наш запрос:
foobardb=# explain analyze select emp_id, first_name, last_name from employees where birth_month = 3 and birth_dayofmonth = 20; QUERY PLAN ------------------------------------------------------------------------------------------------------------ Seq Scan on employees (cost=0.00..11667.19 rows=1 width=22) (actual time=103.131..151.084 rows=1 loops=1) Filter: ((birth_month = 3::numeric) AND (birth_dayofmonth = 20::numeric)) Rows Removed by Filter: 500001 Total runtime: 151.103 ms (4 rows)
Это идентично последнему, и мы можем видеть, что план одинаков, индекс не используется.
Давайте создадим еще один индекс с помощью уникального ограничения на emp_id, birth_month и birth_dayofmonth (в конце концов, мы не запрашиваем для birth_year в HBapp):
foobardb=# alter table employees add constraint birth_uniq_m_dom unique (emp_id, birth_month, birth_dayofmonth); NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "birth_uniq_m_dom" for table "employees"
Давайте посмотрим на результат нашей настройки:
foobardb=# explain analyze select emp_id, first_name, last_name from employees where birth_month = 3 and birth_dayofmonth = 20; QUERY PLAN ----------------------------------------------------------------------------------------------------------- Seq Scan on employees (cost=0.00..11667.19 rows=1 width=22) (actual time=97.187..139.858 rows=1 loops=1) Filter: ((birth_month = 3::numeric) AND (birth_dayofmonth = 20::numeric)) Rows Removed by Filter: 500001 Total runtime: 139.879 ms (4 rows)
Ничего. Разница выше связана с использованием кешей, но план одинаков.
Пойдем дальше. Затем мы создадим еще один индекс для emp_id и birth_month:
foobardb=# alter table employees add constraint birth_uniq_m unique (emp_id, birth_month); NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "birth_uniq_m" for table "employees"
И запустите запрос еще раз:
foobardb=# explain analyze select emp_id, first_name, last_name from employees where birth_month = 3 and birth_dayofmonth = 20; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------ Index Scan using birth_uniq_m on employees (cost=0.00..11464.19 rows=1 width=22) (actual time=0.089..95.605 rows=1 loops=1) Index Cond: (birth_month = 3::numeric) Filter: (birth_dayofmonth = 20::numeric) Total runtime: 95.630 ms (4 rows)
Успех! Запрос на 40% быстрее, и мы можем видеть, что план изменился: база данных больше не сканирует всю таблицу, но использует индекс на birth_month и emp_id.
Мы создали все миксы из четырех полей, осталось только одно. Стоит попробовать:
foobardb=# alter table employees add constraint birth_uniq_dom unique (emp_id, birth_dayofmonth); NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "birth_uniq_dom" for table "employees"
Последний индекс создается на полях emp_id и birth_dayofmonth.
И результат:
foobardb=# explain analyze select emp_id, first_name, last_name from employees where birth_month = 3 and birth_dayofmonth = 20; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------- Index Scan using birth_uniq_dom on employees (cost=0.00..11464.19 rows=1 width=22) (actual time=0.025..72.394 rows=1 loops=1) Index Cond: (birth_dayofmonth = 20::numeric) Filter: (birth_month = 3::numeric) Total runtime: 72.421 ms (4 rows)
Теперь наш запрос примерно на 49% быстрее, используя последний (и только последний) индекс.
Наша таблица и связанные с ней индексы выглядят следующим образом:
foobardb=# \d+ employees
Table "public.employees"
Column | Type | Modifiers | Storage | Stats target | Description
------------------+---------+-----------------------------------------------------+----------+--------------+-------------
emp_id | numeric | not null default nextval('employees_seq'::regclass) | main | |
first_name | text | not null | extended | |
last_name | text | not null | extended | |
birth_year | numeric | not null | main | |
birth_month | numeric | not null | main | |
birth_dayofmonth | numeric | not null | main | |
Indexes:
"employees_pkey" PRIMARY KEY, btree (emp_id)
"birth_uniq" UNIQUE CONSTRAINT, btree (emp_id, birth_year, birth_month, birth_dayofmonth)
"birth_uniq_dom" UNIQUE CONSTRAINT, btree (emp_id, birth_dayofmonth)
"birth_uniq_m" UNIQUE CONSTRAINT, btree (emp_id, birth_month)
"birth_uniq_m_dom" UNIQUE CONSTRAINT, btree (emp_id, birth_month, birth_dayofmonth)
Has OIDs: no
Нам не нужны промежуточные индексы, в плане четко указано, что они не будут их использовать, поэтому мы их дропаем:
foobardb=# alter table employees drop constraint birth_uniq; ALTER TABLE foobardb=# alter table employees drop constraint birth_uniq_m; ALTER TABLE foobardb=# alter table employees drop constraint birth_uniq_m_dom; ALTER TABLE
В итоге наша таблица получает только один дополнительный индекс, который является низкой ценой для двойной скорости HBapp:
foobardb=# \d+ employees
Table "public.employees"
Column | Type | Modifiers | Storage | Stats target | Description
------------------+---------+-----------------------------------------------------+----------+--------------+-------------
emp_id | numeric | not null default nextval('employees_seq'::regclass) | main | |
first_name | text | not null | extended | |
last_name | text | not null | extended | |
birth_year | numeric | not null | main | |
birth_month | numeric | not null | main | |
birth_dayofmonth | numeric | not null | main | |
Indexes:
"employees_pkey" PRIMARY KEY, btree (emp_id)
"birth_uniq_dom" UNIQUE CONSTRAINT, btree (emp_id, birth_dayofmonth)
Has OIDs: no
И мы можем представить нашу настройку для производства, добавив индекс, который мы видели наиболее полезным:
alter table employees add constraint birth_uniq_dom unique (emp_id, birth_dayofmonth);
Вывод
Излишне говорить, что это всего лишь фиктивный пример.
Маловероятно, что вы будете хранить дату рождения вашего сотрудника в трех отдельных полях, в то время как вы можете использовать поле типа даты, что позволяет выполнять операции, связанные с датой, намного проще, чем сравнивать значения месяца и дня как целые числа. Также обратите внимание, что приведенные выше объяснения запросов не подходят для чрезмерного тестирования.
В сценарии реального мира вам необходимо проверить влияние нового объекта базы данных на любое другое приложение, использующее базу данных, а также компоненты вашей системы, которые взаимодействуют с HBapp.
Например, в этом случае, если мы сможем обрабатывать таблицу получателей в 50% от исходного времени ответа, мы можем фактически создать 200% писем на другом конце приложения (скажем, HBapp работает последовательно для все 500 дочерней компании Nice Company), что может привести к пиковой нагрузке где-то еще – возможно, почтовые серверы получат много писем «С днем рождения» для ретрансляции, прежде чем они отправят ежедневные отчеты руководству, что приведет к задержкам доставки. Также немного далека от реальности, что кто-то настраивает базу данных, создаст индексы со слепой пробкой и ошибкой – или, по крайней мере, будем надеяться, что это так происходит в компании, использующей много людей.
Обратите внимание, однако, что мы получили 50% -ное повышение производительности по запросу, только используя встроенную функцию объяснения PostgreSQL, чтобы идентифицировать один индекс, который может быть полезен в данной ситуации. Мы также показали, что любая реляционная база данных не лучше, чем четкий текстовый поиск, если мы не используем их, поскольку они предназначены для использования.
