Спецы по MySQL и сила гофака! Нужна помощь!

[DimaX]

Привет всем, а реально крутым специалистам по MySQL особенно
Предыстория:
Жил-был на обычном виртуальном хостинге oscommerce шоп, в базе которого примерно 1.5к товаров, жил не тужил более 1.5 лет, нагрузка от него была такая:

Дата CPU MySQL
2012-09-12 19 158
2012-09-11 20 165
2012-09-10 17 150
2012-09-09 15 134
2012-09-08 16 137
2012-09-07 18 153
2012-09-06 18 186
2012-09-05 18 172
2012-09-04 19 160
2012-09-03 16 119
2012-09-02 15 124
2012-09-01 12 124

Данная нагрузка вписывалась в лимиты хостинга (500 единиц по MySQL в день) и все было круто. Но 13 сентября была внедрена крутая идея, сделать тотальную распродажу, запихнув все 1.5к товаров в таблицу specials (в которой и содержатся товары со спец ценой).
После этого, нагрузка выросла просто в дичайшие разы:

Дата CPU MySQL
2012-09-28 23 784
2012-09-27 25 906
2012-09-26 27 1055
2012-09-25 19 794
2012-09-24 26 1002
2012-09-23 24 897
2012-09-22 25 971
2012-09-21 27 1189
2012-09-20 23 921
2012-09-19 26 1283
2012-09-18 29 1691
2012-09-17 34 2005
2012-09-16 24 1467
2012-09-15 18 809
2012-09-14 25 1172
2012-09-13 24 382

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

Соответственно, спустя несколько дней (17 числа, кажется), распродажа завершается, полностью удаляются все распродажные товары из таблицы specials, распродажные цены устанавливаются всем товарам на постоянку, все, казалось бы, возвращается к тому же, с чего началось (только с измененными ценами), но вот незадача - нагрузка не вернулась к старым значениям почему-то, и продолжает оставаться стабильно очень высокой! Да, кстати. Дней 5 назад был активирован кеш, который ранее вообще не включался (я даже не знал, что он есть в движке), но это, как видно, не помогло.

Внимание, вопросы к специалистам

1) Из-за чего могло такое случиться, что после удаления изменений, нагрузка все равно осталась запредельной?

2) Могло ли что-то зависеть от того, каким способом добавлялись товары в таблицу specials или менялись массово цены у товаров? Я написал небольшой скрипт для этого, а т.к. я с MySQL знаю буквально SELECT и WHERE, может че не так в запросах было, о чем я не в курсе, что может как-то закосячить базу или типа того? Вот, собственно, скрипт:

PHP код:

<?php

set_time_limit(0);

$dbu = array('host' => '', 'dbname' => '', 'user' => '', 'pass' => '');

$db = mysql_connect($dbu['host'], $dbu['user'], $dbu['pass']) or die('Не могу сконнектиться к БД');
mysql_select_db($dbu['dbname'], $db) or die('БД не найдена');

mysql_query("SET NAMES 'cp1251'");

$sql = 'SELECT `products_id`, `products_base_price`, `products_price` FROM `products` WHERE `products_base_price` != 0 AND `products_status` != 0';
$result = mysql_query($sql);

while($row = mysql_fetch_assoc($result))
    {
        if ($row['products_base_price'] <= 10)
            {
                $koeff = 2;
            }
            elseif ($row['products_base_price'] <= 20)
            {
                $koeff = 1.5;
            }
            elseif ($row['products_base_price'] <= 30)
            {
                $koeff = 1;
            }
            else
            {
                $koeff = 0.5;
            }
        
        $new_price = round($row['products_base_price'] + $row['products_base_price'] * $koeff);
        
        if ($new_price >= $row['products_price'])
            {
                $new_price = round($row['products_price'] - $row['products_price'] * 0.1);
            }
        
        #если надо добавить спец предложение, а не изменить цены у товаров
        #$sql2 = 'INSERT INTO `specials` (`specials_id`, `products_id`, `specials_new_products_price`, `specials_date_added`, `specials_last_modified`, `expires_date`, `date_status_change`, `status`) VALUES ("", "'.$row['products_id'].'", "'.$new_price.'", "'.date('Y-m-d H:i:s').'", NULL, NULL, NULL, "1");';
        
        #если надо массово изменить цены у товаров
        $sql2 = 'UPDATE `products` SET `products_price` = '.$new_price.' WHERE `products_id` = '.$row['products_id'];
        $result2 = mysql_query($sql2);    
    }
