На сервере MySQL жрёт 99% CPU. Как мне оптимизировать скрипты, чтобы меньше жрала, какие есть способы.

m1kle, запросы в студию

MoLeX, Их куча. В основном в программах, которые выполняются каждую минуту автоматически.

Например: $q_zav=mysql_query("select * from `zavodi`");
$zav=mysql_num_rows($q_zav);

или

$sel_p1_precomp=mysql_query("select * from `inv` WHERE `stat`='1' and `name`='$p1[4]'");
$p1_precomp=mysql_fetch_array($sel_p1_precomp);

тогда может кешировать результат?

MoLeX, а как это? Это сильно уменьшит CPU?

все зависит от того сколько данных в БД. но в основном да...

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

т.е. на сервере - только MySQL крутится? ни apache, ни РНР?

skyboy, есть Apache и PHP.

Информации около 50 Мб.

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

Это сообщение отредактировал(а) m1kle - 21.5.2008, 16:11

m1kle, делается это просто.
Сначала надо узнать, какой запрос оптимизировать.  Внимательно читаем предыдущее предложение. оно в этом топике самое важное. Как и во всех вопросах по оптимизации. Оптимизировать надо не запросы вообще, а те, которые реально тормозят.

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

После того, как нашел самые тормозящие - пытаешься понять их неоптимальность или исправить. Если не можешь самостоятельно - приходишь с этими запросами сюда.


Это можно оптимизировать и без замеров. Оторвав предварительно руки.
$q_zav=mysql_query("select count(*) from `zavodi`");

(продолжая тему избирательной оптимизации)....да и вообще не в БД может быть дело. сокеты с большим timeout по дохлым адресам, вызовы сомнительной надежности программ через system.... да, мало ли ещё чего!
вобщем, первая задача - найти узкое место.
потом уже разберемся, как оптимирзировать.

Попробую..

Попробовал
$q_zav=mysql_query("select count(*) from `zavodi`");

Он почему считает только одну надпись. А работать стал действительно быстрее на 1 секунду.

m1kle, 
Слово профайлинг тебе что-то говорит?
Если нет, то прочитай эту заметку http://habrahabr.ru/blog/php/37834.html

И попробуй сделать то, что там описано. Будут вопросы - задавай.

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

делается только для того, чтоб получить количество записей

вот тебе Feldmarschall и привел пример оптимизации: переделал запрос так, чтоб запрос сразу возвращал только количество(одна запись), а не все подряд, только для подсчета.
если тебе надо не только количество, но и данные, то, естественно, такая оптимизация не подходит. но, черт возьми, только ты знаешь, что тебе точно надо. соотвественно, оптимизация - на твоих плечах.
P.S. Ну, как? Провел поиск узкого места? или просто на глаз определил, что "так работает быстрее"?

skyboy, Потихоньку разбираюсь. Мне данные не нужны, только количество. Что-то я непонимаю.
 - это старая запись, она очень тормозит, и как я понял делает ненужную работу.
 - тут проблема в том, что не выводит сколько записей в таблице. ( выводит "1" при echo $zav; )


Fortop, профайлинг ничего не говорит, обязательно прочитаю статью.

Ещё есть вопрос про оптимизацию.

Правда-ли, что 

"
*Селект из базы один
*Селект из базы два
*Селект из базы один"

Будет больше тормозить, чем
"
*Селект из базы один
*Селект из базы один
*Селект из базы два
"

Т.е. лучше сначало работать с одной таблицей, потом с другой, чем сначало с одной, потом со второй, а потом опять с первой.

Слушай, если ты не можешь получить строку из БД, то тебе не оптимизацией надо заниматься, а учиться с БД работать. 
http://www.intuit.ru/department/database/mysql/12/ - учебный курс. Освой хотя бы азы. Чтобы такие вопросы тебя не ставили в тупик.



Это вопрос не про оптимизацию. 
Ещё раз, если ты не понял:
Оптимизировать надо то, что тормозит. А не то, про что кто-то где-то сказал.

А если отойти в сторону от функции PHP mysql_num_rows()
и почитать что тебе возвращает SQL функция COUNT()?

