Maximize mysql Performance

[Client] # consumer [mysqld] # globalsettingsport3306socketdatamysqlmysql. sockbasedirusrlocalmysqltmpdirdatamysqldatadirdatamysqlpid-filedatamysqlrongzhong

[Client] # passwd = your_passwd port = 3306 socket =/data/mysql. sock [mysqld] # global settings port = 3306 socket =/data/mysql. sock basedir =/usr/local/mysql tmpdir =/data/mysql datadir =/data/mysql pid-file =/data/mysql/rongzhong

[Client]

# Passwd = your_passwd

Port = 3306

Socket =/data/mysql. sock

[Mysqld]

# Global settings

Port = 3306

Socket =/data/mysql. sock

Basedir =/usr/local/mysql

Tmpdir =/data/mysql

Datadir =/data/mysql

Pid-file =/data/mysql/rongzhong. pid

# Skip external lock only for MyISAM

Skip-external-locking

# Disable tcp socket connections. Only unix/linux socket connections are allowed.

Skip_networking

# Disable DNS resolution to speed up connection

Skip_name_resolve

# Skip the authorization table and use it when you forget the root password

# Skip-grant-tables

# Default storage engine

# Default-storage-engine = MyISAM/INNODB

# Default Character Set

Character_set_server = utf8

# Maximum number of connections

Max_connections = 20480

# Maximum number of connections per user

Max_user_connection= 1024

# Number of seconds before the server closes an interactive connection. The default value is 28800.

Interactive_timeout = 7200

# Number of thread caches. Use idle connections to create a new connection. 1G-> 8 2G-> 16 3G-> 32> 3G-> 64

# You can add this value when many short connections exist.

Thread_cache_size = 64

# Buffer for data sorting (eg: order/group by) in a single thread (connection). One-time allocation during connection creation. The default value is 2 MB. This value can be increased when Sort_merge_passes is large.

Sort_buffer_size = 2 M

# Buffer for join operations on tables without indexes. The default value is 256 kb. One-time allocation during connection Creation

Join_buffer_size = 262144

# Specify the size of the connection listening queue. this parameter is invalid when the value exceeds OS tcp_max_syn_backlog/somaxconn. We recommend that you set this parameter to an integer smaller than 512.

Back_log = 512

# Table_open_cache number of tables that can be opened at the age

Table_open_cache = 256

# The maximum number of messages transmitted at a time, which is the maximum value of net_buffer_length

Max_allowed_packet = 8 M

# Bin-log

# Enable binary log

Log-bin = mysql-bin

Log-bin-index = mysql-bin.index

# Log format. Optional values: row, statement, and mixed. The default value is statement. mixed is recommended.

Binlog_format = mixed

# Maximum value of a binlog

Max_binlog_size = 1G

# Binlog cache size for a single connection. The default value is 1 MB.

Binlog_cache_size = 2 M

# Total binlog cache size

Max_binlog_cache_size = 2G

# Sync_binlog may be 0 or 1

# Whether the binlog is synchronized to the disk. If it is 0, it is handed over to the OS. If it is 1, it is synchronized immediately after the transaction ends, causing high IO consumption.

Sync_binlog = 0

# Slow-log

# Enable slow log

Slow-query-log = 1

# Specify the slow query duration

Long-query-time = 2

# Path

Slow-query-log-file =/data/mysql/slowlog/slow. log

# Queries with no indexes are recorded in slow log

Log_queries_not_using_indexes = 1

# Query cache

# Enable query cache

Query_cache_type = 1

# Maximum value for storing a single query cache. The default value is 1 MB.

Query_cache_limit = 1 M

# Minimum value for storing a single query cache. The default value is 4 K.

Query_cache_min_res_unit = 4096

# Maximum query value

Query_cache_size = 256 M

# MyISAM

# Index cache size, formula: Key_Size = key_number * (key_length + 4)/0.67

Key_buffer_size = 128 M

# Cache for sequential reading by a single thread. The default value is 128 kb.

Read_buffer_size = 2 M

# Cache for random reading by a single thread. The default value is 256 kb.

Read_rnd_buffer_size = 4 M

# Maximum Cache size allowed during index reconstruction. The default value is 8 Mb.

Myisam_sort_buffer_size = 64 M

# Specify the block size in the index cache. The default value is 1 K.

Key_cache_block_size = 1024

# Disabling index refresh

Delay_key_write = 1

# Innodb

# Enable independent tablespace

# Innodb_file_per_table = 1

# Set the innodb memory buffer size. In a mysql-only environment, we recommend that you set it to 60 to the server memory ~ 80%

Innodb_buffer_pool_size = 8G

# Set the number of threads that the OS can enter the innodb kernel. 2 * (number of CPU cores + number of disks) is recommended)

Innodb_thread_concurrency = 16

# Set innodb io behavior. Optional values: fsync/O_DSYNC/O_DIRECT. We recommend O_DIRECT to improve random write efficiency.

Innodb_flush_method = O_DIRECT

# Set the number of dirty pages that the mysql master process fl to the disk per second. The default value is 200.

# For systems with inpidual 5400 RPM or 7200 RPM drives, you might lower the value to the former default of 100

Innodb_io_capacity = 100

# Set the number of threads for writing dirty pages

Innodb_write_io_threads = 8

# Set the number of threads for reading file blocks from the disk

Innodb_read_io_threads = 8

# Set the transaction log buffer size

# Set the transaction log buffer size

Innodb_log_buffer_size = 8 M

# How to synchronize logs from a log thread to a disk; optional value: 0/1/2

#0: refresh every second 1: refresh every time a transaction is submitted 2: Hand it to OS for synchronization

Innodb_flush_log_at_trx_commit = 0

# Database dictionary information and table structure space

Innodb_additional_mem_pool_size = 16 M

# Enable the Double write Buffer area to ensure data integrity, but consume IO

Innodb_doublewrite = 1

[Mysqldump]

# Do not buffer the whole result set in memory before writing it to file. Required for dumping very large tables

Quick

Max_allowed_packet = 16 M

[Mysql]

No-auto-rehash

[Myisamchk]

Key_buffer_size = 512 M

Sort_buffer_size = 512 M

Read_buffer = 8 M

Write_buffer = 8 M

[Mysqlhotcopy]

Interactive-timeout

[Mysqld_safe]

# Increase the amount of open files allowed per process. Warning: Make

# Sure you have set the global system limit high enough! The high value

# Is required for a large number of opened tables

Open-files-limit = 8192