Я пытаюсь настроить базу данных MySQL с помощью mysqltuner. Mysqltuner советует мне увеличить join_buffer_size и query_cache_size. Однако в то же время он предупреждает, что мой максимальный объем использования памяти высок, а именно 200%+ от установленной оперативной памяти (что составляет 2 ГБ). Конечно, я в затруднении: если я сделаю то, что говорит mysqltuner, объем использования памяти еще больше возрастет. Так что же мне делать? Проблема не в mysql, а в приложениях, работающих на этом сервере, которые, очевидно, требуют от mysql выполнения огромного объема кэширования? Как бы вы, эксперты по администрированию mysql, поступили дальше? Посмотрите отчет mysqltuner ниже вместе с моими текущими настройками [mysqld]:
Отчет MySqlTuner:
MySQLTuner 1.2.0 — основные отчеты об ошибках Hayden, запросы на функции и загрузки наhttp://mysqltuner.com/ Запустите с параметром '--help' для получения дополнительных параметров и фильтрации вывода.
-------- Общая статистика --------------------------------------------------
[--] Пропущена проверка версии для скрипта MySQLTuner
[OK] В настоящее время запущена поддерживаемая версия MySQL 5.5.30-log
[OK] Работает на 64-битной архитектуре-------- Статистика Storage Engine -------------------------------------------
[--] Статус: +Архив -BDB -Федеративный +InnoDB -ISAM -NDBCluster
[--] Данные в таблицах MyISAM: 310M (таблицы: 264)
[--] Данные в таблицах InnoDB: 8M (таблицы: 365)
[--] Данные в таблицах PERFORMANCE_SCHEMA: 0B (таблицы: 17) [!!] Всего фрагментированных таблиц: 376-------- Рекомендации по безопасности --------------------------------------------
[OK] Всем пользователям базы данных назначены пароли-------- Показатели производительности -------------------------------------------------
[--] Работает: 20 ч 20 мин 10 с (2 млн q [35,305 qps], 25 тыс. conn, TX: 88B, RX: 2B)
[--] Чтения / записи: 22% / 78%
[--] Всего буферов: 480,0 млн глобальных + 33,5 млн на поток (макс. 110 потоков)
[!!] Максимально возможное использование памяти: 4,1 ГБ (203% установленной оперативной памяти)
[OK] Медленные запросы: 0% (0/2 млн)
[OK] Максимальное использование доступных подключений: 7% (8/110)
[OK] Размер буфера ключа / общее количество индексов MyISAM: 150,0 млн / 129,5 млн
[OK] Частота попаданий в буфер ключа: 100,0% (15 млн кэшированных / 7 тыс. чтений)
[OK] Кэш запросов эффективность: 74,3% (762K кэшировано / 1M выборок)
[!!] Очистка кэша запросов в день: 4341
[OK] Сортировки, требующие временных таблиц: 0% (3 временные сортировки / 11K сортировок)
[!!] Объединения, выполненные без индексов: 3901
[OK] Временные таблицы, созданные на диске: 0% (2K на диске / 547K всего)
[OK] Коэффициент попадания в кэш потоков: 99% (8 создано / 25K подключений)
[OK] Коэффициент попадания в кэш таблиц: 46% (688 открыто / 1K открыто)
[OK] Использованный лимит открытых файлов: 17% (593/3K)
[OK] Блокировки таблиц, полученные немедленно: 99% (1M немедленно / 1M блокировок)
[OK] Размер данных InnoDB / буферный пул: 8.9M/256.0M-------- Рекомендации -----------------------------------------------------
Общие рекомендации:
Запустите OPTIMIZE TABLE для дефрагментации таблиц для повышения производительности
MySQL запущен в течение последних 24 часов — рекомендации могут быть неточными
Уменьшите общий объем памяти MySQL для стабильности системы
Отрегулируйте запросы на соединение, чтобы всегда использовать индексы
Переменные для настройки:
* Максимальный объем используемой памяти MySQL опасно высок
Добавьте ОЗУ перед увеличением переменных буфера MySQL *
query_cache_size (> 32M)
join_buffer_size (> 32.0M или всегда используйте индексы с объединениями)
Текущие настройки mysqld в my.cnf:
[mysqld]
local-infile=0
datadir=/var/lib/mysql
user=mysql
symbolic-links=0
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-external-locking
key_buffer_size = 150M
max_allowed_packet = 160M
max_connections = 110
wait_timeout = 60
query-cache-type = 1
query-cache-size = 32M
query_cache_limit = 2M
thread_cache_size = 16
tmp_table_size = 32M
max_heap_table_size = 32M
join_buffer_size = 32M
table_open_cache = 128
table_cache = 1600
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512 КБ
myisam_sort_buffer_size = 8 МБ
innodb_buffer_pool_size = 256 МБ
innodb_additional_mem_pool_size = 2 МБ
innodb_log_buffer_size = 8 МБ
решение1
У вас довольно странная база данных — она выполняет гораздо больше операций записи, чем чтения.
Очень важно настроить СУБД для корректной работы с Innodb.иливы настраиваете его для хорошей работы с MyISAM, но у вас, похоже, смесь обоих типов таблиц. У вас не может быть СУБД, которая была бы оптимальной с обоими. Вам нужно начать переносить данные на один движок. Учитывая интенсивную запись в базу данных, я бы рекомендовал Innodb.
Общее количество буферов: 480,0 млн глобальных + 33,5 млн на поток (максимум 110 потоков)
В настоящее время риск попадания в своп в первую очередь обусловлен использованием памяти на каждое соединение. Но учтите, что это толькорисктолько что. Вам также нужно установить, нужно ли вам 110 одновременных подключений - и желательно немного уменьшить это значение. Обычно sort_buffer_size является основным виновником использования памяти на одно подключение - но ваш составляет четверть от значения по умолчанию.
Если вы выполняете консолидацию на базе innodb, то вам, вероятно, следует увеличить размер пула буферов innodb.
Вы можете поиграться с разными значениямиздесь.