Оптимізуємо сервер MySQL змінюємо значення змінних

Живий код - створити та розкрутити сайт

Найбільш читане

Як розкрутити форум 60
Про сайт 25
Вибираємо найкращий форум для сайтів Joomla 21
Професійний хостинг за доступними цінами 18
Перевірені засоби захисту PHP 16

Корисні ресурси

Напишу скрипт безкоштовно

Сортування масивів за зростанням та зменшенням у PHP

Зірки блогу ТОП 3

Оптимізуємо сервер MySQL: змінюємо значення змінних

Отже, ви купили виділений чи віртуальний сервер (VPS). Розмістили на ньому один або багато своїх сайтів. І потекли гості. І, здається, все добре. Відвідуваність зростає, заробіток зростає. Однак що це – зі збільшенням відвідуваності сайти починають відкриватися все повільніше та повільніше. Все частіше відвідувачі скаржаться, що сайт не відкривається. І треба щось робити.

Матеріал цієї статті буде актуальним для власників віртуального або виділеного сервера. Оскільки вони зможуть змінити налаштування встановленого на хостингу MySQL-сервера. На більш дешевих варіантах хостингу ніхто не дозволить вам змінювати налаштування свята веб-сервера.

Відкриваємо панель керування сайтом

Що ж, час – гроші. А особливо коли веб-сервер працює абияк, і ваші відвідувачі просто в люті. Тому одразу перейдемо до практики.

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

Після того, як панель керування запросить пароль для логінуroot, і ви його успішно введете, ви опинитеся у свята-святих вашого веб-сервера. У нашому випадку (див. малюнок) використовується панель керування cPanel. У розділі SQL Services панелі керування виберіть посилання phpMyAdmin.

Панель phpMyAdmin: шукаємо проблемні ділянки

Отже, панель phpMyAdmin ми відкрили (переконайтеся, що ви відкрили її від імені адміністратора сервера, а не від імені одного із встановлених на сервері сайтів, інакше ви не зможете змінювати глобальні змінні). Тепер переходимо до розділу Стан, і починаємо його уважно вивчати.

У цьому розділі відображаються статистичні відомості про роботу MySQL-сервера. Насамперед подивіться на значення наступних лічильників:

- MySQL сервер працює … - кількість часу з моменту останнього перезапуску MySQL сервера. Як правило, MySQL-сервер, що добре працює, ніколи не перезапускається. Тому якщо значення даного лічильника не викликає у вас поваги, значить на сервері дійсно є проблеми, через які сервер MySQL часто доводиться автоматично перезапускати;

- таблиця З'єднання рядок Максимально одночасних – показує максимальну кількість одночасних з'єднань з базою даних MySQL, яка була зареєстрована з моменту останнього перезапуску веб-сервера; значення даного лічильника має бути далеко до максимально можливої кількості одночасних з'єднань (далі ми навчимося змінювати це налаштування), адже якщо максимальна кількість одночасних з'єднань буде перевищена, нові відвідувачі не зможуть зайти на сайт (для них буде відображено повідомлення too many connections);

- таблиця З'єднання рядок Невдалих спроб – показує відсоток всіх з'єднань із базою даних, які встановити зірвалася; чим менше це значення, тимкраще;

- таблиця З'єднання рядок Перервано – показує відсоток всіх з'єднань з базою даних, які вдалося довести остаточно; що менше це значення, то краще.

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

Тепер переходимо до змінних (у розділі Стан, нижче). Нас цікавлять значення, виділені червоним кольором. Даним кольором панель phpMyAdmin сигналізує про проблеми роботи MySQL-сервера.

Наступний крок після того, як проблемну змінну знайдено – читання опису, який наводиться для цієї змінної. Наприклад, змінна Created_tmp_disk_tables. На сервері значення цієї змінної – 268 k, що дуже багато. Читаємо опис: «Кількість тимчасових таблиць, автоматично створених сервером на диску, під час виконання SQL-виразів. Якщо значення Created_tmp_disk_tables велике, слід збільшити значення змінної tmp_table_size, щоб тимчасові таблиці розташовувалися у пам'яті, а чи не на жорсткому диске.». Що ж із цього випливає?

Панель phpMyAdmin: змінюємо значення глобальних змінних

Налаштування сервера MySQL найчастіше полягає у зміні глобальних змінних. Робиться це за допомогою SQL-запитів. Однак, перш ніж змінити значення змінної, слід подивитися, чому значення цієї змінної дорівнює зараз.

