MySQL Performance basic tuning

Innodb_buffer_pool_size 80% (System cache Pool)

Innodb_log_file_size 4G (maximum value in mysql5.5, redo log increase to improve performance, redo log to recover faster after a small crash)

\ \ Start Setting this value to 512M can have a 1G redo log, which will allow ample write space

Max_connections 151 (default value, need to be modified)

Innodb_file_per_table OFF (by default, the data and index of all tables are stored in the shared tablespace, and a. ibd file is created for each table when the value is on, and the advantage is that disk space is reclaimed when you drop, truncate, or rebuild each table. Without any benefit to database performance, this is not recommended in a large number of tables (10k+))

Innodb_flush_log_at_trx_commit 1 (the default value, which supports ACID properties, is the most appropriate when the primary concern is data security, but it can be costly for systems with slower IO (read-write) speeds. A value of 2 can lead to unreliable, because the committed thing only flush once per second to the redo log, the value of 0 o'clock is the fastest, but when the system crashes, some data may be lost, only for the backup node)

Innodb_flush_method Fdatasync (in the case of a hardware RAID controller, and a standalone cache with write-back mechanism, with battery power-off protection, configured as O_direct, otherwise, set to Fdatasync)

Innodb_log_buffer_size 1M (the default, this configuration determines the cache that is allocated for things that have not been executed, and the default is generally sufficient.) If the object contains a large number of binary large objects or large text fields, the cache will be filled quickly and trigger additional IO, see innodb_log_waits status, not 0, increase)

Query_cache_size (the best way to query the cache is to deactivate it at first, set the value to 0, and use other methods to speed up the query, optimize the index, increase the copy spread load, or enable additional caching)

Log_bin

Skip_name_resolve

skip-locking (avoid MySQL external lock, reduce the chance of error increase stability)

BACK_LOG=500 (This value indicates how many requests can be present in the stack in a short period of time before MySQL temporarily stops responding to a new request.) If there is a lot of continuity in a short period of time, increase this value. This value sets the size of the listening queue for incoming TCP/IP links. The Linux system is recommended to be set to a number less than 512, which is related to the Listen function backlog size of the system itself.

Key_buffer_size = 256M (Specifies the buffer size of the index, increasing the number of customers to get better index processing performance.) This value setting is too general to make the overall efficiency of the server lower)

Sort_buffer_size = 6M (the size of the buffer that can be used when querying the sort, exclusive per connection.) ）

Wait_timeout = 10 (Specifies the maximum link time for a request)

Recommendations for improving performance:

1. If the opened_tables is too large, you should make the Table_cache in the my.cnf bigger.

2. If the key_reads is too large, the my.cnf should be key_buffer_size larger. The cache failure rate can be calculated with key_reads/key_read_requests

3. If the handler_read_rnd is too large, then you write a lot of SQL statement query is to scan the entire table, and do not play the role of the index key

4. If the threads_created is too large, increase the value of thread_cache_size in the my.cnf. Cache hit rate can be calculated with Threads_created/connections

5. If the created_tmp_disk_tables is too large, increase the value of tmp_table_size in MY.CNF and replace the disk-based temporary table with a memory-based

System-Level optimizations:

Vi/etc/fstab under

Noatime

Ensure that the Atime logging feature is disabled on the file system. Atime is the time of the most recent file access, and the underlying filesystem must record this timestamp whenever the file is accessed. Because system administrators rarely use atime, disabling it can reduce disk access time.  This feature is disabled by adding the Noatime option in the fourth column of/etc/fstab. ---need remount.

Mount-o REMOUNT,RW/effect is obvious

Memory configuration: Numa=off

Alternatively, you can set vm.zone_reclaim_mode=0 try to reclaim memory

IO Scheduler Modification:

Scheduler:/sys/block/sda/queue/scheduler//SDA = = {Device-name}

Nr_requests:/sys/block/sda/queue/nr_requests //Disk Queue Length

READ_AHEAD_KB:/sys/block/sda/queue/read_ahead_kb//reduce read-ahead

This article is from the "Tring" blog, make sure to keep this source http://warcraft3.blog.51cto.com/6514883/1427299