Download: High Performance MySQL Driver

[mat206]

File Name: High Performance MySQL Driver
File Submitter: mat206
File Submitted: 27 Dec 2010
File Updated: 08 Feb 2012
File Category: Generic Tools

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:

(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' => '' )

Click here to download this file

[sadam]

Please change the hpmysql_config.php php open tag to <?php from <? as many people do not have the short tags on as default.

[sadam]

There is also a bug once a report is submitted could we have a fix please.

[mat206]

What is the submitted bug report?

[valendono]

is it working with 3.3.x ? any info ?

[Luis Manson]

is this alive?

[Well-Trained Mind Press]

Hi Matt, I love this and it is greatly needed.

I am getting an error when I visit the reports page

Call to a member function join_com_permissions() on a non-object in htdocs/admin/applications/core/sources/reportLibrary.php on line 313, referer: http://domain.com/forum/12-announcements/

[mat206]

I can't actively work on this guys but if someone wants to fork it and keep progress going they certainly may do so. This ended up being more proof of concept than anything since it lacks a ton of functionality you'd want to see in a load balancing driver.

[Well-Trained Mind Press]

Do you have a github repo for this?

[FlorinP]

Hi guys,

I was wondering if this driver works with 3.4.2 or the latest 3.4.4 (we plan to upgrade to the latest version soon).

We tried to install it but we get:

error now : PHP Fatal error: Declaration of db_driver_balancedmysql::_buildSelect() must be compatible with that of dbMain::_buildSelect() in /home/migrate/public_html/forums/ips_kernel/classDbBalancedmysql.php on line 19