Что нужно настроить в mySQL сразу после установки? Mysql настройки

Оптимизация MySQL с помощью настроек в my.cnf

Хочу рассказать о настройках, которые применял, но не все из них помогли, просто опишу их.

Введение

Как я успел разобраться, при каждом соединении с MySQL создается mysqld (демон), который и обрабатывает все запросы соединения. Вот в блоке [mysqld] описывается именно настройки таких демонов.

Итак, давайте рассмотрим настройки демона [mysqld].

Выставить кодировку по умолчанию можно так:

character-set-server = utf8collation-server = utf8_unicode_ci

Защитить сервер от кривых рук программиста, способного join`ом на 10 миллионов записей похоронить даже 4-х процессорный сервер, можно так:

max_join_size = 1000000

Буфер можно выставить 25% от общего объема оперативной памяти:

key_buffer_size = 2048M

как я понял, это буфер обмена для всех демонов, т.е. реально будет: key_buffer_size / кол-во демонов = ???M

Размер стека для каждого потока (демона):

thread_stack = 512K

стек - это место для хранения списка задач (открыть таблицу, выполнить запрос, закрыть и т.п.)

Кол-во потоков, которые сервер должен поместить в кэш для повторного использования:

thread_cache_size = 32

т.е. если к примеру есть часто повтояющийся SELECT * FROM myTable, то он попадет в кэш, чтобы не выполняться каждый раз.

Полезная настройка: если размер временной таблицы превышает размер, установленный этой переменной, она сбрасывается на диск. При наличии достаточного количества памяти на сервере, рекомендуется повысить значение данной переменной, по ускорения запросов с конструкцией GROUP BY

tmp_table_size = 512M

Установить максимальный размер таблиц типа MEMORY ( HEAP ) можно так:

max_heap_table_size = 256M

Размер буфера, выделяемого демону при выполнении операций сортировки. Для ускорения операций ORDER BY, GROUP BY рекомендуется увеличить данное значение

sort_buffer_size = 4M

Размер буфера выделяемого для сортировки MyISAM индексов с помощью оператора REPAIR TABLE или при создании индексов операторами CREATE TABLE, ALTER TABLE:

myisam_sort_buffer_size = 256M

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

read_buffer_size = 4M

Размер буфера, выделяемого для чтения строк после сортировки, что-бы избежать повторного поиска на диске. Увеличение значения данной переменной может существенно увеличить эффективность конструкции ORDER BY. Имейте в виду, так как данный буфер выделяется для каждого демона, не следует устанавливать чересчур большое значение.

read_rnd_buffer_size = 8M

Размер буфера использующегося при операциях объединения таблиц ( если не используются индексы ). Буфер устанавливается один раз во время каждой операции объединения

join_buffer_size = 8M

Величина буфера, который используется для индексов, всех демонов. Если используется много DELETE или INSERT запросов к таблицам с большим кол - индексов, то увеличение значения повысит скорость выполнения таких запросов. Для достижения еще большей скорости нужно использовать LOCK TABLES. Советуют устанавливать не больше чем 1/3 озу и не больше объема всех б.д.

key_buffer = 2048M

Максимально количество соединений клиентов с сервером

max_connections = 35

Задает максимально количество неудачных попыток подключения с хоста. Значение по-умолчанию 10. При достижении данного значения, хост блокируется. Разблокировать хост можно с помощью: mysql> FLUSH HOSTS

max_connect_errors = 50

Максимальное число одновременных подключений для одной учетной записи MySQL. Значение по-умолчанию 0, отсутствие каких-либо ограничений

max_user_connections = 25

table_cache старое название для переменной table_open_cache, в нем указывается количество открытых таблиц для всех демонов. Увеличение значения приведет к увеличению количества используемых дескрипторов файла. Советуют рассчитывать по формуле: количество одновременных соединений * количество открытых таблиц в соединении. Т.е. для каждого соединения используется свои ячейки из кэша. Для проверки можно запустить mysqltuner.pl

table_cache = 128

Количество одновременно запускаемых демонов, советуют формулу: количество ядер процессора умножаем на 2

thread_concurrency = 16

Размер буфера для соединений, устанавливаемый сервером в промежутках между запросами

net_buffer_length = 1024

Максимальный объем одного SQL-запроса к серверу. Изначально буфер сообщений имеет размер net_buffer_length и при необходимости, автоматически увеличивается до значения данной переменной.

max_allowed_packet = 512M

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

max_sort_length = 512

Полезная настройка: запросы, результат которых превышает значение данной переменной, не будут размещаться в кэше запросов.

query_cache_limit = 2M

Полезная настройка: объем памяти, выделенной для кэширования результатов запросов. По-умолчанию данный кэш отключен, значение - 0

query_cache_size = 16M

Полезная настройка: вид кэширования:

0 - ничего не кэшировать (по-умолчанию)1 - кэшировать все запросы, кроме SELECT SQL_NO_CACHE2 - кэшировать только запросы, начинающихся с конструкции SELECT SQL_CACHE

query_cache_type = 2

Настройки innodb (извините, что без пояснений, просто оставлю их тут, чтобы не забыть):

innodb_data_home_dir = /var/lib/mysqlinnodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextendinnodb_log_group_home_dir = /var/lib/mysqlinnodb_buffer_pool_size = 64Minnodb_additional_mem_pool_size = 32Minnodb_file_io_threads = 8innodb_lock_wait_timeout = 50innodb_log_buffer_size = 8Minnodb_flush_log_at_trx_commit = 2innodb_flush_method = O_DIRECT

Битрикс рекомендует:

transaction-isolation = READ-COMMITTEDdefault-character-set = utf8

Настройки блока для создания дампов

[mysqldump]

default-character-set = utf8

[mysql]

Старое название следующей настройки: character-set-server = utf8 выдает ошибку: /usr/bin/mysql_upgrade: unknown variable 'character-set-server=utf8

default-character-set = utf8

Надеюсь, кому-нибудь помог разобраться, удачки в освоении MySQL.

yapro.ru

Установка Mysql: пошаговая инструкция

Установка MySQL никогда не вызывает проблем как на платформе Windows, так и на всем семействе линуксоидов. На официальном сайте можно найти MySQL Installer, ответить на все его вопросы и моментально получить работающую систему управления базами данных.

Особенности установки MySQL

Варианты, при которых стандартный установщик сработает не так как нужно, ничтожно малы, но даже если они случаются можно попробовать установить другую версию, перепроверить файл my.ini и попросту разрешить доступ к порту 3306, что обычно является причиной проблем.

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

пользователи;
базы данных;
скорость работы;
оптимизация запросов;
миграция данных и пр.

При создании крупных веб-проектов потребуется использование тонких настроек MySQL и управление ее функциями в полном объеме. Когда веб-сайт подойдет к планке высоконагруженного ресурса, понадобится корректировать и тестировать my.ini - конфигурацию системы управления данными.

В среде Windows нередки случаи, когда трудно или просто невозможно выполнить импорт базы данных удобными средствами (например, phpMyAdmin), но всегда все можно сделать командной строкой.

Если однажды установленный и прекрасно работавший сервер лег, то первая причина этой проблемы - настройки my.ini (my.cnf для линуксоидов).

Традиции и особенности операционных систем

Установка MySQL может быт выполнена на ином порту (стандартно - 3306), а следовательно, нет необходимости сносить то, что уже стоит.

«Магические» пакеты и репозитории в линуксоидах - гарантия непрерывной обновляемости при предельно четком движении к цели: ни при каких обстоятельствах система не должна поддаваться панике.

Возможность установки, обновления и удаления, вплоть до автоматического, любого софта в среде Windows при движении по направлению «мы знаем, что нужно пользователю, и всегда можем ему помочь».

На основании данных обстоятельств с течением времени в недрах операционных систем накапливается мусор от инсталляций/деинсталляций программ, многие из них оставляют следы в системных реестрах, важных файлах, висят в рабочих процессах.

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

Установка MySQL предваряется удалением предыдущей установки:

и установкой пакетов:

vcredist_x64;
vc_redist.x64.

Удаление предыдущей версии производится из командной строки в режиме администратора. Не всегда доступно удалить предыдущую установку в разделе «Установка и удаление программ». Недостаточно просто остановить службу или почистить реестр.

Установка MySQL на Windows

Процесс не представляет проблем ни для профессионала, ни для новичка. Основное правило, которому следует доверять и следовать при установке: MySQL работает надежно и безукоризненно.

Вспомогательное правило: следует рассчитывать на установку из zip-архива и собственные силы. Использование лояльного и «самостоятельного» установщика MySQL - это только для знакомства с вопросом и процессом.

Только при установке посредством MySQL Installer будет возможность удалить продукт в разделе «Установка и удаление программ».

Установка Apache, MySQL, PHP на Windows - «веками» отработанный процесс. Все всегда работает стабильно, надежно, эффективно. Если что-то идет не так, значит, есть ошибка в файлах конфигурации или инициализации, незаслуженно забыт файл hosts, работает конфликтующее приложение, есть проблемы от предыдущей установки (служба, реестр).

Быстрый старт

Первый шаг: на официальном сайте скачать zip-архив нужной версии. Последняя на сегодня 5.7.21 и разархивировать ее.

Второй шаг: выбрать диск и папку, в которой будет находиться СУБД и ее базы данных. Лучше всего, когда установка Apache, MySQL и PHP выполняется в одном месте. Но это обстоятельство абсолютно не принципиально. Иное решение просто создаст трудности при исполнении реальных проектов. Доступ к папкам этих продуктов будет необходим время от времени и вспоминать, где что установлено - лишняя трата времени.

Третий шаг: написать файл «my.ini». Это очень важный файл, но для начала подойдет такой образец:

Это содержание файла позволяет запустить сервер без проблем. Если оставить указание только на папку MySQL и папке его данных, сервер также запустится, но стремится в начале установки планировать и проектировать my.ini не перспективно. Слишком много параметров, а понимать их использование без практики - не слишком перспективная идея.

Существенное обстоятельство: на просторах интернета можно найти тонну образцов my.ini. Важно смотреть на дату предлагаемого варианта. Мир так быстро меняется, что старые варианты важных файлов не всегда соответствуют свежим версиям программ.

Уточнение положения MySQL и установка

После того как zip-архив будет разархивирован, его следует дополнить папками:

scFiles;
scLog;
scTmp;
MySQL_DBs (самое главное!).

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

После того как все будет готово, следует запустить командную строку в режиме администратора и выполнить всего две команды. Следует обратить внимание: команды исполняются в папке bin пакета MySQL, в том месте куда он помещен.

Процедура не занимает много времени, но после завершения операции «mysqld --console» командная строка «висит». Следует запустить вторую командную строку в режиме администратора, чтобы установить пароль пользователя - root.

Временный пароль создается и выводится на экран при первой команде. Следует его записать, чтобы не повторять процесс еще раз. В данном примере временный пароль был oRJiT%Im5eBA.

После этих трех команд сервер «стал», но не готов к работе: MySQL - появился в списке служб. Нужно установить пароль для root, добавить пару администраторов и перезагрузить компьютер.

Финальная стадия установки

Установка MySQL завершена, для создания пароля root вновь запускается командная строка в режиме администратора (2).

Во втором окне вводится команда: mysql -u root -p. Здесь вызывается сервер, а не его демон. Будет предложено ввести пароль: нужно ввести ту временную строку, что была выдана ранее. В данном случае: oRJiT%Im5eBA.

Единственная первая и правильная команда - установка пароля для root:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'sc';

Вместо 'sc' - следует написать желаемый пароль с учетом требований безопасности, то есть не два простых символа, а что-то более-менее сложное. Следует обязательно написать в конце команды символ ";" - это команда! Этот символ обязателен.

В скриншоте показано добавление еще двух администраторов и передача им полных прав управления установленной системой MySQL.

На этом процедура завершена, она не сложнее, чем установка MySQL на Ubuntu, CentOS, FreeBSD или другой вариант линуксоида. Следует отметить: вариант установки под Windows - это простое использование мощного инструментального средства для создания и использования баз данных.

Качество, проверенное временем

MySQL практически не имеет конкуренции. Так сложилось: своя ниша, свои разработчики, свое направление развития. MySQL занимает свое собственное место в «реестре» популярных систем управления базами данных, идеально подходит для малых и больших проектов.

Установка Apache, MySQL и PHP на локальный компьютер - это своего рода квалификационный признак разработчика (программиста). Умение ставить LAMP и ориентироваться во всех параметрах конфигурационных файлов очень важно.

Отличное программирование на PHP не реально без уверенных знаний самого языка, системы управления базами данных MySQL и Apache. А знание «httpd.conf», «php.ini» и «my.ini» важно и существенно влияет на скорость, качество и надежность разработки.

Установка MySQL в связке с Apache и PHP - хорошая практика, его настройка на оптимальный режим работы - востребованное знание и умение.

fb.ru

Установки и настройка MySQL 5.5.23

Главная страница / Документация / Инструментарий веб-разработчика /

Статья описывает процесс установки и первоначальной настройки на локальном компьютере, работающим под операционной системой Windows XP, прекрасно зарекомендовавшей себя связки программ, используемых при создании, как крупных, так и средних веб-проектов: Apache, MySQL, PHP и phpMyAdmin.

Авторы: Виктор Волков, Иван Шумилов

Содержание:

Сайт разработчика: http://www.mysql.com/Документация: http://dev.mysql.com/doc/Дистрибутив: http://www.mysql.com/downloads/mysql/Прямая ссылка: mysql-5.5.23-win32.msi Скачайте самораспаковывающийся архив "Windows (x86, 32-bit), MSI Installer" и запустите его.

Установка MySQL в картинках

Далее будут показаны те диалоговые окна, в которых необходимо делать какой-либо выбор.

Нажмите в данном окне выборочную установку компонентов "Custom".

Здесь вы можете выбрать дополнительные компоненты и сменить установочную директорию программы.

Теперь приступим к настройке MySQL сервера.

Выбираем детализированную настройку - "Detailed Configuration".

Отмечаем пункт "Developer Machine". Мы ведь разработчики – правда? :)

Выбрав пункт "Multifunctional Database", вы сможете работать как с таблицами типа InnoDB (с возможностью использования транзакций), так и с высокоскоростной MyISAM (как правило для веб-разработок используется именно этот тип таблиц).

Выбор диска и директории для хранения таблиц типа InnoDB.

В данном диалоговом окне выбирается максимально возможное количество подключений к серверу MySQL. При выборе "Decision Support (DSS)/OLAP", максимальное количество подключений будет ограничено двадцатью, чего более чем достаточно при установке сервера на домашнем компьютере и отсутствии большого количества одновременных подключений.

Отметив "Enable TCP/IP Networking" мы включаем поддержку TCP/IP соединений и выбираем порт, через который они будут осуществляться. Стандартным для сервера MySQL является порт 3306. Отметив "Enable Strict Mode", мы задаем режим строгого соответствия стандарту SQL (данную опцию рекомендуется оставлять включенной).

Обратите внимание на выставление настроек данного окна. Отметив "Manual Selected Default Character Set / Collation" и выбрав из ниспадающего меню "cp1251" определяем, что изначально для таблиц будет использоваться кодировка Cyrillic Windows (cp1251), что означает корректную работу с русским языком в данной кодировке.

Если отметить "Install As Windows Service", сервер будет запускаться в виде сервиса, что является рекомендуемым способом запуска. Ниже, в ниспадающем списке, задается имя сервиса. Далее, уберите галочку рядом с "Launch the MySQL Server automatically" - мы будем запускать сервер вручную. Также поставьте галочку рядом с "Include Bin Directory in Windows PATH" - это позволит установить видимость директории "bin", для командной строки.

Установите пароль пользователя "root". Советую сделать это. Поставьте хотя бы какой-нибудь простенький пароль, только не оставляйте поле пустым, это убережёт вас от возможных неприятностей в дальнейшем.

В данном окне обратите внимание на строку "Write configuration file", которая указывает на месторасположение конфигурационного файла MySQL - "my.ini", далее, его необходимо будет немного отредактировать. Откройте для редактирования файл "my.ini".

В раздел [client], после строки:port=3306 Добавьте строку определяющую каталог содержащий файлы описания кодировок:character-sets-dir="C:/Program Files/MySQL/MySQL Server 5.5/share/charsets"
В раздел [mysqld], после строки:port=3306 Добавьте следующие две строки, первая из которых вам уже известна, вторая – устанавливает кодировку в которой данные передаются MySQL:character-sets-dir="C:/Program Files/MySQL/MySQL Server 5.5/share/charsets" init-connect="SET NAMES cp1251"
Далее, найдите строку:default-storage-engine=INNODB Замените изначально устанавливаемый тип таблиц на MYISAM:default-storage-engine=MYISAM

Сохраните изменения и закройте файл "my.ini". Установка и настройка сервера MySQL – завершена.

php-myadmin.ru

MySQL-тюнинг. Настраиваем по-взрослому. (Тонкая настройка)

MySQL-тюнинг. Настраиваем по-взрослому.

Итак, для начала благодарности. Выражается нереальная благодарность Олегу Копачовцу(он же Dr. Cop, http://www.kopachovets.com), за собранный материал и анализ фактов, а также за удачную подачу материала.Идея написания статьи витала в воздухе уже давно, вопрос правильной настройки сего загадочного зверька всегда вызывал у меня интерес. Мало кто знает, но правильно оттюненный MySQL может работать в 10-100 раз быстрее своего неоптимизированного собрата из базовой установки.Я человек не жадный, именно поэтому данная статья увидела свет. Итак, приступимс …

Поучение 1. Вы еще не стартовали MySQL? – Тогда мы идем к вам …

Если вы думаете, что вы стартовали демона MySQL и на этом ваша работа закончилась – вы оптимист.А уверены ли вы, что стартовали MySQL правильно? Да? Ну так давайте посмотрим, что стоило бы сделать в первую очередь …

Итак, предположим, что MySQL стартуется из init-скрипта, через демон mysqld_safe. Находим строку запуска MySQL, и что мы видим?

$bindir/mysqld_safe --datadir=$datadir --pid-file=$pid_file >/dev/null 2>&1 &

И чего? – спросите вы.

А вот чего, аккуратно добавляем к строке запуска следующие параметры:

–skip-name-resolve – Не производится разрешения имен хостов. Все значения в столбце Host в таблицах привилегий должны быть IP-адресами или значениями localhostЭто сильно увеличивая быстордействие запросов за счет выключения постоянных DNS запросов (до 1000% при “внешних” соединениях с mysql)–skip-locking – Нужно запускать mysqld с опцией –skip-locking. Запрет внешней блокировки существенно повысит скорость работы. Редко когда с одной базой работают одновременно 2 сервераОграничение: при запрете внешней блокировки нельзя будет использовать несколько серверов для работы с теми же базами данных.–low-priority-updates – INSERT/UPDATE в БД являются более низкоприоритетными, чем SELECT… Думаю для многих проектов это будет актуально, хотя пользоваться этим надо с умом.

$bindir/mysqld_safe --datadir=$datadir --pid-file=$pid_file --skip-name-resolve --low-priority-updates --skip-locking >/dev/null 2>&1 &

Поучение 2. Ну вроде бы запустились …

Мы конечно уже обрадовались, думается, ну вот же он – наш Database server, но-но, не тут то было, а как же my.cnf?Открываем /etc/my.cnf, и начинаем стучать в бубен (то есть определять конфигурационные переменные MySQL):

thread_concurrency. Если у вас много памяти и много таблиц, то для увеличения производительности, при запуске сервера рекомендуется использовать следующие формулы, учитывающие специфику работы mysql под различные ОС:

Для FreeBSD: thread_concurrency = (кол-во процессоров)*(кол-во ядер в одном процессоре)
Для Linux: thread_concurrency = (кол-во процессоров)*(кол-во ядер в одном процессоре)*3

Почему на Linux можно давать можно и нужно давать больше потоков, чем на FreeBSD? Это связано с тем, что Linux умеет распределять и управлять потоками между ядрами, а FreeBSD не умеет, зато FreeBSD умеет эффективно распределять процессы, чего, в свою очередь, не умеет Linux.

max_connections=4000 Разрешенное количество одновременно подсоединенных клиентов. Ставим 4000, чтобы не было казусов с “Too many connections…”, но стараемся поставить все таки меньше, так как, Mysql 5.0 все ещё использует select() вызов, а если хотим держать большое число соединений, то необходимо ставить mysql 6.0, который построен на libevent (epoll).Примечание: если вам хочется использовать больше 4000 коннектов к базе, вынужден вас огорчить, с этим есть проблема. Задать такое количество соединений, вы конечно сможете, но стандартные билды не позволяют использовать такое количество соединений. Вы конечно, можете скачать патчи, для повышения количества соединений, – но это не спасет Отца Русской демократии, даже в этом случае вы будете иметь ограничение порядка 7000 коннектов.

key_buffer=1024M Блоки индексов буферизированы и доступ к ним разрешен всем потокам. key_buffer – размер буфера, используемого для блоков индексов. Чтобы улучшить обработку индексов (для всех операций чтения и записи нескольких элементов), необходимо увеличить это значение настолько, насколько возможно. Рекомендуется выставлять это значение от 15% до 25% ОЗУ, чтобы система не начала сохранять временные файлы на диске, что значительно снизит производительность.Производительность буфера ключей можно проверить, выполнив команду show status LIKE "Key%"; и проверив значения переменных Key_read_requests, Key_reads, Key_write_requests и Key_writes. Отношение значений Key_reads/Key_read_request обычно должно быть < 0,01.Пример:

mysql> show status LIKE "Key%";

+------------------------+------------+

| Variable_name | Value |

+------------------------+------------+

| Key_blocks_not_flushed | 0 |

| Key_blocks_unused | 818569 |

| Key_blocks_used | 287552 |

| Key_read_requests | 3012333357 |

| Key_reads | 2435564 |

| Key_write_requests | 668018001 |

| Key_writes | 70927911 |

+------------------------+------------+

Заметка: Key_buffer является общим для всех потоков, все остальные буфера выделяются для каждого потока конкретно

key_buffer = 0,25 * Объему ОЗУ - поскольку у меня 4Gb оперативной памяти, мне не жалко отдать 1Gb для индексов.

table_cache=1024 – Количество открытых таблиц для всех потоков. С увеличением этого значения увеличивается количество дескрипторов файлов, необходимых для mysqld. Чтобы узнать, необходимо ли изменять значение кэша таблиц, следует проверить значение переменной Opened_tables в вашем сервере MySQL.

mysql> show status LIKE "Opened_tables%";+---------------+-------+| Variable_name | Value |+---------------+-------+| Opened_tables | 685 |+---------------+-------+

Итак, сейчас у нас 685 открытых таблиц, есть смысл задать этот параметр для нашего сервера в 1024.

sort_buffer=128M – Каждый поток, которому необходимо произвести сортировку, выделяет буфер данного размера. Увеличение данного значения позволит ускорить выполнение операторов ORDER BY или GROUP BY. “Увлекаться” большим значением не стоит, а посчитать его можно исходя из среднего значения открытых потоков (Threads_running) и кол-ва ОЗУ сервера.

mysql> show status LIKE "Threads_running%";+-----------------+-------+| Variable_name | Value |+-----------------+-------+| Threads_running | 4 |+-----------------+-------+

Текущее значение Threads_running равно 4, таким образом на буферы сортировки у нас выделяется в среднем 512Mb.

record_buffer=32M – Каждый поток, осуществляющий последовательное сканирование, выделяет буфер указанного размера для каждой сканируемой таблицы. Если проводится много последовательных операций сканирования, это значение можно увеличить. Адекватно оценить/подсчитать размер этого буфера можно исходя из данных о количестве прочитанных строк из таблиц mysql и объема данных в таблицах… Обычно рекомендуется принять его в 4-6 раз меньшим чем sort_buffer.

query_cache_limit=2M – Результаты, превышающие это значение, не кэшируются (по умолчанию – 1Мб). Зависит от типа извлекаемых данных из mysql. Если запросов много и в то же время преимущественно извлекается небольшое количество данных (1 Mb), то данное значение лучше уменьшить.

max_join_size=1000000 Это защита от кривых рук программиста, способного join`ом на 10 миллионов записей похоронить даже 4-х процессорный сервер.Объединения, которые потенциально могут считывать более max_join_size записей, будут возвращать ошибку. Это значение нужно задавать, если ваши пользователи осуществляют объединения, которым недостает оператора WHERE, – такие объединения занимают много времени, а затем возвращают миллионы строк.

