SQL global_reg_value

[nanakiwurtz]

Hi I have a question about this kind of script, I have read lots of script like this and save the value into global_reg_value for each characters.

And I read in a post that AnnieRuru said something like this 'saving too much value into global_reg_value will slow your server down', so is it necessary to use the 'set Freebies,1;' for each characters?

[AnnieRuru]

Hi I have a question about this kind of script, I have read lots of script like this and save the value into global_reg_value for each characters.

And I read in a post that AnnieRuru said something like this 'saving too much value into global_reg_value will slow your server down', so is it necessary to use the 'set Freebies,1;' for each characters?

yeah

http://www.eathena.ws/board/index.php?s=&showtopic=181741&view=findpost&p=1478950

what is the thing you don't understand ?

[nanakiwurtz]

So it's save to use the global_reg_value for every character in a server?

It may be still far from the limitation allowed in the SQL though, but if I may ask, what is the safe limit and the maximum limit for saving in the global_reg_value?

[AnnieRuru]

global_reg_value .... in theory is unlimited

because SQL InnoDB will perform row locking ...

http://dev.mysql.com...al-locking.html

well its more efficient to convert your SQL to innoDB for rathena ...

because it only retrieve row value

src\char\inter.c

   	 if( SQL_ERROR == Sql_Query(sql_handle, "SELECT `str`, `value` FROM `%s` WHERE `type`=3 AND `char_id`='%d'", reg_db, char_id) )
  	 if( SQL_ERROR == Sql_Query(sql_handle, "SELECT `str`, `value` FROM `%s` WHERE `type`=2 AND `account_id`='%d'", reg_db, account_id) )

`mapreg` on the other hand is the one that need to watch out

because it perform whole table search

src\map\mapreg_sql.c

