Тестирование производительности форков Mysql на реальных данных

Введение


Назрел апгрейд системы web сервера, на котором с 2007 года крутился сайт интернет-магазина на самописном движке mysql 5.1 + perl + apache + nginx.

Как обычно, при росте посещаемости все стало упираться в базу данных. Стал выбирать новую базу данных, совместимую с текущей. Выбирал из Mysql 5.5, Mysql 5.6, MariaDB 10, Percona Sever 5.6.

После длительного изучения бенчмарков стало понятно, что нужно тестировать производительность на реальных данных. Во-первых, в большинстве случаев сравнивали InnoDB и XtraDB, во-вторых — тестировали в основном бешеные нагрузки на монстр-серверах, интересные мне показатели находились на узеньком участке графика, где обычно ничего не было понятно.

Подготовка к тестированию


  1. Создаем виртуалку, я выбрал простую конфигурацию с 4 ядрами и 4гб оперативки, Debian 7.6;
  2. Ставим nginx и аpache, конфигурацию берем из коробки без кэша и тюнинга;
  3. Ставим siege — утилиту для нагрузочного тестирования, есть в стандартном репозитории;
  4. Лезем в статистику сайта и выбираем самые популярные страницы, чтобы протестировать работу сайта полноценно. Я выбрал первые 50 страниц по хитам за последний месяц, сохраняем эти урлы в urls.txt, Яндекс.Метрика умеет выгружать эти данные в csv;
  5. Прописываем в /etc/hosts ip виртуалки для нужного домена;
  6. Подключаем репозитории Mysql, Maria, Percona

    deb http://mirror.timeweb.ru/mariadb/repo/10.0/debian wheezy main
    deb-src http://mirror.timeweb.ru/mariadb/repo/10.0/debian wheezy main
    deb http://repo.flops.ru/debian/ wheezy free
    
    deb http://repo.percona.com/apt wheezy main
    deb-src http://repo.percona.com/apt wheezy main
    
    deb http://repo.mysql.com/apt/debian/ wheezy mysql-5.6
    deb-src http://repo.mysql.com/apt/debian/ wheezy mysql-5.6
    


    Mysql 5.5 есть в стандартном репозитории;
  7. Переносим дамп старой базы данных, создаем еще 2 дампа с подменой стореджа
    sed -e 's/myisam/InnoDB/gi' dump_myisam.sql >dump_innodb.sql 
    sed -e 's/myisam/XtraDB/gi' dump_myisam.sql >dump_xtradb.sql
    


Само тестирование


Ставим по очереди базы данных и заливаем туда созданные дампы, проводим тестирование с помощью siege. При установке новой базы полностью сносим пакеты предыдущей и чтобы не париться с конвертацией удаляем каталог /var/lib/mysql, затем по новой заливаем дамп.

В качестве параметров для siege я выбрал: /usr/bin/siege -b -c 20 -r 50 -f urls.txt -v
  • -b включает режим бенчмарка, и не делает произвольных пауз между запросами, на Ваше усмотрение эту опцию можно не использовать;
  • -c 20 задает количество одновременных запросов. Я выбрал 20, это примерно соответствует пиковой нагрузке на боевую конфигурацию в моем случае;
  • -r 50 задает количество запросов с каждого потока, имеет смысл делать равным количеству URL для тестирования, чтобы пробежаться по всем;
  • -f urls.txt — задает файл с тестируемыми URL сайта.

В итоге siege делает 1000 запросов к сайту в 20 потоков.

Параметры трестируемой базы


Размер на диске: 300МБ (в MyIsam)
Чтение / Запись: 98% / 2%

Основная таблица имеет 40000 записей, порядка 200 столбцов (в ней хранится товары и их свойства), к ней джойнятся дополнительные параметры: производители, курсы валют, группы, акции скидки и другие спецпризнаки, единицы измерения, коллекции… Порядка 10 джойнов.

my.cnf

key_buffer              = 512M
join_buffer_size        = 158M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
max_connections        = 100
table_cache            = 500
query_cache_limit       = 16M
query_cache_size        = 2G

innodb_log_file_size    = 50M
innodb_buffer_pool_size = 512M
innodb_log_buffer_size  = 8M
innodb_file_per_table   = 1
innodb_open_files       = 2548
innodb_io_capacity      = 400
innodb_flush_method     = O_DIRECT



Результаты тестирования


Описание колонок

  • ETime — Общее время тестирования (1000 запросов) в секундах
  • RTime — Среднее время ответа в секундах
  • TRate — Среднее количество запросов в секунду
  • Conc — Максимально выдерживаемое количество клиентов одновременно
  • OK — Количество успешно обработанных запросов (статус 200 OK)
  • LA — Максимальный Load Average в процессе тестирования
  • MEM — Общая занятая память на виртуалке


База/Стореж ETime RTime TRate Conc OK LA MEM
Mysql 5.1 MyISAM 252.98 3.95 3.83 15.15 969 6.98 1055
Mysql 5.5 MyISAM 500.19 7.48 1.80 13.50 902 14.67 1265
Mysql 5.6 MyISAM 289.89 5.55 3.43 19.01 994 6.59 1000
Percona 5.6 MyISAM 510.71 8.42 1.80 15.15 919 11.76 1657
MariaDB 10.0 MyISAM 351.74 6.10 2.74 16.71 964 10.23 889
MariaDB 10.0 Aria page=8k Trans 904.76 12.07 0.74 8.89 666 27.86 1545
MariaDB 10.0 Aria page=1k NonTrans 781.43 10.47 0.94 9.89 738 27.76 1465
Mysql 5.1 InnoDB 368.21 5.91 2.55 15.08 940 7.23 1241
Mysql 5.6 InnoDB 229.92 4.54 4.35 19.76 1000 6.66 1414
MariaDB InnoDB 223.35 4.38 4.48 19.61 1000 6.24 1813
Percona 5.6 XtraDB 223.78 4.42 4.47 19.75 1000 5.09 1176
MariaDB 10.0 XtraDB 222.55 4.38 4.49 19.66 1000 6.39 1176


Диаграммы





Из данных теста выбираю между Maria DB и Percona Server на XtraDB. Склоняюсь к Percona. Кроме незначительного выигрыша в производительности есть еще расширенная статистика по запросам к таблицам и хороший набор утилит.

P.S. Тестирование заняло порядка 5 часов, большую часть времени занял перенос данных с боевой машин и установка/снос пакетов. А вообще, мой хостер (flops.ru) позволяет клонировать виртуалку, занимает это порядка 1 минуты и перебрасывать IP между виртуалками. Если бы не старый софт на боевой виртуалке, можно было бы клонировать ее и потестировать (списание средств идет посуточно за потребление). Это сэкономило бы мне кучу времени.

P.P.S. Думаю следующим этапом устроить тестирование в боевом режиме, сделать клон виртуалки, настроить тестовую срезу и на 1 день перекинуть IP адрес с боевой.
Share post