max_sort_length=20 – Защита от кривых мозгов архитектора БД, когда не стоят адекватные лимиты по индексам сортировки текстовых полейПараметр определяет, сколько байтов следует использовать при сортировке значений BLOB или TEXT (обрабатываются только первые max_sort_length байтов каждого значения, остальные игнорируются).

thread_cache_size=64 Определяет, сколько потоков должно сохраняться в кэше для повторного использования. После отключения клиента потоки клиента помещаются в кэш, если там не больше потоков, чем thread_cache_size. Все новые потоки сначала берутся из кэша, и только когда кэш становится пустым, создаются новые потоки. Значение этой переменной можно увеличить, чтобы повысить производительность, если создается много новых соединений (если потоки у вас хорошо организованы, обычно заметного улучшения производительности не наблюдается). Насколько эффективен текущий кэш потоков, можно определить по разнице между Connections и Threads_created. Если есть возможность, рекомендуется установить это значение не меньше, чем значение переменной Max_used_connections. Если значение этой переменной больше 128, рекомендуется ограничиться этим значением.

myisam_sort_buffer_size=512М – Буфер, который выделяется для сортировки индексов при выполнении команды REPAIR или для создания индексов при помощи команд CREATE INDEX или ALTER TABLE. Рекомендуется не жадничать …

