февраля 23, 2009

Касательно INNODB

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

Производственные мощности - это база в десятки гигабайт, >1k QPs и все такое.

Конечно в InnoDB есть такие вещи как FK Constraints, вроде даж триггеры есть, и даже локи на уровне строки, но .... увы. По шустроте с MyISAM он просто несравним.

Это все вопреки "форумным" мнениям начинающих программистов-теоретиков, что InnoDB рулит. Рулит. На десятке инсертов в сутки.

Еще одно заблуждение - InnoDB надежнее MyISAM, MyISAM крашится, InnoDB нет, у него мол есть восстановление из лог файла. Ну начнем с того что InnoDB вообще по непонятным причинам теряет данные при более-менее серъезной мощности. MyISAM крашится да. При криворуких составлениях схемы БД, при криворукой работе с ней и при внезапном выключении питания.

Ничего из этого лично меня не беспокоит и я наслаждаюсь производительностью MyISAM. Чего и вам советую.

Если будет время, выложу какой нить бенчмарк.

Кросс-пост: http://www.pentarh.com/wp/2009/02/23/innodb-vs-myisam/

апреля 11, 2007

Небольшой апгрейд

Было:
2 x Intel(R) Xeon(TM) Dual CPU 3.00GHz
2G RAM
FAST-160 WIDE SCSI 320.0 MB/s DT IU RTI
Gentoo 2006 i386 SMP HTT, cfq io scheduler

Стало:
Тоже самое, только
4G RAM
Gentoo 2006 amd64 SMP HTT, deadline io scheduler
CFLAGS="-O2 -pipe -march=nocona"

Разница в производительности mysql пожалуй минимум раза в два быстрее.
Было очень активное обращение к винчестеру на чтение, очень большие таблицы. % io wait был очень высок, т.к. процессор постоянно ждал данных от винчестера. LA было около 2.5-3 обычное.

Выигрыш в производительности получился за счет:

  • Увеличения объема памяти:
    База занимает около 4 гигабайт. Памяти на машине было 2. Из них 1 гигабайт занимал сам процесс mysqld. Оставался 1 гигабайт на кеш
    После увеличения объема памяти под кеш стало доступно 3 гигабайта.
    Mem: 4052136k total, 4016616k used, 35520k free, 168860k buffers, 2766228k cached
    Почти половина базы сейчас сидит в памяти
  • Переход системы на архитектуру amd64. На архитектуре 32бит линукс использовал извраты чтобы добраться до памяти выше 800М. Теперь же он к любой странице памяти обращается одинаково быстро
  • Компиляция mysqld в режиме 64 бит. "-march=nocona" дает команду GCC подстроить код под архитектуру EMT64 данного процессора. Чем конкретно это выигрышно я точно не знаю, подозреваю что операции ввода-вывода должны дать прирост быстродействия.

марта 18, 2007

Высокие показатели io wait

Столкнулся было дело с такой ситуацией когда на сервере mysqld серъезно загружал процессор, при чем процессор большее время проводил в режиме iowait и очень небольшую в режиме user.

Грубо говоря, процессор большее количество времени ждет ответа какого то блочного устройства. Так как кандидатов не много (сеть и винт), диагностировать затык не составило труда.

При помощи утилит iostat,dstat,vmstat удалось выяснить что узкое место винчестер.

Однако как я не парился. (система Gentoo Linux)

1. Сменил io scheduler deadlock на cfq (перекомпиляция ядра)
2. Затюнил кучу sysctl
3. Скомпилировал mysqld в статическом варианте, лишившись очень полезной утилиты mytop
4. Разнес даже таблицы по разным дискам

В результате добился только некоторого улучшения ситуации, однако перспектива мне не нравилась

Все оказалось довольно просто. Это именно тот случай, когда админ вынужден подметать за программистами. Когда mysqld приходится обрабатывать миллионы записей да еще и без индекса, мы наблюдаем именно такую картину - процессор ждет сотни мегабайт от блочного устройства - винта.

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

марта 11, 2007

Немного о Key Buffer

Итак, эта переменная конфигурации достойна отдельного внимания.

Известна как "key_buffer_size" или "key_buffer" в my.cnf

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

