MySQL configuration file my. CNF

MySQL parameter optimization is difficult for new users. In fact, this parameter optimization is very complicated. For different websites, their online volume, access volume, and number of posts, network Conditions and machine hardware configurations are related. Optimization cannot be completed at one time. You need to constantly observe and debug the optimization to obtain the best results.

Next, let's talk about the hardware and Forum information of my servers,

CPU: 2 quad-core Intel Xeon 2.00 GHz

Memory: 4 GB DDR

Hard Disk: SCSI 146 GB

Forum: the number of online members is generally about 5000-the highest record is 13264.

Next, based on the above hardware configuration combined with an optimized my. CNF analysis: Some parameters may have to be adjusted based on the changes in the forum and the programmer's program.

[Mysqld]

Port = 3306

Serverid = 1

Socket =/tmp/MySQL. Sock

Skip-locking # Avoid MySQL external locks, reduce the chance of errors, and enhance stability.

Skip-name-resolve

Prohibit MySQL from performing DNS resolution on external connections. This option can eliminate the time for MySQL to perform DNS resolution. However, if this option is enabled, IP addresses are required for all remote host connection authorizations. Otherwise, MySQL cannot process connection requests normally!
Back_log = 500
The number of connections that MySQL can have. When the main MySQL thread receives a lot of connection requests in a very short period of time, this works, and then the main thread takes some time (although very short) to check the connection and start a new thread.
The back_log value indicates how many requests can be heap before MySQL temporarily stops answering new requests
Stack. Only if you want to have many connections in a short period of time, you need to increase it. In other words, this value is the size of the listener queue for the incoming TCP/IP connection. Your operating system has
Its own limits. Trying to set back_log to be higher than your operating system limit will be invalid. When you observe your host process list, a large number of 264084 processes are found. |
Unauthenticated User | XXX. XXX | null | connect
| Null | login | when a null process is to be connected, the value of back_log must be increased. The default value is 50. I will change it to 500.
Key_buffer_size = 384 m
#
Key_buffer_size specifies the size of the buffer used for the index. increase the size of the index that can be better processed (for all reads and multiple writes) so that you can afford that much. If you make it too large
The system will begin to change pages and it will be really slow. This parameter can be set to 512 MB or MB for servers with around 4 GB of memory. Check the key_read_requests and
Key_reads to check whether the key_buffer_size setting is reasonable. Proportional key_reads/
Key_read_requests should be as low as possible, at least, is better (the above status values can use show
Status like 'key _ read % ). Note: If this parameter value is set too large, the overall efficiency of the server will be reduced!
Max_allowed_packet = 32 m
It is safe to increase the value of this variable because additional memory is allocated only when necessary. For example, if you issue a long query or mysqld must return a large result, mysqld will allocate more memory. The small default value of this variable is a preventive measure to capture error packets between the client and the server, and ensure that memory overflow is not caused by accidental use of large information packets.
Table_cache = 512
Table_cache specifies the table cache size. When MySQL accesses a table
There is still space, and the table is opened and put into it, so that you can access the table content more quickly. Check the status values open_tables and opened_tables of the peak time.
Whether to increase the value of table_cache. If you find
Open_tables is equal to table_cache, and opened_tables is growing, you need to increase the value of table_cache (
You can use show status
Like
'Open % tables ). Note that you cannot blindly set table_cache to a large value. If it is set too high, the file descriptor may be insufficient, resulting in performance
Unstable or connection failed.
Sort_buffer_size = 4 m
The buffer size that can be used to query sorting. Note: The allocated memory corresponding to this parameter is exclusive to each connection! If there are 100 connections, the total size of the actually allocated sort buffer is 100 × 4 = 400 mb. Therefore, we recommend that you set the size of a server with around 4 GB to 4-8 Mb.
Read_buffer_size = 4 m
The buffer size that can be used by the read query operation. Like sort_buffer_size, the allocated memory corresponding to this parameter is exclusive to each connection!
Join_buffer_size = 8 m
The buffer size that can be used by the Joint query operation. The same as sort_buffer_size, the allocated memory corresponding to this parameter is exclusive to each connection!
Myisam_sort_buffer_size = 64 m
Buffer required for re-sorting when the MyISAM table changes
Query_cache_size = 64 m
Specify the size of the MySQL Query Buffer. You can run the following command on the MySQL console:
#> Show variables like '% query_cache %'; #> show status like 'qcache % '; # If the qcache_lowmem_prunes value is very large, it indicates that the buffer is insufficient frequently;
If the qcache_hits value is very large, it indicates that the query buffer is used very frequently. If the value is small, it will affect the efficiency, you can consider not to use the Query Buffer; qcache_free_blocks, if the value is very large, it indicates that there are many fragments in the buffer.
Thread_cache_size = 64
The number of threads that can be reused. If yes, the new thread gets it from the cache. If the connection is disconnected
And the customer's line is placed in the cache. If there are many new threads, this variable value can be used to improve performance. By comparing connections and threads_created
Status variable, you can see the role of this variable
Tmp_table_size = 256 m
Max_connections = 1000
Specifies the maximum number of connection processes allowed by MySQL. If the too connector connections error message appears frequently during Forum access, you need to increase the value of this parameter.
Max_connect_errorrs = 10000000
If an error connection is interrupted for the same host that exceeds the value of this parameter, the connection to the host is disabled. To unban the host, run: flush host ;.
Wait_timeout = 10
Specify the maximum connection time of a request. For servers with around 4 GB of memory, you can set it to 5-10.
Thread_concurrency = 8

In this example, the server has two physical CPUs, and each physical CPU supports h.t hyper-threading, therefore, the actual value is 4x2 = 8.
Skip-networking
Enabling this option can completely disable the MySQL TCP/IP connection mode. If the Web server accesses the MySQL database server remotely, do not enable this option! Otherwise, the connection will fail!
Long_query_time = 10
Log-Slow-queries =
Log-queries-not-using-Indexes
Enable slow query log)
Slow query logs are useful for queries with tracing problems. It records all long_query_time queries. If needed, you can also record records that do not use indexes. The following is an example of slow log query:
To enable slow query logs, you must set the log_slow_queries, long_query_times, and log-queries-not-using-indexes parameters.
Log_slow_queries specifies the log file. If the file name is not provided, MySQL will generate the default
File Name. Long_query_times specifies the threshold for slow queries. The default value is 10 seconds. Log-queries-not-using-indexes is 4.1.0
The introduced parameter that indicates that the record does not use an index for queries. Set long_query_time = 10
You can also use the show status command to view the MySQL Status values and their meanings:
Use the show STATUS Command
Meaning:
Number of times the aborted_clients client has been disconnected illegally
Aborted_connects connection failure count
The number of times the com_xxx XXX command is executed. There are many
Number of connections from connections to MySQL
Created_tmp_disk_tables temporary table created on the disk
Created_tmp_tables temporary table created in memory
Created_tmp_files temporary files
Key_read_requests the number of requests to read a key block from the cache
Key_reads the number of physical reads of a key block from disk
Number of connections simultaneously used by max_used_connections
Open_tables open tables
Open_files files
Opened_tables Open Table
Number of queries submitted by questions to the server
Sort_merge_passes if the value is large, you should increase the sort_buffer value in my. CNF.
The number of seconds that the uptime server has been working on.
Suggestions for improving performance:

1. If opened_tables is too large, increase table_cache in my. CNF.

2. If key_reads is too large, you should increase the key_buffer_size in my. CNF. You can use key_reads/key_read_requests to calculate the cache failure rate.

3. If handler_read_rnd is too large, many of the SQL statements you write need to scan the entire table, but do not use the index key.

4. If threads_created is too large, you need to increase the value of thread_cache_size in my. CNF. You can use threads_created/connections to calculate the cache hit rate.

5. If created_tmp_disk_tables is too large, you need to increase the value of tmp_table_size in my. CNF and replace the disk-based temporary table with a memory-based temporary table.

========================================================== ======================================

What is the storage engine? MySQL data is stored in files (or
Correct compilation methods are important, but they are only part of improving MySQL server performance. Many parameters of the MySQL server affect the performance of the server.
The configuration file is saved so that these parameters automatically take effect each time the MySQL server is started. This configuration file is my. CNF.

The MySQL server provides several examples of the my. CNF file, which can be found in the/usr/local/MySQL/share/MySQL/directory named
My-small.cnf, my-medium.cnf, my-large.cnf and my-huge.cnf. The file name describes the scale.
The applicable system type. For example, if the system memory that runs the MySQL server is small and MySQL is used only occasionally, it is best to use the my-small.cnf configuration file, which
Configuration files tell mysqld
Daemon uses the least amount of system resources. Otherwise, if the MySQL server is used to support a large-scale online mall, the system has 2 GB of memory, then use mysql-huge.cnf
Most suitable.

To use the preceding sample configuration file, we should first copy the most suitable configuration file and name it my. CNF. The copied configuration file can be used in the following three ways:

Global: copy the my. CNF file to the/etc directory of the server. The parameters defined in the file are globally valid, that is, they are valid for all MySQL database servers running on the server.

Local: copying this my. CNF file to [MYSQL-INSTALL-DIR]/var/will make the file valid only for the specified server, where [MYSQL-INSTALL-DIR] indicates the directory for installing MySQL.

User: At last, we can limit the scope of the file to a specified user, so we only need to copy the my. CNF file to the user's root directory.

So, how to set parameters in the my. CNF file? Or further, which parameters can be set? All these parameters have a global impact on the MySQL server.
Parameters are closely related to specific parts of MySQL. For example, the max_connections parameter belongs to the mysqld class. So how can we know this? You only need to execute
Run the following command:

%>/Usr/local/MySQL/libexec/mysqld-help

This command displays various options and parameters related to mysqld. It is very convenient to search for these parameters, because these parameters are in the "possible variables
Option-set-variable (-O)
Are. After finding these parameters, we can set all these parameters in the my. CNF file as follows:

Set-variable = max_connections = 100

The result of this line of code is that the maximum number of connections to the MySQL server at the same time is 100. Do not forget to add a set-variable command in the [mysqld] section of the my. CNF file. For details, see the example in the configuration file.