[Type-R]

Hello guys, sometimes my server lags up and i looked it up, that sometimes the query execution is slighty long. At that time everyone shows AFK and chat lags up. I have a global timer: zaidimas1, that goes globaly every minute and stores players data after each minute, for how long they played. This long query execution, happens only sometimes.. i have a feeling that maybe i set it up wrong, where there are to many querys sent? Maybe i should create a timer for each player separately, when they spawn and execute querys at those players intervals? Well heres the mysql log:

Код:

[17:12:08] [DEBUG] mysql_format - connection: 1, len: 200, format: "UPDATE vartotojai SET Zaidziu='%d', XP='%d', Siandien='%d', GangXP='%d' WHERE Vardas = '%s'"
[17:12:08] [DEBUG] mysql_tquery - connection: 1, query: "UPDATE vartotojai SET Zaidziu='7700', XP='74678', Siandien='108'", callback: "(null)", format: "(null)"
[17:12:08] [DEBUG] mysql_format - connection: 1, len: 200, format: "UPDATE vartotojai SET Zaidziu='%d', XP='%d', Siandien='%d', GangXP='%d' WHERE Vardas = '%s'"
[17:12:08] [DEBUG] mysql_tquery - connection: 1, query: "UPDATE vartotojai SET Zaidziu='7767', XP='47802', Siandien='20',", callback: "(null)", format: "(null)"
[17:12:08] [DEBUG] mysql_format - connection: 1, len: 200, format: "UPDATE vartotojai SET Zaidziu='%d', XP='%d', Siandien='%d', GangXP='%d' WHERE Vardas = '%s'"
[17:12:08] [DEBUG] mysql_tquery - connection: 1, query: "UPDATE vartotojai SET Zaidziu='2667', XP='21202', Siandien='16',", callback: "(null)", format: "(null)"
[17:12:08] [DEBUG] mysql_format - connection: 1, len: 200, format: "UPDATE vartotojai SET Zaidziu='%d', XP='%d', Siandien='%d', GangXP='%d' WHERE Vardas = '%s'"
[17:12:08] [DEBUG] mysql_tquery - connection: 1, query: "UPDATE vartotojai SET Zaidziu='10566', XP='10213', Siandien='25'", callback: "(null)", format: "(null)"
[17:12:08] [DEBUG] mysql_format - connection: 1, len: 200, format: "UPDATE vartotojai SET Zaidziu='%d', XP='%d', Siandien='%d', GangXP='%d' WHERE Vardas = '%s'"
[17:12:08] [DEBUG] mysql_tquery - connection: 1, query: "UPDATE vartotojai SET Zaidziu='12', XP='72', Siandien='12', Gang", callback: "(null)", format: "(null)"
[17:12:08] [DEBUG] CMySQLQuery::Execute[] - starting query execution
[17:12:35] [DEBUG] CMySQLQuery::Execute[] - query was successfully executed within 26489.123 milliseconds
[17:12:35] [DEBUG] CMySQLQuery::Execute[] - no callback specified, skipping result saving
[17:12:35] [DEBUG] CMySQLQuery::Execute[] - starting query execution
[17:12:35] [DEBUG] CMySQLQuery::Execute[] - query was successfully executed within 21.936 milliseconds
[17:12:35] [DEBUG] CMySQLQuery::Execute[] - no callback specified, skipping result saving
[17:12:35] [DEBUG] CMySQLQuery::Execute[] - starting query execution
[17:12:35] [DEBUG] CMySQLQuery::Execute[] - query was successfully executed within 18.227 milliseconds
[17:12:35] [DEBUG] CMySQLQuery::Execute[] - no callback specified, skipping result saving
[17:12:35] [DEBUG] CMySQLQuery::Execute[] - starting query execution
[17:12:35] [DEBUG] CMySQLQuery::Execute[] - query was successfully executed within 19.441 milliseconds
[17:12:35] [DEBUG] CMySQLQuery::Execute[] - no callback specified, skipping result saving
[17:12:35] [DEBUG] CMySQLQuery::Execute[] - starting query execution
[17:12:35] [DEBUG] CMySQLQuery::Execute[] - query was successfully executed within 18.222 milliseconds
[17:12:35] [DEBUG] CMySQLQuery::Execute[] - no callback specified, skipping result saving

here is how i execute the querys every minute:

Код:

for(new i = 0; i < MAX_PLAYERS; i++)
	{
 		if(IsPlayerNPC(i) || !IsPlayerConnected(i) || ZaidejasZaidzia[i] == 0)
		{
			continue;
		}
		else
		{
  			PRAZAIDZIAU[i] ++;
			SIANDIEN[i] ++;
			if(GetPVarInt(i, "Admin_lygis") >= 1)
			{
				SetPlayerScore(i,GetPlayerScore(i)+12); // Xp ++
			}
			else
			{
   				SetPlayerScore(i,GetPlayerScore(i)+6); // Xp ++
			}
			if( specialybe[i] == BALLAS || specialybe[i] == GROVESTREET || specialybe[i] == VAGOS || specialybe[i] == AZTECAS )
   			{
	    		GAUJOJEXP[i] += 6;
		    }
		 	GetPlayerName(i, pname, 24);
			new query[200];
			mysql_format(mysql, query, sizeof(query ),"UPDATE vartotojai SET Zaidziu='%d', XP='%d', Siandien='%d', GangXP='%d' WHERE Vardas = '%s'", PRAZAIDZIAU[i],GetPlayerScore(i),SIANDIEN[i],GAUJOJEXP[i],pname);
			mysql_tquery(mysql, query);
		}

What do you guys think?

[AndySedeyn]

The loop is ineffective and you've probably never heard of foreign keys or making relations between tables (database normalization). You can also work out another way of updating the player's score; example: when they disconnect instead of updating it on a minute-basis.

There is a three-part tutorial in my signature about database normalization and its implementation in PAWN. The tutorial(s) is(/are) made by Vince.

[Type-R]

Okay, i will read the toturials. The thing why i am doing it every minute, is because i want players to be able to access current TOP, on todays time played on server. I dont see another way of doing it

[Vince]

I seem to remember you having 13,000 rows. You should know that searching through a table using a string as a key is very, very slow in comparison to a surrogate integer id key. Especially if there is no index on that string field as in that case a full table scan is required which is much slower, still.

[Type-R]

I cleared the database, now it is down to 2,6K rows. By that do you mean, i should use the account ID number of players to look them up, and not their name? And this lag it seems to happen randomly now..