Page 1 of 1

MySQL Databases and 2 servers?

Posted: Thu Sep 02, 2004 2:39 am
by visionmaster
Hello,

We are planning to reprogramm our search machine and website. Since we have call agents who query the same database as the customers do, we had the idea of splitting the database to 2 servers.

The call agents can update customer data with our intern webinterface, which not only concerns tables that are for the call agents, but also concerns tables of the search machine, holding company data such as address, search words, etc.

O.k., now my questions. Since the customer himself can administer his own data, as well as the call agent (or media agent), I was thinking of doing following:
* I have 2 servers. Server 1 is responsible for the search machine and just serves customers searching for a company or for products. The MySQL database is there just for querying.
* Server 2 serves call agents, as well customers who want to change their data, reading and writing.
* Since the search machine should of course show updated data the call agent or the customer have eventually changed, the data from server 2 must be synchronized with the MySQL databese of server 1. (e.g. at midnight)

Questions:
-------------
0. Is the above described the right way?
1. What do I have to consider when planning the database structure?
2. How do I synchronize? Can I copy specific tables from one server to another using cronjobs (crontables)? Since this is absolute newland to me, how?

Any expert knowledge or experience with MySQL replication or MySQL clusters? I think replication is just thought for mirroring, or am I mistaken?

Thanks a lot.

Re: MySQL Databases and 2 servers?

Posted: Thu Sep 02, 2004 5:55 am
by Weirdan
visionmaster wrote: Questions:
-------------
0. Is the above described the right way?
I'd say so.
visionmaster wrote: 1. What do I have to consider when planning the database structure?
As long as you have one way only replication there's nothing special to consider. Just read the manual: http://dev.mysql.com/doc/mysql/en/Replication.html
visionmaster wrote: 2. How do I synchronize? Can I copy specific tables from one server to another using cronjobs (crontables)? Since this is absolute newland to me, how?
Replication is a way to go.
visionmaster wrote: I think replication is just thought for mirroring, or am I mistaken?
To some extent, yes.

Re: MySQL Databases and 2 servers?

Posted: Thu Sep 02, 2004 8:35 am
by visionmaster
Hello,
As long as you have one way only replication there's nothing special to consider. Just read the manual: http://dev.mysql.com/doc/mysql/en/Replication.html
Is replication really the right solution?

"Each slave server receives from the master the saved updates that the master has recorded in its binary log, so that the slave can execute the same updates on its copy of the data."

=> That means following: the master server is the server which is updated by inserts, updates and deletes (by customers and call agents). The search engine uses the slave server. Is that correct?

=> Or are selects from the slave server to slow, since the search machine is the interface which is used by the users and the "main business"?

=> What does this mean for my PHP programming? One script connects to the master, another script (search script) connects to the slave.

Thanks.

Re: MySQL Databases and 2 servers?

Posted: Thu Sep 02, 2004 9:58 am
by Weirdan
visionmaster wrote: Is replication really the right solution?
Replication is nearly real-time solution. It's well-known, well-tested and wide-used. I don't know if it's "right" ;)
visionmaster wrote: "Each slave server receives from the master the saved updates that the master has recorded in its binary log, so that the slave can execute the same updates on its copy of the data."

=> That means following: the master server is the server which is updated by inserts, updates and deletes (by customers and call agents). The search engine uses the slave server. Is that correct?
Yes. Moreover, you can pass your SELECTs from call agents to slave server as well.
=> What does this mean for my PHP programming? One script connects to the master, another script (search script) connects to the slave.
Usually it's done using the technique like this:
  • You have the db abstraction class/layer, don't you? ;)
  • Each instance of the db class maintains two connections, one to the slave and another to the master.
  • query method determines the type of the query it received as its argument. It passes modifying (e.g. INSERTs, DELETEs, etc) queries to master and all other to the slave.
Later you can add several slaves and implement some sort of load balancing to distribute the load of SELECTs between them.

Re: MySQL Databases and 2 servers?

Posted: Thu Sep 02, 2004 3:34 pm
by visionmaster
Hi,

