Perl DBI connect and disconnect

[Akkadius]

Originally Posted by thepoetwarrior

That show processlist in mysql> was very useful.

I confirmed that $dbh->disconnect(); indeed does work to close a global $dbh

Also a global connection variable will still pass if(!$dbh) will still pass as connected even though using disconnect on it.

So ether non-global with 'my' or disconnect at end, will disconnect it.

I guess question I have, for global_player.pl should I just make 1 global connect when player zones in, and disconnect it when player zones out? Instead of always making new connects each time we get something from DB?

I would keep it open if I were you. Otherwise you are opening and tearing down sessions constantly for no good reason.

Keep it global per global_player.pl, because:

global_player.pl runs per zone, not per player.

Yes each player may trigger the Perl script but it is not unique to each player, the same script and same variables are accessible no matter what character runs it in that zone. That is why the need for entity variables came in to play, so you can declare unique variables to an entity.

Same goes for NPC's, if you use a default.pl, only one instance of the script runs.

So in conclusion, if you declare a connect variable, it is accessible to each client within the zone. Just because a client leaves a zone do you need to tear down that variable for good measure because another client in the zone will use it because it is already globally declared and the session is open.

EDIT: Also to save you some time, there was a period where I would check to see if I had declared DBI, and checked if it was declared before I declared it again. Like:

if(!$connect){ $connect = plugin::LoadMysql(); }

This does not work in the long run because you can have a connect declared but that doesn't mean that your mysql session could tear itself down within that time, so it is always best to redeclare the open.

What issues are you running into that you are concerned with?

If you are running into a simultaneous connections issue, you can raise that with your Mysql my.cnf, google for 'max connections mysql'

[thepoetwarrior]

I have my own sub function for $connect = function() but yeah does the same thing as your plugin.

No real issues besides being too poor or untrusting to host elsewhere for more bandwidth, which is topic for a different day.

Just trying to be more efficient. Right now we have

my $connect = LoadMySQLConnection()

and

$connect->disconnect()

about 56 times each across 9,600 lines in global_player.pl

Most of them are for my GM tools, and some of them for players such as custom stats window and instances/waypoints.

Almost all of the

my $connect =

is done immediately after

sub Function {

and

$connect->disconnect

done just before the

return $variable; }

end of the sub function.

Would like to find a way to maybe only add public player type code to global_player.pl, then add more stuff from external text file if a GM ($status > X) zones in, which could reduce maybe 80% of the code since the file is over 300k already in size. I guess that is where plugins would come in then, so maybe explaining it wrong, or just reinventing the wheel.

Was reading advice on perl and mysql websites for tips. Found one such as don't make connect in middle of while loops, which I think is common sense and we've never done that before.

Since

if(!$connect){ $connect = plugin::LoadMysql(); }

would fail, showing always connected even if connection is lost, you say "best to redeclare the open". Would this result in multiple connections or would it over write the last one? I'll have to test that out.

I guess I wanted to make sure I wasn't locking up my db from too many connections or anything but so far shouldn't be a problem since they are all my declared and have disconnect. The only issue then would be the spam of connect/disconnect so might revamp the code just to keep an open connection, if redeclare over writes instead of making new additional connection.

I actually try not to use DBI as much as possible. Stuff that tracks bots, or AA/Exp per hour, usually do all the work with EntityVariables and then only log or access the db tables when meeting certain requirements like above X aa per hour, or after X minutes.

For our leaderboards, I even use EntityVariable to save the time() as a way to compare/check prevent players from spamming the db, that they can only view leaderboards once every X seconds. Same with anti /say chat spam, made a queue that allows player to say up to 5 things in 10 seconds, else the if statements won't check what they want to avoid spamming the db, etc.

DBI and custom tables opened up a whole ton of possibilities for stuff like leaderboards, instances, and GM tools.

Thanks for your time and knowledge as always Akkadius.

[thepoetwarrior]

you can raise that with your Mysql my.cnf, google for 'max connections mysql'

Seems we're set to 800 max connections.

We have 337 static + dynamic zones loaded checked via /server command in game.

Any opinion if this is high enough, considering during holidays we peek around 500-600 players online + 337 zones, is 800 concurrent connections enough?

What would the dangers be of doubling the 800 to 1600?

Will check out other settings in the my.ini file see what can be tweaked.