Key_buffer
The index block is buffered and shared by all threads. Key_buffer_size is the buffer size used for index blocks. You can increase the size of indexes that can be better processed (for all reads and multi-Rewrite) so that you can afford that much. If you make it too large, the system will begin to change pages and it will really slow down. The default value is 8388600 (8 m). My MySQL host has 2 GB of memory, so I changed it to 402649088 (400 mb)
Max_connections
Number of customers allowed simultaneously. Increase the number of file descriptors required by mysqld. This number should be added. Otherwise, you will often see the too connector connections error. The default value is 100.
Max_allowed_packet
The maximum size of a package during import. The message buffer is initialized to net_buffer_length, but can be added to max_allowed_packet as needed. By default, if this value is too small, a large (possibly incorrect) package will be captured. If you are using a large blob column, you must add this value. It should be as big as the largest blob you want to use.
Table_cache
Number of tables opened for all threads. Increase this value to increase the number of file descriptors required by mysqld. MySQL requires two file descriptors for each unique opened table. The default value is 64. I changed it to 512.
Configure the parameters of the MyISAM table:
Record_buffer_size
Each thread that performs an ordered scan allocates a buffer of this size to each table it scans. If you perform many sequential scans, you may want to increase the value. The default value is 131072 (128 K). I changed it to 16773120 (16 m)
Sort_buffer_size
Each thread that needs to be sorted allocates a buffer of this size. Add this value to accelerate the order by or group by operation. The default value is 2097144 (2 m). I changed it to 16777208 (16 m)
Read_buffer_size
Each thread that does a Sequential Scan allocates a buffer of this size for each table it scans. If you do have sequential scans, you might want to increse this value
Read_rnd_buffer_size
When reading rows in sorted order after a sort, the rows are read through this buffer to avoid disk seeks. setting the variable to a large value can improve order by performance by a lot. however, this is a buffer allocated for each client, so you should
Not set the global variable to a large value. Instead, change the session variable only from within those clients that need to run large queries.
Bulk_insert_buffer_size
This parameter is introduced in 4.0.3. MyISAM uses a tree buffer to accelerate a large number of inserts, such as insert... Select, insert... Values (...), Values (...),..., Load data infile. This parameter specifies the buffer size. The default value is 8 M. If it is set to 0, this optimization is not used. If you do not use the MyISAM table, you can set it to 0.
Myisam_sort_buffer_size
Configure InnoDB table parameters
Innodb_buffer_pool_size
Innodb_buffer_pool_size is equivalent to key_buffer_size for MyISAM tables. InnoDB uses this parameter to specify the memory size to buffer data and indexes. For a separate MySQL database server, you can set this value to 80% of the physical memory. According to the MySQL manual, for 2 GB memory machines, the recommended value is 1 GB (50%)
Innodb_flush_log_at_trx_commit
This value specifies how InnoDB logs are recorded. If set to 1, MySQL writes transaction logs to the disk when each transaction is committed. If it is set to 0 or 2, logs are written to the disk every second. (The difference between 0 and 2 is unclear ). In actual tests, it is found that this value has a great impact on the speed of data insertion. If it is set to 2, it takes only 2 seconds to insert 10000 records, and if it is set to 0, it only takes 1 second, it takes 229 seconds to set it to 1. Therefore, we recommend that you merge the insert operation into a transaction as much as possible in the MySQL manual, which can greatly increase the speed. According to the MySQL manual, you can set this value to 0 if the latest transaction is lost.
Innodb_log_file_size
The size of the buffer that InnoDB uses to write to the log files on disk. sensible values range from 1 MB to 8 Mb. the default is 1 MB. A large log buffer allows large transactions to run without a need to write the log to disk before the transactions commit.
Thus, if you have big transactions, making the log buffer larger will save disk I/O. According to the MySQL manual, the recommended value is 25% of innodb_buffer_pool_size.
Note: When you reset this value, it seems that you want to delete the original file.
Innodb_log_buffer_size
The size of the buffer that InnoDB uses to write to the log files on disk. sensible values range from 1 MB to 8 Mb. the default is 1 MB. A large log buffer allows large transactions to run without a need to write the log to disk before the transactions commit.
Thus, if you have big transactions, making the log buffer larger will save disk I/O. According to the MySQL manual, the recommended value is 8 Mb.
Innodb_additional_mem_pool_size
This parameter specifies the memory pool size that InnoDB uses to store data dictionaries and other internal data structures. The default value is 1 MB. Generally, it doesn't need to be too large. It only needs to be enough. It should be related to the complexity of the table structure. If not, MySQL will write a warning message in the error log. According to the MySQL manual, the recommended value for 2 GB memory machines is 20 mb.
Thread_cache_size
The number of threads that can be reused. If yes, the new thread is obtained from the cache. If there is space when the connection is disconnected, the customer's thread is placed in the cache. If there are many new threads, this variable value can be used to improve performance. By comparing variables in connections and threads_created states, you can see the role of this variable. I set it to 80.
Query_cache_size
Query Buffer. MySQL stores the SELECT statement and query result in the buffer. In the future, the same SELECT statement (case sensitive) will be read directly from the buffer. Check the status value qcache _ * to check whether the query_cache_size setting is reasonable (the preceding status value can be obtained using show status like 'qcache % ). If the qcache_lowmem_prunes value is very large, it indicates that the buffer is often insufficient. If the qcache_hits value is also very large, it indicates that the query buffer is frequently used. In this case, you need to increase the buffer size; if the qcache_hits value is small, it indicates that your query repetition rate is very low. In this case, the Query Buffer will affect the efficiency, so you can consider not to use the query buffer. In addition, adding SQL _no_cache to the SELECT statement explicitly indicates that no Query Buffer is used. Parameters related to query buffering include query_cache_type, query_cache_limit, and query_cache_min_res_unit.
Query_cache_type specifies whether to use the Query Buffer. It can be set to 0, 1, and 2. This variable is a session-level variable. Query_cache_limit specifies the buffer size that can be used by a single query. The default value is 1 MB. Query_cache_min_res_unit is introduced after version 4.1. It specifies the minimum unit for allocating the buffer space. The default value is 4 K. Check the status value qcache_free_blocks. If the value is very large, it indicates that there are many fragments in the buffer. This indicates that the query results are relatively small. In this case, reduce query_cache_min_res_unit.
Query_cache_limit
Buffer size available for order query. The default value is 1 MB.
Query_cache_min_res_unit
Specifies the minimum unit for allocating buffer space. The default value is 4 K.
Check the status value qcache_free_blocks. If the value is very large, it indicates that there are many fragments in the buffer. This indicates that the query results are relatively small. In this case, reduce query_cache_min_res_unit.
Thread_concurrency
Set the number of your CPUs to X2. For example, if there is only one CPU, then thread_concurrency = 2
Skip-InnoDB
Remove InnoDB support
Skip-bdb
Remove bdb transaction table support
Skip-name-resolve
Solve the problem that MySQL cannot be connected remotely.
Max_allowed_packet
Allowed size of information package used in information exchange (for example, import table)
A configuration is attached.
[mysqld]port = 3306socket = /tmp/mysql.sockskip-lockingkey_buffer = 256Mmax_allowed_packet = 8Mtable_cache = 1024sort_buffer_size = 64Mnet_buffer_length = 8Kread_buffer_size = 16Mread_rnd_buffer_size = 16Mmyisam_sort_buffer_size = 128Mdefault-table-type=innodbinnodb_buffer_pool_size = 1024Minnodb_additional_mem_pool_size = 128Minnodb_flush_log_at_trx_commit = 0innodb_log_file_size = 256Minnodb_log_buffer_size = 8Mlong_query_time = 1log-slow-queries = /usr/local/mysql/var/log-slow.logskip-name-resolvelog_bin_trust_routine_creators=1max_connections = 2048query_cache_size = 128Mrecord_buffer = 32Mback_log = 500#interactive_timeout = 7200interactive_timeout = 2880000thread_cache_size = 80#wait_timeout = 720wait_timeout = 2880000max_connect_errors=100tmp_table_size = 512Mlog = /usr/local/mysql/var/query.log
