Тормозит сервер при большом количестве одновременных подключений

Замерил запросы. Сумма по времени на выполнение всех запросов не превышает 0.02-0.03 сек

Еще есть такая соманда 'SHOW PROCESSLIST'.. когда база тупит там видно что происходит. можно сделать самому или  использовать монитор phpmyadmin.

можно даже использовать ее вывод в коде, например так:

foreach($dbh->query("SHOW PROCESSLIST") as $f_pl) {
    $proc_list.= $f_pl.' '.$f_pl.', '. $f_pl .'<br>';
    $ptoc_total_time = $ptoc_total_time+$f_pl;
    if(($f_pl == 'Locked')||($f_pl == 'Copying to tmp table')) {
        $locked_f = true;
    }
};
if($locked_f) {
    echo '<div>'.$proc_list.'<br>total time:'. $ptoc_total_time.'</div>';
    if($ptoc_total_time > 100) exit;
}

можно к примеру вместо exit добавить отсылку списка процессов на email.. далее принимать решение.
еще есть такая соманда 'SHOW PROCESSLIST'.. когда база тупит там видно что происходит. можно сделать самому или  использовать монитор phpmyadmin.

можно даже использовать ее вывод в коде, например так:

foreach($dbh->query("SHOW PROCESSLIST") as $f_pl) {
    $proc_list.= $f_pl.' '.$f_pl.', '. $f_pl .'<br>';
    $ptoc_total_time = $ptoc_total_time+$f_pl;
    if(($f_pl == 'Locked')||($f_pl == 'Copying to tmp table')) {
        $locked_f = true;
    }
};

if($locked_f) {
    echo '<div>'.$proc_list.'<br>total time:'. $ptoc_total_time.'</div>';
    if($ptoc_total_time > 100) exit;
}

можно к примеру вместо exit добавить отсылку списка процессов на email.. далее принимать решение.

andrew-projects, большое спасибо. Попробую посмотреть.

Получилось что нибудь?

regado, пока в процессе. Оптимизирую настройки сервера и запросы

Slow query log надо и собрать io статистику во время вылетов.

Ну если много записей и таблицы MyIsam первым делом думается что много блокировок.
SHOW STATUS в случае Isam показывает стату по блокировкам и не только по ним.

И довольно много зависить от отдаваемого объема и способа манипуляций с ним. Например, если делать какой-то мудреный рендеринг или вычисления между mysql_fetch_row, и этих фетчей много (много строк в наборе данных), то коннект (и лок) будет держаться сильно дольше. Соответственно запись в это время будет ждать. По барабану простые или сложные у вас запросы.

inter-job, как только таблица входит в блокировку, при большом количестве запросов в эту таблицу сразу же прыгает i/o и за ним сразу же прыгает LA cpu, в начале темы описано что CPU нагрузки нету.
А были бы локи с очередями, CPU ушел бы в огромный LA

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

Спасибо ребята, столько советов. Буду все пробовать.

Цитата:

если делать какой-то мудреный рендеринг или вычисления между mysql_fetch_row

Нет, таких штук нету вообще.

Цитата (micros):

inter-job, как только таблица входит в блокировку, при большом количестве запросов в эту таблицу сразу же прыгает i/o и за ним сразу же прыгает LA cpu, в начале темы описано что CPU нагрузки нету.
А были бы локи с очередями, CPU ушел бы в огромный LA

Не всегда. Как минимум не всегда. (собственно на прошлой работе трахались с этим много, но там правда mysql крутился на бешеном сервере, который перемолол бы все)
А чисто логически чему там CPU быть занятым при локах? io же асинхронный давно. Треды выжирать не должно. Или?

inter-job, ну как то так: пример лок по обновлению индексов(insert/update) - таблица в локе, собирается очередь огромная, которая спокойно ждет, как только лок убран, эта вся очередь асинхронно (вы все правильно написали) начинает читать, и тут же прилетает новый лок, кто то успел дочитать, кто то нет, кто то начал чтение -  и вот на то что бы реорганизовать всю эту очередь, сверить данные, и обновить очередь тратится CPU причем очень дико.

Все это дело происходит на столько быстро, что отследить смену локов можно только по ИД процесса, если смотреть только на статус то будет казаться что там лок висит постоянно(типа залочилась и висит), но на самом деле нет, их там могло быть уже с сотню от разных евентов, и локи делаются так же в порядке очереди.

Опять же из опыта:  если проблемы с базой - индексы, огромное кол-во insert/update + при этом много SELECT - 100% сразу же выносит cpu, ram не всегда, потому что по рам там лимитировано довольно таки жестко все, но бывает после оптимизаторов и рам уход в 100% + кернел паник c ребутами или глухим зависанием) 

Ну и второе (не применимо к обычным проектам) -  в хайлоад проектах, при больших рейтах конектов (10к/сек и выше) просто удержание конектов nginx/apache - php - mysql в ESTABLISHED и последующий перевод конекта  дальше по состояниям убивает к чертям производительность всего приложения. На уровне ядра системы.  И каждые 0,1 сек выполнения кода могут быть решающими - тормозит проект или нет.

