MySQL Tipps und Tricks

From Lolly's Wiki
Revision as of 16:22, 25 November 2021 by Lollypop (talk | contribs) (Text replacement - "</source" to "</syntaxhighlight")
Jump to navigationJump to search

Tipps und Tricks


Oneliner

Show MySQL-traffic fired from a client

<source lang=bash>

  1. tcpdump -i any -s 0 -l -vvv -w - dst port 3306 | strings | perl -e '

while(<>) { chomp; next if /^[^ ]+[ ]*$/; 

 if(/^(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER)/i) {
   if (defined $q) { print "$q\n"; }
   $q=$_;
 } else {
   $_ =~ s/^[ \t]+//; $q.=" $_";
 }

}' </syntaxhighlight>

Mysql processes each second

<source lang=bash>

  1. mysqladmin -i 1 --verbose processlist

</syntaxhighlight>

All grants

<source lang=bash>

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

</syntaxhighlight>

Or a little nicer: <source lang=bash>

  1. !/bin/bash
    1. Written by Lars Timmann <L@rs.Timmann.de> 2017

function usage () { 

 cat << EOH

Usage: $0 [--all] [--grant-user <pattern>|--gu <pattern>] [--grant-db <pattern>|--gdb <pattern>] [--help] ... 

          --help:    This output

--grant-user|--gu: You can specify this option several times. 

                     The <pattern> can be:
                         <user>        : You will get grants on all hosts for this user.
                         @<host>       : You will get grants for all users on this host.
                         <user>@<host> : You will get specific grants for user@host.
                     The pattern may contain % as wildcard.
                     If the pattern is @% it shows all grants where host is exactly '%'.
--grant-db|--gdb:    You can specify this option several times.
                     The pattern names the database to look for.
                     The pattern may contain % as wildcard.
           --all:    Show all grants                 
             ...:    Optional parameters to the mysql command

EOH 

 exit

}

show_all_grants=0 declare -a grant_user for ((param=1;param<=${#};param++)) do 

 case ${!param} in
 --grant-user|--gu)
   param=$[ ${param} + 1 ]
   grant_user+=( "${!param}" )
   # delete 2 parameters from list and set back $param
   set -- "${@:1:param-2}" "${@:param+1}"
   param=$[ ${param} - 2 ]
   ;;
 --grant-db|--gdb)
   param=$[ ${param} + 1 ]
   grant_db+=( "${!param}" )
   # delete 2 parameters from list and set back $param
   set -- "${@:1:param-2}" "${@:param+1}"
   param=$[ ${param} - 2 ]
   ;;
 --all)
   show_all_grants=1
   # delete 1 parameter from list and set back $param
   set -- "${@:1:param-1}" "${@:param+1}"
   param=$[ ${param} - 1 ]
   ;;
 --help)
   usage
   ;;
 *)
   ;;
 esac

done

