[SkyFlare]

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.

[Variable™]

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.

[SkyFlare]

Quote:

Originally Posted by Variable™ 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?

[Variable™]

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.

[SkyFlare]

Quote:

Originally Posted by Variable™ 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

[Variable™]

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.

[SkyFlare]

Quote:

Originally Posted by Variable™ 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