Jump to content

Persistent mysqli connection


Recommended Posts

Enabled the profiler, and saw that db connection took about 1000ms on average. A freaking second!

Looked into mysqli and changed line 36 in lib/config.php


$this->conn = new mysqli('p:'.$host, $username, $password, $dbname);


To enable persistent connection. So new connections are not made on each page load. Connection time went down to 2-3ms

Persistent connection has other things to think about I am sure, and still learning about mysqli myself. But I will at lest use it while developing. Does make sense to use in a game, where a user is expected to do frequent page loads.

Should perhaps be an optional setting?


Persistent connection support was introduced in PHP 5.3 for the mysqli extension. Support was already present in PDO MYSQL and ext/mysql. The idea behind persistent connections is that a connection between a client process and a database can be reused by a client process, rather than being created and destroyed multiple times. This reduces the overhead of creating fresh connections every time one is required, as unused connections are cached and ready to be reused.

Unlike the mysql extension, mysqli does not provide a separate function for opening persistent connections. To open a persistent connection you must prepend p: to the hostname when connecting.

The problem with persistent connections is that they can be left in unpredictable states by clients. For example, a table lock might be activated before a client terminates unexpectedly. A new client process reusing this persistent connection will get the connection "as is". Any cleanup would need to be done by the new client process before it could make good use of the persistent connection, increasing the burden on the programmer.

The persistent connection of the mysqli extension however provides built-in cleanup handling code. The cleanup carried out by mysqli includes......

More about persistent connections http://php.net/manual/en/mysqli.persistconns.php

Edited by Someone
Link to comment
Share on other sites

Do you get slow connections from your host normally? I mean you shouldn't have to use persistent connections since other parts of the software may/probably don't clean up after themselves (in other words doing this may introduce other errors). But I would look at your Mysql connection speed outside of the software first.

My opinion.


Link to comment
Share on other sites

So 1000ms is not normal then LOL

Was looking for similar systems to compare against.

When using persistent connections, I do get errors if not logged in though. If I send a bug report, it works again, something in the bug repoeting restores the connection. So its not working 100% just to change the connection type.


Profiler info: (mouse over -> expand)
Include time:
10 ms
DB Conn time:
1 ms
Pre process time:
4 ms
Load stats time:
45 ms
Run libs time:
8 ms
admin_game_stats	0 ms
avatar	0 ms
banner	0 ms
clans	0 ms
combat	1 ms
hospital	0 ms
inventory	0 ms
jail	0 ms
level_handler	0 ms
locations	0 ms
messages	0 ms
ngm_crime	0 ms
npc_shop	0 ms
personal_log	0 ms
quests	0 ms
security_token	0 ms
speed_puzzle	0 ms
validate_values	0 ms
Run auto_pre_content time:
112 ms
admin_game_stats	81 ms
admin_side_panel	11 ms
auto_logout	1 ms
cron	1 ms
flood_control	1 ms
game_lock	1 ms
lottery_grid	1 ms
ngm_crime	1 ms
player_class	3 ms
player_template	2 ms
restore_stats	3 ms
tutorial	4 ms
Run module time:
92 ms
Run auto_post_content time:
109 ms
admin_action_log	58 ms
inside_menu_boots	43 ms
messages_alert	1 ms
ngm_stats	1 ms
tutorial	1 ms
Run finish time:
7 ms
Full engine time:
390 ms
Query Time:
207 ms
Edited by Someone
Link to comment
Share on other sites

Went and checked the profiler at the engine demo, it was 1ms as well, so there must be something slowing my end. Good point on testing outside of NWE.


$time = microtime();
$time = explode(' ', $time);
$time = $time[1] + $time[0];
$start = $time;

new mysqli('localhost', 'root', 'PASSWORDHERE', 'DATABASENAME');

$time = microtime();
$time = explode(' ', $time);
$time = $time[1] + $time[0];
$finish = $time;
$total_time = round(($finish - $start), 4);
echo 'Page generated in '.$total_time.' seconds.';

Outputs: Page generated in 1.0282 seconds.

Edited by Someone
Link to comment
Share on other sites


Page generated in 0.0108 seconds. :D

The issue was with hostname resolving. Connecting with localhost as the server name on windows machines may cause problems.

If anybody in the future have long mysql connection time. The simplest will be to use as hostname instead of localhost.

Or in mysql config file under [mysqld] section add skip-name-resolve

_Before_ doing that, make sure to have added a user with as host. This can be done in phpmyadmin.

Seems to be no reason to use persistent connections after all :o

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...