Someone Posted December 30, 2012 Share Posted December 30, 2012 (edited) 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 December 30, 2012 by Someone Quote Link to comment Share on other sites More sharing options...
gmoore Posted December 30, 2012 Share Posted December 30, 2012 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. Greg Quote Link to comment Share on other sites More sharing options...
Someone Posted December 30, 2012 Author Share Posted December 30, 2012 This is on my local windows 7 xampp PHP Version 5.4.7 Do not have much to compare with. The 1000ms was on all pages in NWE (I have not made any new queries outside of content.php files ). What connection time do you get? Quote Link to comment Share on other sites More sharing options...
gmoore Posted December 30, 2012 Share Posted December 30, 2012 I run local windows 7 xampp also. I don't think I can TRY to get 1000ms lol. Was MySQL turned off perhaps on the control panel? Or try turning it off and on. Also, have you tried a stand alone page outside of NWE and see what you get? Greg Quote Link to comment Share on other sites More sharing options...
Someone Posted December 31, 2012 Author Share Posted December 31, 2012 (edited) 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 Queries: 39 Query Time: 207 ms Edited December 31, 2012 by Someone Quote Link to comment Share on other sites More sharing options...
Someone Posted December 31, 2012 Author Share Posted December 31, 2012 (edited) 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. <?php $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 December 31, 2012 by Someone Quote Link to comment Share on other sites More sharing options...
Someone Posted December 31, 2012 Author Share Posted December 31, 2012 Sooooooo... 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 127.0.0.1 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 127.0.0.1 as host. This can be done in phpmyadmin. Seems to be no reason to use persistent connections after all :o Quote Link to comment Share on other sites More sharing options...
a_bertrand Posted January 4, 2013 Share Posted January 4, 2013 I never had such issue myself as localhost should not go through a DNS resolution anyhow. But good to know thanks for the trick 1331 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.