MySQL configuration file my.cnf Chinese details with MySQL performance optimization method sharing _mysql

First of all, my server's hardware and forum situation,
Cpu:2 Quad Core Intel Xeon 2.00GHz
Memory: 4GB DDR
Hard drive: SCSI 146GB
Forum: Online membership is generally around 5000 people – the highest record is 13264.
Below, we are based on the above hardware configuration combined with a previous optimization of the MY.CNF to analyze the description: Some parameters may also be based on the changes in the forum and programmers to adjust the program.
[Mysqld]
Port = 3306
ServerID = 1
Socket =/tmp/mysql.sock
skip-locking # Avoid MySQL external locking, reduce error probability and enhance stability.

Skip-name-resolve

To prevent MySQL from DNS resolution of external connections, use this option to eliminate the time that MySQL makes DNS resolution. Note, however, that if this option is turned on, all remote host connection authorizations should be in the IP address mode, otherwise MySQL will not handle the connection request properly!
Back_log = 500
Require MySQL to have the number of connections. This works when the main MySQL thread gets a lot of connection requests in a very short time, and then the main thread takes some time (albeit very short) to check the connection and start a new thread.
The Back_log value indicates how many requests can be on the stack within a short time before MySQL temporarily stops answering the new request. Only if you expect to have a lot of connections in a short time, you need to increase it, in other words, this value is the size of the listening queue for incoming TCP/IP connections. Your operating system has its own limits on the size of this queue. Attempting to set Back_log above your operating system will be ineffective. When you look at your host process list, find a lot of 264084 | Unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | Login | NULL to connect process, it is necessary to increase the value of Back_log. The default value is 50, and I'll change it to 500.
Key_buffer_size = 384M
# KEY_BUFFER_SIZE Specifies the size of the buffer to use for the index, increasing the index it can get better at (for all read and multiple writes), to the extent that you can afford it. If you make it too big, the system will start to change pages and it really slows down. This parameter can be set to 384M or 512M for servers with around 4GB. By checking the status values key_read_requests and Key_reads, you can see if the key_buffer_size settings are reasonable. The proportional key_reads/key_read_requests should be as low as possible, at least 1:100,1:1000 better (the above state values can be obtained using show status like ' key_read% '). Note: The value of this parameter set too large will be the overall efficiency of the server down!
Max_allowed_packet = 32M
Increasing the value of this variable is safe because additional memory is allocated only when needed.For example, mysqld allocates more memory only if you issue a long query or mysqld must return a large result row. The variable's smaller default value is a precaution to capture the error packets between the client and the server, and to ensure that there is no memory overflow caused by accidental use of large packets.
Table_cache = 512
Table_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 placed in it, so that the table content can be accessed more quickly. By checking the status values of peak time open_tables and Opened_tables, you can decide whether to increase the Table_cache value. If you find that open_tables equals Table_cache, and Opened_tables is growing, then you need to add Table_cache values (the above status values can be used show status like ' open% Tables ' Get). Note that Table_cache can not be blindly set to a large value. If set too high, the file descriptor may be insufficient, causing performance instability or connection failure.
Sort_buffer_size = 4M
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 total sorted buffer size that is actually allocated is 100x4 = 400MB. Therefore, for the existence of around 4GB server recommended set to 4-8m.
Read_buffer_size = 4M
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!
Join_buffer_size = 8M
The size of the buffer that the Federated query operation can use, like sort_buffer_size, the allocated memory for this parameter is exclusive to each connection!
Myisam_sort_buffer_size = 64M
MyISAM the buffer needed to reorder when the table is changed
Query_cache_size = 64M
Specifies the size of the MySQL query buffer. You can observe this by performing the following command at the MySQL console:
# > Show VARIABLES like '%query_cache% '; # > Show STATUS like ' qcache% '; # If the value of Qcache_lowmem_prunes is very large, it indicates that the buffer is often insufficient;
If the value of the qcache_hits is very large, it indicates that query buffering is used very frequently, if the value is small but it will affect efficiency, then you can consider not to query buffer; Qcache_free_blocks, if the value is very large, it indicates that there are a lot of fragments in the buffer.
Thread_cache_size = 64
The number of threads stored in that can be reused. If there is, a new thread is obtained from the cache, and if there is space when disconnected, the client's line is placed in the cache. If there are a lot of new threads, in order to improve performance you can have this variable value. By comparing the variables of connections and threads_created states, we can see the effect of this variable
Tmp_table_size = 256M
max_connections = 1000
Specifies the maximum number of connection processes allowed by MySQL. You need to increase the value of a too Many connections error message when you visit the forum frequently.
Max_connect_errors = 10000000
For the same host, if there is an interrupt error connection that exceeds the number of values for this parameter, the host will be blocked from connecting. If the host is required to be lifted, execute: FLUSH host;.
Wait_timeout = 10
Specifies the maximum connection time for a request, and the server with about 4GB of memory can be set to 5-10.
Thread_concurrency = 8
This parameter takes the server logical CPU Quantity x2, in this case, the server has 2 physical CPUs, and each physical CPU supports H.T Hyper-threading, so the actual value is 4x2 = 8
Skip-networking
Turn on this option to completely turn off MySQL TCP/IP connections, and do not turn on this option if the Web server is remotely connected to the MySQL database server! Otherwise you will not be able to connect properly!
Long_query_time = 10
Log-slow-queries =
Log-queries-not-using-indexes
Turn on slow query log (slow query logs)
Slow query logs are useful for tracking queries that have problems. It records all queries that have been checked for Long_query_time, and records that do not use the index if necessary. The following is an example of a slow query log:
Open the slow query log, you need to set parameters log_slow_queries, Long_query_times, log-queries-not-using-indexes.
log_slow_queries Specifies the log file, and if the file name is not provided, MySQL produces the default file name itself. LONG_QUERY_TIMES Specifies the threshold value for a slow query, which defaults to 10 seconds. Log-queries-not-using-indexes is a parameter introduced later in 4.1.0, which indicates that a query that does not use an index is logged. Set long_query_time=10
enclosed please see the MySQL status related values and their meanings using the show status command:
Using the Show status command
The meanings are as follows:
Aborted_clients Client Illegal interrupt connection number
Aborted_connects Connection MySQL Failure count
com_xxx xxx Command execution times, there are many
Number of connections connections to MySQL
Created_tmp_disk_tables temporary tables created on disk
Created_tmp_tables temporary tables created in memory
Created_tmp_files number of temporary files
Key_read_requests the number of requests to read a key blocks from the cache
Key_reads the number of physical reads of a key block from disk
Max_used_connections the number of connections used at the same time
Open_tables Open Table
Open_files Open files
Opened_tables Open Table
Questions the number of queries submitted to the server
Sort_merge_passes If this value is large, you should increase the Sort_buffer value in the MY.CNF
Number of seconds the Uptime server has worked
Recommendations for improving performance:
1. If the opened_tables is too large, the table_cache in the my.cnf should be bigger.

