MyISAM vs InnoDB

[heap]

Всем доброго времени.

 

Есть некий пока не понятный трабл. Работает небольшой веб-портальчик на сервер, который работает с БД в MySQL. Движек MyISAM. Решил было перевести с ростом потребностей эту структуру на InnoDB.

Версия мускуля 5.0.51a. Добавил в конфиг опции:

innodb_data_file_path = ibdata1:100M:autoextend

innodb_buffer_pool_size = 2048M

innodb_log_file_size = 256M

innodb_log_buffer_size = 4M

 

И передамбил БД. Через минут 15 портальчик по вебу не отзывался. Вхожу по ссш, делаю /usr/bin/mysqladmin status

и вижу несколько сотен Slow queries и несколько десятков тысяч Opens. При этом это было в ненагруженное время суток. В итоге вернул все в MyISAM, сейчас пиковое время, а та же команда кажет:

Uptime: 19725 Threads: 2 Questions: 8259483 Slow queries: 0 Opens: 1284 Flush tables: 1 Open tables: 64 Queries per second avg: 418.732

 

Возможно что-то накосячено в InnoDB? Или известный баг, хотя гугл не нашел вроде.

[voron]

И передамбил БД.

Можно для таблиц делать alter table имятаблицы engine=innodb

Возможно, портал не рассчитан на innodb. Innotop может чем-то помочь в диагностике.

 

ps: вроде innodb_file_per_table=1 лучше использовать, чем общий tablespace.

[make.kernel]

innodb_flush_method = O_DIRECT

innodb_file_per_table = 1

и

innodb_buffer_pool_size = 12G при 16 гб оперативки

 

прирост в скорости в 2 раза примерно на таблицах в 4-5 гб, пока пул сайз был маленький работало медленнее чем myisam

 

[voron]

innodb_flush_method = O_DIRECT

С этим не всё так однозначно. Не всегда кэш ОС это плохо. Когда целостность данных не так важна, кэш записи ОС может помочь уменьшить нагрузку. Про

innodb_flush_log_at_trx_commit=0 (о5 же когда целостность данных не так важна) тоже не стОит забывать.

[photon]

innodb_flush_method = O_DIRECT

С этим не всё так однозначно. Не всегда кэш ОС это плохо. Когда целостность данных не так важна, кэш записи ОС может помочь уменьшить нагрузку.

Двойное кэширование -- это зло в любом случае. У хранилищ InnoDB есть свой кэш, его незачем дублировать. Для целостности данных нужно обеспечить резервирование питания (RAID с батарейкой, как минимум) и память с ECC.

Edited October 28, 2009 by photon

[voron]

У хранилищ InnoDB есть свой кэш, его незачем дублировать

Тыкните носом в кэш операций записи(аналог writeback кэша ОС). Сейчас с настройками количества потоков IO в 5.4 стало полегче, а до этого смысл иногда есть "спихнуть" данные в буфера ОС, и она уже пусть беспокоится о записи на диск.

Edited October 29, 2009 by voron

[heap]

Я, конечно, все понимаю. Но если бы проблема была в медленной работе - изначально то после mysql restart оно работает нормально, а потом начинает безумно расти значение Opens в status и появляются Slow queries. Причем Opens доростал и до более чем 100к, в то время как на MyISAM в час-пик оно было 1200 с копейками.

Или я рассуждаю в неверном направлении?

[Dyr]

innodb_flush_method = O_DIRECT практически не оказывает никакого влияния на производительность. Проверял на сервере с SAS-контроллером и RAID5 из трёх дисков, под Gentoo и под FreeBSD, на MySQL 5.0 и 5.1 с помощью sysbench.

[heap]

innodb_flush_method = O_DIRECT практически не оказывает никакого влияния на производительность. Проверял на сервере с SAS-контроллером и RAID5 из трёх дисков, под Gentoo и под FreeBSD, на MySQL 5.0 и 5.1 с помощью sysbench.

 

Вобщем-то вопрос не снят. Даже расширен.

Проводил стрессовое тестирование freeradius. Перевел таблицу radacct в InnoDB. Усиленно лил запросы.

Постепенно начинают повисать Slow queries. В конфиге мускуля помимо прочего:

innodb_flush_log_at_trx_commit = 2
innodb_buffer_pool_size        = 1024M
innodb_thread_concurrency =8
innodb_lock_wait_timeout = 500
interactive_timeout = 20
innodb_log_file_size        = 256M
innodb_log_buffer_size        = 4M
innodb_file_per_table = 1
innodb_locks_unsafe_for_binlog = 1
back_log = 75
table_cache = 300
thread_cache = 32
wait_timeout = 30
connect_timeout = 10

 

По хорошему дед-локов быть не должно. Ан нет - в скором времени наблюдаем растущее число висящих запросов UPDATE на табличку. То ли неверно я понимаю, что InnoDB сверх надежный, а кроме того быстрый на инсерт/апдейт ввиду того, что не лочит всю таблицу. Но при этом перевожу табличку в MyISAM - и оно выживает даже при большем числе запросов, нежели InnoDB.

В InnoDB появляются долгие запросы с первых секунд (наблюдаю mytop), но поначалу не более 3-10 секунд. Потом их становится все больше и в итоге начинается постоянный рост количества и времени ожидания.

Версия мускуля уже 5.1, на 5.0 была та же история.

Как такое может быть?

 

PS: Судя по доке innodb_locks_unsafe_for_binlog = 1 должен исключить дедлоки, ибо связей между таблицами нет. Но эффекта от этого параметра глобально тоже нет.

[Dyr]

heap, поставьте innotop и смотрите, какие запросы блокируются, или включите slow-log. Больше ничего в голову пока не приходит. У меня конфиг тестирования выглядел так:

skip-name-resolve
skip-locking
key_buffer_size = 384M
max_allowed_packet = 1M
table_cache = 1024
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_limit = 4M
thread_concurrency = 8
log-bin=mysql-bin
expire_logs_days=30
server-id       = 1
tmpdir          = /tmp/
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:1000M;ibdata2:100M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/
innodb_buffer_pool_size = 6000M
innodb_log_file_size = 250M
innodb_file_per_table
default-storage-engine=InnoDB
innodb-support-xa=0
innodb_flush_log_at_trx_commit=0
join_buffer_size=256K
innodb_lock_wait_timeout = 50