MySQL Configuration tuning options

[Mysqld]

Port = 3306

Default is 3306

user = MySQL

DataDir =/data/mysql

Default is/var/lib/mysql/

Tmpdir =/dev/shm

Default is/tmp

Slave-load-tmpdir =/tmp

Slave_load_tmpdir in the official documentation: Create a directory name for the temporary file from the server to replicate the load DATA infile statement. When the data is restored, MySQL automatically deletes the corresponding data file.

The default is no master-slave operation, so no

Socket =/var/lib/mysql/mysql.sock

The default value is/var/lib/mysql/mysql.sock

If the TAR package is installed, it is/tmp/mysql.sock

Key_buffer = 128M

Specifies the size of the index buffer, which determines the speed of index processing

It is recommended that the Key_buffer be set to 1/4 of physical memory (for MyISAM engines), or even 25% of physical memory, and if the key_buffer_size setting is too large, the system will change pages frequently, reducing system performance. Because MySQL caches data using the operating system's cache, we have to leave enough memory for the system, and in many cases the data is much larger than the index.

Default 8M

Max_allowed_packet = 128M

Default 4M

Table_open_cache = 2048

When a connection accesses a table, MySQL checks the number of tables that are currently cached. If the table is already open in the cache, direct access to the tables in the cache speeds up the query, and if the table is not cached, the current table is added to the cache and queried.

Table_open_cache Specifies the size of the table cache. Whenever MySQL accesses a table, if there is room in the table buffer, the table is opened and put into it, which allows for faster access to the table contents. By checking the status values of peak time open_tables and Opened_tables, you can determine whether you need to increase the value of Table_open_cache. If you find that open_tables equals Table_open_cache, and opened_tables is growing, you need to increase the value of Table_open_cache (the status value above can be briefed show status like ' Open %tables ' obtained). Note that you cannot blindly set the Table_open_cache to a very large value. If set too high, it may cause insufficient file descriptors, resulting in performance instability or connection failures.

Open_tables/opened_tables >= 0.85

Open_tables/table_cache <= 0.95

Default 2000

Join_buffer_size = 128MJoin operation using memory

The size of the buffer that the Federated query operation can use, like sort_buffer_size, which allocates memory for each connection alone!

Default 256KB

Sort_buffer_size = 128M Sort using memory

The size of the buffer that can be used when the query is sorted. Note: The allocated memory for this parameter is per-connection exclusive! If there are 100 connections, the actual allocated total sort buffer size is 100x6 =

600MB. Therefore, the recommended setting for a server that has around 4GB is 6-8m.

Default 256KB

Read_buffer_size = 64M Sequential read data buffer using memory

The size of the buffer that can be used by the read query operation. As with Sort_buffer_size, the allocated memory for this parameter is exclusive to each connection!

Default 256KB

Read_rnd_buffer_size = 64M Random read data buffer using memory

and sequential reads, when MySQL makes a non-sequential read (random Read) data block, it uses this buffer to stage the read data. If you read the table data based on the index information, sort

After the result set joins the table and so on. In general, MySQL needs to generate random reads to use the memory buffers set by the Read_rnd_buffer_size parameter when the data blocks are read in a certain order.

Default 256KB

Myisam_sort_buffer_size = 128M

The amount of buffer space that can be used to sort the index in the Repair table procedure, or when an index is added to the MyISAM tables by the Create Index/alter table. The minimum value is 4, and the maximum value that can be used on a 32-bit system is 4294967295, which means that more space can be used on the 4g;64-bit system. The scope is global or session level and can be used for configuration files, which belong to dynamic variables.

Default 8M

Query_cache_size = 128M

Specifies the buffer size that the query can use if it is set to 0, the query buffer is disabled (the default is 0)

Qcache_queries_in_cache number of queries registered in the cache

Qcache_inserts number of queries that are added to the cache

Number of Qcache_hits cache samples

Qcache_lowmem_prunes number of queries removed from cache due to lack of memory

