MySQL. Збільшення продуктивності. Частина 1 - Кешування даних
Моніторинг продуктивності СУБД — досить об'ємне завдання і орієнтуватися серед сотень індикаторів для системного адміністратора-початківця дуже складно. Основне завдання - визначення порогових значень, при перевищенні яких потрібно робити активні дії. У цій статті я намагатимусь пролити світло на питання моніторингу та аналізу продуктивності кешування даних та індексів, а також дати рекомендації щодо подальших дій.
Отримати інформацію про поточний стан змінних, що відображають показники продуктивності, можна за допомогою команди SHOW STATUS. Її повний синтаксис виглядає так: SHOW [GLOBAL SESSION] STATUS [LIKE ‘pattern’ WHERE expr]1.
Перейдемо до детальних описів показників продуктивності.
MyISAM 2
Аналіз показників, які будуть розглянуті нижче, є актуальним лише в тому випадку, якщо ви використовуєте MyISAM як низькорівневу підсистему зберігання даних. До версії MySQL 5.5 MyISAM використовувалася за умовчанням.
Key_reads - кількість фізичних операцій читання блоків ключів з диска;
Key_read_requests - Операції читання блоків ключів з кешу в оперативній пам'яті. На більшості ресурсів оптимізації продуктивності MySQL рекомендують обов'язково відстежувати ці параметри разом. Важливе ставлення Key_reads до Key_read_requests; якщо воно більше 99%, то все гаразд, якщо менше 95%, то варто проаналізувати проблеми у продуктивності, можливо провести апгрейд обладнання або збільшити розмір кешу.
Формула виглядає так:Ratio(%)=(1-Key_reads/Key_read_requests)*100
В ідеалі обсягу виділеної для кеша пам'яті має бути достатньо, щоб розмістити у ній усі індекси3.
Key_writes - кількість фізичних записів даних на диск;
Key_write_requests - кількість звернень на запис даних у кеш.
Аналізувати останні два параметри рекомендується також як ставлення одного до іншого. В ідеалі значення має бути менше 90%.
Ratio(%)=Key_writes/Key_write_requests*100
key_buffer_size - змінна, що управляє поточним розміром кешу. Зрозуміти який обсяг необхідно виставляти цією змінною можна із сумарного обсягу всіх файлів з розширенням .MYI, які знаходяться в директорії баз даних. Як це зробити стосовно певних операційних систем у цій статті я розглядати не планую (при всій кількості інформації в мережі5 ) Також я не зачіпатиму питання маніпулювання розміром кешу за допомогою теплих і гарячих черг, що особливо актуально для систем з 32-х розрядною архітектурою, оскільки використання в робочому середовищі 32-х розрядних систем вже само по собі є проблемою, а для 64-х розрядних систем такої проблеми поки не повинно бути.
InnoDB 6
Починаючи з версії MySQL 5.6 двигуном за умовчанням став InnoDB, параметри моніторингу якого будуть розглянуті нижче.
Innodb_buffer_pool_reads - кількість операцій читання з диска, але стосовно іншого двигуна СУБД - InnoDB;
Innodb_buffer_pool_read_requests — кількість звернень до кешу, також у контексті InnoDB.
У комплексі параметри мають ті ж обмеження та формулу обчислення відношення (дізнатися зведену інформацію можна також командою "SHOW ENGINE INNODB STATUS output")7 : вище 99% - норма, нижче 95% - є проблеми у продуктивності.
Ratio(%)=(1-Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests)*100
Для аналізувикористання кешу також не зайвим буде звернутися до таких параметрів:
Innodb_buffer_pool_pages_dirty - відображає кількість "брудних" сторінок - тобто які були змінені, перебуваючи в кеші, але ці зміни ще не зафіксовані на диску;
Innodb_buffer_pool_pages_data — відображає суму чистих та брудних сторінок у кеші, а також індексних сторінок;
Innodb_buffer_pool_pages_flushed — кількість запитів на скидання сторінок з кешу на жорсткий диск;
Innodb_buffer_pool_pages_free - кількість вільних сторінок;
Innodb_buffer_pool_pages_misc - сторінки, що використовуються самої СУБД для різних потреб;
Innodb_buffer_pool_pages_total — загальна кількість сторінок у пам'яті, утворюється підсумовуванням параметрів pages_data, pages_free, pages_misc8.
З п'яти параметрів особливу увагу варто приділити кількості вільних сторінок (Innodb_buffer_pool_pages_free), т.к. воно може сказати про надлишок або нестачу розміру кешу9. Однак якщо аналізувати цей параметр один, то нічого виразного ми не дізнаємося, оскільки він представляється в абсолютному значенні. Є сенс пов'язати цей параметр із загальною кількістю сторінок у пам'яті та обчислити його відсоток. Знаючи, що обсяг сторінок під потреби СУБД (pages_misc) зазвичай не перевищує 10%10, а кількість вільних сторінок (pages_free) має бути мінімальною, можна приблизно визначити межу, по перевищенні якої варто задуматися про ефективність використання пам'яті. Це значення буде приблизно 10-15%, при перевищенні 20-25% рекомендується знижувати обсяг кешу, оскільки кількість вільних сторінок стає значною.
Rate(%)=(1-Innodb_buffer_pool_pages_data/Innodb_buffer_pool_pages_total) *100%
Замислитися про збільшення кешу потрібно при падінні значення вільних сторінок нижче 5% (це виключно моя особиста думка), або відстежувати абсолютне значення і якщо воно дорівнює 0, це говорить про сильний брак кешу. Формула виглядатиме приблизно так:
Rate(%)=Innodb_buffer_pool_pages_free/Innodb_buffer_pool_pages_total*100%
innodb_buffer_pool_size — відображає розмір пам'яті, яка виділена для розміщення даних та індексів. Ідеєю чим це значення більше, тим краще. Якщо йдеться виключно про сервери баз даних без будь-яких інших ролей, то рекомендують виставляти його приблизно в 70-80% від загального обсягу оперативної пам'яті, але існують інші думки11. На серверах, що поєднують ролі, будьте уважні, треба точно розуміти скільки оперативної пам'яті необхідно іншим додаткам. До того ж на серверах з 32-бітною архітектурою є деякі обмеження12. Розрахувати необхідний вам розмір кешу InnoDB не є складним завданням, в мережі є безліч готових прикладів13. Розмір кешу обчислюється як добуток загальної кількості сторінок на розмір однієї сторінки (Innodb_buffer_pool_pages_total * Innodb_page_size).
Інші параметри нас не цікавлять (наприклад, параметр innodb_additional_mem_pool_size взагалі за умовчанням вимкнено14 ). На цьому огляд збільшення продуктивності з допомогою зміни параметрів кешування даних завершено.