linux swap and mysql memory

After mysql installation, To make mysql server production ready we have to tune default mysql variables considering the hardware, production load, performance, durability etc.

But What about the optimizing  Linux OS settings  with mysql ?

mysql-linux1

Here it is, i encountered  an issue once when i was checking TOP output for mysqld process and found that the mysqld  memory continuously increasing. Also there was one more thing  that it was using swap memory even if there was enough RAM memory available. we have monitored for a week and this was causing an issue like slow query processing, performance and slowness on DB server.

To solve this problem we have to set correct swappiness

  •       To check vm.swappiness

                    cat /proc/sys/vm/swappiness    (Default value : 60 (Range 0 to 100))

  •    To set a new non-persistent value :

                     sysctl -w vm.swappiness=0

  •    To set a new persistent value :

                     add vm.swappiness=0 in the /etc/sysctl.conf file

All Set !!

About SWAP space:

The swappiness parameter controls the tendency of the kernel to move processes out of physical memory and onto the swap disk. Because disks are much slower than RAM, this can lead to slower response times for system and applications if processes are too aggressively moved out of memory.

  1. swappiness can have a value of between 0 and 100
  2. swappiness=0 tells the kernel to avoid swapping processes out of physical memory for as long as possible
  3. swappiness=100 tells the kernel to aggressively swap processes out of physical memory and move them to swap cache

Following are basic instructions for checking swappiness, emptying your swap and changing the swappiness to 0:

To check memory usage status :

          free

To check the swappiness value:

         cat /proc/sys/vm/swappiness

To turn swapoff :
This will empty your swap and transfer all the swap back into memory. First make sure you have enough memory available by viewing the resources tab of gnome-system-monitor, your free memory should be greater than your used swap. This process may take a while, use gnome-system-monitor to monitor and verify the progress.

sudo swapoff -a

To set the new value to 0:

echo 0 | sudo tee /proc/sys/vm/swappiness

To turn swap back on:

sudo swapon -a

mysql geographical replication lag

Recently while working on geographical master slave replication we encountered an issue, where in peak hours replication slave lagging.

when we checked SHOW SLAVE STATUS value of Seconds_Behind_Master was jumping from 0 to a random high value, and then back to 0.

We also found this in the processlist of Master,

1366318 rpluser [IP Address]:15120  NULL  Binlog  Dump   9033    Writing to net  NULL

The status “Writing to net” means that MASTER is sending (or trying to send) data to SLAVE.

In a healthy replication, the status would be “Has sent all binlog to slave; waiting for binlog to be updated“.

It usually happens when MASTER-SLAVE geographically distributed in a different data-center, network , and slow network.

Here limited network bandwidth was causing a problem, and we needed a solution to compress the transmission between the master and slave databases. MySQL provides a variable, which is dynamic and is set on the slave: slave_compressed_protocol=1

If this option is set to 1, use compression for the slave/master protocol if both the slave and the master support it. The default is 0 (no compression).

changes on slave:

show global variables like 'slave_compressed_protocol';   //default OFF

stop slave;

set global slave_compressed_protocol=1;

start slave;

show global variables like 'slave_compressed_protocol';     // ON

Also,  check PROCESSLIST on master and replication user status,

ALL SET!!