net_read_timeout=12 – Количество времени в секундах, на протяжении которого ожидаются дополнительные данные от соединения, пока не будет отменено чтение. Обратите внимание, что мы не ожидаем поступления данных от соединения, время ожидания определяется по write_timeout.

net_write_timeout=15 – Время ожидания записи блока через соединение, пока запись не будет прервана (в секундах).

wait_timeout=30 – Время в секундах, на протяжении которого сервер ожидает активности соединения прежде, чем закрыть его.Примечание: мы, предполагаем, что наша система очень динамична, и висеть конекшенам по несколько часов не требуется, 30 секунд достаточно даже для очень медленных запросах от Web-приложения.

interactive_timeout=600 – Количество времени в секундах, на протяжении которого сервер ожидает активности со стороны интерактивного соединения, прежде чем закрыть его. Интерактивный клиент – это клиент, который использует параметр CLIENT_INTERACTIVE для mysql_real_connect(). См. также информацию по wait_timeout.

long_query_time=30 – Если обработка запроса отнимает больше указанного промежутка времени (в секундах), значение счетчика Slow_queries будет увеличено. Если используется параметр –log-slow-queries, запрос будет записан в журнал медленных запросов.Значение этого параметра должно быть примерно равно time_limit скрипта php или временно лимиту операции выдачи, т.к. часто получаются ситуации когда PHP-скрипт уже вылетел по time_limit, а бендненькое умирающее животное MySQL все еще корчится в конвульсиях над запросом по группировке 10 млн записей.