qcache_not_cached number of queries not cached (cannot be cached, or due to query_cache_type)

Qcache_free_memory total free memory for query cache

Qcache_free_blocks number of free memory blocks in the query cache

Qcache_total_blocks the total number of blocks in the query cache

Qcache_queries_in_cache number of queries registered in the cache

Qcache_inserts number of queries that are added to the cache

Number of Qcache_hits cache samples

Qcache_lowmem_prunes number of queries removed from cache due to lack of memory

qcache_not_cached number of queries not cached (cannot be cached, or due to query_cache_type)

Qcache_free_memory total free memory for query cache

Qcache_free_blocks number of free memory blocks in the query cache

Qcache_total_blocks the total number of blocks in the query cache

Query_cache_limit = 8M

Specifies the buffer size that can be used by a single query, which defaults to 1M

Max_tmp_tables = 512

Number of temporary tables that a single client connection can open

Default 32

Tmp_table_size = 128M

Default 16M

Max_heap_table_size = 128M

This variable defines the size of the memory table that the user can create. This value is used to calculate the maximum row value for the memory table

Default 16M

Thread_cache = 32 thread stack information using memory

Mainly used to store each thread's own identity information, such as thread ID, thread runtime basic information, etc., we can set the Thread_stack parameter to each thread stack allocation of how much inside

Save.

Default 13

Thread_concurrency = 16

, the error setting the value of thread_concurrency will cause MySQL to not take full advantage of multi-CPU (or multicore), appearing at the same moment only one CPU (or core) is working in the case. The thread_concurrency should be set to twice times the number of CPU cores. For example, there is a dual-core CPU, then the thread_concurrency should be 4; 2 Dual-core CPUs, the value of thread_concurrency should be 8.

Default is 10

Max_connect_errors = 99999999

Max_connect_errors is a security-related counter value in MySQL that is responsible for blocking excessive attempts by clients that fail to prevent brute-force password violations. The value of max_connect_errors is not much related to performance.

Default 100

Wait_timeout = 300

Default 28800

Interactive_timeout = 300

In interactive mode, Interactive_timeout replaces Wait_timeout. This way, if some clients are interactive mode, connect to MySQL server. Then the client timeout is subject to interactive_timeout. If some clients are in non-interactive mode, long connect to MySQL server. Then the client timeout is subject to wait_timeout. (Whether the connection is interactive mode, determined by the client)

Default 28800

Long_query_time = 3

Slow queries differentiate how long it takes to slow queries

Back_log = 600

Specifies the number of possible connections for MySQL. When the MySQL main thread receives very many connection requests in a very short time, this parameter takes effect and the main thread takes a short time to check the connection and start a new thread. The value of the Back_log parameter indicates how many requests can be present in the stack for a short period of time before MySQL temporarily stops responding to a new request. If the system has many connections in a short period of time, you need to increase the value of this parameter, which specifies the size of the listening queue for incoming TCP/IP connections. Different operating systems have its own limitations on this queue size. Attempting to set the limit of Back_log above your operating system will be invalid. For Linux systems, the recommended setting is an integer less than 512.

The default value is 152

Myisam_repair_threads = 1

The default is 1.

Myisam-recover = DEFAULT

Automatic check and repair does not close the MyISAM table correctly

Default means that each time the table is accessed, it is determined whether a fix is needed, but does not force the fix (just try to fix it from key cache), and backup means that the old data file is first backed up before the repair, and force is a mandatory fix.

The default is off, which means that this feature is not turned on

Expire_logs_days = 10

Number of days to save the binary log

Default is 0 with no expiration

Default_tmp_storage_engine = MyISAM

Default temporary table storage engine

The default is InnoDB

###

Plugin-load = thread_pool.so

The thread pool contains a number of thread groups, each of which manages a set of client connections. When the connection is established, the thread pool assigns them to the thread group in a polling manner.

