ECommerce і не тільки Оптимізація MySQL для роботи з Magento
AddThis Smart Layers
Оптимізація MySQL для роботи з Magento. Ревізія 2
Час мчить з божевільною швидкістю. Здавалося б, буквально нещодавно я писав про оптимізацію налаштувань MySQL для серверів Magento, а виявляється минуло вже півтора роки з того часу.
Що ж змінилося з того часу і чому має сенс провести ревізію налаштувань? Насправді, той факт, що Magento дісталася версії 1.8.1.0 на момент написання цієї посади абсолютно не впливає на зміни в налаштуваннях MySQL.
Так, система стала ще стабільнішою, ще швидше, залишається все менше неоптимізованих запитів. Але разом з цим це та сама стара добра Magento першого покоління. І до тих пір, поки не вийде Magento 2.0, ми все також будемо оптимізувати сервер баз даних.
Крім того, якби справа була в самій Magento. На жаль, далеко не всі розробники сторонніх розширень можуть оптимізувати свої запити, використовувати індекси, тригери, тимчасові таблиці.
В результаті навіть досить потужний сервер може загнутися вже за десятки одночасних користувачів, якщо не оптимізувати налаштування MySQL.
Однією зі змін став вихід MySQL 5.6.x у якому, крім різних оптимізацій у самому ядрі, відбулися зміни в різних директивах налаштувань.
І другою суттєвою причиною став суттєвий прорив у технологіях віртуалізації серверів і, як результат, зниження їх вартості.
На сьогоднішній день абсолютно немає сенсу економити на ресурсах сервера, коли вартість віртуального сервера з 2-4 гігабайтами пам'яті та з 2-4 процесорами починається з $20 на місяць.
Таким чином, ми можемо повною мірою використовувати пам'ять сервера для зберігання і швидкого доступу до SQL запитів. Наявність кількохпроцесорів дозволить швидше обробляти запити, які були закешированы.
Отже, нижче йтиметься про оптимізацію сервера MySQL 5.6
Конфігурація віртуального сервера (VPS), що використовується: Кількість CPU: 2 Об'єм пам'яті RAM: 4Gb
Тепер спробуємо розібратися, що тут до чого і чому.
max_connectionsКількість максимальних коннектів за замовчуванням 151. Багато це чи мало? Це сильно залежить від трафіку на сервер, від швидкості обробки запитів та від оптимізації сайту. Якщо сайт добре оптимізований, використовуються різні технології кешування сторінок, кількість запитів до бази даних істотно скорочується. При цьому велику роль відіграє оптимізація запитів до бази даних. В ідеалі повинно бути запитів, час виконання яких займає більше 0.5 секунди. Якщо сервер досить потужний, більшість запитів закешовані, для зберігання даних використовується пам'ять RAM, кількість одночасних підключень буде помірною.
Наприклад візьму сайт одного з наших клієнтів. Середньодобова відвідуваність сайту складає близько 5000 відвідувачів. При цьому, кількість одночасних підключень до бази даних не перевищує 50.
Чи потрібно залишати значення max_connections за промовчанням? Так, тільки якщо є надлишок пам'яті RAM. Тому що деякі параметри, що використовуються в нашій конфігурації, не є глобальними і при запуску сервера будуть помножені на максимальну задану кількість підключень і зарезервовані. Іншими словами, не важливо яка відвідуваність буде у сайту і які інші процеси вимагатимуть додаткових ресурсів, левову частку пам'яті завжди займатиме сервер баз даних.
Тому має все ж таки сенс знизити значення max_connections до реального.
запорівняно з попередньою ревізією, у поточній версії налаштувань немає багато різних параметрів. При цьому додалися нові.
Наприклад, ми більше не змінюватимемо параметрkey_buffer, який до того ж у MySQL 5.6 тепер називаєтьсяkey_buffer_size. Тому що цей параметр актуальний тільки для таблиць MyISAM, яких Magento кіт наплакав. Крім того, значення за промовчанням для даного атрибута 8Mb, що більш ніж достатньо. Аналогічно надійдемо з параметромmyisam_sort_buffer_size.
thread_cache_sizeЦей параметр починаючи з версії MySQL 5.6.8 приймає значення -1 (autosized).
thread_concurrencyТакож неактуальний.
table_cacheБуло перейменовано наtable_open_cache. Значення цього параметра залежить від кількості баз даних і таблиць у яких.
table_definition_cacheБільше не потрібно вказувати, оскільки він починаючи з версії MySQL 5.6.8 приймає значення -1 (autosized).
open_files_limitТепер налаштовується автоматично.innodb_additional_mem_pool_sizeБуло скасовано починаючи з версії MySQL 5.6.3.
innodb_thread_concurrencyВсе також розраховується за формулою 2 * (у CPU) + (у дисків). Так як у порівнянні з попередньою ревізією кількість CPU у нас збільшилася, використовуємо значення 5.
max_connect_errorsПочинаючи з версії MySQL 5.6.6 значення за промовчанням для цього параметра 100. Більш ніж достатньо.
tmp_table_sizeОскільки зараз у нас є значно більше пам'яті, ми можемо собі дозволити зберігати набагато більше тимчасових таблиць у пам'яті. Значення цього параметра залежить від кількості баз даних і таблиць у яких.
max_heap_table_sizeВсе також повинен мати значення аналогічнеtmp_table_size.
innodb_log_file_sizeРозраховується за формулою 1/N від розміруinnodb_buffer_pool_size, де N значення параметраinnodb_log_files_in_group, який у свою чергу має за змоченням 2. Отже, якщо ми виділяємо дляinnodb_buffer_pool_size1024Mb пам'яті, то параметраinnodb_log_file_sizeможна виділити чверть від цього значення, тобто. 256Mb.
innodb_log_buffer_sizeДозволяє виконувати великі транзакції, у тому числі операції з великою кількістю рядків, без необхідності записувати лог у файл та використовувати файлову систему сервера. Однозначно має сенс використовувати швидку пам'ять RAM замість відносно повільної файлової системи.
innodb_sort_buffer_sizeЦей параметр з'явився лише з версії MySQL 5.6.4. Його значення за промовчанням 1Mb, спробуємо задати йому 2Mb.
Як можна бачити, чим більше пам'яті RAM є в наявності, тим ширші можливості для оптимізації сервера баз даних.
Так що євреї, не шкодуйте заварки не потрібно економити на дешевій пам'яті. Чим більше пам'яті, тим менше використовуватиметься повільна файлова система сервера, а відповідно сайт працюватиме в рази швидше.
Вочевидь, все значення кількості пам'яті тих чи інших параметрів залежить від розміру бази чи баз даних на сервері, інтенсивності використання даних, кількості таблиць у базі та інших змінних.
Немає і не може бути універсальних налаштувань, яких можна знайти в гугле в надлишку.
Для зручності тестування налаштувань дуже зручно користуватися такими інструментами: MySQLTuner MySQL Performance Tuning Primer MySQL Report
Основнимджерелом інформації з усіх параметрів налаштувань є офіційний сайт. Server System Variables InnoDB Startup Options and System Variables