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

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