не хочешь попробовать?

Без понимания что такое профайлинг и для чего он - оптимизировать бесполезно.

Fortop, что такое профайлинг я кажется понял, но как его использовать нет. Пороюсь в интернете.

И ещё, там же про PHP код говорится.

Про count тоже поищу в интернете.

Это сообщение отредактировал(а) m1kle - 7.6.2008, 01:00

m1kle, 
xdebug.org,
плюс на Хабре были заметки про то, как проводить профайлинг.

Собственно ничего сложного. Ставишь xdebug, настраиваешь его.

Затем запускаешь скрипт на выполнение. Служебная информация пишется в указанную тобой директорию.

Просмотреть ее можно через WinCacheGrind.

Выхватываешь самые медленные куски кода и начинаешь разбирать уже их. Если это SQL запросы, то копируешь их и делаешь в MySQL
EXPLAIN для каждого из запросов.

Разбираюсь, там не так то просто установить, на UNIX.

Xdebug очень сложно скомпилировать.
Какое приложение отвечает за команду phpize? А то у меня она пишет, что команда не найдена.

Сорри за даблпостинг - не заметил.

P.S.: Функцией count(*) я пользоваться научился.

И ещё, я тут задумался о Xdebug'e, а нужен ли он вообще, время выполнения можно посмотреть и в HeidiSQL. Другое дело, как именно оптимизировать.

Это сообщение отредактировал(а) m1kle - 9.6.2008, 17:55

для первичного профайлинга достаточно функции microtime()

m1kle, если у тебя фря, идешь в порты (если PHP собран из портов) 



Потом тебе предложат подключить сошку в конфиг как zend_extension. Подключаешь. Дальше настраиваешь конфиг по ману и радуешься жизни.

А еще есть такая фича как EXPLAIN запросов в MySQL. Проверь все свои запросы, используют ли они индексы и т.п.
Подробнее...
А еще лучше, как кто-то тут сказал, попробовать для начала основы программирования и баз данных выучить. И, заодно, как пользоваться интернетом (в т.ч. http://google.com).

P.S. Ребят, я плакал с этой темы)

Feldmarschall, Вот в чём проблема я непонимаю, как оптимизировать, мне почему-то всегда кажется, что всё и так по максимальному оптимизировано.


IZ@TOP, cd /usr/ports/devel/php-xdebug/ - такой папки у меня нету.



 Вот например код, ну что тут можно оптимизировать?

Feldmarschall, +1.


m1kle, а я и не говорил что она у тебя должна быть. Я ведь не знаю какая у тебя система, просто для примера привел.
А вот что оптимизировать в этом коде, это ты должен сам определить. 
Хотя бы запросы протестируй как я говорил:




Это сообщение отредактировал(а) IZ@TOP - 10.6.2008, 15:55

IZ@TOP, тут апдейты в цикле. И не в цикле. И по одному, и пачками. И вся база перетряхивается. 
Этот тот случай, когда эксплейн не поможет - здесь руки надо выпрямлять и мозги вправлять, а не отдельные запросы тестировать.
Тут только моя рекомендация поможет, которую добрый модератор удалил. Потому что тишина должна быть в библиотеке.

Добавлено через 3 минуты и 46 секунд
меня другое радует:



Добавлено через 5 минут и 7 секунд
m1kle, как часто выполняется этот скрипт?
каков размер таблицы zavodi и других, перечисленных в этом скрипте?

IZ@TOP, тут апдейты в цикле. И не в цикле. И по одному, и пачками. И вся база перетряхивается. 

Так получается, это замысл работы скрипта.
Про count(*) в такой маленькой БД count ничего не даст.

Work 4 Кб
Zavodi 12,4 Кб
Users 295 Кб

Скрипт выполняется раз в минуту.

Как считает, можно что нибудь сдесь улучшить?

какие интересные таблицы. а где же остальные 50 мегабайт лежат?

Вот об том и речь, что замысел и надо переделывать. Замысел. Заплатками тут не обойдешься.
Не нужно раз в минуту ничего апдейтить. БД не для этого предназначена. 
все данные можно вычислить по надобности. пришел игрок через час - посчитал его параметры - и записал в базу. один раз за час. а не 60.
Это азы. основы. И у тебя они не выполняются. И оптимизировать этот винегрет из апдейтов - бесполезно.

