MySQL. ini parameters in the mysql configuration file

My. ini (my. cnf in Linux). When the mysql server starts, it reads this file and sets relevant runtime environment parameters. My. ini is divided into two parts: ClientSection and ServerSection. ClientSection is used to configure MySQL client parameters. To view the configuration parameters, run the following command: showvariableslike % inno

My. ini (my. cnf in Linux). When the mysql server starts, it reads this file and sets relevant runtime environment parameters. My. ini is divided into Client Section and Server Section. The Client Section is used to configure MySQL Client parameters. To view the configuration parameters, run the following command: show variables like '% inno

My. ini (my. cnf in Linux). When the mysql server starts, it reads this file and sets relevant runtime environment parameters.

My. ini is divided into Client Section and Server Section.

The Client Section is used to configure MySQL Client parameters.

To view the configuration parameters, run the following command:

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

Show status like '% innodb %'; # view innodb-related runtime parameters (for example, the number of currently opened tables and the number of currently opened tables)

Show global status like 'open % tables '; # view global runtime parameters. In addition, global collects statistics on all database instances running on the current mysql server. If global is not added, only the current database instance is counted.

1. Client Section

[Client]

Port = 3306 # Set the default port used by the mysql client to connect to the server

[Mysql]

Default-character-set = utf8 # set the default character set of the mysql client

2. Server Section

[Mysqld]

Port = 3306 # mysql Server default listening (listen on) TCP/IP port

Basedir = "C:/Program Files/MySQL Server 5.5/" # reference path.

Datadir = "C:/Program Files/MySQL Server 5.5/Data" # directory of mysql database Files

Character-set-server = latin1 # The default character set used by the server is the 8-bit latin1 character set.

Default-storage-engine = INNODB # default storage engine used to create a new table

SQL-mode = "STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION" # the SQL mode is strict.

Max_connections = 100 # maximum number of concurrent connections supported by the mysql server (number of users ). However, a connection is always reserved for the Administrator to log on with super permissions, even if the maximum number of connections is reached. If the configuration is Too small and there are many users, the "Too connector connections" error will often occur.

Query_cache_size = 0 # query cache size, used to cache SELECT query results. If many SELECT queries that return the same query results and rarely change the table, you can set query_cache_size to be greater than 0, which greatly improves the query efficiency. If the table data changes frequently, do not use this option, which is counterproductive.

Table_cache = 256 # this parameter is called table_open_cache in Versions later than 5.1.3 and is used to set the number of table high-speed caches. Since 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 cached tables. If the table has been opened in the cache, it will directly access the table in the cache to speed up the query. If the table is not cached, it will add the current table to the cache for query. Before caching, table_cache is used to limit the maximum number of cached tables. If the number of cached tables does not reach table_cache, a new table is added. If this value is reached ,, mySQL releases the cache based on the last query time and query rate of the cache table.

Tmp_table_size = 34 M # maximum size allowed for each temporary table in the memory. If the temporary Table size exceeds this value, the temporary Table is automatically converted to a Disk-Based Table ).

Thread_cache_size = 8 # maximum number of cached threads. When the client connection is disconnected, if the total number of client connections is less than this value, the thread that processes the client task is put back into the cache. In the case of high concurrency, if this value is set too small, many threads will be created frequently, the thread creation overhead will increase, and the query efficiency will also decrease. In general, if the application side has a good multi-threaded processing, this parameter will not greatly improve the performance.

# MyISAM Parameters

Myisam_max_sort_file_size = 100G # maximum temporary file size that can be used for index reconstruction in mysql

Myisam_sort_buffer_size = 68 M

Key_buffer_size = 54 M # Key Buffer size, used to cache index blocks of the MyISAM table. Determines the speed of database index processing (especially index reading)

Read_buffer_size = 64 K # buffer size used for full table scan of the MyISAM table. Allocate each thread (on the premise that a full table scan is performed ). During sorting query, MySql first scans the buffer to avoid disk search and increase the query speed. If you need to sort a large amount of data, you can increase the value accordingly. However, MySql will issue this buffer space for each client connection. Therefore, set this value as much as possible to avoid excessive memory overhead.

Read_rnd_buffer_size = 256 K

Sort_buffer_size = 256 K # connection-level parameters (configured for each thread), 500 threads will consume 500 * K sort_buffer_size.

# InnoDB Parameters

Innodb_additional_mem_pool_size = 3 M # size of the memory pool used by InnoDB to store metadata, which generally does not need to be modified.

Innodb_flush_log_at_trx_commit = 1 # transaction-related parameters. If the value is 1, InnoDB writes transaction logs to the disk at each commit (High Disk IO consumption), ensuring the complete ACID feature. If the value is set to 0, the transaction log is written to the memory log and the memory log is written to the disk once per second. If it is set to 2, transaction logs are written to the memory log at each commit, but the memory log is written to the disk once per second.

Innodb_log_buffer_size = 2 M # InnoDB log data buffer size. If the buffer is full, the log data in the buffer is written to the disk (flush ). Generally, a disk is written at least once per second, so it is not necessary to set a large value, even for long transactions.

Innodb_buffer_pool_size = 105 M # InnoDB uses the buffer pool to cache indexes and row data. The larger the value, the less disk I/O. Generally, this value is set to 80% of the physical memory.

Innodb_log_file_size = 53 M # size of each InnoDB Transaction log. Generally, it is set to 25% to 100% of innodb_buffer_pool_size.

Innodb_thread_concurrency = 9 # maximum number of concurrent threads in the InnoDB Kernel