MySQL по-прежнему остается самой популярной в мире реляционной базой данных, и, тем не менее, она по-прежнему наиболее неоптимизирована — многие люди оставляют ее по умолчанию, не беспокоясь о дальнейших исследованиях. В этой статье мы рассмотрим некоторые советы по оптимизации MySQL, которые мы рассмотрели ранее, и объединим их с новинками, появившимися с тех пор.
Оптимизация конфигурации
Первый — и самый пропущенный! — повышение производительности, которое должен сделать каждый пользователь MySQL, — это настройка конфигурации. 5.7 (текущая версия) имеет намного лучшие значения по умолчанию, чем его предшественники, но все же легко сделать улучшения поверх них.
Мы предполагаем, что вы используете хост на базе Linux или хорошую Vagrant- коробку, такую как Homestead Improved, поэтому ваш файл конфигурации будет находиться в /etc/mysql/my.cnf . Вполне возможно, что ваша установка на самом деле загрузит вторичный файл конфигурации в этот файл конфигурации, поэтому посмотрите на это — если в файле my.cnf не так много содержимого, файл /etc/mysql/mysql.conf.d/mysqld.cnf может.
Редактирование конфигурации
Вам должно быть удобно с командной строкой. Даже если вы еще не сталкивались с этим, сейчас самое подходящее время.
Если вы редактируете локально в окне Vagrant, вы можете скопировать файл в основную файловую систему, скопировав его в общую папку с помощью cp /etc/mysql/my.cnf /home/vagrant/Code и отредактировав его с помощью обычной текстовый редактор, а затем скопировать его обратно на место, когда закончите. В противном случае используйте простой текстовый редактор, такой как vim, выполнив sudo vim /etc/mysql/my.cnf .
Примечание. Измените указанный выше путь, чтобы он соответствовал реальному местоположению файла конфигурации — возможно, он находится в /etc/mysql/mysql.conf.d/mysqld.cnf
Ручные твики
Следующие ручные настройки должны быть сделаны из коробки. Согласно этим советам , добавьте это в файл конфигурации в разделе [mysqld] :
innodb_buffer_pool_size = 1G # (adjust value here, 50%-70% of total RAM) innodb_log_file_size = 256M innodb_flush_log_at_trx_commit = 1 # may change to 2 or 0 innodb_flush_method = O_DIRECT
-
innodb_buffer_pool_size— пул буферов является областью хранения для кэширования данных и индексов в памяти. Он используется для хранения часто используемых данных в памяти, и когда вы используете выделенный или виртуальный сервер, где БД часто является узким местом, имеет смысл предоставить этой части вашего приложения максимум оперативной памяти. Следовательно, мы даем ему 50-70% всей оперативной памяти. В документации по MySQL есть руководство по определению размера буферного пула. - Размер файла журнала хорошо объяснен здесь, но в двух словах, сколько данных нужно сохранить в журнале перед его очисткой. Обратите внимание, что журнал в этом случае не является журналом ошибок или чем-то, к чему вы могли бы привыкнуть, но вместо этого он указывает время контрольной точки, потому что с MySQL записи происходят в фоновом режиме, но все же влияют на производительность переднего плана. Большие файлы журнала означают лучшую производительность из-за того, что создается меньше новых и меньших контрольных точек, но более длительное время восстановления в случае сбоя (необходимо перезаписать больше данных в БД).
-
innodb_flush_log_at_trx_commitобъясняетсяinnodb_flush_log_at_trx_commitи указывает, что происходит с файлом журнала. С 1 у нас есть самая безопасная настройка, потому что журнал сбрасывается на диск после каждой транзакции. С 0 или 2 это меньше ACID, но более производительный. Разница в этом случае не настолько велика, чтобы перевесить преимущества стабильности 1. -
innodb_flush_method— чтобыinnodb_flush_method, устанавливается значениеO_DIRECTчтобы избежать двойной буферизации. Это всегда следует делать, если только система ввода-вывода не обладает очень низкой производительностью. На большинстве хост-серверов, таких как дроплеты DigitalOcean, у вас есть SSD, поэтому система ввода-вывода будет иметь высокую производительность.
Есть еще один инструмент от Percona, который может помочь нам автоматически найти оставшиеся проблемы. Обратите внимание, что если бы мы запустили его без вышеуказанных ручных настроек, только 1 из 4 исправлений был бы идентифицирован вручную, потому что остальные 3 зависят от предпочтений пользователя и среды приложения.
Инспектор переменных
Чтобы установить инспектор переменных в Ubuntu:
wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb sudo apt-get update sudo apt-get install percona-toolkit
Для других систем следуйте инструкциям .
Затем запустите инструментарий с:
pt-variable-advisor h=localhost,u=homestead,p=secret
Вы должны увидеть результат, не похожий на этот:
# WARN delay_key_write: MyISAM index blocks are never flushed until necessary. # NOTE max_binlog_size: The max_binlog_size is smaller than the default of 1GB. # NOTE sort_buffer_size-1: The sort_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it. # NOTE innodb_data_file_path: Auto-extending InnoDB files can consume a lot of disk space that is very difficult to reclaim later. # WARN log_bin: Binary logging is disabled, so point-in-time recovery and replication are not possible.
Все это не критично, их не нужно исправлять. Единственное, что мы могли бы добавить, это двоичное ведение журналов для репликации и создания снимков.
Примечание: размер binlog по умолчанию будет равен 1G в новых версиях и не будет отмечен PT.
max_binlog_size = 1G log_bin = /var/log/mysql/mysql-bin.log server-id=master-01 binlog-format = 'ROW'
- параметр
max_binlog_sizeопределяет, насколько большими будут двоичные журналы. Это журналы, которые регистрируют ваши транзакции и запросы и создают контрольные точки. Если транзакция больше, чем max, тогда журнал может быть больше, чем max при сохранении на диск — в противном случае MySQL сохранит их на этом пределе. - опция
log_binвключает двоичное ведение журнала вообще. Без этого нет снимков или репликации. Обратите внимание, что это может быть очень напряженным на диске. Идентификатор сервера является необходимой опцией при активации двоичного ведения журнала, поэтому журналы знают, с какого сервера они пришли (для репликации), а формат — это просто способ записи журналов.
Как вы можете видеть, новый MySQL имеет нормальные настройки по умолчанию, которые почти готовы к работе. Конечно, каждое приложение уникально и имеет дополнительные настройки.
MySQL Tuner
Тюнер будет отслеживать базу данных с более длительными интервалами (запускать ее раз в неделю или около того в реальном приложении) и предлагать изменения в зависимости от того, что он видит в журналах.
Установите его, просто загрузив его:
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl chmod +x mysqltuner.pl
Запуск его с ./mysqltuner.pl запросит у вас имя пользователя и пароль администратора для базы данных и выведет информацию из быстрого сканирования. Например, вот мой раздел InnoDB:
[--] InnoDB is enabled. [--] InnoDB Thread Concurrency: 0 [OK] InnoDB File per table is activated [OK] InnoDB buffer pool / data size: 1.0G/11.2M [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (50 %): 256.0M * 2/1.0G should be equal 25% [!!] InnoDB buffer pool <= 1G and Innodb_buffer_pool_instances(!=1). [--] Number of InnoDB Buffer Pool Chunk : 8 for 8 Buffer Pool Instance(s) [OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances [OK] InnoDB Read buffer efficiency: 96.65% (19146 hits/ 19809 total) [!!] InnoDB Write Log efficiency: 83.88% (640 hits/ 763 total) [OK] InnoDB log waits: 0.00% (0 waits / 123 writes)
Опять же, важно отметить, что этот инструмент должен запускаться раз в неделю или около того, так как сервер работал. После изменения значения конфигурации и перезапуска сервера его следует запустить через неделю после этого. Это хорошая идея, чтобы настроить cronjob, чтобы сделать это для вас и периодически отправлять вам результаты.
Убедитесь, что вы перезапускаете сервер mysql после каждого изменения конфигурации:
sudo service mysql restart
Индексы
Далее, давайте сосредоточимся на индексах — основной больной точке зрения многих администраторов БД! Особенно те, кто сразу же подключается к ORM и, таким образом, никогда не подвергается действительному сырому SQL.
Примечание: термины ключи и индексы могут использоваться взаимозаменяемо.
Вы можете сравнить индексы MySQL с индексом в книге, которая позволяет легко найти нужную страницу, содержащую искомую тему. Если бы не было никаких индексов, вам пришлось бы просматривать всю книгу в поисках страниц, содержащих эту тему.
Как вы можете себе представить, поиск по индексу намного быстрее, чем просмотр каждой страницы. Поэтому добавление индексов в вашу базу данных в целом ускоряет ваши запросы выбора. Тем не менее, индекс также должен быть создан и сохранен. Таким образом, запросы на обновление и вставку будут выполняться медленнее и будут стоить вам немного больше места на диске. Как правило, вы не заметите разницы с обновлением и вставкой, если правильно проиндексировали свою таблицу, и поэтому желательно добавлять индексы в нужных местах.
Таблицы, которые содержат только несколько строк, на самом деле не получают выгоды от индексации. Вы можете себе представить, что поиск по 5 страницам не намного медленнее, чем сначала перейти к индексу, получить номер страницы и затем открыть эту конкретную страницу.
Итак, как мы узнаем, какие индексы добавить и какие типы индексов существуют?
Уникальные / первичные индексы
Первичные индексы — это основные индексы данных, которые являются способом адресации по умолчанию. Для учетной записи пользователя это может быть идентификатор пользователя или имя пользователя, даже основной адрес электронной почты. Первичные индексы уникальны. Уникальные индексы — это индексы, которые не могут повторяться в наборе данных.
Например, если пользователь выбрал конкретное имя пользователя, никто другой не сможет его взять. Добавление «уникального» индекса к столбцу username решает эту проблему. MySQL будет жаловаться, если кто-то попытается вставить строку с уже существующим именем пользователя.
... ALTER TABLE `users` ADD UNIQUE INDEX `username` (`username`); ...
Первичные ключи / индексы обычно определяются при создании таблицы, а уникальные индексы определяются по факту путем изменения таблицы.
И первичные ключи, и уникальные ключи могут создаваться в одном или нескольких столбцах одновременно. Например, если вы хотите убедиться, что в каждой стране может быть определено только одно имя пользователя, вы создадите уникальный индекс для обоих столбцов, например:
... ALTER TABLE `users` ADD UNIQUE INDEX `usercountry` (`username`, `country`), ...
Уникальные индексы помещаются в столбцы, к которым вы будете часто обращаться. Так что, если учетная запись пользователя часто запрашивается и у вас много учетных записей в базе данных, это хороший вариант использования.
Регулярные индексы
Регулярные индексы облегчают поиск. Они очень полезны, когда вам нужно быстро найти данные по определенному столбцу или комбинации столбцов, но эти данные не обязательно должны быть уникальными.
... ALTER TABLE `users` ADD INDEX `usercountry` (`username`, `country`), ...
Вышеуказанный способ ускорит поиск имен пользователей в каждой стране.
Индексы также помогают с сортировкой и скоростью группировки.
Полнотекстовые индексы
Индексы FULLTEXT используются для полнотекстового поиска. Только механизмы хранения InnoDB и MyISAM поддерживают индексы FULLTEXT и только для столбцов CHAR, VARCHAR и TEXT.
Эти индексы очень полезны для всего поиска текста, который вам может понадобиться. Поиск слов внутри текстовых текстов — специальность FULLTEXT. Используйте их в сообщениях, комментариях, описаниях, отзывах и т. Д., Если вы часто разрешаете искать их в своем приложении.
Нисходящие индексы
Не особый тип, а переделка. Начиная с версии 8+, MySQL поддерживает индексы по убыванию , что означает, что он может хранить индексы в порядке убывания. Это может пригодиться, когда у вас огромные таблицы, которым часто нужны последние добавленные данные, или расставьте приоритеты для записей таким образом. Сортировка по убыванию всегда была возможна, но приводила к небольшому снижению производительности. Это еще больше ускоряет ход вещей.
CREATE TABLE t ( c1 INT, c2 INT, INDEX idx1 (c1 ASC, c2 ASC), INDEX idx2 (c1 ASC, c2 DESC), INDEX idx3 (c1 DESC, c2 ASC), INDEX idx4 (c1 DESC, c2 DESC) );
Подумайте о применении DESC к индексу при работе с журналами, записанными в базе данных, постами и комментариями, которые загружаются в порядке очереди и т. П.
Вспомогательные инструменты: объяснить
При поиске оптимизации запросов инструмент EXPLAIN будет бесценным. Префикс простого запроса с EXPLAIN обработает его очень подробно, проанализирует используемые индексы и покажет соотношение попаданий и промахов. Вы заметите, сколько строк нужно было обработать, чтобы получить результаты, которые вы ищете.
EXPLAIN SELECT City.Name FROM City JOIN Country ON (City.CountryCode = Country.Code) WHERE City.CountryCode = 'IND' AND Country.Continent = 'Asia'
Вы можете расширить это с помощью EXTENDED :
EXPLAIN SELECT City.Name FROM City JOIN Country ON (City.CountryCode = Country.Code) WHERE City.CountryCode = 'IND' AND Country.Continent = 'Asia'
Посмотрите, как использовать это и применить открытия, прочитав этот превосходный, подробный пост .
Вспомогательные инструменты: Percona для дублированных индексов
Ранее установленный Percona Toolkit также имеет инструмент для обнаружения дублирующихся индексов, который может пригодиться при использовании сторонних CMS или просто проверки, если вы случайно добавили больше индексов, чем необходимо. Например, установка WordPress по умолчанию имеет дублирующиеся индексы в таблице wp_posts :
pt-duplicate-key-checker h=localhost,u=homestead,p=secret # ######################################################################## # homestead.wp_posts # ######################################################################## # Key type_status_date ends with a prefix of the clustered index # Key definitions: # KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`), # PRIMARY KEY (`ID`), # Column types: # `post_type` varchar(20) collate utf8mb4_unicode_520_ci not null default 'post' # `post_status` varchar(20) collate utf8mb4_unicode_520_ci not null default 'publish' # `post_date` datetime not null default '0000-00-00 00:00:00' # `id` bigint(20) unsigned not null auto_increment # To shorten this duplicate clustered index, execute: ALTER TABLE `homestead`.`wp_posts` DROP INDEX `type_status_date`, ADD INDEX `type_status_date` (`post_type`,`post_status`,`post_date`);
Как видно из последней строки, он также дает советы о том, как избавиться от дублирующих индексов.
Вспомогательные инструменты: Percona для неиспользуемых индексов
Percona также может обнаруживать неиспользуемые индексы. Если вы регистрируете медленные запросы (см. Раздел «Узкие места» ниже), вы можете запустить инструмент, и он проверит, используют ли эти зарегистрированные запросы индексы в таблицах, связанных с запросами.
pt-index-usage /var/log/mysql/mysql-slow.log
Подробнее об использовании этих инструментов см. Здесь .
Узкие
В этом разделе объясняется, как обнаруживать и отслеживать узкие места в базе данных.
slow_query_log = /var/log/mysql/mysql-slow.log long_query_time = 1 log-queries-not-using-indexes = 1
Выше следует добавить к конфигурации. Он будет отслеживать запросы продолжительностью более 1 секунды и не использующие индексы.
Когда в этом журнале есть данные, вы можете проанализировать его на предмет использования индекса с помощью вышеупомянутого инструмента pt-index-usage или инструмента pt-query-digest , который выдает такие результаты:
pt-query-digest /var/log/mysql/mysql-slow.log # 360ms user time, 20ms system time, 24.66M rss, 92.02M vsz # Current date: Thu Feb 13 22:39:29 2014 # Hostname: * # Files: mysql-slow.log # Overall: 8 total, 6 unique, 1.14 QPS, 0.00x concurrency ________________ # Time range: 2014-02-13 22:23:52 to 22:23:59 # Attribute total min max avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= # Exec time 3ms 267us 406us 343us 403us 39us 348us # Lock time 827us 88us 125us 103us 119us 12us 98us # Rows sent 36 1 15 4.50 14.52 4.18 3.89 # Rows examine 87 4 30 10.88 28.75 7.37 7.70 # Query size 2.15k 153 296 245.11 284.79 48.90 258.32 # ==== ================== ============= ===== ====== ===== =============== # Profile # Rank Query ID Response time Calls R/Call V/M Item # ==== ================== ============= ===== ====== ===== =============== # 1 0x728E539F7617C14D 0.0011 41.0% 3 0.0004 0.00 SELECT blog_article # 2 0x1290EEE0B201F3FF 0.0003 12.8% 1 0.0003 0.00 SELECT portfolio_item # 3 0x31DE4535BDBFA465 0.0003 12.6% 1 0.0003 0.00 SELECT portfolio_item # 4 0xF14E15D0F47A5742 0.0003 12.1% 1 0.0003 0.00 SELECT portfolio_category # 5 0x8F848005A09C9588 0.0003 11.8% 1 0.0003 0.00 SELECT blog_category # 6 0x55F49C753CA2ED64 0.0003 9.7% 1 0.0003 0.00 SELECT blog_article # ==== ================== ============= ===== ====== ===== =============== # Query 1: 0 QPS, 0x concurrency, ID 0x728E539F7617C14D at byte 736 ______ # Scores: V/M = 0.00 # Time range: all events occurred at 2014-02-13 22:23:52 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 37 3 # Exec time 40 1ms 352us 406us 375us 403us 22us 366us # Lock time 42 351us 103us 125us 117us 119us 9us 119us # Rows sent 25 9 1 4 3 3.89 1.37 3.89 # Rows examine 24 21 5 8 7 7.70 1.29 7.70 # Query size 47 1.02k 261 262 261.25 258.32 0 258.32 # String: # Hosts localhost # Users * # Query_time distribution # 1us # 10us # 100us ################################################################ # 1ms # 10ms # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS LIKE 'blog_article'\G # SHOW CREATE TABLE `blog_article`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT b0_.id AS id0, b0_.slug AS slug1, b0_.title AS title2, b0_.excerpt AS excerpt3, b0_.external_link AS external_link4, b0_.description AS description5, b0_.created AS created6, b0_.updated AS updated7 FROM blog_article b0_ ORDER BY b0_.created DESC LIMIT 10
Если вы предпочитаете анализировать эти журналы вручную, вы можете сделать это тоже — но сначала вам нужно экспортировать журнал в более «анализируемый» формат. Это можно сделать с помощью:
mysqldumpslow /var/log/mysql/mysql-slow.log
Дополнительные параметры могут дополнительно фильтровать данные и обеспечивать экспорт только важных вещей. Например: первые 10 запросов отсортированы по среднему времени выполнения.
mysqldumpslow -t 10 -s at /var/log/mysql/localhost-slow.log
Для других параметров, см. Документы .
Вывод
В этом всеобъемлющем посте по оптимизации MySQL мы рассмотрели различные способы заставить MySQL летать.
Мы занимались оптимизацией конфигурации, использовали некоторые индексы и избавились от некоторых узких мест. Все это было в основном теоретическим, однако — для реального случая использования этих методов в реальном приложении, следите за обновлениями в нашем месячном проекте — скоро!
Мы пропустили какие-либо методы и советы? Дайте нам знать!