Поучение 3. А не выпить ли нам таблеток от Склероза?

Кэш в MySQL называется QuickCache или он же QCache. Ошибочно мнение, что эффективность использования кэша это отношение хитов в кэш к инсертам в кэш. Все немного сложнее. Эффективность попадания в кэш можно оценить вот таким показателем:

qcache_hit_ratio = qcache_hits / (qcache_hits + qcache_inserts + qcache_not_cached)

Посмотреть эти значения можно следующим образом:

mysql> SHOW STATUS LIKE 'Qcache%';+-------------------------+--------+| Variable_name | Value |+-------------------------+--------+| Qcache_free_blocks | 36 || Qcache_free_memory | 138488 || Qcache_hits | 79570 || Qcache_inserts | 27087 || Qcache_lowmem_prunes | 3114 || Qcache_not_cached | 22989 || Qcache_queries_in_cache | 415 || Qcache_total_blocks | 912 |+-------------------------+--------+

Если это значение > 0.8, то значит 80% ваших запросов попадают в кэш, это очень хороший показатель.Если % попадания в кэш низкий, то необходимо увеличить значение query_cache_size.Текущее значиние можно посмотреть так:

SHOW VARIABLES LIKE 'query_cache_size';

Опять же возникает вопрос: как выбрать адекватное значение query_cache_size?В этом поможет Qcache_lowmem_prunes. В этой переменной хранится число запросов, которые были убраны из кэша из-за необходимости кэширования новых запросов. Необходимо стремится к такому размеру кэша, при котором Qcache_lowmem_prunes будет лишь незначительно увеличиваться. Для этого, рекомендуется сравнить разницу значений Qcache_lowmem_prunes за час и кол-во запросов, поступивших на mysql за этот же час.На практике, для расчета query_cache_size можно использовать одну из 2-х формул:

query_cache_size = (число запросов за 10 минут)*(срений объем ответа на запрос) * 1,2илиquery_cache_size = (объем трафика за 10 минут) * 1,2

Это позволит закэшировать запросы на 10 минут + дать дополнительные 20% памяти на фрагментацию кэша и дополнительный резерв кэшированияПодсчитать количество и средний объем ответа на запроса можно использую переменные Bytes_sent соответственно

И так значения query_cache_size мы увеличили, после чего стоит обратить внимание на значения Qcache_total_blocks, Qcache_free_blocks и Qcache_queries_in_cache. MySQL хранит кэш в блоках. На 1 запрос необходимо 2 блока: один для самого текста запроса, второй для результата.Если рассмотреть таблицу со значения Qcache%Общее количество блоков кэша Qcache_total_blocks – 912Закешировано сейчас 415 запрос, а значит занят 415*2 = 830 блоковсвободно блоков Qcache_free_blocks – 36. Чем больше незадействованных Qcache_free_blocks, тем больше степень “фрагментации” кэша.Если большинство запросов имеют небольшой объем результирующих данных, то стоит уменьшить минимальный размер блока кэша query_cache_min_res_unit, который по умолчанию равен 4 Кб.Если же большинство запросов возвращают много данных – то стоит увеличить размер блока кэша.Главное – это добится минимального значения Qcache_free_blocks.

Если же случилось непоправимое, и ваш кеш вас подводит, рекомендуется не забыть про следующие команды MySQL:FLUSH QUERY CACHE – дефрагментировать кэшRESET QUERY CACHE – очистить кэш

Поучение 4. СМЕРШ. Ищем злодеев и вредителей.

Без комментариев:

–log-slow-queries=/var/log/slow_queries

Перед этим, стоит помочь MySQL (на всякий случай):#touch /var/log/slow_queries#chmod 777 /var/log/slow_queries

Рекомендуется ежедневно смотреть лог медленных запросов через less или же пользоваться mysqldumpslow.

Примечание: –log-long-format позволяет заносить в лог запросы, не использующие индексов. Это также полезная информация, для поиска внутренних врагов.

Поучение 5. Подсматриваем и вынюхиваем.

Даже если ваши руки настроили MySQL как конфетку, НЕ ЗАБЫВАЙТЕ О МОНИТОРИНГОВОМ программном обеспечении, – целее будете …

