[11284]Prepared statements for MySQL [part 1]

[Ambal]

Всем трямс

Вобщем, как и обещалось некоторое время назад, поддержка для prepared statements в мангосе таки появится

Для тех, кто незнает, что сие такое и зачем оно вообще надо, могут погуглить - многа букаф писать на выходных я не осилю ))

На данном этапе реализована поддержка для INSERT+DELETE+UPDATE запросов. SELECTы будут реализованы позднее, дабы не коммитить патчи монструозного размера.

Важный момент: нативные prepared statements реализованы только для MySQL. Пользователи PostgreSQL будут пользоваться эмуляцией этой функциональности, которая будет сводится к отправке обычных строковых запросов к БД.

На данный момент переписаны все запросы, которые используются во время сохранения игрока, e.g. вызова функции Player::SaveToDB().

WARNING: СДЕЛАЙТЕ БЕКАП БАЗЫ ДО НАЧАЛА ТЕСТОВ!!!

Ссылка на тему на офф форуме: http://getmangos.com/community/post/132464/

репозиторий: prepared_statements

update #1: добавлена поддержка PostgreSQL. Финальная версия патча.
update #2: ODBC-похожий АПИ для передачи параметров процедурам.
update #3: правка ошибок компиляции под *nix
update #4: правка синтаксиса некоторых запросов.
update #5: переписан код всвязи с найденными критическими нюансами инициализации статических переменных функций в многопоточной среде. Спасибо Vinolentus. Ожидайте незначительного увеличения потребления CPU из-за постоянного поиска в реесте нужных хранимых процедур по строке запроса.
update #6: переписан код, теперь мы корректно используем статические локальные переменные для хранения информации по процедурам. Должно вернуть потребление ЦП к норме.

[RomanRom2]

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

могут погуглить - многа букаф

погуглил. а, вот как это называется

цитирую:

Из таких результатов я делаю следующий вывод: использование повсеместно запросов с prepared statements может замедлить работу с базой данных в полтора раза. Реальная выгода происходить только лишь при запуске подряд одинаковых запросов с разными параметрами. Также я вижу, что mysqlnd не совсем оправдывает ожидания. Как говорят разработчики, mysqlnd пришел заменить libmysql в связи с тем, что libmysql оптимизирована для работы с приложениями, написанными на С, а не PHP. И настоятельно предлагают использовать mysqlnd, потому что он оптимизирован для работы из PHP и по сравнению с libmysql имеет множество усовершенствований по работе с памятью и скоростью работы. Но, как мы можем наблюдать, mysqlnd все время проигрывает libmysql, хотя стоит заметить, что я не измерял используемую память и ресурс процессора. Это что касается производительности.

Что же с нашей безопасностью? Что лучше использовать prepared statements или real_escape_string? Многие говорят что real_escape_string это костыль, он что-то там недофильтровывает и всё такое. Я считаю, что он такой же костыль как и prepared statements , который не может подставлять переменные абсолютно во все места SQL запроса. Обычно логика вокруг real_escape_string такая же костыльная как и вокруг prepared statements , причём вокруг последних бывает побольше всякой хрени типа call_user_func_array. В связи с эти, я делаю следующий вывод: что безопасность, обеспечиваемая при использовании расширения mysqli, одинаково удобна, костыльна и "безопасна" как при real_escape_string так и при prepared statements . Другое дело использование prepared statements в PDO, где они поддерживаются на клиентской части и эмулируются, если сервер базы данных не поддерживает prepared statements . Слой абстракции PDO явно оправдывает подстановку параметров в запросы к разным СУБД.

http://itdumka.com.ua/index.php?cmd=shownode&node=17

или если не открывается:
http://hghltd.yandex.net/yandbtm?fmo...83d9b8&keyno=0

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

procedure DB_GetAccountInfo(var A: TAccount);
var
  DBResult: TMySQLResult;
  i: longint;
begin
  if not DataBase.Query('SELECT id, passw, access, UNIX_TIMESTAMP(game_prepaid), game_type, game_level FROM accounts WHERE login="%s"', [A.SecurityInfo.Login], QUERY_RESULT_STORE, DBResult) then
    begin
      MainLog('DB: ' + DataBase.GetLastError);
      Exit;
    end;

  if DBResult.Fetch then
    begin
      i:= 0;  A.AccountInfo.ID:=             StrToIntDef(DBResult.Field[i], 0);
      inc(i); A.SecurityInfo.Passw:=         DBResult.Field[i];
      inc(i); A.AccountInfo.Access:=         StrToIntDef(DBResult.Field[i], 0);
      inc(i); A.AccountInfo.PrepaidTime:=    StrToIntDef(DBResult.Field[i], 0);
      inc(i); A.AccountInfo.GameType:=       StrToIntDef(DBResult.Field[i], 0);
      inc(i); A.AccountInfo.GameLevel:=      StrToIntDef(DBResult.Field[i], 0);
    end;

  DBResult.Free;
