Назад | Перейти на главную страницу

Настройка MySQL базы данных 1,3 ГБ, оперативной памяти 8 ГБ, ожидания большого количества операций ввода-вывода

У меня есть база данных MySQL размером 1,3 ГБ на 8-ядерной виртуальной машине, 8 ГБ памяти, и у меня возникают проблемы с оптимизацией конфигурации. Есть много маленьких рядов и много соединений.

Я нашел об этом инструменте: https://tools.percona.com/wizard, но предложенные различия только ухудшают результат. В первую очередь они предлагают увеличить буферный пул InnoDB до 6 ГБ. В настоящее время у меня буферный пул размером 1 ГБ, но когда я пытаюсь его увеличить, производительность становится хуже. Почему это происходит?

Есть ли способ заставить MySQL кэшировать всю БД в памяти, чтобы она не зависала на таких высоких уровнях ожидания ввода-вывода?

Остальные настройки вроде бы не имеют такого значения.

Текущее состояние my.cnf: https://gist.github.com/knyttl/ac6efe5c0730dd34a5cc

Глядя на свой my.cnf, у вас есть настройки по умолчанию для многих вещей. Что вам отчаянно нужно, так это полная очистка инфраструктуры InnoDB. Щелкните здесь, чтобы увидеть графическое представление инфраструктуры InnoDB.

ШАГ 01) Запустите этот запрос

SELECT CEILING(Total_InnoDB_Bytes/POWER(1024,2)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;

Это скажет вам, какой размер установить innodb_buffer_pool_size (в МБ).

Допустим, ответ пришел 1024.

ШАГ 02) Запустите этот запрос

SELECT CEILING(Total_MYI_Bytes/POWER(1024,2)) RKBS FROM
(SELECT SUM(index_length) Total_MYI_Bytes FROM information_schema.tables
WHERE engine='MyISAM' AND table_schema NOT IN
('information_schema','performance_schema','mysql')) A;

Это скажет вам, какой размер установить key_buffer_size (в МБ).

Допустим, этот ответ пришел 64.

ШАГ 03: Запустите этот запрос

SET GLOBAL innodb_fast_shutdown = 0;

Это выполнит полную транзакционную фиксацию любых транзакций, оставшихся в журналах транзакций во время выключения. Журналы транзакций

  • /var/lib/mysql/ib_logfile0
  • /var/lib/mysql/ib_logfile1

ШАГ 04: Выгрузите все данные в текстовый файл

cd /root
mkdir datadump
cd datadump
MYSQLDUMP_CONNECT="-uroot -ppassword"
MYSQLDUMP_OPTIONS="--single-transaction --routines --triggers"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --flush-privileges --all-databases"
mysqldump ${MYSQLDUMP_CONNECT} ${MYSQLDUMP_OPTIONS} > MySQLData.sql

ШАГ 05: Завершение работы MySQL

service mysql stop

ШАГ 06: Добавьте следующее в my.cnf

Если вы используете MySQL 5.5 (или MySQL 5.1 с включенным подключаемым модулем InnoDB), добавьте эти

[mysqld]
key_buffer_size=64M
innodb_file_per_table
innodb_open_files=1000
innodb_buffer_pool_size=1024M
innodb_log_file_size=256M
innodb_fast_shutdown=0
innodb_read_io_threads=16
innodb_write_io_threads=16
join_buffer_size=4M
read_buffer_size=4M
read_rndbuffer_size=4M

Если вы используете MySQL 5.0, добавьте эти

[mysqld]
key_buffer_size=64M
innodb_file_per_table
innodb_open_files=1000
innodb_buffer_pool_size=1024M
innodb_log_file_size=256M
innodb_fast_shutdown=0
innodb_thread_concurrency=0
join_buffer_size=4M
read_buffer_size=4M
read_rndbuffer_size=4M

Обратите внимание, я выбрал innodb_log_file_size до 25% innodb_buffer_pool_size

ШАГ 07: освободите место для новых файлов InnoDB

cd /var/lib/mysql
mv ib_logfile0 ib_logfile0.bak
mv ib_logfile1 ib_logfile`.bak
mv ibdata1 ib_data1.bak

ШАГ 08: Запустите MySQL

service mysql start

ШАГ 09: Загрузите данные обратно

mysql -uroot -p < /root/datadump/MySQLData.sql

ШАГ 10: Если все работает лучше, запустите это:

rm -f ib_logfile0.bak
rm -f ib_logfile1.bak
rm -f ibdat1.bak

Все сделано с помощью InnoDB Cleanup.

Я уже много раз предлагал такие улучшения InnoDB.

Попробуйте !!!