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