[community.invisionpower.com - pliki] High Performance MySQL Driver

[NewsBot]

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.

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

Pobierz plik