Узнайте, как настраивать параметры PostgreSQL, оптимизировать SQL-запросы и настраивать разделяемую память для достижения максимальной производительности поисковой системы.
Тонко настраивайте параметры вашей базы данных, чтобы выжать из неё максимум производительности!
Здравствуйте,
Ниже приведены некоторые выводы из моего опыта создания поисковой системы и оптимизации базы данных PostgreSQL для достижения высокой скорости работы.
Наша конфигурация сервера PostgreSQL была следующей:
Red Hat 7.2
Система PIV 2.00 ГГц
1024 МБ ОЗУ
Одной из первых вещей, которые я заметил после запуска сервлет-программы, было то, что хотя запросы возвращались почти так же быстро, как и из предыдущей системы на базе MySQL, нагрузка на сервер оказалась значительно выше. Тогда я начал погружаться в детали. Ранее я оптимизировал MySQL, значительно увеличивая кэш и размеры буферов, а также выделяя больше оперативной памяти под эту проблему. Самое главное, что нужно сделать перед запуск PostgreSQL — это выделить достаточно места под разделяемый буфер. Но тогда, сколько — это достаточно?
На эту тему ведутся жаркие споры между теми, кто утверждает, что логически под это можно отвести всю оперативной память, и теми, кто говорит, что добавление ОЗУ сверх определенного лимита бесполезно. Чем больше у вас кэш разделяемого буфера, тем большая доля вашей базы данных не вызывает ни чтений, ни копирования памяти из буферного кэша ОС. Тем не менее, в целом вы будете кэшировать меньшее количество блоков, поскольку они будут буферизоваться дважды. Когда вы копируете блок из буфера ОС в разделяемую память, копия все еще существует в буфере ОС, а это значит, что теперь этот блок буферизуется дважды. Одиночная операция ввода-вывода на диск обходится гораздо дороже, чем сотни копирований между буферным кэшем ОС и разделяемой памятью PostgreSQL. Также учитывайте все остальные фоновые задачи, запущенные на машине, такие как cron. Всё это требует памяти; поэтому опасно не позволять операционной системе управлять значительной частью памяти.
Оказывается, эти два противоположных фактора можно отобразить на графике, и идеальной точкой будет место их пересечения.
Кроме того, я также оптимизировал SQL-запросы, адаптировав их специально под свои задачи. Основной недостаток PostgreSQL заключается в оценке запросов, содержащих «IN» и «EXISTS». Предположим:
Запрос 1. SELECT * FROM db1 WHERE ID IN ((SELECT id FROM db2 WHERE word = 'whatever')) LIMIT 20;
Запрос 2. SELECT * FROM db1 WHERE ID IN (1234, 2345, 1242, 1256, 1245, 1567, 2222, 22, 345, 234, 567, 456, 35, 56);
(где ID — первичный ключ)
Последний запрос сканируется с использованием индекса по ID, в то время как первый приводит к последовательному сканированию. Это происходит, когда база данных выполняет подзапрос для каждой строки во внешнем запросе. Вместо этого, если мы используем явные JOIN (как показано ниже), мы можем заставить базу данных использовать сканирование по индексу:
Итоговый запрос:
select * from db1, db2 a, db2 b
where id = a.id and a.word = 'word1'
and id = b.id and b.word = 'word2'
etc
ПРИМЕЧАНИЕ: Вы также можете столкнуться с последовательным сканированием вместо ожидаемого сканирования по индексу, если количество сканируемых кортежей превышает 30–40% от общего числа кортежей в таблице. Этот порог можно настроить, изменив весовые коэффициенты, назначенные random_page_cost, cpu_tuple_cost, cpu_index_cost и cpu_operator_cost, используемым оптимизатором.
Я также решил выделить больше оперативной памяти под эти цели, выделив 64 МБ ОЗУ под пространство разделяемого буфера. Файл /var/lib/pgsql/data/postgresql.conf содержит настройки сервера базы данных. PostgreSQL использует системную разделяемую память в качестве буфера. В системе Linux вы можете проверить, сколько разделяемой памяти было выделено, выполнив команду:
cat /proc/sys/kernel/shmmax
А чтобы посмотреть использование разделяемой памяти в системе:
ipcs
Результат будет в байтах. По умолчанию Red Hat 7.2 выделяет 32 МБ разделяемой памяти, чего может быть недостаточно для PostgreSQL. Я увеличил этот лимит до 64 МБ, выполнив:
echo 67108864 > /proc/sys/kernel/shmmax
Вам нужно поместить эту строку в файл автозагрузки PostgreSQL или отредактировать файл /etc/rc.d/rc.local для постоянной настройки. Затем в файле postgresql.conf я установил shared_buffers равным 8192. Я также установил sort_mem равным 16384 (16 МБ для области памяти сортировки). Поскольку использовалось пулирование соединений, я установил max_connections равным 50, а параметр fsync был установлен в значение false.
shared_buffers = 8192
sort_mem = 16384
max_connections = 50
fsync = false
Первоначальной сложностью было то, что системе приходилось создавать и закрывать соединение с PostgreSQL при каждом запросе. Это было невыносимо, поэтому я начал использовать функции пулирования соединений, предоставляемые Resin (http://caucho.com).
---
Варун
Благодарности: Курту, Брюсу, Эндрю и другим за разъяснение моих сомнений!
Оптимизация баз данных и точная настройка
Узнайте, как настраивать параметры PostgreSQL, оптимизировать SQL-запросы и настраивать разделяемую память для достижения максимальной производительности поисковой системы.
Тонко настраивайте параметры вашей базы данных, чтобы выжать из неё максимум производительности!
Здравствуйте,
Ниже приведены некоторые выводы из моего опыта создания поисковой системы и оптимизации базы данных PostgreSQL для достижения высокой скорости работы.
Наша конфигурация сервера PostgreSQL была следующей:
Red Hat 7.2
Система PIV 2.00 ГГц
1024 МБ ОЗУ
Одной из первых вещей, которые я заметил после запуска сервлет-программы, было то, что хотя запросы возвращались почти так же быстро, как и из предыдущей системы на базе MySQL, нагрузка на сервер оказалась значительно выше. Тогда я начал погружаться в детали. Ранее я оптимизировал MySQL, значительно увеличивая кэш и размеры буферов, а также выделяя больше оперативной памяти под эту проблему. Самое главное, что нужно сделать перед запуск PostgreSQL — это выделить достаточно места под разделяемый буфер. Но тогда, сколько — это достаточно?
На эту тему ведутся жаркие споры между теми, кто утверждает, что логически под это можно отвести всю оперативной память, и теми, кто говорит, что добавление ОЗУ сверх определенного лимита бесполезно. Чем больше у вас кэш разделяемого буфера, тем большая доля вашей базы данных не вызывает ни чтений, ни копирования памяти из буферного кэша ОС. Тем не менее, в целом вы будете кэшировать меньшее количество блоков, поскольку они будут буферизоваться дважды. Когда вы копируете блок из буфера ОС в разделяемую память, копия все еще существует в буфере ОС, а это значит, что теперь этот блок буферизуется дважды. Одиночная операция ввода-вывода на диск обходится гораздо дороже, чем сотни копирований между буферным кэшем ОС и разделяемой памятью PostgreSQL. Также учитывайте все остальные фоновые задачи, запущенные на машине, такие как cron. Всё это требует памяти; поэтому опасно не позволять операционной системе управлять значительной частью памяти.
Оказывается, эти два противоположных фактора можно отобразить на графике, и идеальной точкой будет место их пересечения.
Кроме того, я также оптимизировал SQL-запросы, адаптировав их специально под свои задачи. Основной недостаток PostgreSQL заключается в оценке запросов, содержащих «IN» и «EXISTS». Предположим:
Запрос 1. SELECT * FROM db1 WHERE ID IN ((SELECT id FROM db2 WHERE word = 'whatever')) LIMIT 20;
Запрос 2. SELECT * FROM db1 WHERE ID IN (1234, 2345, 1242, 1256, 1245, 1567, 2222, 22, 345, 234, 567, 456, 35, 56);
(где ID — первичный ключ)
Последний запрос сканируется с использованием индекса по ID, в то время как первый приводит к последовательному сканированию. Это происходит, когда база данных выполняет подзапрос для каждой строки во внешнем запросе. Вместо этого, если мы используем явные JOIN (как показано ниже), мы можем заставить базу данных использовать сканирование по индексу:
Итоговый запрос:
select * from db1, db2 a, db2 b
where id = a.id and a.word = 'word1'
and id = b.id and b.word = 'word2'
etc
ПРИМЕЧАНИЕ: Вы также можете столкнуться с последовательным сканированием вместо ожидаемого сканирования по индексу, если количество сканируемых кортежей превышает 30–40% от общего числа кортежей в таблице. Этот порог можно настроить, изменив весовые коэффициенты, назначенные random_page_cost, cpu_tuple_cost, cpu_index_cost и cpu_operator_cost, используемым оптимизатором.
Я также решил выделить больше оперативной памяти под эти цели, выделив 64 МБ ОЗУ под пространство разделяемого буфера. Файл /var/lib/pgsql/data/postgresql.conf содержит настройки сервера базы данных. PostgreSQL использует системную разделяемую память в качестве буфера. В системе Linux вы можете проверить, сколько разделяемой памяти было выделено, выполнив команду:
cat /proc/sys/kernel/shmmax
А чтобы посмотреть использование разделяемой памяти в системе:
ipcs
Результат будет в байтах. По умолчанию Red Hat 7.2 выделяет 32 МБ разделяемой памяти, чего может быть недостаточно для PostgreSQL. Я увеличил этот лимит до 64 МБ, выполнив:
echo 67108864 > /proc/sys/kernel/shmmax
Вам нужно поместить эту строку в файл автозагрузки PostgreSQL или отредактировать файл /etc/rc.d/rc.local для постоянной настройки. Затем в файле postgresql.conf я установил shared_buffers равным 8192. Я также установил sort_mem равным 16384 (16 МБ для области памяти сортировки). Поскольку использовалось пулирование соединений, я установил max_connections равным 50, а параметр fsync был установлен в значение false.
shared_buffers = 8192
sort_mem = 16384
max_connections = 50
fsync = false
Первоначальной сложностью было то, что системе приходилось создавать и закрывать соединение с PostgreSQL при каждом запросе. Это было невыносимо, поэтому я начал использовать функции пулирования соединений, предоставляемые Resin (http://caucho.com).
---
Варун
Благодарности: Курту, Брюсу, Эндрю и другим за разъяснение моих сомнений!
Оптимизация баз данных и точная настройка
By russiatips
Узнайте, как настраивать параметры PostgreSQL, оптимизировать SQL-запросы и настраивать разделяемую память для достижения максимальной производительности поисковой системы.