After installing mysql, You need to optimize the configuration and open the c: windowsmy. ini file.
After installing mysql, you need to optimize the configuration and open the c: \ windows \ my. ini file.
First
The Code is as follows:
# 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 enough for the whole, but in terms of pconnect and the maximum number of connections, max_connections does not need to be so large. I personally think that a few hundred is enough, otherwise it will increase the burden on the server, it is often said that the setting of connection timeout should also be adjusted according to the actual situation. You can adjust it as needed and then observe the effect.
Second
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:
The Code is as follows:
[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.
Third
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:
--------------------------------------------------------------------------------
The Code is as follows:
[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.
