Home / server / Questions / 98922
Accepted
James Simpson
Asked: 2010-01-03 21:13:51 +0800 CST

Lower Maximum Possible Memory Usage

  • 772

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

mysql

4 Answers

  1. Best Answer

    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.

    • 1

  2. bring your max_connections down to 200 and wait_timeout to 10

    • 1

  3. 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.

    • 0 
  4. max_user_connections=2000
max_connections=2000

    These can be brought down considerably to tune your MySQL.

    • 0