Short-Connection Issues
Normally in short-connection mode, after connecting to the database, only a few SQL statements are executed before disconnecting, then reconnecting the next time they are needed. If you're using short connections, you may see a sudden surge in the number of connections during peak business hours.
Short connection models carry a risk. If the database processes slowly, the number of connections can skyrocket. The
max_connections parameter is used to control the maximum number of concurrent connections that a MySQL instance can have. If this value is exceeded, the system will refuse subsequent connection requests and return an error message "Too many connections". For requests that are denied connection, from a business perspective, the database is not available.The purpose of designing the
max_connections parameter is to protect MySQL. If you set it too high, allowing more connections to come in, the system's load might increase further. A large amount of resources would be consumed on permissions verification, which could backfire.Solutions
- Handle threads that occupy connections but are idle.
max_connections is not concerned with who is running, as long as a connection is established, it takes up a counter position. For those connections that don't need to be maintained, you can actively kick them off with kill connection, or set wait_timeout in advance. The wait_timeout parameter signifies that if a thread is idle for a certain number of seconds, MySQL will directly disconnect it.If there are too many connections, you can prioritize disconnecting connections that have been idle for too long outside of transactions. If that's not enough, consider disconnecting connections that have been idle for too long inside transactions.
- Reduce the consumption during the connection process.
You can skip the permissions verification stage.
The method to skip permissions verification is to restart the database and start it with the
--skip-grant-tables parameter. In this way, the entire MySQL will skip all permission verification stages, including during the connection process and the statement execution process.Slow Query Performance Issues
In MySQL, slow queries that can cause performance problems typically fall into one of three categories:
- Poorly designed indexes;
- Poorly written SQL statements;
- MySQL has chosen the wrong index.
Solutions
Poorly Designed Indexes
Starting with MySQL 5.6, creating indexes supports Online DDL. For situations where the database is already hung up due to this statement during peak periods, the most efficient method is to directly execute the
alter table statement.Ideally, you should first execute this on the standby database. Suppose your service is a master-slave setup, with Master A and Slave B, the rough process of this plan is:
- Execute
set sql_log_bin=offon Slave B, i.e., don't write binlog, then execute thealter tablestatement to add an index;
- Execute master-slave switch;
- Now, B is the master and A is the slave. Execute
set sql_log_bin=offon A, then execute thealter tablestatement to add an index.
Wrong Index Chosen
The emergency plan is to force the index on this statement with
force index.