?>

З.Ы. Вы даже не представляете, как я надеюсь на гофак))

[DimaX]

Сообщение от creator123

теперь запихай эти запросы поочереди в phpmyadmin
выполни, а потом тыкни "Explain SQL"
оно покажет о чем думало, и чем занималось так долго.

Попробовал, вот по первому запросу ответ:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE pd ALL PRIMARY NULL NULL NULL 1450 Using where; Using temporary; Using filesort
1 SIMPLE p eq_ref PRIMARY PRIMARY 4 basename.pd.products_id 1 Using where
1 SIMPLE m eq_ref PRIMARY PRIMARY 4 basename.p.manufacturers_id 1 Using index
1 SIMPLE s ALL NULL NULL NULL NULL 6
1 SIMPLE p2c eq_ref PRIMARY PRIMARY 8 basename.p.products_id,const 1 Using where; Using index

Вот по второму:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE p ALL PRIMARY NULL NULL NULL 1450 Using where
1 SIMPLE p2c eq_ref PRIMARY PRIMARY 8 basename.p.products_id,const 1 Using index

Где здесь говорится, где и что долго?

[creator123]

ну тока вот это Using filesort
но скорее всего причина не тут.
так в phpmyadmin запросы эти скока времени выполняются? Они правда тормозят?

[DimaX]

Сообщение от Алёша

use base_name;
describe table_name;
смотришь, есть ли там PRI в колонке 'key'

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

Сделал, PRI есть (смотрел у таблиц продуктов распродажных товаров).

Сообщение от creator123

ну тока вот это Using filesort
но скорее всего причина не тут.
так в phpmyadmin запросы эти скока времени выполняются? Они правда тормозят?

Несколько раз рефрешнул их, первый запрос 0.4489 сек, 0.4518 сек, 0.0008 сек, 0.4403 сек, 0.0005 сек, а у второго время выполнения не пишется, может оттого, что он моментально выполнился, хз.

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

[creator123]

Чет я еще раз посмотрел на запросы и нагрузку.
А может просто возросла посещаемость? Ботов стало больше?
Что там с кэшированием например?

[DimaX]

Сообщение от creator123

Чет я еще раз посмотрел на запросы и нагрузку.
А может просто возросла посещаемость? Ботов стало больше?
Что там с кэшированием например?

Посещаемость буквально уников 150 стабильна как ничто. Т.е. и год назад было 150, и 4 месяца назад 150, и вчера вот 169, а сегодня где-то 140 будет.
Парсил access-лог, никакой аномалии не заметил, никто вроде сайт не парсит, одни боты гугла да яндекса, но они в рамках разумного, как и раньше было.
Кеширование включено дней 5 назад, заметного спада нагрузки не случилось.

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

[chesser]

кстати, описанная проблема напоминает переполнение LRU или подобных алгоритмов кеширования

а хостер в чем нагрузку mysql меряет?
попробуйте включить/отключить кеширование в мускуле.

ps а вообще, имхо топик не про mysql, а про oscommerce и , видимо, какие-то его особенности

[DimaX]

Сообщение от chesser

а хостер в чем нагрузку mysql меряет?

Без понятия, просто в панели управления хостингом на отдельной вкладке "Нагрузка" выводятся те цифры, что я привел в 1 посте.

Сообщение от chesser

попробуйте включить/отключить кеширование в мускуле.

Так мне их администраторам что конкретно написать, чтобы они включили или выключили кеширование мускула? И как это может помочь?

[chesser]

Сообщение от DimaX

Так мне их администраторам что конкретно написать, чтобы они включили или выключили кеширование мускула? И как это может помочь?

если буфер кеша запросов уже забит и постоянно обновляет сам себя, то либо увеличить буффер, либо отключить кеширование запросов вообще. Если кеш запросов выключен, то можно попробовать его включить.

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

[DimaX]

В общем, решилось дело так.

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

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

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