Оптимізація MySQL: індекси, повільні запити, конфігурація

1. оптимізація конфігурації
2. ручний тюнінг
3. Variable Inspector
4. MySQL Tuner
5. індекси
6. Унікальні / первинні індекси
7. регулярні індекси
8. повнотекстові індекси
9. Зворотні індекси (по спадаючій)
10. Допоміжні інструменти: Explain
11. Допоміжні інструменти: Percona Toolkit для виявлення дублюючих індексів
12. Допоміжні інструменти: Percona Toolkit для невикористовуваних індексів
13. Вузькі місця
14. висновок

Наша команда-партнер Artmisto

MySQL і раніше є найпопулярнішою в світі реляційної базою даних, але в той же час і особливо не оптимізованої. Багато людей залишаються з настройками за замовчуванням, не "копаючи" глибше. У цій статті ми розглянемо деякі поради щодо оптимізації MySQL в поєднанні з деякими новинками, які вийшли відносно недавно.

оптимізація конфігурації

Перше, що кожен користувач MySQL повинен зробити для підвищення продуктивності - це налаштувати конфігурацію. Однак, більшість цей крок пропускають. В 5.7 (поточна версія) настройки за замовчуванням стали набагато краще, ніж у її попередників, але поліпшити їх як і раніше можна і нескладно.

Ми сподіваємося, що ви використовуєте Linux або щось на зразок Vagrant -box (як наш Homestead Improved ), А, відповідно, ваш конфігураційний файл буде знаходитися в /etc/mysql/my.cnf. Цілком можливо, що ваша установка насправді буде довантажувати додатковий файл конфігурації в цей. Так що подивіться, якщо файл my.cnf містить трохи, то подивіться в /etc/mysql/mysql.conf.d/mysqld.cnf.

ручний тюнінг

Наступні настройки повинні бути зроблені "з коробки". згідно цих порад , Додайте в файл конфіга в розділ [mysqld]:

innodb_buffer_pool_size = 1G # (тут поміняйте приблизно 50% -70% від загального обсягу оператіви) innodb_log_file_size = 256M innodb_flush_log_at_trx_commit = 1 # можна поміняти на 2 або 0 innodb_flush_method = O_DIRECT

• innodb_buffer_pool_size. Пул буферизації (buffer pool) є таким собі "складом" для кешування даних і індексів в пам'яті. Він використовується, щоб зберігати часто використовувані дані в пам'яті. І коли ви використовуєте виділений або віртуальний сервер, на якому часто саме БД є вузьким місцем, то є сенс віддати їй більшу частину оператіви. Отже, ми даємо їй 50-70% всієї RAM. У документації MySQL є посібник з настроювання цього пулу .
• innodb_log_file_size. Налаштування розміру лог-файлу добре описана тут , Але в двох словах це кількість даних, що зберігаються в логах, перш ніж його почистять. Зверніть увагу, що лог в цьому випадку - це не записи про помилки, а якийсь дельта-зліпок змін, які ще не були скинуті на диск в основні файли innodb. MySQL пише у фоновому режимі, але це все ж впливає на продуктивність в момент запису. Великий лог-файл означає більш високу продуктивність через малу кількість створюваних нових і невеликих контрольних точок, але при цьому більш тривалий час відновлення в разі краш (більше даних має бути переписано в БД).
• innodb_flush_log_at_trx_commit описаний тут і показує, що відбувається з файлом логів. Значення 1 - найбезпечніше, т. К. Лог скидається на диск після кожної транзакції. При значеннях 0 і 2 - менше гарантується ACID , Але більше продуктивність. Різниця не є достатньо великий, щоб переважити переваги стабільності при 1.
• innodb_flush_method. На додачу до всього того, що стосується скидання даних, цей параметр потрібно встановити в O_DIRECT - щоб уникнути подвійної буферизації. Раджу завжди це робити, поки система введення-виведення залишається дуже повільної. Хоча на більшості хостингах, типу DigitalOcean, ви будете мати SSD-диски, тому система введення-виведення буде більш продуктивна.

Є інструмент від Percona, який допоможе нам знайти залишилися проблеми автоматично. Зверніть увагу, що якщо ми б запустили його без цієї ручної настройки, то тільки 1 з 4 налаштувань була б визначена, т. К. Інші 3 залежать від уподобань користувача та навколишнього середовища додатки.

