1. Соблюдайте Правила форума и проявляйте уважение к другим участникам беседы.

Оптимизация insert select запросов бота в БД.

Тема в разделе 'Кодинг/Собственные решения', создана пользователем Mefix, 6 дек 2011.

Статус темы:
Закрыта.
  1. Mefix Наш человек

    Сообщения:
    114
    Спасибы:
    3
    Идея состоит в том чтобы оптимизировать запросы чтобы бот не перелопачивал каждый раз кучу значений.

    Сейчас к примеру как понял:
    В БД 200 человек, 30 игр из ник сыграл Пупкин.
    Пупкин запросил стату с запросом средних игр.
    Перелопачивается бд, ищет значение из 200 игр, где играл Пупкин, считает средний разультат, убийств, смертей, смотрим сколько всего записей(знач столько игр), из них вычитаем тех которые вин, и прочие данные...
    Соответственно, когда заканчивается игра и считается поле Score, бот опять перелопачивает все эти данные.

    Моя идея оптимизации состоит в том:
    Когда заканчивается игра, поле Score считаем по формуле, но Score мы не заного вставляем, а плюсуем по уже добавленным общим значениям, тоесть:
    Пупкин сыграл игру:
    Данные о нём занеслись в gamePlayers, кроме этого, в таблицу Scores в новое поле в его записи идёт апдейт значения, к старому плюсуется новое, то есть было убийств 343 за все игры, а в новой он убил 10, соответственно плюсует 10 и становится 353, и так все остальные значения. Так же добавляется туда поле gamecounts, смертей, и др значения. Для полей с уже усреднённым значением, сколько убийств в среднем за игру, сколько смертей в среднем за игру. и прочее и прочее.
    Соответственно когда Пупкин запрашивает статистику, так же изменяем чтобы запрос не перелопачивал полностью базу данных, а брал уже готовые значения, которые посчитаны ранее. Я считаю что с помощью такой оптимизации можно уменьшить нагрузку на Mysql в десятки раз, + быстроту доступа данных.

    Сперва считаю нужно реализовать такую работу бота, а потом уже сделать скрипт который все уже имеющиеся значения в БД приведёт к новому формату. И протестить.

    Вопрос состоит в том, кто поможет разобраться и направить в нужное русло, ибо смотрю в код, и что-то не понимаю как в боте запрос по таблицам идёт. Ибо не вижу запроса к таблице, а только к полю. Так сказать, не понимаю как реализована работа ghostdbmysql.
  2. Deals Старожила

    Сообщения:
    784
    Спасибы:
    21
    Сборка бота GHost:
    Ghost One 1.7.266
    Skype:
    Мой статус
    Я думаю что хранить все данные сразу в боте - будет огромная нагрузка. Учитывая что бот держит много игр + работает с сетью и +++++ еще много "дел" которые долго обрабатываются. Для облегчения на бот нагрузки в БД MySQL каждый запрос отправляется в отдельном потоке данных. Эта система называется Multithreading. И вообще боту своей "работы" хватает. Вместо него пусть еще "подумает" MySQL.
    ghostdb - базовый класс, ghostdbmysql или ghostdbsqlite наследуемые. В боте подключается ghostdbmysql или ghostdbsqlite в зависимости какая была выбрана компиляция. Если компиляция была с БД MySQL (в файле проекта ghost указан параметр GHOST_MYSQL) - то есть возможность работать с MySQL, если в виде БД в конфиге была указана mysql.
    Код:
    	DBType = CFG->GetString( "db_type", "sqlite3" );
    	CONSOLE_Print( "[GHOST] opening primary database" );
    	if( DBType == "mysql" )
    	{
    #ifdef GHOST_MYSQL
    		m_DB = new CGHostDBMySQL( CFG );
    #else
    		CONSOLE_Print( "[GHOST] warning - this binary was not compiled with MySQL database support, using SQLite database instead" );
    		m_DB = new CGHostDBSQLite( CFG );
    #endif
    	}
    	else
    		m_DB = new CGHostDBSQLite( CFG );
    А если оптимизировать - нужно переписывать запросы, переделывать методы хранения данных в самой БД. Чтоб не было запросов в ghostdbmysql.cpp по типу таких:
    Код:
    Query = "select totgames,wins,losses,killstotal,deathstotal,creepkillstotal,creepdeniestotal,assiststotal,neutralkillstotal,towerkillstotal,raxkillstotal,courierkillstotal,kills,deaths,creepkills,creepdenies,assists,neutralkills,towerkills,raxkills,courierkills, server from(select *, (kills/deaths) as killdeathratio, (totgames-wins) as losses from (select gp.name as name,ga.server as server,gp.gameid as gameid, gp.colour as colour, avg(dp.courierkills) as courierkills, sum(dp.raxkills) as raxkillstotal, sum(dp.towerkills) as towerkillstotal, sum(dp.assists) as assiststotal,sum(dp.courierkills) as courierkillstotal, sum(dp.creepdenies) as creepdeniestotal, sum(dp.creepkills) as creepkillstotal,sum(dp.neutralkills) as neutralkillstotal, sum(dp.deaths) as deathstotal, sum(dp.kills) as killstotal,avg(dp.raxkills) as raxkills,avg(dp.towerkills) as towerkills, avg(dp.assists) as assists, avg(dp.creepdenies) as creepdenies, avg(dp.creepkills) as creepkills,avg(dp.neutralkills) as neutralkills, avg(dp.deaths) as deaths, avg(dp.kills) as kills,count(*) as totgames, SUM(case when((dg.winner = 1 and dp.newcolour < 6) or (dg.winner = 2 and dp.newcolour > 6)) then 1 else 0 end) as wins from gameplayers as gp, dotagames as dg, games as ga,dotaplayers as dp where dg.winner <> 0 and dp.gameid = gp.gameid and dg.gameid = dp.gameid and dp.gameid = ga.id and gp.gameid = dg.gameid and gp.colour = dp.colour and gp.name='"+name+"' and ga.gamestate='"+ gamestate +"' group by gp.name) as h) as i";
    Именно с этого начинается оптимизация.
    А по поводу запросов - они находятся как глобальные ф-ии в ghostdbmysql.cpp.
    Вот например задай поиск в файле по
    Код:
    MySQLDotAPlayerSummaryCheck
    Найдешь 2 значения. Первое используется в CMySQLCallableDotAPlayerSummaryCheck а 2 это именно то где обрабатывается запрос. Либо наоборот первое. В общем не важно. Все запросы в БД находятся в ghostdbmysql.cpp
  3. Mefix Наш человек

    Сообщения:
    114
    Спасибы:
    3
    Ну собственно, понял.

    Как раз и планировал с оптимизации таких длинных запросов. То есть создав новые поля данных в таблице Scores(таких как средние значения убийств, смертей, ракскилс и т.д), чтобы они сразу из БД брались.
    Вопрос в том, как правильно реализовать адекватный просчёт, чтобы сперва в БД записывался нужный +1 к gamecount, нужный + к убийствам, + к смертям, другим значениям, а потом уже просчитывалось киллс/gamecount=srkills, и прочие. Или добавлялось бы сразу в бд, kills(из тек игры)/(gamecount+1) и идёт + в таблицу средних убийств. Ну или kills(из тек игры)/gamecount. Вопрос в том, в какой очерёдности данные gamecount kills и других полей заносятся в БД, чтобы можно было высчитать правильно средние значения. Хотя мне кажется, что легче от них вообще отказаться.

    У меня конечно не рейд, но я не думаю что mysql должна грузить при innoDB хард на 100%(Sata3, 7200 rpm, 150 mb/s чтение с диска), и отвечать на запросы фигову тучу времени. Так же при MyISam, только проц(3-х ядерный) на 100%, хард юзается по минимуму.
  4. Deals Старожила

    Сообщения:
    784
    Спасибы:
    21
    Сборка бота GHost:
    Ghost One 1.7.266
    Skype:
    Мой статус
    Не важно как ты будешь оптимизировать. Важна сама оптимизация! Убрать эти трехэтажные запросы.
    Любым путем.
  5. rost Гуру

    Сообщения:
    1.258
    Спасибы:
    150
    Сборка бота GHost:
    LtG
    Дата начала использования бота:
    01.11.2010
    Skype:
    Мой статус
    я вырезал функцию подсчета ботом.
  6. ____ROB____ Старожила

    Сообщения:
    1.008
    Спасибы:
    22
    Сборка бота GHost:
    Ghost One 1.5
    тобишь у тебя топ щитатает как гейм.php ? o_O
  7. rost Гуру

    Сообщения:
    1.258
    Спасибы:
    150
    Сборка бота GHost:
    LtG
    Дата начала использования бота:
    01.11.2010
    Skype:
    Мой статус
    Cлегка странный вопрос)
    но по сути бот нече несчитает, да это делает типа гаме.пхп (типа)
  8. ____ROB____ Старожила

    Сообщения:
    1.008
    Спасибы:
    22
    Сборка бота GHost:
    Ghost One 1.5
    scores пустая?
  9. Mefix Наш человек

    Сообщения:
    114
    Спасибы:
    3
    Народ. Такой вопрос, а бот что во время игры, когда кого-нибудь убивают, ОЧКИ ПЕРЕСЧИТЫВАЕТ? o_O
  10. rost Гуру

    Сообщения:
    1.258
    Спасибы:
    150
    Сборка бота GHost:
    LtG
    Дата начала использования бота:
    01.11.2010
    Skype:
    Мой статус
    Нет, вроде бы после каждого сохранения данных о игре (ну когда игра заканчивается)
    Нет, заполненая.
  11. Mefix Наш человек

    Сообщения:
    114
    Спасибы:
    3
    Rost, Тоесть какие таблицы расчётов почистил? и как Score полная? Или она через что-то другое просчитывается?
  12. rost Гуру

    Сообщения:
    1.258
    Спасибы:
    150
    Сборка бота GHost:
    LtG
    Дата начала использования бота:
    01.11.2010
    Skype:
    Мой статус
    Непонял о чем ты o_O
    Нечего из таблиц нечистил...
    Легко, считается и заполняетсо :alequate:
    есен пень да :bIbIbIblya:
  13. Synth (LM) Пантограф!

    Сообщения:
    2.762
    Спасибы:
    172
    Сборка бота GHost:
    My own version of GhostOne
    Дата начала использования бота:
    15.06.2008
    Skype:
    Мой статус
    У него база рассыпится, если одна из игр потеряется, а он захочет сделать перерасчет.
  14. rost Гуру

    Сообщения:
    1.258
    Спасибы:
    150
    Сборка бота GHost:
    LtG
    Дата начала использования бота:
    01.11.2010
    Skype:
    Мой статус
    Ты бы небазарил а!?
    Незнаешь как оно считает так молчал бы не?
    Я перещет делать незахочу т.к. щас считает так как мне нужно, с балансом команд и т.д...
    Нече сыпатсо небудет. Хотя нах я тебе это вообще объясняю :facepalm:
    ----
    а вообще для перещета, есть тоже спец скрипт... который так же считает только в ускоренном варианте.
  15. Synth (LM) Пантограф!

    Сообщения:
    2.762
    Спасибы:
    172
    Сборка бота GHost:
    My own version of GhostOne
    Дата начала использования бота:
    15.06.2008
    Skype:
    Мой статус
    Всё я знаю, всё.
    Если ты так подумал, зачем ты мне ответил тогда? Хочешь показать что ты думаешь как старая маразматичка - вслух сказала мысли и потом подумала. Да, я знаю, есть в тебе такой комплекс.

    Ах да, ты забыл писать на РУССКОМ языке, тп.
  16. Mefix Наш человек

    Сообщения:
    114
    Спасибы:
    3
    Ну почему рассыпется. Если сделается полный перерасчёт, то просто той игры не будет ))
    Но не будем предполагать )))

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

    PS: Т.к. Я знаю что никто готовых решений не подскажет, если кто пытался. То придётся самому. Вопрос мой в основном в том, в правильное ли русло мои мысли по оптимизации идут, будет ли иметь успех оптимизации, таким каким я его описал. Причём при всём при это, статистика каждой игры по отдельности тоже будет вестись.
  17. rost Гуру

    Сообщения:
    1.258
    Спасибы:
    150
    Сборка бота GHost:
    LtG
    Дата начала использования бота:
    01.11.2010
    Skype:
    Мой статус
    Ахахах, варн за пункт 10 ?? несмешно?? где там синтаксические ошибки выше СРЕДНЕГО?
    Нет, ты неверно понял, сначала хотел объяснить... потом понял что н***я ты мне нужен и н***я мне тебе что-то пояснять (ток варн за мат недавай ок да? то со стула упаду :lol: :lol: :lol: )

    Выделил слова... зачем? Ты знаешь тока как нам мстить за твои вымышленные обиды, вот и все.
    Ты бы не строил из себя крутого модера, карающего за все попало, ну или карал бы всех имхо рак.
  18. Mefix Наш человек

    Сообщения:
    114
    Спасибы:
    3
    Ну я буду начинать делать с конца, сперва хочу на sql запросах все нужные запросы сделать, а потом уже в бота их пихать. Ну примерно что-то такое хочу сделать в таблице Score, Тоесть там ещё убийство курьеров будет, крипов и прочего, всего в играх.
    http://clip2net.com/clip/m0/1323212024-clip-18kb.png

    Собственно потом буду писать чтобы бот при завершении игры score считал по этим полям. А не по выборке всей базы, ну и соответственно, потом sql запрос, который всю базу перелопатит и заполнит для каждого игрока эти значения.
  19. ____ROB____ Старожила

    Сообщения:
    1.008
    Спасибы:
    22
    Сборка бота GHost:
    Ghost One 1.5
    ты хочешь что бы инфа заносилась вся (килы смерти тд) в таблицу очки ? а на что ето повлияет, ну всмысле для чего?
    для вывода страницы топ.пхп ? или ты хочешь облегчить сам перещет очков, тем самым убрать все остальные таблицы, оставив только таблцу скоре ?
  20. ViperNight ▒▒▒▒▒▒▒▒▒

    Сообщения:
    311
    Спасибы:
    154
    Сборка бота GHost:
    GHost++ r597M
    Дата начала использования бота:
    20.01.2007
    Skype:
    Мой статус

    Я у себя сделал следующим образом:

    Немного изменил структуру базы, добавил поля имени игрока и результат об исходе игры (нарушив НФ :) ), в некоторые дефолтные таблицы, там, где это было необходимо, чтобы убрать излишние JOIN’ы и избавиться от вложенности запросов.

    Изменил метод занесения результатов в базу ботом после окончания игры, сделал именно так, как собственно и написал Mefix.
    Вместо обычного INSERT’а в MySQLDotAPlayerAdd происходит вызов процедуры, которая, в свою очередь, выполняет тот самый INSERT, который тут был по умолчанию, собственно заполнял вот такую таблицу:
    Код:
    CREATE TABLE IF NOT EXISTS `dotaplayers` (
      `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
      `botid` tinyint(2) unsigned NOT NULL DEFAULT '0',
      `gameid` mediumint(8) unsigned NOT NULL DEFAULT '0',
      `outcome` enum('0','1','2') NOT NULL DEFAULT '0',
      `color` tinyint(2) unsigned NOT NULL DEFAULT '0',
      `newcolor` tinyint(2) unsigned NOT NULL DEFAULT '0',
      `name` varchar(15) NOT NULL DEFAULT '',
      `hero` char(4) NOT NULL DEFAULT '',
      `herolevel` tinyint(2) unsigned NOT NULL DEFAULT '0',
      `kills` smallint(4) unsigned NOT NULL DEFAULT '0',
      `deaths` smallint(4) unsigned NOT NULL DEFAULT '0',
      `assists` smallint(4) unsigned NOT NULL DEFAULT '0',
      `creepkills` smallint(4) unsigned NOT NULL DEFAULT '0',
      `creepdenies` smallint(4) unsigned NOT NULL DEFAULT '0',
      `neutralkills` smallint(4) unsigned NOT NULL DEFAULT '0',
      `courierkills` smallint(4) unsigned NOT NULL DEFAULT '0',
      `towerkills` tinyint(2) unsigned NOT NULL DEFAULT '0',
      `raxkills` tinyint(2) unsigned NOT NULL DEFAULT '0',
      `gold` mediumint(8) unsigned NOT NULL DEFAULT '0',
      `apm` double(6,3) unsigned NOT NULL DEFAULT '0.000',
      `item1` char(4) NOT NULL DEFAULT '',
      `item2` char(4) NOT NULL DEFAULT '',
      `item3` char(4) NOT NULL DEFAULT '',
      `item4` char(4) NOT NULL DEFAULT '',
      `item5` char(4) NOT NULL DEFAULT '',
      `item6` char(4) NOT NULL DEFAULT '',
      PRIMARY KEY (`id`),
      KEY `gameid` (`gameid`,`color`),
      KEY `newcolor` (`newcolor`),
      KEY `name` (`name`),
      KEY `hero` (`hero`),
      KEY `item1` (`item1`),
      KEY `item2` (`item2`),
      KEY `item3` (`item3`),
      KEY `item4` (`item4`),
      KEY `item5` (`item5`),
      KEY `item6` (`item6`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;
    плюс к этому, для каждого игрока, закончившего игру, происходит выборка общих результатов по всем предыдущим играм (ака totals) из таблицы totalstats, и их суммирование с результатами, полученными по окончанию данной игры, идёт расчёт и занесение очков, если это необходимо, и т.п.:

    Код:
    CREATE TABLE IF NOT EXISTS `totalstats` (
      `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
      `name` varchar(15) NOT NULL DEFAULT '',
      `score` double(6,3) unsigned NOT NULL DEFAULT '0.000',
      `total_wins` mediumint(8) unsigned NOT NULL DEFAULT '0',
      `total_losses` mediumint(8) unsigned NOT NULL DEFAULT '0',
      `total_draws` mediumint(8) unsigned NOT NULL DEFAULT '0',
      `total_kills` mediumint(8) unsigned NOT NULL DEFAULT '0',
      `total_deaths` mediumint(8) unsigned NOT NULL DEFAULT '0',
      `total_assists` mediumint(8) unsigned NOT NULL DEFAULT '0',
      `total_creeps` mediumint(8) unsigned NOT NULL DEFAULT '0',
      `total_denies` mediumint(8) unsigned NOT NULL DEFAULT '0',
      `total_neutrals` mediumint(8) unsigned NOT NULL DEFAULT '0',
      `total_couriers` mediumint(8) unsigned NOT NULL DEFAULT '0',
      `total_towers` mediumint(8) unsigned NOT NULL DEFAULT '0',
      `total_raxs` mediumint(8) unsigned NOT NULL DEFAULT '0',
      `total_aegis` mediumint(8) unsigned NOT NULL DEFAULT '0',
      `avg_apm` double(6,3) unsigned NOT NULL DEFAULT '0.000',
      KEY `name` (`name`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;

    При таком раскладе, статистика по !statsdota вылетает в одно мгновение.

    Вот собственно и всё.
    I_aM_Fake нравится это.
Статус темы:
Закрыта.