У панелі phpMyAdmin для цього достатньо перейти до розділу Змінні, після чого знайти потрібну змінну. Зверніть увагу, що назва змінної в панелі phpMyAdmin пишеться без знаків підкреслення (див. малюнок). Замість знаківпідкреслення використовуються пробіли.

Після того, як значення змінної знайдено, перейдіть до розділу SQL панелі phpMyAdmin, і змініть значення даної змінної за допомогою SQL-запиту виду SET GLOBAL змінна = значення.

Наприклад, у розділі Стан червоним кольором виділено значення змінної Created_tmp_disk_tables. І в описі цієї змінної було зазначено, що потрібно збільшити значення змінної tmp_table_size. Значить:

- у розділі Змінні знайдіть змінну tmp table size, та запам'ятайте її значення; у нас значення цієї змінної дорівнювало 33,554,432, тобто, для тимчасових таблиць виділялося 32 Мбайта;

- у розділі SQL вводимо команду SET GLOBAL tmp_table_size = 48554432. Тобто збільшуємо буфер приблизно до 48 Мбайт (розмір всіх буферів вказується в байтах).

Найчастіші проблемні ділянки

Загальний алгоритм оптимізації MySQL сервера ми розглянули. Тепер давайте коротко розглянемо, які змінні слід змінювати за наявності тих чи інших «червоних значень».

Table_locks_waited. Вказує на проблеми із SQL-запитами, які здійснюють ваші сайти. Щоб вирішити ці проблеми, слід оптимізувати SQL запити.

Slow_queries. Кількість запитів, що виконувались дуже повільно. Для вирішення цієї проблеми слід увімкнути ведення лога повільних запитів, після чого оптимізувати всі SQL-запити, які будуть занесені до лог-файлу. Ці дії у цій статті не розглядатимуться.

Created_tmp_disk_tables. Збільште значення змінної tmp_table_size (розмір буфера в байтах). Фактично значення даного буфера визначає максимальний розмір тимчасової таблиці, яка зберігатиметься в оперативній пам'яті. Тобто якщо тимчасова таблиця має більший розмір, ніжtmp_table_size, вона розташовуватиметься на жорсткому диску.

Sort_merge_passes. Збільште значення змінної sort_buffer_size (буфер для сортування результатів запиту).

Opened_tables. Збільште значення змінної table_cache. У цьому буфері зберігаються дескриптори всіх відкритих сервером MySQL таблиць.

Threads_created. Збільште значення змінної thread_cache_size.

Key_reads. Збільште значення змінної key_buffer_size. Цей буфер є спільним для всіх працюючих сайтів. Рекомендується встановлювати розмір буфера, що дорівнює 30-35% від загальної кількості оперативної пам'яті на сервері.

Максимально одночасних. І остання порада. У розділі Змінні панелі phpMyAdmin перегляньте значення змінної max connections. Якщо поточна кількість одночасних з'єднань з сервером MySQL (таблиця З'єднання рядок Максимально одночасних) наближається до максимально можливого, слід збільшити значення цієї змінної. Або подумати над купівлею окремого хостингу для частини сайтів, що працюють на даному хостингу.

Редагування файлу my.cnf

Зміна змінних за допомогою SQL-запиту SET GLOBAL має один суттєвий недолік – після перезапуску MySQL-сервера всі зроблені вами зміни будуть втрачені. Тому замість введення SQL-команд правильніше відредагувати вміст файлу my.cnf, який знаходиться у каталозі /etc.

Достатньо знайти в цьому текстовому файлі розділ [mysqld], або ввести в ньому потрібні змінні та їх значення (у форматі змінна = значення), або відредагувати значення вже існуючих змінних.

Що робити далі

Після того, як ви змінили значення потрібних змінних, знову зайдіть у розділ Стан і клацніть на посилання Скинути (перезапустити серверMySQL ми не можемо, адже після перезапуску всі зроблені нами зміни будуть втрачені). Так ви обнулите всі лічильники сервера. І нам залишиться лише почекати день-два, щоб подивитись:

- чи знизився відсоток невдалих та перерваних з'єднань;

- чи зникли "червоні" значення змінних.

Якщо "червоні" значення змінних все ще є, слід знову відкоригувати значення відповідних глобальних змінних. Адже оптимізація роботи веб-сервера – це нескінченний процес.