Variable Inspector

Установка variable inspector на 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

Для інших систем, дотримуйтесь цих інструкцій .

Потім запустіть 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.

Прим. перекладача:
На моїй локальній машині, крім цього, видав ще ось такий Ворнінг:

set innodb_flush_method to O_DIRECT to avoid double -buffering, unless the I / O system is very low performance.

Про те, що параметр innodb_flush_method потрібно встановити в O_DIRECT і чому говорилося вище. І якщо ви дотримувалися послідовності тюнінгу як в статті, то ви не побачите це попередження.

Жодне з цих (прім.пер .: зазначених автором) попереджень не критично, їх необов'язково виправляти. Єдине, що можна поправити - це настройка бінарного логу для реплікації і снапшотов.

Примітка: в нових версіях розмір binlog-а за умовчанням 1G і цього Ворнінг не буде.

max_binlog_size = 1G log_bin = /var/log/mysql/mysql-bin.log server-id = master-01 binlog-format = 'ROW'

• max_binlog_size. Визначає наскільки великими будуть бінарні логи. У них записуються ваші транзакції і запити і робляться контрольні точки. Якщо транзакція перевищує максимум, то лог може перевищувати свій розмір при збереженні на диск; в іншому випадку MySQL буде підтримувати його в рамках цього ліміту.
• log_bin. Ця опція включає запис бінарних логів в цілому. Без неї неможливі снапшоти або реплікації. Зверніть увагу, що це може дуже позначитися на дисковому просторі. server-id - це необхідна опція при включенні бінарного логу, тому логи "знають" з якого сервера вони прийшли (для реплікації), а binlog-format - це просто спосіб, яким вони записуються.

Як ви бачите, новий MySQL має значення за замовчуванням, які практично готові до застосування в продакшені. Звичайно, кожен додаток відрізняється і має додаткові застосовувані ним хитрості і тюнинги.

MySQL Tuner

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)

Знову ж таки, важливо відзначити, що цей інструмент повинен запускатися приблизно раз в тиждень, так як сервер цей час працював і накопичував інформацію. Коли ви зрозуміли значення в конфігах і провели перезапуск сервер, він повинен спочатку опрацювати тиждень від цього часу. Гарна ідея створити cron-завдання, яка буде робити це за вас і періодично відправляти вам результати.

Переконайтеся, що ви провели перезапуск MySQL після будь-якої зміни конфігурації:

sudo service mysql restart

індекси

Далі, звернемо увагу на індекси - головна больова точка багатьох адмінів БД любителів! Особливо тих, хто відразу став використовувати ORM і ніколи не нюхав чистого SQL.

Примітка: терміни ключі та індекси можуть бути використані як взаємозамінні.

Ви можете порівняти індекси MySQL зі змістом в книзі, яка дозволяє вам легко знайти потрібну сторінку, яка містить інформацію, яку ви шукаєте. Якщо немає ніяких індексів, вам доведеться перегортати всю книгу в пошуку потрібної сторінки.

Як ви можете собі уявити, знайти по змісту набагато швидше, ніж перегортати кожну сторінку. Таким чином, додавання індексів в базу в цілому прискорює select-запити. Однак, цей індекс повинен бути створений і збережений. А значить, запити update і insert будуть повільніше і це займе трохи більше місця на диску. В цілому ви не помітите різниці при оновленнях і вставках, якщо ви проиндексировали вашу таблицю правильно, а тому важливо додавати індекси в потрібні місця.

Таблиці, які містять лише кілька рядків, насправді немає сенсу індексувати. Ви можете собі уявити, що перегорнути 5 сторінок - це не набагато повільніше, ніж спочатку сходити в зміст, отримати номер сторінки, а потім відкрити цю сторінку.

Так як же ми дізнаємося, які індекси потрібно додати, і які види індексів існують?

Унікальні / первинні індекси

Первинні індекси є основними індексами, які використовуються за замовчуванням при пошуку за даними. Для облікового запису користувача це можуть бути ідентифікатор користувача, або логін, або навіть основний email. Первинні індекси є унікальними. Унікальні індекси - це індекси, які не можуть мати повторів на всьому наборі даних.

