I have a VPS with 2.25GB of RAM. MySQL is using 1.4GB with only four users on my site right now. It seems to use this amount even when no users are online. The available memory of my server regularly dips below 100MB, which is disturbing.
Yesterday MySQL kept crashing with OOM errors and resulted in database corruption, so that's what really prompted me to look into this issue.
I'm using the following configuration generated by the Percona Configuration Wizard (slightly modified):
# Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5_20120208 # MODIFIED SLIGHTLY [mysql] # CLIENT # port = 3306 socket = "/var/lib/mysql/mysql.sock" [mysqld] slow-query-log=1 long-query-time=1 # GENERAL # user = mysql default_storage_engine = InnoDB socket = "/var/lib/mysql/mysql.sock" pid_file = "/var/lib/mysql/mysql.pid" # MyISAM # key_buffer_size = 32M myisam_recover = FORCE,BACKUP # SAFETY # max_allowed_packet = 256M # Percona said 16M, but I need it larger to avoid errors max_connect_errors = 1000000 # DATA STORAGE # datadir = "/var/lib/mysql/" ######### removed the following because the logs were taking up TONS Of space: # BINARY LOGGING # #log_bin = /var/lib/mysql/mysql_bin #expire_logs_days = 14 #sync_binlog = 1 # CACHES AND LIMITS # tmp_table_size = 32M max_heap_table_size = 32M query_cache_type = 0 # was previously 1 query_cache_size = 0 # was previously 32M max_connections = 500 thread_cache_size = 50 open_files_limit = 65535 # was previously 4822 table_definition_cache = 1024 table_open_cache = 2048 # was previously 1024 # INNODB # innodb_flush_method = O_DIRECT innodb_log_files_in_group = 2 innodb_log_file_size = 128M innodb_flush_log_at_trx_commit = 1 innodb_file_per_table = 1 innodb_buffer_pool_size = 1456M # LOGGING # log_error = "/var/lib/mysql/mysql_error.log" log_queries_not_using_indexes = 1
As you can see, I increased
table_open_cache. I made the changes because I was receiving errors (I forget exactly what was causing them now) and making these changes caused the errors to go away.
Also, I noticed the
innodb_buffer_pool_size setting is close to how much RAM MySQL is using (which may just be a coincidence), but as this answer suggested I ran the following:
SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes FROM information_schema.tables WHERE engine='InnoDB') A;
And the output was
2, which I believe means the recommended
innodb_buffer_pool_size is 2GB. If that's correct, then my current setting is even below the recommended amount.
I really don't know what I'm doing and would prefer not to screw things up by blindly making changes to
my.cnf. Are there changes I can make to reduce memory usage, or do I just need to upgrade my VPS plan?