Thanks for your reply.
Yes. Moreover, you can pass your SELECTs from call agents to slave server as well.
From your explanation below, this is done automatically, did I get that right?

[*]Each instance of the db class maintains two connections, one to the slave and another to the master.
[*]query method determines the type of the query it received as its argument. It passes modifying (e.g. INSERTs, DELETEs, etc) queries to master and all other to the slave.
[/list]
O.k., so all this happens in the background. As a progammer I just do my usual db-connect and db-selects, inserts, etc., the rest is done automatically.
Where can I read more about what happens in the background. Especially about passing modfying queries to the master and all other to the slave.
Later you can add several slaves and implement some sort of load balancing to distribute the load of SELECTs between them.
What exactly is meant by "add several slaves and implement load balancing"?

How can I imagine master and slave physically? Is the master on one server and the slave on another?

Thanks for help on this specific topic!

Re: MySQL Databases and 2 servers?

Posted: Fri Sep 03, 2004 5:01 am
by Weirdan
visionmaster wrote: From your explanation below, this is done automatically, did I get that right?
Exactly.
Where can I read more about what happens in the background. Especially about passing modfying queries to the master and all other to the slave.
here's simple snippet

Code: Select all

class dbmysql extends db {
    var $_slave_connection;
    var $_master_connection;
    
    function dbmysql($config) {
        $this->_slave_connection = $this->_connect($config['slave']);
        $this->_master_connection = $this->_connect($config['master']);
    }
    
    function _connect($config) {
        return mysql_connect(
                      $config['server'] . ':' . $config['port'], 
                      $config['user'], 
                      $config['password']
                   );
    }
    
    function _is_modifying($query) {
        return preg_match('/^\s*(insert|delete|update)\s+/i', $query);
    }
     
     function query($query) {
         return mysql_query(
                       $query, 
                       $this->_is_modifying($query) ? 
                                 $this->_master_connection :
                                 $this->_slave_connection
                   );
     }
}
It's oversimplified and includes only methods and member vars necessary to perform queries.
Later you can add several slaves and implement some sort of load balancing to distribute the load of SELECTs between them.
What exactly is meant by "add several slaves and implement load balancing"?

How can I imagine master and slave physically? Is the master on one server and the slave on another?
It's how replication often used. Say we have some application which issues the modifying/non-modifying queries in 20/80 proportion. To achieve the maximum performance we would set one mysql host/server/box/blade ;) to be the master and four others to be slaves connected to the master. This way each box serves only 20% of queries, busting the performance of entire 'farm' up to 5 times comparing to single db server.

More info is available here: http://dev.mysql.com/doc/mysql/en/Replication_FAQ.html
Look for 'How can I use replication to improve performance of my system?' Q.

Re: MySQL Databases and 2 servers?

Posted: Fri Sep 03, 2004 6:42 am
by visionmaster
Hello,

Thanks again for your detailed response. Really appreciate it!
here's simple snippet

Code: Select all

class dbmysql extends db {
    var $_slave_connection;
    var $_master_connection;
    
    function dbmysql($config) {
        $this->_slave_connection = $this->_connect($config['slave']);
        $this->_master_connection = $this->_connect($config['master']);
    }
    
    function _connect($config) {
        return mysql_connect(
                      $config['server'] . ':' . $config['port'], 
                      $config['user'], 
                      $config['password']
                   );
    }
    
    function _is_modifying($query) {
        return preg_match('/^\s*(insert|delete|update)\s+/i', $query);
    }
     
     function query($query) {
         return mysql_query(
                       $query, 
                       $this->_is_modifying($query) ? 
                                 $this->_master_connection :
                                 $this->_slave_connection
                   );
     }
}
It's oversimplified and includes only methods and member vars necessary to perform queries.

