Shulou(Shulou.com)05/31 Report--

Editor to share with you the example analysis of my.ini and mysql optimization, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Mysql has been optimized

So let's talk about this first. In fact, this is very complicated. For different websites, their online, visits, number of posts, network situation, and machine configuration.

It all matters. Optimization can not be done at once. It needs constant observation and debugging in order to get the best results.

Next, I would like to quote two optimization measures by Asiwish and Lao Gao to show you.

These settings can be achieved by modifying the c:/winnt/my.ini of win2000 or the c:/windows/my.ini of win2003.

First of all, we open this file and change the password entry at the bottom to something else, which has no effect on the database itself, mainly to prevent some interested people from getting this password, which is disadvantageous to us.

Then let's take a look at two examples, some of which have a slight understanding of English, that is, what does it mean?

If you don't know how to look up the words, I don't bother to write instructions one by one.

One is online with thousands of people and 1 gigabyte 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=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 is sufficient as a whole, but in terms of pconnect and maximum number of connections, it needs to be studied.

Max_connections doesn't have to be that big. I personally think a few hundred is enough, otherwise it will add a lot of burden to the server and often crash.

The setting of connection timeout should also be adjusted according to the actual situation, and you can adjust it freely, and then observe the effect.

Here are some suggestions from Lao Gao two years ago. Please refer to them.

7. Optimization of MYSQL (/ etc/my.cnf)

1) confirm that the parameters "skip-innodb" and "skip-bdb" have been added to the "[mysqld]" section

2) confirm that the parameters "skip-name-resolve" and "skip-locking" have been added to the "[mysqld]" section

3) if you don't need it, you can stop the binary log (binlog) by commenting out "log-bin"

4) if memory allows, reconfigure some parameters. The goal is to concentrate most of the operations in memory and avoid disk operations as far as possible. I modified my MYSQL server as follows, based on 2G memory:

[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 by the "show status" command. I mainly pay attention to 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, if the value of mysql wait_timeout is set too high, it is not necessary to do about 10 (what Big C says)

Wait_timeout is the control of idle processes during long-term connections, as long as the database is in the connection state, it does not interfere with it, regardless of whether there is a query or update operation to make this setting smaller and then use pconnect.

During the time of timeout, my test value is 5-20, depending on the quantity of your SERVER. (Mengfei said)

The greater the number of visits, the smaller the value, otherwise too many idle processes will take up unnecessary memory.

On a 15-minute online forum with 3000 people, it is more appropriate to set it to 3 and open pconnect at the same time.

In addition, I would like to quote another article

Errors caused by too many database connections, possible causes and solutions

Analysis.

The system can not connect to the database, the key depends on two data:

1. The maximum number of connections allowed by the database system max_connections. This parameter can be set. If it is not set, the default is 100. The maximum is 16384.

2. The current number of connection threads in the database threads_connected. This is dynamic.

See later on how to check max_connections and max_connections.

If threads_connected = = max_connections, the database system will not be able to provide more connections. At this time, if the program still wants to create a new connection thread, the database system will reject it. If the program does not do too much error handling, an error message similar to Qiangtan will appear.

Because creating and destroying database connections will consume the resources of the system. And in order to avoid opening too many connection threads at the same time, programming now generally uses the so-called database connection pooling technology.

However, database connection pool technology can not avoid program errors leading to the depletion of connection resources.

This usually occurs when the program fails to release database connection resources in time or for other reasons, but Qiangtan system estimates that such low-level programming errors will not occur.

An easy way to check for this error is to constantly monitor threads_connected changes while refreshing the Qantan page. If the max_connections is large enough and the threads_connected value continues to increase to reach max_connections, then it's time to check the program. Of course, if you use database connection pooling technology, threads_connected will no longer grow when it reaches the maximum number of connection threads in the database connection pool.

Judging from the mistakes made by Qiangtan, it is more likely that the database system has not been configured properly. Here are some suggestions. For reference

Ask your engineer to change the maximum number of allowed connections for MySQL from the default 100 to 32000. This will not always have the problem of too many connections.

View max_connections

Enter MySQL and use the command: show variables

View the variable value of the maximum number of connections to the database: max_connections

View threads_connected

Enter MySQL and use the command: show status

View the value of the currently active connection thread variable: threads_connected

Set up max_connections

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

-

[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 take a look at max_connections.

Note:

1. Although 32000 is written here. But the maximum number of connections allowed by the actual MySQL server is 16384

2. In addition to max_connections, the other configurations mentioned above should be configured according to the needs of your system.

3. The maximum number of allowable connections is added, which does not increase the consumption of the system.

4. If your mysql uses my.ini as a configuration file, the setting is similar, but the format should be slightly modified.

It can be seen that the optimization of mysql is diversified and must be adjusted flexibly according to the environment.

The above is all the content of the article "sample Analysis of my.ini and mysql Optimization". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.