Тест производительности движков MySQL, PostgreSQL, FireBird (только SELECT в несколько потоков).
Рассказ о повышении производительности тормозящего запроса. Сравнение движков баз данных: 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 синтаксис):
Аналогичная структура базы данных была воспроизведена и в остальных движках БД: 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. Здесь же приведен всего лишь синтетический тест, одного конкретного медленного запроса.
Впрочем, эти выводы — лишь мое личное мнение
Источник
