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.

回答1:

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.

回答2:

This is dependent on the amount of memory you have and how your other settings are configured. There's a MySQL Calculator you can use for this.

回答3:

You need to give information like:

1. How many users using the application?
2. How many applications connect to the database?
3. What would be the load and how queries are given?

For server configuration:

1. It is better to have 2 separate servers for Apache/PHP and MySQL with the Linux of your choice.
2. Try not to run too much else on either box; leave the resources for Apache/PHP and MySQL.

You can get more info at Tuning MySQL.