Shulou(Shulou.com)06/01 Report--

Background: mariadb,mycat middleware.

Problem: too many DB connections; developers use connection pooling to connect mycat

DB items to be optimized: interactive_timeout,wait_timeout is the default value of 8 hours.

Mycat configuration: 100sharding libraries, and other business libraries. Now the sharding library is used in 16 slices, but it has not yet been used later.

Current maximum number of DB connections: 3000

Mycat version: when the mycat version on the front line is 1.5.8, it is recommended to use the most stable mycat1.6.5 version online in the future.

After meeting with DB and developers, we know that the time of these two timeout cannot be shortened, so conventional optimization methods cannot be used:

The number of normal DB connections is 1000, and the two timeout of the database is 300 Murray 500. The parameters can take effect dynamically globally.

There are always too many connections in the company's online DB some time ago. Normally, the number of connections is 1000, which has been able to meet most of the needs.

When normal means cannot be used, then find out why DB has too many connections.

1. Audit log

The audit log has been deployed on DB. Please move the audit log deployment: audit log deployment, you can see who the bad boy who did something bad is in the audit log!

It was not saved because of the time constraint. However, it is found from the audit log that a large number of access connections sql is' select 1', which is also the connection between mycat and mysql.

And the link is connected to a large number of unused physical libraries.

So far, the audit log can only be judged here.

2. DB level

The processlist table in the mariadb physical library information_schema records connection-related information, such as DB,HOST,INFO,STATUS. And can count the number of connections in a specific library.

After query, it is found that many unused DB connections have not been released, accounting for about 60% of the total number of connections. The connection is still reconnected after using the script "kill id;".

This situation is very awkward and I can't find the reason.

3. Audit log

Combined with the audit log to see where the connection came from or select 1 sent by mycat, we can temporarily locate the heartbeat problem of mycat, combined with the heartbeat algorithm of online search.

One of them is about the unused DB connection algorithm: if the current DB has been accessed all the time, the heartbeat linking the DB is not executed, and the unused DB link reconnects to the DB in 300 seconds.

This algorithm is a personal understanding, because the ability is limited, code reading ability is low, can only understand a general; now can also judge a general direction.

4. Change mycat configuration

Current mycat configuration: datanode

Unused datanode needs to be reduced, and after the reduction, it is found that the number of connections has not decreased.

The number of connections will grow again after kill id;. Determine that changing datanode alone is not the solution to the problem.

Change:

Delete the unused ones and kill id; the operation again. Unused connections no longer appear.

So far, the experimental text has more descriptions and less descriptions, which can only be used as a reference for readers.

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.