Поучение 6. Что делать и Кто виноват?

И так в один прекрасный момент мы видим, что очень медленно открываются страницы нашего драгоценного сайта…При выяснении причин и при просмотри top сервера обнаруживаем, что паршивой овцой в нашей боевой linux-связке оказался таки MySQL. Главное спокойно и без паники.

Админы с поверхностным представлением об MySQL сделают “service mysql restart” и на этом успокоятся… Вот только, минут через 10-15, как правило, MySQL опять начнет бится в конвульсиях.

Админы с более продвинутой встроенной логикой начнут делать mysqladmin processlist и пытатся выловить кто же мучает их MySQL и найти причину. Не спорю метод эффективен, но может оказаться что сайт развился до такой степени что ему уже просто не хватает MySQL ресурсов.

Итак, что же мы будем делать в сутации когда наш верный конь MySQL захромал?

таки да… “show processlist;” – и попытаться найти первопричину загрузки. По возможности ослабить её или устранить. Возможно какие-то боты “долбят” ваш сервер
если таки причина не устранена пробуем сделать: mysqladmin flash-tables . Такое действие поможет закрыть устаревшие дескрипторы для таблиц и таким образом можно вправить МОСК своему любимому MySQL
FLUSH QUERY CACHE – дефрагментировать кэш
service mysql restart

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

webhamster.ru

Что нужно настроить в mySQL сразу после установки? / Хабр

Вольный перевод довольно старой статьи с MySQL Performance Blog о том, что лучше сразу же настроить после установки базовой версии mySQL. Удивительно, сколько народу устанавливает mySQL на свои сервера и оставляют его с настройками по умолчанию.

Несмотря на то, что в mySQL существует довольно много настроек, которые Вы можете изменить, есть набор действительно очень важных характеристик, которые обязательно нужно оптимизировать под собственный сервер. Обычно после такой небольшой настройки производительность сервера заметно увеличивается.

key_buffer_size — крайне важная настройка при использовании MyISAM-таблиц. Установите её равной около 30-40% от доступной оперативной памяти, если используете только MyISAM. Правильный размер зависит от размеров индексов, данных и нагрузки на сервер — помните, что MyISAM использует кэш операционной системы (ОС), чтобы хранить данные, поэтому нужно оставить достаточно места в ОЗУ под данные, и данные могут занимать значительно больше места, чем индексы. Однако обязательно проверьте, чтобы всё место, отводимое директивой key_buffer_size под кэш, постоянно использовалось — нередко можно видеть ситуации, когда под кэш индексов отведено 4 ГБ, хотя общий размер всех .MYI-файлов не превышает 1 ГБ. Делать так совершенно бесполезно, Вы только потратите ресурсы. Если у Вас практически нет MyISAM-таблиц, то key_buffer_size следует выставить около 16-32 МБ — они будут использоваться для хранения в памяти индексов временных таблиц, создаваемых на диске.
innodb_buffer_pool_size — не менее важная настройка, но уже для InnoDB, обязательно обратите на неё внимание, если собираетесь использовать в основном InnoDB-таблицы, т.к. они значительно более чувствительны к размеру буфера, чем MyISAM-таблицы. MyISAM-таблицы в принципе могут неплохо работать даже с большим количеством данных и при стандартном значении key_buffer_size, однако mySQL может сильно «тормозить» при неверном значении innodb_buffer_pool_size. InnoDB использует свой буфер для хранения и индексов, и данных, поэтому нет необходимости оставлять память под кэш ОС — устанавливайте innodb_buffer_pool_size в 70-80% доступной оперативной памяти (если, конечно, используются только InnoDB-таблицы). Относительно максимального размера данной опции — аналогично key_buffer_size — не стоит увлекаться, нужно найти оптимальный размер, найдите лучшее применение доступной памяти.
innodb_additional_mem_pool_size — данная опция практически никак не влияет на производительность mySQL, однако рекомендую оставлять для InnoDB около 20 МБ (или чуть больше) под различные внутренние нужды.
innodb_log_file_size — крайне важная настройка в условиях баз данных с частыми операциями записи в таблицы, в особенности при больших объёмах. Большие размеры увеличивают быстродействие, однако будьте осторожны — увеличится и время восстановления данных. Я обычно выставляю значение около 64-512 МБ в зависимости от размера сервера.
innodb_log_buffer_size — стандартное значение данной опции вполне подойдёт для большинства систем со средним количеством операций записи и небольшими транзакциями. Если же в Вашей системе бывают всплески активности, или Вы активно работаете с BLOB-данными, то рекомендую немного увеличить значение innodb_log_buffer_size. Однако не переусердствуйте — слишком большое значение будет пустой тратой памяти: буфер сбрасывается каждую секунду, поэтому Вам не понадобится больше места, чем требуется в течение этой секунды. Рекомендуемое значение — около 8-16 МБ, а для небольших баз — и того меньше.
innodb_flush_log_at_trx_commit — жалуетесь, что InnoDB работает в 100 раз медленнее MyISAM? Вероятно, Вы забыли про настройку innodb_flush_log_at_trx_commit. Значение по умолчанию «1» означает, что каждая UPDATE-транзакция (или аналогичная команда вне транзакции) должна сбрасывать буфер на диск, что достаточно ресурсоёмко. Большинство приложений, в особенности ранее использовавшие таблицы MyISAM, будут хорошо работать со значением «2» (т.е. «не сбрасывать буфер на диск, только в кэш ОС»). Лог, однако, всё равно будет сбрасываться на диск каждые 1-2 секунды, поэтому в случае аварии Вы потеряете максимум 1-2 секунды обновлений. Значение «0» повысит производительность, но Вы рискуете потерять данные даже при аварийной остановке mySQL-сервера, в то время как при установке значение innodb_flush_log_at_trx_commit в «2» Вы потеряете данные только при аварии всей операционной системы.
table_cache — открытие таблиц может быть весьма ресурсоёмко. К примеру, MyISAM-таблицы помечают заголовки .MYI файлов как «используемые в текущий момент». Обычно не рекомендуется открывать таблицы слишком часто, поэтому лучше, чтобы кэш был достаточных размеров, чтобы держать все Ваши таблицы открытыми. Для этого используется некоторое количество ресурсов ОС и оперативной памяти, однако это обычно не является существенной проблемой для современных серверов. Если у Вас несколько сотен таблиц, то стартовым значением для опции table_cache может быть«1024» (помните, что каждое соединение требует свой собственный дескриптор). Если у Вас ещё больше таблиц или очень много соединений — увеличьте значение параметра. Я видел mySQL сервера со значением table_cache равной 100 000.
thread_cache — создание/уничтожение потоков также является ресурсоёмкой операцией, которая происходит при каждой установке соединения и каждом разрыве соединения. Я обычно выставляю эту опцию равную 16. Если у Вашего приложения могут быть скачки количество конкурентных соединений и по переменной Threads_Created виден быстрый рост количества потоков, то стоит увеличить значение thread_cache. Цель — не допускать создания новых потоков в условиях нормального функционирования сервера.
query_cache_size — если Ваше приложение много и часто читает данные, и при этом у Вас нет кэша на уровне приложения, эта опция может очень помочь. Не ставьте здесь слишком большое значение, так как обслуживание большого кэша запросов будет само по себе затратным. Рекомендуемое значение — от 32 до 512 МБ. Не забудьте проверить, насколько хорошо используется кэш запросов — в некоторых условиях (при небольшом количестве хитов в кэше, т.е. когда практически не выбираются одинаковые данные) использование большого кэша может ухудшить производительность.

Как Вы можете видеть, это — глобальные настройки. Эти переменные зависят от «железа» сервера и используемых движков mySQL, в то время как сессионные переменные обычно настраиваются специально под конкретные задачи. Если Вы в основном используете простые запросы, то нет никакой необходимости увеличивать значение sort_buffer_size, даже если у Вас есть лишние 64 ГБ оперативной памяти. Более того, большие значения кэшей могут только ухудшить производительность сервера. Сессионные переменные лучше оставить на потом, для тонкой настройки сервера.

