Posts Tagged MySQL

Troubleshooting huge MySQL temp files

Tonight I got an alert about the disk usage on db1.idc1, its root partition usage was higher than 96% and was increasing.

[root@db1.idc1 ~]# df -hP
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/mylvm-root 102G 92G 4.5G 96% /
tmpfs 32G 0 32G 0% /dev/shm
/dev/sda1 240M 30M 198M 13% /boot
/dev/mapper/mylvm2-var 1.1T 152G 893G 15% /var

After a quick troubleshoot, I found some MySQL temp files took most spaces.

[root@db1.idc1 ~]# cd /tmp
[root@db1.idc1 tmp]# ls -lh | grep G
total 78G
-rw-rw---- 1 mysql mysql 32G Aug 5 07:28 #sql_4862_0.MYD
-rw-rw---- 1 mysql mysql 7.5G Aug 5 07:28 #sql_4862_0.MYI
-rw-rw---- 1 mysql mysql 32G Aug 5 07:28 #sql_4862_2.MYD
-rw-rw---- 1 mysql mysql 7.5G Aug 5 07:28 #sql_4862_2.MYI

[root@db1.idc1 tmp]# lsof | grep /tmp/#sql_4862_0.MYD
mysqld 18530 mysql 282u REG 253,0 33339280800 2883602 /tmp/#sql_4862_0.MYD

[root@db1.idc1 tmp]# ps aux | grep 18530
root 16365 0.0 0.0 103304 916 pts/0 S+ 07:30 0:00 grep --colour=auto 18530
mysql 18530 66.5 4.2 15718552 2795960 ? Sl May20 73373:06 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql/ --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/db1.idc1.err --pid-file=/var/lib/mysql/db1.idc1.pid --socket=/var/lib/mysql/mysql.sock --port=3306

Then found that two slow queries were running on db1.idc1.
And those two queries were selecting data from 52320599850 == 52 billion rows, from the host 10.8.8.88.

[root@db1.idc1 tmp]# mysql -uusername -ppassword -e 'show full processlist\G'
...
*************************** 10. row ***************************
Id: 34770751
User: username
Host: 10.8.8.88:50657
db: dbname
Command: Query
Time: 303040
State: Sending data
Info: Select ...
Rows_sent: 0
Rows_examined: 52320599850
Rows_read: 52320599850

*************************** 11. row ***************************
Id: 34824847
User: username
Host: 10.8.8.88:50829
db: dbname
Command: Query
Time: 302476
State: Sending data
Info: Select ...
Rows_sent: 0
Rows_examined: 52240222853
Rows_read: 52240222853
...

I killed those two queries then saved the db1.idc1, asked the developers to improve the queries.

[root@db1.idc1 tmp]# mysql -uusername -ppassword
mysql> kill 34770751;
Query OK, 0 rows affected (0.00 sec)

mysql> kill 34824847;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye


[root@db1.idc1 tmp]# df -hP
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/mylvm-root 102G 12G 85G 12% /
tmpfs 32G 0 32G 0% /dev/shm
/dev/sda1 240M 30M 198M 13% /boot
/dev/mapper/mylvm2-var 1.1T 152G 892G 15% /var

No Comments

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

[mysqld]
# 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/mysqld.pid

# 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
server-id=33071

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

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

[mysqlhotcopy]
interactive-timeout

[client]
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

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

CONF="/etc/my_3307.cnf"
PIDFILE="/opt/mysql_3307/run/mysqld.pid"

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

status(){
  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
    else
      echo "MySQL (on Port 3307) is not running, but PID file exists"
      exit 1
    fi
  else
    echo "MySQL (on Port 3307) is not running"
    exit 2
  fi
}

start(){
  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
    else
      echo "MySQL (on Port 3307) is not running, but PID file exists"
      exit 1
    fi
  else
    echo "Starting MySQL (on Port 3307)"
    mysqld_safe --defaults-file=${CONF} --user=mysql >/dev/null 2>&1 &
  fi
}

stop(){
  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}
      fi
    else
      echo "MySQL (on Port 3307) is not running, but PID file exists"
      exit 1
    fi
  else
    echo "MySQL (on Port 3307) is not running"
    exit 2
  fi
}

check_root
case "$1" in
    start)
        start
        sleep 2
        status
        ;;
    stop)
        stop
        sleep 2
        status
        ;;
    status)
        status
        ;;
    *)
        echo $"Usage: $0 {start|stop|status}"
        exit 2
esac

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

No Comments

Replication and auto-failover made easy with MySQL Utilities

Reference:
http://www.clusterdb.com/mysql/replication-and-auto-failover-made-easy-with-mysql-utilities

Environment:
master: demoenv-trial-1
slaves: demoenv-trial-2 demoenv-trial-3

1. Install Percona Server, on all servers:
$ sudo yum install http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm
$ sudo yum install Percona-Server-shared-compat
$ sudo yum install Percona-Server-server-56

$ sudo yum install http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
$ sudo yum install mysql-utilities

2. Configure /etc/my.cnf, on all servers:
Ensure the “server-id” is different and the “report-host” same as own hostname
$ sudo vim /etc/my.cnf

[mysqld]
# basic setting
datadir = /opt/mysql/data
tmpdir = /opt/mysql/tmp
socket = /opt/mysql/run/mysqld.sock
port = 3306
pid-file = /opt/mysql/run/mysqld.pid

# innodb setting
default-storage-engine = INNODB
innodb_file_per_table = 1
log-bin = /opt/mysql/binlogs/bin-log-mysqld
log-bin-index = /opt/mysql/binlogs/bin-log-mysqld.index
innodb_data_home_dir = /opt/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /opt/mysql/data
binlog-do-db = testdb

# server id
server-id=1

# gtids setting
binlog-format = ROW
log-slave-updates = true
gtid-mode = on
enforce-gtid-consistency = true
report-host = demoenv-trial-1
report-port = 3306
master-info-repository = TABLE
relay-log-info-repository = TABLE
sync-master-info = 1

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

[mysqld_safe]
log-error = /opt/mysql/log/mysqld.log
pid-file = /opt/mysql/run/mysqld.pid
open-files-limit = 8192

[mysqlhotcopy]
interactive-timeout

[client]
port = 3306
socket = /opt/mysql/run/mysqld.sock
default-character-set = utf8

3. Create directories, on all servers:
$ sudo mkdir -p /opt/mysql/{data,tmp,run,binlogs,log}
$ sudo chown mysql:mysql /opt/mysql/{data,tmp,run,binlogs,log}

4. Initialize the database, on all servers:
$ sudo -i
# su – mysql
$ mysql_install_db –user=mysql –datadir=/opt/mysql/data/
$ exit
# exit
$ sudo /etc/init.d/mysql start

5. Create remote access to root@’%’ for “mysqlreplicate” to create replication setting, on all servers:
$ mysql -uroot
mysql> grant all on *.* to root@’%’ identified by ‘pass’ with grant option;
mysql> quit;

6. Create replication user, on all servers:
$ mysql -uroot
mysql> grant replication slave on *.* to ‘rpl’@’%’ identified by ‘rpl’;
mysql> quit;

7. Set up replication, on any one server:
[dong.guo@demoenv-trial-1 ~]$ mysql -uroot
mysql> use mysql;
mysql> drop user root@’demoenv-trial-1′;
mysql> quit;
Read the rest of this entry »

, ,

4 Comments

Fork me on GitHub