От автора: одной установкой и созданием баз данных все не кончается. Для того, чтобы адаптировать под веб-разработку, необходимо внести изменения в MySQL параметры. Освоить все это методом тыка — настоящее испытание, которое может и не привести к успешному результату. Тем более, что дорожка давно проторена и уже сейчас можно найти массу мануалов, касательно конфигураций баз данных MySQL. Мы собрали несколько из них и создали статью, из которой вы узнаете о тонкой настройке, основных отличиях и преимуществах различных видов настройки.
Чем плохи умолчания?
Если говорить о веб-разработке, то реляционные MySQL были созданы не только для нее. Прежде всего необходима была систематизация файлов, включение их в одну архитектуру и прочие преимущества, которые мы сегодня наблюдаем. Тем не менее, MySQL уже настолько прочно укоренилась в мир веба, что даже ассоциируется с ним: наборы разработчиков обязательно включают одну из СУБД. Примечательно, что в большинстве случаев — это MySQL.
Но, стандартные настройки базы данных не только не дадут создать полноценный продукт, но и затруднят управление системой в принципе. К изначальной конфигурации необходимо добавить немало пунктов, которые сделают из ваших БД настоящий агрегатор данных, который можно использовать, как для массивных веб-приложений, так и для небольших решений, рассчитанных, в основном, на чтение с минимальным взаимодействием.
InnoDB или MyISAM?
Существует несколько наиболее распространённых систем хранения данных. Две из них встроены в MySQL: InnoDB и MyISAM. У каждой из них есть свои преимущества и недостатки, что определено применением. Существует очень простое объяснение того, когда следует использовать каждую их них. К примеру, некоторые разработчики считают, что MyISAM стоит включить только как систему для программ чтения и записи. В реальности все намного сложнее.
В процессе рассмотрения конфигураций MySQL мы еще не раз вернемся к вопросу о том, как настраивать каждую из подсистем. Мы покажем, насколько применимой может оказаться каждая их них. Проблема в том, что в большинстве мануалов из интернета информация устарела и уже не может рассматриваться, как релевантная. В итоге, все приходят к выводам о том, что новичку следует использовать MyISAM, а всем остальным — Inno. Необходимо рассмотреть основные отличия, чтобы прийти к объективным выводам:
транзакции. То, чего не хватает MyISAM. Это основной ресурс для работы платежных систем и не только. Любой веб-продукт, где будет осуществляться обмен информацией, нуждается в транзакционном движке. Как только в MySQL возникает ошибка, движок может совершить rollback и вернуть данные к предыдущему состоянию;
блокировки. В MyISAM они совершаются на уровне таблиц, в то время, как Inno блокирует отдельно каждую из строк;
deadlock. Подобных блокировок не стоит ожидать в ISAM, в то время, как в Inno они случаются время от времени;
count. Эти запросы выполняются быстрее именно в ISAM, несмотря на то, что ее даже не рассматривают, как вариант некоторые разработчики;
хранение таблицы. В Inno — это больше файлы, в которых насыпью находятся все данные. В ISAM для таблицы существует файл, у каждой свой.
И это далеко не весь перечень отличий. По сути, это две принципиально разные подсистемы, механизм работы которых имеет мало общих черт. Но, мы рассмотрим оптимизации MySQL для каждого из них.
Первоначальная настройка
До того, как мы перейдём непосредственно к расширению возможностей MySQL, нам необходимо произвести первичную настройку баз данных. Это короткий этап, который сделает работу более персонализированной и безопасной. Представим, что вы уже произвели установку пакетов для взаимодействия с БД, и теперь вас ждут первые шаги тонкой настройки. На этом этапе не стоит ждать высокоточного распределения ресурсов, но уже с этого момента можно начать создание базы. Если нет желания вдаваться в технические детали, то, по сути, на этом можно и остановиться.
Итак, серверная и клиентская стороны MySQL установлены, пора настроить их взаимодействие и запустить. Делаем это при помощи команды mysqld. Если всевозможные опции вам неизвестны (а это так), необходимо вызвать помощь, интуитивной командой — help. Новая информация отобразит и пути, с которыми взаимодействует СУБД.
Создание первой базы. Ей будет присвоено имя mysql. Стоит найти и директории, с которыми MySQL будет работать. Современные инсталляторы позволяют пропустить этот этап, но знание матчасти никому не повредит.
Теперь пришло время для ограничения привилегий. Сейчас любой желающий может войти в вашу БД с правами суперпользователя. Необходимо ограничить это круг и присвоить пароль для root-прав. Как включить эту конфигурацию? Очень просто:
mysql —u root mysql
После нашей команды, в MySQL открывается консоль пользователя, куда мы и забьём запрос на изменение пароля:
update user set password=PASSWORD('NEW-PASSWORD-HERE') where User='John';
Чтобы применить внесенные правки нужно воспользоваться командой:
flush privileges;
Попробуйте зайти в рут, при помощи других учетных данных, чтобы проверить работу защиты.
Для привилегированного пользователя существует ряд действий, которые он может выполнять с готовыми таблицами баз данных, а также создавать новые. Вы сами можете создать несколько типов пользователей с разными возможностями для редактирования. Обычный администратор должен иметь доступ только к одной БД, в то время, как для разработчика необходимы более широкие полномочия.
Необходимое логирование
Итак, в вашем распоряжении создание пользователей, которые будут модифицировать базу данных. Вы можете ограничить их функциональность, можете расширить ее до максимума. Но, в случае нарушения целостности или любых других угроз безопасности, винить будет некого, если не отслеживать все действия в MySQL. Это могут обеспечить логи, если включить их как следует. Единственный способ контроля, который не предусматривает логирование — это постоянный мониторинг, который мало кто может себе позволить. Слишком много времени уйдет на работы с низким коэффициентом полезного действия.
Логи уведомления ошибок включены при установке программного обеспечения для работы с MySQL. С ошибками и их обработкой мы уже знакомились. При логировании они сохраняются в виде чисел и текстовых маркировок, по которым их идентифицирует администратор.
Общие логи — это любые действия с базами данных. Как правило, их оставляют отключенными. Слишком много ресурсов будет потреблять такой процесс. Куда интереснее природа медленных логов. В них будут записаны все операции, которые заняли больше времени, чем обычно. К примеру, вы устанавливаете допустимую задержку в пятнадцать секунд. Все, что заняло больше времени, будет записано. Таким образом можно отследить атаку на сервер с базами данных.
Параметры для расширения
Теперь перейдём непосредственно к параметрам для расширения, которые необходимо провести на самом старте. Вы уже совершили все первоначальные настройки и пришло время для точных изменений. Важно знать, что выставление значений опций — сугубо индивидуальный процесс, который сильно зависит не столько от целей, для которых вы создаете базу данных, сколько от «железа», на котором происходит основной процесс. Мы рассмотрим те команды, которые являются наиболее глобальными и необходимы при создании большинства веб-проектов.
Начнем с опции для ISAM-таблицы. Хоть на них и любят поругаться профессионалы, участие в веб-разработке этих движков все еще велико. Тем более, что подходят они для 80% всего, что сейчас создают в сети — легковесный информационный сайт с небольшими динамическими элементами. В первую очередь, речь пойдет о key_buffer_size. В целом, значение можно ставить примерно в треть всей оперативной памяти, которая доступна на железе. Так как ISAM расходует кэш-память операционки, необходимо учесть и общую нагрузку на сервер. Оставьте также память и для того, чтобы хранить сами данные. Важным моментом является использование полного объема кэша. Поэтому, если ваши файлы занимают меньше места, уменьшите и память, отведенную под них.
Теперь похожую операцию с буфером можно провести, если вы собираетесь использовать в основном InnoDB. Параметр носит название innodb_buffer_pool_size. Дело в том, что, если ISAM может неплохо взаимодействовать и с теми настройками, что система несет сама по себе, то с Inno так не получится. Здесь необходимо внести изменения в любом случае. Иначе, все что вы создадите погрязнет во «фризах». Вы могли видеть последствия такой ошибки: веб-приложение явно не должно тратить много ресурсов, но постоянно тормозит даже на мощных машинах и с хорошим соединением.
Этот движок не использует кэш — у него свой буфер. Если вы работаете только с этим типом таблиц, значит оперативную память устройства можно «кушать» на 2/3. Можно поиграться со значениями, чтобы обеспечить быстродействие. Не стоит думать, что чем больше вы выставите, тем лучше заработает ваш продукт. Здесь важен не размер, а правильное соотношение.
Мы отредактируем место, которое выделено системой под запись всего, что происходило с базой данных в определенный момент. Начнем с опции innodb_log_file_size. Она необходима, если в таблицы происходит постоянная запись данных. Как только вы увеличите размер файлов, база данных начнет «летать». Не стоит просто выставлять размер, который вы считаете большим. Превалирующее число разработчиков считает, что 512 мб — это крайнее значение. Но, не ограничивайте себя этими рамками. Постепенно увеличивайте размер и тестируйте. Только так вы сможете выбрать идеальное соотношение. Не забывайте соблюдать прогрессию использования памяти.
Продолжаем! Теперь настал черед innodb_log_buffer_size. Если ваш проект — не социальная сеть и не стриминговый музыкальный портал, лучше оставить все как есть. Иначе вас ждет нестабильность и перерасход мощностей. Если же ваш веб-продукт и правда ожидает такая популярность и активность пользователей, можно немного увеличить объем. Рекомендуемое число — до шестнадцати мегабайт. Но, как мы и говорили, все переменные — индивидуальны. Придется подбирать и тестировать производительность.
Теперь пришло время приблизить быстродействие Inno к ISAM. Если ранее вы смотрели в сторону второго движка, то сейчас эта эпоха закончится. Необходимо внести изменения в опцию под названием innodb_flush_log_at_trx_commit. После установки ПО, значение параметра равно единице. Это значит, что все транзакции, которые производит движок, сбрасываются на диск устройства. Только представьте, что это значит для большого проекта. Есть две альтернативы. Первая — это значение ноль. Система начнет летать, но при остановке, либо другом нарушении, вся информация об изменениях будет утеряна. Если выставить значение «2», то данные будут храниться в кэше операционной. Для вас это значит, что база данных заработает быстрее, а серьезные последствия ждут только в том случае, если слетит сама ось.
Даже сам факт открытия таблицы может потреблять немало ресурсов, если не внести изменения в конфигурацию. С этой операцией можно совладать с помощью параметра table_cache. Он изменяет размер кэша до требуемых размеров. Вместо того, чтобы постоянно открывать новую таблицу, вы сможете держать их открытыми постоянно. Кстати, на этом примере видно, насколько прогрессивным решением является реляционная база данных, в сравнении с файлами.
Отличным решением для быстродействия является кэш у самого приложения. Но, такой подход не всегда работает — не всегда роскошь доступна. Однако, если считывание данных — частая операция для программы, а кеша нет, то query_cache_size и его изменение может помочь. Установите значение около 512 МБ и посмотрите, не увеличилось ли быстродействие. Если нет — уменьшите наполовину. Потом, увеличьте на столько же. Такой «метод тыка» бывает более быстрым, чем арифметическое сложение и вычитание.
Кстати, если вы не особенно хорошо разбираетесь в железе, значит можно попробовать действовать с помощью конфигурационных заготовок. Перед тем, как менять все подряд в системных файлах, можно попробовать те, которые подготовили разработчики СУБД. Несколько разных файлов my.cnf и один из вас может подойти под тот тип оборудования, который используете именно вы.
Когда применить MyISAM
Это вопрос, который мы задали в самом начале статьи и теперь дадим на него ответ. Но, сначала необходимо узнать об этом движке немного больше. Это система хранения данных, которая основана на одноименных принципах, разработанных, внимание, в 1963 году в компании IBM. Но, взирая на то, что математическим законам нет возраста, система работает до сих пор. Некоторые небольшие веб-проекты активно используют MyISAM и не видят надобности в переходе на InnoDB.
Однако, как только вы увидите, что проект нуждается в масштабировании в сторону «больше», ISAM станет непригоден к использованию. Эта система не поддерживает транзакции, а значит, не позволит участникам взаимодействовать друг с другом в полной мере. По сути, на основе этого движка можно разрабатывать лишь новостные информационные сайты. И даже они нуждаются в высоком уровне интерактивности, как показывают тренды современной веб-разработки.
Совсем недавно преимуществом ISAM был полнотекстовый поиск. Но, с выходом версии InnoDB 5.6.4, он доступен и на этом движке. Сегодня можно с уверенностью сказать, что ISAM — это базы данных начального уровня. Тем не менее, их довольно успешно комбинируют в реализациях социальных сетей, как например Facebook. Там почти весь массив информации хранится в Inno, в то время, как файлы для чтения размещены в MyISAM.
Database twist
Однозначно хорошей новостью можно назвать то, что базы данных можно бесконечно переносить с одного движка на другой. Делается это в уже знакомом нам файле my.cnf. Если вы работали с ISAM, то не забудьте убрать теги комментирования со строки «skip-innodb». Их блокируют для того, чтобы ускорить быстродействие базы данных.
Убедитесь в том, что правильно выставлен параметр innodb_data_file_path. Он должен выглядеть следующим образом:
innodb_data_file_path = ibdata1:100M:autoextend
Для любознательных: он определяет, каким будет наименьший объем файла, в котором хранятся таблицы. А также, позволяет ему увеличиваться в зависимости от хранимой информации. Не желательно, чтобы параметр был в максимальном значении. Далее, следует перезапустить MySQL.
Теперь необходимо отгрузить все данные из базы данных:
mysqldump --opt -u USER -p DBNAME > dump.sql
Далее заменяем все на новый движок:
sed 's/ENGINE=MyISAM/ENGINE=InnoDB/g' dump.sql > resultdump.sql
Или:
sed 's/ENGINE=InnoDB/ENGINE=MyISAM/g'dump.sql > resultdump.sql
Все зависит от того, что на что вы собрались менять. Осталось только удалить старую базу данных. Создайте новую пустую базу данных и выгрузите в нее дамп.
Как видите, разнообразие настраиваемых параметров в MySQL способно удовлетворить всем требованиям, которые ставит перед СУБД веб-разработка. Несмотря на то, что существуют реляционные аналоги этого программного обеспечения, они остаются лидерами рынка. Кроме того, компания Oracle обеспечивает постоянное развитие и обновление, а AMP сборки все чаще делают свой выбор не в пользу альтернатив. Проверенный продукт остается на рынке и продолжает радовать разработчиков.
А у нас все! Экспериментируйте с конфигурациями и получите опыт работы с тонкой настройкой баз данных.
