MySQL Tipps und Tricks
From Lolly's Wiki
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.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/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,noatimeMySQL
= /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 |
+---------------------------+------------------------------------+
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;
- See [MySQL Performance Tuning]
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;
'
