MySQL configuration Summary

1. Check the machine configuration. The three main parts are CPU, memory, and hard disk.
2. Check MySQL configuration parameters.
3. Check the MySQL row status. You can use the mysqlreport tool to view the status.
4. View MySQL slow queries
After solving the preceding problems in sequence, you can find the program problems.

My. CNF Cache Optimization
Add/modify the following options in my. CNF:

# Cancelling the external lock of the file system
Skip-locking

# Do not perform domain name anti-resolution, pay attention to the resulting permission/authorization issues
Skip-name-resolve

# Index cache, depending on the memory size. If it is an independent DB server, you can set up to 80% of the total memory.
Key_buffer = 512 m

# Total number of connection queues
Back_log = 200
Max_allowed_packet = 2 m

# The total number of opened table caches to avoid overhead caused by frequent opening of data tables
Table_cache = 512

# Buffer required for sorting by each thread
Sort_buffer_size = 4 m

# Buffer required by each thread to read the index
Read_buffer_size = 4 m

# Cache required for re-sorting when the MyISAM table changes
Myisam_sort_buffer_size = 64 m

# Number of cache reusable threads
Thread_caching = 128

# Query result Cache
Query_cache_size = 128 m

# Set the timeout time to avoid persistent connections
Set-variable = wait_timeout = 60

# Maximum number of concurrent threads, CPU x 2
Thread_concurrency = 4

# Record slow queries, and then optimize slow queries one by one
Log-Slow-queries = slow. Log
Long_query_time = 1

# Disable unnecessary table types. Do not add this type if necessary.
Skip-InnoDB
Skip-bdb

My. CNF configuration in MySQL
 
 
Source: chinaunix blog date: (0 comments in total) I want to comment

Introduction to the my. CNF configuration file and parameters in MySQL
For a single running web server, we recommend that you add:
Skip-locking
Skip-name-resolve
Skip-networking
Use "localhost" when connecting to the database in PHP. In this way, the mysql client library will overwrite and try to connect to the local socket .(
We can use PHP. ini
Code:; default socket name for local MySQL connects. If empty, uses the built-in
; MySQL ults.
MySQL. default_socket =/tmp/MySQL. Sock it can be seen that UNIX will access/tmp/MySQL. Sock by default)

 

Some options are described as follows:
My. CNF does not exist by default. You can see it in/usr/local/share/MySQL:

My-huge.cnf

My-innodb-heavy-4G.cnf

My-large.cnf

My-medium.cnf

My-small.cnf and other files. copy the file that fits your machine configuration to/etc/My. CNF or MySQL data directory/My. CNF (/var/DB/MySQL) or ~ /. My. CNF. Detailed descriptions are provided in the file.
[Mysqld]
Port = 3306
Serverid = 1
Socket =/tmp/MySQL. Sock
Skip-locking
# Avoid external locks of MySQL to 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 = 384
Specify the number of possible MySQL connections. When the MySQL main thread receives many connection requests within a short period of time, this parameter takes effect. The main thread takes a short time to check the connection and start a new thread.
The value of the back_log parameter indicates how many requests can be stored in the stack within a short time before MySQL temporarily stops responding to a new request. If the system has many connections in a short period of time, you need to increase the value of this parameter, which specifies the size of the listener queue for the incoming TCP/IP connection. Different operating systems have their own limits on the queue size.
Trying to set back_log to be higher than your operating system limit will be invalid. The default value is 50. We recommend that you set the value to an integer smaller than 512 in Linux.
Key_buffer_size = 256 m
# Key_buffer_size specifies the buffer size used for the index. Increasing the size can improve the index processing performance.
This parameter can be set to 384 m or m for servers with around 4 GB of memory.
Note: If this parameter value is set too large, the overall efficiency of the server will be reduced!
Max_allowed_packet = 4 m
Thread_stack = 256 K
Table_cache = 128 K
Sort_buffer_size = 6 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 × 6 = 600 mb. Therefore, we recommend that you set the size of a server with around 4 GB to 6-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
Table_cache = 512
Thread_cache_size = 64
Query_cache_size = 64 m
Specify the size of the MySQL Query Buffer. You can run the following command on the MySQL console:
Code: #> show variables like '% query_cache % ';
#> Show status like 'qcache % '; if the qcache_lowmem_prunes value is very large, it indicates that there is often insufficient buffer;
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.
Tmp_table_size = 256 m
Max_connections = 768
Specifies the maximum number of connection processes allowed by MySQL. If the too connector connections error is frequently reported during Forum access, you need to increase the value of this parameter.
Max_connect_errorrs = 10000000
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!
 
