How to clear old mysql bin log files in mysql 5.7

Advertisements

We have seen how to do mysql master slave replication in one of our previous post. Once we setup mysql server as master with traditional replication, it will start creating bin-logs. This will go on and on, depending on the transactions happening on you database it may eat all your storage space.

One day I checked the disk usage of one of our mysql instance and found that 82% of disk is utilized!

[root@mysql mysql]# df -hT
Filesystem           Type   Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root
                            ext4    50G   39G  8.6G  82% /
tmpfs                   tmpfs  3.0G     0  3.0G   0% /dev/shm
/dev/xvda1           ext4   477M   55M  398M  12% /boot
/dev/mapper/VolGroup-lv_home
                           ext4    94G  642M   89G   1% /home

You can check in you master mysql console which is the current bin-log file.
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000089 |    25682 |              | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+

Then I checked the size of /var/lib/mysql and found a lot of bin-log files. When I checked the master status I found that the current bin file is mysql-bin.000089. So I can delete the files till that. Or the files till the last week.

And you can delete all the old bin-log files upto the latest one

mysql> PURGE BINARY LOGS TO 'mysql-bin.000075';
Query OK, 0 rows affected (0.91 sec)

You can actually automate this. You need to specify the expire log days variable in my.cnf of the master mysql server.

for example.
vim /etc/my.cnf
under [mysqld] add
expire_logs_days=7

Restart the mysql service and you are done.

How to set up Master Slave replication in mysql 5.7

Advertisements

We need to keep our data safe so we do the database replication. In mysql the most common replication is Master-Slave replication. In this post we will see how we can setup a mysql server as master and another one as slave. We are using Mysql version 5.7 in this example on Centos Linux 6.8.

We need two systems

1. Master
IP : 172.16.0.50
2. Slave
IP : 172.16.0.60

Configuring Master:
Install mysql community server version from mysql website and edit the my.cnf file as below
vim /etc/my.cnf
bind-address = 0.0.0.0
server-id = 1
log-bin = mysql-bin
binlog-ignore-db =mysql

innodb_flush_log_at_trx_commit=1
sync_binlog=1
expire_logs_days=7

Restart the mysql server.
/etc/init.d/mysqld restart

Once the mysql-server is running, you can check the status of the master with the following command.
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000089 |    35626 |              | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Configuring slave:

Install mysql community server version from mysql website and edit the my.cnf file as below
vim /etc/my.cnf
bind-address = 0.0.0.0
server-id = 2
relay-log=mysqld-relay-bin

restart the mysql server and in the mysql console, set the details of the Master server from where client has to receive data.

mysql> CHANGE MASTER TO  MASTER_HOST='172.16.0.50',MASTER_USER='replication_user', MASTER_PASSWORD='abcdefgh', MASTER_LOG_FILE='mysql-bin.000089', MASTER_LOG_POS=23694;

If the slave service is already running then you will get the following error.
ERROR 3021 (HY000): This operation cannot be performed with a running slave io thread; run STOP SLAVE IO_THREAD FOR CHANNEL '' first.


Start the slave, using the --skip-slave-start option so that replication does not start.

Or you can use the command
mysql>STOP SLAVE;

Then once the master details are set, you can start slave service.
 mysql>START SLAVE;

Once the Slave is running and receiving the data from the master, you can see the slave status as follows.

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.0.50
                  Master_User: replication_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000089
          Read_Master_Log_Pos: 35626
               Relay_Log_File: mysqld-relay-bin.000168
                Relay_Log_Pos: 7186
        Relay_Master_Log_File: mysql-bin.000089
             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:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 35626
              Relay_Log_Space: 9874
              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
                  Master_UUID: a7e33c46-3463-11e5-a325-2e092b9265bc
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

mysql>

In the master server, you can see how many slaves are connected to that by issuing the following command.

mysql> SHOW SLAVE HOSTS;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|         2 |      | 3306 |         1 | c18cf848-2efa-11e6-8534-ee8445cdbbe1 |
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)


References:
0. https://dev.mysql.com/doc/refman/5.7/en/replication.html
1. https://dev.mysql.com/doc/refman/5.7/en/replication-howto-masterbaseconfig.html
2. https://dev.mysql.com/doc/refman/5.7/en/replication-howto-masterstatus.html
3. https://dev.mysql.com/doc/refman/5.7/en/replication-setup-slaves.html
4. https://dev.mysql.com/doc/refman/5.7/en/replication-administration-status.html

checking the disk performance using iostat

Advertisements

How do we know the disk is creating the issue? Like so much write and reads are happening and disks are not able to perform according to the needs? We can check this with top and iostat command.

First try with top and check whether there is a wait for io devices.

#top -c