end;
procedure DB_StoreSessionKey(var A: TAccount);
var
  i: longint;
  s: string;
begin
  for i:= 0 to 39 do 
    s:= s + IntToHex(A.SecurityInfo.Session[i], 2);

  if not DataBase.Query('UPDATE accounts SET session_key="%s", session_time=NOW() WHERE login="%s"', [s, A.SecurityInfo.Login]) then
    begin
      MainLog('DB: ' + strr(DataBase.GetLastErrNo)+ ', ' +DataBase.GetLastError);
      Exit;
    end;
end;

[zhenya]

грац Ж)

[Ambal]

RomanRom2, опыт использования prepared statements в РНР к мангосу никакого отношения не имеет Мы повторно используем наши подготовленные процедуры, так что выигрыш будет в любом случае:
http://euedge.com/blog/2007/11/11/pr...ance-in-mysql/
http://rubayeet.wordpress.com/2008/1...hp-experiment/

P.S. RomanRom2, в статье, что вы привели, деятель каждый раз в функции function workWithPS() подготавливает процедуры при каждом ее вызове - не удивительно, что у него все тормозит ))

function workWithPS()
{
$stmt = $db->prepare('Select ID from USERS where LOGIN = ? and EMAIL = ?');
....
$stmt->close();
}

[RomanRom2]

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

уверен, что хуже точно не станет. а что касается статьи, это первое что у меня открылось по теме и сразу вот так, в точку можно сказать

[Ambal]

Добавил поддержку PostgreSQL - prepared statements будет эмулироваться отправкой обычных строковых запросов. На большее любители этой DBMS от меня пусть не расчитывают

[rsa]

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

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

уверен, что хуже точно не станет. а что касается статьи, это первое что у меня открылось по теме и сразу вот так, в точку можно сказать

ну в общем PS однозначно не будут хуже чем строковые запросы. сильного выигрыша от них вряд ли можно дождаться (на сильнонагруженном сервере будет максимум экономия 2-3% LA и 5-10% канала сервер-база IMHO), но зато можно пооптимизировать код. главное не увлечься и не прибить старые методы, как это часто бывает...

[Ambal]

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

[ghostpast]

            for (int k = 0; k < MAX_EFFECT_INDEX; ++k)
                insertAuras.addInt32(damage[k]);
            for (int k = 0; k < MAX_EFFECT_INDEX; ++k)
                insertAuras.addInt32(maxduration[k]);
            for (int k = 0; k < MAX_EFFECT_INDEX; ++k)
                insertAuras.addInt32(remaintime[k]);

можно заменить на

            for (int k = 0; k < MAX_EFFECT_INDEX; ++k)
            {
                insertAuras.addInt32(damage[k]);
                insertAuras.addInt32(maxduration[k]);
                insertAuras.addInt32(remaintime[k]);
            }

поменяв порядок заполнения в запросе

[Ambal]

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

Тестеры с офф сайта говорят, что с последней версией патча при 1к онлайна с mtmaps проблем не замечено, так что можете пробовать.

[Vinolentus]

Ambal, а что произойдет, если один и тот же статик стэйтмент в каком-то одном потоке уже запрошен, но еще не создан в Database::CreateStatement, и в этот момент к нему подходит другой поток?

[Ambal]

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

Это было сделано ради того, чтобы:
1) закешировать информацию по процедуре, а не возиться с поиском по реестру;
2) иметь возможность создавать объекты SqlStatement в любом месте кода, не не возиться напрямую с айдишниками и различными дефайнами как это сделано, например, в TrinityCore.

P.S. Я лично никого не воодушевляю на подобную практику со статическими переменными в функциях - слишком много подводных камней при инициализации.

[Vinolentus]

Мы со статическими переменными рискуем только несколько раз вызвать конструктор/код инициализации для нашего объекта из разных потоков

Я у себя вижу другую картинку (вин, VC2010). Когда в первом потоке вошли, но еще не вышли из CreateStatement, во втором static-переменной _уже_ не присваивается значение.

[Ambal]

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

Я у себя вижу другую картинку (вин, VC2010). Когда в первом потоке вошли, но еще не вышли из CreateStatement, во втором static-переменной _уже_ не присваивается значение.

Отсюда попрошу поподробнее. Т.е. второй поток считает, что объект уже создан, хотя это не так? Идея в том, чтобы код получал уже созданный объект, т.к. если он попробует обратиться к только конструируемому объекту - у нас будут большие неприятности. Нас устроит либо повторный вызов кода инициализации, либо уже готовый объект. Хотите сказать, что VC2010 нам не гарантирует?

UPDATE: провел собственное расследование: незнаю, какой жопой думали в Майкрософте разработчики компилятора, но таки компилятор отлавливает только первое обращение, а не момент инициализации. Если вы запустите программу http://paste2.org/p/1298414, то получите результат:

Obtained value = 0
I'm the object constructor
Obtained value = 1

