PhpRiot
News Archive
PhpRiot Newsletter
Your Email Address:

More information

PHP replication plugin statistics and troubleshooting

Note: This article was originally published at Planet PHP on 18 April 8560.
Planet PHP

The PHP replication and load balancing mysqlnd plugin as been released as an alpha through PECL. Alpha is for those who want to try out the 1.0 feature set as early as possible. Alpha is for those who do not fear debugging, if need be. Notes on troubleshooting.

Testing load balancing and read/write split

There are three ways to verify that load balancing and read/write split works as it should:

  • within PHP script: checking thread/connection ids
  • within PHP script: monitoring statistics
  • externally: mysqlnd debug log file

Thread/connection ids to distinguish connections

PECL/mysqlnd_ms changes the semantics of a PHP MySQL connection handle. If using the plugin, a connection handle returned by any of the three PHP MySQL extensions (mysql, mysqli, PDO_MySQL) does no longer map to a physical MySQL connection in a strict 1:1 way. The connection handle represents a pool of connections managed by the plugin. At a time, the connection handle may be mapped to a connection to the master. Later on, it may be mapped to a connection to one of the slaves.

Every physical connection to MySQL has a thread/connection id. By checking the thread id, a PHP script can detect if the plugin has decided to switch connections. You can obtain the thread id of a mysql or mysqli connection handle using the functions mysql_thread_id() respectively mysqli_thread_id(). PDO_MySQL offers no corresponding API call. Please, check the thread id immediately after running a query.

You must not use the query SELECT CONNECTION_ID() to fetch the thread id for checking if the load balancing plugin works! The query itself will be load balanced. It makes the plugin potentially switch connections. It changes the state of the connection handle. Thus, it cannot be used to monitor the state.

[myapp] master[]=localhost:/tmp/mysql.sock slave[]=192.168.2.27:3306 pick[]=random_once

function run_query($mysqli, $query, $hint = NULL) { if (!is_null($hint)) $query = sprintf("/*%s*/%s", $hint, $query); if ($ret = $mysqli-query($query)) return $ret; printf("[%d] %s\n", $mysqli-errno, $mysqli-error); return $ret; } /* Create load balanced connection handle */ $mysqli = new mysqli("myapp", "username", "password", "database"); /* Find connection thread ids, assumption: SQL hints work */ $threads = array('master' = NULL, 'slaves' = array()); run_query($mysqli, "SELECT 1 FROM DUAL", MYSQLND_MS_MASTER_SWITCH); $threads['master'] = $mysqli-thread_id(); run_query($mysqli, "SELECT 1 FROM DUAL", MYSQLND_MS_SLAVE_SWITCH); $threads['slaves'][$mysqli-thread_id()] = $mysqli-thread_id(); /* Verify if plugin works */ run_query($mysqli, "DROP TABLE IF EXISTS test"); if ($mysqli-thread_id != $threads['master']) printf("DROP TABLE has not been run on the master!"); run_query($mysqli, "SELECT 1 FROM DUAL"); if (!isset($threads['slaves'][$mysqli-thread_id])) printf("SELECT has not been run on a slave!");

The more slaves you have, the more complicated the thread id checking matter becomes. Do yourself a favor: start with one or two slaves. If it works with one or two slaves, why wouldn't it work with three or foura Watch out for the load balancing strategy (random, random_once (sticky), roundrobin, user) you have configured when collecting thread ids.

Statistics - mysqlnd_ms_get_stats()

Users of PDO_MySQL may want to (ab)use statistics to monitor decisions of the plugin. There are some 15 statistics telling you how many statements have been sent to the master server and the slave servers, why they have been sent there, how many connections have been opened and how often the transaction mode has been used.

mysqlnd_ms.enable=1 mysqlnd_ms.collect_statistics=1

Statistics are returned

Truncated by Planet PHP, read more at the original (another 8622 bytes)