MySQL Tipps und Tricks

From Lolly's Wiki
Jump to navigationJump to search

Tipps und Tricks


Oneliner

All grants

# mysql --skip-column-names --batch --execute 'select concat("`",user,"`@`",host,"`") from mysql.user' | xargs -n 1 -i mysql --execute 'show grants for {}'

Last update time

  • Per table
mysql> SELECT TABLE_SCHEMA AS DB,TABLE_NAME,UPDATE_TIME FROM INFORMATION_SCHEMA.TABLES ORDER BY DB,UPDATE_TIME;
  • Per database
mysql> SELECT TABLE_SCHEMA AS DB,MAX(UPDATE_TIME) AS LAST_UPDATE FROM INFORMATION_SCHEMA.TABLES GROUP BY DB ORDER BY LAST_UPDATE;

InnoDB space

Per database

mysql> select table_schema as database_name, sum(round(data_length/1024/1024,2)) as total_size_mb from information_schema.tables where engine like 'innodb' group by table_schema order by total_size_mb;

Per table

mysql> select table_schema as database_name,table_name,round(data_length/1024/1024,2) as size_mb from information_schema.tables order by size_mb;

Logging

If you use SET GLOBAL it is just for the moment.

Don't forget to add it in your my.cnf to make it permanent!

What can I log?

The interesting variables here are:

  • log_queries_not_using_indexes
  • log_slave_updates
  • log_slow_queries
  • general_log

Choose logging destination FILE/TABLE/NONE

This affects general_log and slow_query_log.

  • Log to the table mysql.slow_log and mysql.general_log
mysql> SET GLOBAL log_output=TABLE;
  • Log to the table mysql.slow_log and mysql.general_log
mysql> SET GLOBAL log_output=TABLE;
  • Both: tables and files
mysql> SET GLOBAL log_output = 'TABLE,FILE';
  • None, if NONE appears in the log_output destinations there is no logging
mysql> SET GLOBAL log_output = 'TABLE,FILE,NONE';

is equal to 

mysql> SET GLOBAL log_output = 'NONE';

Enable/disable general logging

mysql> SET GLOBAL general_log_file = '/var/lib/mysql/general.log';
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL general_log = 'ON';
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL general_log = 'OFF';
Query OK, 0 rows affected (0.00 sec)

Enable/disable logging of slow queries

mysql> SET GLOBAL slow_query_log_file = '/var/lib/mysql/slow-query.log';
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL slow_query_log = 'ON';
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL slow_query_log = 'OFF';
Query OK, 0 rows affected (0.00 sec)

Filesystems for MySQL

ext3/ext4

Create Options

# mkfs.ext4 -b 4096  /dev/mapper/vg--data-lv--ext4--mysql_data

Mountoptions

  • noatime
  • data=writeback (best performance , only metadata is logged)
  • data=ordered (ok performance , recording metadata and grouping metadata related to the data changes)
  • data=journal (worst performance, but best data protection, ext3 default mode, recording metadata and all data)


Raw devices with InnoDB

Take a look at setting device permissions via udev first.

After that the device is owned by mysql: 

# ls -alL /dev/vg-data/lv-rawdisk-innodb01 
brw-rw---- 1 mysql mysql 252, 0 Aug 12 15:07 /dev/vg-data/lv-rawdisk-innodb01

Determine the size: 

# lvs vg-data 
  LV                  VG      Attr      LSize  Pool Origin Data%  Move Log Copy%  Convert
  lv-rawdisk-innodb01 vg-data -wi-a---- 25.00g

# fdisk -l /dev/vg-data/lv-rawdisk-innodb01 

Disk /dev/vg-data/lv-rawdisk-innodb01: 26.8 GB, 26843545600 bytes
255 heads, 63 sectors/track, 3263 cylinders, total 52428800 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000

# bc -l
26843545600/(1024*1024*1024)
25.00000000000000000000

Yes... really 25GB!

Add your logical volume to your configuration /etc/mysql/conf.d/innodb.cnf : 

[mysqld]
# InnoDB raw disks
innodb_data_home_dir=
innodb_data_file_path=/dev/vg-data/lv-rawdisk-innodb01:25Gnewraw

Start mysql: 

# service mysql start

Aaaaaand.. do not forget apparmor! Like I did.. :-D 

InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name /dev/dm-0
InnoDB: File operation call: 'open'.
InnoDB: Cannot continue operation.

