SA-MP Forums

Go Back   SA-MP Forums > SA-MP Scripting and Plugins > Scripting Help

Reply
 
Thread Tools Display Modes
Old 03/06/2020, 05:15 AM   #1
SkyFlare
Huge Clucker
 
SkyFlare's Avatar
 
Join Date: Apr 2015
Location: NZ
Posts: 221
Reputation: 43
Default Question about further MySQL Optimization

So in my Script, I am updating SQL Data as it changes as you should...
For example:
Code:
CMD:unbagounce(playerid, params[])
{
  UpdateSQLPlayerMarijuanaData(playerid);
    return 1;
}
Code:
UpdateSQLPlayerMarijuanaData(playerid)
{
	mysql_format(g_SQL, query, sizeof query, "UPDATE `x` SET `x` = %d WHERE `x` = %d LIMIT 1", x, x);
	mysql_tquery(g_SQL, query);
	return 1;
}
But when a player Leaves the Server... I am in conjunction, saving all data...
Code:
UpdatePlayerData(playerid)
{
	if (Player[playerid][IsLoggedIn] == false) return 0;
	if(Player[playerid][PlayerSpawnCalled] != false)
	{
		UpdateSQLPlayerPosData(playerid);
		UpdateSQLPlayerFamilyData(playerid);
		UpdateSQLPlayerFactionData(playerid);
		UpdateSQLPlayerCharacterData(playerid);
        UpdateSQLPlayerLevelGroupData(playerid);
        UpdateSQLPlayerAccountData(playerid);
        UpdateSQLPlayerMarijuanaData(playerid);
        UpdateSQLPlayerWeaponData(playerid);
        UpdateSQLPlayerChatToggles(playerid);
        UpdateSQLPlayerHouseData(playerid);
        UpdateSQLPlayerPhoneData(playerid);
        UpdateSQLPlayerChatTitles(playerid);
	}
	#if DEBUG_FUNCTIONS == true
		print("DEBUG: UpdatePlayerData Called");
	#endif
	return 1;
}
I fear this is going to be quite harsh on MySQL and Server Performance, wondering if/how I can make a check to see if SQL Entry is required or not, for each of these, without also causing stress on the server too.
__________________
Purchasable Scripting Services Available, Inbox me with your Requests.
My Project Progress Tracker
My Work
Link
Information
S-Store System V2 LinkIngame Store in Dialogs

Last edited by SkyFlare; 03/06/2020 at 07:54 AM.
SkyFlare is offline   Reply With Quote
Old 03/06/2020, 06:52 AM   #2
Variable™
Gangsta
 
Variable™'s Avatar
 
Join Date: Jul 2015
Posts: 828
Reputation: 200
Default Re: Question about further MySQL Optimization

Are you experiencing performance trouble doing it this way? I'd say that having tables for each system separately and updating them when needed is a better idea, and is safer when it comes to facing errors, since one system or two would get affected, not the entire saving system.

I'd say, lesser queries = lesser requests and may slightly be better on your performance but you should consider maintaining well-structured queries when you are considering performance, keeping queries organized according to what they are being used for is better in my opinion.

I see some people consider using one big query is better but since you're using pawn in your case, you need to create a very big string if you want to use one query, and you will eventually need to split up this query so it wouldn't do a difference any more, you will mostly end up doing it how you do it now. Except that you will waste time and effort.

I always recommend having stuff organized, and sometimes it might not be the best for performance, but it's worth it.
__________________
Need a game server? Contact me for information.
Variable™ is offline   Reply With Quote
Old 03/06/2020, 07:03 AM   #3
SkyFlare
Huge Clucker
 
SkyFlare's Avatar
 
Join Date: Apr 2015
Location: NZ
Posts: 221
Reputation: 43
Default Re: Question about further MySQL Optimization

Quote:
Originally Posted by Variable™ View Post
Are you experiencing performance trouble doing it this way? I'd say that having tables for each system separately and updating them when needed is a better idea, and is safer when it comes to facing errors, since one system or two would get affected, not the entire saving system.

I'd say, lesser queries = lesser requests and may slightly be better on your performance but you should consider maintaining well-structured queries when you are considering performance, keeping queries organized according to what they are being used for is better in my opinion.

I see some people consider using one big query is better but since you're using pawn in your case, you need to create a very big string if you want to use one query, and you will eventually need to split up this query so it wouldn't do a difference any more, you will mostly end up doing it how you do it now. Except that you will waste time and effort.

I always recommend having stuff organized, and sometimes it might not be the best for performance, but it's worth it.
Thanks for the detailed response, I am not seeing any Performance issues right now, I just feel like how it's handling it right now, OnPlayerDisconnect, its saving all that data, regardless if its split up into different queries like ive done or not, if I was to split the systems up, id still be saving it all, I just am worried if this was to be used on a larger scale of like 200+ players, the disconnects being so frequent, it may make my server hang, thats my worry at the moment, I basically am seeing if the way I am doing it now, is the most efficient available, or is there a tweak I can do, to prevent having to save ALL the data on exit? I mean if I make sure I am saving the data in all areas when its changing, id not need to on disconnect right?
__________________
Purchasable Scripting Services Available, Inbox me with your Requests.
My Project Progress Tracker
My Work
Link
Information
S-Store System V2 LinkIngame Store in Dialogs
SkyFlare is offline   Reply With Quote
Old 03/06/2020, 07:15 AM   #4
Variable™
Gangsta
 
