1

I would like to add a test using PHP’s register_shutdown_function to verify that all database connections are closed. Additionally, I would like to automatically close these connections. I am on a shared hosting environment and thus do not have much system access.

The current site is experiencing horrible performance issues (broken access or >60 sec page loads) particularly when under a load exceeding 10 visitors. The slow downs affect admin screens and occur even when all plugins are disabled. Yet there are times when performance is less than 8 sec page load. Our hosting provider has responded by saying that we have open database connections that sometimes require manual closing.

I know that WordPress defers closing database connections to PHP and it should not create these kind of issues yet what else could it be.

Palec
  • 12,743
  • 8
  • 69
  • 138
tom.fflux
  • 11
  • 1
  • 3
  • You can improve page load speed with Cache plugins. They store the HTML output of your pages in static files to reduce drastically the number of SQL queries. I find [W3 Total Cache](http://wordpress.org/plugins/w3-total-cache/) to do this stuff very well. – Andrea Riva Dec 24 '13 at 15:32
  • I think Caching is a good idea. However, I don't think it would address issues of this magnitude and it will not provide me the comforting info I seek. We have tried caching in the past with little improvement in performance. Once the current performance issues are resolved, we intend to try again. – tom.fflux Dec 24 '13 at 15:50

3 Answers3

1

Disclaimer: I’m not a Wordpress user. My knowledge about Wordpress comes from articles I googled now, some of them are really old.

wpdb

Wordpress provides wpdb class and its global instance $wpdb. If I get it right, this instance is used both internally and by plugins to perform all database manipulation.

Explicitly closing wpdb connection

wpdb has no close method. From How to close wpdb connection? at WordPress Answers:

There is no explicit method. It stores link identifier in $wpdb->dbh field, so I guess you could use mysql_close() on it.

It looks like Wordpress is still using the outdated mysql API nowadays. There are three MySQL APIs in PHP now. If/when Wordpress migrates to a more recent one, inform me in comments and I’ll update this answer and the answer in the original question.

Automatic closing, persistent connections

Are you using persistent connections? They cannot be closed, they persist after the script finishes its execution.

When using non-persistent connections, they should be closed on script end. Quoting mysql_connect function manual page:

Note:

The link to the server will be closed as soon as the execution of the script ends, unless it's closed earlier by explicitly calling mysql_close().

Wordpress relies on that, connections are not closed explicitly. If you want to explicitly close the connection when destroying $wpdb, change

function __destruct() {
    return true;
}

to

function __destruct() {
    mysql_close($this->dbh);
    return true;
}

in wp-includes/wp-db.php.

Verifying that connections are closed

If you have a list of connections to be checked/closed, you can verify that they already have been closed by calling mysql_close with error suppression and return value checking:

if (@mysql_close($conn)) {
    echo "Closed now\n";
} else {
    echo "Probably already closed\n";
}

When not having such a list, you are doomed. There is no way to list all DB connections made by the current script. You can only list all connections to a server that you have an active connection to, but not as PHP resources. If you want to close them, you can do that using KILL statement if you have high enough privileges. Remember, though, that the connections may have been created and may be being used by other scripts.

See also

Community
  • 1
  • 1
Palec
  • 12,743
  • 8
  • 69
  • 138
  • Do you know a way to to verify that all the plugins use **$wpdb**? Or do I need to review the code for each plugin? And thanks for the good leads on tracking down verifying connections. – tom.fflux Dec 26 '13 at 21:45
  • @tom.fflux More precisely, you want to verify that the plugins do not use any other means to access the DB. The ultimate method to ensure they don’t is code review, indeed. When using many plugins and not wanting to be absolutely sure, you can try to detect other methods of accessing the DB. You can use [APD extension](http://php.net/manual/en/book.apd.php) to [rename](http://php.net/manual/en/function.rename-function.php) e.g. `mysql_connect()` to `orig_mysql_connect()` and create a new `mysql_connect()` that logs where it is called from and then calls `orig_mysql_connect()`. – Palec Dec 27 '13 at 01:06
  • @tom.fflux Also you can use [runkit](http://php.net/manual/en/book.runkit.php) instead of APD. But for overriding built-in functions, you need to enable [`runkit.internal_override`](http://php.net/manual/en/runkit.configuration.php#ini.runkit.internal-override) in PHP configuration. – Palec Dec 27 '13 at 01:10
  • @tom.fflux Also looking for use of `DB_*` constants from [wp-config.php](http://codex.wordpress.org/Editing_wp-config.php) may provide some info. Plugins creating their own connections to DB need the credentials. – Palec Dec 27 '13 at 01:27
1

WP 4.5 now has a $wpdb->close method

Terry
  • 21
  • 1
0

PHP use managed resources, which means resources (including DB connections) are automatically released when the script exits. Unless you use persistent connections but WordPress doesn't use persistent connections.

I also had this kind of issues on a VPS with the hosting provider telling me I had to close connections, the solution was to add page caching.

Claude Vedovini
  • 2,421
  • 21
  • 19