2. If the key_reads is too large, the my.cnf key_buffer_size should be larger. Can calculate the cache failure rate with key_reads/key_read_requests

3. If the handler_read_rnd is too large, then many of the queries in the SQL statement you write are to scan the entire table without playing the role of the index key

4. If the threads_created is too large, it is necessary to increase the value of thread_cache_size in MY.CNF. You can use Threads_created/connections to calculate cache hit rates

5. If the created_tmp_disk_tables is too large, it is necessary to increase the value of tmp_table_size in the MY.CNF and replace the disk-based based temporary table

What is the

===================================================================
Storage engine? The data in MySQL is stored in a file with a variety of technologies (or the correct compilation method is important, but it is only part of improving the performance of the MySQL server.) Many of the parameters of the MySQL server affect the performance of the server, and we can save these parameters to the configuration file, which automatically works every time the MySQL server starts. This configuration file is my.cnf. The
MySQL server provides several examples of my.cnf files that 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. A description of the size in the file name describes the system type that the profile applies to. For example, if you run a MySQL server with few system memory, and MySQL is only occasionally used, then using the MY-SMALL.CNF profile is ideal, and this profile tells Mysqld Daemon to use minimal system resources. Conversely, if the MySQL server is used to support a large-scale online shopping mall, the system has 2G of memory, then the use of MYSQL-HUGE.CNF is most appropriate.
To use the sample configuration file above, we should first copy the configuration file that best fits the requirements and name it my.cnf. This replicated configuration file can be used in three ways:
Global: Copy this my.cnf file to the server's/etc directory, where the parameters defined in the file are globally valid, which is valid for all MySQL database servers running on that server.
Local: Copying this my.cnf file to [mysql-install-dir]/var/] will make the file available only to the specified server, where [Mysql-install-dir] represents the directory where MYSQL is installed.
User: Finally, we can also limit the scope of the file to the specified user, which simply copies the my.cnf file to the user's root directory.
So, how do you set the parameters in the my.cnf file? Or what are some of the parameters that we can set? All of these parameters have a global impact on the MySQL server, but at the same time each parameter is more closely related to the particular part of MySQL. For example, the max_connections parameter belongs to the Mysqld class. So how do you know that? This simply performs the following command:

% >/usr/local/mysql/libexec/mysqld–help
This command will display various options and parameters related to mysqld. It is convenient to find these parameters because they are all behind the line "Possible variables for option–set-variable (-O) are". Once these parameters are found, we can set all of these parameters in the My.cnf file as follows:

Set-variable = max_connections=100

The effect of this line of code is that the maximum number of connections to the MySQL server at the same time is limited to 100. Don't forget to add a set-variable directive to the my.cnf file [mysqld] section, see the example in the configuration file.