# tail /var/log/kern.log
...
Aug 12 15:30:09 mysql kernel: [ 5840.118528] audit: type=1400 audit(1439386209.399:33): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/dev/dm-0" pid=11810 comm="mysqld" requested_mask="wr" denied_mask="wr" fsuid=108 ouid=108
...

Add your raw device to the apparmor config in /etc/apparmor.d/local/usr.sbin.mysqld : 

# Site-specific additions and overrides for usr.sbin.mysqld.
# For more details, please see /etc/apparmor.d/local/README.
/dev/dm-* rwk,

Reload apparmor: 

# service apparmor reload

Another try! 

# service mysql start

InnoDB: The first specified data file /dev/vg-data/lv-rawdisk-innodb01 did not exist:
InnoDB: a new database to be created!
150812 15:48:23  InnoDB: Setting file /dev/vg-data/lv-rawdisk-innodb01 size to 25600 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000 1100 1200 ...

Much better!

So shutdown MySQL again!

Change your configuration /etc/mysql/conf.d/innodb.cnf and change newraw to raw! : 

[mysqld]
# InnoDB raw disks
innodb_data_home_dir=
innodb_data_file_path=/dev/vg-data/lv-rawdisk-innodb01:25Graw

NFS

NFSv4

On NetApp CDOT SVM
cdot1nfsv4::> export-policy rule create -policyname default       -clientmatch 172.18.128.0/22 -superuser none -rwrule none -rorule sys -allow-dev false -allow-suid false
cdot1nfsv4::>
cdot1nfsv4::> export-policy      create -policyname mysql_clients
cdot1nfsv4::> export-policy rule create -policyname mysql_clients -clientmatch 172.18.128.0/22 -superuser sys -rwrule sys -rorule sys -allow-dev true -allow-suid false
cdot1nfsv4::>
cdot1nfsv4::> nfs server modify -v4.0 enabled -v4-id-domain this.domain.tld           
cdot1nfsv4::> set -units GB
cdot1nfsv4::> vol show -volume MYSQLNFS_* -fields volume,policy,size,junction-path
vserver            volume                size policy        junction-path          
------------------ --------------------- ---- ------------- ---------------------- 
cdot1nfsv4         MYSQLNFS_DATA         40GB mysql_clients /MYSQLNFS_DATA 
cdot1nfsv4         MYSQLNFS_LOG          1GB  mysql_clients /MYSQLNFS_LOG  
2 entries were displayed.

Links:

On Linux
/etc/sysctl.d/99-mysql.conf
#
## http://www.ajohnstone.com/achives/optimizing-mysql-over-nfs-with-netapp/
#
###################################################################
# Semaphores & IPC for optimizations in innodb
kernel.shmmax=2147483648
kernel.shmall=2147483648
kernel.msgmni=1024
kernel.msgmax=65536
kernel.sem=250 32000 32 1024

###################################################################
# Swap
vm.swappiness = 0
vm.vfs_cache_pressure = 50
/etc/sysctl.d/99-netapp-nfs.conf
#
## http://www.ajohnstone.com/achives/optimizing-mysql-over-nfs-with-netapp/
#
###################################################################
# Optimization for netapp/nfs increased from 64k, @see http://tldp.org/HOWTO/NFS-HOWTO/performance.html#MEMLIMITS
net.core.wmem_default=262144
net.core.rmem_default=262144
net.core.wmem_max=262144
net.core.rmem_max=262144
 
net.ipv4.tcp_rmem = 4096 87380 16777216
net.ipv4.tcp_wmem = 4096 65536 16777216
net.ipv4.tcp_no_metrics_save = 1
 
# Guidelines from http://media.netapp.com/documents/mysqlperformance-5.pdf
net.ipv4.tcp_sack=0
net.ipv4.tcp_timestamps=0
sunrpc.tcp_slot_table_entries=128

#nfs.v3.enable on
nfs.tcp.enable=on
nfs.tcp.recvwindowsize=65536
nfs.tcp.xfersize=65536
 
#iscsi.iswt.max_ios_per_session 128
#iscsi.iswt.tcp_window_size 131400
#iscsi.max_connections_per_session 16
 
net.ipv4.tcp_tw_reuse = 1
net.ipv4.ip_local_port_range = 1024 65023
net.ipv4.tcp_max_syn_backlog = 10240
net.ipv4.tcp_max_tw_buckets = 400000
net.ipv4.tcp_max_orphans = 60000
net.ipv4.tcp_synack_retries = 3
net.core.somaxconn = 10000
 
kernel.sysrq=0
 
