Calculating Maximum Connections for MySQL Server

Published November 1st, 2008

Problem Statement:

When you develop Web applications for large exposure on the Web , you need to worry about performance from the very beginning. MySQL server performance is always one of the key issue that you need to concentrate on from the very beginning; just slapping together a vanilla MySQL configuration will get you started but will not scale up automatically. Remember, the company that makes MySQL makes their money by selling MySQL consulting services, which includes the best tuning and scalability stuff that we all want to have but often the project’s budget does not permit. After all, most MySQL projects start with lot less than an Oracle budget.

However, there is no reason to resort to a heavy sigh.  The beautiful thing about Open Source and Web as a knowledge base is that you are not alone. Many people have figured out how to tune MySQL to dance under heavy load. Here we will discuss a key performance tip that will allow you to answer a specific performance tuning question:  how many MySQL simultaneous connections can my MySQL server handle?

Step 1: Backup your /etc/my.cnf file

The /etc/my.cnf file is the only MySQL configuration file you need to work with to get the best out of MySQL database server. Make sure you always backup a copy of this very important configuration file before tweaking with it.

Step 2: Prepare for many connections

Have you seen an error message from MySQL that reads: Too many connections? If you did, congratulations! You wrote a popular PHP application! Seriously, if you write a useful PHP application that reads and writes MySQL data, you might have already experienced connection issues. By default, MySQL does not handle a lot of connection. You have to configure the max_connections setting under [mysqld] section in the /etc/my.cnf file. For example:

[mysqld]
max_connections=1000

The above configuration tells MySQL to allow up to 1000 connections to the database server. Actually, MySQL allows max_connections+1 connections at all time. The extra connection is reserved for users with SUPER privilege (such as root) so that she can get in the MySQL shell and execute commands such as SHOW PROCESSLIST or KILL [process id] to manage the connections.

Now the question is: how high do you want to set this number to? Well, that depends on:

• How much memory (RAM) do you have on the server
• Are you using Sun Microsystems provided binaries for MySQL?
• How good is your thread library on your operating system platform?
• etc.

As a rule of thumb, typical Linux installations can handle 500-1000 connections and Sun Microsystems provided Linux binaries for MySQL can handle up to 4000 connections!

The formula that you want to use to come up with this magic number is as follows;

Available RAM = Global Buffers + (Thread Buffers x max_connections)
max_connections = (Available RAM - Global Buffers) / Thread Buffers

So if you have 4GB of available RAM (beyond what is used by your operating system and other processes already), you can determine how many connections you can have by summing up all the global and thread buffer sizes and applying them to the above-mentioned equation.

Or, if you know that you need n connections, you can derive the amount of RAM you need available using the first equation. This will help you do capacity planning when load requirements are already known.

To find out what are the current settings for these buffers, you can use the following statement:

SHOW VARIABLES LIKE '%buffer%';

Here is a sample output:

+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| bulk_insert_buffer_size | 8388608    |
| innodb_buffer_pool_size | 8388608    |
| innodb_log_buffer_size  | 1048576    |
| join_buffer_size        | 4194304    |
| key_buffer_size         | 1073741824 |
| myisam_sort_buffer_size | 67108864   |
| net_buffer_length       | 16384      |
| preload_buffer_size     | 32768      |
| read_buffer_size        | 4194304    |
| read_rnd_buffer_size    | 33554432   |
| sort_buffer_size        | 8388608    |
| sql_buffer_result       | OFF        |
+-------------------------+------------+
12 rows in set (0.00 sec)

Here is a sample max_connections calculation done using the numbers shown above for 4 GB of available RAM.

Here you can see that a server with 4GB of available RAM (beyond what is needed to run server operating system and other software) can handle maximum 27 simultaneous connections. Of course, if you change the buffer sizes, you can tweak this number. For example, if you only use InnoDB tables and never plan to use MYISAM than you can set myisam_sort_buffer_size to 0 and get 63 connections with the same 4GB of available RAM.

You can download this Open Office spreadsheet by clicking on the link below:
MySQL Server Max Connection Calculator

Edit the numbers in the yellow column to fit your size requirements.

Following table shows which buffers are global and which are thread specific:

Global Buffers	Thread Buffers
key_buffer_size - for storing indexes in main memory	sort_buffer_size -for ORDER BY and GROUP BY operations
innodb_buffer_pool	myisam_sort_buffer_size -same for MYISAM tables
innodb_log_buffer	read_buffer_size for MYISAM table reads
innodb_additional_mem_pool	join_buffer_size - for joins that don’t use indexes
net_buffer_size for network data	read_rnd_buffer_size for queries with ORDER BY clauses

Hopefully, now you know how many simultaneous connections you can have on your MySQL server. There are many other configuration related to how many MySQL threads you need to cache ahead of time so that you are ready for handling the connections. We will discuss such topics in a future article.

Now go enjoy your connections!

Category: Performance | Tags: