Паразитная нагрузка от mysql

тип таблиц какой?

MyISAM    

> порядка 70 коннектов/тредов, в основном выборка данных

в том и дело, что «в основном».
если идет хотя бы один insert или update, то все select-ы к этой таблице сидят и ждут своей очереди.
ну а также если селекты со всякими inner-join и left-join, а также order-by и т.д. - то 5.0 мускулю башню от них срывает. 5.1 поадекватней в этом плане, 5.5 под нагрузкой еще не тестировал.

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

я думаю мб проблема в тредах: их 60 штук, возможно переключение меж ними. хотя всё равно не реально. хз куда копать

мускул какой версии? 5.0, 5.1, другой? MariaDB пробовал?

5.0

обновлять бд из-за неразрешённой проблемы...есть подозрение у меня что проблема сохранится и на версии 6.0

посмотреть параметры типа

mysql> show variables like 'thread_cache_size';

mysql> set global thread_cache_size=200;

еще в phpmyadmin есть страница с отчетом о производительности, иногда с советами по оптимизации.

кеш нитей уже правил, сейчас = 1000;

>если идет хотя бы один insert или update, то все select-ы к этой таблице сидят и ждут своей очереди.

кажется речь идет о сисколе select(), а не о sql

да, всё верно. лог strace за полминуты работы.

Process 25751 attached - interrupt to quit
Process 25751 detached
% time seconds usecs/call calls errors syscall
------ ----------- ----------- --------- --------- ----------------
99.92 19.444963 23541 826 select
0.04 0.007031 2 4130 fcntl64
0.02 0.003656 3 1122 5 futex
0.01 0.001570 2 770 mmap2
0.01 0.001450 1 1372 280 setsockopt
0.01 0.001158 1 826 getsockname
0.01 0.001116 1 826 accept
0.00 0.000034 0 118 munmap
0.00 0.000021 0 118 close
0.00 0.000013 0 118 write
0.00 0.000000 0 118 shutdown
------ ----------- ----------- --------- --------- ----------------
100.00 19.461012 10344 285 total

я привык искать проблемы на более высоких уровнях абстракции :)

какой только фигней люди не страдают, лишь бы постгресом не пользоваться

[troll]ок, после того, как весь нужный софт будет портирован благородным анонимусом за умеренную плату с мускуля на постгресс, мы так и быть закопаем ставший не нужным мускуль[/troll]

skip-locking
max_allowed_packet = 64M


table_cache = 7000
max_heap_table_size = 100000000
tmp_table_size = 100000000
query_cache_size = 50000000
key_buffer_size = 500000000
sort_buffer_size = 32000
thread_cache_size = 900
max_tmp_tables = 200

join_buffer=1M
read_buffer=1M
read_buffer_size = 512K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 16M

# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

часть конфига сервера rhel5/mysql5.0.77, обрабатывающего 15к-17к запросов.

может сервер кто-то досит или брутит? select обычно с I/O используется...

Каг-бе селект и должен съедать почти всё время. это означает, что в 99 процентах времени сервер тупо ждёт событий на файловых дескрипторах.

iotop

atop

тебе, возможно, помогут. Есть подозрение что в strace ты увидел не все процессы и тхреады.

pstree -upal

предоставь список жизненно необходимых тебе «софтин», работащих только с мускулем

А теперь попробуй innodb. А лучше MariaDB с её движком Xtradb.

акромя thread_concurrency и read_rnd_buffer_size все приведённые параметры увеличил весьма заметно. кстати, по запросам нагрузка идёт даже меньше < 2к...что для приведённой конфигурации сервера, полный фейл. сервер никто не брутит и недосит, коннект только по локалхосту, сама база отведена под бету, до беты доступ токо у меня, сайт тестится jmeter'ом. сам сайт по запросам очень легковесный...вообщем непонятки.

чего уж там, завтра снесу центос и выкачу вендасервер2008 и мсскуль2008, он же круче чё.

прикажете движок сайта переписывать чтобы проверить как оно на innodb взлетит?

Каг-бе селект и должен съедать почти всё время. это означает, что в 99 процентах времени сервер тупо ждёт событий на файловых дескрипторах.

стоп. почему он должен съедать почти всё время? если сервер ждёт событий - почему ядро то тогда загружено под самое немогу? что-то нето, нагрузка десткая чтобы такой сервер под 75% загружать

> прикажете движок сайта переписывать чтобы проверить как оно на innodb взлетит?

Я смотрю ты крутой разработчик на пахапе, если не знаешь разницу между myisam и innodb.

И для использования mariadb тебе тоже твой пахапе-код не надо менять, только саму субд.

> strace показал что 99,9% всех сисколов из сервера mysql приходится на select.

Дык у MyISAM в памяти только индексы хранятся. Если запросы без coverage index - данные будут читаться либо с диска либо с кеша. Вот вам и сисколы.

можете поподробнее объяснить про coverage index, документации толковой не нашёл (возможно плохо искал)

[тупой вброс] а счётчик запросов при этом не растёт? Ты стрейс правильно делал? С -f? А то может мускуль старается а ты его пришить хочешь...