Т.е. в первом потоке мы только инициализируем переменную, а второй уже считает, что объект сконструирован О.о

Так что чую буду переписывать всю эту писанину

P.S. Моя в полнейшем ах...е

[Vinolentus]

какой жопой думали в Майкрософте разработчики компилятора

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

[Ambal]

Как бы там нибыло, патч я переделал с учетом "особенностей национальной инициализации статических переменных функций в С++". Пока будет использоваться поиск по реестру при создании объектов SqlStatement. Чуть позже подумаю, как корректно сделать нормальный вызов процедур по их айди.

[Йоха]

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

И какой смысл вкладывался в выделенную строчку ? Хотели что бы переменная _obj существовала в памяти в единственном экземпляре ?

static DWORD WINAPI ThreadFunc(LPVOID param)
{
	static Object _obj(Object::init());
	_obj.Print();
	return 0;
}

[Ambal]

Сообщение от Йоха

вообще такое программирование многопоточных классов без использования объектов синхронизации чревато ...

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

Есть другое предложение по поводу использования статических локальных переменных.

SqlStatement Database::CreateStatement(SqlStatementID& index, const char * fmt)
{
       if(!index.initialized())
           index = AllocIndex(fmt);

       return SqlStatement(index, *this);
}

...

static SqlStatementID index;

SqlStatement request = CharacterDatabase.CreateStatement(index, "SELECT * FROM table");

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

Мысли, возражения?

[Йоха]

что-то тут не то с дизайном приложения ...

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

если я правильно понял идею то по моему это должно выглядеть примерно так:

class Database;

class SqlStatementID
{
public:
	SqlStatementID(){};
	~SqlStatementID(){};
};

class SqlStatement
{
public:
	SqlStatement(){};
	SqlStatement(SqlStatementID& index, Database *db){};
	~SqlStatement(){};
};

class Database
{
public:
	Database(void){};
	~Database(void){};
	SqlStatement CreateStatement(const char *fmt)
	{
		return SqlStatement(m_index, this);
	};
	static SqlStatementID m_index;
};

SqlStatementID AllocIndex()
{
	return SqlStatementID();
}
// инициализация статической переменной
SqlStatementID Database::m_index = AllocIndex();

int _tmain(int argc, _TCHAR* argv[])
{
	Database CharacterDatabase;
	SqlStatement request = CharacterDatabase.CreateStatement("SELECT * FROM table");

	return 0;
}

[Ambal]

Сообщение от Йоха

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

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

[Йоха]

тут или я что-то не понимаю ... или в логике ошибка

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

В общем для меня слишком много непонятного, опишите ситуацию целиком если не трудно

P.S. к тому же какой смысл объявлять глобальную переменную как статик ? глобальные переменные и так существуют в единственном экземпляре

[Ambal]

Йоха, мы инициализируем локальную статическую переменную функции, а не член класса

void Function()
{
static SqlStatementID index;

SqlStatement request = CharacterDatabase.CreateStatement(index, "INSERT INTO table");

request.Execute();
}

Кроме того, нам надо закешировать исключительно айди запроса во внутреннем реестре и количество входящих параметров для prepared statements. Капитан Очевидность уверен, что эти данные будут одинаковы для заданного запроса

Поэтому не вижу причин для паники.

[Йоха]

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

Йоха, мы инициализируем локальную статическую переменную функции, а не член класса

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

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

[Ambal]

Йоха, менеджер уже сейчас есть и используется. Только вот постоянный поиск по хеш-таблице ради нужных даных по запросу - занятие довольно таки затратное, отчасти виной тому наши странные хеширующие функции для строк. При текущих 90 запросах мы имеем увеличение загрузки ЦП на 6-10%. А таких запросов при поддержке SELECTов будет порядка 150-200.

[Йоха]

ну при таком подходе других вариантов нет, к тому же возможно затраты на поиск нужного prepared statement для строки запроса сведет на нет выигрыш от использования самих prepared statements. Тут придется только искать способ максимально быстро находить PS соответствующий sql выражению

[Ambal]

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

[Vinolentus]

DirectExecute() тысячи запросов с текстом, который нужно escape():

2854 ms

И prepared statement:

671 ms

[Ambal]

Я планирую закоммитить патч в течение ближайшей недели в случае если не будут найдены проблемы в коде. На серверах с 1к+ онлайна ошибок на данный момент не обнаружено, поэтому можете начинать тестировать добровольно пока у вас есть время

[Ambal]

Всем, кому интересна статистика с сервера MySQL, что обслуживает мангос с prepared statements, могут взглянуть на скриншот:


Доля PS теперь занимает 42% на сервере (поправьте если неправильно интерпретирую результаты). И это благодаря только 90 запросам из почти 900 в чистом ядре Цифра может отличаться от чистого мангоса, т.к. у тестировщика очень много сторонних патчей.

fgenesis готовит патч, который позволит собрать статистику по каждому запросу к БД чтобы мы могли перевести на PS только критические для быстродействия запросы.