MySQL, MariaDB, как и любой программа, имею тенденцию к ошибкам и поломкам. В этом руководстве попытаюсь создать инструкцию с чего начинать анализ проблем базы данных. В конце опишу ряд типичных ошибок и буду их дополнять.

Начинать нужно не с просмотра логов, как бы не странно это звучало. Нужно собрать сведения о рабочем окружении базы данных.

Если не будет сказано обратного, то статья тестировалась на CentOS 7.

Из листинга ниже, видно что на сервере установлен именно MySQL версии 5.6.51, а не MariaDB.

mysql --version
mysql  Ver 14.14 Distrib 5.6.51, for Linux (x86_64) using  EditLine wrapper

Теперь нужно узнать какими мощностями обладает сервер, как минимум количество процессоров, размер оперативной памяти и свободное место нужно проверить.

• Размер ОЗУ смотрим утилитой free. Нам интересны столбцы total - полный размер установленной ОЗУ (в нашем случае 1ГБ) и столбец available - сколько в настоящее время свободно оперативной памяти.

# free -h
              total        used        free      shared  buff/cache   available
Mem:           991M        537M         76M        207M        376M        109M

• Свободное место на жестком диске утилитой df:

• Количество ядер процессора вашего сервера, можно узнать несколькими способами, я использую пожалуй самый универсальный и посмотрю в файловой системе proc. Я не буду выводить полный листинг, а покажу только на что обратить внимание.

cat /proc/cpuinfo
или
lscpu

CPU(s):                1
Model name:            DO-Regular
CPU MHz:               2294.606
Hypervisor vendor:     KVM
Virtualization type:   full
L1d cache:             32K
L1i cache:             32K
L2 cache:              4096K

В нашем случае всего 1 ядро, процессор DO-Regular (это самый минимум).

Читайте также: Размер базы данных PostgreSQL

Предполагаем, что все файлы баз данных хранятся физически в одном месте (по умолчанию это директория /var/lib/mysql). Стандартно используем утилиту du. Вывести полный размер директории:

# du -hsx /var/lib/mysql
208M    /var/lib/mysql

Вывести полный размер директории для каждой базы данных. Учтите, что файлы находящие непосредственно в директории /var/lib/mysql показаны не будут, а в примере ниже как раз они и занимают больше всего места. А это очень интересные файлы: ibdata1, ib_logfile0,ib_logfile1, но о них поговорим ниже.

# du -h /var/lib/mysql
2.0M    /var/lib/mysql/mysql
636K    /var/lib/mysql/performance_schema
13M     /var/lib/mysql/task41topadm
2.8M    /var/lib/mysql/task42topadm
2.7M    /var/lib/mysql/task43topadm
1.6M    /var/lib/mysql/postfix
1.9M    /var/lib/mysql/task3
12M     /var/lib/mysql/task42wordpress
208M    /var/lib/mysql

Узнаем размер базы данных (в мегабайтах) с помощью SQL запроса. Запрос выведет точный размер базы MySQL для каждой из существующих баз, теперь вы можете ориентироваться что и сколько занимает

(поменяйте название БД на свое в строке - "WHERE table_schema = "dbname""):

SELECT table_schema AS "Name DB",
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size in MB"
FROM information_schema.TABLES
GROUP BY table_schema;

Если какая -то из БД вам кажется большой, можно посмотреть для конкретной базы данных размер ее таблиц. Используя SQL запрос ниже не забудьте заменить, в параметре table_schema = "nameDB", nameDB на имя вашей БД.

SELECT TABLE_NAME AS "Name table",  ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size in MB" FROM information_schema.TABLES WHERE table_schema = "nameDB" ORDER BY (data_length + index_length) DESC;

Зачастую приблуды типо phpmyadmin или нет времени использовать или нет возможности. Значит все сведения нужно получить из консоли Mysql.

Возможно понадобится узнать движки, которые поддерживает установленная версия MySQL. Для этого используем SQL запрос: SHOW ENGINES; Обратите внимание на столбец Support, если указано значение "NO" тип хранения данных не поддерживается (не включен).