Покажи strace -f -F -s 256 -p <pid>

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

Проще всего сделать как вам советовали - пройпдейтиться до 5.1 перконы и перегнать таблицы в xtradb. Хуже точно не будет. На 5.0 вы даже нормально логи медленных запросов включить не сможете. Потом включайте логи всего, что медленнее 0.1 сек, и лог запросов без индексов (со сканами). InnoDB/XtraDB позволит базу полностью в память всосать на чтение и уберет блокировки столбцов на апдейтах.

Как вариант - разобраться, не стал ли кто базу селектами задалбывать. Так тоже бывает.

mysql> show global status like 'Handler_read_rnd_next';

количество запросов не использующих индексы. или, например, запросов без where.

можно попробовать это значение сравнить с общим количество запросов SELECT

mysql> show global status like 'Com_select';

лог медленных запросов включал, кой-какие сыпятся, но всё < 10 msec.

завтра тогда обновлюсь и протестю на xtradb.

95% - именно селекты идут.

А как вы смогли их увидеть, если slow queries log только с 5.1 научился меньше секунды отсекать?

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

mysql> show global status like 'Handler_read_rnd_next';
+-----------------------+-----------+
| Variable_name | Value |
+-----------------------+-----------+
| Handler_read_rnd_next | 116661614 |
+-----------------------+-----------+
1 row in set (0.00 sec)

mysql> show global status like 'Com_select';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Com_select | 134653 |
+---------------+--------+
1 row in set (0.00 sec)


при нагрузке чисто тестами (бета версии) Handler_read_rnd_next    122 M, за секунду прирост порядка мегабайта. так же Select_full_join    71, Table_locks_waited    385 , Opened_tables    183 ,

Ну грузтся проц понятно при скане, но почему не юзер спейс а именно ядро?

Уверены, что это именно нагрузка, а не ожидание? Чем меряли?

> за секунду прирост порядка мегабайта

«M» означает миллионы, а не мегабайты :) .

http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.html#statvar_H...

Включите лог запросов без индексов. Не знаю, можно ли в 5.0, в перконе 5.1 точно можно.

в момент нагрузки

top - 13:40:25 up 21:19, 0 users, load average: 15.86, 9.38, 4.57
Tasks: 134 total, 3 running, 131 sleeping, 0 stopped, 0 zombie
Cpu0 : 35.9%us, 41.0%sy, 0.0%ni, 23.1%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu1 : 24.2%us, 66.7%sy, 0.0%ni, 9.1%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu2 : 17.6%us, 70.6%sy, 0.0%ni, 11.8%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu3 : 28.6%us, 62.9%sy, 0.0%ni, 8.6%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu4 : 26.5%us, 64.7%sy, 0.0%ni, 8.8%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu5 : 8.8%us, 85.3%sy, 0.0%ni, 5.9%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu6 : 20.6%us, 67.6%sy, 0.0%ni, 11.8%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu7 : 17.1%us, 68.6%sy, 0.0%ni, 14.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu8 : 21.1%us, 73.7%sy, 0.0%ni, 5.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu9 : 19.4%us, 58.3%sy, 0.0%ni, 8.3%id, 13.9%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu10 : 14.3%us, 71.4%sy, 0.0%ni, 14.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu11 : 51.3%us, 41.0%sy, 0.0%ni, 7.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu12 : 25.0%us, 22.5%sy, 0.0%ni, 52.5%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu13 : 29.7%us, 62.2%sy, 0.0%ni, 8.1%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu14 : 25.0%us, 66.7%sy, 0.0%ni, 8.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu15 : 16.7%us, 69.4%sy, 0.0%ni, 11.1%id, 2.8%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu16 : 16.2%us, 64.9%sy, 0.0%ni, 18.9%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu17 : 33.3%us, 52.8%sy, 0.0%ni, 13.9%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu18 : 21.9%us, 65.6%sy, 0.0%ni, 6.2%id, 6.2%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu19 : 22.2%us, 61.1%sy, 0.0%ni, 16.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu20 : 37.1%us, 57.1%sy, 0.0%ni, 5.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu21 : 8.8%us, 76.5%sy, 2.9%ni, 8.8%id, 2.9%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu22 : 11.4%us, 68.6%sy, 0.0%ni, 20.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu23 : 8.8%us, 70.6%sy, 0.0%ni, 20.6%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 2000000k total, 1319444k used, 680556k free, 0k buffers
Swap: 0k total, 0k used, 0k free, 0k cached



собственно видео что sy чрезмерно большой.

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

т.е. если вы ничего не перепутали про «прирост на мегабайт в секунду», то за секунту мускулю пришлось последовательно выбрать из таблиц миллион строк.

Вообще таблицу статистики лучше сбросить, копить сутки, и показывать целиком.

> может есть какие-то варианты обойтись без обновления и попытаться найти проблему в текущей конфигурации?

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

Из первого что всплыло в голове: Abills. Меня устраивает его работа с MySQL, с Postgresql он работать пока не умеет. Автор в свое время просил то ли 5, то ли 8 штук зеленых, чтобы добавить поддержку Postgresql

ветку не перепутали? abills - биллинговая система