if ( SQL_ERROR == SqlStmt_Prepare(stmt, "SELECT `varname`, `index`, `value` FROM `%s`", mapreg_table)

[Vach]

What is innoDB?

[AnnieRuru]

https://rathena.svn....gine_innodb.sql

its inside your own folder LOL

http://www.softwarep...gines-1470.html

http://dba.stackexch...nnodb-or-myisam

myisam is mostly use for read-heavy + table locking storage engine = such as pvp ladder ( always select ... order by kill desc )

innodb is mostly use for write-heavy + row locking storage engine = such as quest script ( select ... from char_id ... only 1 row is retrieve )

[GmOcean]

In short, you don't really need to worry too much about using perm-char variables. But watch out for too much #variables($) & $variables($) as these will slowly start to eat your server...(I learned the hard way, but Annie saved me before it was too late =P).

[Vach]

I'm still a little confused, which us embarassing because I'm usually pretty versed in the sql stuff.

So I would want to execute that sql script on top of main.sql as soon as possible? Should that be in the wiki?

[AnnieRuru]

its totally up to the user wants to execute it or not

http://www.eathena.ws/board/index.php?autocom=bugtracker&showbug=1252

http://www.eathena.ws/board/index.php?autocom=bugtracker&showbug=1342

performance-wise, and at least in theory, in rathena

innodb should be faster than myisam because we mostly perform WHERE `account_id` or WHERE `guild_id` stuffs

however innodb type tables doesn't support "INSERT DELAYED INTO" command ... though

but in practical, myisam is still quite stable ...

because most rathena users doesn't have problems with it

not to mention that our query has already index properly

CREATE TABLE IF NOT EXISTS `char` (

.......

PRIMARY KEY (`char_id`),

UNIQUE KEY `name_key` (`name`),

KEY `account_id` (`account_id`),

KEY `party_id` (`party_id`),

KEY `guild_id` (`guild_id`),

KEY `name` (`name`),

KEY `online` (`online`)

just take a peek at our own sql-files you might also learn something from it Edited November 15, 2012 by AnnieRuru

[Vach]

Hmm... well based on my situation...

In short, you don't really need to worry too much about using perm-char variables. But watch out for too much #variables($) & $variables($) as these will slowly start to eat your server...(I learned the hard way, but Annie saved me before it was too late =P).

This worries me, I have a TON of $Variables$ for my quest system, and many #variables$... what should I do to minimize problems? Should I move all my $variables$ to some kind of temporary function call pretending to be global variables? More specifically, should I change that ASAP?

[KeyWorld]

I rarely used permanent global variable in my server.

In a lot of case you don't need to have them.

Same with $@var (but it's not a permanent one), for now I don't see the use of this type of variable with my way to script.

If you want to find solution, first you should start thinking if all this variables really need to be permanent ?

Secondly, you can try to pack multiple variables into one, using bitmask or storing multiples values in a string and delimit content with "#" or something else.

But the best way is definitely to change the way you script to avoid using permanent variable when it's not needed.

[Vach]

The permanent global variables are avoidable, it will just drastically slow the script down, and I can't set npc "permanent" .Variables in the script because of how the script is primarily a function... although that could work since its technically still an "NPC."

[GmOcean]

Well, in regards to your global variables, you can remove all of those, an replace them with .variables then, when you need to call them for information, just use, getvariableofnpc(); however, do note, that, it will get a little confusing, as each npc does have it's own .value for the variable, so you just need to make sure you ping the correct npc when using the command.

And you wouldn't need to change them asap, just little by little... i unfortunately had over 1thousand $(@)variables in use at the time, since i was using everything with set/getd. This just means i had a $var for each character involved at the time, i also didn't do the proper measures to make sure they were erased when they were no longer needed.

Lastly as an alternative to .variables and using getvariableofnpc(); you can also just use, setd/getd to store the information into perm-char variables, if they need to be perm.

e.g: setd "variable"+ getcharid(0) +"",123; getd("variable"+ getcharid(0) +"");

This efficently stores the information in a variable UNIQUE to the character, since part of the variable has the character's ID_num as it's name. This way, you know, no other person can access the data stored in another's variable. (Unless they hacked his/her account lol).

[AnnieRuru]

@Vach

try and crack my mission board script

after you tested my script,

setup a quest ... and get a player complete a 'Only Once' or 'Everyday' mission limitation

then try run

select * from mapreg where varname like '$ms_b_%';
select * from global_reg_value where str like 'ms_b_%';

the method I used was quite optimized ... well at the time of creation

now the method is getting outdated, but still this script is still working in latest SVN

[Vach]

I actually use setd and getd for the temporary variables used in the function call, as it makes it dynamic and I never need to change anything.

I need them global, or at least thought I did, because my function matrix (bunch of nested functions) calls them and explodes them/reads them for data... But if I can make a single function have access to .Variables and pass that to the other functions which then use temporary scope variables... Viola. No globals.

So my question to you, if I put ".Var$,"String"" inside a "- script function {" NPC... Setting it with OnInit, will those variables stay for the duration of map server load even though its just a function NPC?

@Annie - I don't understand... You want me to test your script?

[AnnieRuru]

1.

prontera,156,185,5	script	dfjhdksjf	100,{
mes .itemlist$;
next;
select .itemlist$;
mes .item$[ @menu -1 ];
close;
OnInit:
.itemlist$ = "hat:cat:lot:pot:hot";
explode .item$, .itemlist$, ":";
close;
}

2.

@Annie - I don't understand... You want me to test your script?

yeah

[Vach]

@Annie

Yea, that's what I was thinking... Will that method work with a global function NPC?

And okay, I'll take a look.

[Vach]

Well, there is no way to dynamically call a .Variable with getvariableofnpc (I am using getd at the moment for the global variables). So instead, I am going to make them temporary global variables... otherwise I'd have to write a gigantically huge if statement in my script and that just can't do.

If anyone knows a way to use getvariableofnpc with a variable named via a string (aka, getd), please let me know... because function NPCs can't seem to store .Variables properly. =/

[ToastOfDoom]

I recall that Using getd inside getvariableofnpc Works.

Getvariableofnpc(getd("var"), <npc>)

Sorry I can't test this ATM.

[Vach]

Yep, was gonna say, tested that for the kicks and it actually works. Thanks for the post, though.

This brings me to my next question... what's so bad about $@ vars? I get what's bad about billions of $Vars... but I mean, if I use $@vars I'm only calling one function and if I use the getvariableofnpc with getd embedded I call 2 functions then one more.

Wouldn't the globals use less processing power with the same amount of memory (as setting a bunch of vars into memory each time the server boots)?

[ToastOfDoom]

Its not really a matter of how much processing power but more about organising Data better. If you have all your data in the global scope it is very easy for other scripts to accidentally access and modify that same data.

[Vach]

Oh... in that case I'm definitely fine with it this way. The variable names are filled with numbers with complex hungarian programming notation.

Thanks for your quick response. =)