Всем доброе время суток.

Разработал сайт для одного интернет-аукциона. Все было неплохо, пока не начало расти количество пользователей (как всегда). В итоге сейчас дошло до того момента, что в пиковый момент под закрытие аукциона все начинают делать ставки и при 300 он-лайн пользователях идет 100% нагрузки на проц. Сервер отдельный для БД, мощный. 2 проца по 4 ядра, 12 Гб оперативки, RAID ...

В начале грешил на конфиг мускула, подправил - не помогло. Пользуюсь советами от mysqltuner - но не помогает. Дальше пошел к запросам на БД. большая часть запросов связана с таблицей ставок



В пиковый момент идет примерно 7 ставок в секунду, т.е. 7 Insertов в таблицу и порядка 60 чтений из нее. Сервер должен бы с таким справиться, но увы (((. 

Самый основной запрос - это получение таблицы ставок

Оптимизировать его никак не получается. Сортировка в таком порядке обязательна. А сложный индекс какой только не пробовал - не помогает.

Так же частый запрос пользователя на наличие ставки


На данный момент я решил делать для каждого аукциона отдельную таблицу ставок. Это уберет выбор по аукциону и индекс auction_id. И появится смысл сделать индекс по полю sum. Тесты показали что и запись и чтение вырастут примерно в 2 раза. Но все-равно останется запрос 

КОторый будет просматривать всю таблицу.

Вопрос:
1. Можно ли сделать индекс по полю status и sum, но только чтоб он сортировался именно status, sum DESC иначе от него не будет смысла.
2. Для хранения истории ставок я после закрытия аукционов перекидываю их в таблицу rates_close. Но если делать таблицу на каждый аукцион - то потом могут пересектись ай-дишники. Как в  этом случае лучше поступить. Выделять для каждой таблицы к примеру по 100 000 индексов? пока хватит, но если вдруг не хватит - все полетит?

Проблема очень для меня важная, буду очень признателен за любой совет, Спасибо!

как вариант попробовать innodb, в целом она медленнее чем MyISAM, но при редактировании лок на уровне строк а не целой таблицы.




хороший тон - для double  без кавычек.



если status это флаги, значений которых несколько, а не 2^32-1, то можно заюзать enum.




поясните, будете для каждого auction_id - create table выполнять? Это пагубно и не реляционно.



имхо, индекс по случайному флоту ничего не даст.

Добавлено через 4 минуты и 45 секунд

везде, где возможно замените на значение (0, 0.0) это некоторые излишние проверки минимизирует

Сделал тестовый скрипт для проверок нагрузок. Скрипт имитирует совершение ставки пользователем. Примерно 15 запросов. Со вставкой записи, чтение таблицы ставок и т.п. Запускал 500 запросов, параллельно 10. Результат:

Добавлено через 4 минуты и 9 секунд
InnoDb - 53 секунды
MyISAM - 22 екунды
если сделать отдельную таблицу для каждого аукциона - 8 секунд на MyISAM

Да, status - 0, 1, 2. TinyInt (2) подойдет, спс.

Аукцион закрывается 3 раза в неделю. Максимум открытых аукционов - 9. По этому для каждого открыть отдельную таблицу не так чревато. Если это ускорит работу в 3 раза, как показали тесты. После закрытия аукциона ставки перенесутся в общую таблицу rates_close и отдельная таблица удалится.

предлагаю попробовать такое: использование mysql как nosql

а когда их станет 109? 


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

Как я понял, у вас достаточно стандартный SQL, следовательно можно пощупать другие реляционные СУБД, смысл в этом обрисуется, когда открытых аукционов станет 1009   

Почитаю, отпишусь

Добавлено через 8 минут и 11 секунд
triclosan, т.е. хочешь сказать, что мускулу такие нагрузки не под силу? что такое 10 вставок в секунду - чепуха

хотя, нет. рекомендую начать с "set profiling=1", чтоб выяснить, на каком именно этапе тормоза появляются.

значит что-то не так делаю. Или мускул такой слабый, что не выдержит 10 вставок в секунду? 


Да, еще вопрос. На сервере стоит мускул 5.0 Есть смысл переходить 5.1. И это что-то повредит или смело переходить, там серьезных изменений нет?

Добавлено через 4 минуты и 4 секунды
skyboy, тормоза появляются на запросах 

и 


Кстати поставил полю status - tinuint(1) и убрал кавычка в запросах - время выполнения уменьшилось с 22 сек до 15 сек для цельной таблицы.

Так идея разделить таблицы - это бред? не стоит такое делать?

Добавлено через 10 минут и 5 секунд
т.к. для отдельной таблицы для аукциона сейчас время выполнения 500 ставок уменьшилось до 3.5 секунд. Но в отдельных таблицах опасный вопрос - это пересечение ай-ди ставок. Не угадаешь на перед сколько будет на 1 аукцион. Сегодня например эта цифра 50 000 ставок, а неделю назад была 15 000 ставок

слабый, но вообще-то не настолько



этого никак не избежать, можно чего-то додумать чтобы с double не сравнивать?



вот это интересно, стоит подумать это именно тормозит - софт или железо.




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

Это сообщение отредактировал(а) triclosan - 14.12.2010, 17:45

1. сделать индексы по status, sum. ASC или DESC никакой роли не играет
2. сделать sum NOT NULL (default 0)
3. (если возможно) изменить тип sum на INT, сохраняя умноженные на 100 значения
4. каков размер БД? Нельзя ли для текущей работы использовать HEAP, копируя, при необходимости, в постоянную таблицу (например, после торгов)

По поводу предыдущих запросов - все в порядке. помог индекс на поле sum и изменение поля stаsus на  tinyint

Сейчас глючит вот такой запрос


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

Вот это я дурак. Эти запросы можно заменить на запрос 


Так и сделал и добавил еще один индекс по полям auction_id, status, sum. Время выполнения 500 ставок стало 1.6 сек. И как после такого в чудеса не верить. ))

Спасибо большое всем за помощь

Все, да не все. Остался запрос


Он не оптимизируется никак. Как ни крути, а все-равно выборка идет по всей таблице с auction_id=439. Тут ничего нельзя сделать?

Это сообщение отредактировал(а) shevak - 15.12.2010, 00:10

должен работать быстро.

Добавлено через 11 секунд
поле sum NOT NULL DEFAULT 0 сделал?

В порядке бреда


Добавлено @ 14:35
Или уж совсем

shevak, покажи explain select

Да, сделал, и индекс по нему поставил. А так же индек по полям auction_id - status - sum. Он помогает на других запросах. а на этом - нет

Добавлено через 2 минуты и 36 секунд
Explain


Добавлено через 5 минут и 17 секунд
Akina, ни тот ни другой запрос не помог

Ожидалось, в принципе...
Попробуй пойти дальше, и вынести в последнем варианте подзапрос в статический вьюв.
А заодно посмотри, сколько записей он выбирает. Может, мы зря копья ломаем?

Это не то. auction_id=439 выбирает все строки правильно и их сортирует. Потому и получатся 39957 строк. Но если б можно было поставить для ключа auction_id, status, sum нужную сортировку, то ситуация б улучшилась. Возможно ли такое в мускул? мне надо чтоб сортировал auction_id, status. sum DESC

Добавлено через 5 минут и 53 секунды
Сейчас к примеру если я убираю сортировку DESC  - все работает четко и быстро.

Добавлено через 12 минут и 11 секунд
Когда ставлю DESC - появляется Using filesort а число строк и там и там одинаковое

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

Вот сам запрос


Никакой сортировки по auction_id нет. Это для того чтоб ключ был эффективным я добавляю в него auction_id

Добавлено через 6 минут и 37 секунд
Думал сделать что-то типа такого


Но не помогло, наверное синтаксис неправильных, хотя отработал нормально

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

индекс auction_id_3 - это с полем id. Время выполнения запроса - 0.0006 sec





Время выполнения запроса 0.0507 sec




Время выполнения запроса 0.0613 sec

А чем я и говорю, мешает параметр DESC, только как его в формирование индекса в ставить

Ну при указании индекса можно указать ASC/DESC - только на текущей версии оно игнорируется и строится всегда в ASC. Это даже в документации описано.
Однако если эта операция действительно критична, могу предложить пойти по пути увеличения таблицы. Ввести в неё ещё одно поле (скажем назвать его sum2)? и писАть в него (0-sum). Включить именно его в индекс, и именно этот индекс использовать при сортировке.

Akina, у меня версия 5.0, случайно не знаешь в 5.1. исправили это проблему? или хотя бы в 5.5

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