The number of thread group is configured by Thread_pool_size, the default is 16, the maximum is 64, the minimum is 1.

Each thread group can have a maximum of 4,096 threads.

No plugins are loaded by default

Collation_server = Utf8_general_ci

Default Latin1_swedish_ci

Character_set_server = UTF8

Default is Latin1

Character-set-client-handshake = False

Skip-character-set-client-handshake = True

This feature is not turned on by default

Innodb_lock_wait_timeout = 3600

Refers to the maximum amount of time a transaction waits for a resource to wait, which will return an application failure if it has not been assigned to a resource at this time, the time unit of the parameter is seconds, and the minimum can be set to 1s (at which point the frequent exception handling at the application end consumes performance and cannot be set too small)

Default 50

Group_concat_max_len = 4000

Sets the maximum length of the return value of the Group_concat () function, valid values range from 4 to "2^cpu word length". The scope is global or session level and is used for configuration files, which belong to dynamic variables. Combined with GROUP by will have an effect on the maximum length of merging multiple columns.

The default is 1024.

#####

Skip-slave-start

Skip-slave-start means that the synchronization thread is not started when booting from the MySQL server, it is necessary to manually start the synchronization thread after starting the slave server, and run "start slave" at the mysql> prompt to

Default is empty

Skip-name-resolve

As we all know, when the client of MySQL is connected, the server will proactively check the domain name of the client, when you set up the correct DNS server, and the DNS server no exception at this time, then your client connection to the MySQL server is very fast, if your DNS server has an exception at this time, Then connecting to the MySQL server is slow

Default is Off

Max_user_connections = 20000

Maximum number of connections for a user

The default is 0, which means you can connect as long as the server is

Max_connections = 20000

Maximum number of connections overall

Default is 151

READ_ONLY = 0

1. The database can only be read.

2. For users with super privileges, this option can be ignore.

Default is Off

Log-slave-updates = 1

Indicates that if one master hangs out, the other one takes over immediately.

Default is empty

Log-slave-updates tells slave to record the update data that is synchronized from the primary database to the binary record. If you only start Log-bin and do not start Log-slave-updates, slave only records updates for your own database operations.

Default is empty

Bulk_insert_buffer_size = 32M

Like key_buffer_size, this parameter is also used only for the use of the MyISAM storage engine to cache the temporary cache write data when bulk data is inserted. This memory area is used to cache bulk structure data to help bulk write data files when we write to statements using several of the following data:

Insert ... select ...

Insert ... values (...), (...), (. ...) ...

Load data infile ... into ... (Non-empty table)

Default 8M

Innodb_data_home_dir =/data/mysql

This parameter specifies the public part of the path that creates the InnoDB tablespace, which, by default, is the default data for MySQL, specified by the MySQL parameter DataDir

The default is to see the parameter datadir specified

#innodb_data_file_path = Ibdata1:128m:autoextend

Specifies the individual data files of the InnoDB and their sizes, separated by semicolons with more than one file. The data file path can be a relative path, relative to the directory that the Innodb_data_home_dir variable points to, and the file size representation can be in K (KB), M (MB), G (GB), but the size of these files must be at least 10MB. Without explicitly setting the Innodb_data_file_path variable, the MySQL server automatically creates a data file named Ibdata1 that automatically grows and has an initial size of 10MB in the data directory. The maximum size of a single data file depends on the operating system, which means that the maximum single file size supported by the operating system can be used to limit the volume of its data files. InnoDB also supports the use of bare devices as data files. The scope is global and can be used for option files, which are non-dynamic variables.

Storing Data file contents

Default Ibdata1:12m:autoextend

Innodb_log_group_home_dir =/data/mysql

This parameter determines the location of the files in the log filegroup, the number of files in the log group is determined by Innodb_log_files_in_group, and this location is set to the default of MySQL DataDir

The default is MySQL's DataDir designation

innodb_file_per_table = 1

You can modify InnoDB as a stand-alone tablespace pattern, and each table in each database generates a data space