PS: инсталляция mySQL идёт с несколькими предустановленными файлами my.cnf, рассчитанными под разную нагрузку. Если Вам некогда настраивать сервер вручную, то обычно лучше использовать их, чем стандартный конфигурационный файл, выбрав тот, что больше подойдёт под нагрузку Вашего сервера.

habr.com

MySQL сервер. Файл конфигураций my.ini. Настройка кодировки MySQL

Здравствуйте, уважаемые посетители моего скромного блога для начинающих вебразработчиков и web мастеров ZametkiNaPolyah.ru. Продолжим сегодня рубрику Заметки о MySQL, в которой я уже успел рассказать о том, как установить MySQL сервер и как настроить сервер баз данных. Сегодня я продолжу рассказывать о настройках сервера MySQL. В данной статье мы разберемся со следующими вопросами: куда устанавливается MySQL сервер, где найти базы данных MySQL сервера, как найти базы данных MySQL, для чего нужен файл my.ini, посмотрим примеры настройки сервера MySQL и где найти примеры настройки MySQL сервера.

Разберемся с кодировкой MySQL сервера. Какую кодировку лучше использовать. Разберемся с командами MySQL сервера для смены кодировки. Так же затронем вопрос о включение и выключение MySQL, обратите внимание, что MySQL сервер не перезагружается. Посмотрим, какие возможности есть у сервера MySQL. Разберемся где лежат таблицы перекодировок MySQL сервера и как их добавить. А также сделаем настройки файла my.ini.

MySQL сервер. Настройка MySQL сервера, файл конфигурации my.ini, примеры настройки MySQL сервера.

Содержание статьи:

Как я уже говорил, с установкой и предварительной настройкой MySQL сервера мы разобрались, теперь давайте посмотрим, хотя бы поверхностно, из чего состоит MySQL сервер. Какие файлы за что отвечают. MySQL сервер, в моем случае был установлен по данному пути: c:\Users\Public\MySQL\, кто-то мог установить сам сервер в папку Program Files, обратите внимание, что этот путь не указывает, то место, где хранятся базы данных MySQL сервера, здесь находится сам сервер баз данных.

На скрине можно увидеть несколько файлов и папок сервера MySQL, нас собственно большая часть этих файлов не волнует.

Основной файл конфигурации MySQL сервера my.ini, это обычный текстовый файл, в который были вписаны настройки, которые мы вводили при установке MySQL сервера. Все остальные установленные файлы с расширением .ini – это всего лишь демонстрации настроек для my.ini, работает только my.ini, то есть, все остальные файлы существуют, как примеры для конфигурации и настройки MySQL сервера. Проще говоря, пример, как настроить MySQL сервер.

Программы MySQL сервера, какой файл для чего нужен

Где лежат примеры настроек MySQL сервера мы разобрались. Сами программы, предназначенные для обслуживания баз данных, находятся в папке bin.

Сам сервер MySQL – это mysqld.exe, d – означает демон, ну или служба. Про некоторые другие утилиты и программы мы поговорим в дальнейшем более подробно. Если у вас стоит win xp, то базы данных будут находиться c:\Documents and Settings\All Users\Application Data\MySQL\ MySQL Server 5.x\data, если вы пользуетесь total commander или любым другим нормальным файловым менеджером, то без труда найдете эту папку, если вы пользуетесь проводником от Windows, то обязательно укажите в настройках «Отображать скрытые файлы», так как данная папка скрыта.

На самом деле все это можно настроить в файле my.ini и реально эти папки могут находиться, там где вам будет удобнее. Файл my.ini нужно будет разобрать более подробно, и тема эта для отдельной статьи.

Кодировка MySQL сервера. SET NAMES — команда для смены кодировки. Кодировка командной строки Windows.

И так, теперь немного поговорим о кодировке сервера MySQL и кодировке командной строки Windows. Если вы будете пользоваться MySQL Command Lint Client, то проблем с кодировкой у вас возникнуть не должно.

Данный клиент установиться вместе с сервером и работа в нем не отличается от работы с командной строкой Windows. Для начала, вы вводите пароль, придуманный вами при установки MySQL сервера, а затем, не заморачиваясь с кодировками и командами типа SET Names, начинаете работать с базами данных: создавать новые базы данных, удалять базы данных, добавлять строки и столбцы к существующим таблицам баз данных, делать выборки из баз данных, создавать и удалять таблицы и многое-многое другое.

Но если вы решили управлять сервером MySQL через командную строку, то знайте, что кодировка командной строки Windows отличается от кодировки MySQL сервера, в командной строке – это cp866, MySQL сервер в моем случае работает с кодировкой UTF8. И это нужно исправить, кодировку командной строки поменять мы не можем. Остается менять кодировку, с которой работает сервер MySQL.

Как изменить кодировку MySQL сервера. Как получить доступ к серверу MySQL через командную строку Windows.

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

Выглядит все это примерно так:

Не забудьте точку с запятой, этот символ означает конец команды.

Чуть было не забыл, если вы пользуетесь командной строкой, то не забудьте, что MySQL сервер запускается путем написания команды mysql –uroot –pпароль, соответственно, после буквы u вы указываете пользователя сервера MySQL, а после буквы p – пароль. Пример на скрине:

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

По сути, вводя команду SET NAMES, мы как бы говорим серверу: «В данном сеансе(или иначе подключении) я буду работать с тобой вот в этой кодировки». То есть, из этого следует, что при каждом новом подключении к серверу MySQL, нам потребуется постоянно вводить команду SET NAMES и указывать кодировку, с которой необходимо работать. Удобно? Мне кажется, что не очень. Никаких SET NAMES при работе с консолью от MySQL вводить не надо.

Настройка MySQL сервера. Файл конфигураций my.ini. Настройка кодировок MySQL сервера.

И так, чтобы не париться с кодировкой, нужно настроить MySQL сервер. Настройки сервера производятся в файле my.ini. Открывайте его текстовым редактором, я пользуюсь Notepad++, очень удобный редактор, его легко настроить, есть подсветка синтаксиса, а самое главное – его можно скачать бесплатно.

Обратите внимание, что my.ini состоит из разделов, первый – Client, второй – mysql, третий – mysqld. Раздел mysqld – отвечает за настройку сервера MySQL. Mysql – это настройка черного окошечка, в котором собственно и будем работать. Раздел client – это настройки по умолчанию для всех клиентов MySQL сервера.

Обратите внимание, на скриншоте выделена кодировка, которая стоит для работы в консоле, если вы будете пользоваться консолью от MySQL, то здесь ничего не меняйте, если вы предпочитаете командную строку Windows, то кое какие изменения сделать придется, дабы постоянно не писать SET NAMES. Как видно, кодировка для работы с окном DOS стоит utf8, но это не правда, так как в черном окне у нас кодировка cp866. То есть, первое, что надо поменять – это вместо utf8 написать cp866.

default-character-set=cp866

Таблицы перекодировок MySQL сервера. Где находятся таблицы перекодировок и куда их прописать.

Но этого будет недостаточно. Потому что мы пока не указали, где лежат таблицы перекодировки сервера MySQL. То есть, грубо говоря, консоль пока не знает, как переводить из одной кодировки в другую. Программе надо указать, где лежат таблицы перекодировки, а лежат они собственно в самом сервере MySQL, в папке Share, в папке charsets. Путь выглядит примерно так:

c:\Users\Public\MySQL\MySQL Server 5.5\share\charsets\

В этой папке много файлов с расширением XML, про язык расширяемой разметки XML, уже есть несколько публикаций в рубрике Заметки о XML. Именно в папке charsets вы можете посмотреть, какие кодировки поддерживает MySQL сервер.

