First Kind
Copy Code code as follows:
#This File was made using the Winmysqladmin 1.4 Tool
#2004-2-23 16:28:14
#Uncomment or ADD only the keys, you know.
#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=384m
Set-variable = max_allowed_packet=1m
Set-variable = table_cache=512
Set-variable = sort_buffer=2m
Set-variable = record_buffer=2m
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=64m
#set-variable = connect_timeout=5
#set-variable = wait_timeout=5
Server-id = 1
[Isamchk]
Set-variable = key_buffer=128m
Set-variable = sort_buffer=128m
Set-variable = read_buffer=2m
Set-variable = write_buffer=2m
[Myisamchk]
Set-variable = key_buffer=128m
Set-variable = sort_buffer=128m
Set-variable = read_buffer=2m
Set-variable = write_buffer=2m
[Winmysqladmin]
Server=d:/mysql/bin/mysqld-nt.exe
This scheme, the overall enough, but in the pconnect and the maximum number of connections, need to study max_connections not necessarily so big, I personally think that hundreds of is enough, or to the server to increase a lot of burden, often will be connected to the setting of the time is also adjusted according to the actual situation, Everyone is free to adjust and see how it works.
Second Kind
7, MYSQL optimization (/ETC/MY.CNF)
1) Confirm that the "Skip-innodb" and "skip-bdb" parameters have been added to the "[Mysqld]" section;
2) Confirm that the "skip-name-resolve" and "skip-locking" parameters have been added to the "[Mysqld]" section;
3 If you do not need to, you can stop the binary log (Binlog), the method is to "Log-bin" commented out;
4 in the case of memory permitting, to reconfigure some parameters, the goal is to focus most operations in memory, as far as possible without disk operation, for my MYSQL server I am the following modified, based on 2G memory:
Copy Code code as follows:
[Mysqld]
Set-variable = key_buffer=512m
Set-variable = max_allowed_packet=4m
Set-variable = table_cache=1024
Set-variable = thread_cache=64
Set-variable = join_buffer_size=32m
Set-variable = sort_buffer=32m
Set-variable = record_buffer=32m
Set-variable = max_connections=512
Set-variable = wait_timeout=120
Set-variable = interactive_timeout=120
Set-variable = max_connect_errors=30000
Set-variable = long_query_time=1
Set-variable = max_heap_table_size=256m
Set-variable = tmp_table_size=128m
Set-variable = thread_concurrency=8
Set-variable = myisam_sort_buffer_size=128m
You can fine-tune the status returned according to the show status command. I mainly notice the values of the following variables, the smaller the better, preferably zero:
Created_tmp_disk_tables
Created_tmp_tables
Created_tmp_files
Slow_queries
In addition, the MySQL wait_timeout that value is set to a large 10 or so can be done (Big C said)
Wait_timeout is the control of idle processes when using long connections. He's not interfering with a database in a connected state. Whether or not there is a query or update operation This setting is a little bit more ideal for using pconnect;)
Timeout time, my experience is 5-20, to see your server's interview volume ~ ~ (Dream Fly said)
The larger the number of accesses, the smaller the value should be, or too many idle processes left to consume unnecessary memory.
Set to 3 on a 15-minute online 3000-person forum to open pconnect at the same time
Third Kind
Too many database connections errors, possible cause analysis and solutions
Analysis
The system cannot connect to the database, it depends on two data:
1, the database system allows the maximum number of max_connections to connect. This parameter can be set. If not set, the default is 100. The maximum is 16384.
2, the database current connection thread number threads_connected. This is a dynamic change.
See Max_connections, Max_connections of the way after.
If the threads_connected = = max_connections, the database system can not provide more connection number, at this time, if the program also want to create a new connection thread, the database system will reject, if the program did not do too much error handling, will appear similar to the strong altar of information.
Because the connection to the database is created and destroyed, the resources of the system are consumed. And to avoid the simultaneous opening of too many connection threads at the same time, programming is now generally using the so-called database connection pooling technology.
However, database connection pooling technology does not prevent program errors from depleting the connection resources.
This situation usually occurs when the program fails to release the database connection resources or other causes in time for the database connection resources to be released, but the high altar system does not expect this low-level programming error to occur.
The easy way to check this error is to constantly monitor threads_connected changes as you refresh the strong altar page. If the max_connections is large enough and the threads_connected value is increasing to max_connections, then the procedure should be checked. Of course, if you are using database connection pooling technology, threads_connected grows to the maximum number of connection threads in the database connection pool, no longer grows.
Judging from the strong altar, the larger possibility is that the database system could not be properly configured. Here are some suggestions. For reference
Ask your engineers to turn MySQL's maximum allowable number of connections from the default of 100 to 32000. This will not be the problem of too many connections.
View Max_connections
Enter MySQL, with command: show variables
To view a variable value for the maximum number of connections to a database: Max_connections
View threads_connected
Enter MySQL, with command: Show status
To view the current active connection thread variable value: threads_connected
Set Max_connections
The Setup method is to add the following last red line in the My.cnf file:
--------------------------------------------------------------------------------
Copy Code code as follows:
[Mysqld]
port=3306
#socket =mysql
Skip-locking
Set-variable = key_buffer=16k
Set-variable = max_allowed_packet=1m
Set-variable = thread_stack=64k
Set-variable = table_cache=4
Set-variable = sort_buffer=64k
Set-variable = net_buffer_length=2k
Set-variable = max_connections=32000
--------------------------------------------------------------------------------
After the modification, restart MySQL. Of course, to make sure the settings are correct, you should check out the max_connections.
Attention:
1, although this is written in 32000. But the actual MySQL server allows the maximum number of connections 16384;
2, in addition to Max_connections, the above other configuration should be based on your system needs to be configured, do not adhere to;
3, add the maximum allowable connection number, the system consumption is not increased.
4, if your MySQL with My.ini as a configuration file, set similar, but the format should be slightly modified.
Visible, the MySQL optimization, is diverse, and according to the environment, must be flexible adjustment, we can not mechanically, you slowly realize it
