Настройка innodb_flush_method определяет метод сброса данных из памяти на диск. Выбор подходящего значения — это выбор между скоростью и надежностью.
Основные значения — это O_DIRECT и O_DSYNC.
O_DIRECT
Этот метод использует 4 операции записи на диск на каждый вызов. Все эти операции верифицируются, что обеспечивает дополнительную надежность записи на диск, однако замедляет выполнение. 
Лучше использовать когда для БД не настроен бэкап, и нет возможности быстро переключиться на резервную копию.
O_DSYNC
Этот метод также использует 4 операции записи на диск на каждый вызов. Однако при этом, только две из этих операций будут верифицированы (скажем так — 2 операции выполняться последовательно, а еще 2 параллельно, но без гарантий). Это обеспечит более эффективную запись, что позволит увеличить пропускную способность записи MySQL. 
Лучше использовать когда БД резервируется, и есть возможность быстро переключиться на копию.
В итоге
O_DSYNC будет работать быстрее, чем O_DIRECT, но не так надежно. Настройка указывается в my.cnf :
Описание, рекомендации и значение параметра innodb_log_file_size
Описание, рекомендации и значение параметра slow_query_log
Описание, рекомендации и значение параметра thread_cache
Описание, рекомендации и значение параметра innodb_buffer_pool_size
Описание, рекомендации и значение параметра max_binlog_size
Описание, рекомендации и значение параметра thread_cache_size
Описание, рекомендации и значение параметра query_cache_size
Анализ медленных запросов (профилирование) в MySQL с помощью Percona Toolkit
Уменьшение размера картинок при сохранении качества
Простой способ выбрать индексы для Mysql
DROP INDEX в Mysql
Просмотр профиля запросов в Mysql
Запросы для выборки самых больших таблиц
Создание индексов на рабочих MySQL таблицах без головной боли
И как правильно работать с длительными соединениями в MySQL
Как исправить ошибку доступа к базе 1045 Access denied for user
Запрос для определения версии Mysql: SELECT version()
Как создать индекс в Mysql
Обновление до новой версии Mysql
Включение и работа с логом медленных запросов в Mysql
Включение или восстановление репликации на работащих Mysql серверах
Что значит и как это починить
Описание, рекомендации и значение параметра log_slow_queries
Не знаю почему, но по умолчанию настройки MySQL рассчитаны на десктопы 90-х годов. Например, 8Mb памяти под индексы InnoDB. Помните, как Билл Гейтс заявил, что «640 Кб памяти должно хватать каждому». Дефолтные настройки MySQL из этой серии.
Для начала моя выжимка из конфига (4G RAM, AMD Athlon 64 X2 Dual 5600+)
Как посмотреть что с базой что-то не в порядке
Самый быстрый вариант зайти в phpMyAdmin во вкладку «Текущее состояние MySQL»
Там вы увидите подсказки, какие настройки нужно подкрутить.
Сами настройки можно посмотреть в том же phpMyAdmin во вкладке «Системные переменные».
У MySQL есть несколько настроек, с помощью которых можно разогнать базу до первой космической. Во-первых, настройки по хранению индексов в памяти. Мало того, что индексы значительно ускоряют выборки, но если их хранить в памяти, а не на диске (где они обычно лежат), то профит будет значительным.
key_buffer_size = 512M
Таким образом выделяем 512 Mb под индексы таблиц MyISAM. Дело в том, что у меня половина баз в MyISAM (так исторически сложилось). На 99,9% эти базы используются на чтение, так что переходить на InnoDB смысла нет.
innodb_buffer_pool_size = 512M
Такой же объем памяти выделяем на таблицы InnoDB.
Тут нужно знать меру. Если у вас 1 база размером 100 Mb, то нет смысла выделять 1 Гб памяти – она всё равно не будет использована.
Во-вторых, нужно смотреть не на размер таблицы, а на размер индексов. Пример из жизни: таблица 300 000 комментариев весит 300 Мб, а ее индексы занимают в 15 раз меньше, что вполне логично, так как обычно индексы расставляются на числовые и временные столбцы, а не на текст. Посмотреть это опять же можно в phpMyAdmin
innodb_additional_mem_pool_size = 16M
Размер памяти, выделяемый InnoDB для хранения различных внутренних структур.
innodb_flush_method = O_DIRECT
Тут мы вырубаем буферизацию таблиц для файловой системы и говорим MySQL обращаться к файлам напрямую.
innodb_flush_log_at_trx_commit = 2
При каждой транзакции MySQL пишет лог и сбрасывает на диск (значение 1). Значение 2 – сбрасываем в память. Мне не критично потерять транзакции за последние 2 секунды в случае падения сервера.
join_buffer_size = 8M
Память для запросов с джойнами, когда объединение происходит без использования индексов.
sort_buffer_size = 8M
read_rnd_buffer_size = 8M
Полезно для запросов с сортировкой ORDER BY и группировкой GROUP BY. При малом значении сортировка идет во временной таблице на диске.
tmp_table_size = 64M
max_heap_table_size = 32M
Настройки для хранения временных таблиц в памяти. Временные таблицы часто образуются при больших джойнах.
table_cache = 256
Максимальное число одновременно открытых таблиц.
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 1
Пишем в лог медленные запросы
query_cache_type = 2
query_cache_limit = 1M
query_cache_size = 32M
Кэширование запросов внутри MySQL
In my configuration innodb_flush_method=O_DSYNC from O-DIRECT reduces about 75% the iowait, and accordingly this the load. Should I set another variables besides innodb_flush_method to reduce more the iowait?
My configuration file is:
I have a database with 100 Innodb tables, 3 of them has about 25000 records, the others has no significant records. The average queries in peak time is about 160, the majority is SELECT
1 Answer 1
innodb_buffer_pool_size
Major problem is innodb_buffer_pool_size is too small. Recommandation is set to 50
75% of main memory.
I strongly recommand that you should increase it’s value.
Generally speaking, O_DIRECT is little bit fast because InnoDB Buffer Pool caches Data+Index, So with O_DIRECT disabled File System Page Cache is faster. MySQL Manaual says (
Depending on hardware configuration, setting innodb_flush_method to O_DIRECT can either have either a positive or negative effect on performance. Benchmark your particular configuration to decide which setting to use.
But in my experience, there was no significant difference between O_DIRECT and O_DSYNC. Both SSD and HDD are test.
Anyway you should increase innodb_buffer_pool_size .
Calculating innodb buffer pool hit ratio
E.g. above examples,
I think this value is too small in your case.
query_cache_size
If most queries are SELECT and update query is rare, I think increasing query_cache_size is very helpful for you.
Could you post your query cache status as follows?
Calculating innodb buffer pool hit ratio
first, figure out your query cache hit ratio.