Perl DBI connect and disconnect

[thepoetwarrior]

I've tested perl mysql DBI connect and disconnect in various ways and it seems that disconnect doesn't do anything.

$dbh->disconnect;
$dbh->disconnect();

It still has a value if I pass them to quest::gmsay()

putting a 'my' in front of it makes it disappear after the sub is done, which was tested with if(!$dbh), otherwise becomes global or something.

Is disconnect required? Does it even do anything?

If we don't disconnect, would we eventually have hundreds and thousands of active connections?

Hope this make sense, and someone can answer.

[Akkadius]

I've tested perl mysql DBI connect and disconnect in various ways and it seems that disconnect doesn't do anything.

$dbh->disconnect;
$dbh->disconnect();

It still has a value if I pass them to quest::gmsay()

putting a 'my' in front of it makes it disappear after the sub is done, which was tested with if(!$dbh), otherwise becomes global or something.

Is disconnect required? Does it even do anything?

If we don't disconnect, would we eventually have hundreds and thousands of active connections?

Hope this make sense, and someone can answer.

These are valid concerns, however I don't think you have too much to worry about. Define your connection variable globally and you'll be fine. You're going to have tons of connections regardless if you use DBI regularly as part of your normal player tools. Typically you're going to see at least one DBI auth per zone process. See mine below, all of the roots are the zone processes, dbiconnect is obviouslly DBI.

I define all of my connect statements like this (For reference)

$connect = plugin::LoadMysql();

And I use a plugin that is referenced here: http://www.eqemulator.org/forums/showthread.php?t=37649


