How do I increase the MySQL connections for my server?

• Applies to: Grid
  • Difficulty: Easy
  • Time Needed: 10
  • Tools Required: None
• Applies to: Legacy DV & VPS Hosting
  • Difficulty: Medium
  • Time Needed: 10
  • Tools Required: SSH, vi knowledge
• Applies to: VPS Hosting
  • Difficulty: Medium
  • Time Needed: 10
  • Tools Required: SSH, vi knowledge
• Applies to: DV 4.0
  • Difficulty: Medium
  • Time Needed: 10
  • Tools Required: SSH, vi knowledge
• Applies to: WordPress Hosting
  • Difficulty: Easy
  • Time Needed: 10
  • Tools Required: None

Overview

The Managed WordPress servers allows for 24 concurrent connections. Unfortunately this value is not able to be increased. If you require a service that will need additional connections, you may want to consider a VPS server, as that will allow you to have a higher default value of 100, with the ability to increase the value further if desired.

Overview

 

If you get a Too many connections error when you try to connect to the mysqld server, this means that all available connections are in use by other clients. The number of connections permitted is controlled by the max_connections system variable. Its default value is 100. If you need to support more connections, you should set a larger value for this variable.

The MySQL SmartPool v.2 for the Grid comes with 30 MySQL connections.

If you get a Too many connections error when you try to connect to the mysqld server, this means that all available connections are in use by other clients.

The number of connections permitted is controlled by the max_connections system variable. Its default value is 100. If you need to support more connections, you should set a larger value for this variable.

Requirements

Before you start, this article has the following dependencies:

• You must have SSH access set up for root or a sudo user.
  • Connecting via SSH to your server
  • How do I enable root access to my DV?
  • How do I disable SSH login for the root user?

READ ME FIRST
This article is provided as a courtesy. Installing, configuring, and troubleshooting third-party applications is unsupported by (mt) Media Temple. Please take a moment to review the Statement of Support.

Advanced Support can help!
If you're having trouble with the steps in this article, additional assistance is available via Advanced Support, our premium services division. Our expert engineers can tweak and tune your server for optimal performance. For more information on what Advanced Support can do for you, please click here.

Instructions

It is possible to upgrade your concurrent MySQL connections by adding a MySQL GridContainer to your Grid hosting package. The three different sizes offered are 60, 100, and 150 concurrent connections respectively.

For more information on the MySQL GridContainer, please see our Grid product page.

To increase the maximum connections that MySQL allows, you must edit the file /etc/my.cnf. Connect to the server via SSH as root or sudo user, and type this:

vi /etc/my.cnf

There will be a section that looks like this: (may be slightly different)

[mysqld]
local-infile=0
datadir=/var/lib/mysql
user=mysql
symbolic-links=0

max_connections = 100

Under [mysqld], you can increase your maximum MySQL connections by changing the max_connections attribute to something like this.

[mysqld]
safe-show-database
innodb_data_file_path=ibdata1:10M:autoextend
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

Add the following line under [mysqld]

max_connections=250

The resulting config file should resemble this:

[mysqld]
safe-show-database
innodb_data_file_path=ibdata1:10M:autoextend
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
max_connections=250

Save the changes and type the following to restart mysqld:

/etc/init.d/mysqld restart

You will see:

Stopping mysql: [ OK ]
Starting mysql: [ OK ]

If stopping FAILS, then you did something wrong. Try to backtrack and make sure you entered the new setting correctly.

NOTE:

If you'd like to check to see if this setting was successfully applied, you may check using one of the following methods:

1. Log into MySQL and you will be at the mysql> prompt.

   mysql -uadmin -p`cat /etc/psa/.psa.shadow`

2. Then run the command

   show variables like 'max_connections';

   Make sure you enter the semi-colon on the end. To exit mysql, use the quit command.

Resources

The authoritative source for this information may be found at the following URL:

• Grid Hosting FAQs: MySQL GridContainer
• Getting started with your MySQL GridContainer
• MySQL 5.5 Reference Manual: C.5.2.7 Too many connections
• MySQL 5.1 Reference Manual: C.5.2.7 Too many connections