Самый простой способ узнать тип движка у таблицы — это вызвать SHOW CREATE TABLE <имя_таблицы>

SHOW CREATE TABLE wp_posts;
...
ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
...

• my.cnf примеры конфигурации MySQL, MariaDB

• Подробноо временном включении slow query log

Для обнаружения неэффективных запросов к базе, следует проверить лог медленных запросов. В случае оптимизации запросов этот лог поможет выяснить, что необходимо оптимизировать в первую очередь. Настройки в my.cnf раздел mysqld

[mysqld]
log_output = file
slow_query_log = ON
long_query_time = 0
log_slow_admin_statements = ON
log_slow_slave_statements = ON
slow_query_log_file = /var/log/mariadb/slow-queries.log

• Местоположение лога: log_slow_queries = /var/log/mysql/mysql_slow.log
• Со скольки секунд выполнения запрос считается медленным, минимальное значений — 1 секунда, по умолчанию 10 секунд: long_query_time = 10
• Если надо логировать запросы, которые не используют индексы, надо добавить строку: log-queries-not-using-indexes
• Если надо вести лог медленных команд, таких как OPTIMIZE TABLE, ANALYZE TABLE и ALTER TABLE: log-slow-admin-statements

• Мониторинг производительности MySQL при помощи MyTop
• mysqladmin

Загрузка mysql, которую вы видите в "top", представляет собой общую загрузку всех потоков mysql на всех ядрах. Если у вас есть система с 24 ядрами, вы можете приблизиться к нагрузке одного ядра, разделив 200%/24, что дает нагрузку ~ 8%, что неплохо. Если вы хотите иметь подробное представление о том, что происходит, используйте "htop" вместо "top", а в его настройках включите "древовидное представление" (settings- > display options- > tree view). Вы увидите все потоки mysql с их загрузкой и общую нагрузку (теперь вы видите) в корневом дереве.

Говорят! MySQL полностью многопоточен и будет использовать несколько процессоров, при условии, что операционная система поддерживает их, это также увеличит системные ресурсы при правильной настройке производительности.

Но настройка этой так называемой многопоточности разница от версии к версии:

Типичным параметром в my.ini, влияющим на производительность потока, является:

thread_cache_size = 8

Thread_cache_size может быть увеличен для улучшения производительность, если у вас много новых подключений. Обычно это не обеспечивает заметного повышения производительности, если у вас хорошая реализация потоков. Однако, если ваш сервер видит сотни подключений в секунду, вам обычно следует установить значение thread_cache_size достаточно высоким, чтобы большинство новых подключений использовали кэшированные потоки

Если вы используете Solaris, то вы можете использовать

thread_concurrency = 8

Thread_concurrency позволяет приложениям давать системе потоков подсказку о желаемом количестве потоков, которые должны выполняться одновременно.

Эта переменная устарела с MySQL 5.6.1 и удалена в MySQL 5.7. Вы должны удалять ее из файлов конфигурации MySQL всякий раз, когда вы ее видите, если они не предназначены для Solaris 8 или более ранних версий.

Если же нужно посмотреть информацию в более удобном виде и узнать не только ENGINE, то можно выполнить такой SQL-запрос:

SELECT TABLE_NAME,ENGINE,ROW_FORMAT,TABLE_ROWS,DATA_LENGTH,INDEX_LENGTH FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'mysql' ORDER BY ENGINE ASC;

Колонка ENGINE — это тип движка у таблицы; Колонка ROW_FORMAT — это формат строк таблицы; Колонка TABLE_ROWS — это количества записей в таблице; Колонка DATA_LENGTH — это размер данных в таблице в байтах; Колонка INDEX_LENGTH — это размер индекса в таблице в байтах;

InnoDB — основной движок для MySQL, который с версии 5.5 стал дефолтным. Поддерживает транзакции, репликацию, построчную блокировку. В отличие от таблиц MyISAM, где для каждой таблицы создается один файл данных, данные InnoDB в настройках по умолчанию хранятся в больших совместно используемых файлах. То есть данные для всех таблиц и всех баз данных хранятся в одном файле, изменить это можно с помощью настроек опции innodb_file_per_table (Как включить MySQL innodb_file_per_table?). http://michael-xiii.blogspot.com/2011/10/mysql-innodbfilepertable.html

Чтобы уменьшить размер своей базы данных с движком InnoDB, необходимо воспользоваться mysqldump, чтобы сделать дамп всех своих таблиц, создать новую базу данных и импортировать таблицы в новую базу данных.

Источник https://runebook.dev/ru/docs/mariadb/innodb-persistent-statistics/index

MariaDB начиная с 10.0.4 Постоянная статистика для InnoDB была введена в MariaDB 10.0.4 .

До MariaDB 10.0 статистика InnoDB не хранилась на диске, а это означало, что при перезапуске сервера статистику необходимо было пересчитывать, что является как ненужным вычислением, так и приводило к несогласованным планам запросов.

Существует ряд переменных,контролирующих постоянную статистику:

innodb_stats_persistent - если установлено (по умолчанию), включает постоянную статистику InnoDB. innodb_stats_auto_recalc - если установлено (по умолчанию), постоянная статистика автоматически пересчитывается при значительном изменении таблицы (более 10% строк) innodb_stats_persistent_sample_pages - Количество страниц индекса, отобранных (по умолчанию 20) при оценке количества элементов и статистики для индексированных столбцов. Увеличение этого значения повысит точность статистики индекса, но потребует больше ресурсов ввода-вывода при запуске ANALYZE TABLE . Эти настройки могут быть перезаписаны для каждой таблицы с помощью предложений STATS_PERSISTENT , STATS_AUTO_RECALC и STATS_SAMPLE_PAGES в операторе CREATE TABLE или ALTER TABLE .

Данные, относящиеся к таблицам формата InnoDB, могут храниться в одном файле ibdata1 или в раздельных файлах формата .ibd. Исторически хранение в одном файле является основным методом.

Ранее MySQL по умолчанию все таблички innodb хранил в одном файле - когда их накапливается приличное количество - файл значительно разрастается. Плюс не забывайте, что при удалении данных в innodb - размер файла не уменьшается - он растет только в большую сторону. Так что если данных в базе у вас много или идет активное удаление - рано или поздно вы задумаетесь о том, чтобы выполнить подобное разделение

Как уменьшить размер InnoDB

Данные, загруженные в таблицы MySQL, остаются в файле ibdata1 и не удаляются даже после того как удалены сами таблицы. Размер файла при этом может значительно увеличиваться. Чтобы этого избежать в конфигурацию сервера баз данных добавляют директиву innodb_file_per_table, благодаря ей данные вновь создаваемых таблиц сохраняются в отдельные файлы.

Значения параметра, используемые по умолчанию для разных версий пакетов:

• MySQL версии до 5.6.6 и MariaDB 5.5 — innodb_file_per_table = 0 (данные хранятся в одном файле ibdata1)
• MySQL 5.6.6 и старше + MariaDB 10.x — innodb_file_per_table = 1 (данные хранятся в разных файлах с расширением .ibd)

Аналогичная ошибки, если вы в логах видите Can't open file: '*.MYI'. (errno: 145) или Fix: MySQL table is marked as crashed and last repair failed То лечится это одинаково, что MYSQL, что в MariaDB <code> 100125 9:33:30 /usr/local/libexec/mysqld: Can't open file: '*.MYI'. (errno: 145) ERROR: 1016 Can't open file: '***.MYI'. (errno: 145) </code>

Table 'your table' is marked as crashed and last (automatic?) repair failed

Предварительно остановите Базу данных, например так

service mysql stop

• Решение: запуск утилиты myisamchk

myisamchk --silent --force */*.MYI

• Восстановить сразу во всех базах

myisamchk -r /var/lib/mysql/*/*.MYI

Also, run the following SQL query using phpMyAdmin:

REPAIR TABLE TableName

Если такая проблема возникает, необходимо выяснить, заключается она в сервере или в клиенте. Обратите внимание, что многие сбои сервера вызваны повреждёнными файлами данных или индексными файлами.

Чтобы узнать время безотказной работы сервера, запустите команду mysqladmin.

mysqladmin version -uroot -p

Server version          5.6.51
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 1 hour 4 min 51 sec

Threads: 1  Questions: 59  Slow queries: 0  Opens: 102  Flush tables: 1  Open tables: 95  Queries per second avg: 0.015

Кроме того, можно остановить сервер, сделать отладку MySQL и снова запустить службу. Для отображения статистики процессов MySQL во время выполнения других процессов откройте окно командной строки и введите следующее:

$ sudo mysqladmin -i 5 status

Или

$ sudo mysqladmin -i 5 -r status

100125  9:49:57 Error reading packet from server: Client requested master to start replication from impossible position (server_errno=1236)
100125  9:49:57 Got fatal error 1236: 'Client requested master to start replication from impossible position' from master when reading data from binary log
100125  9:49:57 Slave I/O thread exiting, read up to log 'broadband-bin.3239', position 61452688

• Решение:

Остановить репликацию. Сделать mysqldump на мастере, перед этим SHOW MASTER STATUS\G; Отметить у себя текущий File (binlog.000xx) и позицию Position. Дамп залить на slave, там же сделать CHANGE MASTER TO MASTER_LOG_FILE="записанный binlog.000xx", MASTER_LOG_POS="записанная позиция"; Запустить репликацию

Ошибка "Access denied for user 'debian-sys-maint'@'localhost'" в Операционная система Debian может появляться в нескольких случаях, например

• при повреждении системной БД mysql и установки её заново
• при переносе MySQL из другой операционной системы

Пользователь debian-sys-maint служит например для проверки таблиц при запуске сервера MySQL.

Для исправления ошибки, запоминаем пароль(строка password) из файла debian.cnf. И через клиента mysql и выполняем две SQL команды

# nano /etc/mysql/debian.cnf
# mysql -u root -p
mysql> GRANT RELOAD, SHUTDOWN, PROCESS, SHOW DATABASES, SUPER, LOCK TABLES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY 'пишем пароль из debian.cnf';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY 'пишем пароль из debian.cnf' WITH GRANT OPTION; 

Существует несколько видов журналов (logs) сервера MySQL:

1. бинарный лог (binary log)
2. лог ошибок (error log)
3. лог медленный запросов (slow query log)
4. лог запросов (general query log)
5. лог репликаций (relay log)

Для включения подробного логгирования запросов ко всем базам Движок БД MySQL, нужно в файле my.cnf в секции [mysqld] нужно прописать строки:

general_log_file        = /var/log/mysql/mysql.log
general_log             = 1

Создать и присвоить права этому файлу, перегрузить сервер MySQL:

touch /var/log/mysql/mysql.log
chown mysql:mysql /var/log/mysql/mysql.log
/etc/init.d/mysql restart

Самое важное при диагностике — понять, что именно вызвало ошибку. Следующие шаги помогут вам в этом:

• Cамый важный шаг — просмотреть журналы MySQL, которые хранятся в каталоге /var/log/mysql/. Вы можете использовать утилиты командной строки вроде tail для чтения файлов журнала.
• Если служба MySQL не запускается, проверьте её состояние с помощью systemctl. Или используйте команду journalctl (с флагом -xe) в systemd.
• Вы также можете проверить файл системного журнала (например, /var/log/messages) на предмет обнаружения ошибок.
• Попробуйте использовать такие инструменты, как mytop, glances, top, ps или htop, чтобы проверить, какая программа использует весь ресурс процессора или блокирует машину. Они также помогут определить нехватку памяти, дискового пространства, файловых дескрипторов или какого-либо другого важного ресурса.
• Если проблема в каком-либо процессе, можно попытаться его принудительно остановить, а затем запустить (при необходимости).
• Если вы уверены, что проблемы именно на стороне сервера, можете выполнить команды: mysqladmin -u root ping или mysqladmin -u root processlist, чтобы получить от него ответ.
• Если при подключении проблема не связана с сервером, проверьте, нормально ли работает клиент. Попробуйте получить какие-либо его выходные данные для устранения неполадок.