[NikolaSRB]

Hello everyone,

I have problem with MySQL. It is very slow while processing queries and while it is processing them it blocks SAMP thread and server seems to be lagging.

In purpose of testing MySQL performance, I made a command SaveAccount which on calling /test I will invoke 500 times to simulate 500 saving accounts. So that is 500 update queries and it takes 15 - 20sec on my PC (i7-4790k, 16gb ram).

What Am I doing wrong? If I don't fix this, server will be always lagging because it can't handle too many queries in second, and imagine 500 players online, and they buy car, gun etc you need to update their status in database.

Please help me how to make MySQL run in different thread which will not make SAMP thread lag.

I am using MySQL plugin R39-6.

Here is the code:

Код:

CMD:test(playerid, params[])
{
	SCMF(playerid, BLUE, "START  TO WRITE WITH - SaveACC4 - 500 times");
	for(new i = 0; i < 500; i++)
	{
		SaveAccount(playerid);
	}
	SCMF(playerid, BLUE, "DONE WITH UPDATING");

	return 1;
}

Код:

forward SaveAccount(playerid);
public SaveAccount(playerid)
{
    if(PlayerInfo[playerid][Level] == 0) return 1;
	new Pname[24], escpname[24];
	GetPlayerName(playerid, Pname, 24);
	mysql_real_escape_string(Pname, escpname);
	new Query[1000];
	format(Query,sizeof(Query),"UPDATE korisnici SET Novac = '%d', Banka = '%d', Pizza = '%d', Hotdog = '%d', Cigare = '%d'",
	PlayerInfo[playerid][Novac],PlayerInfo[playerid][Banka],PlayerInfo[playerid][Pizza],PlayerInfo[playerid][Hotdog],PlayerInfo[playerid][Cigare]);

	format(Query, sizeof(Query), "%s, Utisan = '%d', ZatvorenVreme = '%d', MehanicarOdvoz = '%d', MobKredit = '%d', Sladoled = '%d'",
	Query,PlayerInfo[playerid][Utisan],PlayerInfo[playerid][ZatvorenVreme],PlayerInfo[playerid][MehanicarOdvoz],PlayerInfo[playerid][MobKredit],PlayerInfo[playerid][Sladoled]);

    format(Query, sizeof(Query), "%s, Kazne = '%d', Materijali = '%d', GunMatsi = '%d', Droga = '%d', Zavisnost = '%d'",
	Query,PlayerInfo[playerid][Kazne],PlayerInfo[playerid][Materijali],PlayerInfo[playerid][GunMatsi],Dzep[playerid][Droga],PlayerInfo[playerid][Zavisnost]);

	format(Query, sizeof(Query), "%s, GunProdano = '%d', Level = '%d', Iskustvo = '%d', Meta = '%d', CijenaGlave = '%d'",
	Query,PlayerInfo[playerid][GunProdano],PlayerInfo[playerid][Level],PlayerInfo[playerid][Iskustvo],PlayerInfo[playerid][Meta],PlayerInfo[playerid][CijenaGlave]);

	format(Query, sizeof(Query), "%s, PayDayPoeni = '%d', Minonl = '%d', Sationl ='%d', PosaoUgovor = '%d', TimUgovor = '%d'",
	Query,PlayerInfo[playerid][PayDayPoeni],PlayerInfo[playerid][Minonl],PlayerInfo[playerid][Sationl],PlayerInfo[playerid][PosaoUgovor],PlayerInfo[playerid][TimUgovor]);

	format(Query, sizeof(Query), "%s, KaznaUgovor = '%d', PreostaloZaOtplatu = '%d', RKuca = '%d'",
	Query,PlayerInfo[playerid][KaznaUgovor], PlayerInfo[playerid][PreostaloZaOtplatu],PlayerInfo[playerid][RKuca]);

    format(Query, sizeof(Query), "%s WHERE ID = '%d'",Query,PlayerInfo[playerid][BazaID]);
	mysql_tquery(MySQL, Query, "OnTHREAD_UPDATEACCOUNT", "d", playerid);
	
	return 1;
}

Код:

forward OnTHREAD_UPDATEACCOUNT(playerid);
public OnTHREAD_UPDATEACCOUNT(playerid)
{
	return 1;
}

I have also tried to run query inside SaveAccount method with mysql_query and mysql_pquery but result is the same or even worse.