Variable™'s Avatar
 
Join Date: Jul 2015
Posts: 828
Reputation: 200
Default Re: Question about further MySQL Optimization

mysql_tquery is basically a threaded MySQL query which shouldn't hang the main thread that the SA-MP server runs on.

Think about it, if you update the kill count in the database for every player, every single kill, and the same for deaths. You will practically execute more queries than you would by using one big query for the account system. More queries are heavier on the performance, so you will save the load on the disconnection and distribute it on other systems that are more frequent than a regular session time.

Players in death match for example kill too often, that means you will send too many update queries for both the killer and the dead, and if you have other data to update it will become hard to manage.

Use one big query for updating a table, and another big query for updating another table. Split queries if you are having too many columns in a single table. Don't mix irrelevant systems under one query just to save performance, and don't execute too many queries and every single update or you'd really have performance issues in an amount of players that is less than 200.

The way of using OnPlayerDisconnect to update data means you are updating the same data you'd update and even less often, in big queries, where each query is used for updating a specific table that is linked with a particular system. That way you have organized your system in a way that works well with performance.

If you update data per specific system, you will then depend on your players to update data and in this situation, if your players are interacting too often, you will have unstable load since it will all depends on what your players do. If there are many players doing many things that update too often, that's more load than just updating any necessary data on disconnection, because it would be less often, and same data will be updated anyway, but in lesser queries. Again saying, MySQL queries are threaded so you shouldn't worry about server hanging.
__________________
Need a game server? Contact me for information.
Variable™ is offline   Reply With Quote
Old 03/06/2020, 07:41 AM   #5
SkyFlare
Huge Clucker
 
SkyFlare's Avatar
 
Join Date: Apr 2015
Location: NZ
Posts: 221
Reputation: 43
Default Re: Question about further MySQL Optimization

Quote:
Originally Posted by Variable™ View Post
mysql_tquery is basically a threaded MySQL query which shouldn't hang the main thread that the SA-MP server runs on.

Think about it, if you update the kill count in the database for every player, every single kill, and the same for deaths. You will practically execute more queries than you would by using one big query for the account system. More queries are heavier on the performance, so you will save the load on the disconnection and distribute it on other systems that are more frequent than a regular session time.

Players in death match for example kill too often, that means you will send too many update queries for both the killer and the dead, and if you have other data to update it will become hard to manage.

Use one big query for updating a table, and another big query for updating another table. Split queries if you are having too many columns in a single table. Don't mix irrelevant systems under one query just to save performance, and don't execute too many queries and every single update or you'd really have performance issues in an amount of players that is less than 200.

The way of using OnPlayerDisconnect to update data means you are updating the same data you'd update and even less often, in big queries, where each query is used for updating a specific table that is linked with a particular system. That way you have organized your system in a way that works well with performance.

If you update data per specific system, you will then depend on your players to update data and in this situation, if your players are interacting too often, you will have unstable load since it will all depends on what your players do. If there are many players doing many things that update too often, that's more load than just updating any necessary data on disconnection, because it would be less often, and same data will be updated anyway, but in lesser queries. Again saying, MySQL queries are threaded so you shouldn't worry about server hanging.
That's very interesting, see also I have been thinking about in the event of a server crash, while the server is live, players wont be disconnecting on crashes, so data that hasnt updated during their gameplay, will be lost, which is where I am stuck deciding, weather to leave it for ondisconnect and make it bulletproof or even have a brief period of every hour do a save, or stay safe and update it after changes occur
__________________
Purchasable Scripting Services Available, Inbox me with your Requests.
My Project Progress Tracker
My Work
Link
Information
S-Store System V2 LinkIngame Store in Dialogs
SkyFlare is offline   Reply With Quote
Old 03/06/2020, 07:43 AM   #6
Variable™
Gangsta
 
Variable™'s Avatar
 
Join Date: Jul 2015
Posts: 828
Reputation: 200
Default Re: Question about further MySQL Optimization

You can update players data automatically using a timer (or a task if you use YSI) every 5-7 minutes and it wouldn't hurt at all.
__________________
Need a game server? Contact me for information.
Variable™ is offline   Reply With Quote
Old 03/06/2020, 07:49 AM   #7
SkyFlare
Huge Clucker
 
SkyFlare's Avatar
 
Join Date: Apr 2015
Location: NZ
Posts: 221
Reputation: 43
Default Re: Question about further MySQL Optimization

Quote:
Originally Posted by Variable™ View Post
You can update players data automatically using a timer (or a task if you use YSI) every 5-7 minutes and it wouldn't hurt at all.
I will definitely do that then, if it's not gonna hurt then why not, that saves me having to be so conscious about saving after every change in the cmds
__________________
Purchasable Scripting Services Available, Inbox me with your Requests.
My Project Progress Tracker
My Work
Link
Information
S-Store System V2 LinkIngame Store in Dialogs
SkyFlare is offline   Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Server Optimization Question BrainDamaged Scripting Help 5 09/12/2018 10:53 AM
Question for optimization and protection SmockRO Server Support 2 25/11/2018 09:39 AM
Question on optimization Seifspeed Scripting Help 4 12/07/2018 11:49 AM
Optimization Related Question elcid7772 Scripting Help 2 23/08/2013 07:41 AM
An Optimization question. Ronaldo_raul™ Scripting Help 2 11/02/2012 01:09 PM


All times are GMT. The time now is 04:36 AM.


Powered by vBulletin® Version 3.8.6
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.