Наприклад, якщо користувач вибрав конкретний username, ніхто більше не може вибрати його. Додавання унікального індексу на стовпець username вирішує цю проблему. MySQL буде "лаятися", якщо хто-то повторно спробує вставити рядок з ім'ям користувача (username), яке вже існує.

... ALTER TABLE `users` ADD UNIQUE INDEX` username` ( `username`); ...

Первинні ключі / індекси, як правило, задаються при створенні таблиці, а унікальні індекси пізніше через зміну (ALTER) таблиці.

І первинні ключі, і унікальні ключі можуть бути створені на один стовпець або на декілька стовпців відразу. Наприклад, якщо ви хочете переконатися, що тільки одне ім'я користувача використовується в рамках однієї країни, то можна створити унікальний індекс на обидва ці стовпці, наприклад, так:

... ALTER TABLE `users` ADD UNIQUE INDEX` usercountry` ( `username`,` country`), ...

Унікальні індекси ставляться на стовпці, до яких ви будете часто звертатися. Так що якщо обліковий запис користувача часто запитується, а у вас багато облікових записів користувачів в базі даних, це добра ознака для використання індексу.

регулярні індекси

Регулярні індекси полегшують пошук. Вони дуже корисні, коли вам необхідно швидко знайти дані за певним стовпцем або комбінації стовпців, але ці дані не обов'язково повинні бути унікальними.

... ALTER TABLE `users` ADD INDEX` usercountry` ( `username`,` country`), ...

Наведений вище приклад прискорює пошук по імені користувача в країні.

Індекси також допомагають збільшити швидкість угруповань та угруповань.

повнотекстові індекси

Повнотекстові індекси (FULLTEXT) використовуються для повнотекстового пошуку. Їх підтримують тільки InnoDB і MyISAM і тільки для стовпців з типами CHAR, VARCHAR і TEXT.

Ці індекси дуже корисні, якщо вам потрібен пошук по всьому тексту в колонці. Він спеціалізується на пошуку слів у тексті. Використовуйте його на постах, коментарях, описах, відгуках та ін., Якщо ваше додаток дозволяє шукати в них.

Зворотні індекси (по спадаючій)

Починаючи з версії 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)) ;

Застосовуйте зворотні індекси коли, наприклад, пишете логи в базу або, наприклад, для постів і коментарів, у яких в першу чергу підтягуються останні записи з БД і т. П ..

Допоміжні інструменти: Explain

Якщо ви дивитеся в сторону оптимізації запитів, інструмент EXPLAIN буде безцінний. Запит, перед яким ви просто поставили EXPLAIN, буде розглядатись не як запит, а як аналіз його виконання. MySql відобразить вам аналіз використовуваних індексів і покаже вам співвідношення влучень і промахів. А також скільки рядків він повинен пробігти / порівняти, щоб отримати результати, які ви шукаєте.

EXPLAIN SELECT City.Name FROM City JOIN Country ON (City.CountryCode = Country.Code) WHERE City.CountryCode = 'IND' AND Country.Continent = 'Asia'

Ви можете розширити звіт за допомогою EXTENDED:

EXPLAIN EXTENDED SELECT City.Name FROM City JOIN Country ON (City.CountryCode = Country.Code) WHERE City.CountryCode = 'IND' AND Country.Continent = 'Asia'

Детальніше ви можете дізнатися в документації .

Допоміжні інструменти: Percona Toolkit для виявлення дублюючих індексів

Раніше встановлений нами 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 Toolkit для невикористовуваних індексів

Percona Toolkit може також можливість виявити індекси. Якщо ви логіруете повільні запити (див. Розділ "вузькі місця" нижче), ви можете запустити утиліту і вона буде перевіряти, чи використовують ці запити індекси в таблицях і як саме.

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

Рядки вище повинні бути додані в конфігурацію mysql. БД буде відстежувати запити, які виконувалися більше ніж 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 S ELECT 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 # 10 0us ################################################# ############### # 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 літав.

Ми розібралися з оптимізацією конфігурації, ми прокачали за індексами, і ми позбулися деяких вузьких місць. Все це було в основному теорією, однак, все це може бути застосовано на реальних додатках.