mysql> show processlist;
+---------+------------+-----------------+--------------+---------+-------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+---------+------------+-----------------+--------------+---------+-------+-------+------------------+
| 1365401 | root | localhost:16413 | eqemu2 | Sleep | 1 | | NULL |
| 2519344 | root | localhost:32415 | eqemu2 | Sleep | 8 | | NULL |
| 2519405 | root | localhost:32480 | eqemu2 | Sleep | 3 | | NULL |
| 2519406 | root | localhost:32481 | eqemu2 | Sleep | 4 | | NULL |
| 2519407 | root | localhost:32482 | eqemu2 | Sleep | 0 | | NULL |
| 2519408 | root | localhost:32483 | eqemu2 | Sleep | 7 | | NULL |
| 2519409 | root | localhost:32484 | eqemu2 | Sleep | 5 | | NULL |
| 2519410 | root | localhost:32485 | eqemu2 | Sleep | 4 | | NULL |
| 2519411 | root | localhost:32486 | eqemu2 | Sleep | 4 | | NULL |
| 2519412 | root | localhost:32487 | eqemu2 | Sleep | 4 | | NULL |
| 2519413 | root | localhost:32488 | eqemu2 | Sleep | 5 | | NULL |
| 2519414 | root | localhost:32489 | eqemu2 | Sleep | 1 | | NULL |
| 2519415 | root | localhost:32490 | eqemu2 | Sleep | 7 | | NULL |
| 2519416 | root | localhost:32491 | eqemu2 | Sleep | 2 | | NULL |
| 2519417 | root | localhost:32492 | eqemu2 | Sleep | 4 | | NULL |
| 2519418 | root | localhost:32493 | eqemu2 | Sleep | 3 | | NULL |
| 2519419 | root | localhost:32494 | eqemu2 | Sleep | 5 | | NULL |
| 2519420 | root | localhost:32495 | eqemu2 | Sleep | 4 | | NULL |
| 2519421 | root | localhost:32496 | eqemu2 | Sleep | 4 | | NULL |
| 2519422 | root | localhost:32497 | eqemu2 | Sleep | 2 | | NULL |
| 2519423 | root | localhost:32498 | eqemu2 | Sleep | 4 | | NULL |
| 2519424 | root | localhost:32499 | eqemu2 | Sleep | 4 | | NULL |
| 2519425 | root | localhost:32500 | eqemu2 | Sleep | 1 | | NULL |
| 2519426 | root | localhost:32501 | eqemu2 | Sleep | 4 | | NULL |
| 2519427 | root | localhost:32502 | eqemu2 | Sleep | 4 | | NULL |
| 2519428 | root | localhost:32503 | eqemu2 | Sleep | 4 | | NULL |
| 2519429 | root | localhost:32504 | eqemu2 | Sleep | 5 | | NULL |
| 2519430 | root | localhost:32505 | eqemu2 | Sleep | 5 | | NULL |
| 2519431 | root | localhost:32506 | eqemu2 | Sleep | 4 | | NULL |
| 2519432 | root | localhost:32507 | eqemu2 | Sleep | 5 | | NULL |
| 2519433 | root | localhost:32508 | eqemu2 | Sleep | 7 | | NULL |
| 2519434 | root | localhost:32509 | eqemu2 | Sleep | 4 | | NULL |
| 2519435 | root | localhost:32510 | eqemu2 | Sleep | 5 | | NULL |
| 2519436 | root | localhost:32511 | eqemu2 | Sleep | 5 | | NULL |
| 2519437 | root | localhost:32512 | eqemu2 | Sleep | 5 | | NULL |
| 2519438 | root | localhost:32513 | eqemu2 | Sleep | 4 | | NULL |
| 2519439 | root | localhost:32514 | eqemu2 | Sleep | 4 | | NULL |
| 2519440 | root | localhost:32515 | eqemu2 | Sleep | 3 | | NULL |
| 2519441 | root | localhost:32516 | eqemu2 | Sleep | 5 | | NULL |
| 2519442 | root | localhost:32517 | eqemu2 | Sleep | 1 | | NULL |
| 2519443 | root | localhost:32518 | eqemu2 | Sleep | 4 | | NULL |
| 2519444 | root | localhost:32519 | eqemu2 | Sleep | 5 | | NULL |
| 2872975 | dbiconnect | localhost:63610 | eqemu2 | Sleep | 19228 | | NULL |
| 2873477 | dbiconnect | localhost:64166 | eqemu2 | Sleep | 17067 | | NULL |
| 2873486 | dbiconnect | localhost:64175 | eqemu2 | Sleep | 17032 | | NULL |
| 2874877 | dbiconnect | localhost:1225 | eqemu2 | Sleep | 10567 | | NULL |
| 2874878 | dbiconnect | localhost:1226 | eqemu2 | Sleep | 10567 | | NULL |
| 2874879 | dbiconnect | localhost:1227 | eqemu2 | Sleep | 10567 | | NULL |
| 2876350 | dbiconnect | localhost:2969 | eqemu2 | Sleep | 3173 | | NULL |
| 2876351 | dbiconnect | localhost:2971 | eqemu2 | Sleep | 3150 | | NULL |
| 2876352 | dbiconnect | localhost:2972 | eqemu2 | Sleep | 3150 | | NULL |
| 2876353 | dbiconnect | localhost:2973 | eqemu2 | Sleep | 3150 | | NULL |
| 2876674 | dbiconnect | localhost:3379 | eqemu2 | Sleep | 707 | | NULL |
| 2876681 | dbiconnect | localhost:3388 | achievements | Sleep | 639 | | NULL |
| 2876699 | dbiconnect | localhost:3423 | eqemu2 | Sleep | 278 | | NULL |
| 2876700 | dbiconnect | localhost:3424 | eqemu2 | Sleep | 258 | | NULL |
| 2876701 | dbiconnect | localhost:3425 | eqemu2 | Sleep | 258 | | NULL |
| 2876702 | dbiconnect | localhost:3426 | eqemu2 | Sleep | 258 | | NULL |
| 2876708 | dbiconnect | localhost:3437 | eqemu2 | Sleep | 225 | | NULL |
| 2876711 | dbiconnect | localhost:3442 | achievements | Sleep | 156 | | NULL |
| 2876712 | dbiconnect | localhost:3446 | eqemu2 | Sleep | 88 | | NULL |
| 2876713 | dbiconnect | localhost:3447 | eqemu2 | Sleep | 88 | | NULL |
| 2876714 | dbiconnect | localhost:3448 | eqemu2 | Sleep | 88 | | NULL |
| 2876716 | root | localhost:3460 | eqemu2 | Query | 0 | init | show processlist |
+---------+------------+-----------------+--------------+---------+-------+-------+------------------+
64 rows in set

Hopefully that helps

[thepoetwarrior]

Yup, thanks again!

[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?

[Akkadius]

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.

[thepoetwarrior]

Seems we have improved performance, either with stoptimer after each timer, then startimer, or it was the MySQL max connections increased that helped.