High Performance MySQL Driver

[[R] NuclearGeneral]

ABOUT THIS DRIVER:

 

This driver allows you to set up your mysql servers in a master-slave configuration where you have one master that you can both read and write updates to, and several slave databases that are read-only and stay in-sync with the master server. You can then use this driver to randomly distribute read (select) queries across one or more slave databases. You can also distribute read (select) queries to the master server as well. Using a setup like this you can potentially boost performance by using more than one database server.

 

NOTE: If you do not use mysql replication this driver will not improve your performance at all, as it relies on the native IPS MySQL drivers. This driver primarily implements load distribution of queries.

 

PRIOR TO ANY APPLICATION UPGRADES OR INSTALLATIONS YOU MUST CHANGE YOUR DRIVER IN conf_global.php BACK TO 'mysql'. OTHERWISE YOU MUST RENAME ALL APPLICATION SQL QUERY FILES TO REFLECT THE balancedmysql DRIVER NAME. ONCE UPGRADES/INSTALLATIONS ARE COMPLETE YOU CAN RESUME USING THE balancedmysql DRIVER. A SPECIAL HOOK HAS BEEN INCLUDED THAT WILL PREVENT YOU FROM ACCIDENTILY MAKING INSTALLATIONS/UPGRADES WHILE 'balancedmysql' DRIVER IS ACTIVE.

 

 

Sample High Performance Setup:

http://dev.mysql.com/doc/refman/5.1/en/images/scaleout.png

(source: http://dev.mysql.com...s-scaleout.html)

 

What this script will do is randomly distribute select queries between the master and one or more slaves.

 

INSTALLATION:

 

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 /hooks/hpmysql_config.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 ( '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' => '' ));

 

 

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.

 

 

 

Step 4: Additional configuration items

 

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 /hooks/hpmysql_config.php 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' => '' )

 

View the full article IPS Resources