MySQL configuration file Mysql.ini parameters detailed

My.ini (Linux system is my.cnf), when the MySQL server is started, it will read this file, set the relevant operating environment parameters.

My.ini is divided into two pieces: Client section and Server section.

Client section is used to configure MySQL client parameters.

To view configuration parameters, you can use the following command:

Show variables like '%innodb% '; # view INNODB Related configuration parameters

Show status like '%innodb% '; # view InnoDB related Run-time parameters (such as the number of tables currently open, the number of tables currently open)

Show global status like ' Open%tables '; # View Global run-time parameters, plus Global is a statistic for all database instances running in the current MySQL server. Without global, only the current database instance is counted.

1, Client section

[Client]

Port = 3306 # Set the ports that are used by default when the MySQL client connects to the server

[MySQL]

Default-character-set=utf8 # set MySQL client default character set

2, Server section

[Mysqld]

port=3306 # MySQL server-side default listener (listen on) TCP/IP port

Basedir= "C:/Program files/mysql/mysql Server 5.5/" # benchmark path, other paths are relative to this path

Datadir= "C:/Program files/mysql/mysql Server 5.5/data" # MySQL Database file directory

Character-set-server=latin1 # Server uses a character set that defaults to a 8-bit encoded latin1 character set

DEFAULT-STORAGE-ENGINE=INNODB # Default storage engine to be used when creating new tables

Sql-mode= "strict_trans_tables,no_auto_create_user,no_engine_substitution" # SQL mode is STRICT mode

MAX_CONNECTIONS=100 # Maximum number of concurrent connections (users) supported by the MySQL server. But always reserve one of the connections to the administrator to log on with super privileges, even if the number of connections reaches the maximum limit. If set too small and the user is more, will often appear "Too many connections" error.

Query_cache_size=0 # Query cache size, which is used to cache the results of a select query. If you have many select queries that return the same query results, and you rarely change the table, you can set query_cache_size greater than 0 to greatly improve query efficiency. And if the table data changes frequently, do not use this, will be counterproductive

TABLE_CACHE=256 # This parameter is called Table_open_cache in the 5.1.3 version, and is used to set the number of table caches. Because each client connection accesses at least one table, the value of this parameter is related to Max_connections. When a connection accesses a table, MySQL checks the number of currently cached tables. If the table is already open in the cache, direct access to the table in the cache is faster; If the table is not cached, the current table is added to the cache and queried. Table_cache is used to limit the maximum number of cached tables before performing a cache operation: if the currently cached table does not reach Table_cache, the new table will be added, and if this value is reached, MySQL will release the previous cache based on rules such as the last query time of the cached table, query rate, and so on.

tmp_table_size=34m # The maximum allowable size for each temporary table in memory. If the temporary table size exceeds this value, the temporary table is automatically converted to a disk-based table (disk Based table).

Maximum number of threads for the Thread_cache_size=8 # cache. When a client connection is disconnected, the thread that handles the client task is put back to the cache if the total number of client connections is less than this value. In high concurrency situations, if the value is set too small, many threads are created frequently, the cost of thread creation becomes larger, and query efficiency is reduced. In general, if there is good multithreading on the application side, this parameter will not improve the performance much.

# MyISAM Related parameters

myisam_max_sort_file_size=100g # mysql maximum size of temporary files allowed when rebuilding indexes

Myisam_sort_buffer_size=68m

key_buffer_size=54m # Key buffer size, which is used to cache the index block of the MyISAM table. Determines the speed of database indexing processing (especially index reads)

READ_BUFFER_SIZE=64K # The size of the buffer used when scanning the MyISAM table full table. Allocate for each thread (provided a full table scan is performed). When sorting queries, MySQL first scans the buffer to avoid disk search, improve query speed, if you need to sort large amounts of data, you can adjust the value appropriately. However, MySQL will release this buffer space for each client connection, so try to set this value as appropriate to avoid excessive memory overhead.

read_rnd_buffer_size=256k

sort_buffer_size=256k # Connection level parameters (configured for each thread), 500 threads will consume 500*256k sort_buffer_size.

# InnoDB Related parameters

INNODB_ADDITIONAL_MEM_POOL_SIZE=3M # InnoDB The size of the memory pool used to store meta data information, typically without modification

Innodb_flush_log_at_trx_commit = 1 # Transaction-related parameters, if the value is 1, InnoDB writes the transaction log to disk (disk IO consumes large) for each commit, thus ensuring full acid characteristics. If set to 0, the transaction log writes to the memory log and the memory log is written to Disk 1 times/sec. If set to 2, the transaction log is written to the memory log on each commit, but the memory log is written to disk for 1 times/sec.

INNODB_LOG_BUFFER_SIZE=2M # INNODB log data buffer size, if the buffer is full, the cached log data is written to disk (flush). Because the disk is usually written at least 1 seconds, it is not necessary to set it too large, even if it is a long transaction.

innodb_buffer_pool_size=105m # InnoDB uses a buffer pool to cache indexes and row data. The greater the value is set, the less disk IO is. This value is typically set to 80% of the physical memory.

innodb_log_file_size=53m # Each InnoDB the size of the transaction log. Generally set to Innodb_buffer_pool_size 25% to 100%

Innodb_thread_concurrency=9 # InnoDB Kernel maximum number of concurrent threads