Nota bene. Существует мнение что на современных *nix системах, при условии, что размер всех баз данных не превышает некоторого процента свободной оперативной памяти, key_buffer можно смело отключать вообще, т.к. система итак кеширует наиболее часто читаемые файлы, используя свободную оперативную память.
В таком случае не только индексы, но и данные будут "сидеть" в оперативной памяти с отключенным key_buffer, а его наличие будет делать "двойную работу" - т.е. индексы будет кешировать и система и mysqld. В таком случае можете его ставить в ноль, если вы отдаете себе отчет в том, что вы делаете.
Однако я бы не советовал этого делать, если размер баз превышает половину вашей оперативной памяти. Система ведь кеширует все подряд, и там может не найтись места конкретно под индексы.

Как определить оптимальное значение? Во-первых надо чтобы сервер немного потрудился в реальных условиях, т.е. был запущен в течении неск. часов. Используем запрос "SHOW [GLOBAL] STATUS" и смотрим значения "Key_read_requests" и "Key_reads".

  • "Key_read_requests" - сколько с момента запуска было запросов на чтение индексов
  • "Key_reads" - сколько запросов было прочитано не из кеша "key_buffer_size", т.е. с диска (на самом деле тут не учитывается дисковый кеш самой системы).

Сравнивая эти два значения, можно судить, насколько мало значение "key_buffer_size" в my.cnf

Если "Key_reads" менее чем на два порядка (100 раз) меньше "Key_read_requests", то "key_buffer_size" определенно нужно увеличить с поправкой на количество свободной памяти.

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

SHOW STATUS и MySQL 5

Как известно, запрос "SHOW STATUS" используется для отладки сервера и подгона параметров. Однако "SHOW STATUS" начиная с пятой ветки показывает статистику текущего подключения. Если вы хотите видеть статистику уровня сервера, используйте "SHOW GLOBAL STATUS"

Тоже самое касается "SHOW VARIABLES" и "SHOW GLOBAL VARIABLES"

См. также MySQL SHOW Syntax

Немного о локах

Бывает такая хрень когда локи жить мешают. Т.е. заходит какой то slow SELECT, локает таблицу, пока он там думает, приходит пачка INSERTов и ждут...

У меня количество INSERTов в такой пачке иногда достигало max_used_connections

Ну или мускуль просто падал не в состоянии обработать такой поток данных.

Либо наоборот. Приходит какой то UPDATE, а за ним пачка INSERTов. Ну вообще это разные ситуации и чтобы их разрулить, надо неплохо рубить в движке мускуля

Помочь могу следующие фишки

1. concurrent_insert - параметр такой. Что делать с одновременными инсертами? 1 - ждать пока снимется лок с предыдущего, 2 - всунуть запись в конец таблицы. 1 - лучше для избежания фрагментации динамических таблиц. 2 - для производительности

2. Если INSERT/UPDATE треды мешают жить SELECTам, то можно поюзать low-priority-updates. Тогда апдейты будут иметь более низкие права на лок, чем SELECT. Т.е. поменяются местами

А вообще при проблемах с локами советую проверить запросы на правильность индексов а так же перейти на таблицы INNODB, у которых лок не на уровне таблицы, а на уровне строки данных.

Кстати, локать таблицы еще может периодический бекап mysqldump. Думпать рабочую нагруженную базу не советую, поднимите лучше репликацию, и думпайте SLAVE сервер.

Так же полезно почитать офф документацию: Table locking issues

марта 04, 2007

Разница между localhost и 127.0.0.1 в php

Если ваш сервер MySQL установлен локально, на одной машине с выполняющимися скриптами, то всегда в скриптах при подключении надо указывать в качестве host: localhost.

Бывают случаи когда указывается в качестве хоста айпи 127.0.0.1, либо hostname этого же сервера, либо какой то айпи этого сервера.

Это неправильно, т.к. php приподключении на localhost открывает соединение через unix-socket (напр. /tmp/mysql.sock), а не использует TCP/IP соединение. Если же вы написали что то отличное от localhost, то используется соединение с mysql сервером через TCP/IP сокет.

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

Во-вторых, Unix-socket в разы шустрее TCP/IP и потребляет меньше ресурсов. Так же на него меньше ограничений и меньше посредников между клиентом и сервером типа файрвола и т.д.

На небольших нагрузках разница не замечается. Но когда идет приличное число коннектов, то начинает все серъезно глючить.

Тюнинг тредов (mysql threads tunning)

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

Итак. Как известно, mysqld это "один процесс - много тредов". В достаточно приближенном понимании тред (thread) это когда процесс копирует сам себя, и родительский процесс передает треду какую то задачу для решения.
Создание треда отличается от обычного fork() в основном тем, что fork порождает +1 процесс, тогда как деление на треды использует другую, более производительную технологию ветвления без клонирования данных в памяти.