net.ipv4.neigh.default.gc_thresh1 = 4096
net.ipv4.neigh.default.gc_thresh2 = 8192
net.ipv4.neigh.default.gc_thresh3 = 8192
net.ipv4.neigh.default.base_reachable_time = 86400
net.ipv4.neigh.default.gc_stale_time = 86400


Raise allowed number of open files for mysql in /etc/security/limits.d/mysql.conf
mysql soft nofile 1024000
mysql hard nofile 1024000
mysql soft nproc 10240
mysql hard nproc 10240
Modify systemd mysql.service to raise the number of files limit

To raise the number of files for the service you have to tell the systemd the new limit. 

# systemctl edit mysql.service

and enter: 

[Service]
        LimitNOFILE=1024000
# systemctl cat mysql
# /lib/systemd/system/mysql.service
# MySQL systemd service file
...
# /etc/systemd/system/mysql.service.d/override.conf
[Service]
        LimitNOFILE=1024000

Do not forget to activate and check the limit 

# systemctl daemon-reload
# systemctl restart mysql
# awk 'NR==1 || /Max open files/' /proc/$(pgrep mysqld$)/limits 
Limit                     Soft Limit           Hard Limit           Units     
Max open files            1024000              1024000              files
Modify systemd service to wait for NFS

To be sure that the NFS mount is ready when the mysql server starts add After=nfs-client.target to the systemd service in the Unit-section. 

# systemctl edit mysql.service

and enter: 

[Unit]
        Description=MySQL Community Server
        After=network.target
        After=nfs-client.target
# systemctl cat mysql
# /lib/systemd/system/mysql.service
# MySQL systemd service file

[Unit]
Description=MySQL Community Server
After=network.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql
PermissionsStartOnly=true
ExecStartPre=/usr/share/mysql/mysql-systemd-start pre
ExecStart=/usr/sbin/mysqld
ExecStartPost=/usr/share/mysql/mysql-systemd-start post
TimeoutSec=600
Restart=on-failure
RuntimeDirectory=mysqld
RuntimeDirectoryMode=755

# /etc/systemd/system/mysql.service.d/override.conf
[Unit]
        Description=MySQL Community Server
        After=network.target
        After=nfs-client.target

[Service]
        LimitNOFILE=1024000

Do not forget to activate the changes... 

# systemctl daemon-reload
# systemctl restart mysql

... and check they are active: 

# systemctl list-dependencies --after mysql.service | grep nfs-client.target
● ├─nfs-client.target
/etc/idmapd.conf
# Domain = localdomain
Domain = this.domain.tld
/etc/fstab
cdot-nfsv4-svm:/MYSQLNFS_LOG  /MYSQLNFS_LOG  nfs rw,hard,nointr,rsize=65536,wsize=65536,bg,vers=4,proto=tcp,noatime	
cdot-nfsv4-svm:/MYSQLNFS_DATA /MYSQLNFS_DATA nfs rw,hard,nointr,rsize=65536,wsize=65536,bg,vers=4,proto=tcp,noatime
/etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
...
datadir		= /MYSQLNFS_DATA/data/mysql
...
/etc/mysql/mysql.conf.d/innodb.cnf
[mysqld]
#
# * InnoDB
#
innodb_data_home_dir            = /MYSQLNFS_DATA/InnoDB
innodb_data_file_path           = ibdata1:200M:autoextend
innodb_log_group_home_dir       = /MYSQLNFS_LOG/ib_log
#innodb_flush_method             = O_DIRECT
innodb_flush_log_at_trx_commit  = 2
innodb_file_per_table           = on
# mysql -e "show variables where variable_name like '%dir' and value like '/MYSQLNFS%'"
+---------------------------+------------------------------------+
| Variable_name             | Value                              |
+---------------------------+------------------------------------+
| datadir                   | /MYSQLNFS_DATA/data/mysql/         |
| innodb_data_home_dir      | /MYSQLNFS_DATA/InnoDB              |
| innodb_log_group_home_dir | /MYSQLNFS_LOG/ib_log               |
+---------------------------+------------------------------------+
/etc/mysql/mysql.conf.d/query_cache.cnf
[mysqld]
#
# * Query Cache Configuration
#
query_cache_type         = 1
query_cache_limit        = 256K
query_cache_min_res_unit = 2k
query_cache_size         = 80M
mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+
1 row in set (0,00 sec)

mysql> SHOW VARIABLES LIKE 'query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 262144   |
| query_cache_min_res_unit     | 2048     |
| query_cache_size             | 83886080 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
5 rows in set (0,00 sec)
apparmor : /etc/apparmor.d/local/usr.sbin.mysqld
# vim:syntax=apparmor

