High Memory Usage in mySQL 5.5

Portal Home > Knowledgebase > Articles Database > High Memory Usage in mySQL 5.5

Posted by adrianwa, 05-03-2012, 04:44 AM
I recently installed cPanel on my virtual dedicated server running CentOS and I've been noticing that the mySQL database has been using a large amount of RAM (420.8MB). The databases running on the server are small (around 30 mb) total. All the databases have been repaired and optimized through phpmyadmin and it's still having the issue. I've also restarted the server multiple times. Is there a way to reduce memory usage for the mysql server. PID | private/writ. mem |command 01:34:44 | current | previous |(truncated) (waiting 15 min.) 1811 | 420.8 MB | +++++ |/usr/sbin/mysqld -basedir=/usr -datadir=/var/lib/mys 1444 | 31.5 MB | ++++ |/sbin/rsyslogd-i/var/run/syslogd.pid-c4 11802 | 17.3 MB | +++ |/usr/bin/perl/usr/local/cpanel/bin/leechprotect 3155 | 17.0 MB | +++ |tailwatchd 3092 | 13.3 MB | +++ |cpsrvd (SSL) - waiting for connections 3097 | 13.2 MB | +++ |cpdavd - accepting connections on 2077 and 2078 13492 | 5.3 MB | +++ |/usr/local/apache/bin/httpd-kstart-DSSL 15831 | 5.0 MB | ++ |/usr/local/apache/bin/httpd-kstart-DSSL 16075 | 4.8 MB | ++ |/usr/local/apache/bin/httpd-kstart-DSSL RAM usage: ========== 31.2 % dditional Details --------------------------------------------------------- [root@ve ~]# mysql_config --version 5.5.22 [root@ve ~]# cat /etc/my.cnf [mysqld] default-storage-engine=MyISAM innodb_file_per_table=1 max_connections = 50 max_user_connections = 150 key_buffer = 36M myisam_sort_buffer_size = 64M join_buffer_size = 2M read_buffer_size = 2M sort_buffer_size = 3M table_cache = 1024 thread_cache_size = 286 interactive_timeout = 25 wait_timeout = 1800 connect_timeout = 10 max_allowed_packet = 1M max_connect_errors = 1000 query_cache_limit = 1M query_cache_size = 16M query_cache_type = 1 tmp_table_size = 16M query_cache_size = 10M --------------------------------------------------------- ------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.5.22-cll [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 8M (Tables: 433) [--] Data in InnoDB tables: 28M (Tables: 197) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [--] Data in MEMORY tables: 1M (Tables: 4) [!!] Total fragmented tables: 8 -------- Performance Metrics ------------------------------------------------- [--] Up for: 4h 31m 8s (335K q [20.622 qps], 2K conn, TX: 64M, RX: 63M) [--] Reads / Writes: 1% / 99% [--] Total buffers: 206.0M global + 7.5M per thread (50 max threads) [OK] Maximum possible memory usage: 581.0M (33% of installed RAM) [OK] Slow queries: 0% (0/335K) [OK] Highest usage of available connections: 12% (6/50) [OK] Key buffer size / total MyISAM indexes: 36.0M/1.9M [OK] Key buffer hit rate: 99.7% (51K cached / 143 reads) [OK] Query cache efficiency: 81.7% (15K cached / 19K selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts) [!!] Joins performed without indexes: 288 [OK] Temporary tables created on disk: 16% (400 on disk / 2K total) [OK] Thread cache hit rate: 99% (6 created / 2K connections) [OK] Table cache hit rate: 49% (696 open / 1K opened) [OK] Open file limit used: 44% (935/2K) [OK] Table locks acquired immediately: 99% (315K immediate / 315K locks) [OK] InnoDB data size / buffer pool: 28.2M/128.0M
Posted by WHR-Abner, 05-03-2012, 10:29 AM
How much RAM do you have on your VPS?
Posted by adrianwa, 05-03-2012, 10:47 AM
1GB. I started with 512MB but it the database took 80-90% of the RAM and so I ended up upgrading the server. It's still an issue now since the database is using 400+ mb of RAM out of 1GB.
Posted by WHR-Abner, 05-03-2012, 11:02 AM
Hello, There should be other variables which is not specified in your my.cnf that uses the default values. For eg, "innodb_buffer_pool_size". You can check the default values assigned to those variables by running the command "mysqladmin variables". To fine tune mysql, you can also rely on particular mysql tuner scripts which are available for download.
Posted by adrianwa, 05-03-2012, 11:08 AM
I ran the command and this is what the server returned to me. +---------------------------------------------------+------------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +---------------------------------------------------+------------------------------------------------------------------------------------------------------------------------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | | autocommit | ON | | automatic_sp_privileges | ON | | back_log | 50 | | basedir | /usr | | big_tables | OFF | | binlog_cache_size | 32768 | | binlog_direct_non_transactional_updates | OFF | | binlog_format | STATEMENT | | binlog_stmt_cache_size | 32768 | | bulk_insert_buffer_size | 8388608 | | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | | completion_type | NO_CHAIN | | concurrent_insert | AUTO | | connect_timeout | 10 | | datadir | /var/lib/mysql/ | | date_format | %Y-%m-%d | | datetime_format | %Y-%m-%d %H:%i:%s | | default_storage_engine | MyISAM | | default_week_format | 0 | | delay_key_write | ON | | delayed_insert_limit | 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000 | | div_precision_increment | 4 | | engine_condition_pushdown | ON | | event_scheduler | OFF | | expire_logs_days | 0 | | flush | OFF | | flush_time | 0 | | foreign_key_checks | ON | | ft_boolean_syntax | + -><()~*:""&| | | ft_max_word_len | 84 | | ft_min_word_len | 4 | | ft_query_expansion_limit | 20 | | ft_stopword_file | (built-in) | | general_log | OFF | | general_log_file | /var/lib/mysql/ve.log | | group_concat_max_len | 1024 | | have_compress | YES | | have_crypt | YES | | have_csv | YES | | have_dynamic_loading | YES | | have_geometry | YES | | have_innodb | YES | | have_ndbcluster | NO | | have_openssl | DISABLED | | have_partitioning | YES | | have_profiling | YES | | have_query_cache | YES | | have_rtree_keys | YES | | have_ssl | DISABLED | | have_symlink | YES | | ignore_builtin_innodb | OFF | | init_connect | | | init_file | | | init_slave | | | innodb_adaptive_flushing | ON | | innodb_adaptive_hash_index | ON | | innodb_additional_mem_pool_size | 8388608 | | innodb_autoextend_increment | 8 | | innodb_autoinc_lock_mode | 1 | | innodb_buffer_pool_instances | 1 | | innodb_buffer_pool_size | 134217728 | | innodb_change_buffering | all | | innodb_checksums | ON | | innodb_commit_concurrency | 0 | | innodb_concurrency_tickets | 500 | | innodb_data_file_path | ibdata1:10M:autoextend | | innodb_data_home_dir | | | innodb_doublewrite | ON | | innodb_fast_shutdown | 1 | | innodb_file_format | Antelope | | innodb_file_format_check | ON | | innodb_file_format_max | Antelope | | innodb_file_per_table | ON | | innodb_flush_log_at_trx_commit | 1 | | innodb_flush_method | | | innodb_force_load_corrupted | OFF | | innodb_force_recovery | 0 | | innodb_io_capacity | 200 | | innodb_large_prefix | OFF | | innodb_lock_wait_timeout | 50 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_buffer_size | 8388608 | | innodb_log_file_size | 5242880 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | | innodb_max_dirty_pages_pct | 75 | | innodb_max_purge_lag | 0 | | innodb_mirrored_log_groups | 1 | | innodb_old_blocks_pct | 37 | | innodb_old_blocks_time | 0 | | innodb_open_files | 300 | | innodb_purge_batch_size | 20 | | innodb_purge_threads | 0 | | innodb_random_read_ahead | OFF | | innodb_read_ahead_threshold | 56 | | innodb_read_io_threads | 4 | | innodb_replication_delay | 0 | | innodb_rollback_on_timeout | OFF | | innodb_rollback_segments | 128 | | innodb_spin_wait_delay | 6 | | innodb_stats_method | nulls_equal | | innodb_stats_on_metadata | ON | | innodb_stats_sample_pages | 8 | | innodb_strict_mode | OFF | | innodb_support_xa | ON | | innodb_sync_spin_loops | 30 | | innodb_table_locks | ON | | innodb_thread_concurrency | 0 | | innodb_thread_sleep_delay | 10000 | | innodb_use_native_aio | ON | | innodb_use_sys_malloc | ON | | innodb_version | 1.1.8 | | innodb_write_io_threads | 4 | | interactive_timeout | 25 | | join_buffer_size | 2097152 | | keep_files_on_create | OFF | | key_buffer_size | 37748736 | | key_cache_age_threshold | 300 | | key_cache_block_size | 1024 | | key_cache_division_limit | 100 | | large_files_support | ON | | large_page_size | 0 | | large_pages | OFF | | lc_messages | en_US | | lc_messages_dir | /usr/share/mysql/ | | lc_time_names | en_US | | license | GPL | | local_infile | ON | | lock_wait_timeout | 31536000 | | locked_in_memory | OFF | | log | OFF | | log_bin | OFF | | log_bin_trust_function_creators | OFF | log_output | FILE | | log_queries_not_using_indexes | OFF | | log_slave_updates | OFF | | log_slow_queries | OFF | | log_warnings | 1 | | long_query_time | 10.000000 | | low_priority_updates | OFF | | lower_case_file_system | OFF | | lower_case_table_names | 0 | | max_allowed_packet | 1048576 | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 1073741824 | | max_binlog_stmt_cache_size | 18446744073709547520 | | max_connect_errors | 1000 | | max_connections | 50 | | max_delayed_threads | 20 | | max_error_count | 64 | | max_heap_table_size | 16777216 | | max_insert_delayed_threads | 20 | | max_join_size | 18446744073709551615 | | max_length_for_sort_data | 1024 | | max_long_data_size | 1048576 | | max_prepared_stmt_count | 16382 | | max_relay_log_size | 0 | | max_seeks_for_key | 18446744073709551615 | | max_sort_length | 1024 | | max_sp_recursion_depth | 0 | | max_tmp_tables | 32 | | max_user_connections | 150 | | max_write_lock_count | 18446744073709551615 | | metadata_locks_cache_size | 1024 | | min_examined_row_limit | 0 | | multi_range_count | 256 | | myisam_data_pointer_size | 6 | | myisam_max_sort_file_size | 9223372036853727232 | | myisam_mmap_size | 18446744073709551615 | | myisam_recover_options | OFF | | myisam_repair_threads | 1 | | myisam_sort_buffer_size | 67108864 | | myisam_stats_method | nulls_unequal | | myisam_use_mmap | OFF | | net_buffer_length | 16384 | | net_read_timeout | 30 | | net_retry_count | 10 | | net_write_timeout | 60 | | new | OFF | | old | OFF | | old_alter_table | OFF | | old_passwords | OFF | | open_files_limit | 2108 | | optimizer_prune_level | 1 | | optimizer_search_depth | 62 | | optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on | | performance_schema | OFF | | performance_schema_events_waits_history_long_size | 10000 | | performance_schema_events_waits_history_size | 10 | | performance_schema_max_cond_classes | 80 | | performance_schema_max_cond_instances | 1000 | | performance_schema_max_file_classes | 50 | | performance_schema_max_file_handles | 32768 | | performance_schema_max_file_instances | 10000 | | performance_schema_max_mutex_classes | 200 | | performance_schema_max_mutex_instances | 1000000 | | performance_schema_max_rwlock_classes | 30 | | performance_schema_max_rwlock_instances | 1000000 | | performance_schema_max_table_handles | 100000 | | performance_schema_max_table_instances | 50000 | | performance_schema_max_thread_classes | 50 | | performance_schema_max_thread_instances | 1000 | | plugin_dir | /usr/lib64/mysql/plugin/ | | port | 3306 | | preload_buffer_size | 32768 | | profiling | OFF | | profiling_history_size | 15 | | protocol_version | 10 | | query_alloc_block_size | 8192 | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 10485760 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | | query_prealloc_size | 8192 | | range_alloc_block_size | 4096 | | read_buffer_size | 2097152 | | read_only | OFF | | read_rnd_buffer_size | 262144 | | relay_log | | | relay_log_index | | | relay_log_info_file | relay-log.info | | relay_log_purge | ON | | relay_log_recovery | OFF | | relay_log_space_limit | 0 | | report_host | | | report_password | | | report_port | 3306 | | report_user | | | rpl_recovery_rank | 0 | | secure_auth | OFF | | secure_file_priv | | | server_id | 0 | | skip_external_locking | ON | | skip_name_resolve | OFF | | skip_networking | OFF | | skip_show_database | OFF | | slave_compressed_protocol | OFF | | slave_exec_mode | STRICT | | slave_load_tmpdir | /tmp | | slave_net_timeout | 3600 | | slave_skip_errors | OFF | | slave_transaction_retries | 10 | | slave_type_conversions | | | slow_launch_time | 2 | | slow_query_log | OFF | | slow_query_log_file | /var/lib/mysql/ve-slow.log | | socket | /var/lib/mysql/mysql.sock | | sort_buffer_size | 3145728 | | sql_auto_is_null | OFF | | sql_big_selects | ON | | sql_big_tables | OFF | | sql_buffer_result | OFF | | sql_log_bin | ON | | sql_log_off | OFF | | sql_low_priority_updates | OFF | | sql_max_join_size | 18446744073709551615 | | sql_mode | | | sql_notes | ON | | sql_quote_show_create | ON | | sql_safe_updates | OFF | | sql_select_limit | 18446744073709551615 | | sql_slave_skip_counter | 0 | | sql_warnings | OFF | | ssl_ca | | | ssl_capath | | | ssl_cert | | | ssl_cipher | | | ssl_key | | | storage_engine | MyISAM | | stored_program_cache | 256 | | sync_binlog | 0 | | sync_frm | ON | | sync_master_info | 0 | | sync_relay_log | 0 | | sync_relay_log_info | 0 | | system_time_zone | PDT | | table_definition_cache | 800 | | table_open_cache | 1024 | | thread_cache_size | 286 | | thread_concurrency | 10 | | thread_handling | one-thread-per-connection | | thread_stack | 262144 | | thread_statistics | OFF | | time_format | %H:%i:%s | | time_zone | SYSTEM | | timed_mutexes | OFF | | tmp_table_size | 16777216 | | tmpdir | /tmp | | transaction_alloc_block_size | 8192 | | transaction_prealloc_size | 4096 | | tx_isolation | REPEATABLE-READ | | unique_checks | ON | | updatable_views_with_limit | YES | | userstat | OFF | | version | 5.5.22-cll | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86_64 | | version_compile_os | Linux | | wait_timeout | 1800 | +---------------------------------------------------+------------------------------------------------------------------------------------------------------------------------+
Posted by deydod, 05-03-2012, 11:13 AM
Hi I have customized my.cnf for 1GB VPS. You can create a backup of your existing my.cnf and put this: [mysqld] tmpdir=/tmp skip-external-locking query_cache_limit=1M query_cache_size=32M query_cache_type=1 max_connections=300 max_user_connections=600 interactive_timeout=100 wait_timeout=100 connect_timeout=10 thread_stack=128K thread_cache_size=128 myisam-recover=BACKUP key_buffer=64M join_buffer_size=1M max_allowed_packet=32M table_cache=1024 sort_buffer_size=1M read_buffer_size=1M read_rnd_buffer_size=768K thread_concurrency=4 myisam_sort_buffer_size=32M skip-locking after updating my.cnf please restart mysql. Let me know what the results are. Deyan
Posted by WHR-Abner, 05-03-2012, 11:17 AM
Hello, Search for a mysql tuner script in google, download it and execute it on the server. It will recommended you values for the mysql variables according to the RAM you have. Assign those values to the respective variables in my.cnf and restart the mysql service.
Posted by adrianwa, 05-03-2012, 11:21 AM
The server is returning this to me now with with memtop. The memory usage has gone down by around 100mb. I'm noticing though that with time the database memory usage keeps increasing though. Is this normal for a small database of under 30mb (InnoDB) for this type of usage? (mySQL 5.5) PID | private/writ. mem |command 08:18:48 | current | previous |(truncated) (waiting 15 min.) 13854 | 316.9 MB | +++++ |/usr/sbin/mysqld -basedir=/usr -datadir=/var/lib 1444 | 31.5 MB | ++++ |/sbin/rsyslogd-i/var/run/syslogd.pid-c4 12287 | 17.3 MB | +++ |/usr/bin/perl/usr/local/cpanel/bin/leechprotect 3155 | 17.0 MB | +++ |tailwatchd 3092 | 13.3 MB | +++ |cpsrvd (SSL) - waiting for connections 3097 | 13.2 MB | +++ |cpdavd - accepting connections on 2077 and 2078 13327 | 4.8 MB | ++ |/usr/local/apache/bin/httpd-kstart-DSSL 13313 | 4.8 MB | ++ |/usr/local/apache/bin/httpd-kstart-DSSL 10050 | 4.6 MB | ++ |/usr/local/apache/bin/httpd-kstart-DSSL RAM usage: ======== 24.8 %
Posted by adrianwa, 05-03-2012, 11:35 AM
I've rerun the optimization scripts and I'm getting 335.9MB MB of memory usage right now. Would this be considered normal for a small database?
Posted by WHR-Abner, 05-03-2012, 12:05 PM
You still have more than 33% of memory being used by MySQL. If Apache and other applications too take high memory, soon your VPS will start swapping and this will degrade the performance of the VPS. Check the output of "mysqladmin proc stat" and see if there are any databases that is causing this. Also see how much free memory your VPS has now "free -m" and also run vmstat.
Posted by adrianwa, 05-03-2012, 12:31 PM
mysqladmin proc stat is showing this. Would this output be normal? I'm not too familiar with this. Thanks [root@ve ~]# mysqladmin proc stat +-----+------+-----------+----+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+------+-----------+----+---------+------+-------+------------------+ | 346 | root | localhost | | Query | 0 | | show processlist | +-----+------+-----------+----+---------+------+-------+------------------+ Uptime: 3017 Threads: 1 Questions: 11992 Slow queries: 0 Opens: 2465 Flush tables: 1 Open tables: 399 Queries per second avg: 3.974
Posted by WHR-Abner, 05-03-2012, 12:38 PM
No slow queries, open tables fine, less queries per second; the stats look more than good at present. If you are not facing any issues at the moment, there are no changes required. Run this command when you start seeing issues and see if any of the database is showing up in the list.
Posted by adrianwa, 05-03-2012, 12:41 PM
Ah ok. So I shouldn't be concerned about the 350+ mb usage of ram on the vpn from the mysql? I was worried that there was an underlying problem since i'm not sure if it's suppose to be using that much ram.
Posted by WHR-Abner, 05-03-2012, 01:02 PM
If the MySQL process list is fine and if Apache doesn't swap, you are fine. I don't see any critical issues with your VPS at present.
Posted by VectorVPS, 05-03-2012, 05:23 PM
You are using very large buffers for the small size of your databases. key_buffer (MyISAM): 36M innodb_buffer_pool_size: 128M (not set in your config, using the default MySQL 5.5 value of 128M) You only have 8 MB of data in MyISAM tables, and 28 MB in InnoDB tables. Reduce these values in your my.cnf: key_buffer = 12M innodb_buffer_pool = 48M In your case, this will save approximately 100 MB of RAM without hurting performance (as long as your database does not grow significantly).
Posted by adrianwa, 05-03-2012, 05:51 PM
I made the changes to the my.cnf and the mysql server fails to start. I reverted back to the original configurations and cpanel cannot startup the mysql server still. The only way I've managed to get it up again is to delete out the my.cnf and put only the : [mysqld] default-storage-engine=MyISAM innodb_file_per_table=1 inside the file. Not sure if I messed something up. "Waiting for mysql to restart...............................................................finished. mysql has failed, please contact the sysadmin (result was "mysql is not running")."