Интересно кто как оптимизирует свои базы ? Например, я знаю про необходимость частых VACUUM и выгоду от создания INDEX, но у каждого свои секреты и тонкости, может поделимся опытом друг с другом ?Например, давно подметил, что вместо вложенных SELECT .... WHERE IN (SELECT ...) и JOIN лучше использовать конструкцию WHERE EXISTS (SELECT...) и ... JOIN tab USING вместо JOIN tab ON, производительность запроса на порядок выше.
Предлагаю также обсудить проблему борьбы с deadlock в PostgreSQL которая уже мной раньше поднималась в одним из форумов http://www.opennet.me/openforum/vsluhforumID3/1121.html
--------
Интересно почему в PostgreSQL (7.2.3 FreeBSD 4.7 SMP) постоянно возникают deadlock. Причем для простейших таблиц (5 int полей с int ключем, 60 записей) с достаточно интенсивным UPDATE (примерно от 5 раз в минуту до 3 раз в сек.)- dead lock выскакивают регулярно, несмотря на ежедневный VACUUM ANALYZE и еженедельный VACUUM FULL базы. Запросы простейшие, даже транзакций нет. Просто постоянные SELECT и UPDATE, гораздо реже INSERT.Раньше грешил на транзакции, dead lock вываливались на совершенно других базах, порядок следования доступа к таблицам был одинаковым.
>Скорее всего у Вас алгоритм работы программы не проработан.
Я тоже так думал, пока deadlock не стали вылазить на ровном месте и на самой простейшей таблице при апдейте без транзакции. Есть скрпипт который выполняет SELECT/UPDATE||INSERT, скрипт запускается хаотично, вполне вероятна ситуация одновременного запуска. Но по идее PostgreSQL отлично справляется с этой ситауцией, мне кажется , что из-за SMP два управляющих фронтэнда PostgreSQL (при запуске на 2-процессорной системе) не разбирают ситуацию блокировки при одновременном запросе к обоим фронтэндам.
Про порядок следования операций с таблицами в пределах транзакции я в курсе, про тонкости описанные в LOCK-мануале тоже.
--------
Ниже черновик так и не дописанной в свое время статьи, это просто заметки на полях, на полноту и полную объективность не претендует.Заметки по оптимизации PostgreSQL базы
--------------------------
1. Диагностика узких мест.Для определения узких мест очень помогает создание промежуточной библиотеки,
в которой реализован отладочный режим, при активации которого в лог файл
пишется каждый запрос и время его выполнения. Можно обойтись просто определив
время до запроса и после, а затем произвести запись разницы и тела запроса в лог.Далее, имея перед глазами текущую структуру базы и список индексов, используем
оператор "EXPLAIN [ ANALYZE ] [ VERBOSE ] запрос" для определения причины
задержек при выполнении самых медленных запросов.Например:
EXPLAIN SELECT * FROM news ORDER BY enter_date LIMIT 100;
Limit (cost=531.60..531.85 rows=100 width=572)
-> Sort (cost=531.60..536.17 rows=1827 width=572)
Sort Key: enter_date
-> Seq Scan on news (cost=0.00..164.27 rows=1827 width=572)EXPLAIN SELECT * FROM news ORDER BY enter_date;
Sort (cost=531.60..536.17 rows=1827 width=572)
Sort Key: enter_date
-> Seq Scan on news (cost=0.00..164.27 rows=1827 width=572)CREATE INDEX idx_news_date ON news (enter_date);
VACUUM ANALYZE;
EXPLAIN SELECT * FROM news ORDER BY enter_date;
Sort (cost=531.60..536.17 rows=1827 width=572)
Sort Key: enter_date
-> Seq Scan on news (cost=0.00..164.27 rows=1827 width=572)EXPLAIN SELECT * FROM news ORDER BY enter_date LIMIT 100;
Limit (cost=0.00..32.66 rows=100 width=572)
-> Index Scan using idx_news_date on news (cost=0.00..596.68 rows=1827 width=572)
и т.д.--------------------------
2. Профилактика и оптимизация часто обновляемых таблиц.Есть простое правило: чем чаще обновляется таблица, тем чаще нужно делать
"VACUUM ANALYZE" (обновление статистики для оптимизатора).
Если UPDATE преобладает над INSERT, то полезно применять "VACUUM FREEZE"
который имеет один большой недостаток, во время его работы таблица
блокируется, проведение каких-либо операций с ней невозможно (VACUUM ANALYZE
для PostgreSQL 7.2.x и старше не требует блокировки). Даже в простейшей
таблице содержащей строковые поля, элементы которой обновляются, допустим,
раз в 5 минут (подсчет трафика на интерфейсах по SNMP), за неделю размер файла
базы вырастает на несколько порядков, а с ним замедляется и скорость выполнения запросов,
особенно по непроиндексированным полям.При частом обновлении (INSERT или UPDATE) использование индексов может обернуться
во вред, ведь при обновлении SQL сервер обновляет не только данные в таблице,
но и данные в индексе.Если одновременно обновляется большое число записей, то увеличить
скорость обновления можно поместив апдейт в BEGIN WORK/COMMIT или вообще
использовать эксклюзивный лок таблицы "LOCK TABLE EXCLUSIVE".
--------------------------
3. Индексы или советы по оптимизации таблиц с высокой частотой выборки данных.При использовании индексов для их хранения требуется дополнительное дисковое
пространство, если проиндексированы текстовые поля, то это пространство может
быть сравнимо с размером самой таблицы. Так же оптимизатор при обилии индексов,
(особенно по нескольким полям или пересекающихся) может "запутаться" и оптимизировать
выполнение запроса не в лучшую сторону.- Индекс для полей используемых в "JOIN", "ORDER BY" и "GROUP BY", "MAX()" и
"MIN()" не менее важен, чем индекс для полей в "WHERE" условиях (в PostgreSQL
для "ORDER BY", MIN(), MAX() часто используется "Seq Scan" вместо индекса,
каждый случай нужно рассматривать используя EXPLAIN, иногда использование LIMIT помогает "выбрать" index вместо Seq Scan).
- Лучше не индексировать поля имеющие строковый тип, особенно поля типа text.- Индекс для LIKE и ~ (regex) полезен только когда маска не идет вначале, т.е. при
указании '%text' индекс не будет использован, а при 'text% будет (для regex
индекс используется только для масок вида '^text...').- Если в запросе используются функции LOWER/UPPER или ILIKE, то индекс будет
использован только если он был построен с учетом регистра, т.е. например
"CREATE INDEX news_ilike ON news (lower(title));"
- Не следует индексировать boolean поля или числовые поля имеющие небольшой
разброс значений (флаговые поля), в данном случае индекс больше навредит, чем
окажет пользу.- Для полей отражающих дату/время и числовых больше подходит btree индекс,
для текстовых - hash, для индексов по двум и более полям возможно использовать только tree. btree индексы лучше подходят для операций '<','>', сортировки, а hash для '=' и '<>'.
Если не уверен какой тип индекса использовать лучше использовать btree. Примеры:
CREATE UNIQUE INDEX "idx_news1" on "news" ("news_id");
CREATE INDEX "idx_news2" on "news" using btree ("news_time");
CREATE UNIQUE INDEX "idx_profile" on "profile" using hash ("login");- При создании таблицы с UNIQUE и PRIMARY KEY индексы для этих полей создаются
автоматически.- Оптимизатору сильно помогает VACUUM ANALYZE при принятии решения использовать
ли индексы;- На небольших таблицах, прямой перебор бывает быстрее использования индекса,
из-за лишних дисковых операций (на небольших таблицих оптимизатор может отказаться
от использоватния индексов автоматически).- UNIQUE индексы быстрее, чем индексы не по уникальным полям.
- Поля text в которых заведомо будет храниться большой объем данных (например, текст
статей) лучше отделить от остальных атрибутов, таких как время, автор, раздел...
Т.е. две таблицы вместо одной:
---tab1----
ключ PRIMARY KEY
заголовок
время
автор
раздел
флаг подтверждения
--------
---tab2----
ключ UNIQUE REFERENCES tab1(ключ)
текст статьи
--------
Индексы по tab1.ключ, tab2.ключ (при UNIQUE и PRIMARY KEY будут созданы автоматически),
tab1.время.- Поля помеченные как NOT NULL немного экономят место и исключают лишние проверки.
- Вместо типа text лучше использовать character varying(N).
- Чем меньше размер типа, тем лучше. Т.е. где лучше использовать int4 вместо int8,
идеально когда в таблице вообще нет строковых типов и особенно типа text.
>Ниже черновик так и не дописанной в свое время статьи, это просто
>заметки на полях, на полноту и полную объективность не претендует.
>Вах! Спасибо, очень грамотные заметки...
Кстати, кого интересует могу выложить книгу PostgreSQL - Introduction and Concepts... вот токо куда...
>- Индекс для полей используемых в "JOIN", "ORDER BY" и "GROUP BY",
>"MAX()" и "MIN()" не менее важен, чем индекс для полей в "WHERE" условиях
>(в PostgreSQL
> для "ORDER BY", MIN(), MAX() часто используется "Seq Scan" вместо индекса,
> каждый случай нужно рассматривать используя EXPLAIN, иногда использование LIMIT помогает "выбрать"
>index вместо Seq Scan).
>
Эта ситуация хорошо рассмотрена здесь http://www.opennet.me/docs/RUS/postgresql_tune/node3.html#SE...
>Интересно кто как оптимизирует свои базы ?Ниже уже отлично все разжевали, добавить ничего.
> Интересно почему в PostgreSQL (7.2.3 FreeBSD 4.7 SMP) постоянно возникают deadlock.
В 4.x SMP не совсем полноценный, могу посоветовать дождаться 5.2-5.4 и попробовать перейти потестировать в боевых условиях на 5.x, хотя сомневаюсь, что проблема из-за SMP, маловероятно и легко проверить (перезагрузиться без SMP поддержки).
Другой вариант - попробовать перейти на PostgreSQL 7.3.2, там много чего исправили и после 7.2.3.
Возможно пригодится кому. Гоу.
Один из самых важных аспектов для производительности - написание заведомо правильных и адаптированных под конкретную задачу и среду приложений. Никакой тюнинг не спасет вас в случае бесконечного цикла в коде или таймаутов. Соcтояние MySQL можно отслеживать с помощью различных ключей утилиты mysqladmin (SHOW PROCESSLIST, SHOW STATUS) в сочетании с мониторингом всей системы утилитами top и ps. Для удобства можно использовать phpMyAdmin.
Условно можно выделить 3 группы проблем:
а) соединение клиента с сервером;
б) проблемы в самой базе;
в) проблемы дискового ввода/вывода.Для начала, необходимо посмотреть параметры, заданные по умолчанию в MySQL при компиляции. Для этого можно воспользоваться готовым программным обеспечением (phpMyAdmin), либо помощью утилит имеющихся в системе. В командной строке наберем
#mysqladmin -p variables, или запишем сразу в файл
#mysqladmin -p variables > temp.txt (опция p указывает на необходимость ввода пароля)
Переменные, которые заслуживают пристального внимания:
max_connections - число одновременных соединений разрешенное сервером;
table_cache - буфер хранения данных наиболее частых обращений;
key_buffer - буфер хранения последних использовавшихся ключей;
back_log - количество одновременных подключений по TCP/IP стеку;
skip-locking - редко встречаемая трабла в BSD, но лучше выставить (решает проблемы с блокировкой файлов).Устанавливая систему из портов, вы получите несколько готовых к применению конфигурационных файлов. Они рассчитаны для разного железа и количества пользователей. Необходимо выбрать наиболее близкий вашей системе вариант и отредактировать файл руками. Достаточно скопировать готовый файл под именем my.cnf в каталог /usr/var/db/mysql и перегрузить базу. Возможное содержание этого файла из расчета на 512 MB оперативной памяти выглядит так:
# Example def's MySQL config file [my.cnf]
# The MySQL server
[mysqld]
set-variable = max_connections=300
set-variable = back_log=120
skip-locking
set-variable = key_buffer=256M
set-variable = max_allowed_packet=1M
set-variable = table_cache=256
set-variable = sort_buffer=1M
set-variable = record_buffer=1M
set-variable = myisam_sort_buffer_size=64M
set-variable = thread_cache=8
# При многопроцессорной системе параметр умножить на число CPU
set-variable = thread_concurrency=2
log-bin
server-id = 1
# Меняем стандартные пути
tmpdir = /tmp/# Ускоряем архивацию
[mysqldump]
quick
set-variable = max_allowed_packet=16M# Оптимизация и проверка таблиц
[isamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=2M
set-variable = write_buffer=2M[mysqlhotcopy]
interactive-timeoutДля начала достаточно, двинемся дальше. О проблемах дискового ввода/вывода. Главное, помните, что swap и /var должны по возможности быть на разных физических HDD - базы mySQL находятся в каталоге /var/db/mysql, а mySQL регулярно свопится.
В FreeBSD вы обязательно найдете утилиту isamchk, которая предназначена для анализа размещения данных в таблицах, их оптимизации, нахождению и исправлению ошибок. Утилита имеет кучу параметров, но есть пара простых соображений по части её использования.
а) при каждом удвоении базы запускайте isamchk -a;
б) редко, но обязательно запускайте isamchk -d. Этот параметр покажет вам число удаленных блоков, и если их количество велико, следует после запускать isamchk -r.При большой нагрузке может оказаться, что пользователи получат отказ даже при заведомо выставленных правильно параметрах max_connections и back_log. Дело в том, что существует еще ограничение дескрипторов файлов и процессов в Unix. Эта проблема решается обязательной пересборкой ядра и выставлением опции maxusers = xxx, но это уже отдельная тема для разговора, а на сегодня все.