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
Best Answer
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:
MySQL keeps restarting due to running out of memory
Some earlier tuning recommendations I provided:
How do I set my.cnf in Mysql so that there are no limits to connections?
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.