ну ХОТЯ БЫ сократить эту колбасу до трех запросов можно было догадаться?

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

50 мегабайт, в другой таблице, в которая не участвуете в предоставленным мною скрипту.

"Вот об том и речь, что замысел и надо переделывать. Замысел. Заплатками тут не обойдешься.
Не нужно раз в минуту ничего апдейтить. БД не для этого предназначена. 
все данные можно вычислить по надобности. пришел игрок через час - посчитал его параметры - и записал в базу. один раз за час. а не 60.
Это азы. основы. И у тебя они не выполняются. И оптимизировать этот винегрет из апдейтов - бесполезно."

В онлайн играх есть такая штука, как работа, там нужно каждую минуту проверять, не закончил ли кто-то работать, это необходимо.

"ну ХОТЯ БЫ сократить эту колбасу до трех запросов можно было догадаться?"

Разве можно делать сразу несколько запросов к таблице одним Апдейтом?

"и сколько у тебя таких скриптов, которые раз в минуту выполняются?"

Есть ещё один, огромный, двести строк.

Остальные раз в час, раз в день.

НЕСКОЛЬКО ПОЛЕЙ в одной строке можно обновлять ОДНИМ запросом.
если ты в таблице users меняешь поля money, rh, и dob, то это можно, и нужно! делать одним запросом. а не десятью.
ты так и не ответил на вопрос, сколько всего запросов исполняется

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

Зачем?

Данные нужно проверять лишь при обращении к ним. Иначе у тебя никакая база не выдержит.

Feldmarschall, прочитал я синтаксис оператора UPDATE, стало многое понятно.


skyboy, всегда кто-то работает, всегда нужно уменьшать время сколько осталось работать на еденицу.

Fortop, У мен так и есть, если что-то лежит в базе - делаем, не лежит, не делаем.



Вот новый код. Помоему по сравнению со старым кодом, это просто супер-код. Что то ещё можно сделать?

Это сообщение отредактировал(а) m1kle - 12.6.2008, 23:03

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

у тебя реальная работа с лопатой за окном или модель, основанная на БД и алгоритмах? 
положим, мне надо определить время выполнения скрипта. как мне лучше сделать: каждую секунду увеиличать счетчик секунд на 1 или посчитать время до запуска, время после выполнения и вычислить разницу?

Тебе skyboy, уже привел пример.
Вопрос не в делаем/не делаем.

Вопрос в том, что ты не должен обновлять данные каждую секунду/минуту. А рассчитывать их когда какой-либо пользователь захочет их прочитать. Рассчитал, и сохранил с новым временем. Теперь при следующем обращении ты будет отсчитывать от текущих значений.

И только 1-2 раза в сутки ты можешь сделать глобальный пересчет. Например для статистики/рейтингов и т.д.

Пример.

Есть иммитатор выращивания морковки. Вместо того чтобы каждую секунду обновлять базу, что какая-то морковка выросла на 1мм.
Мы сохранили ее состояние на момент посева 0мм и время. Также у нас есть формула за сколько времени она вырастает на 1мм.

Тогда, если пользователь посмотрел на эту конкретную морковку.... 
1. Мы читаем состояние из базы, вычисляем разницу между временем текущим и тем что в базе. 
2. Записываем новое состояние (3мм и время в базу).

Все остальное время мы базу не трогаем.

Похоже я начинаю въезжать. Только реализовать вычитание из одного времени другое?

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

Я бы, все-таки, разобрался сначала, что именно у него там тормозит.
удивительно ведь - база крошечная, все в памяти крутится.  Тупо от количества запросов ложится? 
Или аффтар врет, что таблицы только маленькие. Ну что за база юзеров размером в 300 килобайт. что у него - всего пара тыщ человек зарегистрировано, что ли?
или тормозит совсем в другом месте.

Надо смотреть в базе show processlist в момент, когда проц занят
смотреть slow queries log