Hmm, I think I still don't understand the basics. Do I have to implement the master/slave thing as a PHP progammer, or is that done automatically? O.k., I have a php script that queries the database, I mean does everything stay the same? I just write my "SELECT" and that is it or do I really have to include some kind of class, like your example. I really don't understand that. :cry:
Later you can add several slaves and implement some sort of load balancing to distribute the load of SELECTs between them.
Here the same question, do I have to implement that as a PHP progammer, or is that done automatically? (Sorry if this question is "too simple"...)
It's how replication often used. Say we have some application which issues the modifying/non-modifying queries in 20/80 proportion. To achieve the maximum performance we would set one mysql host/server/box/blade ;) to be the master and four others to be slaves connected to the master. This way each box serves only 20% of queries, busting the performance of entire 'farm' up to 5 times comparing to single db server.
Thanks for the explanation, understood that one. We are indeed planning to buy a set of blades.

Thanks!

Re: MySQL Databases and 2 servers?

Posted: Fri Sep 03, 2004 9:54 am
by Weirdan
visionmaster wrote: Hmm, I think I still don't understand the basics. Do I have to implement the master/slave thing as a PHP progammer, or is that done automatically? O.k., I have a php script that queries the database, I mean does everything stay the same? I just write my "SELECT" and that is it or do I really have to include some kind of class, like your example. I really don't understand that. :cry:
My bad, I didn't provide any sample code using that class! That was the source of confusion, IMO. As a PHP programmer you don't need to implement replication on itself, great folks from MySQL AB have done that for you ;) But you have to choose which queries are going where, which to the master and which to the slave(s). That's what dbmysql class was intented for. You might ask the following:
  • Why do I need that class?. Because it provides db abstraction level.
  • Why would I need that... how did you call it? prescription? description?... that thing, at last? ;). Abstraction. I did call it abstraction. Because it's GOOD THING, imho. There are a lot of discussion on these forums on OOP, OOD and sometimes on OOA topics, you can search and read if you got free time.
  • Ok, couldn't I code that layer as a set of functions? You could. But that would require related vars ($slave_connection, $master_connection, etc) to be stored as globals, which is BAD THING (imo, of course ;) ).
  • Well, why wouldn't you just code something like this:

    Code: Select all

    $slave = mysql_connect('slave.host:3306', 'user', 'password');
      $master = mysql_connect('master.host:3306', 'anotheruser', 'anotherpassword');
       
      $result1 = mysql_query("SELECT something FROM somewhere....", $slave);
      while($row = mysql_fetch_assoc($result1)) {
          if( met_something_important($row) )
              if(!mysql_query("UPDATE important_counters SET met = met+1 WHERE thing = {$row['something']}", $master))
                    die("ALARM! ERROR! Update of important counter failed!");
      }
      mysql_free_result($result1);
      echo "IMPORTANT COUNTERS AFTER PROCESSING:<br><pre>";
      $result2 = mysql_query("SELECT * FROM important_counters", $slave);
      while($row = mysql_fetch_assoc($result2)) 
           var_dump($row);
      echo "</pre>";
      mysql_free_result($result2);
    You could. But I wouldn't suggest to code it like that.
    Well, the first reason would be "I don't like it". Secondly, what if some day you want to add two more slaves? You would need to add, say $slave1, $slave2 global vars, go through all of your code and change some of your $slave to either $slave1 or $slave2. What an awful way to spend weekend, isn't it?
  • Hey, you promised to show me some sample code using your class. Where is it? I did. Here is it:

    Code: Select all

    require_once 'That.mysql.class.php';
      $mysql =& new dbmysql(
                                 array(
                                      'master' => array(
                                          'server' = > 'master.host',
                                          'port' => 3306
                                          'user' => 'user',
                                          'password' => 'password'
                                      ),
                                      'slave' => array(
                                          'server' = > 'slave.host',
                                          'port' => 3306
                                          'user' => 'anotheruser',
                                          'password' => 'anotherpassword'
                                      )
                                  )
                              );                                 
      $result1 = $mysql->query("SELECT something FROM somewhere....");
      while($row = mysql_fetch_assoc($result1)) {
          if( met_something_important($row) )
              if(!$mysql->query("UPDATE important_counters SET met = met+1 WHERE thing = {$row['something']}"))
                    die("ALARM! ERROR! Update of important counter failed!");
      }
      mysql_free_result($result1);
      echo "IMPORTANT COUNTERS AFTER PROCESSING:<br><pre>";
      $result2 = $mysql->query("SELECT * FROM important_counters");
      while($row = mysql_fetch_assoc($result2)) 
           var_dump($row);
      echo "</pre>";
      mysql_free_result($result2);
  • Well, how that differ from what I coded? Significantly! ;) Look once more: in your code you had to specify the link to use on each query. For large script that would mean hudreds of queries. My sample class abstracts you from the fact that there are several servers involved almost totally (you need to specify connection params only once, then you just call $mysql->query() and don't care to choose where the query should be sent).
Later you can add several slaves and implement some sort of load balancing to distribute the load of SELECTs between them.
Here the same question, do I have to implement that as a PHP progammer, or is that done automatically? (Sorry if this question is "too simple"...)
From php perspective you have to implement piece(s) of code which check if given query would modify data or wouldn't. Modifying queries are sent to master. Non-modifying queries are sent to some slave (concrete slave might be choosen sequentially from the pool of available slaves, or their current load might be metered and distributed equally, or... you say me ;) )

Re: MySQL Databases and 2 servers?

Posted: Mon Sep 06, 2004 4:04 am
by visionmaster
Hello,

Thanks again for your very detailed explanation!

Code: Select all

require_once 'That.mysql.class.php';
  $mysql =& new dbmysql(
                             array(
                                  'master' => array(
                                      'server' = > 'master.host',
                                      'port' => 3306
                                      'user' => 'user',
                                      'password' => 'password'
                                  ),
                                  'slave' => array(
                                      'server' = > 'slave.host',
                                      'port' => 3306
                                      'user' => 'anotheruser',
                                      'password' => 'anotherpassword'
                                  )
                              )
                          );                                 
  $result1 = $mysql->query("SELECT something FROM somewhere....");
  while($row = mysql_fetch_assoc($result1)) {
      if( met_something_important($row) )
          if(!$mysql->query("UPDATE important_counters SET met = met+1 WHERE thing = {$row['something']}"))
                die("ALARM! ERROR! Update of important counter failed!");
  }
  mysql_free_result($result1);
  echo "IMPORTANT COUNTERS AFTER PROCESSING:<br><pre>";
  $result2 = $mysql->query("SELECT * FROM important_counters");
  while($row = mysql_fetch_assoc($result2)) 
       var_dump($row);
  echo "</pre>";
  mysql_free_result($result2);
O.k., I understood that, good example.
From php perspective you have to implement piece(s) of code which check if given query would modify data or wouldn't. Modifying queries are sent to master. Non-modifying queries are sent to some slave (concrete slave might be choosen sequentially from the pool of available slaves, or their current load might be metered and distributed equally, or... you say me ;) )
Hmm, I'm still a bit "mixed up". Could you just provide me one example code with the class and code using this class in one code snippet? Your class in a previous thread already takes care of modifying connections which are sent to the master. But what if I have more than one slave and want to disitribute the selects to e.g. 3 slaves? This also has to be implemented by the class, correct?

Thanks for your time!

Posted: Mon Sep 06, 2004 2:26 pm
by Weirdan
I'll get back with it tomorrow.

Posted: Tue Sep 07, 2004 3:10 am
by visionmaster
Weirdan wrote:I'll get back with it tomorrow.
That would be great! Your bringing me on the right way, but I do have some gaps... Thanks for your patience!

Posted: Tue Sep 07, 2004 7:48 am
by Weirdan
Ok, here we go:

Code: Select all

&lt;?php
class dbmysql /*extends db*/ { 
    var $_slave_connections = array(); 
    var $_total_slaves;
    var $_current_slave;
    var $_master_connection; 
    
    function dbmysql($config_file) { 
        $config = $this-&gt;_get_config($config_file);
        $this-&gt;_setup_slaves($config&#1111;'slaves']);
        $this-&gt;_master_connection = $this-&gt;_connect($config&#1111;'master']); 
    }
 
    function _setup_slaves($slaves) {
        if( !count($slaves) )  // reuse master connection if there are no slaves
            $this-&gt;_slave_connections&#1111;] = $this-&gt;_master_connection;

        foreach($slaves as $host)
            $this-&gt;_slave_connections&#1111;] = $this-&gt;_connect($host); 

        $this-&gt;_total_slaves = count($this-&gt;_slave_connections);
        $this-&gt;_current_slave = rand(0, $this-&gt;_total_slaves - 1);
    }
    
    function _connect($config) { 
        //return rand();
        return mysql_connect( 
                      $config&#1111;'server'] . ':' . $config&#1111;'port'], 
                      $config&#1111;'user'], 
                      $config&#1111;'password'] 
                   ); 
    }
 
    function _get_config($file) {
        $conf = parse_ini_file($file, true);
        $ret = array('master'=&gt;$conf&#1111;'master'], 'slaves'=&gt;array());
        foreach($conf as $name =&gt; $section)
            if( preg_match('/^slave_\d+$/', $name) )
                $ret&#1111;'slaves']&#1111;] = $section;
        return $ret; 
    }
    
    function _is_modifying($query) { 
        return preg_match('/^\s*(insert|delete|update)\s+/i', $query); 
    } 
    
    function _select_slave() {
        if($this-&gt;_current_slave &gt; ($this-&gt;_total_slaves - 1) ) 
            $this-&gt;_current_slave = 0;	
        return $this-&gt;_slave_connections&#1111;$this-&gt;_current_slave++];
    }
     
    function query($query) { 
        return mysql_query( 
                      $query, 
                      $this-&gt;_is_modifying($query) ?  
                                $this-&gt;_master_connection : 
                                $this-&gt;_select_slave() 
                  ); 
    } 
}
//var_dump(dbmysql::_get_config('db.ini'));
/*
$mysql = new dbmysql('db.ini');
var_dump($mysql);
for($i=0;$i&lt;30; $i++) 
   var_dump($mysql-&gt;_select_slave());
*/

?&gt;
file: db.ini

Code: Select all

&#1111;master]
host = "master.example.com"
port = 3306
user = "master_user"
password = "master_password"

&#1111;slave_1]
host = "slave1.example.com"
port = 3306
user = "slave1_user"
password = "slave1_password"

&#1111;slave_2]
host = "slave2.example.com"
port = 3306
user = "slave2_user"
password = "slave2_password"

&#1111;slave_3]
host = "slave3.example.com"
port = 3306
user = "slave3_user"
password = "slave3_password"

Code: Select all

&lt;?php
  require_once 'dbmysql.class.php';
  $mysql =&amp; new dbmysql('db.ini');                                 
  $result1 = $mysql-&gt;query("SELECT something FROM somewhere....");
  while($row = mysql_fetch_assoc($result1)) {
      if( met_something_important($row) )
          if(!$mysql-&gt;query("UPDATE important_counters SET met = met+1 WHERE thing = {$row&#1111;'something']}"))
                die("ALARM! ERROR! Update of important counter failed!");
  }
  mysql_free_result($result1);
  echo "IMPORTANT COUNTERS AFTER PROCESSING:&lt;br&gt;&lt;pre&gt;";
  $result2 = $mysql-&gt;query("SELECT * FROM important_counters");
  while($row = mysql_fetch_assoc($result2)) 
       var_dump($row);
  echo "&lt;/pre&gt;";
  mysql_free_result($result2);
?&gt;
it's basically the same script and the same class except the following:
  • class now reads its configuration from ini file
  • class now supports 'no-slaves' configuration
  • script is modified to reflect these changes
This class implements very basic algorithm to choose the slave. In constructor it selects random slave to begin with. Then on each subsequent non-modifying query it selects next slave from the array (if $_current_slave is past the end of $_slave_connections it wraps to the first slave).

Adding new slaves to pool is now made easy. Just add corresponding section to db.ini file. On next invocation your script will use that additional slave.

Posted: Wed Sep 08, 2004 12:15 pm
by visionmaster
Thanks again for your detailed response. Really appreciate your help! I will look trough your code and take my time. If I have anymore questions (I bet I do...) I will ask once more.