MySQL参数优化简析

1. Enable InnoDB file-per-table
The innodb_file_per_table is enabled by default in MySQL 5.6 and above. You can see that in /etc/my.cnf file. The directive looks like this:
innodb_file_per_table=1
2. Store MySQL Database Data on Separate Partition

The next step is to prepare the new drive:
# fdisk /dev/sdb
# mkfs.ext4 /dev/sdb1
# mkdir /data/
# mount /dev/sdb1 /data/
# echo "/dev/sdb1 /data ext4 defaults 0 0 " >> /etc/fstab
# service mysqld stop

# cp /var/lib/mysql /data/ -Rp
# mv /var/lib/mysql /var/lib/mysql-backup
# ln -s /data/mysql /var/lib/mysql
# service mysqld start

3. Optimizing InnoDB buffer pool Usage
   you would probably want to give about 60-70% of the memory to the innodb_buffer_pool_size
   innodb_buffer_pool_size = （60%-70%）total_mem
4. Avoid Swappiness in MySQL
By default the option is enabled:
# sysctl vm.swappiness
vm.swappiness = 60
To disable swappiness, run the following command:
# sysctl -w vm.swappiness=0
5. Set MySQL Max Connections

mysql> set global max_connections = 300;

6. Configure MySQL thread_cache_size

mysql> show status like 'Threads_created';
mysql> show status like 'Connections';

Now use the following formula to calculate the thread cache hit rate percentage:

100 - ((Threads_created / Connections) * 100)

mysql> set global thread_cache_size = 16;

7. Disable MySQL Reverse DNS Lookups

[mysqld]
skip-name-resolv

8. Configure MySQL query_cache_size

query_cache_type = 1
query_cache_limit = 256K
query_cache_min_res_unit = 2k
query_cache_size = 80M

9. Configure tmp_table_size and max_heap_table_size
[mysqld]
tmp_table_size= 64M
max_heap_table_size= 64M

10. Enable MySQL Slow query Logs

slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log
long_query_time = 1

11. Check for MySQL idle Connections
# mysqladmin processlist -u root -p | grep “Sleep”
This will show you list of processes that are in sleep state,you can change the wait_timeout directive. The default value is 28800 seconds, while you can safely decrease it to something like 60:
wait_timeout=60
12. Choosing Right MySQL Filesystem

Filesystems                         XFS         Ext4       Btrfs
Maximum filesystem size     8EB        1EB        16EB
Maximum file size                8EB        16TB      16EB

13. Set MySQL max_allowed_packet

14. Check MySQL Performance Tuning
# wget https://github.com/major/MySQLTuner-perl/tarball/master
# tar xf master
# cd major-MySQLTuner-perl-993bc18/
# ./mysqltuner.pl

 

具体使用请参考：https://github.com/major/MySQLTuner-perl

转载于:https://www.cnblogs.com/yanglinux/p/9054729.html