Да, две тысячи сто с небольшим человек.

В этом скрипте уже неважно, где тормозит, его скоро не будет.

хех. действительно. теперь будет тормозить в другом =)

Feldmarschall, на счет выпрямления конечностей согласен. И за дурачка, пожалуйста, меня не держи, ок? А вот апдейты - не самое страшное, если на поля в условии созданы индексы. Еще меньше это все роляет, если таблица InnoDB с транзакциями. 



Я плачу...

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

Я как хотел - так и сказал. Давайте не будем путать понятия: апдейт и инсерт. 
А что происходит, когда на таблице MyISAM проходит запрос на UPDATE с условием по полю, на котором нет индекса я видел неоднократно. И результат, самое главное, всегда один: забивается очередь запросов.

По поводу перестройки индексов, читаем оф. документацию:


Это сообщение отредактировал(а) IZ@TOP - 18.6.2008, 14:27

Я же сказал, откажусь от функции, которая выполняется раз в минуту. Но тут другая проблема, допустим я записал в базу время устройства на работу..как мне его вытащить, и вычесть его из текущего времени?

m1kle, тут свои дебаты 
http://dev.mysql.com/doc/refman/5.1/en/dat...-functions.html на счет Вашего вопроса, советую ознакомиться.

m1kle, мы слышали, что ты сказал.
Мы только не видели информации, которая показывает, что все тормоза именно из-за этого скрипта.


IZ@TOP, честно говоря, эти два предложения звучат для меня взаимоисключающе. Надо будет поспрашивать специалистов...

Feldmarschall, а то что написано в официальной документации по MySQL тебя не устраивает? За специалистами далеко ходить не нужно. Я могу ответить на большинство твоих вопросов исходя из знаний/опыта.

Это сообщение отредактировал(а) IZ@TOP - 18.6.2008, 14:35

Да, меня не устраивает, когда там написаны противоречащие, на мой взгляд, друг другу вещи.
Второе предложение в твоей цитате из мануала, я, со своим скромным знанием языка, перевожу так:
"Скорость записи зависит от объема обновляемых данных и количества обновляемых индексов." Из чего я делаю вывод, что индексы обновляются. Что противоречит следующему предложению. Возможно, я неправильно понял.

Добавлено @ 14:44
Тьфу. Я тормоз. пропустил слово "that" в следующем предложении. 
Сорри. То есть, обновляются не все индексы, а только те, в которых данные поля изменились.
то есть, индекс по id, к примеру, не перестраивается.
А если есть по полю date, и оно изменилось - то перестраивается

Это сообщение отредактировал(а) Feldmarschall - 18.6.2008, 14:44

Feldmarschall, верно!
На самом деле, я сам не в совершенстве знаю MySQL. Поэтому, если в чем-то не уверен (да даже если уверен), иду в доку и смотрю что к чему.

http://dev.mysql.com/doc/refman/5.1/en/dat...-functions.html на счет Вашего вопроса, советую ознакомиться. 

Помоему глупо использовать таких вещей MySQL, не быстрее будет сделать с помощью PHP.

И ещё мне непонятно, как вычесть из одной даты другую средствами mktime()

Я пытался так, неполучилось.

m1kle, в плане удобства, проще вычислить на уровне MySQL.
Что-то вы прям извращение в коде придумали, честное слово... 



Так не проще? Получаем поле в результате выборки с интересующей нас информацией.

Эта штука показывает разницу в днях, а мне нужна разница в минутах..

Вот ведь ленивый человек!

http://dev.mysql.com/doc/refman/5.1/en/dat...n_timestampdiff
Пример 1.1:


http://dev.mysql.com/doc/refman/5.1/en/dat...nction_timediff
Пример 1.2:



Это сообщение отредактировал(а) IZ@TOP - 19.6.2008, 16:11

IZ@TOP, Прости пожалуйста, не заметил . 

Теперь работает, очень благодарен.

Поставлю тебе плюсик в репутацию ;)

Буду пользоваться. Но оптимизация на этом не завершена.

Расскажу о своей самой большой временной функции, выполняется раз в минуту, 132 строки, выполняется 0,3 секунды.