Это же касается удержания очередей (читай конектов) активных для mysql. Когда очередь 500-1000 запросов, переварить ее раз плюнуть, когда очередь из 10 тысяч запросов, просто перебрать ее сохранив порядок - уже не быстрое дело

Изложенное выше описано простым, человеческим языком. Технически это звучало бы по другому. Но суть описана как она есть.

Добавлю еще. 'SHOW PROCESSLIST' отработает  думаю в любой случае. Лог медленных запросов толком ничего не раскажет. А вот если образовался затор по непонятной причине.. то им можно увидеть процессы , статусы. Посмотрел phpmyadmin.. у меня он не обновляет его. Нужен скрипт который будет его гонять раз в секунду.. потом смотреть.. ИЛИ в строке:

if($ptoc_total_time > 100) {} 

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

micros, спасибо, я что-то честно говоря не думал, что там такая запутанная логика, очередь и есть очередь, что-то вынулось, что-то осталось, зачем реорганизовывать ее.. надо почитать поискать будет. (не нашел пока ничего такого кроме ссылка)
Не подскажете, где такое описано?

Я тут почитал внимательнее, что в итоге в момент затыка базы, не работают даже селекты на левые (простые?) таблицы. Получается тогда не локи, ага.

Если уж мигрировать - может перкону сразу?

inter-job, пока без миграции пробую решить проблему. Очень не хочется так сильно заморачиваться с этим всем.

inter-job,
Каждый запрос обрабатывается отдельным соединением - т.е. параллельно. Если одинзапрос не блокирует другой запрос, они выполняются независимо друг от друга. Если параллельных
соединений много, то может случиться ситуация, когда все соединеия будут исчерпаны.
Их количество определяется директивой max_connections (обычно несколько сотен). Если
соединение блокируется - оно ожидает, когда нужные участки таблицы (InnoD или сама
таблица (MyISAM) освободятся. В этот момент соединение не доступно и обслуживается
другими свободными соединениями. Если блокировок много и много потоков ждут, соединения
начинают заканчиваться и на обслуживание быстрых запросов остается мало соединений.
Если соединения вообще закончились и мы уперлись в потолок max_connections сервер дает
отлуп на новые запросы и просто их не обрабатывает, возвращая ошибку "Too many connections".
Бесконечно увеличивать размер соединений нельзя, так как каждое потребляет оперативную
память и не мало, порядка нескольких мегабайт, в зависимости от настроек в my.cnf.

Блокировки, как правило, случаются только при записи и обновлении. Чем быстрее выполняются
запросы - тем меньше времени происходит блокировка и тем быстрее выполняются остальные
запросы. Чем быстрее выполняются запросы - тем быстрее они освобождают соединения для
других запросов. Поэтому обычно основная задача разработчика - добиться как можно более
быстрого выполнения запросов. Если в системе много блокирующих запросов (как правило,
на запись) - она начинает загибаться, копится очередь ожидающих соединений, исчерпывается
оперативная память, даже если потом блокировка снимается, накопившиеся запросы все-равно
нужно выполнять.

гуглить по запросу - очередность выполнения запросов mysql

Переход на перкону ниче не даст, это все mysql))) у перконы вообще другая фишка, не решающая подобные задачи оптимизацией)) Мария имхо самый быстрый форк вне кластера  

micros, про коннекты я знаю и думал, но такое разве не просто отслеживается в логах?

Я именно просил про загрузку CPU по причине пересортинга очереди локов. Искал вообще что там за очередь такая хитрая, и в чем смысл ее пересорта, но с первого раза ничего не нашел.

Переход на перкону даст более человечные средства мониторинга как минимум.

inter-job, ссылка начните с этого, что бы понимать как устроено, и потом читайте по обработке очередей, по этой же тематике - тасклеты, softirq, ksoftirqd, workqueue

Что именно используется mysql я не помню(я давно так не заморачивался), но если сильно интересно делайте так - скачать исходники mysql, и по именам методов того или иного менеджера тасков поищите. А дальше углубляйтесь в распределение и перечитку очередей. Хотя в плане реорганизаций очередей они +- одинаково работают, разница там в стуктуре и приоритезации и низкоуровневых методах взаимодействия

Эти механизмы использует подавляющее большинство ПО. Ну собственно как и остальное апи ядра системы) 

А расскажите про человеческие методы отладки на перконе) Что там есть, чего нету в той же марии например?=)

Ребята, а кто может подскажет еще по netstat, нужно посмотреть сколько подключений с каждого IP адреса идет.

netstat -plan|grep :80|awk {'print $5'}|cut -d: -f 1|sort|uniq -c|sort -nk 1

Эта команда показывает стату по подключениям по всему серверу.
А как посмотреть только по определенному URL? Возможно такое?

1. Найти селекты, которые тормозят.

select * from users where email='' and phone=''

2. Создать индексы

ALTER TABLE `users` ADD INDEX `email_phone` (`email`,`phone`)

100 запросов в секунду высокая нагрузка? ))))))))))))))))

DimaShpak, а если по делу?