count=${#grant_user[@]} for((param=0;param<count;param++)) do 

 before=${#grant_user[@]}
 grant="${grant_user[${param}]}"
 user="${grant%@*}"
 if [[ "${grant}" == *\@?* ]]
 then
   host="${grant/*@}"
 else
   host=
 fi
 case ${host} in
   )
     select="select concat('\,user,'\'@\,host,'\) as user from mysql.user where user like '${user}'"
     ;;
   '%')
     select="select concat('\,user,'\'@\,host,'\) as user from mysql.user where host='${host}' ${user:+and user like '${user}'}"
     ;;
   *)
     select="select concat('\,user,'\'@\,host,'\) as user from mysql.user where host like '${host}' ${user:+and user like '${user}'}"
     ;;
 esac
 grant_user=( "${grant_user[@]:0:param}" $(mysql $* --silent --skip-column-names --execute "${select}" | sort ) "${grant_user[@]:param+1}" )
 after=${#grant_user[@]}
 param=$[ param + after - before ]
 count=$[ count + after - before ]

done

  1. Get user for database in grant_db array

for db in ${grant_db[@]} do 

 grant_user+=( $(mysql $* --silent --skip-column-names --execute "
   select concat('\,user,'\'@\,host,'\) as user from mysql.db where db like '${db}';
   select concat('\,user,'\'@\,host,'\) as user from mysql.columns_priv where db like '${db}';
   select concat('\,user,'\'@\,host,'\) as user from mysql.tables_priv where db like '${db}';
 " | sort -u ) )

done


  1. --all

if [ ${show_all_grants} -eq 1 ] then 

 printf -- '--\n-- %s\n--\n' "all grants";
 grant_user=( $(mysql $* --silent --skip-column-names --execute "select concat('\,user,'\'@\,host,'\) as user from mysql.user" | sort ) )

fi

for user in ${grant_user[@]} do 

 printf -- '--\n-- %s\n--\n' "${user}";
 show_create_user="$(mysql $* --silent --skip-column-names --execute "select (substring_index(version(), '.',1) >= 5) and (substring_index(substring_index(version(), '.', 2),'.',-1) >=7) as show_create_user;";)"
 if [ "${show_create_user}" -eq 1 ]
 then
   mysql $* --silent --skip-column-names --execute "show create user ${user};" | sed 's/$/;/'
 fi
 OLD_IFS=${IFS}
 IFS=$'\n'
 for grant in $(mysql $* --silent --skip-column-names --execute "show grants for ${user}" | sed 's/$/;/')
 do
   regex='GRANT[ ]+.*[ ]+ON[ ]+(FUNCTION[ ]+|)`([^`]*)`\..*'
   if $grant =~ $regex 
   then
     database=${BASH_REMATCH[2]}
     if [ ${#grant_db[@]} -gt 0 ]
     then

if [[ " ${grant_db[@]} " =~ " ${database} " ]] 

       then

echo "${grant}" fi 

     else
       echo "${grant}"
     fi
   else
     echo "${grant}"
   fi
 done

done </syntaxhighlight>

Last update time

  • Per table

<source lang=mysql> mysql> SELECT TABLE_SCHEMA AS DB,TABLE_NAME,UPDATE_TIME FROM INFORMATION_SCHEMA.TABLES ORDER BY DB,UPDATE_TIME; </syntaxhighlight>

  • Per database

<source lang=mysql> mysql> SELECT TABLE_SCHEMA AS DB,MAX(UPDATE_TIME) AS LAST_UPDATE FROM INFORMATION_SCHEMA.TABLES GROUP BY DB ORDER BY LAST_UPDATE; </syntaxhighlight>

InnoDB space

Per database

<source lang=mysql> 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; </syntaxhighlight>

Per table

<source lang=mysql> 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; </syntaxhighlight>

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

<source lang=mysql> mysql> SET GLOBAL log_output=TABLE; </syntaxhighlight>

  • Log to the table mysql.slow_log and mysql.general_log

<source lang=mysql> mysql> SET GLOBAL log_output=TABLE; </syntaxhighlight>

  • Both: tables and files

<source lang=mysql> mysql> SET GLOBAL log_output = 'TABLE,FILE'; </syntaxhighlight>

  • None, if NONE appears in the log_output destinations there is no logging

<source lang=mysql> mysql> SET GLOBAL log_output = 'TABLE,FILE,NONE'; </syntaxhighlight> is equal to <source lang=mysql> mysql> SET GLOBAL log_output = 'NONE'; </syntaxhighlight>

Enable/disable general logging

<source lang=mysql> 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) </syntaxhighlight>

<source lang=mysql> mysql> SET GLOBAL general_log = 'OFF'; Query OK, 0 rows affected (0.00 sec) </syntaxhighlight>

Enable/disable logging of slow queries

<source lang=mysql> 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) </syntaxhighlight>

<source lang=mysql> mysql> SET GLOBAL slow_query_log = 'OFF'; Query OK, 0 rows affected (0.00 sec) </syntaxhighlight>

Slave

Debugging

What did we see from the master

Read the binlog from Master: <source lang=bash>

  1. mysqlbinlog --read-from-remote-server --host='your replication host' --user='your replication user' --password='your replication password' --base64-output=auto --database='limit output to this database' -vv mysql-bin.number | less

</syntaxhighlight> if you get 

ERROR: Failed on connect: SSL connection error: protocol version mismatch

try <source lang=bash>

  1. mysqlbinlog --read-from-remote-server --host='your replication host' --user='your replication user' --password='your replication password' --ssl-mode=DISABLED --base64-output=auto --database='limit output to this database' -vv mysql-bin.number | less

</syntaxhighlight>

For an idea of the binlog file to investigate on the master do this on your slave: <source lang=bash>

  1. mysql -e 'show slave status\G' | awk '$1=="Master_Log_File:"'

</syntaxhighlight>

Filesystems for MySQL

ext3/ext4

Create Options

<source lang=bash>

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

</syntaxhighlight>

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: <source lang=bash>

  1. 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 </syntaxhighlight>

Determine the size: <source lang=bash>

  1. lvs vg-data
 LV                  VG      Attr      LSize  Pool Origin Data%  Move Log Copy%  Convert
 lv-rawdisk-innodb01 vg-data -wi-a---- 25.00g
  1. 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

  1. bc -l

26843545600/(1024*1024*1024) 25.00000000000000000000 </syntaxhighlight> Yes... really 25GB!

Add your logical volume to your configuration /etc/mysql/conf.d/innodb.cnf : <source lang=mysql> [mysqld]

  1. InnoDB raw disks

innodb_data_home_dir= innodb_data_file_path=/dev/vg-data/lv-rawdisk-innodb01:25Gnewraw </syntaxhighlight>

Start mysql: <source lang=bash>

  1. service mysql start

</syntaxhighlight>

Aaaaaand.. do not forget apparmor! Like I did.. :-D <source lang=mysql> 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. </syntaxhighlight>

<source lang=bash>

  1. 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 ... </syntaxhighlight> Add your raw device to the apparmor config in /etc/apparmor.d/local/usr.sbin.mysqld : <source lang=bash>

  1. Site-specific additions and overrides for usr.sbin.mysqld.
  2. For more details, please see /etc/apparmor.d/local/README.

/dev/dm-* rwk, </syntaxhighlight>

Reload apparmor: <source lang=bash>

  1. service apparmor reload

</syntaxhighlight>

Another try! <source lang=bash>

  1. service mysql start

</syntaxhighlight>

<source lang=mysql> 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 ... </syntaxhighlight>

Much better!

So shutdown MySQL again!

Change your configuration /etc/mysql/conf.d/innodb.cnf and change newraw to raw! : <source lang=mysql> [mysqld]

  1. InnoDB raw disks

innodb_data_home_dir= innodb_data_file_path=/dev/vg-data/lv-rawdisk-innodb01:25Graw </syntaxhighlight>

NFS

NFSv4

On NetApp CDOT SVM

<source lang=text> 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. </syntaxhighlight>

Links:

On Linux
Blacklist rpcsec_gss_krb5

To disable loading of the rpcsec_gss_krb5 kernel module which causes problems with performance, do this: <source lang=text>

  1. echo "blacklist rpcsec_gss_krb5" > /etc/modprobe.d/blacklist-rpcsec_gss_krb5.conf
  2. rmmod rpcsec_gss_krb5

</syntaxhighlight>

/etc/sysctl.d/99-mysql.conf

<source lang=text>

    1. http://www.ajohnstone.com/achives/optimizing-mysql-over-nfs-with-netapp/
  3. Semaphores & IPC for optimizations in innodb

kernel.shmmax=2147483648 kernel.shmall=2147483648 kernel.msgmni=1024 kernel.msgmax=65536 kernel.sem=250 32000 32 1024

  2. Swap

vm.swappiness = 0 vm.vfs_cache_pressure = 50 </syntaxhighlight>

/etc/sysctl.d/99-netapp-nfs.conf

<source lang=text>

    1. http://www.ajohnstone.com/achives/optimizing-mysql-over-nfs-with-netapp/
  3. 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

  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

  1. nfs.v3.enable on

nfs.tcp.enable=on nfs.tcp.recvwindowsize=65536 nfs.tcp.xfersize=65536

  1. iscsi.iswt.max_ios_per_session 128
  2. iscsi.iswt.tcp_window_size 131400
  3. 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 </syntaxhighlight>


Raise allowed number of open files for mysql in /etc/security/limits.d/mysql.conf

<source lang=text> mysql soft nofile 1024000 mysql hard nofile 1024000 mysql soft nproc 10240 mysql hard nproc 10240 </syntaxhighlight>

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.

<source lang=bash>

  1. systemctl edit mysql.service

</syntaxhighlight> and enter: <source lang=ini> [Service] 

       LimitNOFILE=1024000

</syntaxhighlight> <source lang=bash>

  1. systemctl cat mysql
  2. /lib/systemd/system/mysql.service
  3. MySQL systemd service file

...

  1. /etc/systemd/system/mysql.service.d/override.conf

[Service] 

       LimitNOFILE=1024000

</syntaxhighlight>

Do not forget to activate and check the limit <source lang=bash>

  1. systemctl daemon-reload
  2. systemctl restart mysql
  3. awk 'NR==1 || /Max open files/' /proc/$(pgrep mysqld$)/limits

Limit Soft Limit Hard Limit Units Max open files 1024000 1024000 files </syntaxhighlight>

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. <source lang=bash>

  1. systemctl edit mysql.service

</syntaxhighlight> and enter: <source lang=ini> [Unit] 

       Description=MySQL Community Server
       After=network.target
       After=nfs-client.target

</syntaxhighlight>

<source lang=bash>

  1. systemctl cat mysql
  2. /lib/systemd/system/mysql.service
  3. 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

  1. /etc/systemd/system/mysql.service.d/override.conf

[Unit] 

       Description=MySQL Community Server
       After=network.target
       After=nfs-client.target

[Service] 

       LimitNOFILE=1024000

</syntaxhighlight>

Do not forget to activate the changes... <source lang=bash>

  1. systemctl daemon-reload
  2. systemctl restart mysql

</syntaxhighlight>

... and check they are active: <source lang=bash>

  1. systemctl list-dependencies --after mysql.service | grep nfs-client.target

● ├─nfs-client.target </syntaxhighlight>

/etc/idmapd.conf

<source lang=text>

  1. Domain = localdomain

Domain = this.domain.tld </syntaxhighlight>

/etc/fstab

<source lang=text> 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 </syntaxhighlight>

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

<source lang=ini> [mysqld] ... datadir = /MYSQLNFS_DATA/data/mysql ... </syntaxhighlight>

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

<source lang=ini> [mysqld]

  2. * InnoDB

innodb_data_home_dir = /MYSQLNFS_DATA/InnoDB innodb_data_file_path = ibdata1:200M:autoextend innodb_log_group_home_dir = /MYSQLNFS_LOG/ib_log

  1. innodb_flush_method = O_DIRECT

innodb_flush_log_at_trx_commit = 2 innodb_file_per_table = on </syntaxhighlight> <source lang=mysql>

  1. 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 | +---------------------------+------------------------------------+ </syntaxhighlight>

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

<source lang=ini> [mysqld]

  2. * Query Cache Configuration

query_cache_type = 1 query_cache_limit = 256K query_cache_min_res_unit = 2k query_cache_size = 80M </syntaxhighlight>

<source lang=mysql> 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) </syntaxhighlight>

apparmor : /etc/apparmor.d/local/usr.sbin.mysqld

<source lang=text>

  1. vim:syntax=apparmor
  1. This should be always there...
 owner @{PROC}/@{pid}/status r,
 /sys/devices/system/node/ r,
 /sys/devices/system/node/** r,
  1. The mysql datadir, innodb_data_home_dir
 /MYSQLNFS_DATA/ r,
 /MYSQLNFS_DATA/** rwk,
  1. The mysql innodb_log_group_home_dir
 /MYSQLNFS_LOG/ r,
 /MYSQLNFS_LOG/** rwk,

</syntaxhighlight>

Short stupid performance test

<source lang=bash>

  1. 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 </syntaxhighlight> Some things seem to work...

Sample InnoDB configuration

/etc/mysql/conf.d/innodb.cnf <source lang=mysql> [mysqld]

  1. InnoDB Parameters
  1. innodb_buffer_pool_size=(0.7*total_mem_size)

innodb_buffer_pool_size=1433M

  1. bulk_insert_buffer_size

bulk_insert_buffer_size=256M

  1. innodb_buffer_pool_instances=... more = more concurrency

innodb_buffer_pool_instances=2

  1. innodb_thread_concurrency= 2*CPUs

innodb_thread_concurrency=4

  1. innodb_flush_method=O_DIRECT (avoids double buffering)

innodb_flush_method=O_DIRECT

  1. InnoDB data raw disks

innodb_data_home_dir= innodb_data_file_path=/dev/vg-data/lv-rawdisk-innodb01:25Graw

  1. InnoDB log files

innodb_log_files_in_group=2 innodb_log_file_size=100M innodb_log_group_home_dir=/var/lib/mysql/ib_log </syntaxhighlight>

Analyze

<source lang=mysql> mysql> select * from <tablename> PROCEDURE ANALYSE(); </syntaxhighlight>

<source lang=mysql> mysql> SHOW /*!50000 GLOBAL*/ STATUS; </syntaxhighlight>

Find statements which lead into an error

<source lang=mysql> mysql> select CURRENT_SCHEMA,DIGEST_TEXT,MYSQL_ERRNO,MESSAGE_TEXT from performance_schema.events_statements_history where errors!=0\G

                                                      • 1. row ***************************

CURRENT_SCHEMA: NULL 

  DIGEST_TEXT: NULL
  MYSQL_ERRNO: 1046
 MESSAGE_TEXT: No database selected

1 row in set (0,00 sec) </syntaxhighlight>


percona-toolkit

<source lang=bash>

  1. aptitude install percona-toolkit
  1. 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

</syntaxhighlight>

Sysbench

<source lang=bash>

  1. mysql -u root -e "create database sbtest;"
  2. 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
  1. 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
  1. mysql -u root_rw -e "drop table sbtest;" sbtest

</syntaxhighlight>

Recover a damaged root account

Lost grants

Try out: <source lang=bash>

  1. service mysql stop
  2. echo "grant all privileges on *.* to 'root'@'localhost' with grant option;" > /root/mysql-init
  3. mysqld_safe --init-file=/root/mysql-init

... 150812 19:14:24 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

  1. rm /root/mysql-init
  2. service mysql start

</syntaxhighlight> Or: <source lang=bash>

  1. service mysql stop
  2. mysqld_safe --skip-grant-tables &

...

  1. mysql -e "UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root'; FLUSH PRIVILEGES; GRANT ALL ON *.* TO 'root'@'localhost';"
  2. mysqladmin -u root shutdown
  3. service mysql start

</syntaxhighlight>

Lost password

<source lang=bash>

  1. service mysql stop
  2. echo "SET PASSWORD FOR 'root'@'localhost' = PASSWORD('the root password for mysql');" > /root/mysql-init
  3. mysqld_safe --init-file=/root/mysql-init

... 150812 19:15:24 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

  1. rm /root/mysql-init
  2. service mysql start

</syntaxhighlight>

Structured configuration

This is the default in Ubuntus /etc/mysql/my.cnf: <source lang=mysql> ...

  2. * IMPORTANT: Additional settings that can override those from this file!
  3. The files must end with '.cnf', otherwise they'll be ignored.

!includedir /etc/mysql/conf.d/ </syntaxhighlight>

/etc/mysql/conf.d/innodb.cnf: <source lang=mysql> [mysqld]

  1. InnoDB Parameters
  1. innodb_buffer_pool_size=(0.7*total_mem_size)
  2. innodb_buffer_pool_size=512M

innodb_buffer_pool_size=256M

  1. bulk_insert_buffer_size
  2. bulk_insert_buffer_size=256M

bulk_insert_buffer_size=128M

  1. innodb_buffer_pool_instances=... more = more concurrency

innodb_buffer_pool_instances=2

  1. innodb_thread_concurrency= 2*CPUs

innodb_thread_concurrency=4

  1. innodb_flush_method=O_DIRECT (avoids double buffering)

innodb_flush_method=O_DIRECT

  1. InnoDB data raw disks

innodb_data_home_dir= innodb_data_file_path=/dev/vg-data/lv-rawdisk-innodb01:25Graw

  1. InnoDB log files

innodb_log_files_in_group=2 innodb_log_file_size=100M innodb_log_group_home_dir=/var/lib/mysql/ib_log </syntaxhighlight>

/etc/mysql/conf.d/myisam.cnf: <source lang=mysql> [mysqld]

  1. key_buffer = 512M

key_buffer = 128M table_cache = 8K myisam_sort_buffer_size = 64M tmp_table_size = 64M

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

concurrent_insert=2

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

myisam_use_mmap=1 </syntaxhighlight>

/etc/mysql/conf.d/mysqld.cnf: <source lang=mysql> [mysqld] datadir = /var/lib/mysql/data/data

  1. because mysql is soooo stupid
  2. 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

  1. Enable the full query log. Every query (even ones with incorrect
  2. syntax) that the server receives will be logged. This is useful for
  3. debugging, it is usually disabled in production use.
  4. log
  1. Print warnings to the error log file. If you have any problem with
  2. MySQL you should enable logging of warnings and examine the error log
  3. for possible explanations.

log_warnings

  1. Log slow queries. Slow queries are queries which take more than the
  2. amount of time defined in "long_query_time" or which do not use
  3. indexes well, if log_long_format is enabled. It is normally good idea
  4. to have this turned on if you frequently add new queries to the
  5. system.

log_slow_queries slow_query_log_file = /var/log/mysql/mysql-slow.log

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

long_query_time = 2

  1. Log more information in the slow query log. Normally it is good to
  2. have this turned on. This will enable logging of queries that are not
  3. using indexes in addition to long running queries.
  4. 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 </syntaxhighlight>

/etc/mysql/conf.d/mysqld_safe.cnf: <source lang=mysql> [mysqld_safe] </syntaxhighlight>

/etc/mysql/conf.d/mysqld_safe_syslog.cnf: <source lang=mysql> [mysqld_safe] syslog </syntaxhighlight>

/etc/mysql/conf.d/query_cache.cnf: <source lang=mysql> [mysqld] query_cache_limit = 4M query_cache_size = 128M query_cache_min_res_unit = 2K </syntaxhighlight>

MySQL Clients

Small one liners for testing purposes.

PHP

PHP PDO

<source lang=php> $ 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;

' </syntaxhighlight>

Retrieved from "https://lars.timmann.de/wiki/index.php?title=MySQL_Tipps_und_Tricks&oldid=2203"