Monday, August 31, 2015

mysql - Lower Maximum Possible Memory Usage



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



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.


No comments:

Post a Comment

linux - How to SSH to ec2 instance in VPC private subnet via NAT server

I have created a VPC in aws with a public subnet and a private subnet. The private subnet does not have direct access to external network. S...