MySQL Driver for Read/Write Splitting

[mat206]

I got a little bored and figured I'd hack up a MySQL database driver that does read/write splitting. The nice thing about this driver is that it shouldn't break as the current mysql drivers are updated. I'm not sure how well it works but if you want to try it out and give me some feedback it will help me to improve it. From my own experiments I didn't seem to come across any problems.

The idea for this driver is that you can set up your mysql in a master-slave configuration where you have one master that you can both read and write updates to, and copies of the data are sent out to the slave databases.

Sample Setup:
Mysql Master Server
+ Slave #1
+ Slave #2
+ Slave #3

What this script will do is randomly distribute select queries between the master and three slaves. If there is demand later I'll add a weighted random distribution, so you can shift the balancing in favor of the more powerful servers rather than purely equally. First I need to know if this works well and where the bugs are if you have them.


Step 1: Upload the contents of the attached zip file to your server..


Step 2: In your conf_global.php set your sql driver to 'balancedmysql' and keep your existing mysql settings.

$INFO['sql_driver'] = 'balancedmysql';

Step 3: In your conf_global.php file add the following lines toward the bottom to define your slave databases (make sure you keep the driver set to mysql!) Be sure to fill in the correct host, username, and password

$INFO['sql_slaves'] = Array ( // First read slave details Array ( 'sql_driver' => 'mysql', 'sql_host' => 'localhost', 'sql_database' => '', 'sql_user' => '', 'sql_pass' => '', 'sql_tbl_prefix' => '' ) );

If more than one slave:

$INFO['sql_slaves'] = Array ( // First read slave details Array ( 'sql_driver' => 'mysql', 'sql_host' => 'localhost', 'sql_database' => '', 'sql_user' => '', 'sql_pass' => '', 'sql_tbl_prefix' => '' ), // Second read slave details Array ( 'sql_driver' => 'mysql', 'sql_host' => 'localhost', 'sql_database' => '', 'sql_user' => '', 'sql_pass' => '', 'sql_tbl_prefix' => '' ) );

Step 4: Done! Go test and tell me your results and if you see any problems popping up.

[mat206]

Updated - Noticed a small bug that may lead to non select queries being identified as select if they follow a select query.

Use the attached instead.

[mat206]

Feature Addition: Weighted Random Distribution


(source: http://dev.mysql.com/doc/refman/5.1/en/replication-solutions-scaleout.html)

Yet another update, and this one adds some cool features that I think are pretty useful to make configuration easier for those setting up load balanced web frontends (or for those looking for finer grained control over slave server selection).

First off, the setup of slaves in conf_global.php is different. Note the addition of two more fields for setting up slaves.. "name" and "weight". The name field (not optional) is a key that will identify the slave mysql server in the database driver, and the weight field is a positive INTEGER that is used to control how often the particular slave is chosen by random selection.

For example, if slave "apple" has weight = 1 and slave "banana" has weight = 9 then think of it like putting 1 apple and 9 bananas in a basket. Choosing the server will be as simple as drawing one item from the basket.

This new addition will allow you to route all select queries to one or more slaves or even balance randomly among all available servers.

$INFO['sql_slaves'] = Array ( // First read slave details Array ( 'name' => 'slave1', // REQUIRED!!!! 'weight' => 5, // (OPTIONAL) 'sql_driver' => 'mysql', 'sql_host' => 'localhost', 'sql_database' => '', 'sql_user' => '', 'sql_pass' => '', 'sql_tbl_prefix' => '' ), // Second read slave details Array ( 'name' => 'slave2', // REQUIRED!!!! 'weight' => 3, // (OPTIONAL) 'sql_driver' => 'mysql', 'sql_host' => 'localhost', 'sql_database' => '', 'sql_user' => '', 'sql_pass' => '', 'sql_tbl_prefix' => '' ) );

Now some other very important configuration items. The first configuration item is 'read_selection', which allows you to define what servers to balance traffic across. Note that your master server will ALWAYS be used for deletions, inserts, create tables, etc.

$INFO['read_selection'] Possible Values

$INFO['read_selection'] = "random";  // When issuing read (select) queries, choose a random server from among all slaves

-or-

$INFO['read_selection'] = "randomslave";  // When issuing read (select) queries, choose a random server from among all slaves

-or-

$INFO['read_selection'] = "master|slave2|slave3";  // Choose randomly between the master server, slave2, and slave3.  These are the names you have given to each of the slaves when setting them up in $INFO['sql_slaves'].  Note that "master" is reserved

$INFO['read_weighted'] (OPTIONAL)

$INFO['read_weighted'] = true;  // Apply weights when choosing a random read server

-or-

$INFO['read_weighted'] = false;  // Do not apply weights when choosing a random read server (any configured weights will be ignored and a purely random server will be chosen)

$INFO['master_weight'] (OPTIONAL)

$INFO['master_weight'] = 5;  // Apply a weight to the master server

So putting this all together, your conf_global might look like this:

$INFO['read_selection'] = "randomslave"; $INFO['read_weighted'] = true; $INFO['master_weight'] = 2; // this won't do anything with 'randomslave' enabled // First read slave details Array ( 'name' => 'slave1', // REQUIRED!!!! 'weight' => 5, // (OPTIONAL) 'sql_driver' => 'mysql', 'sql_host' => 'localhost', 'sql_database' => '', 'sql_user' => '', 'sql_pass' => '', 'sql_tbl_prefix' => '' ), // Second read slave details Array ( 'name' => 'slave2', // REQUIRED!!!! 'weight' => 3, // (OPTIONAL) 'sql_driver' => 'mysql', 'sql_host' => 'localhost', 'sql_database' => '', 'sql_user' => '', 'sql_pass' => '', 'sql_tbl_prefix' => '' )

[mat206]

This driver along with bug fixes and improvements has been uploaded to the IPS Marketplace and is on sale. Your purchases will make it possible for me to devote more time to refining the driver. The price is set at $75 because I don't think this is one of those high volume selling products.

http://community.invisionpower.com/files/file/3839-high-performance-mysql-driver/

[Chillyware]

Thanks for the information,

:thumbsup:

[mat206]

This driver along with bug fixes and improvements has been uploaded to the IPS Marketplace and is on sale. Your purchases will make it possible for me to devote more time to refining the driver. The price is set at $75 because I don't think this is one of those high volume selling products.

http://community.invisionpower.com/files/file/3839-high-performance-mysql-driver/

IPS rejected the modification from their store because it requires modification of conf_global.php. I'll keep you posted if you want the updated version.

[mat206]

Do not use the version posted earlier. I have made the new version available in the IPS Marketplace here: http://community.invisionpower.com/files/file/3841-high-performance-mysql-driver/

All future updates will be done to that driver. I hope a few people purchase it and find it useful so I can continue to add higher end performance features. =)

[Zhana]

I have a vps with 1 mysql server running. What's the benefit of it if I buy it? I'll buy if there's any benefit.


Thank you.

[mat206]

I have a vps with 1 mysql server running. What's the benefit of it if I buy it? I'll buy if there's any benefit.

Thank you.

Nope, no benefit to you so save the money. This was primarily made for much larger sites that can't run everything adequately on a single database server. If you were to purchase a second vps, network the two together, and then run TWO MySQL servers in a Master-Slave configuration then I'd say the driver would be a good candidate for you.

The benefit to you would be that database load could be distributed across two mysql servers rather than just one.

[Zhana]

Ok I might buy another vps soon. What about Ram usage?


Thank you.

[Chillyware]

You can also apply at the server level, which unfortunately I can not test.

[mat206]

Ok I might buy another vps soon. What about Ram usage?

Thank you.

The Ram usage is a big variable and depends how much load you are under, whether you are using MyISAM or InnoDB, etc. So there is no exact figure as to how much your database server should have.