MySQL's my. CNF optimization instance is MySQL's my. CNF optimization instance. In the architecture of Apache, PHP, and MySQL, MySQL has the greatest impact on performance and is also a key core component. For discuz! The same is true for Forum programs. Whether MySQL settings are reasonably optimized directly affects the speed and carrying capacity of the Forum! At the same time, MySQL is also the most difficult part of optimization. It not only needs to understand some MySQL professional knowledge, but also requires a long period of observation statistics and judgment based on experience, and then set reasonable parameters.

Next, let's take a look at some of the basics of MySQL optimization. MySQL optimization is divided into two parts: one is the optimization of the physical hardware of the server, and the other is the optimization of Mysql itself (My. CNF.

(1) Impact of server hardware on MySQL Performance
A) disk tracing capability (disk I/O). Taking the current high-speed SCSI hard disk (7200 RPM) as an example, this hard disk is theoretically found 7200 times per second, which is determined by the physical characteristics, there is no way to change. MySQL performs a large number of complex query operations every second. You can imagine the disk read/write volume. Therefore, we usually think that disk I/O is one of the biggest factors restricting MySQL performance. For discuz with an average daily access volume of more than 1 million PVS! Forum, due to disk I/O constraints, MySQL performance will be very low! You can consider the following solutions to solve this constraint:
Use a raid-0 + 1 disk array. Do not try raid-5. The efficiency of MySQL on a raid-5 disk array will not be as fast as you expected; discard the traditional hard disk, use a faster flash storage device. After discuz! The company's technical engineering tests show that the use of flash storage devices is about 6-10 times faster than traditional hard disks.
B) For MySQL applications, we recommend that you use a multi-channel symmetric CPU in the S. M. P. architecture. For example, you can use two Intel Xeon GHz CPUs.
C) For a database server using MySQL, we recommend that the server memory be no less than 2 GB. We recommend that you use more than 4 GB physical memory.

(2) MySQL's own factors when the above server hardware constraints are solved, let's see how MySQL's own optimization works. The optimization of MySQL is mainly to optimize and adjust the parameters in its configuration file my. CNF. The following describes some parameters that have a great impact on performance.
Since the optimization settings of the my. CNF file are closely related to the server hardware configuration, we specify a hypothetical server hardware environment:
CPU: 2 Intel Xeon 2.4 GHz
Memory: 4 GB DDR
Hard Disk: SCSI 73 GB

Next, we will describe the optimized my. CNF based on the above hardware configuration:
# Vi/etc/My. CNF
Only the content in the [mysqld] section in the my. CNF file is listed below. The content in other sections has little impact on MySQL running performance, so ignore it.
[Mysqld]
Port = 3306
Serverid = 1
Socket =/tmp/MySQL. Sock
Skip-locking
# Avoid external locks of MySQL to 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 = 384
Specify the number of possible MySQL connections. When the MySQL main thread receives many connection requests within a short period of time, this parameter takes effect. The main thread takes a short time to check the connection and start a new thread.
The value of the back_log parameter indicates how many requests can be stored in the stack within a short time before MySQL temporarily stops responding to a new request. If the system has many connections in a short period of time, you need to increase the value of this parameter, which specifies the size of the listener queue for the incoming TCP/IP connection. Different operating systems have their own limits on the queue size.
Trying to set back_log to be higher than your operating system limit will be invalid. The default value is 50. We recommend that you set the value to an integer smaller than 512 in Linux.

Key_buffer_size = 256 m
# Key_buffer_size specifies the buffer size used for the index. Increasing the size can improve the index processing performance.
This parameter can be set to 384 m or m for servers with around 4 GB of memory.
Note: If this parameter value is set too large, the overall efficiency of the server will be reduced!
Max_allowed_packet = 4 m
Thread_stack = 256 K
Table_cache = 128 K
Sort_buffer_size = 6 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 × 6 = 600 mb. Therefore, we recommend that you set the size of a server with around 4 GB to 6-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
Table_cache = 512
Thread_cache_size = 64
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 there is often insufficient buffer;
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.

Tmp_table_size = 256 m
Max_connections = 768
Specifies the maximum number of connection processes allowed by MySQL. If the too connector connections error is frequently reported during Forum access, you need to increase the value of this parameter.

Max_connect_errorrs = 10000000
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!