Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What max_connection should I set for MySQL?

I am using MySQL 5.1.35 database on Linux Centos.

The Linux server has 2GB RAM with 14GB of disk space.

I have created webservices using Restlet framework in Java which has thousand user access.

I want to set max_connection for maximum concurrent connections.

So please suggest me that what max_connection should I set?

Thanks in advance.

like image 730
Deepu Avatar asked Aug 31 '12 11:08

Deepu


People also ask

What is the typical server name for the MySQL?

As you could see, the MySQL server hostname is Localhost. Localhost means the MySQL database is running on the same server as the website.

How do I avoid too many connections error in MySQL?

If clients encounter Too many connections errors when attempting to connect to the mysqld server, all available connections are in use by other clients. The permitted number of connections is controlled by the max_connections system variable. To support more connections, set max_connections to a larger value.

What causes MySQL too many connections?

The MySQL “Too many connections” error occurs when more queries are sent to a MySQL database than can be processed. The error can be fixed by setting a new number of maximum connections in the configuration file or globally.


1 Answers

You need to calculate the memory required by your MySQL engine. See manual here

If you are using MYISAM tables then you can calculate memory requirement using following formula:

key_buffer_size + (read_buffer_size + sort_buffer_size) * max_connections = K bytes of memory 

Ideally this should not exceed 2 GB in your case.

Configuration parameters depends on type of your application and querys, but standard values for you could be:

key_buffer_size = 1024MB + (read_buffer_size = 1MB + sort_buffer_size = 4MB) * 200 ~= 2GB

key_buffer_size is a global variables whereas read_buffer_size and sort_buffer_size are session level parameters.

like image 93
Omesh Avatar answered Sep 28 '22 17:09

Omesh