Как оптимизировать MySQL Server для повышения производительности?
Недавно мне пришлось работать с устаревшей системой. Работа с ним заняла у меня много времени, потому что многие таблицы не были должным образом нормализованы. Из-за проблем с обслуживанием это невозможно изменить. Мой старший инженер сказал мне оптимизировать сервер MySQL для повышения производительности и скорости.
Я изменил свой C:\Program Files\MySQL\MySQL Server 5.6\my.ini файл должен быть следующим:
key_buffer_size = 128M 30% of your memory (Max 4GB)
max_allowed_packet = 5M
table_open_cache = 256
sort_buffer_size = 10M
read_buffer_size = 20M
read_rnd_buffer_size = 10M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 30M
thread_concurrency = 8
bulk_insert_buffer_size = 256
innodb_buffer_pool_size = 5G to 50% - 70% of your ram.
innodb_additional_mem_pool_size = 1G to to 10% of ram
innodb_log_buffer_size = 1G
innodb_log_file_size = 1G (Max 4GB) larger the file recovery slow.
innodb_file_per_table = 1
Сначала работает нормально. Однако через несколько часов я не смог запустить MySQL, и мне пришлось переустановить MySQL Server.
Что я сделал не так и как я могу это исправить?
1 ответ
Просто, чтобы получить некоторую перспективу извлечения вашего конфигурационного файла, я сделал сравнение со значением по умолчанию ( 1, 2). Обозначение будет иметь параметр, за которым следует ваше значение и значение по умолчанию в скобках.
key_buffer_size = 128M (8M)
max_allowed_packet = 5M (4M)
table_open_cache = 256 (2000)
sort_buffer_size = 10M (2M)
read_buffer_size = 20M (1M)
read_rnd_buffer_size = 10M (2M)
myisam_sort_buffer_size = 64M (8M)
thread_cache_size = 8 (-1)
query_cache_size= 30M (0/1M)
thread_concurrency = 8 (10)
bulk_insert_buffer_size = 256 (8M)
innodb_buffer_pool_size = 5G (128M)
innodb_additional_mem_pool_size = 1G (8M)
innodb_log_buffer_size = 1G (8M)
innodb_log_file_size = 1G (5M)
innodb_file_per_table = 1 (Off/On)
Как мы видим, вы значительно увеличили большинство кешей. Но имеет ли это смысл? Вы сказали, что ваша БД использует движок InnoDB. Согласно вышеуказанной документации key_buffer_size, read_buffer_size, bulk_insert_buffer_size применяется только к базам данных MyISAM. Я не уверен, когда временные пробелы будут работать на этом.
С помощью myisam_sort_buffer_size кажется задом наперед, особенно потому, что в документации говорится: The size of the buffer that is allocated when sorting MyISAM indexes during a REPAIR TABLE or when creating indexes with CREATE INDEX or ALTER TABLE. Так что это будет использоваться редко в большинстве нормальных случаев использования.
Кроме того, вы используете следующие настройки, которые, по-видимому, в значительной степени зависят от вашей модели использования БД max_allowed_packet, table_open_cache, thread_cache_size, innodb_log_buffer_size но вы ничего не сказали о шаблонах использования. Поэтому рассмотрите приведенную выше документацию, чтобы взглянуть на то, что они на самом деле делают, и сравнить их с моделью использования БД.
Остерегайтесь этого sort_buffer_size а также read_rnd_buffer_size зарезервировано для каждой сессии. Некоторые параметры MyISAM могут быть такими же, так что вы действительно получаете немало накладных расходов для каждого сеанса. Опять же, вам нужно оценить фактическую модель использования.
Теперь о настройках InnoDB, которые вы указали. innodb_additional_mem_pool_size устарела. Непонятно почему ты изменишь innodb_log_file_size, innodb_file_per_table не уверен, что ваша причина для этого изменения может быть. Последний может быть просто по умолчанию On? Установив оставшиеся настройки innodb_buffer_pool_size до такого высокого значения, вы можете рассмотреть возможность использования innodb_buffer_pool_instances также.
В целом похоже, что вы только что попробовали что-то, и это не сработало. Согласно вашим комментариям, вы даже не заглядывали в файлы журналов MySQL, чтобы выяснить, что отвечает за службу, которая не принимает соединения или вообще не устанавливает соединение. Если вы действительно хотите оптимизировать производительность БД, вам придется это сделать. Подумайте об использовании дополнительных индексов в таблицах, где это целесообразно, чтобы повысить производительность запросов и посмотреть, какие запросы фактически выполняются в БД. Кроме того, попытайтесь понять, какой шаблон использования БД имеет с точки зрения количества соединений и размера запросов.