My. ini (my. cnf) and mysql Optimization Guide _ MySQL

1. although 32000 is written here. However, the maximum number of connections allowed by the actual MySQL server is 16384. 2. except max_connections, the above configurations should be configured according to your system's own needs. 3. the maximum number of connections allowed is added, it does not increase the system consumption much. 4. MySQL optimization

Mysql optimized

So let's talk about this first. In fact, this is very complicated. for different websites, their online, traffic, number of posts, network conditions, and machine configurations
Optimization is not completed at one time, so you need to constantly observe and debug the optimization to achieve the best results.
The following are two optimization measures of Asiwish and laogao.

You only need to modify the c:/winnt/my. ini of win2000 or c:/windows/my. ini of win2003 to implement these settings.

First, we open this file and change the bottom password to another one. This does not affect the database itself. it is mainly intended to prevent some interested users from obtaining this password, which is not good for us.

Then let's take a look at two examples, some of which are set up in English to understand what it means,
I don't know how to query words. I'm too lazy to compare them one by one.
One is online for thousands of people and 1 GB of memory

# This File was made using the WinMySQLAdmin 1.4 Tool
#2004-2-23 16:28:14

# Uncomment or Add only the keys that you know how works.
# Read the MySQL Manual for instructions

[Mysqld]
Basedir = D:/mysql
# Bind-address = 210. 5 .*.*
Datadir = D:/mysql/data
# Language = D:/mysql/share/your language directory
# Slow query log # =
# Tmpdir # =
# Port = 3306
Set-variable = max_connections = 1500
Skip-locking
# Skip-networking
Set-variable = key_buffer = 384 M
Set-variable = max_allowed_packet = 1 M
Set-variable = table_cache = 512
Set-variable = sort_buffer = 2 M
Set-variable = record_buffer = 2 M
Set-variable = thread_cache = 8
# Try number of CPU's * 2 for thread_concurrency
Set-variable = thread_concurrency = 8
Set-variable = myisam_sort_buffer_size = 64 M
# Set-variable = connect_timeout = 5
# Set-variable = wait_timeout = 5
Server-id = 1
[Isamchk]
Set-variable = key_buffer = 128 M
Set-variable = sort_buffer = 128 M
Set-variable = read_buffer = 2 M
Set-variable = write_buffer = 2 M

[Myisamchk]
Set-variable = key_buffer = 128 M
Set-variable = sort_buffer = 128 M
Set-variable = read_buffer = 2 M
Set-variable = write_buffer = 2 M
[WinMySQLadmin]
Server = D: // mysql/bin/mysqld-nt.exe
This solution is sufficient, but research is required on pconnect and Max connections.
Max_connections does not need to be so big. I personally think that a few hundred is enough. Otherwise, it will increase the burden on the server and often become a machine.
The connection timeout settings should also be adjusted according to the actual situation. you can adjust the settings as needed and then observe the effect.

The following are some suggestions from the old master two years ago.

7. MYSQL optimization (/etc/my. cnf)
1) confirm that the "skip-innodb" and "skip-bdb" parameters are added to the "[mysqld]" section;
2) confirm that the "skip-name-resolve" and "skip-locking" parameters are added to the "[mysqld]" section;
3) If you do not need it, you can stop the binary log (binlog) by commenting out "log-bin;
4) when the memory permits, some parameters are reconfigured. The goal is to concentrate most operations on the memory and avoid disk operations as much as possible, for my MYSQL server, I modified it as follows based on 2 GB memory:

[Mysqld]
Set-variable = key_buffer = 512 M
Set-variable = max_allowed_packet = 4 M
Set-variable = table_cache = 1024
Set-variable = thread_cache = 64
Set-variable = join_buffer_size = 32 M
Set-variable = sort_buffer = 32 M
Set-variable = record_buffer = 32 M
Set-variable = max_connections = 512
Set-variable = wait_timeout = 120
Set-variable = interactive_timeout = 120
Set-variable = max_connect_errorrs = 30000
Set-variable = long_query_time = 1
Set-variable = max_heap_table_size = 256 M
Set-variable = tmp_table_size = 128 M
Set-variable = thread_concurrency = 8
Set-variable = myisam_sort_buffer_size = 128 M