top - 11:58:53 up 16 days, 18:29,  1 user,  load average: 0.08, 0.13, 0.09
Tasks: 124 total,   1 running, 123 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.2%us,    0.2%sy,    0.0%ni, 98.2%id,  1.4%wa,  0.0%hi,  0.1%si,  0.0%st
Mem:   3711248k total,  3562888k used,   148360k free,   183332k buffers
Swap:  3850236k total,   817960k used,  3032276k free,   146796k cached

1.4% is fine. But if you have high I/O wait, then we need to check what is causing the issue.

Now when we run the iostat command.

[root@main ~]# iostat  -x 1
Linux 2.6.32-642.3.1.el6.x86_64 (main)     01/16/2017     _x86_64_    (6 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
                   0.39      0.00    0.54       0.80       0.00    98.27

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               1.69     6.66    0.28    1.06    16.99    61.73    58.92     0.03   19.46    0.82   24.29   9.81   1.31
sdb               0.00   142.17    4.73   16.41  1208.15  1268.67   117.15     0.02    0.85    0.20    1.03   1.35   2.85
dm-0              0.00     0.00    0.03    5.49     1.51    43.92     8.23     0.15   26.35    6.36   26.46   2.28   1.26
dm-1              0.00     0.00    1.94    2.23    15.49    17.80     8.00     0.07   17.13    0.38   31.70   0.12   0.05

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
                  0.17      0.00      0.33     0.00       0.00      99.50

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0.00    19.00    0.00    3.00     0.00   176.00    58.67     0.06   20.67    0.00   20.67  13.33   4.00
sdb               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
dm-0              0.00     0.00    0.00   22.00     0.00   176.00     8.00     0.39   17.77    0.00   17.77   1.82   4.00
dm-1              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00

Installation usage and understanding of nicstat command

Advertisements

A lot of times we need to check whether its the network causing the bottleneck. There are a lot of tools like iftop to check this. nicstat is one of the nice tools to check the network performance. In this post we will see how to install and use nicstat command. This example is executed on a centos linux system.

Download the nicstat tar file.
wget https://downloads.sourceforge.net/project/nicstat/nicstat-1.95.tar.gz

Untar the downloaded file.
tar xvzf nicstat-1.95.tar.gz

Go inside the extracted nicstat directory.
cd nicstat-1.95

Check the Readme file for the instructions.
cat README.txt

Compile
mv Makefile.Linux Makefile
make

If your system is 64bit, then you will get the following error.
[root@sysadmin nicstat-1.95]# make
gcc -O3 -m32    nicstat.c   -o nicstat
In file included from /usr/include/features.h:385,
                 from /usr/include/stdio.h:28,
                 from nicstat.c:33:
/usr/include/gnu/stubs.h:7:27: error: gnu/stubs-32.h: No such file or directory
make: *** [nicstat] Error 1
[root@sysadmin nicstat-1.95]#

Then you need to install the following packages.
yum -y install glibc-devel.i686 glibc-devel

On some systems, even if you install glibc, the following error will come.

 [root@main nicstat-1.95]# make
gcc -O3 -m32    nicstat.c   -o nicstat
/usr/bin/ld: skipping incompatible /usr/lib/gcc/x86_64-redhat-linux/4.4.7/libgcc_s.so when searching for -lgcc_s
/usr/bin/ld: skipping incompatible /usr/lib/gcc/x86_64-redhat-linux/4.4.7/libgcc_s.so when searching for -lgcc_s
/usr/bin/ld: cannot find -lgcc_s
collect2: ld returned 1 exit status
make: *** [nicstat] Error 1
[root@main nicstat-1.95]

You have to install the following packages to resolve this.
yum install libstdc++-devel.i686

Once all the required packages are installed, compile and install.
[root@sysadmin nicstat-1.95]# make
gcc -O3 -m32    nicstat.c   -o nicstat
mv nicstat `./nicstat.sh --bin-name`
[root@sysadmin nicstat-1.95]# make install
gcc -O3 -m32    nicstat.c   -o nicstat
sudo install -o root -g root -m 4511 `./nicstat.sh --bin-name` /usr/local/bin/nicstat
sudo install -o bin -g bin -m 555 enicstat /usr/local/bin
sudo install -o bin -g bin -m 444 nicstat.1 /usr/local/share/man/man1/nicstat.1


Now we can run the command.
[root@sysadmin nicstat-1.95]# nicstat
    Time      Int   rKB/s   wKB/s   rPk/s   wPk/s    rAvs    wAvs %Util    Sat
14:46:58       lo    0.00    0.00    0.00    0.00   80.58   80.58  0.00   0.00
14:46:58     eth0   13.27    0.83   13.95    8.32   974.1   102.3  0.12   0.00

To see the stats of a particular Ethernet interface
[root@sysadmin nicstat-1.95]# nicstat -i eth0
    Time      Int   rKB/s   wKB/s   rPk/s   wPk/s    rAvs    wAvs %Util    Sat
14:47:05     eth0   13.26    0.83   13.94    8.31   973.9   102.2  0.12   0.00
[root@sysadmin nicstat-1.95]#


Use -x extended output
[root@main nicstat-1.95]# nicstat -i eth2 -x
15:00:28      RdKB    WrKB   RdPkt   WrPkt   IErr  OErr  Coll  NoCP Defer  %Util
eth2        2078.2  2289.4  2413.0  1868.8   0.00  0.00  0.00  0.00  0.00   2.54

To get the summary
[root@main nicstat-1.95]# nicstat -i eth2 -s
    Time      Int          rKB/s          wKB/s
15:01:13     eth2       2078.175       2289.413

To repeat the summary in every 2 seconds for 5 times.
[root@main nicstat-1.95]# nicstat -i eth2 -s 2 5
    Time      Int          rKB/s          wKB/s
15:01:36     eth2       2078.173       2289.411
15:01:38     eth2       1088.889       1641.253
15:01:41     eth2       1065.228       1353.393
15:01:43     eth2        943.999        952.306
15:01:44     eth2        855.518       1361.279



To show TCP statistics

[root@main nicstat-1.95]# nicstat -t
15:09:12    InKB   OutKB   InSeg  OutSeg Reset  AttF %ReTX InConn OutCon Drops
TCP         0.00    0.00  1252.8   980.8  0.05  0.28 0.000   12.4   0.52  0.00

To show UDP statistics
[root@main nicstat-1.95]# nicstat -u
15:09:48                    InDG   OutDG     InErr  OutErr
UDP                         0.59    0.59      0.00    0.00

Distributing interrupts of network controller to all cpus

Advertisements

When we use PCI network card, for eg 10g fiber NIC, it will have more than one channels. Our Server will have many CPUs also. If all the channels of the NIC is interrupting only one CPU, there will be huge stress on the single CPU and it will decrease the performance of network, server etc.

The best way to handle this distribute the interrupts to all the cpus available in the system. In this post we will see how to do it.

I have one PCI Fiber NIC, eth2.

[root@main scripts]# ifconfig
eth2      Link encap:Ethernet  HWaddr 14:02:EC:6C:5A:XX
          inet addr:172.16.0.30  Bcast:172.16.0.127  Mask:255.255.255.128
          inet6 addr: fe80::1602:ecff:fe6c:5a68/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:23985923006 errors:13425 dropped:0 overruns:0 frame:13425
          TX packets:18582827575 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:21135762796818 (19.2 TiB)  TX bytes:23298305448313 (21.1 TiB)

Initially, we can see that all the channels are interrupting singe cpu CPU0
[root@main ~]# cat /proc/interrupts
           CPU0       CPU1       CPU2       CPU3       CPU4       CPU5
 75: 1937793764          0          0          0          0          0  IR-PCI-MSI-edge      eth2-TxRx-0
 76: 1033638701          0          0          0          0          0  IR-PCI-MSI-edge      eth2-TxRx-1
 77:  242534932           0          0          0          0          0  IR-PCI-MSI-edge      eth2-TxRx-2
 78:  100181128           0          0          0          0          0  IR-PCI-MSI-edge      eth2-TxRx-3
 79:   64797499            0          0          0          0          0  IR-PCI-MSI-edge      eth2-TxRx-4
 80:   50919167            0          0          0          0          0  IR-PCI-MSI-edge      eth2-TxRx-5
 81:      15920               0          0          0          0          0  IR-PCI-MSI-edge      eth2

Now we are running the set irq affinity script with eth2 as argument. You can get this script from intel websites. 

[root@main scripts]# ./set_irq_affinity eth2

IFACE CORE MASK -> FILE

=======================

eth2 0 1 -> /proc/irq/75/smp_affinity

eth2 1 2 -> /proc/irq/76/smp_affinity

eth2 2 4 -> /proc/irq/77/smp_affinity

eth2 3 8 -> /proc/irq/78/smp_affinity

eth2 4 10 -> /proc/irq/79/smp_affinity

eth2 5 20 -> /proc/irq/80/smp_affinity

Now we can see that the new interrupts are distributed over the cpus.

[root@main scripts]# cat /proc/interrupts | grep eth2

           CPU0       CPU1       CPU2       CPU3       CPU4       CPU5

 75: 1938401156          0          0          0          0          0   IR-PCI-MSI-edge      eth2-TxRx-0

 76: 1033645576        416        0          0          0          0   IR-PCI-MSI-edge      eth2-TxRx-1

 77:   242536923          0         45         0          0          0   IR-PCI-MSI-edge      eth2-TxRx-2

 78:   100182754          0          0         36         0          0   IR-PCI-MSI-edge      eth2-TxRx-3

 79:     64799086          0          0          0         38         0   IR-PCI-MSI-edge      eth2-TxRx-4

 80:     50920844          0          0          0          0         41  IR-PCI-MSI-edge      eth2-TxRx-5

 81:           15920          0          0          0          0          0   IR-PCI-MSI-edge      eth2

[root@main scripts]# cat /proc/interrupts | grep eth2

           CPU0       CPU1       CPU2       CPU3       CPU4       CPU5

 75: 1938413752          0          0          0          0          0    IR-PCI-MSI-edge      eth2-TxRx-0

 76: 1033645576        597        0          0          0          0    IR-PCI-MSI-edge      eth2-TxRx-1

 77:  242536923          0       1358        0          0          0    IR-PCI-MSI-edge      eth2-TxRx-2

 78:  100182754          0          0         78          0          0    IR-PCI-MSI-edge      eth2-TxRx-3

 79:    64799086          0          0           0         70         0    IR-PCI-MSI-edge      eth2-TxRx-4

 80:    50920844          0          0           0          0        115  IR-PCI-MSI-edge      eth2-TxRx-5

 81:          15920          0          0           0          0          0    IR-PCI-MSI-edge      eth2

Understanding Linux vmstat command

Advertisements

vmstat is a linux/unix performance analysis tool. It gives you information about the processors, memory and possible bottlenecks. Lets see how to use and what the output indicates.

usage:
[root@main ~]# vmstat
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 0  0 11932960 377768 203520 64211448    1    2   108   117    0    0  0  1 98  1  0   
[root@main ~]#

If you give vmstart with interval as first argument and number of repetitions as second, output will be like as below.
[root@main ~]# vmstat 1 5
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 0  0 11932564 433468 203528 64211756    1    2   108   117    0    0  0  1 98  1  0   
 0  0 11932564 433576 203528 64211852    0    0     0   112 1634 3199  0  0 99  1  0   
 0  0 11932564 433576 203528 64211856    0    0     0     0 2148 3216  0  0 100  0  0   
 0  0 11932564 433576 203528 64211856    0    0     0     0 1810 3100  0  0 100  0  0   
 0  0 11932564 433576 203528 64211856    0    0     0     0 1415 3014  0  0 100  0  0   
[root@main ~]#

If you give only interval, it will give output in specified interval for infinate times.
[root@main ~]# vmstat 1
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 0  0 11932328 433096 203536 64212164    1    2   108   117    0    0  0  1 98  1  0   
 0  0 11932328 432948 203536 64212164    0    0     0    32 1962 3203  0  0 99  1  0   
 0  0 11932328 433072 203536 64212164    0    0     0     0 1408 2983  0  0 100  0  0   
 0  0 11932328 432824 203536 64212164    0    0     0     0 1731 2987  0  0 100  0  0   
 0  0 11932328 432824 203536 64212164    0    0     0     0 1407 2931  0  0 100  0  0   
 1  0 11932308 432824 203536 64212184    0    0     0    76 1925 3145  0  0 99  0  0   
 0  0 11932308 432912 203536 64212192    0    0     0     0 1461 2996  0  0 100  0  0   
 0  0 11932308 432912 203536 64212192    0    0     0     0 1807 3073  0  0 100  0  0   
 0  0 11932308 432536 203536 64212192    0    0     0     0 1597 2941  0  0 100  0  0   
 0  0 11932308 432520 203536 64212192    0    0     0     0 1640 3012  0  0 99  0  0   
^C
[root@main ~]#

Fields explained as follows:
Procs:
r : The number of processes waiting for run time or placed in run queue or are already executing.

b : The number of processes in uninterruptible sleep. (b=blocked queue, waiting for resource (e.g. filesystem I/O blocked, inode lock))

If runnable threads (r) divided by the number of CPU is greater than one -> possible CPU bottleneck

Memory:

swpd: shows how many blocks are swapped out to disk (paged). The amount of Virtual memory used.
free: The amount of Idle Memory

buff: Memory used as buffers, like before/after I/O operations
cache: Memory used as cache by the Operating System

Swap:

si: Amount of memory swapped in from disk (/s). This shows page-ins

so: Amount of memory swapped to disk (/s). This shows page-outs. The so column is zero consistently, indicating there are no page-outs.

In Ideal condition, si and so should be at 0 most of the time, and we definitely don’t like to see more than 10 blocks per second.

IO:

bi: Blocks received from block device
bo: Blocks sent to a block device

System:

in: The number of interrupts per second, including the clock.
cs: The number of context switches per second.

A context switch occurs when the currently running thread is different from the previously running thread.

CPU:

us: % of CPU time spent in user mode (not using kernel code, not able to access to kernel resources). 

sy: % of CPU time spent running kernel code. (system time)

id: % of CPU  idle time

wa: % of CPU time spent waiting for IO.