Persistent Database Connections (MySQLi)

Get help with installation and running phpBB 3.0.x here. Please do not post bug reports, feature requests, or MOD-related questions here.
Anti-Spam Guide
Forum rules
END OF SUPPORT: 1 January 2017 (announcement)
Locked
Haravikk
Registered User
Posts: 261
Joined: Sat Nov 02, 2002 4:42 pm

Persistent Database Connections (MySQLi)

Post by Haravikk » Thu Dec 06, 2012 2:48 pm

Okay, so I'm trying to optimise my database usage for a forum that's grown well beyond what I ever expected. So far I've already switched to InnoDB from MyISAM to reduce the slow down from table blocking, and I'm using a content distribution network to offload the many download requests to my site.

Anyway, one thing that it seems I could still optimise is my database connections, as phpBB still seems to be using a single connection per user with no persistency.

Now it looks like in /includes/db/mysqli.php that persistency is now supported for php 5.3 and onwards, which is fine, however as far as I can tell there's no correct way to enable persistent connections.

Someone correct me if I'm wrong, but the database connection is established by common.php on the following line (108):

Code: Select all

$db->sql_connect($dbhost, $dbuser, $dbpasswd, $dbname, $dbport, false, defined('PHPBB_DB_NEW_LINK') ? PHPBB_DB_NEW_LINK : false);
The 2nd to last argument (for database persistency) is set to false; it isn't fed by a constant or other configuration information.

So does this mean that persistent connections are unsafe to use, or is it okay to simply switch this to true? If it's okay to use, can anyone comment on how much of a difference (if any) it ought to make to performance on a large site (typically around 200 to 300 concurrent users)?

Lastly, if the feature is safe to use, should I make a bug report to see the persistency setting correct somehow?

User avatar
HGN
Former Team Member
Posts: 4706
Joined: Wed Dec 03, 2008 1:53 pm
Location: The Netherlands
Name: Alfred
Contact:

Re: Persistent Database Connections (MySQLi)

Post by HGN » Thu Dec 06, 2012 10:42 pm

The intention of the PHPBB_DB_NEW_LINK constant is to have that constant defined in an external application when you have phpBB embedded in that application.
It makes you able to force phpBB to open it's own database connection (when the constant is defined true) or to use the database connection allready opened by the existing application (when the constant is defined false).

Oleg
Former Team Member
Posts: 1221
Joined: Sat Jan 30, 2010 4:42 pm
Location: NYC
Contact:

Re: Persistent Database Connections (MySQLi)

Post by Oleg » Fri Dec 07, 2012 12:14 am

To open a persistent connection you must prepend p: to the hostname when connecting.
http://php.net/manual/en/mysqli.persistconns.php

This is also in the code of mysqli connect method in dbal.
Participate in phpBB development: Get involved | Issue tracker | Report a bug | Development board | [url=irc://chat.freenode.net/phpbb-dev]Development IRC chat[/url]
My stuff: mindlinkgame.com

Haravikk
Registered User
Posts: 261
Joined: Sat Nov 02, 2002 4:42 pm

Re: Persistent Database Connections (MySQLi)

Post by Haravikk » Fri Dec 07, 2012 10:06 am

HGN wrote:The intention of the PHPBB_DB_NEW_LINK constant is to have that constant defined in an external application when you have phpBB embedded in that application.
I'm not asking about PHPBB_DB_NEW_LINK (which doesn't actually seem to be implemented in the phpBB 3.0.11 version of mysqli.php anyway). I'm interested in the persistence argument (second to last parameter of the sql_connect function).
Oleg wrote:
To open a persistent connection you must prepend p: to the hostname when connecting.
http://php.net/manual/en/mysqli.persistconns.php

This is also in the code of mysqli connect method in dbal.
Sorry but that's not what I'm asking either; I know that this is implemented in mysqli.php, the problem is that the persistence argument is always passed a value of false, so a persistent connection is never created, and there appears to be no way of changing this without editing common.php directly.

Now I can easily enough change that or add a constant of my own to control it, but what I wanted to know is why a value of false is always passed into it; it suggests that the feature has intentionally been left disabled, which had me wondering if it's unsafe to use for some reason, i.e - is phpBB incompatible with persistent connections for some reason, or are they considered unnecessary? For example, the documentation for MySQLi persistence points out that clean up is performed after each use of the persistent connection; does this mean that it will actually make performance worse, or would it still be a better option for a large forum?

Oleg
Former Team Member
Posts: 1221
Joined: Sat Jan 30, 2010 4:42 pm
Location: NYC
Contact:

Re: Persistent Database Connections (MySQLi)

Post by Oleg » Fri Dec 07, 2012 10:50 am

1. It is my estimate that persistent connections in most configurations don't gain much.

2. It is my impression that most programs don't use persistent connections. Therefore there is higher probability of bugs when persistent connections are used.

I would guess that persistent connections are used by less than 5% of installations. Maybe less than 1%. Keep in mind that persistent connections require more simultaneous connections to the database to exist.

If you want to be safe, instrument the connect call and find out how long it takes vs using a persistent connection.
Participate in phpBB development: Get involved | Issue tracker | Report a bug | Development board | [url=irc://chat.freenode.net/phpbb-dev]Development IRC chat[/url]
My stuff: mindlinkgame.com

Locked

Return to “[3.0.x] Support Forum”