Thanks for help in advance!

[10MIN]

Do you really need to save everything on a query? Use something like:

• Player Action that modifies his/her info
• Modifing the array that stores the player info
• Executing a query to save the new value

For example:

• Player buys something
• Modify the money from array / variable
• Execute a query to update money in MySQL table

[NikolaSRB]

I am doing that already, every change made to players, their houses, apartments, vehicles etc are saved immediately.

I made this method only to test MySQL performance and what will happen if 500 players buys something at the same time which can be in theory real situation.

[Vince]

Quote:

Originally Posted by NikolaSRB I am doing that already, every change made to players, their houses, apartments, vehicles etc are saved immediately.

The query above suggests you're not. If you just call "SaveAccount" for everything then you're still sending way too much data. What 10MIN is hinting at is sending shorter queries that update one or two fields at a time.

Couple of points of advice:

1. Don't wrap decimal (%d) values in quotes. If they're wrapped in quotes the parser will treat these values like text and they will first need to be converted. This is relatively fast but it is still extra overhead. Apparently it will also cause indexes to not be used (see below).
2. Make sure your indexes are set up properly. In particular, make sure that you have indexes (unique or otherwise) on columns that are frequently featured in WHERE, GROUP BY or ORDER BY clauses.
3. Run EXPLAIN on "slow" queries to see where they can be optimized (e.g. EXPLAIN SELECT ... or EXPLAIN UPDATE ...). What you want to avoid are "full table scan" and "using filesort" because these are particularly slow. Sometimes a full table scan is unavoidable (such as when selecting all the rows in the table) but if you're only selecting or updating one or a subset of rows then it is worrisome.

I find it pretty annoying that you named all columns in your native language. I'm not planning to translate each and every one of them so I can't give you any advice on schema optimization.

[NikolaSRB]

Like I said, this query was made only for testing purposes. When changing for example players level, money, etc. I update only columns I am actually changing, not the entire entity in database.

I made few more versions of SaveAccount where I am updating only few columns, not entire table. Results are the same, 500 queries still take 15 - 20 sec for updating few (1-5) columns.

Also I am sorry for native language, there are few varchar columns and rest(most of them) are integer.

Could the problem be because table has 100 columns?

[Vince]

Quote:

Originally Posted by NikolaSRB Like I said, this query was made only for testing purposes. When changing for example players level, money, etc. I update only columns I am actually changing, not the entire entity in database.

Sorry, I must've misunderstood.

Quote:

Originally Posted by NikolaSRB Could the problem be because table has 100 columns?

I doubt it, although I tend to keep my own tables under 20 columns if I can help it at all. I have 35 tables and the largest one (vehicle table) has 17 columns.

I would still like to see the output of EXPLAIN. In phpMyAdmin (or whatever client you use) run your problematic query (with dummy data if you want) but insert the word EXPLAIN before it. The output should be a neat little table that outlines how the server will process this query.

[NikolaSRB]

Quote:

Originally Posted by Vince Sorry, I must've misunderstood. I doubt it, although I tend to keep my own tables under 20 columns if I can help it at all. I have 35 tables and the largest one (vehicle table) has 17 columns. I would still like to see the output of EXPLAIN. In phpMyAdmin (or whatever client you use) run your problematic query (with dummy data if you want) but insert the word EXPLAIN before it. The output should be a neat little table that outlines how the server will process this query.

Okay, screenshot is in attachment.

[NikolaSRB]

I need to add this, I tried saving all companies I have on server.

There are 2 different companies in database:

company_v1 - table with 48 records, 25 columns

company_v2 - table with 82 records, 12 columns

Function save companies update only 4 columns in each table.

Time for running these 82 + 48 update queries was 5sec.

Something is terribly wrong but I can't figure out what.

[Vince]

Well, it's using the primary key so in theory it should be very fast. Are you running on InnoDB or MyISAM? Because MyISAM is a lot slower writing data than InnoDB and it doesn't support foreign keys.

[NikolaSRB]

Quote:

Originally Posted by Vince Well, it's using the primary key so in theory it should be very fast. Are you running on InnoDB or MyISAM? Because MyISAM is a lot slower writing data than InnoDB and it doesn't support foreign keys.

Its InnoDB, that was actually the first thing I've checked..

The problem of slow MySQL wouldn't be such a big problem if it doesn't block SAMP thread and makes it looks like a lag.