MySQL — это мощная база данных с открытым исходным кодом. С появлением все большего числа приложений, управляемых базой данных, люди расширяют возможности MySQL. Вот 101 совет по настройке и оптимизации установки MySQL. Некоторые советы относятся к среде, в которой они установлены, но концепции универсальны. Мы разделили их на несколько категорий, чтобы помочь вам получить максимальную отдачу от MySQL :
Оборудование сервера MySQL и настройка ОС:
1. Имейте достаточно физической памяти, чтобы загрузить весь файл InnoDB в память — InnoDB намного быстрее, когда к файлу можно получить доступ в памяти, а не с диска. 2. Избегайте свопа любой ценой — своп читает с диска, он медленный. 3. Используйте ОЗУ с батарейным питанием. 4. Используйте расширенный RAID — предпочтительно RAID10 или выше. 5. Избегайте RAID5 — контрольная сумма, необходимая для обеспечения целостности, является дорогостоящей. 6. Разделите свою ОС и разделы данных не только логически, но и физически — дорогостоящие операции записи и чтения ОС будут влиять на производительность вашей базы данных. 7. Поместите свое временное пространство mysql и журналы репликации в отдельный раздел, где ваши данные — фоновые записи будут влиять на вашу базу данных, когда она перейдет к записи/чтению с диска. 8. Чем больше дисков, тем больше скорость. 9. Чем быстрее диски, тем лучше. 10. Используйте SAS поверх SATA. 11. Меньшие диски быстрее, чем большие, особенно в конфигурациях RAID. 12. Используйте RAID-контроллеры с кэш-памятью с батарейной поддержкой. 13. Избегайте программных рейдов. 14. Подумайте об использовании твердотельных карт ввода-вывода (не дисководов) для вашего раздела данных — эти карты могут поддерживать запись со скоростью более 2 ГБ/с практически для любого объема данных. 15. В Linux установите значение подкачки равным 0 — нет причин для кэширования файлов на сервере базы данных, это скорее преимущество веб-сервера или рабочего стола. 16. Смонтируйте файловую систему с noatime и nodirtime, если доступно — нет причин обновлять время модификации файла базы данных для доступа. 17. Используйте файловую систему XFS — более быструю, меньшую файловую систему, чем ext3, и имеющую больше возможностей для ведения журнала, также было показано, что ext3 имеет проблемы двойной буферизации с MySQL. 18. Настройте журнал файловой системы XFS и переменные буфера — для максимальной производительности производительности. 19. В системах Linux используйте планировщик NOOP или DEADLINE IO — было показано, что планировщик CFQ и ANTICIPATORY медленнее, чем планировщик NOOP и DEADLINE. 20. Используйте 64-битную ОС — больше памяти адресуемой и пригодной для MySQL. 21. Удалите неиспользуемые пакеты и демоны с серверов — меньше воровства ресурсов. 22. Поместите ваш хост, который использует MySQL, и ваш хост MySQL в файл hosts — без поиска DNS. 23. Никогда не убивайте принудительно процесс MySQL — вы повредите свою базу данных и будете запускать резервные копии. 24. Выделите свой сервер для MySQL — фоновые процессы и другие сервисы могут украсть время процессора БД.
25. Используйте innodb_flush_method = O_DIRECT, чтобы избежать двойного буфера при записи. 26. Избегайте файловой системы O_DIRECT и EXT3 — вы будете сериализовать все ваши записи. 27. Выделите достаточно innodb_buffer_pool_size для загрузки всего вашего файла InnoDB в память — меньше чтения с диска. 28. Не делайте innodb_log_file_size слишком большим, с более быстрыми и большими дисками — более частая очистка хороша и сокращает время восстановления при сбоях. 29. Не смешивайте переменные innodb_thread_concurrency и thread_concurrency — эти два значения несовместимы. 30. Выделите минимальное количество для max_connections — слишком много соединений могут использовать вашу оперативную память и заблокировать ваш сервер MySQL. 31. Держите thread_cache с относительно большим числом, около 16 — для предотвращения замедления при открытии соединений. 32. Используйте skip-name-Resolution — для удаления DNS-запросов. 33. Используйте кэш запросов, если ваши запросы повторяются и ваши данные меняются не часто — однако использование кеша запросов для данных, которые часто изменяются, приведет к снижению производительности. 34. Увеличить temp_table_size — для предотвращения записи на диск. 35. Увеличить max_heap_table_size — для предотвращения записи на диск. 36. Не устанавливайте свой sort_buffer_size слишком высоким — это для каждого соединения и может быстро израсходовать память. 37. Контролируйте key_read_requests и key_reads, чтобы определить размер вашего key_buffer — запросы на чтение ключа должны быть выше, чем ваши key_reads, иначе вы неэффективно используете свой key_buffer. 38. Установка innodb_flush_log_at_trx_commit = 0 улучшит производительность, но оставив ее по умолчанию (1), вы гарантируете целостность данных, а также гарантируете, что репликация не отстает. 39. Имейте тестовую среду, где вы можете проверить свои конфигурации, а также частые перезапуски без влияния на производительность.
Оптимизация схемы MySQL:
40. Используйте в своей базе данных trim. 41. Архивировать старые данные — убрать излишние строки возврата или поиска по запросам. 42. Поместите индексы на свои данные. 43. Не злоупотребляйте индексами, сравните с вашими запросами. 44. Сжатие текстовых и блобных типов данных — для экономии места и уменьшения числа операций чтения с диска. 45. UTF 8 и UTF16 медленнее латинского1. 46. Используйте триггеры экономно. 47. Сохраняйте избыточные данные до минимума — не дублируйте данные без необходимости. 48. Используйте связывание таблиц, а не расширение строк. 49. Обратите внимание на ваши типы данных, используйте наименьший возможный для ваших реальных данных. 50. Отделите данные BLOB/текста от других данных, если другие данные часто используются для запросов, а объекты BLOB/Text — нет. 51. Часто проверяйте и оптимизируйте таблицы. 52. Часто переписывайте таблицы InnoDB для оптимизации. 53. Иногда быстрее удалить индексы при добавлении столбцов, а затем добавить индексы обратно. 54. Используйте разные механизмы хранения для разных нужд. 55. Используйте механизм хранения ARCHIVE для журналирования таблиц или аудита таблиц — это гораздо более эффективно для записи. 56. Храните данные сеанса в memcache, а не в MySQL — memcache допускает автоматическое истечение значений и не позволяет создавать дорогостоящие операции чтения и записи в MySQL для временных данных. 57. Используйте VARCHAR вместо CHAR при хранении строк переменной длины — для экономии места, поскольку CHAR — это фиксированная длина, а VARCHAR — нет (на utf8 это не влияет). 58. Вносите изменения схемы постепенно — небольшое изменение может иметь радикальные последствия. 59. Протестируйте все изменения схемы в среде разработки, которая отражает производство. 60. НЕ изменяйте произвольно значения в вашем конфигурационном файле, это может иметь катастрофические последствия. 61. Иногда меньше — это больше в конфигурациях MySQL. 62. В случае сомнений используйте общий конфигурационный файл MySQL.
Оптимизация запросов:
63. Используйте медленный журнал запросов, чтобы найти медленные запросы. 64. Используйте EXPLAIN для определения правильности работы запросов. 65. Часто проверяйте свои запросы, чтобы убедиться, что они работают оптимально — производительность со временем изменится. 66. Избегайте подсчета (*) для целых таблиц, это может заблокировать всю таблицу. 67. Сделайте запросы единообразными, чтобы последующие похожие запросы использовали кеш запросов. 68. При необходимости используйте GROUP BY вместо DISTINCT. 69. Используйте индексированные столбцы в предложениях WHERE, GROUP BY и ORDER BY. 70. Сохраняйте индексы простыми, не используйте столбец повторно в нескольких индексах. 71. Иногда MySQL выбирает неправильный индекс, используйте USE INDEX для этого случая. 72. Проверьте наличие проблем, используя SQL_MODE = STRICT. 73. Используйте LIMIT для UNION вместо OR для менее чем 5 проиндексированных полей. 74. Используйте INSERT ON DUPLICATE KEY или INSERT IGNORE вместо UPDATE, чтобы избежать SELECT до обновления. 75. Используйте индексированное поле и ORDER BY вместо MAX. 76. Избегайте использования ORDER BY RAND(). 77. LIMIT M, N может на самом деле замедлять запросы при определенных обстоятельствах, используйте экономно. 78. Используйте UNION вместо подзапросов в предложениях WHERE. 79. Для UPDATES используйте SHARE MODE, чтобы предотвратить эксклюзивные блокировки. 80. При перезапуске MySQL не забудьте прогреть свою базу данных, чтобы убедиться, что ваши данные находятся в памяти и запросы выполняются быстро. 81. Используйте DROP TABLE, затем CREATE TABLE вместо DELETE FROM, чтобы удалить все данные из таблицы. 82. Минимизируйте данные в вашем запросе только до тех данных, которые вам нужны, большую часть времени вы используете *. 83. Рассмотрите постоянные соединения вместо нескольких соединений, чтобы уменьшить накладные расходы. 84. Контрольные запросы, в том числе с использованием нагрузки на сервер, иногда простой запрос может повлиять на другие запросы. 85. Когда нагрузка на ваш сервер увеличивается, используйте SHOW PROCESSLIST для просмотра медленных/проблемных запросов. 86. Протестируйте все подозрительные запросы в среде разработки, в которой вы отразили производственные данные.
Процедуры резервного копирования MySQL:
87. Резервное копирование со вторичного реплицируемого сервера. 88. Остановите репликацию во время резервного копирования, чтобы предотвратить несоответствия в зависимостях данных и внешних ограничениях. 89. Полностью остановите MySQL и сделайте резервную копию файлов базы данных. 90. Резервное копирование двоичных журналов одновременно с дамп-файлом, если используется дамп MySQL — чтобы убедиться, что репликация не прерывается. 91. Не доверяйте снимку LVM для резервных копий — это может привести к несоответствиям данных, что приведет к проблемам в будущем. 92. Создавайте дампы для каждой таблицы, чтобы упростить восстановление одной таблицы, если данные изолированы от других таблиц. 93. Используйте –opt при использовании mysqldump. 94. Проверьте и оптимизируйте таблицы перед резервным копированием. 95. При импорте временно отключите внешние ограничения для более быстрого импорта. 96. При импорте временно отключите уникальные проверки для более быстрого импорта. 97. Рассчитайте размер данных и индексов базы данных/таблиц после каждой резервной копии, чтобы отслеживать рост. 98. Следите за репликацией подчиненного устройства на наличие ошибок и задержек с помощью скрипта cron. 99. Регулярно делайте резервные копии. 100. Регулярно проверяйте свои резервные копии.