В качестве примера можно привести Денвер – джентльменский набор web разработчика. У многих возникают проблемы типа: у меня на Денвере не работает кодировка UTF8, что делать? Ответ: для начала загляните в папку charsets Денверовского MySQL сервера, и если там нет файла utf8.xml, то понятно, что он и не будет поддерживать эту кодировку.

Понятно, что командная строка Windows не знает, где лежат таблицы перекодировок и ей это нужно указать. Делается это все в том же my.ini, в разделе mysql указывается папка, в которой хранятся таблицы перекодировок, при помощи команды character-sets-dir=””, между двойными кавычками нужно вписать путь к папке, в которой лежат таблицы перекодировок.

Не забудьте, что в разделе Client нужно указать кодировку – там необходимо указать utf8, а также прописать путь к таблицам сравнения, на всякий пожарный.

Итоговая настройка my.ini будет выглядеть примерно так:

[client] character-sets-dir="c:\Users\Public\MySQL\MySQL Server 5.5\share\charsets" default-character-set=utf8 port=3306 [mysql] character-sets-dir="c:\Users\Public\MySQL\MySQL Server 5.5\share\charsets" default-character-set=cp866

[client]

character-sets-dir="c:\Users\Public\MySQL\MySQL Server 5.5\share\charsets"

default-character-set=utf8

port=3306

[mysql]

character-sets-dir="c:\Users\Public\MySQL\MySQL Server 5.5\share\charsets"

default-character-set=cp866

Но если вы будете использовать консоль от MySQL, то внесите изменения только в раздел client, и то не обязательно, хотя папку с таблицами перекодировки лучше указать в двух разделах:

[client]

character-sets-dir="c:\Users\Public\MySQL\MySQL Server 5.5\share\charsets"

default-character-set=utf8

port=3306

[mysql]

character-sets-dir="c:\Users\Public\MySQL\MySQL Server 5.5\share\charsets"

default-character-set=utf8

Ну а вот скрин из редактора, тут прописаны настройки для работы с сервером MySQL через командную строку:

Все эти настройки и команды означают буквально следующее: character-sets-dir – этой строкой мы как бы говорим, в первом случае, клиенты вы берете таблицы вот отсюда и указываете, откуда они берут таблицы перекодировок. default-character-set=cp866, этой строкой вы как бы говорите консоли, ты будешь использовать кодировку cp866. Да чуть не забыл, все слэши нужно использовать в UNIX виде, то есть, в примерах слеши написаны не правильно их нужно развернуть вот так — /. В Windows без разницы, какие вы будете использовать разделители, но если вы пользуетесь UNIX системами, то слеши нужно будет развернуть.

Как включить MySQL сервер. Как выключить MySQL сервер. Что нужно сделать, чтобы новые настройки сохранились.

После того, как вы внесли изменения в my.ini, нужно перезагрузить MySQL сервер, команды рестарт, как в случае с Apache тут нет. Придется выключить и заново включить сервер. Сделать это можно из командной строки, используя две команды. Первая net stop – предназначена для выключения службы, вторая – net start, которая предназначена для включения службы. Служба у нас MySQL сервер, следовательно, для него эти команды будут выглядеть так:

net stop mysql net start mysql

net stop mysql

net start mysql

Обратите внимание, что никаких точек с запятой после этих команд ставить не надо, как в случае с Set NAMES. Да и перед тем, как перезагрузить MySQL сервер, не забудьте, что нужно выйти из аккаунта управления сервером MySQL, чтобы это сделать есть команда exit.

Командой exit мы вышли из MySQL сервера, а командой net stop mysql мы выключили MySQL сервер. Когда вы в следующий раз включите MySQL сервер, командой net start mysql, а затем и войдете, как администратор сервера то никаких SET NAMES в командной строке Windows писать уже не надо, так как все уже указано в файле my.ini. Еще одна маленькая помарка, все эти настройки избавляют вас от того, чтобы постоянно вводить SET NAMES в командной строке, но не избавляет вас от необходимости вводить SET NAMES, в случае, когда вы обращаетесь к серверу MySQL при написание скриптов на PHP или любом другом языке программирования.

Также не забудьте, если настроили MySQL сервер под работу в командной строке, то вам придется постоянно указывать кодировку для работы с сервером в консоли от MySQL – MySQL Command Client.

На этом всё, спасибо за внимание, надеюсь, что был хоть чем-то полезен и до скорых встреч на страницах блога для начинающих вебразработчиков и вебмастеров ZametkiNaPolyah.ru

zametkinapolyah.ru

Настройка MySQL + оптимизация InnoDB

Первоначальная настройка mysql-server

Мой любимый вопрос, задаваемый DBA, которые хотят увеличить производительность MySQL: “какие параметры надо настраивать в первую очередь, сразу после установки сервера?”Я удивлен количеством людей, которые не могут дать ответа на этот вопрос. И еще более удивлен количеством серверов, которые работают с настройками по умолчанию

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

key_buffer_size - очень важный параметр, если вы используете MyISAM-таблицы. Установите его равным 30-40% от имеющейся оперативной памяти, если вы используете только MyISAM. Актуальное для вашей системы значение зависит от индексов, размера данных и рабочего процесса. Помните, MyISAM использует кэш операционной системы для хранения данных и вам необходимо позаботиться о достаточных размерах выделяемой памяти. Во многих случаях, объем данных может быть значительно больше. Проверьте, не используется ли завышенное значение key_buffer. Нередко параметр установлен в значение 4GB при суммарном объеме .MYI-файлов в один гигабайт. Это напрасная трата ресурсов. Если вы используете несколько таблиц MyISAM - понизьте значение этого параметра. Но не опускайте его ниже 16-23 MB - этого будет достаточно для размещения индексов временных таблиц, которые создаются на диске.

innodb_buffer_pool_size - это очень важный параметр для настройки InnoDB. Таблицы этого типа гораздо более чувствительны к размеру буфера, нежели MyISAM. MyISAM может нормально работать даже при дефолтном значении buffer_size, в отличии от InnoDB, производительность которых будет заметно ниже при значении innodb_buffer_pool_size по умолчанию и больших объемах данных. Также пул буферов InnoDB самостоятельно кэширует индексы и данные, так что не нужно оставлять место для кэша ОС. Обычно предполагается выделение 70 - 80% памяти для серверов, на которых ничего не запущено, кроме InnoDB. Некоторые правила key_buffer применимы и в этом параметре: если у вас небольшие объемы данных и они не собираются стремительно увеличиваться, не завышайте значение innodb_buffer_pool_size, вы сможете найти свободной оперативной памяти лучшее применение. innodb_additional_mem_pool_size - этот параметр не имеет сильного влияния на производительность. По крайней мере в операционных системах с грамотным распределением памяти. Но вы можете установить значение этого параметра раным 20MB (иногда больше) и вы можете видетm сколько памяти выделяет InnoDB для различных нужд.

innodb_log_file_size - очень важный параметр для систем с интенсивной записью, особенно больших объемов данных. Увелечение значения этого параметра обычно дает прирост производительности, но будьте осторожны. Обычно я использую значения 64M - 512 MB в зависимости от сервера.

innodb_log_buffer_size - значение по умолчанию вполне подойдет для большинства проектов со средней интенсивностью записи и короткими транзакциями. Однако если у вас бывают пики активности или работа с большим объемом данных, вы, вероятно, захотите увеличить значение этого параметра. Не делайте его слишком большим, это повлечет лишний расход памяти. Буфер сбрасывается каждую секунду и вам не нужен бОльший объем памяти. Обычно вполне хватает 8 - 16МB. Чем меньше система - тем меньше должно быть значение.

