Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

On my server I have 7 databases. Our server has 512 MB of RAM which I'm getting upgraded this evening to 2GB and has a 2.4 single processor.

I've gotten an error about the connection limit exceeded. With increasing my RAM, is it ok to increase the number of connections? Currently it's set to 200 but a single page may connect to 3-4 databases considering JOINs and things.

We've setup so many databases for mere organization. We have a total of about 250-300 tables in all of the databases.

Any advice would be appreciated :)

Update

Turns out, I have a lot more connections than I thought....

With 2GB RAM and a 2.4 single core, is 50 user connections accurate or should I increase that?

[mysqld]
safe-show-database

skip-locking
safe-show-database

interactive_timeout=30
wait_timeout=30
connect_timeout=10
max_user_connections=50
max_connections=1000

key_buffer=64M
join_buffer=2M
record_buffer=2M
sort_buffer=4M
max_allowed_packet=24M

query_cache_limit = 2M
query_cache_size = 64M
query_cache_type = 1

tmp_table_size=128M
table_cache=2048
thread_cache=256
thread_concurrency=1
share|improve this question
    
Number of tables or "schemas" which in mysql-language is a "database", has nothing to do with connections. One connection can send several queries, join as many tables as it like as well as change database/schema. You should consider one connection per simultaneous user, if you don't do multiple connectiosn per client (and in that case, look over your app/website to see if you can't optimize it) –  jishi Jun 18 '10 at 13:42
add comment

3 Answers

up vote 1 down vote accepted

You should be able to substantially increase your connections. However, if you do not properly tune to the per thread memory usage, you risk MySQL running out of memory. MySQL running out of memory can be a disastrous situation and is best to avoid.

An example of what happens when you do not properly tune:

http://serverfault.com/questions/124034/mysql-keeps-restarting-due-to-running-out-of-memory/124099#124099

Some earlier tuning recommendations I provided:

http://serverfault.com/questions/119468/how-do-i-set-my-cnf-in-mysql-so-that-there-are-no-limits-to-connections/119550#119550

The link to InnoDB Memory Usage includes a formula to calculate InnoDB memory usage, which you can plug into a spreadsheet to calculate the memory usage on a per thread basis.

Here is some more information regarding MySQL Server Memory Usage.

Ultimately, your goal should be to calculate your maximum per thread memory usage based on your configuration. Once that is calculated, you can set your maximum connections to the proper value.

share|improve this answer
    
You don't by chance have a spreadsheet you already use for this or something that would be helpful do you? Even a web page to do this would be awesome –  Webnet Jun 25 '10 at 20:59
    
I do but it is arbitrary and something I hack out depending on what I'm doing at the time, which includes being more focused on InnoDB. I considered giving it out but it's not really suitable for someone else to use. –  Warner Jun 25 '10 at 21:10
add comment

On 1gb machines, we routinely set max_connections to 750 without issues

share|improve this answer
    
It's irresponsible to do that without the proper discretion. –  Warner Jun 18 '10 at 13:24
add comment

If you use PHP, then edit the php configuration file, usually at /etc/php.ini and change persistence to OFF.

[MySQL] ; Allow or prevent persistent links. mysql.allow_persistent = Off

That's all. Restart Apache.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.