Lock_time in mysql slow log

   ----- start_utime
 l    .
 o    .
 c   lock tables
 k    .
 t    .
   ----- utime_after_lock
 q    .
 u    .
 e   query execution
 r    .
 y    .
 t    .
    ------ end_query_time = current_time
lockt= utime_after_lock - start_utime
queryt= current_time - utime_after_lock

tuning mysql query cache

The information provided by the Qcache_lowmem_prunes status variable can help you tune the query cache size. It counts the number of queries that have been removed from the cache to free up memory for caching new queries. The query cache uses a least recently used (LRU) strategy to decide which queries to remove from the cache.

FLUSH QUERY CACHE does not remove any queries from the cache, unlike FLUSH TABLES or RESET QUERY CACHE.

SET GLOBAL query_cache_size =
SHOW VARIABLES LIKE 'query_cache_size';

установка значения:

Munin: Plugin to monitor the Seconds_Behind_Master on mysql slave

ln -s /path/to/script /etc/munin/plugins/
/etc/init.d/munin-node restart

# Plugin to monitor the Seconds_Behind_Master of replication on a mysql slave
# Parameters:
#       configure
#       autoconf
# Configuration variables
#       mysqlopts     - Options to pass to mysql
#%# family=auto
#%# capabilities=autoconf
if [ "$1" = "autoconf" ]; then
        $MYSQL --version 2>/dev/null >/dev/null
        if [ $? -eq 0 ]

repair all mysql tables

mysql -Be "select concat('repair table ', TABLE_SCHEMA, '.', table_name, ';') from information_schema.tables where table_schema<>'information_schema';" | tail -n+2 | mysql


mysqlcheck --auto-repair -A

If a checked table is corrupted, automatically fix it. Any necessary repairs are done after all tables have been checked.

Check all tables in all databases.


A client that has the SUPER privilege can disable binary logging of its own statements by using a SET sql_log_bin=0 statement.



When you’re using CHANGE MASTER TO to set start position for the slave you’re specifying position for SQL thread and so you should use Relay_Master_Log_File:Exec_Master_Log_Pos.


Increase innodb_log_file_size

If you modify the innodb_log_file_size, MySQL will fail to restart and InnoDB will complain about the size of the changed log file.

The proper way to increase the innodb_log_file_size:
shutdown mysql server
make backup of data and log files
remove InnoDB log files
set new value for innodb_log_file_size in my.cnf
start mysqld
check error logs to ensure everything went fine.

Advanced MySQL Replication Techniques


In this broader schema, water is the master of air and slave of earth, which is slave of fire; and this last is in turn slave of air, thus completing the circle. The boxed numbers next to each server indicate the server ID, which must be different for each node; the auto_increment_increment, the same for all the nodes; and the auto_increment_offset, which guarantees the uniqueness of self-generated keys.

Here is the complete setup for all nodes:

# node A - water

MySQL Performance Tuning: EXPLAIN


system              The table has only one row 
const               At the most one matching row, treated as a constant 
eq_ref              One row per row from previous tables 
ref                 Several rows with matching index value 
ref_or_null         Like ref, plus NULL values 
index_merge         Several index searches are merged 
unique_subquery     Same as ref for some subqueries 
index_subquery      As above for non-unique indexes 
range               A range index scan 
index               The whole index is scanned 

User tags

zRam find chroot graylog2 elliptics ps glusterfs backup qcow2 alien regex sg PTR arping htop gpg cache bug cpanm bonding noop gdb rtl8723be storage ipmitool vrrp tzdata xhost cluster /etc/network/interfaces opcontrol jackd KVM munin iptables bind wordpress MODx dpkg nfs4 SuperMicro limit_conn xen tin SYN 3Ware sysfs hotplug arp etch hdparm soa puppet vlan parallel sublime iowait lubuntu profiling debootstrap pam pvmove HDFS lenny SpamAssassin RT Areca lstat APU replication vim backtrace usb exim4 vrrpd apt exim bridge source virsh docker-compose nvidia conntrack routing DRBD CentOS build dhclient sftp java perlbrew oprofile iops sysresccd shellshock glxinfo cpu initrd idmapd proxy nginx in-addr.arpa ulimit top iSCSI Swift freedos openvpn scsi tar centos 7 gtk ddos mount iostat alsa sysctl.conf swap virtualbox keyboard fail2ban MegaRAID lxc security erase tiger arch ansible dns ha mariadb LVM ip asoundrc youtube groups language bash ssh chromium performance Salt mdadm quagga boot squeeze OpenSSL tftp fido7 sysrq git sysctl slab raid5 fio route flash gre X forwarding ipmi corosync apache CPAN docker tun shorewall StorMan SSD Adaptec HTTPS qemu radeon rkhunter NFS mkfs rtsp LSI debian ardour numa pulseaudio php pacemaker newgrp leap second firefox video su dstat virt-install drupal taskset kernel xargs PXE bacula cpu usage dhcp search core dump xfs equalizer grub OpenStack vtysh cgroups ubuntu netfilter rsync lts tool encrypt .htaccess raid wget mysql dovecot deb AMD opreport perl bscan rpm cfq