Quickly increase the load capacity of MYSQL database connections

The first step is to limit the concurrent processing of Innodb. If innodb_thread_concurrency = 0, you can change it to 16 or 64 to view the machine pressure. If

Very large. First change to 16 to put pressure on the machine, and then gradually increase to adapt to your own business.

Solution: set global innodb_thread_concurrency = 16;

Method 1: (The my. ini file can be directly modified 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 to restart MYSQL.

Method 2: (in mysql command mode)

When the number of connections has exceeded 600 or more, consider limiting the number of connections appropriately so that the frontend reports an error and does not cause the DB to fail.

When the database is ready, it can always be used to load the data. when the data is loaded into nosql, the database pressure will gradually decrease.

Restrict the number of connections for a single user to less than 500. For example:

Set global max_user_connections = 500;

(MySQL performance will decrease as the number of connections increases, which is also the cause of thread_pool)

In addition, some monitoring programs can read tables under information_schema. you can disable the following parameters.

Innodb_stats_on_metadata = 0

Set global innodb_stats_on_metadata = 0;

Method

The maximum number of connections for MySQL is 100 by default. Client Logon: mysql-uusername-ppassword

Set the new maximum number of connections to 200: mysql> set GLOBAL max_connections = 200

Display the currently running Query: mysql> show processlist

Display Current status: mysql> show status

Exit the client: mysql> exit

View the current maximum number of connections: mysqladmin-uusername-ppassword variables

This parameter mainly prevents reading a large amount of information from the disk when reading information_schema (if the table in information_schema appears in a slow query, you can also disable this parameter)

Processing basis:

When a school canteen can only cook for two meals in one minute, 100 people suddenly come to cook, and there is no queue, there will be no waiting for the teacher to come out to cook now it will take some time

If you choose to serve the user, the more people there are, the more chaotic the scenes will be. It is inevitable that the user will yell at him. In the end, it may not be a meal, but mutual

After the fight, the cook will receive more than 90 Server too busy at the same time. If you can arrange the team, it will take up to 50 minutes.

Method 3: (mysql is newly compiled in linux)

Take mysql 5.0.33 in centos 4.4 as an example:

Vi/usr/local/mysql/bin/mysqld_safe

Find safe_mysqld and edit it. Find the two lines started by mysqld and add the following parameters:

-O max_connections = 1500

The specific point is the following position:

Note in Red:

Then $ NOHUP_NICENESS $ ledir/$ MYSQLD

$ Defaults -- basedir = $ MY_BASEDIR_VERSION

-- Datadir = $ DATADIR $ USER_OPTION

-- Pid-file = $ pid_file

-- Skip-external-locking

-O max_connections = 1500

>>$ Err_log 2> & 1 else

Eval "$ NOHUP_NICENESS $ ledir/$ MYSQLD

$ Defaults -- basedir = $ MY_BASEDIR_VERSION

-- Datadir = $ DATADIR $ USER_OPTION

-- Pid-file = $ pid_file

-- Skip-external-locking $ args

-O max_connections = 1500>

$ Err_log 2> & 1"

Save.

# Service mysqld restart

#/Usr/local/mysql/bin/mysqladmin-uroot-p variables

Enter the password of the root database account.

Max_connections 1500 indicates that the new change has taken effect.