How transparent does master slave load balancing support for mysqlnd have to be? Transparency, when driven to the extremes, has three limiting factory: power users not needing it, increase of messages send to MySQL, time intensive SQL monitoring and parsing. The PECL/mysqlnd_ms proof-of-concept mysqlnd plugin is almost transparent to make it easy to use. Almost because there are some design limits. Please let us know how you rate the severity of these limits. For example, can we ignore transactions? Your feedback on the previous blog post has been most helpful - thanks!

Give me four paragraphs, one minute, for the …

Are you interested in a master slave load balancing plugin for mysqlnd? It could have two operational modes. The automatic mode would operate transparent from an applications point of view. The manual mode would give you full control over all decisions. Automatic mode is for install and forget when you start with MySQL replication. Manual mode is for growing as your application evolves.

Start small…

Let’s assume you have to run a forum for a customer. You have picked, more or less randomly, the second-best PHP forum application. The customer’s website becomes popular. The forum is read-mostly and qualifies for scaling through MySQL replication. You add two MySQL slaves to your web farm. Now, how do you get the second-best PHP forum application to direct all writes to the MySQL master? Of course, the second-best application was not designed to support MySQL replication and …

Are you interested in a master slave load balancing plugin for mysqlnd? It could have two operational modes. The automatic mode would operate transparent from an applications point of view. The manual mode would give you full control over all decisions. Automatic mode is for install and forget when you start with MySQL replication. Manual mode is for growing as your application evolves.

Start small…

Let’s assume you have to run a forum for a customer. You have picked, more or less randomly, the second-best PHP forum application. The customer’s website becomes popular. The forum is read-mostly and qualifies for scaling through MySQL replication. You add two MySQL slaves to your web farm. Now, how do you get the second-best PHP forum application to direct all writes to the MySQL master? Of course, the second-best application was not designed to support MySQL replication and …

Psst, you want some client-side MySQL load balancing with and without sharding for your PHP application? PHP 5.3 has something to offer for you. It is free. It requires no to very little changes to your applications. It is open source but works with closed source applications as well. You won’t find this information in any book, because some book authors still do not cover "mysqlnd". Read on for 30+ lines of PHP to add round-robin connection load balancing and, 70+ lines of PHP to add MySQL master slave replication or sharding support to your application without changing the application. Black magic from Manmuckiel.

The goal

Psst, you want some client-side MySQL load balancing with and without sharding for your PHP application? PHP 5.3 has something to offer for you. It is free. It requires no to very little changes to your applications. It is open source but works with closed source applications as well. You won’t find this information in any book, because some book authors still do not cover "mysqlnd". Read on for 30+ lines of PHP to add round-robin connection load balancing and, 70+ lines of PHP to add MySQL master slave replication or sharding support to your application without changing the application. Black magic from Manmuckiel.

The goal

Recently I was forced to benchmark a mysqlnd plugin on Windows. X-Debug was no help. It worked flawless on Windows but it can’t tell you what goes on inside a PHP user space API call, for example, you will not know how much time mysqli_query() spends reading a result set. Very Sleepy is nice and the latest Microsoft Visual Studio profiler are probably wonderful but any of those external profiling tools did give me too fine-grained information. Also, they are external profiler which means you have to install extra software.

The mysqlnd statistics didn’t help me either. I didn’t need any aggregated values, I was curios if a certain function was the bottleneck. The solution:

• extra statistics for critical sections in the plugin using the mysqlnd statistics framework (C coding)
• dump profiling …

Recently I was forced to benchmark a mysqlnd plugin on Windows. X-Debug was no help. It worked flawless on Windows but it can’t tell you what goes on inside a PHP user space API call, for example, you will not know how much time mysqli_query() spends reading a result set. Very Sleepy is nice and the latest Microsoft Visual Studio profiler are probably wonderful but any of those external profiling tools did give me too fine-grained information. Also, they are external profiler which means you have to install extra software.

The mysqlnd statistics didn’t help me either. I didn’t need any aggregated values, I was curios if a certain function was the bottleneck. The solution:

• extra statistics for critical sections in the plugin using the mysqlnd statistics framework (C coding)
• dump profiling …

Did you ever want to see the database queries an arbitrary PHP MySQL application runs? It takes two free downloads and 62 characters to see them.

• Get PHP 5.3.4-dev
• Get PECL/mysqlnd_qc
• Add 19 characters to your configure line: --enable-mysqlnd-qc
• Add 43 characters at the end of applications source, e.g. using the auto_append_file configuration setting: var_dump(mysqlnd_qc_get_query_trace_log());
• Start your query analysis

array(329) {
  [0]=>
  array(8) {
    ["query"]=>
    string(27) "SET @@session.sql_mode = """
    ["origin"]=>
    string(1330) "#0 /home/nixnutz/Downloads/oxid/core/adodblite/adodbSQL_drivers/mysql/mysql_driver.inc(352): mysql_query('SET @@session.s...', Resource id #26)
#1 …

Did you ever want to see the database queries an arbitrary PHP MySQL application runs? It takes two free downloads and 62 characters to see them.

• Get PHP 5.3.4-dev
• Get PECL/mysqlnd_qc
• Add 19 characters to your configure line: --enable-mysqlnd-qc
• Add 43 characters at the end of applications source, e.g. using the auto_append_file configuration setting: var_dump(mysqlnd_qc_get_query_trace_log());
• Start your query analysis

array(329) {
  [0]=>
  array(8) {
    ["query"]=>
    string(27) "SET @@session.sql_mode = """
    ["origin"]=>
    string(1330) "#0 /home/nixnutz/Downloads/oxid/core/adodblite/adodbSQL_drivers/mysql/mysql_driver.inc(352): mysql_query('SET @@session.s...', Resource id #26)
#1 …

How amusing: an ancient write-up on the first PHP Kongress in 2000 edition written by one who later became a mentor of the autor of phpOpenTracker. How boring: a silver guy talking at the 10th aniversary of the conference about some 60% performace benefit for Oxid eShop, an example of a modern award-winning software, if using the mysqlnd query cache plugin (PECL/mysqlnd_qc). How confusing: the same silver guy calls his own benchmarks irrelevant and faulty.

Award-winning technology: Oxid loves the query cache

View more presentations on …