mysql easy replication

MySQL offers quite simple installation of replication // we need at least two servers that sees each other over tcp/ip and a bit of configuration

on master server (one that we write data in) this is minimal (a bit more) required configuration:

[mysqld]

server-id          = 1
log_bin            = /var/log/mysql/mysql-bin.log
# http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_expire_logs_days

expire_logs_days   = 10 

# http://dev.mysql.com/doc/refman/5.0/en/replication-options-binary-log.html#sysvar_max_binlog_size

max_binlog_size    = 100M
binlog_do_db       = my_database

the last line indicates that we would like to replicate only one databse on master server

after that we need to grant replication previlegies to slave server with command

GRANT REPLICATION SLAVE ON *.* TO 'slave-user'@'%' IDENTIFIED BY 'slave-pass';

FLUSH PRIVILEGES; 

we could also specify exact ip of slave server, but i prefere setting this into iptables - firehol to be exact is the tool for lazy linux admins

interface eth0 inet
    group with src "ip.of.slave.server"
    server "mysql" accept
    group end

next we need to configure slave server 

 

[mysqld]

server-id       = 2
relay-log-index = slave-relay-bin.index
relay-log       = slave-relay-bin

we are almost all set -- we just need to get current database state to slave server and start replication

1. on master we need to get current "log and position"

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.07 sec)

 
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000034 |     1539 | my_databse   |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
 

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

2. we make export of master database and import it on slave (i use good old phpMyAdmin for the job)

3. and we set and start replication on slave

mysql> CHANGE MASTER TO MASTER_HOST='ip.of.master.server', MASTER_PORT=3306, MASTER_USER='slave-user', MASTER_PASSWORD='slave-pass', MASTER_LOG_FILE='mysql-bin.000034', MASTER_LOG_POS=1539;

mysql> START SLAVE;
 

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: ip.of.master.server
                  Master_User: master-user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000034
          Read_Master_Log_Pos: 2060
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 774
        Relay_Master_Log_File: mysql-bin.000034
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table: mysql.%,information_schema.%,performance_schema.%
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 2060
              Relay_Log_Space: 930
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)

connect to oracle

Let say we need to connect to Oracle DB form our windows (64 bit) php server. Linux fan boy would think tkat apt-get php module (on windows most modules are shipped with php installation ) and uncomment form php.ini would do the trick - but not with windows and oracle.

uncomment this line


extension=c:/php54/ext/php_oci8_11g.dll  ; Use with Oracle 11gR2 Instant Client

will probably give u some sort of error

[Fri May 25 09:09:03 2012] [error] [client 192.168.1.194] PHP Warning:  PHP Startup: Unable to load dynamic library 'c:/php54/ext/php_oci8_11g.dll' - %1 is not a valid Win32 application.\r

due fact this extension needs oracle client to be installed as well. So next step would be installing of proper (32bit!) oracle client form http://www.oracle.com/technetwork/topics/winsoft-085727.html (i usually go for smallest package in this case *Instant Client Package - Basic Lite: Smaller version of the Basic, with only English error messages and Unicode, ASCII, and Western European character set support (10.2 only) )

After that we need to put the installation folder to path variable

After apache  restart phpinfo() should output some oci output

The following example will test the connection and do some trivial query

<?php
$c = oci_connect('*username*', '*password*', '//192.168.1.113:1521/ORCL');
$sql  = "SELECT ID, USERNAME FROM USERS";
$stmt = oci_parse($c, $sql);
oci_execute($stmt, OCI_DEFAULT);

 while ($row = oci_fetch_array($stmt, OCI_ASSOC)) {
    print_r($row);
}

debug PHP the JS way

We use CI (http://codeigniter.com/) in almost all our projects and since in JS got console.log our PHP debug/output was lagging behind with it's own print_r. Every good thing is eventually dumped by another new good thing so after lookin around for debug solutions in PHP and the fact, we almost all time share browser screen with firebug window FirePHP (http://www.firephp.org/) was the reasonable path.

For installation we just need to copy all files form archive to /ci/application/libraries/firephp_library and wrapper class looks something like

<?php if (!defined('BASEPATH')) exit('No direct script access allowed');
require "firephp_library/FirePHP.class.php";
class Firephp_library extends FirePHP {
    function __construct() {
    parent::init();
}
}

After that we can use it in controller:

//load library

$this->load->library('Firephp_library');

Then i took it for a test drive...

Stuck in the moment

Sometimes php-cgi.exe process get stuck -- and eventually over some period of time, when number of prcesses get over critical point (i don't have clue what would that be) whole apache-php stack freezes.
That happened around once per month, but it is still unpleasant experience. Someone needed to log into server stop apache kill all php-cgi.exe processes and restart apache.

After a bit googling we write a tiny script and schedule it once a day to kill all php-cgi.exe processes so now can spawn and since than no more incidents like this occurred anymore - not bad (i know if someone process is in the middle of work it gets killed but its small price to pay)

C:\WINDOWS\system32\taskkill.exe /IM php-cgi.exe /F

Do the mongo with me

It's been round two years since our project took off, many things has been done (will be covered in next posts) but somehow i feel next step in pursuit of speed will be migrating some of our data tables to noDB land. I have read lot's about this topic and somehow decided to try http://www.mongodb.org/ at first. This decision was made on ease of use (in terms of instalation) and good support in php (remeber php on windows). Installation was very simple although i had to open port 11371 on firewall to get 10gen gpg key into my apt repositorie.

After that I was ready to go.

We have very special table in our DB called server_cache. It's actually key value table with some 20k lines and 50Mb of data, holding hashes for hotel/room/date records, filtered and sorted by some 10 or something parameters. We populate this table with users requests if record is not in table and just return hashes if desired combination already exists.

So i populate mongoDB collection with all records from MySQL and made a little script that gets 1000 random (predefined in script) hashes form this table.

I start to measure results with ApacheBench tool:

I stared with 100 requests and one thread, slowly increasing number of requests. Server "Time taken for test" increased linear and MySQL was lagging behind by twice the time mongoDB needed to complete the test.
Then i started increasing threads where mongoDB showed all of it's potential and leave MySQL far behind.

With such a fabulous preformace by mongoDB im shure we will implement in production soon.

Technical spec of our DB server:

bl4z@ubuntu:~$ uname -a
Linux ubuntu 2.6.32-38-server #83-Ubuntu SMP Wed Jan 4 11:26:59 UTC 2012 x86_64 GNU/Linux
bl4z@ubuntu:~$ mysql --version
mysql  Ver 14.14 Distrib 5.1.62, for debian-linux-gnu (x86_64) using readline 6.1
bl4z@ubuntu:~$ mongo --version
MongoDB shell version: 2.0.4

Background

We start new project on our beloved LAMP stack, only this time it was WAMP // W was the server waiting for us at clients "data-room" so no tools that we are using daily (apt, terminal, putty, winscp, cron, ps -aux,...) just GUI, RDC and Windows tools...

The server now serving up to 10k visitors per day at peaks we get 50 active users with decent speed - but it wasn't always so. ill try to write down optimisations we already did, and future plans to serve visitors in nice manner.

Our database at the moment is round 1.2 GB with 4 million records in total. At the beginning everything was running on same server with Xeon X3210 CPU and 4GB RAM, Windows server 2003 X64 SP2.