Ways to quickly increase the load capacity of MYSQL database connections

  • 2020-05-30 21:11:05
  • OfStack

First, limit the concurrent processing of Innodb. If innodb_thread_concurrency = 0, you can change it to 16 or 64 depending on the machine pressure, if
Very big, first change to 16 to let the pressure of the machine down, then slowly increase, adapt to their own business.
Processing method: set global innodb_thread_concurrency=16;

Method 1: (directly modify the my.ini file in the window system)

Go to the MYSQL installation directory and open the MYSQL configuration file my.ini or my.cnf to find max_connections=100 and change it to max_connections=1000

Method 2: (in mysql command mode)

For cases where the number of connections has exceeded 600 or more, consider limiting the number of connections to 1 properly, and let the front-end report 1 wrong, and don't let DB fail.
When DB is in, it can always be used to load 1 data. When the data is loaded into nosql, the pressure of DB will gradually drop.
Limit the number of single user connections below 500. For example:
set global max_user_connections=500;

(the performance of MySQL decreases as the number of connections increases, which is why thread_pool appears.)
In addition, for programs that have monitors that read the table below information_schema, consider closing the following parameters
set global innodb_stats_on_metadata=0;


The maximum number of connections for MySQL is 100 client logins by default: mysql-uusername-ppassword
Set the new maximum number of connections to 200: mysql > set GLOBAL max_connections=200
Displays Query: mysql currently running > show processlist
Display current status: mysql > show status
Exit client: mysql > exit
See the current maximum number of connections: mysqladmin-uusername-ppassword variables

This parameter is mainly used to prevent statistics on the large number of read disks caused by reading information_schema (you can also consider disabling this parameter if a table in information_schema appears in a slow query)

Treatment basis:

When the school's 1 canteen 1 minute can only for two dozen meal, suddenly came 100 people to dozen meal, and did not line up, not out will now hit the meal of the master to use some time
Go to choose to serve for that user, the more people, the more chaotic the scene, it is inevitable that there will be a user Shouting at his scene, and finally there may not be a meal, but between each other
If there is a fight, the chef will receive more than 90 Server too busy at the same time. If you can stand in line for one time, you can finish it in 50 minutes at most

Method 3: (newly compiled mysql in linux)

Take the manually compiled version of mysql 5.0.33 below centos 4.4 as an example:
vi /usr/local/mysql/bin/mysqld_safe
Go to safe_mysqld and edit it, go to the two lines that mysqld started, and add the following parameters:
-O max_connections=1500
One specific point is the following position:
In red letters:
$defaults --basedir=$MY_BASEDIR_VERSION
-O max_connections=1500
> > $err_log 2 > & 1 else
$defaults --basedir=$MY_BASEDIR_VERSION
--skip-external-locking $args
-O max_connections=1500 > >
$err_log 2 > & 1"
# service mysqld restart
# /usr/local/mysql/bin/mysqladmin -uroot -p variables
Enter the password for the root database account and you will see it
max_connections 1500 the new change has come into effect.

Related articles: