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.

I've been using MySQLTuner to optimize my database, and the only problem I am having is:

[!!] Maximum possible memory usage: 14.8G (126% of installed RAM)

I'm not sure what I should be changing to get this down under my 12G of RAM. I have a database with 110 tables, 10,000,000 rows (growing fairly quickly), and an average of 250 users online. My my.cnf has looks like:

query_cache_limit=1M

query_cache_size=128M

query_cache_type=1

max_user_connections=2000

max_connections=2000

interactive_timeout=100

wait_timeout=100

connect_timeout=100

thread_cache_size=128

key_buffer=16M

join_buffer=1M

max_allowed_packet=16M

table_cache=15360

record_buffer=1M

sort_buffer_size=4M

read_buffer_size=2M

max_connect_errors=10

thread_concurrency=8

myisam_sort_buffer_size=64M

server-id=1

share|improve this question
add comment

4 Answers

up vote 1 down vote accepted

The memory usage of MySQL is quite hard to predict. I would recommend having a look at http://www.mysqlperformanceblog.com/2006/05/17/mysql-server-memory-usage/ which gives an introduction to what is using memory in your installation. But to summarize:

"In fact typical server with 8GB of memory will often run with maximum theoretical memory usage of 100GB or more."

Or in other words, don't worry too much about the maximum possible memory usage unless you start seeing your server swap.

share|improve this answer
add comment

bring your max_connections down to 200 and wait_timeout to 10

share|improve this answer
add comment

Your average "users online" isn't particularly interesting when it comes to tuning your database, it's the peak requests that you need to consider. Depending on what your actual needs are, you can drop any of your max connections, sort buffer size, read buffer, or query cache. Then there's always the "throw more memory at it" solution.

share|improve this answer
add comment
max_user_connections=2000
max_connections=2000

These can be brought down considerably to tune your MySQL.

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.