Sets whether the InnoDB table uses each table spatial data file (ending with. ibd) to store data and indexes for each table separately. If you use a table space data file per table, it will no longer use system tablespace (that is, shared tablespace). Some features of the InnoDB table, such as compressed tables, are only valid for each table space. The scope is global and can be used in the option file, which is a dynamic variable.

Default is 1

Innodb_buffer_pool_size = 64G

The main function of this parameter is to cache the index of the InnoDB table, the data, the buffer when inserting the data

The size of the dedicated MySQL server setting: Operating system memory 70%-80% best.

Default is 128M

Innodb_log_file_size = 128M

If you have a large number of write operations on the INNODB data table, choosing the right Innodb_log_file_size value is important for improving MySQL performance. However, the settings are too large to increase the recovery time, so the MySQL server will take a long time to recover if MySQL crashes or a sudden power outage occurs. It is usually set to 64M to 512M depending on the size of the server (server size).

Innodb_log_buffer_size = 32M

This is the buffer used by the transaction log of the InnoDB storage engine. Similar to Binlog Buffer,innodb when writing the transaction log, in order to improve performance, the information is written to INNOFB log Buffer, when the corresponding conditions set by the Innodb_flush_log_trx_commit parameter are satisfied ( Or the log buffer is full), the log is written to the file (or to the disk).

You can set the maximum memory space that can be used by the Innodb_log_buffer_size parameter.

Default 8M

Innodb_flush_log_at_trx_commit = 0

Complaining that InnoDB is 100 times times slower than MyISAM? Then you probably forgot to adjust the value. The default value of 1 means that every single transaction commit or out-of-transaction instruction requires the log to be written to (flush) the hard disk, which is time consuming. Especially when using the battery-powered cache (Battery backed up cache). Set to 2 for many applications, especially from the MyISAM table is possible, it means to write to the system cache instead of writing to the hard disk. The log will still flush to the hard drive every second, so you will generally not lose more than 1-2 seconds of updates. Set to 0 will be faster, but the security side is poor, even if MySQL hangs may also lose the transaction data. A value of 2 will only lose data if the entire operating system is hung.

Default is 1

Innodb_flush_method = O_direct

o_dsync| O_direct

When you set the value of the Innodb_flush_method variable to O_direct, InnoDB uses the O_DIRECT flag to open the data file and use Fsync () to brush the data and log files. The O_DIRECT flag causes the operating system to neither cache nor pre-read data, it completely disables the caching of the operating system and allows all read and write operations to be directly to the storage device, avoiding double buffering. However, it does not prohibit caching and pre-reading capabilities for hardware-level (such as RAID cards), and enabling hardware-level caching and pre-reading is the only way to ensure that InnoDB can still maintain good performance when using the O_direct flag.

O_dsync will produce a double cache

Default is empty

Server-id = 281653306

Default is empty

Log-bin = Mysql-bin

Default is empty

Relay-log = Relay-bin

Default is empty

Log-error = Error.log

Default is empty

Slow-query-log=1

Default is empty

Slow-query-log-file = Slow-queries.log

Start slow query log and slow query logging location

Default is empty

US

[Mysqld]

Datadir=/data/mysql

Tmpdir=/data/mysqltmp

Default is/tmp

Plugin-load = thread_pool.so

# General Server Settings

# This setting ensures that temp tables is fast by being stored in memory. Add ENGINE = INNODB to create statements if a temp table

is too big. See Task 186143 for Pros/cons.

#default_tmp_storage_engine = MEMORY

Default is InnoDB

InnoDB write Time is probably about 5 times times the MyISAM and memory, its locking mechanism will inevitably determine the write more performance cost, and its strength lies in multithreading concurrent processing, and this test does not reflect its advantages.

Three database engines are similar in select Performance, memory slightly superior, the same high concurrency under the comparison needs to be further tested.

Table limited by MYSQLD variable max_heap_table_size (default 16M)

