И снова про производительность. Zabbix 1.8.2 и mysql innodb

[mschedrin]

Читал много тем в форуме про оптимизацию производительности mysql и zabbix_server. Ответа на свои вопросы так и не получил.
Испытываю проблемы с производительнотью на сервере, где работают mysql и zabbix.
Сервер сейчас выглядит так:

Zabbix server is running	Yes	- 
Number of hosts (monitored/not monitored/templates)	2886	2860 / 2 / 24
Number of items (monitored/disabled/not supported)	82078	81918 / 10 / 150
Number of triggers (enabled/disabled)[true/unknown/false]	78724	78720 / 4  [4 / 36 / 78680]
Number of users (online)	23	10
Required server performance, new values per second	202.8623	-

Вся база данных заббикса весит 1,7 Гб

Узким местом в системе является скорость работы базы данных с жестким диском. HDD самый обыкновенный, без raid. На него постоянно что-то пишется и с него что-то читается. Казалось бы, требуемая производительность сервера 202 значения в секунду - совсем немного. Непонятно, откуда такая активность на жестком диске. То ли я всё-таки innodb неверно настроил, то ли zabbix как-то неправильно с ним работает.
Поделитесь опытом, наблюдаете ли вы подобное у себя?

[dima_dm]

Могу рассказать, как я оптимизировал MySQL
Под MySQL используется отдельная 64-битная машина, 8 Gb оперативной памяти, Raid 1+0 на 4-х физических дисках SAS 10k rpm.
Опция /etc/my.cnf
innodb_file_per_table
default_table_type = INNODB
Каждая таблица в отдельном файле. (работает при создании новой таблицы)
Способы перевода таблиц в отдельный файл
1) Перенос данных
CREATE TABLE new_table LIKE old_table; INSERT INTO new_table SELECT * FROM old_table; DROP TABLE old_table; RENAME TABLE new_table to old_table;
2) Через dump файл
Сделать полный dump
mysqldump -u root -ppassword zabbix
mysql> drop database zabbix;
cat dump.sql | mysql -u root -ppassword zabbix

Далее использовал скрипты
Скрипт для оптимизации параметров MySQL

http://www.day32.com/MySQL/tuning-primer.sh

http://wiki.mysqltuner.com/MySQLTuner

mysqltuner.pl

http://hackmysql.com/mysqlreport

И делал тонкую настройку.

[mschedrin]

innodb_file_per_table есть. Тюнинг innodb тоже проводил скриптом tuning-primer.sh.
Убивают вот такие запросы в slow query log:

# Time: 100413 20:31:13
# User@Host: zabbix[zabbix] @ localhost []
# Query_time: 3731  Lock_time: 0  Rows_sent: 80784  Rows_examined: 245941
SELECT i.itemid,i.lastclock,i.description,i.key_,i.type,h.host,h.hostid,h.proxy_hostid,i.delay,i.delay_flex FROM items i,hosts h WHERE i.hostid=h.hostid AND h.status=0 AND i.sta
tus=0 AND i.value_type not in (2) AND i.key_ NOT IN ('status','zabbix[log]') AND NOT i.lastclock IS NULL AND ( i.type in (7,13,14,3,5,8,10,15) OR (h.available<>2 AND i.type in (
0)) OR (h.snmp_available<>2 AND i.type in (1,4,6)) OR (h.ipmi_available<>2 AND i.type in (12))) AND  (h.hostid IN (10404,10403,10470,10449,16600,14880,17377,16651,17730,14856,14
886,16650,10390,17726,10412,10391,10401
... <очень много цифр>
16114,16139) OR h.hostid IN (15439,17158,14972,15951,14979,14985,14984,15938,14986))  AND ((h.hostid  BETWEEN
 000000000000000 AND 099999999999999)) ORDER BY i.lastclock,h.host,i.description,i.key_;

Вот такой запрос занял 3731 секунд. Mysql копирует его во временную таблицу, видимо это занимает очень много времени. Вопрос в том - это запросы неоптимальные или у меня что-то в mysql не так настроено?

[dima_dm]

Может у вас физической памяти мало? И идёт активный swap. Соберите статистику по использованию диска (так http://www.zabbix.com/forum/showthread.php?t=16604) и свопированию. Посмотрите iowait статистику. Недавно мой коллега проводил нагрузочные тесты на SunFire V210 (у него тоже нет аппаратного Raid) Zabbix 1.8.2 + MySQL innodb_file_per_table, он тоже производительность больше 220 значений в секунду получить не смог.

[mschedrin]

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

[mschedrin]

Ну хорошо, допустим я поставлю рэйд, это увеличит скорость чтения и записи на диск в 4 раза. Время медленного запроса уменьшится в 4 раза:
3731/4 = 932 секунды. Это ведь абсолютно ненормально время для выполнения запросов!

[oalex]

вот не факт, что рейд ускорит, если рейд 5 да без дополнительного кэширования - на запись тормозить сильнее будет чем просто винт или зеркало

[mschedrin]

Как же тогда другие пользователи zabbix решают подобные проблемы с производительностью?

[oalex]

распределенной системой мб ?
у тебя 82к итемсов уверен что все на нужно одной машине? (хотя кто ее знает - твою структуру).
у меня до 70 доходило.. но как то забил на этих "попугаев"... реально помогло увеличение кэша для INNODB до 2гигов (моя база была 1.8-2Г), т.к. по умолчанию всего 8метров было (как раз mysqlreport и показал).
Скорость увеличилась работы.. но значение "попугаев" не изменилось

[mschedrin]

распределенной системой мб ?
у тебя 82к итемсов уверен что все на нужно одной машине? (хотя кто ее знает - твою структуру).
у меня до 70 доходило.. но как то забил на этих "попугаев"... реально помогло увеличение кэша для INNODB до 2гигов (моя база была 1.8-2Г), т.к. по умолчанию всего 8метров было (как раз mysqlreport и показал).
Скорость увеличилась работы.. но значение "попугаев" не изменилось

У меня всего два гига памяти на сервере, под кэш innodb выделено 1,5 гига. Если кэш будет превышать размер базы, то она теоретически должна вся влезть в этот кэш?

По поводу распределенной системы. Что распределять то? Базу данных? Кластер делать?

[ugh]

поищите на форуме, десятки топиков о производительности мускуля
ну например, можно посоветовать для mysql-tmp сделать в памяти раздел

[mschedrin]

поищите на форуме, десятки топиков о производительности мускуля
ну например, можно посоветовать для mysql-tmp сделать в памяти раздел

В каком разделе лучше искать в русском или английском?
Спасибо за совет, попробую.

[oalex]

" mschedrin "Yesterday, 12:40"
вместо букв - набор непонятных символов, пост прочитать не могу

[mschedrin]

В каком разделе лучше искать в русском или английском?
Спасибо за совет, попробую.

[den_crane]

вообще 202 в секунду это мягко говоря дохрена. sar -d 1 20 бы увидеть.
А чего база такая маленькая? Время хранения маленькое?

а конфиг мускуля где? Сколько памяти вы ему отдали?

Вообще что конкретно волнует? Тот долгий запрос? Или чего? Я так понимаю что тот запрос из фронтэнда? Зачем он вам?