При каждом подключении клиента, mysql создает тред, который это подключение обрабатывает.

Если у вас средне нагруженый mysqld, то убедитесь что mysqld работает на нормальной реализации тредов. К примеру, FreeBSD ниже 6 ветки имеет ненормальную, глючную реализацию тредов pthread. Основные глюки проявляются при работе на SMP (более 1 процессора). pthread "не умеет" нормально раскидывать треды по разным процессорам и в результате мы имеем ситуацию, когда Mysqld висит только на одном процессоре вместе с кучей своих детишек. Все остальные процессоры курят, даже если мускуль съел все процессорное время своего процессора.

Более детальная информация как правильно заставить работать mysql под FreeBSD может быть найдена тут: http://dev.mysql.com/doc/refman/5.1/en/freebsd.html

Итак, посмотрим что у вас творится с тредами. Если сервер слабо нагружен и имеет 1-2 подключения одновременно, то в этой оптимизации особого смысла нет. Гораздо ощутим результат, когда сервер серъезно нагружен и имеет много конкурентных запросов.

Посмотрим статус

shell> mysqladmin extended-status

Обратим внимание на значения:

Threads_cached
Threads_connected
Threads_created
Threads_running


Если Threads_cached равно нулю, у вас отключен кеш тредов. Т.е. при каждом подключении создается новый тред, а при отключении он уходит в /dev/null ;). Это плохо. Когда включен кеш тредов, то при отключении клиента тред уходит в кеш, а при новом подключении он не создается, а берется из кеша. Это гигантская экономия ресурсов при больших нагрузках. В некоторых ситуациях нагрузка уменьшалась в разы при использовании thread cache.

Threads_cached - количество тредов в кеше

Threads_connected - грубо говоря, количество открытых подключений

Threads_running - Сколько тредов сейчас "работают"

Threads_created - а вот и краеугольный камень. Сколько тредов было создано со старта сервера. Если в кеше нет свободного треда, то он создается. Если кеша нет вообще, то при каждом подключении создается тред. А при отключении убивается. Тоже самое происходит, если кеш тредов слишком маленький. Короче. Если это значение у вас измеряется тысячами, то у вас не все впорядке. Оно должно быть чуть больше Threads_cached в идеальном случае.

Что делать если Threads_created имеет слишком высокое значение?

В my.cnf есть такой параметр thread_cache_size (или просто thread_cache). Его необходимо увеличивать эксперементальным путем до той поры пока Threads_created не примет порядок цифр Threads_cached.

Для определения начального значения, вам надо узнать, сколько тредов бывает в среднем запущено в пиковых нагрузках. Для мониторинга сгодится утилитка mytop (она может не работать если mysql скомпилирован статически).

Либо периодически запускать такую команду:

shell> echo "SHOW GLOBAL STATUS" | mysql | grep Threads_connected | awk '{print $2;}'


Определили среднее значение, записали в my.cnf (thread_cache), сделали рестарт сервера. Пошли, покурили, выпили чайку. Смотри статус (команда указана выше). Если Threads_created продолжает неугомонно расти выше значения кеша, увеличиваем переменную thread_cache. И так до победы, но не советую растить эту величину выше нескольких сотен - рискуете получить Out Of Memory.

При увеличении этой величины также надо мониторить Resident Set Size процесса mysqld в долгосрочной динамике. Это колонка RSS в утилите "top". Означает сколько mysql сейчас занимает места в оперативе. Не советую растить его выше половины объема вашей оперативной памяти, т.к. получите пенальти, когда начнут ужиматься системные кеши.

Нормальное значение thread_cache, при котором Threads_created держится на приемлимом уровне, а mysql умеренно жрет память колеблется от 5 до 100 в зависимости от нагрузки и величины доступного ОЗУ.

Слишком высокое значение thread_cache может необоснованно кушать лишнюю память.

Если вас это не спасло, thread_cache превышает мыслимые нормы и Mysql продолжает жрать память и процессор и порождать треды, то необходима комплексная оптимизация: 1. системы, 2. ядра, 3. логики скриптов и запросов 4. других стартовых параметров mysqld

Вопросы и корректировки приветствуются :)

Вступление

Так как я проделал немало работы по оптимизации и тюнингу этого чертова демона, то, дабы не потерять свой опыт в растущем потоке информации а также поделиться им с другими, попробую здесь писать всякие умные вещи :)