# This should be always there...
  owner @{PROC}/@{pid}/status r,
  /sys/devices/system/node/ r,
  /sys/devices/system/node/** r,

# The mysql datadir, innodb_data_home_dir
  /MYSQLNFS_DATA/ r,
  /MYSQLNFS_DATA/** rwk,

# The mysql innodb_log_group_home_dir
  /MYSQLNFS_LOG/ r,
  /MYSQLNFS_LOG/** rwk,
Short stupid performance test
# time dd if=/dev/zero of=/MYSQLNFS_DATA/io.test bs=16k count=65536
65536+0 records in
65536+0 records out
1073741824 bytes (1,1 GB, 1,0 GiB) copied, 1,7552 s, 612 MB/s

real	0m1.772s
user	0m0.016s
sys	0m0.672s

Some things seem to work...

Sample InnoDB configuration

/etc/mysql/conf.d/innodb.cnf 

[mysqld]
# InnoDB Parameters

# innodb_buffer_pool_size=(0.7*total_mem_size)
innodb_buffer_pool_size=1433M

# bulk_insert_buffer_size
bulk_insert_buffer_size=256M

# innodb_buffer_pool_instances=... more = more concurrency
innodb_buffer_pool_instances=2

# innodb_thread_concurrency= 2*CPUs
innodb_thread_concurrency=4

# innodb_flush_method=O_DIRECT (avoids double buffering)
innodb_flush_method=O_DIRECT

# InnoDB data raw disks
innodb_data_home_dir=
innodb_data_file_path=/dev/vg-data/lv-rawdisk-innodb01:25Graw

# InnoDB log files
innodb_log_files_in_group=2
innodb_log_file_size=100M
innodb_log_group_home_dir=/var/lib/mysql/ib_log

Analyze

mysql> select * from <tablename> PROCEDURE ANALYSE();

mysql> SHOW /*!50000 GLOBAL*/ STATUS;

percona-toolkit

# aptitude install percona-toolkit

# mysql -e "explain select * from mysql.user,mysql.db where user.user=db.user" | pt-visual-explain
JOIN
+- Bookmark lookup
|  +- Table
|  |  table          db
|  |  possible_keys  User
|  +- Index lookup
|     key            db->User
|     possible_keys  User
|     key_len        48
|     ref            mysql.user.User
|     rows           3
+- Table scan
   rows           68
   +- Table
      table          user

Sysbench

# mysql -u root -e "create database sbtest;"
# sysbench \
    --test=oltp \
    --oltp-table-size=10000000 \
    --db-driver=mysql \
    --mysql-table-engine=innodb \
    --mysql-db=sbtest \
    --mysql-user=root \
    --mysql-password=$(nawk -F'=' '/password/{print $2}' /root/.my.cnf) \
    --mysql-socket=/var/run/mysqld/mysqld.sock \
    prepare
# sysbench \
    --test=oltp \
    --oltp-test-mode=complex \
    --oltp-table-size=80000000 \
    --db-driver=mysql \
    --mysql-table-engine=innodb \
    --mysql-db=sbtest \
    --mysql-user=root \
    --mysql-password=$(nawk -F'=' '/password/{print $2}' /root/.my.cnf) \
    --mysql-socket=/var/run/mysqld/mysqld.sock \
    --num-threads=4 \
    --max-time=900 \
    --max-requests=500000 \
    run
# mysql -u root_rw -e "drop table sbtest;" sbtest

Recover a damaged root account

Lost grants

Try out: 

# service mysql stop
# echo "grant all privileges on *.* to 'root'@'localhost' with grant option;" > /root/mysql-init
# mysqld_safe --init-file=/root/mysql-init
...
150812 19:14:24 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
# rm /root/mysql-init
# service mysql start

Or: 

# service mysql stop
# mysqld_safe --skip-grant-tables &
...
# mysql -e "UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root'; FLUSH PRIVILEGES; GRANT ALL ON *.* TO 'root'@'localhost';"
# mysqladmin -u root  shutdown
# service mysql start

Lost password

# service mysql stop
# echo "SET PASSWORD FOR 'root'@'localhost' = PASSWORD('the root password for mysql');" > /root/mysql-init
# mysqld_safe --init-file=/root/mysql-init
...
150812 19:15:24 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
# rm /root/mysql-init
# service mysql start

Structured configuration

This is the default in Ubuntus /etc/mysql/my.cnf: 

...
#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

