Archive for March, 2014

Setup a new MySQL Service on Port 3307 without touching existing database.

1. Create directories for MySQL_3307
$ sudo mkdir -p /opt/mysql_3307/{data,tmp,run,binlogs,log}
$ sudo chown mysql:mysql /opt/mysql_3307/{data,tmp,run,binlogs,log}

2. Create configuration file for MySQL_3307
$ sudo vim /etc/my_3307.cnf

# basic settings
datadir = /opt/mysql_3307/data
tmpdir = /opt/mysql_3307/tmp
socket = /opt/mysql_3307/run/mysqld.sock
port = 3307
pid-file = /opt/mysql_3307/run/

# innodb settings
default-storage-engine = INNODB
innodb_file_per_table = 1
log-bin = /opt/mysql_3307/binlogs/bin-log-mysqld
log-bin-index = /opt/mysql_3307/binlogs/bin-log-mysqld.index
innodb_data_home_dir = /opt/mysql_3307/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /opt/mysql_3307/data

# server id

# other settings
max_allowed_packet = 64M
max_connections = 1024
expire_logs_days = 14
character-set-server = utf8

log-error = /opt/mysql_3307/log/mysqld.log
pid-file = /opt/mysql_3307/run/
open-files-limit = 8192


port = 3307
socket = /opt/mysql_3307/run/mysqld.sock
default-character-set = utf8

3. Initialize MySQL_3307
$ sudo -i
# su – mysql
$ mysql_install_db –user=mysql –datadir=/opt/mysql_3307/data/ –defaults-file=/etc/my_3307.cnf
$ exit
# exit

4. Start MySQL_3307
$ sudo mysqld_safe –defaults-file=/etc/my_3307.cnf –user=mysql >/dev/null 2>&1 &

5. Stop MySQL_3307
$ sudo pkill -f “/etc/my_3307.cnf”

6. Connect to MySQL_3307
Must use mysql_3307 to connect MySQL_3307 if you use mysql client on local.
Because “-P 3307” and “–port=3307” not work, mysql client will still connect the default port 3306.
Have to connect via socket file.
On other servers, it’s ok. I searched on Google, this might be a bug.

$ alias mysql_3307=’mysql -S /opt/mysql_3307/run/mysqld.sock’
$ mysql_3307 -uroot -p

7. Create service script for MySQL_3307
$ sudo vim /etc/init.d/mysql_3307

# MySQL daemon on Port 3307 start/stop/status script.
# by Dong Guo at 2014-03-19


function check_root(){
    if [ $EUID -ne 0 ]; then
        echo "This script must be run as root" 1>&2
        exit 1

  if test -s "${PIDFILE}"; then
    read mysqld_pid < "${PIDFILE}"
    if kill -0 ${mysqld_pid} 2>/dev/null ; then
      echo "MySQL (on Port 3307) running (${mysqld_pid})"
      exit 0
      echo "MySQL (on Port 3307) is not running, but PID file exists"
      exit 1
    echo "MySQL (on Port 3307) is not running"
    exit 2

  if test -s "${PIDFILE}"; then
    read mysqld_pid < "${PIDFILE}"
    if kill -0 ${mysqld_pid} 2>/dev/null ; then
      echo "MySQL (on Port 3307) is already running (${mysqld_pid})"
      exit 0
      echo "MySQL (on Port 3307) is not running, but PID file exists"
      exit 1
    echo "Starting MySQL (on Port 3307)"
    mysqld_safe --defaults-file=${CONF} --user=mysql >/dev/null 2>&1 &

  if test -s "${PIDFILE}"; then
    read mysqld_pid < "${PIDFILE}"
    if kill -0 ${mysqld_pid} 2>/dev/null ; then
      echo "Stopping MySQL (on Port 3307)"
      if pkill -f "${CONF}" ; then
        rm ${PIDFILE}
      echo "MySQL (on Port 3307) is not running, but PID file exists"
      exit 1
    echo "MySQL (on Port 3307) is not running"
    exit 2

case "$1" in
        sleep 2
        sleep 2
        echo $"Usage: $0 {start|stop|status}"
        exit 2

$ sudo chmod +x /etc/init.d/mysql_3307
$ sudo /etc/init.d/mysql_3307 status

No Comments

BBCP Test Report

Tested 10 times between west-server1 and east-server1.

Within the same network, sometimes busy sometimes not, via the internet.
bbcp speed was more stable, and always faster than scp, mostly only spent 30% time of scp, sometimes the time was even shorter.

System Load:
During the whole transfer process:
CPU Load changed very few, only around “0.5-1”.
The value of “-/+ buffers/cache” changed very few, only around “100M”, sometimes even lower.

Bigfile and Smallfiles:
Tested the models directory(1.9G, 204 directories, 82 files) with a single bigfile(file.2g)
Single bigfile was faster, modles directory(9m25s), file.2g(6m12s), still faster than “scp -r”(24m8s)

Stop in the middle of transaction and then resume:
By default, files only appear on remote side when they are completed.
So if there was a file on remote side with the same name, it skipped with messages:

"bbcp: File /home/heydevops/file.2g already exists."

But “-a” option gives bbcp the ability to pick up where it left off, it creates a file on remote side for checkpoint informations:
[heydevops.guo@east-server1 heydevops]$ bbcp -k -a /home/heydevops/ -r -P 2 -V -f -w 9m -s 16 -i /home/heydevops/.ssh/id_rsa file.256m west-server1:/home/heydevops/
[heydevops@west-server1 ~]$ cat bbcp.

0 f 278176444449211 644 268435456 531fd087 531fcffd serverymp file.256m

[heydevops@east-server1 ~]$ bbcp -k -a /home/heydevops/ -r -P 2 -V -f -w 9m -s 16 -i /home/heydevops/.ssh/id_rsa file.256m west-server1:/home/heydevops/

bbcp: Will try to complete copying /home/heydevops/file.256m
bbcp: Appending to /home/heydevops/file.256m at offset 199233536

Command explain:
# bbcp -k -a /home/heydevops/ -r -P 2 -V -f -w 9m -s 16 -i /home/heydevops/.ssh/id_rsa file.256m west-server1:/home/heydevops/

-k keeps any partially created target files and allows full recovery after a copy failure.
-a /home/heydevops/ appends data to the end of the target file if the target is found to be incomplete due to a previously failed copy.
-r performs a recursive copy by copying all files starting at the source directory
-P 2 produces progress messages every 2 seconds.
-V produces verbose output, including detailed transfer-speed statistics.
-f forces the copy by erasing the target prior to copying the source file.
-w 9m sets the size of the disk input/output (I/O) buffers.
(window = netspeed/8*RTT = 1000Mb/8*74ms = 1000/1000/8*74 = 9.25 M)
-i specifies the name of the ssh identity file.
-s 16 sets the number of parallel network streams to 16 as suggested:

System optimization:
Increase the os default max windows size.
Otherwise the bbcp shows the autotuning may be misconfigured with given window size:

"bbcp: Target autotuning may be misconfigured; max set to 245760 bytes(240K)."

# vim /etc/sysctl.conf

net.core.rmem_max = 536870912
net.core.wmem_max = 536870912
net.core.rmem_default = 536870912
net.core.wmem_default = 536870912
net.core.optmem_max = 536870912
net.core.netdev_max_backlog = 1000000

1 Comment

Try BBCP to speed up the data transfers on internet

[heydevops@east-server1 ~]$ sudo wget -O /usr/bin/bbcp
[heydevops@east-server1 ~]$ sudo chmod +x /usr/bin/bbcp

[heydevops@west-server1 ~]$ sudo wget -O /usr/bin/bbcp
[heydevops@west-server1 ~]$ sudo chmod +x /usr/bin/bbcp

[heydevops@east-server1 ~]$ which bbcp


[heydevops@east-server1 ~]$ ssh west-server1 which bbcp


[heydevops@east-server1 ~]$ cd heydevops
[heydevops@east-server1 heydevops]$ sudo dd if=/dev/zero of=/home/heydevops/heydevops/file.2g bs=1024M count=2

2+0 records in
2+0 records out
2147483648 bytes (2.1 GB) copied, 45.9129 s, 46.8 MB/s

[heydevops@east-server1 heydevops]$ ls -lh

total 2.0G
-rw-r--r-- 1 root   root   2.0G Mar  4 06:40 file.2g

[heydevops@east-server1 heydevops]$ time bbcp -r -P 2 -V -w 8m -s 16 file.2g west-server1:/home/heydevops/heydevops/

bbcp: Window size reduced to 245760 bytes.
bbcp: Indexing files to be copied...
bbcp: Copying 0 files in 0 directories.
Source using initial send window of 18700
Target using initial recv window of 87380
bbcp: Creating /home/heydevops/heydevops/file.2g
bbcp: 140304 06:46:12  0% done; 8.5 MB/s, avg 8.5 MB/s
bbcp: 140304 06:46:14  1% done; 6.9 MB/s, avg 7.5 MB/s
bbcp: 140304 06:51:46  99% done; 7.7 MB/s, avg 6.1 MB/s
bbcp: 140304 06:51:48  99% done; 3.3 MB/s, avg 6.1 MB/s
Source cpu=3.643 (sys=3.552 usr=0.091).
File /home/heydevops/heydevops/file.2g created; 2147483648 bytes at 6.0 MB/s
48 buffers used with 0 reorders; peaking at 0.
Source using a final send window of 433840
Target cpu=15.149 (sys=14.505 usr=0.644).
Target using a final recv window of 2298624
1 file copied at effectively 6.0 MB/s

real    5m42.236s
user    0m0.104s
sys     0m3.567s

[heydevops@east-server1 heydevops]$ time scp file.2g west-server1:/home/heydevops/heydevops/

file.2g   100%   2048MB   2.1MB/s   16:06    

real    16m8.448s
user    0m43.497s
sys     0m7.548s

, ,

1 Comment

Fork me on GitHub