Меню

Настройка диска для mysql



Первоначальная настройка MySQL

В этой небольшой статье я хотел лишь описать самые первые шаги, которые нужно делать после того, как вы скачали и установили пакет для работы с базами данных MySQL. Я совсем не собирался здесь описывать сам MySQL и не рассчитывал вдаваться во всякие технические подробности относительно безопасности. Если вы хотите подробной информации, Read The Fine Manual. Если вы хотите как можно быстрее начать делать базы данных, читайте это маленькое руководство.

После того, как вы поставили серверную и клиентскую части пакета MySQL, следующим шагом будет заставить все это работать. Демон базы данных запускается командой mysqld. При помощи ключа [—help] можно посмотреть все доступные опции. Так же этот ключ позволит посмотреть список директорий, с которыми работает MySQL.

Для функционирования пакета, надо создать главную системную базу данных по имени mysql. Все базы создаются в отдельно выделенной папке, которую как раз можно вычислить при помощи mysqld —help. Найдите после длинного списка возможных ключей строчки, явно указывающие на разные директории.
basedir: говорит само за себя — это базовая директория, относительно которой могут быть заданы другие.
datadir: вот в ней-то и будут храниться все базы данных.

Если вы устанавливали MySQl при помощи RPM-пакетов или еще каким-нибудь автоматизированным способом, возможно в этой директории уже существует системная база данных. Если же вы компилировали пакет или переписывали исполняемые файлы вручную, то возможно ее не существует или она пуста. В этом случае базу mysql надо создать с помощью скрипта mysql_install_db. Если не будет никаких ошибок, то после окончания работы скрипта, он попросит вас задать пароль для пользователя root. Что это значит?

Базы данных в MySQL, как и во многих других системах доступны одновременно большому количеству пользователей, которые могут подключаться к серверу MySQL как с локального компьютера, посредством серверных языков и CGI, так и по TCP/IP через клиентов MySQL, находящихся на удаленных компьютерах. После создания, в системной базе будут описаны в том числе привелегии для разных пользователей. Самый главный из них конечно же пользователь root, который имеет полный доступ ко всем базам. Для него надо задать пароль, так как по умолчанию его нет.

Означает запуск главного MySQL-клиента по имени mysql от имени пользователя root (-u root) и выбор базы данных mysql Далее откроется консолька программы mysql. Делаем самый обычный SQL-запрос:

Это обновит поля Password для таблицы user, в которой поля user=’root’. Другими словами для пользователя root будет установлен пароль new_password закриптованный по методу PASSWORD().

Заставляем MySQL принять изменения:

Есть еще один способ, работающий на версиях MySQL >= 3.22:

или вообще из shell’а с помощью программы mysqladmin:

Все, теперь root не сможет просто так войти в программу mysql. Пишем

и убеждаемся в этом:

Заходить с паролем надо так:

Вот и все. Пароль для root’а совсем не обязательно должен быть таким же как его пароль в системе.
Если пароль был случайно забыт, чтобы его задать по новой, придется стереть файлы mysql.frm mysql.MYI и mysql.MYD из папки с базами данных, затем запустить скрипт mysql_install_db и повторить все по новой.

Если вам интересна структура системной базы данных вы можете строить исследовать ее с помощью SQL-запросов из программы mysql, а так же с помощью внутренних команд и утилиты mysqlshow. Например

покажет список всех таблиц в базе данных some_database, а запрос

выдаст содержимое some_table в табличном виде.

ok. Теперь хорошо бы добавить пользователей базы данных, вместе с их правами и паролями.

Используем выражение GRANT. Можно опять вносить прямые поправки в таблицы mysql, но это будет слишком длинно. Итак:

Это создаст пользователя admin, который сможет делать все что захочет со всеми базами данных и вообще mysql-ем, подключаясь только с localhost и указывая пароль some_password. Чтобы admin мог подключаться с других хостов, надо добавить строчку

Кстати *.* означает к каким базам данных и таблицам имеет доступ admin. Обозначения делаются следующим образом «база.таблица»

Для создания более-менее продвинутого пользователя можно использовать такое выражение:

Такой пользователь сможет использовать все основные SQL-команды для данных в таблицах, а так же создавать и удалять базы данных. Однако он не сможет выключать, перезапускать демон MySQL, смотреть на список процессов, не будет иметь доступ к файлам сервера, а так же сможет подключаться к базе данных только с localhost’а и указывая свой пароль.

Читайте также:  Настройки майл для андройда

Вот все возможные опции для привилегий:

SELECT,INSERT,UPDATE,DELETE — одноименные sql-команды операций с данными
INDEX — операции с индексами в таблицах
REFERENCES — работа со ссылками в базах данных и таблицах
CREATE, DROP — создание и удаление баз данных и таблиц
GRANT, ALTER — совершение операций с привилегиями
RELOAD, SHUTDOWN, PROCESS — управление сервером mysql. Перезапустить, убить и посмотреть все подключения соответственно. Точнее это дает право на выполнение команд программы mysqladmin, направленных на исполнение указанных целей
FILE — позволяет загонять в базу данных любой читабельный файл с сервера

Выбирайте сами каких пользователей создавать. Для многопользовательского сервера можно посоветовать делать пользователей, способных только изменять данные одной базы данных. Если всем сервером заведует один вебмастер, вполне можно предоставить ему более широкую свободу действий.

Отлично! Теперь минимум того, что может понадобится от сервера MySQL настроено и можно начинать создавать таблицы и вносить данные.

И не забудьте добавить mysqld в автозапуск.

Источник

Как ускорить работу MySQL и снять нагрузку с дисковой подсистемы

Любой успешный проект рано или поздно сталкивается с проблемой роста. Число посетителей веб-сайта увеличивается, веб-сервер обрабатывает бóльшее количество соединений, растёт поток запросов к базе данных. В определённый момент времени отзывчивость сайта снижается: страницы загружаются медленнее, что, согласно многочисленным исследованиям, влияет на конверсию (пример подобного исследования — http://www.webperformancetoday.com/2014/04/09/web-page-speed-affect-conversions-infographic/).

Причины увеличения времени загрузки страниц могут быть самыми разными. В этой статье мы рассмотрим одну из наиболее типичных ситуаций, а именно запросы к базе данных MySQL выполняются долго, и присутствует высокая нагрузка на дисковую подсистему.

В первую очередь следует выяснить характер нагрузки на диски. В этом поможет утилита iostat. В Ubuntu она устанавливается с пакетом sysstat:

iostat — очень мощный и удобный инструмент для просмотра статистики ввода/вывода и показателей загрузки блочных устройств. Его использование — тема для отдельной статьи. В нашей ситуации с его помощью следует определить соотношение чтения/записи, чтобы выяснить дальнейшее направление работы.

Как ускорить чтение

Допустим, диски загружены запросами на чтение. Что можно сделать, чтобы ускорить отдачу данных? Закэшировать данные в памяти. MySQL предоставляет возможность использования разных хранилищ, или движков (storage engines), для доступа к данным, поэтому подход к кэшированию разный. Рассмотрим два наиболее популярных движка: MyISAM и InnoDB.

Движок InnoDB имеет встроенный кэш для данных и индексов — так называемый Buffer Pool. Его размер регулируется переменной innodb_buffer_pool_size. В идеале размер Buffer Pool должен быть как минимум такого объёма, чтобы в нём полностью можно было разместить все данные и индексы плюс 30%-60% от их размера. Дополнительная память используется для служебных нужд и Insert Buffer, а также для обеспечения запаса памяти на будущее. Переменная innodb_buffer_pool_size — не динамическая, поэтому после её изменения в конфигурационном файле потребуется перезапуск MySQL.

Движок MyISAM не имеет кэша для данных. Но мы по-прежнему можем ускорить чтения из таблиц MyISAM. Дело в том, что ядро Linux кэширует все прочитанные файлы в области оперативной памяти, которая называется pagecache. Разумеется, файлы с таблицами MyISAM также попадают в этот кэш. Объём pagecache можно узнать из вывода команды free:

Максимальной производительности чтения можно добиться, если объём pagecache равен объёму данных MyISAM.

По умолчанию под pagecache выделяется почти вся незанятая процессами память, поэтому увеличить его объём можно лишь установкой дополнительных планок RAM. Однако память — недорогой по сравнению с ЦПУ и дисками ресурс, при этом эффект от увеличения кэша может привести к значительному увеличению производительности. Ниже представлен график %iowait — доли времени, в течение которого ЦПУ ожидает ввода/вывода. График снят с рабочего нагруженного сервера. Думаю, комментарии здесь излишни.

Как ускорить запись

Увеличить производительность MySQL при большом объёме записи можно с помощью тонкой настройки параметров сервера.

По умолчанию InnoDB сбрасывает изменённые данные на диск с помощью системного вызова fsync(). При этом операционная система не гарантирует, что данные попадут в хранилища сию секунду, т.к. данные сперва проходят через буфер, поддерживаемый ядром. Буферизация необходима для ускорения ввода/вывода.

Читайте также:  Получить настройки ммс для билайн

Однако если datadir MySQL расположен на аппаратном RAID-массиве, то есть возможность задействовать для такой буферизации NVRAM-кэш RAID-контроллера, что намного эффективнее. Следует только убедиться, что контроллер оснащён BBU (Battery Backup Unit) — отдельным источником питания для кэша. При внезапном отключении электропитания у контроллера должно быть время, чтобы сбросить содержимое кэша на диски, иначе данные в массиве останутся в неконсистентном состоянии.

При задействовании кэша RAID-контроллера повысить производительность операций записи в БД можно, отключив ненужную буферизацию на уровне операционной системы. Для этого требуется выставить переменную MySQL innodb_flush_method в значение O_DIRECT, после чего перезагрузить систему управления базы данных. Снизить нагрузку на диски также может изменение переменной innodb_flush_log_at_trx_commit. Для соответствия требованиям ACID движок InnoDB хранит логи транзакций, или redo-логи, в которые записываются все запросы на изменение данных. Эти логи используются в процессе восстановления после аварийного останова системы управления базами данных.

Значение по умолчанию (1) предполагает, что буфер redo-логов, расположенный в памяти InnoDB, записывается на диск после каждого коммита транзакции. Это наиболее безопасный режим работы, обеспечивающий сохранность каждой транзакции даже в случае “падения” сервера. Можно выставить innodb_flush_log_at_trx_commit в значение 2, тогда логи будут записываться также после каждого коммита, но fsync() — сброс данных на диск — будет выполняться лишь раз в секунду (начиная с версии MySQL 5.6.6 этот интервал определяется переменной innodb_flush_log_at_timeout). Аварийное завершение работы СУБД не приведёт к потере транзакций, однако отключение самого сервера может привести к потере последней секунды транзакций. Значение 0 подразумевает ещё более быстрый режим записи — данные и записываются, и синхронизируются раз в секунду, безотносительно коммитов транзакций. Однако innodb_flush_log_at_trx_commit=0 может привести к потере транзакций даже при падении процесса. Администратору базы данных нужно сделать выбор исходя из текущей нагрузки и бизнес-требований.

Оптимизировать дисковые операции записи помогает правильный выбор размера redo-логов. Для этого есть несложное правило. Достаточно замерить объём данных, который записан в лог за одну минуту. Эту операцию нужно выполнять в момент дневной пиковой нагрузки:

Из примера видно, что за минуту в лог InnoDB записывается 2,44 Мб данных. Объём лога следует подбирать таким образом, чтобы в него умещался объём данных за час. В таком случае у InnoDB будет достаточно времени, чтобы изменить порядок запросов на ввод/вывод для достижения последовательной записи. В нашем примере за один час через redo-логи проходит 150 Мб данных, поэтому переменную innodb_log_file_size следует выставить в значение не менее 75M. Если объём лога выбрать слишком большим, то увеличится время InnoDB Crash Recovery, что увеличит даунтайм при аварийном перезапуске (стоит отметить, что в MySQL 5.5 время Crash Recovery зависит от размера InnoDB-лога в меньшей степени).

Вывод

Разумеется, все эти советы не являются исчерпывающими. Ключом к быстрой работе БД является понимание ваших данных, грамотно спроектированная схема и удачно составленные запросы. Тем не менее ряд эффективных оптимизаций можно произвести на уровне сервера.

Источник

Оптимальная настройка Mysql

Дефолтные конфигурационные параметры в Mysql рассчитаны на микроскопические базы данных, работающие под малыми нагрузками на скромном железе.

Настройка некоторых параметров может повысить производительность базы данных в сотни раз!

Процесс оптимальной настройки Mysql состоит из двух частей — первоначальная настройка и корректировка параметров во время работы. Корректировка параметров в рабочем режиме во многом зависит от специфики Вашей системы и ее мониторинга. Разберемся с параметрами и рекомендациями по установке их значений.

Настройки нужно вносить в my.cnf.

innodb_buffer_pool_size

Если Вы используете только InnoDB таблицы, устанавливайте это значение максимально возможным для Вашей системы. Буфер InnoDB кеширует и данные и индексы. Поэтому значение этого ключа стоит устанавливать в 70%. 80% всей доступной памяти.

# При том, что на нашем сервере 32Гб оперативной памяти

innodb_log_file_size

Эта опция влияет на скорость записи. Она устанавливает размер лога операций (так операции сначала записываются в лог, а потом применяются к данным на диске). Чем больше этот лог, тем быстрее будут работать записи (т.к. их поместится больше в файл лога). Файлов всегда два, а их размер одинаковый. Значением параметра задается размер одного файла:

Читайте также:  Настройка печати принтер эпсон 210

# Так два файла дадут размер лога в 2x512M = 1G

Стоит понимать , что увеличение этого параметра увеличит и время восстановления системы при сбоях. Это происходит потому, что при запуске системы все данные из логов будет накатываться на данные. Однако с каждой новой версией, производительность этого процесса растет. Подумайте над использованием реплик для обеспечения доступности, чтобы не зависеть от времени восстановления базы данных.

innodb_log_buffer_size

Это размер буфера транзакций, которые не были еще закомичены. Значение этого параметра стоит менять в случаях, если вы используете большие поля вроде BLOB или TEXT.

# Значения по умолчанию в 1М должно быть достаточно для большинства случаев

innodb_file_per_table

Если включить эту опцию, Innodb будет сохранять данные всех таблиц в отдельных файлах (вместо одного файла по умолчанию). Прироста в производительности не будет, однако есть ряд преимуществ:

  • При удалении таблиц, диск будет освобождаться. По умолчанию общий файл данных может только расширяться, но не уменьшаться.
  • Использование компрессионного формата таблиц потребует включить этот параметр.

# С версии 5.6 этот параметр включен по умолчанию

innodb_flush_method

Этот параметр определяет логику сброса данных на диск. В современных системах при использовании RAID и резервных узов, вы будете выбирать между O_DSYNC и O_DIRECT :

# Помните об обязательном использовании резервных узлов (например, реплик)

innodb_flush_log_at_trx_commit

Изменение этого параметра может повысить пропускную способность записи данных в базу в сотни раз . Он определяет, будет ли Mysql сбрасывать каждую операцию на диск (в файл лога).

Тут следует руководствоваться такой логикой:

  • innodb_flush_log_at_trx_commit = 1 для случаев, когда сохранность данных — это приоритет номер один.
  • innodb_flush_log_at_trx_commit = 2 для случаев, когда небольшая потеря данных не критична (например, вы используете дублирование и сможете восстановить небольшую потерю). В этом случае транзакции будут сбрасываться в лог на диск только раз в секунду.

Устанавливайте значение на свое усмотрение, однако в большинстве случаев подойдет второй вариант:

# Значительное ускорение записи в базу, однако это потребует механизмов дублирования данных

query_cache_size

Значение этого параметра определяет сколько памяти стоит использовать под кеш запросов. Самый правильный подход — не полагаться на этот механизм. На практике он работает очень неэффективно. Так, весь кеш запросов для определенной таблицы сбрасывается всякий раз, когда в таблицу вносится хотя бы одно изменение. Это может привести к тому, что включение кеширования даже замедлит базу данных :

# Однако убедитесь, что используете индексы для обеспечения высокой скорости работы запросов

max_connections

Не следует изменять значение этого параметра на старте. Однако, если вы получаете ошибки «Too many connections» , эту опцию стоит поднимать. Она определяет максимальное количество одновременных соединений с базой данных:

# Поднимайте значение постепенно при появлении ошибок соединений

Настройки по умолчанию скорее всего не подойдут. Поэтому обязательно стоит пройтись по указанным параметрам в статье и подобрать для них значения. Если совсем лень — генератор настроек Mysql.

Check-unused-keys для определения неиспользуемых индексов в базе данных

Повышение скорости работы запросов с MySQL Handlersocket

Что такое индексы в Mysql и как их использовать для оптимизации запросов

Синтаксис и оптимизация Mysql LIMIT

Оптимизация постраничного вывода данных

Эффективная замена ORDER BY RAND()

Сравнение Vertica и Mysql на практике

Включение и использование логов ошибок, запросов и медленных запросов, бинарного лога для проверки работы MySQL

Правила выбора типов данных для максимальной производительности в Mysql

Ускорение репликации в Mysql 5.6+

Как восстановить данные, если MySQL упал и не поднимается

Быстрая альтернатива Mysqldump для больших таблиц без блокировок и выключений.

Анализ медленных запросов с помощью EXPLAIN

Правильный поиск по тексту в Mysql (full-text search)

Анализ медленных запросов (профилирование) в MySQL с помощью Percona Toolkit

3 примера установки индексов в JOIN запросах

Рекомендации по настройке Redis для оптимизации ресурсов и повышения стабильности на производственном сервере

Использование партиций для ускорения сложных удалений

Сравнение двух движков и когда стоит использовать каждый из них

И как правильно работать с длительными соединениями в MySQL

Проверка работы Mysql под нагрузкой Sysbench

Настройка Master-Slave репликации на MySQL за 6 простых шагов

Настройки для улучшения производительности Postgres

Примеры использования колоночной базы данных Vertica

Источник

Adblock
detector