/etc/mysql/conf.d/innodb.cnf: 

[mysqld]
# InnoDB Parameters

# innodb_buffer_pool_size=(0.7*total_mem_size)
#innodb_buffer_pool_size=512M
innodb_buffer_pool_size=256M

# bulk_insert_buffer_size
#bulk_insert_buffer_size=256M
bulk_insert_buffer_size=128M

# innodb_buffer_pool_instances=... more = more concurrency
innodb_buffer_pool_instances=2

# innodb_thread_concurrency= 2*CPUs
innodb_thread_concurrency=4

# innodb_flush_method=O_DIRECT (avoids double buffering)
innodb_flush_method=O_DIRECT

# InnoDB data raw disks
innodb_data_home_dir=
innodb_data_file_path=/dev/vg-data/lv-rawdisk-innodb01:25Graw

# InnoDB log files
innodb_log_files_in_group=2
innodb_log_file_size=100M
innodb_log_group_home_dir=/var/lib/mysql/ib_log

/etc/mysql/conf.d/myisam.cnf: 

[mysqld]
#key_buffer = 512M
key_buffer = 128M
table_cache = 8K
myisam_sort_buffer_size = 64M
tmp_table_size = 64M

# Variable: concurrent_insert
# Value	Description
# 0	Disables concurrent inserts
# 1	(Default) Enables concurrent insert for MyISAM tables that do not have holes
# 2	Enables concurrent inserts for all MyISAM tables, even those that have holes.
#       For a table with a hole, new rows are inserted at the end of the table if it is in use by another thread.
#       Otherwise, MySQL acquires a normal write lock and inserts the row into the hole.
concurrent_insert=2

# Variable: myisam_use_mmap
# https://www.percona.com/blog/2006/05/26/myisam-mmap-feature-51/
# 
myisam_use_mmap=1

/etc/mysql/conf.d/mysqld.cnf: 

[mysqld]
datadir         = /var/lib/mysql/data/data
# because mysql is soooo stupid
#ignore-db-dirs = lost+found # when we will have mysql >= 5.6.3

bind-address            = 127.0.0.1

open-files-limit       = 4096
max_connections        = 512

max_allowed_packet     = 16M
thread_stack           = 192K
thread_cache_size      = 8
myisam-recover-options = BACKUP
max_connections        = 512
table_cache            = 8192
thread_concurrency     = 4

default-storage-engine = innodb

# Enable the full query log. Every query (even ones with incorrect
# syntax) that the server receives will be logged. This is useful for
# debugging, it is usually disabled in production use.
#log

# Print warnings to the error log file.  If you have any problem with
# MySQL you should enable logging of warnings and examine the error log
# for possible explanations. 
log_warnings

# Log slow queries. Slow queries are queries which take more than the
# amount of time defined in "long_query_time" or which do not use
# indexes well, if log_long_format is enabled. It is normally good idea
# to have this turned on if you frequently add new queries to the
# system.
log_slow_queries
slow_query_log_file = /var/log/mysql/mysql-slow.log

# All queries taking more than this amount of time (in seconds) will be
# trated as slow. Do not use "1" as a value here, as this will result in
# even very fast queries being logged from time to time (as MySQL
# currently measures time with second accuracy only).
long_query_time = 2

# Log more information in the slow query log. Normally it is good to
# have this turned on. This will enable logging of queries that are not
# using indexes in addition to long running queries.
#log_long_format

log_bin                 = /var/lib/mysql/binlog/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
sync_binlog		= 0

performance_schema	= ON

/etc/mysql/conf.d/mysqld_safe.cnf: 

[mysqld_safe]

/etc/mysql/conf.d/mysqld_safe_syslog.cnf: 

[mysqld_safe]
syslog

/etc/mysql/conf.d/query_cache.cnf: 

[mysqld]
query_cache_limit        = 4M
query_cache_size         = 128M
query_cache_min_res_unit = 2K

MySQL Clients

Small one liners for testing purposes.

PHP

PHP PDO

$ php -r '
  $pdo=new PDO("mysql:host=mydbhost;dbname=mydb", "user", "pass", ARRAY(
                     PDO::ATTR_PERSISTENT => true
           )
         );
  $stmt=$pdo->prepare("SELECT * FROM mytable");
  if($stmt->execute()){
    while($row = $stmt->fetch()){
      print_r($row);
    }
  };
  $stmt = null;
  $pdo=null;
'
Retrieved from "https://lars.timmann.de/wiki/index.php?title=MySQL_Tipps_und_Tricks&oldid=1751"