Remove journal from ext4 for DISK IO enhancement

Journaling Filesystem

A journaling filesystem is a filesystem that maintains a special file called a journal that is used to repair any inconsistencies that occur as the result of an improper shutdown of a computer. Such shutdowns are usually due to an interruption of the power supply or to a software problem that cannot be resolved without a rebooting.

To enhance Disk IO performance we may need to disable file system journaling. Ext4 with disabled journal is faster in comparison to ext4 with journal option and is appropriate for less critical system as MySQL replication slave database. In my case I have multiple MySQL replication slaves and I have used ext4 partition with journal disabled on data partition where MySQL datadir resides. Follow step below to disable journaling from ext4 filesystem.

Unmount the partition on which we wish to disable the journaling.

Here we are about to disable journal from the partition /data with device file /dev/sdb1 where MySQL database resides.

umount /data

Verify journaling option exists on the partition. While execution command below we can notice has_journal on Filesystem features: at 6th row.

dumpe2fs /dev/sdb1 |more

Disable journaling with the command below.

tune2fs -O ^has_journal /dev/sdb1

Check the file system status with the command. The command below will scan the file system for any errors.

fsck.ext4 -f /dev/sdb1

Check for file system journaling option. We can noticed has_journal has been disappeared from Filesystem features. 

dumpe2fs /dev/sdb1 |more

For more performance improvement add following options on /etc/fstab.

/dev/sdb1 /data ext4 defaults,noatime,nodiratime 0 0 

Now mount the partition with following option.

mount /data

Finally start mysql services.

/etc/init.d/mysql start

Read More

Parallel compression on Linux using pbzip2

Install pbzip2 in centos

Search and install pbzip2 package 

# yum search pbzip2
# yum -y install pbzip2

Now select the file to compress.

Start compression with normal tar command. 

# time tar cjvf host01.vdi.tar.bz2 host01.vdi

We can notice only Single CPU is used and load never goes beyond 1.06

Finally it is completed with following statistics.

Now compress using same file with pbzip2 with DOP 4 command

time pbzip2 -p4  host01.vdi

It is noticeable that multiple CPU is used and CPU load reached around 4.0

Compression completed with following statistics

Compression is completed around 4 times faster than normal compression and compressed size is same.

Decompress compressed file with command below

time pbzip2 -d -p4 host01.vdi.bz2

Read More

MySQL multi master replication

Environment:

Masters:

192.168.1.201 master1.localdomain master1
192.168.1.202 master2.localdomain master2

Slave:

192.168.1.203 slave.localdomain slave

Create MySQL user in master servers that will be used as mysql replication slave. Execute following commands on master server.

create user 'replicator'@'%' identified by 'password'; 

grant replication slave on *.* to 'replicator'@'%';

Setup mysql master on hosts master1 and master2 two node circular replication. Add following parameters on my.cnf of master1 and master2

Master1:

server-id = 1

log-bin = master1-bin

log-slave-updates

log-bin-index = /var/lib/mysql/log-bin.index

log-error = /var/lib/mysql/error.log

relay-log = /var/lib/mysql/relay.log

relay-log-info-file = /var/lib/mysql/relay-log.info

relay-log-index = /var/lib/mysql/relay-log.index

auto_increment_increment = 2

auto_increment_offset = 1

Master2:

server-id = 2

log-bin = master2-bin

log-slave-updates

log-bin-index = /var/lib/mysql/log-bin.index

log-error = /var/lib/mysql/error.log

relay-log = /var/lib/mysql/relay.log

relay-log-info-file = /var/lib/mysql/relay-log.info

relay-log-index = /var/lib/mysql/relay-log.index

auto_increment_increment = 2

auto_increment_offset = 2

Restart mysql services on both master servers. Login to masters as user root and execute following command.

Master1:

CHANGE MASTER TO MASTER_HOST='192.168.1.202',MASTER_USER='replicator', MASTER_PASSWORD='password', MASTER_LOG_FILE='master2-bin.000001', MASTER_LOG_POS=120;

start slave;

show slave status\G

Master2:

CHANGE MASTER TO MASTER_HOST='192.168.1.201',MASTER_USER='replicator', MASTER_PASSWORD='password', MASTER_LOG_FILE='master1-bin.000001', MASTER_LOG_POS=120;

start slave;

show slave status\G

Verify two node circular replication.

From master1 create database db1 and verify database exists on master2.

Create database db2 on master2 and verify database exists on master1.

Master1:

CREATE TABLE db1.animals (

     id MEDIUMINT NOT NULL AUTO_INCREMENT,

     name CHAR(30) NOT NULL,

     PRIMARY KEY (id)

) ;

INSERT INTO db1.animals (name) VALUES

    ('dog1'),('cat1'),('penguin1'),

    ('lax1'),('whale1'),('ostrich1');

Master2:

INSERT INTO db1.animals (name) VALUES

    ('dog'),('cat'),('penguin'),

    ('lax'),('whale'),('ostrich');

Execute following command on both master1 and master2 and verify the output.

select * from db1.animals;

Configure Slave.

Add following configuration parameters on my.cnf of slave.

server-id=3

log-bin-index = /var/lib/mysql/log-bin.index

log-error = /var/lib/mysql/error.log

relay-log = /var/lib/mysql/relay.log

relay-log-info-file = /var/lib/mysql/relay-log.info

relay-log-index = /var/lib/mysql/relay-log.index

Execute following command to configure master and you may point to any one of the master.

CHANGE MASTER TO MASTER_HOST='192.168.1.202',MASTER_USER='replicator', MASTER_PASSWORD='password', MASTER_LOG_FILE='master2-bin.000001', MASTER_LOG_POS=120;

start slave;

Execute following command on slave and verify the output.

select * from db1.animals;

Read More

Log all queries in MySQL

Create logging tables on mysql database that will hold the log.

Create your log tables on the mysql database

  CREATE TABLE `slow_log` (
   `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
                          ON UPDATE CURRENT_TIMESTAMP,
   `user_host` mediumtext NOT NULL,
   `query_time` time NOT NULL,
   `lock_time` time NOT NULL,
   `rows_sent` int(11) NOT NULL,
   `rows_examined` int(11) NOT NULL,
   `db` varchar(512) NOT NULL,
   `last_insert_id` int(11) NOT NULL,
   `insert_id` int(11) NOT NULL,
   `server_id` int(10) unsigned NOT NULL,
   `sql_text` mediumtext NOT NULL,
   `thread_id` bigint(21) unsigned NOT NULL
  ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log';

  CREATE TABLE `general_log` (
   `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
                          ON UPDATE CURRENT_TIMESTAMP,
   `user_host` mediumtext NOT NULL,
   `thread_id` bigint(21) unsigned NOT NULL,
   `server_id` int(10) unsigned NOT NULL,
   `command_type` varchar(64) NOT NULL,
   `argument` mediumtext NOT NULL
  ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log';

Enable Query logging on the database with parameter below

SET global general_log = 1;
SET global log_output = 'table';

View the log

select * from mysql.general_log;

Disable Query logging on the database

SET global general_log = 0;

Sample Report SQL

select argument,count(*) from general_log 
where lower(argument) like 'select%'
and user_host like '%192.168.1.1%'
group by argument
order by 2 desc;

Read More