• http://mysqltuner.pl • https://launchpad.net/mysql-tuning-primer

mysqltuner проходился

[!!] User '@localhost' has no password set.
[!!] User '@moigoroda.ru' has no password set.
[!!] Joins performed without indexes: 29


включил логирование запросов без индексов, результ: 5 параллельно работающих пользователей, 10 запросов выполняет каждый из них, итог: 4428393 сканирований строк. разрабы индексы не создавали.

сейчас буду их создавать, посмотрим что изменится.

>разрабы индексы не создавали

БД без индексов — это как сверхзвуковой истребитель без керосина.

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

# Query_time: 0.122182 Lock_time: 0.000226 Rows_sent: 8940 Rows_examined: 45469
SET timestamp=1313067256;
SELECT
pc.products_id,
p.products_image,
pd.products_name
FROM products_to_categories pc,
products p,
products_description pd

WHERE categories_id IN (61,66,65,75,77,79,84,85,87,88,89,90,91,93,94,95,96,97,99,100,101,102,103,106,105,107,108,109,110,112,
113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,15
2,153,154,155,156,157,158,159,160,161,162,163,164,165,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,
193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,230,231,232,23
3,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,251,252,253,254,255,256,257,258,259,260,261,262,263,264,266,267,268,269,270,271,272,273,274,
276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,31
5,316,317,318,319,320,321,322,323,324,325,326,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,0)
and p.products_id=pc.products_id
and p.products_id = pd.products_id
and pd.language_id = '1'
and p.products_status=1
ORDER BY p.products_ordered;

те просканировано 45к строк, explain показывает


mysql> explain SELECT pc.products_id, p.products_image, pd.products_name FROM products_to_categories pc, products p, products_description pd WHERE categories_id IN (61,66,65,75,77,79,84,85,87,88,89,90,91,93,94,95,96,97,99,100,101,102,103,106,105,107,108,109,110,112, 113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192, 193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,251,252,253,254,255,256,257,258,259,260,261,262,263,264,266,267,268,269,270,271,272,273,274, 276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,0) and p.products_id=pc.products_id and p.products_id = pd.products_id and pd.language_id = '1' and p.products_status=1 ORDER BY p.products_ordered;
+----+-------------+-------+--------+---------------------------+---------+---------+-------------------------------+------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------------------+---------+---------+-------------------------------+------+-----------------------------------------------------------+
| 1 | SIMPLE | pc | index | PRIMARY,idx_categories_id | PRIMARY | 8 | NULL | 9671 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | p | eq_ref | PRIMARY | PRIMARY | 4 | gb_site.pc.products_id | 1 | Using where |
| 1 | SIMPLE | pd | eq_ref | PRIMARY | PRIMARY | 8 | gb_site.p.products_id,const | 1 | Using where |
+----+-------------+-------+--------+---------------------------+---------+---------+-------------------------------+------+----------------------------------

те ключи таки есть, и он шарит вроде по ним.

>Using temporary; Using filesort

Вот этого не должно быть.

Тебе нужны составные индексы, включающие и условие выборки, и параметр сортировки. Точнее сказать не могу, у меня глаза разбегаются от этой каши. Плюс у меня идиосинкразия на объединение таблиц без JOIN'ов. Когда смотрю на «FROM pc, p, pd…» — прямо дурно становится :)

…

categories_id, хоть, в какой таблице?

анонимус спрашивал по поводу программ, где без MySQL не обойтись, я ему и ответил

>прикажете движок сайта переписывать чтобы проверить как оно на innodb взлетит?
это очень, очень паршивый движок, если его работоспособность зависит от типа используемой БД

>предоставь список жизненно необходимых тебе «софтин», работащих только с мускулем

phpMyAdmin :)

…

Я ничего не имею против Postgres, но я его так и не осилил. Мой мозг ещё осиливает инсталляцию, но настроить это до уровня производительности MySQL и администрировать потом _это_ — выше моих способностей. Насколько я знаю, только Orcale более извращённый в этом плане, чем Postgres :)

>А теперь попробуй innodb

innodb имеет на практике бонус только в транзакциях и внешних ключах. Во всём остальном, включая быстродействие — это сплошная жесть. Месяца не проходит, чтобы на очередных граблях плясать не приходилось:

http://www.linux.org.ru/forum/development/4094810 http://www.linux.org.ru/forum/talks/4350880 http://www.linux.org.ru/forum/general/5574146 http://www.linux.org.ru/forum/web-development/5981375 …

А чего все кинулись в настройку СУБД? Если время ситемное, тратится на select, то надо смотреть, для каких именно дескрипторов вызывается select.

>KRoN73, categories_id, хоть, в какой таблице?
в products_to_categories.

для данного вида запроса какие индексы мне надо готовить? с индексами сталкиваюсь практически первый раз - так что пока только осваиваю

tailgunner , +1 ! я об этом же - это проблема более низкого уровня чем размеры кешей и буферов, я предполагаю что даже без индексов тратилось бы ЮЗЕРСПЕЙС время, но никак не системное.

lsof показывает открытых 410 фд для данной базы, но в этом вряд ли проблема.