innodb_flush_logs_at_trx_commit - Вам кажется, что InnoDB в сто раз медленнее MyISAM? Вероятно, вы забыли изменить значение этого параметра. Значение по умолчанию 1 означает, что после каждой завершенной транзакции (или после изменения состояния транзакции) лог должен быть сброшен на диск. Это достаточно дорогая операция, особенно если у вас нет Battery backed up cache. Многие приложения, особенно те, в которых раньше использовался MyISAM будут хорошо работать при значении 2, который означает, что не надо сбрасывать буфер на диск, а следует отправить его в кэш операционной системы. Лог по-прежнему будет сбрасываться на диск каждую секунду и максимум, что вы можете потерять - это 1-2 секунды записей. Значение 0 обеспечивает более высокую скорость, но и более низкую надежность. Есть вероятность потерять транзакции даже при падении mysql-сервера. При значении равном 2 единственная возможность потерять данные - это фатальный сбой операционной системы.

table_cache - открытые таблицы могут разрастаться. Например, таблицы MyISAM помечают MYI-заголовок, как используемый. Вы, конечно же, не хотите, чтобы это происходило слишком часто и это, как правило, хорошее решение. Лучше увеличить размер кэша, чтобы он мог вместить большинство открытых таблиц. Кэш использует некоторое количество памяти и ресурсов ОС, но для современной техники это, как правило, не проблема. Значение 1024 будет оптимальным решением для нескольких сотен открытых таблиц (помните, каждое соединение нуждается в собственной копии). Если у вас много соединений или большое количество открытых таблиц - увеличьте это значение. Я видел системы со значением этого параметра > 100.000

thread_cache - Создание/уничтожение нитей (threads) может ухудшать производительность, особенно если они создаются/уничтожаются при каждом соединении/разъединении. Обычно я устанавилваю значение этого параметра равным 16. Если приложение делает большие прыжки в параллельных соединениях, то можно увидеть, как быстро растет переменная Threads_Created. Параметр предназначен для того, чтобы не создавать новых нитей в нормальных операциях.

query_cache_size - если ваше приложение читает много данных и у вас нет кэша на уровне приложения, этот параметр может неплохо помочь. Но не устанавливайте слишком большого значения - это может замедлить работу и содержание такого кэша может обойтись довольно дорого. Оптимальные значения - от 32MB до 512MB. Тем не менее, проверьте эффективность работы кэша через некоторое время. Вполне возможно, что текущее значение слишком велико.

Примечание: как вы заметили, все эти переменные являются глобальными и зависят от аппаратного обеспечения и устройств хранения данных. Сессионные переменные зависят от специфики конкретного проекта. Если у вас простые запросы, вам совершенно не нужно увеличивать параметр sort_buffer_size, даже если в вашем распоряжении 64GB оперативной памяти. Кроме того, это может снизить производительность. Обычно я оставляю настройку сессионных переменных на второй шаг, уже после оценки фронта работ.

P.S.: в дистрибутиве MySQL есть отличные примеры файла my.cnf для систем различных размеров. Они могут использоваться в качестве базы для ваших собственных файлов конфигурации, главное правильно выбрать шаблон.

Основы оптимизации производительности InnoDB

Проводя опрос среди посетителей раздела Job Opening я задавал им один простой вопрос: если бы у вас был сервер с 16GB RAM, который был бы предназначен для MySQL-сервера с очень большим объемом innodb-таблиц, работающий с стандартным веб-проектом, какие бы настройки вы скорректировали? И самое интересное, что большинство не смогло четко ответить. Поэтому и было принято решение опубликовать эту заметку, которая, возможно, расширит ваши знания об оптимизации программной и аппаратной частей сервера.

Я назвал эту статью Основы оптимизации производительности InnoDB, желая подчеркнуть, что это именно основы. Это универсальные советы, работающие на большинстве систем. Но более точную настройку необходимо производить, исходя из конкретных поставленных задач.

Hardware

Если у вас есть база данных innodb большого объема, и это действительно важные данные, то 16 - 32 GB оперативной памяти будут оптимальным решением. Процессоры - 2*DualCore подойдут для большой нагрузки, а два Quad Core помогут решить проблемы с дальнейшим масштабированием системы. Хотя имеется множество нюансов. Третий момент - это подсистема ввода/вывода. Напрямую подключенный DataStorage с большим количеством дисков и RAID с возможностью сохранения кэша будут отличным выбором. Обычно необходимо 6 - 8 жестких дисков в стандартный блок, но порой может понадобиться и больше. Также обратите внимание на новые 2.5" SAS диски. Они меньше, но часто работают гораздо быстрее, чем обычные HDD. RAID10 хорошо подходит как для хранения, так и для чтения данных, но в случае, если вы можете позволить некоторую избытычность. В противном случае можно сделать RAID5, но опасайтесь случайных записей.

Операционная система

Дя начала: установите 64-битную операционную систему. Часто можно увидеть 32-битный linux, или запущенный в режиме совместимости с 64-bit. Не делайте так. Если вы используете LVM для хранения базы данных, вы сможете более эффективно работать с резервными копиями. Файловая система Ext3 будет оптимальным выбором в большинстве случаев, но если вы запускаете particular roadblocks, то попробуйте XFS. Вы можете использовать опции noatime и nodiratime, если вы используете innodb_file_per_table и большое количество таблиц, но это, в принципе, не столь важно. Также убедитесь, что OS резервирует достаточно большое количество памяти для MySQL.

Опции MySQL InnoDB

Важнейшими опциями являются:innodb_buffer_pool_size - 70 - 80% оперативной памяти. Я ставлю это значение в 12G на системе с 16G RAMinnodb_log_file_size - зависит от необходимого вам объема данных для восстановления, но 256МБ будут разумным компромиссом между производительностью и рамером лог-файлаinnodb_log_buffer_size=4M - 4 мегабайта - нормальное значение, если вы не используете подачу больших блоков данных в InnoDB через каналы (pipes). Если используете, это значение лучше увеличить.innodb_flush_logs_at_trx_commit=2 - если вас не особо заботит ACID, и вы можете себе позволить потерять транзакции за последние секунду или две, в случае полного краха ОС, то установите это значение. Но это может повлечь печальные эффекты при коротких записях транзакций.innodb_thread_concurrency=8 - даже при имеющихся InnoDB Scalability Fixes будет совсем не лишним иметь ограниченное количество потоков. Значение может быть больше или меньше в зависомости от ваших потребностей, но 8 будет оптимальным значением для начала.innodb_flush_method=O_DIRECT - избегайте двойной буферизации и уменьшите активность swap, в большинстве случаев это увеличивает производительность. Но будьте осторожны, если у вас нет RAID с возможностью сохранения данных, операции ввода-вывода могут проходить некорректно и данные могут быть повреждены.innodb_file_per_table - если у вас немного таблиц, используйте эту опцию и рост занимаемого таблицами места не будет бесконтрольным. Эта опция добавлена в MySQL 4.1 и сейчас достаточно стабильна для использования.

Проверьте также, могут ли ваши приложения запускаться в режиме изоляции READ-COMMITED. Если это так, то установите опцию transaction-isolation=READ-COMITTED. Этот вариант увеличит производительность.

Есть еще немало опций, значения которых можно поменять для достижения лучшей производительности. Об этом можно прочитать в заметке Настройка опций mysql-server (перевод) или в одной из наших презентаций.

Настройка приложений для работы с InnoDB

При переходе с типа MyISAM, вам конечно будет интересно, что изменилось и какие новые возможности вам теперь доступны. Во-первых: убедитесь, что вы используете транзакции при обновлениях. Это необходимо для повышения производительности. Во-вторых: готово ли ваше приложение обрабатывать проблемы, которые могут произойти? И в-третьих: возможно вы захотите пересмотреть структуру своих таблиц и посмотреть как вы можете использовать свойства InooDB: распределение по первичному ключу, использование первичного ключа на всех индексах (это позволяет сократить первичеый ключ), быстрый просмотр по первичным ключам (попробуйте использовать это при запросах с JOIN) или большие несжаты индексы (облегчают индексирование).

При помощи этого краткого описания вы сможете провести первичную настройку InnoDB, которая повысит производительность на системах без battery backup, без изменения настроек ОС и не внося изменения в настройки приложений, до сих пор использующих MyISAM

l.wzm.me