MySQL my. CNF configuration recommendations

 

/Usr/local/share/MySQL contains 5 my-xxxx.cnf files

Minimum my-small.cnf configuration installation, memory limit 64 m, minimum data volume

My-large.cnf memory = 512 m

My-medium.cnf 32 m <memory <64 m, or memory 128 M, but the database and web server public memory

My-huge.cnf 1G <memory <2g, the server mainly runs MySQL

My-innodb-heavy-4G.cnf Max configuration installation with at least 4 GB memory

 

 

_________________________

Large my-huge.cnf is designed for the database in the enterprise, dedicated Database Server
My-innodb-heavy-4G.cnf 4G
My-large.cnf is designed for a computer dedicated to a SQL database
The my-medium.cnf is designed for medium-sized databases ..
A small my-small.cnf is designed for small databases. This model should not be used in databases that contain common projects.

Copy the file suitable for your machine configuration to/etc/My. CNF

 

Recommended my-medium.cnf for test notebook x61t

__________________________

The MySQL configuration file is/etc/My. CNF. modify it to configure MySQL.
/Etc/My. CNF comes from the following files:

If your memory is less than or equal to 64 MB, copy/usr/local/share/MySQL/my-small.cnf to/etc/My. CNF
# This is for a system with little memory (<= 64 m) where MySQL is only used
# From time to time and it's important that the mysqld daemon
# Doesn' t use much resources.

If the memory is 128 M, copy/usr/local/share/MySQL/my-medium.cnf to/etc/My. CNF
# This is for a system with little memory (32 m-64 m) where MySQL plays
# An important part, or systems up to 128 m where MySQL is used together
# Other programs (such as a web server)

If the memory is 512 M, copy/usr/local/share/MySQL/my-large.cnf to/etc/My. CNF
# This is for a large system with memory = 512 m where the system runs mainly
# MySQL.

If the memory is 1-2g, copy/usr/local/share/MySQL/my-huge.cnf as/etc/My. CNF
# This is for a large system with memory of 1g-2g where the system runs mainly
# MySQL.

If the memory is 4 GB, copy/usr/local/share/MySQL/my-innodb-heavy-4G.cnf to/etc/My. CNF
# This is a MySQL example config file for systems with 4 GB of memory
# Running mostly MySQL using InnoDB only tables and memory Ming Complex
# Queries with few connections.

However, there are so many MySQL parameters that we need to know their specific meanings to make specific adjustments based on actual problems.
------------
We can use show variables; To view system parameters and use show status; to judge the system status.
------------
Let's take a look at the impact of the table_cache parameter on performance. Extract the description of it in the my-innodb-heavy-4G.cnf:
# The number of open tables for all threads. Increasing this value
# Increases the number of file descriptors that mysqld requires.
# Therefore you have to make sure to set the amount of open files
# Allowed to at least 4096 in the variable "Open-files-limit" in
# Section [mysqld_safe]
Table_cache = 2048
For example, when the system is busy, we show variables; check the value of table_cache, and then show status; we find that the value of open_tables is similar to that of table_cache, and opened_tables has been increasing, it indicates that our table_cache settings are too small.

Vn: F [1.9.0 _ 1079]

• Recommended settings:

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.The mysql client library will overwrite and try to connect to the local socket .(
We can code from PhP. ini:

 ; Default socket name for local MySQL connects.  If empty, uses the built-in; MySQL defaults.mysql.default_socket = /tmp/mysql.sock

By default, Unix accesses/tmp/MySQL. Sock)

• Some options are described as follows:

My. CNF does not exist by default. You can see it in/usr/local/share/MySQL:

1. My-huge.cnf
2. My-innodb-heavy-4G.cnf
3. My-large.cnf
4. My-medium.cnf
5. My-small.cnf

And other files. copy the files that are suitable for your machine configuration to the/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 to observe the 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!