Default_time_zone = SYSTEM

The default time zone is System

Event_scheduler = Off

Allows you to set up your MySQL database and then perform the actions you want for a certain period of time, which is not the same as the view.

Create Event Test1

On schedule every 1 day starts

' 2007-09-01 12:00:00 '

On completion not Preserve

Do insert into yyy values (' hhh ', ' UUU ');

Before using this feature, you must ensure that the Event_scheduler is turned on and executable

SET GLOBAL event_scheduler = 1;

Default is Off

Group_concat_max_len = 4000

# Matthew said that connections should is enough for a setup like ours for now.

Max_connections = 5000

# Max Heap and temp table size should always match. This setting keeps these tables from getting too big and using the up all of memory.

Max_heap_table_size = 64M

Tmp_table_size = 64M

# Turning off the query cache gives a performance speed gain (a somewhat minor one). Since we don ' t use it and no reason to has it on.

Query_cache_type = Off

including Off,on,demand

Default off

Query_cache_size = 0

Default is 1M

Skip_name_resolve = True

Default is Off

# InnoDB engine Settings

Innodb_buffer_pool_instances = 8

The above test results showed that the data size accounted for about 1/3 of innodb_buffer_pool_size when the test data was 200Warehouse. At this point, innodb_buffer_pool_instances is 2 o'clock, MySQL performance is relatively high, and with the increase of innodb_buffer_pool_instances, MySQL performance has decreased. But overall, the performance of the database has little impact. Sets the number of zones to separate the InnoDB buffer pool. For buffer pool with several gigabytes of space, separating it into multiple zones can reduce the resource contention factor when different threads read and write to the cached page, and enhance their concurrency capability. In buffer pool, the area selected for reading or depositing pages is randomly based on the hash algorithm. Each buffer pool manages its own free lists, List brushes, LRU, and other data structures related to buffer pool, and is protected by their respective mutexes. This variable functions only when the value of the variable innodb_buffer_pool_size is greater than 1G, and the overall buffer pool space is partitioned by the buffer pools instance. For best utility purposes, it is recommended that you use the innodb_buffer_pool_instances and innodb_buffer_pool_size variables to make each buffer pool instance have at least 1G of space. The scope is global and can be used for option files, which are non-dynamic variables.

Default is 8

# This value should is 50%-80% of total system memory for a dedicated server

Innodb_buffer_pool_size = 100GB

# These-settings help warm up the buffer pool, keeping server speed up after restarts. Increases shutdown and startup time.

Innodb_buffer_pool_dump_at_shutdown = On

Default is Off

Innodb_buffer_pool_load_at_startup = On

Default is Off

# This setting makes changes to data = Get committed to disk (Fsync) once per second, reducing disk I/O

Innodb_flush_log_at_trx_commit = 0

# This setting skips unnecessary buffering when writing

Innodb_flush_method = O_direct

Innodb_lock_wait_timeout = 3600

Default is 50

# This is a recommended log size starting point from Matthew. Update This once we get better statistics.

Innodb_log_file_size = 256MB

Default is 48M

# Character set and collation settings

Collation_server = Utf8_general_ci

Character_set_server = UTF8

Character-set-client-handshake = False

Skip-character-set-client-handshake = True

# Replication Settings

# ROW format avoids problems with certain time based queries not being properly replicated, in addition to being faster in General

than STATEMENT.

Binlog_format = ROW

row| Statement| Mixed

Default statement

Log_bin

# This setting should is set to 1 for slaves to prevent writing data to the tables. Temp tables can still be written and created.

READ_ONLY = 0

# This setting needs to is an integer unique to the server. Zero means it does not replicate.

server_id = 0

# This setting can-tell MySQL-look in specific directories for further config files. We can use the slave replication to

Control what databases is replicated.

#!includedir/etc/my.cnf.d

This article from the "WCF" blog, reproduced please contact the author!

MySQL Configuration tuning options