You can fine-tune the status returned by the "show status" command. I mainly pay attention to the values of the following variables. the smaller the value, the better. it is better to be zero :)
Created_tmp_disk_tables
Created_tmp_tables
Created_tmp_files
Slow_queries
In addition, the value of mysql wait_timeout can be set to about 10 if it is set to a large value (as mentioned by big C)

Wait_timeout is the control of idle processes when a connection is established for a long time. as long as the database is in the connection status, it does not intervene. no matter whether there is a query or update operation, it is ideal to set this setting a little and then use pconnect.

In the timeout period, my experience value is 5-20. check your SERVER traffic ~~ (Said Meng Fei)

The larger the access volume, the smaller the value. otherwise, too many idle processes will occupy unnecessary memory.
Set it to 3 on a 15-minute online forum for 3000 people. it is more appropriate to enable pconnect at the same time.

In addition, I will reference another article

Database Connection errors, possible causes and solutions

Analysis

The system cannot connect to the database. The key lies in two data points:
1. maximum number of connections allowed by the database system max_connections. This parameter can be set. If this parameter is not set, the default value is 100. The maximum value is 16384.
2. the current number of connection threads in the database is threads_connected. This is a dynamic change.
For more information about how to view max_connections and max_connections, see.

If threads_connected = max_connections, the database system cannot provide more connections. in this case, if the program wants to create a new connection thread, the database system will reject the connection, if the program does not handle too many errors, an error message similar to the strong altar will appear.

Because the creation and destruction of database connections consume system resources. To avoid opening too many connection threads at the same time, the so-called database connection pool technology is usually used for programming.

However, the database connection pool technology does not prevent connection resources from being exhausted due to program errors.

This usually happens when the program fails to release the database connection resources in time or other reasons cause the database connection resources to be unable to be released. However, it is estimated that this low-level programming error will not occur in the strong altar system.
The easy way to check this error is to constantly monitor the changes of threads_connected when refreshing the strong altar page. If max_connections is large enough, and the value of threads_connected is constantly increasing to reach max_connections, check the program. Of course, if you use the database connection pool technology, threads_connected will not grow to the maximum number of connection threads in the database connection pool.

From the perspective of a strong altar error, it is more likely that the database system fails to be properly configured. The following are some suggestions. For reference

Ask your engineers to change the maximum number of connections allowed for MySQL from 100 to 32000 by default. This will not cause too many connections.

View max_connections

Go to MySQL and run the command: show variables.
Variable value for viewing the maximum number of database connections: max_connections

View threads_connected

Go to MySQL and run the command: show status
Check the value of the currently active connection thread variable: threads_connected

Set max_connections

The setting method is to add the last red line below in the my. cnf file:

---------------------------

[Mysqld]
Port = 3306
# Socket = MySQL
Skip-locking
Set-variable = key_buffer = 16 K
Set-variable = max_allowed_packet = 1 M
Set-variable = thread_stack = 64 K
Set-variable = table_cache = 4
Set-variable = sort_buffer = 64 K
Set-variable = net_buffer_length = 2 K
Set-variable = max_connections = 32000

---------------------------

After modification, restart MySQL. Of course, to ensure correct settings, check max_connections.

Note:
1. although 32000 is written here. However, the actual MySQL server allows a maximum of 16384 connections;
2. in addition to max_connections, the above configurations should be configured according to your system's own needs;
3. added the maximum number of allowed connections, which does not increase the system consumption much.
4. if your mysql instance uses my. ini as the configuration file, the settings are similar, but the settings must be slightly changed.
It can be seen that the optimization of mysql is diversified and must be adjusted flexibly according to different environments. you cannot learn it by yourself.

I wrote this tutorial here. thank you for your support.

If you have any questions, please feel free to raise them.