Рассказ о повышении производительности тормозящего запроса. Сравнение движков баз данных: MySQL, FireBird, PostgreSQL, SQLite. Субъективные выводы. Описание теста. Сколько людей делало всяческие benchmark'и, разных баз данных... Все претендуют на объективность, все такие хорошие, соображающие и опытные тюнингаторы... :-).
Вчера стукнулись, попросили разобраться, что происходит с одним из серверов, некогда настроенным мной... А было там примерно следующее:
1:56PM up 44 days, 17:24, 1 user, load averages: 21.58, 22.19, 22.07
Как выяснилось, всему виной была база данных (неудивительно), базу настраивал лично я, параметры были подобраны нормально, кэширование запросов - включено, казалось бы - все должно быть отлично... Однако за эти пару-тройку месяцев база разрослась и начали возникать первые проблемы.
В продолжении статьи - детали и сравнение производительности БД для разных серверов: MySQL, FireBird, PostgreSQL. И движка SQLite. Все эти движки - опенсорсные и работают на Linux и FreeBSD.
Общие данные о структуре базы данных.
База, состоящая из одной таблицы, представлена следующим образом (MySQL синтаксис):
CREATE TABLE thetable (
"id" int NOT NULL AUTO_INCREMENT,
"var1" varchar(255) default '' NOT NULL,
"var2" varchar(255) default '' NOT NULL,
"var3" TEXT NOT NULL,
"place" varchar(255) NOT NULL,
"var4" varchar(255) NOT NULL,
"created" TIMESTAMP NOT NULL,
PRIMARY KEY ("id"),
KEY 'place' ('place')
);
Аналогичная структура базы данных была воспроизведена и в остальных движках БД: PostgreSQL, FireBird, SQLite. Для тестирования использовался perl (DBD, DBI), использовались модули Time::HiRes и threads.
Использованные серверы баз данных
PostgreSQL 8.0.13 on x86_64-pc-linux-gnu, compiled by GCC x86_64-pc-linux-gnu-gcc (GCC) 4.1.2 (Gentoo 4.1.2)
ISQL Version: LI-V2.0.3.12981 Firebird 2.0 Server version: Firebird/linux AMD64 (access method), version "LI-V2.0.3.12981 Firebird 2.0" Firebird/linux AMD64 (remote server), version "LI-V2.0.3.12981 Firebird 2.0/tcp (localhost)/P10" Firebird/linux AMD64 (remote interface), version "LI-V2.0.3.12981 Firebird 2.0/tcp (localhost)/P10" on disk structure version 11.0
MySQL: mysqladmin Ver 8.41 Distrib 5.0.44, for pc-linux-gnu on x86_64 Server version 5.0.44-log Protocol version 10 Connection Localhost via UNIX socket
Как видно, не для всех серверов версии последние (но для всех достаточно свежие). Везде использовалась 64-битная сборка. ОС - Gentoo Linux. Память: 1024M. Был выполнен следующий тюнинг серверов:
PostgreSQL: процессу передаются такие опции: -N 25 -c work-mem=65536 -c shared-buffers=1000 (не более 25 одновременных подключений, 1000 буферов, разделяемых на эти подключения, рабочая память не более 64Mb).
MySQL: включено кэширование SQL запросов (SET GLOBAL query_cache_limit = 1048576; SET GLOBAL query_cache_type = 1). Настройки для MyISAM дефолтные.
Тюнингом FireBird я никогда ранее не занимался, учиться времени не было, поэтому оставлен конфиг по умолчанию (ОС Gentoo Linux).
По причинам, о которых расскажу позже, привожу данные warm-up запуска (для того, чтобы движок БД считал данные с диска), производимого ОДИН раз, и не перед тестом непосредственно, а после старта системы:
PostgreSQL: четыре процесса (122Mb (6Mb) в сумме). Время разогревочного запроса 0m6.443s. Предположительно связано с огромным размером индекса на диске, я создал два индекса (btree и hash) для колонки place, не зная, какой из них будет использован движком.
MySQL: 202Mb (36Mb). Время разогревочного запроса 0m2.468s.
FireBird: 103Mb (6Mb). Время разогревочного запроса 0m7.836s.
SQLite: Время разогревочного запроса 0m5.108s.
Следующий этап - я останавливаю движки БД, а через 5 минут запускаю их вместе. Еще через 5 минут - выполняю тот же злополучный запрос. В течение этих 5 минут, в совершенно другом каталоге считается размер, занятый файлами.
0m0.841s - PostgreSQL, 0m2.395s - MySQL, 0m4.477s - FireBird, 0m2.896s - SQLite. Как видно, дисковый кэш разогрелся, все движки получили свой прирост производительности. Теперь тестовые данные запуска в такой конфигурации: 20 потоков выполняют по 10 запросов каждый. Выполнение всех 20 потоков начинается одновременно.
Ход тестирования:
Для тестирования использовался самодельный, на скорую руку написанный тест на Perl. Для многопоточности использовался модуль threads.
Multi thread: prepare_pg: Global avg is: 3.13079101085663 Память 124 (6)
Multi thread: prepare_my: Global avg is: 14.5757958674431 Память 484 (52)
Multi thread: prepare_fb: Global avg is: 0.00530530452728272 Память 95 (5)
Multi thread: prepare_s3: Global avg is: 43.2979071068764 Память 675 (111)
Как видно, FireBird успешно справился с кэшированием запроса, - вряд ли хоть один движок БД даже на суперкомпьютере, смог бы выполнить эту выборку за 0,005 секунды. Настройки по умолчанию - действительно отличные! Дальнейшей оптимизации движок подвергать не будем.
Хуже всех, как и ожидалось, в многопоточной среде отработал SQLite. Помимо прочего, во время выполнения скрипта, perl "отжирал" 675Mb (111Mb) памяти, т.е. ясно, что под нагрузкой, в многопоточном окружении, этот движок нежизнеспособен. Впрочем, другого и не ожидалось - не та сфера применения.
Но почему же такие низкие результаты показаны MySQL? MySQL известен по всему интернету, как движок с неимоверно быстрыми SELECT. Даже неверный выбор query plan (который действительно имеет место быть, позже я это покажу, в сравнительном анализе MySQL и PostgreSQL), не мог привести к такой низкой производительности.
Вероятнее всего, проблема кроется в использовании движка MyISAM. Попробуем с InnoDB. Перевод базы в InnoDB осуществлялся следующим образом: mysqldump baron table > table_mysql.sql, в текстовом редакторе vim было заменено ENGINE=MyISAM на ENGINE=InnoDB.
Блокировка в многопоточном режиме должна стать более эффективной, и производительность базы должна возрасти. Даже разогревочный запрос выполнялся всего лишь 0m1.385s.
Как и ожидалось, среднее время запроса уменьшилось (производительность выросла). Но ненамного. Увеличилась скорость выполнения первых запросов (7с.), а затем запросы стали достигать 15с. После еще одного, повторного запуска, производительность, кажется, повысилась еще больше (минимальное время составило 3с.) Этот результат и отражен в таблице.
Multi thread: prepare_my: Global avg is: 12.3769785487652. Память 220 (116).
Увеличение буфера сортировки дало свои результаты (вместо 512Kb по умолчанию, я задействовал 4Mb):
Multi thread: prepare_my: Global avg is: 10.900887594223. Память 235 (127).
Экспериментальным путем было выяснено, что 4 Mb достаточно (увеличение до 6Mb не дает прироста производительности).
Однако у нас в рукаве еще остался козырь: Если FireBird в состоянии закэшировать запрос, значит и MySQL также может это сделать. Надо только понять, в чем причина. А причина, вероятно всего, в большом объеме данных - план выполнения запроса показывает большое количество строк, которые будут проверены - вероятно, они занимают в памяти больше 1Mb, которое отведено под кэширование данных.
Проверим гипотезу. Раз для буфера сортировки оптимальное значение в районе 4 Mb, попробуем установить размер кэшируемых данных также в 4 Mb. Поднимем общий предел закэшированных данных до 20 Mb. (SET GLOBAL query_cache_limit = 4 * 1048576; SET GLOBAL query_cache_size = 20 * 1048576;):
Multi thread: prepare_my: Global avg is: 4.80021322250366. Память 362 (184).
Очевидно, где-то в середине сработало кэширование, и результат получился приемлемым :-). Однако, пробуем еще раз:
Multi thread: prepare_my: Global avg is: 0.000454176664352417
Итак, - мы только что догнали и обогнали FireBird.
Теперь попробуем максимально приблизить к этому результату и PostgreSQL.
Увеличение -c effective_cache_size=1000 не дало нужного результата. Не дало результата и его дальнейшее повышение.
Возможно, в 8.3 версии стало лучше, но сейчас запросы не кэшируются.
Сухая статистика:
Многопоточный тест производительности выборки:
| Движок базы данных | 1 запрос | Память |
| PostgreSQL | 3,1308 | 6 |
| MySQL MyISAM | 14,5758 | 52 |
| MySQL InnoDB | 12,377 | 116 |
| MySQL InnoDB (sort_buffer) | 10,9009 | 127 |
| MySQL InnoDB (sort_buffer, query_cache_limit) | 0,0005 | 184 |
| FireBird | 0,0053 | 5 |
| SQLite 3 | 43,2979 | 111 |
Разогрев базы данных:
| Движок базы данных | 1 запрос |
| PostgreSQL | 6,443 |
| MySQL MyISAM | 2,468 |
| MySQL InnoDB | 1,385 |
| FireBird | 7,836 |
| SQLite 3 | 5,108 |
Примечания: Нет возможности посмотреть реальную статистику использования памяти PostgreSQL, т.к. для выполнения запросов создаются отдельные процессы. Для SQLite 3 статистика памяти не может быть точной, т.к. приведен расход памяти всего perl скрипта, в него включен расход памяти интерпретатора.
Субъективные выводы
Для этого теста лучше всех в конфигурации "из коробки" проявил себя FireBird. Хотя в целом производительность выборки была продемонстрирована довольно посредственная, но ведь это же конфигурация "из коробки" - ни один параметр не подвергался тюнингу, вероятно, разогревочный запрос можно было увеличить хотя бы до уровня PostgreSQL. В любом случае, FireBird оставляет много места для оптимизации - в частности, можно задать план выполнения запроса (query plan) вручную. Однако "из коробки", запросы выполнялись 1.10 sec - в режиме объяснения query plan.
Чрезвычайно порадовали математики, писавшие алгоритм выбора плана запроса для PostgreSQL. План подобран более качественно, благодаря этому стало возможным троекратное превосходство над MySQL. Наверняка есть и возможности роста - во-первых, таблицы размещались в файле; во-вторых, параметры (кроме количества одновременных подключений и размера памяти) не были затронуты. Вероятно, можно изменить размер буфера сортировки; возможно, можно явно указать более эффективный план запроса вручную. Но в любом случае, 0.375 sec при выполнении одиночного запроса на разогретой базе - очень хороший результат.
MySQL, не позволяющий задать план запроса, оказался в не самом удачном положении. Хотя его спасло наличие кэша результатов - при условии, что база не изменяется, за счет него он способен "порвать" очень многих конкурентов. Без использования кэша, на разогретой базе - время выполнения запросов составляет 1.19 sec.
SQLite был оставлен исключительно для того, чтобы было с чем сравнивать. Подобное применение - совершенно нетипично для этого движка, у него и не было ни малейшего шанса. Однако, при условии однопользовательского окружения, SQLite показывает довольно неплохой результат. 2.57 секунд - это всего лишь в 2 раза медленнее, чем MySQL.
Ни один из упомянутых движков, на мой взгляд, не был достаточно хорош. PostgreSQL был бы близок к термину "достаточно хорош" в моем понимании, если бы поддерживал query cache и "из коробки" смог бы конкурировать с FireBird в этом конкретном случае. FireBird бы был "достаточно хорош", если бы производительность выполнения запроса без кэширования оказалось на хорошем уровне. MySQL достаточно хорош - и его повсеместное применение для некритичных задач это подтверждает. Однако при масштабировании (scale) базы данных, могут возникать проблемы, подобные описанной здесь.
Есть, конечно, и иные точки зрения на этот счет. Пример: недавнее исследование - сравнение производительности Drupal 5.5 с базой данных на PostgreSQL и MySQL. Здесь же приведен всего лишь синтетический тест, одного конкретного медленного запроса.
Впрочем, эти выводы - лишь мое личное мнение