Comments 24

    0
    Хорошее сравнение, достаточно давно использую Maria db однако не задумывался о переходе на XtraDB, хотя и mysql 5.6 себя неплохо показывает в реальных условиях.
      +3
      Без TokuDB не интересно :)
        –2
        TokuDB подглючивает
          +2
          а конкретнее? (интересно, вроде они стабильные)
            0
            Вот именно на больших данных и подглючивает. Может что-то и изменилось за это время, делали тесты года два назад.
          +1
          Он же вроде как оптимизирует UPDATE / INSERT, а у меня тут Чтение / Запись: 98% / 2%
            0
            ну не только, у них ведь еще индексы интересные есть. мы делали DWH под собственные нужды (да да, от бедности не на vertica|infobrigh) – и лидером по тестам получился именно tokudb.
          +2
          Гвоздь в крышку аргументу, что MyISAM быстрее.
            –1
            MyISAM таки может быть быстрее, но на вставку, а здесь чтение 98%.
              +1
              MyISAM при вставке блокирует таблицу полностью, в отличии от InnoDB, который при вставке блокирует только строку. Только это говорит об обратном. Все дело в транзакциях и внешний ключах, которых нет в MyISAM, и при бездумной работе с ними можно сильно замедлить InnoDB.
              +10
              Есть много use-кейсов, когда MyISAM просто несравнимо быстрее и более предсказуем, чем InnoDB (но все они работают с ограничением, что данные вам «не нужны», то есть, вы можете их восстановить каким-либо образом в случае краха БД, чтобы не ждать REPAIR TABLE):

              — С опцией myisam_use_mmap, сортировка по вторичным индексам, с доп. фильтрацией работала, по моим бенчмаркам, раз в 5 быстрее:

              SELECT * FROM table WHERE <...> ORDER BY indexed_col LIMIT <...>


              Объясняется это довольно просто — в индексах InnoDB содержится первичный ключ, а в MyISAM — смещение строки. Если строки маленькие и при этом фиксированного размера, то такие запросы проходят на пол-порядка быстрее

              — Если все, что вы делаете с таблицами — это вставка большого количества строк (в один поток на каждую конкретную таблицу) и редкая выборка по индексам, то MyISAM обладает минимальным оверхедом (в плане места и нагрузки на диск), позволяя вставлять сотни тысяч строк в секунду в каждую из таблиц

              — Если вы даете очень большой поток UPDATE (и при этом вам не очень «жалко» данные), то MyISAM ведет себя намного более предсказуемо в плане производительности — у него нет транзакций и purge thread, он масштабируется практически линейно по ядрам

              Если интересно, я могу попробовать провести честные бенчмарки и оформить в виде статьи :)
                +4
                Да, интересно
                  +1
                  Интересно, оформляйте!
                    0
                    Статью можно не ждать?)
                  –1
                  Эх, жаль, нету MariaDB 5.5, которую многие ставят вместо Mysql.
                    0
                    Можно будет дополнить ими потом, вообще хотел посмотреть именно последние версии, Mysql 5.1 здесь был для себя, чтобы сравнить производительность с текущей боевой средой
                    +1
                    Занимательно то, что если посмотреть на прогресс InnoDB от 5.1 к XtraDB, то видно что его сильно ускорили в 5.5, в 5.6 ещё немного, а форки ещё слегка допилили. В тоже время производительность MyISAM прыгает от версии к версии как захочет.
                      +1
                      А где структура таблиц и запросы на которых тестировали? Без этого это сферическое тестирование в вакууме с непонятными красивыми графиками на выходе…
                        +1
                        Примерно так и есть, суть поста в том, что утверждения вроде MyIsam быстрее InnoDB или Aria не уступает MyIsam нужно проверять для себя самостоятельно.
                        Тестируются не запросы к БД а запросы к сайту, который в свою очередь порождает запросы к базе данных, их там много разных, не думаю что имеет смысл все выкладывать.
                        К статье стоит относиться как к описанию методики, данные приводятся для конкретной базы, но все же могут быть интересны пользователям и с некоторой точностью отражают общую картину.
                          0
                          Aria быстрее MyISAM с FIXED ROW форматом на выборках (но и данные должны быть ограниченной длины). Оверхэд только в размере таблицы
                        –2
                        Раньше тоже частенько мигрировал из разных БД и проводил бессонные ночи тестирований нагрузок.
                        Потом просто остановился на Maria db + ssd в рейде. Уже год как никаких проблем, все летает при сравнительно любых нагрузках.
                          +1
                          Сталкивался с тем, что большие значения query_cache_size ведут к тормозам.

                          Это особенность реализации query_cache в mysql, подробнее в блоге перконы:
                          www.percona.com/blog/2007/03/23/beware-large-query_cache-sizes/

                          2G слишком много.

                          Попробуйте меньшее значение, например:

                          query_cache_size               = 128M
                          query_cache_limit              = 8M
                          
                            0
                            Ох и охота вам от мейнстрима отходить? А что будет когда 5.7 выйдет?
                              0
                              Не известно что придумает Oracle с лицензией

                            Only users with full accounts can post comments. Log in, please.