In a normal short connection mode, once connected to the database, only a few SQL statements are executed before the connection is severed. The connection is re-established when needed. During peak times in a short connection model, there could be a sudden spike in connections.

Temporarily Enhancing MySQL Performance

In a normal short connection mode, once connected to the database, only a few SQL statements are executed before the connection is severed. The connection is re-established when needed. During peak times in a short connection model, there could be a sudden spike in connections.

Dangers of Short Connection Model

  • The main risk is that if the database processing slows down slightly, connection numbers can surge.
  • The max_connections parameter controls the upper limit of simultaneous connections to a MySQL instance. Exceeding this results in the system denying further connection requests with the error “Too many connections”. From a business perspective, this means the database is unavailable.
  • Designing max_connections aims to protect MySQL. Increasing this parameter excessively could backfire, increasing system load with resources wasted on permissions checks.

Solutions

1. Deal with Idle Connections

max_connections counts all connections, regardless of their activity. Unnecessary connections can be forcibly removed using kill connection or by setting wait_timeout. If connections are excessive, start by disconnecting those idle outside of transactions. If still necessary, consider disconnecting those idle within transactions.

2. Reduce Connection Overheads

Bypass the permissions check phase during the connection process. Restart the database using the --skip-grant-tables parameter. This allows MySQL to skip all permission checks during both connection and query execution phases.

Slow Query Performance Issues

In MySQL, performance issues due to slow queries can arise from:

  1. Poorly designed indices.
  2. Poorly written SQL statements.
  3. MySQL selecting the wrong index.

Solutions

1. Index Design

From MySQL 5.6 onwards, index creation supports Online DDL. During peak times when a query has slowed the system, the most efficient solution is executing the alter table statement. Ideally, execute this on a backup. For a one-primary-one-backup system (primary A, backup B):

  • On backup B, execute set sql_log_bin=off (not writing binlog) and then execute alter table to add an index.
  • Execute primary-backup switching.
  • Now, primary is B and backup is A. On A, execute set sql_log_bin=off and then execute alter table to add an index.

2. Incorrect Index Selection

The emergency solution is to use the force index for the statement.