0

We recently upgraded a database server from Ubuntu 14.04 to Ubuntu 18.04, and at the same time upgraded MySQL from 5.6 to 5.7.

Since the upgrade, the memory usage has more than doubled, from ~10GB to ~23GB (which is more than the server has). We tried to keep the configuration basically the same. We have been researching why this might be for quite some time, but to no avail. The work load didn't change at all from before the upgrade to after.

Here is some information from "show variables" that could affect memory usage:

binlog_stmt_cache_size 32768 binlog_transaction_dependency_history_size 25000 bulk_insert_buffer_size 8388608 ft_max_word_len 84 ft_min_word_len 4 ft_query_expansion_limit 20 ft_stopword_file (built-in) group_concat_max_len 65535 gtid_executed_compression_period 1000 innodb_buffer_pool_chunk_size 134217728 innodb_buffer_pool_dump_at_shutdown ON innodb_buffer_pool_instances 4 innodb_buffer_pool_load_at_startup ON innodb_buffer_pool_size 8589934592 innodb_change_buffer_max_size 25 innodb_ft_cache_size 8000000 innodb_ft_result_cache_limit 2000000000 innodb_ft_total_cache_size 640000000 innodb_log_buffer_size 16777216 innodb_log_file_size 50331648 innodb_max_undo_log_size 1073741824 innodb_online_alter_log_max_size 134217728 join_buffer_size 262144 key_buffer_size 536870912 key_cache_age_threshold 300 key_cache_block_size 1024 key_cache_division_limit 100 max_allowed_packet 134217728 max_binlog_cache_size 18446744073709547520 max_binlog_size 104857600 max_binlog_stmt_cache_size 18446744073709547520 max_connect_errors 100 max_connections 151 max_delayed_threads 20 max_digest_length 1024 max_heap_table_size 1073741824 max_insert_delayed_threads 20 max_join_size 18446744073709551615 max_length_for_sort_data 1024 max_points_in_geometry 65536 max_prepared_stmt_count 16382 max_seeks_for_key 18446744073709551615 max_sort_length 1024 max_tmp_tables 32 max_write_lock_count 18446744073709551615 metadata_locks_cache_size 1024 metadata_locks_hash_instances 8 myisam_max_sort_file_size 9223372036853727232 myisam_mmap_size 18446744073709551615 myisam_sort_buffer_size 8388608 net_buffer_length 16384 parser_max_mem_size 18446744073709551615 query_alloc_block_size 8192 query_cache_limit 1048576 query_cache_min_res_unit 4096 query_cache_size 1048576 query_prealloc_size 8192 range_alloc_block_size 4096 range_optimizer_max_mem_size 8388608 read_buffer_size 131072 read_rnd_buffer_size 262144 sort_buffer_size 262144 sql_select_limit 18446744073709551615 stored_program_cache 256 thread_stack 262144 tmp_table_size 1073741824

However, most of it is the same as it was before. We have an old image of the old server (which only uses 10GB of RAM), and ran show variables on both that and the new server, and used pt-config-diff to determine the differences. Here are those (removing things like hostname and stuff that I think would be impossible to affect memory, e.g. default character sets):

55 config differences Variable showVariablesOutOld.txt showVariablesOutNew.txt ========================= ========================= ========================= eq_range_index_dive_limit 10 200 expire_logs_days 10 5 innodb_checksum_algorithm innodb crc32 innodb_file_format Antelope Barracuda innodb_file_format_max Antelope Barracuda innodb_large_prefix OFF ON innodb_log_buffer_size 8388608 16777216 innodb_max_dirty_pages... 75 75.000000 innodb_max_dirty_pages... 0 0.000000 innodb_purge_threads 1 4 innodb_version 5.6.33 5.7.26 max_allowed_packet 16777216 134217728 myisam_recover_options BACKUP OFF optimizer_switch index_merge=on,index_m... index_merge=on,index_m... performance_schema_acc... 100 -1 performance_schema_hos... 100 -1 performance_schema_max... 3504 -1 performance_schema_max... 50 80 performance_schema_max... 7693 -1 performance_schema_max... 200 210 performance_schema_max... 15906 -1 performance_schema_max... 40 50 performance_schema_max... 9102 -1 performance_schema_max... 322 -1 performance_schema_max... 168 193 performance_schema_max... 4000 -1 performance_schema_max... 12500 -1 performance_schema_max... 402 -1 performance_schema_set... 100 -1 performance_schema_set... 100 -1 performance_schema_use... 100 -1 query_cache_size 16777216 1048576 slave_net_timeout 3600 60 slow_query_log_file /var/lib/mysql/ip-172-... /var/lib/mysql/dataver... sql_mode NO_ENGINE_SUBSTITUTION NO_AUTO_CREATE_USER,NO... table_open_cache_insta... 1 16 thread_cache_size 8 9 thread_stack 524288 262144 version 5.6.33-0ubuntu0.14.04.... 5.7.26-0ubuntu0.18.04.... version_compile_os debian-linux-gnu Linux

Any ideas on what could be the cause?

  • Additional information request. Post on pastebin.com and share the links. Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report F) SHOW ENGINE INNODB STATUS; AND Optional very helpful information, if available includes - htop OR top OR mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis. – Wilson Hauck Jun 28 at 23:56
  • Post information from OLD and NEW server for comparison, please. – Wilson Hauck Jul 2 at 23:26
  • @WilsonHauck after we fixed the issue, we decommissioned the old server, and don't have the old information anymore. Rick James answer solved the issue. Thanks for the interest. – juacala Jul 3 at 14:35
  • If you post additional information from NEW server only, will likely be able to improve your query execution speed. – Wilson Hauck Jul 13 at 21:26
1

Don't set max_heap_table_size or tmp_table_size to more than 1% of RAM.

Probably wise to turn OFF the Query Cache.

There may be more. http://mysql.rjweb.org/doc.php/mysql_analysis

  • Turning those down to the default fixed the issue (memory usage now sits around 10-11GB). Not sure historically why that was set so high on our servers, but apparently MySQL 5.7 makes use of it more than MySQL 5.6 did. – juacala Jun 27 at 17:00
  • @juacala - Some query might be optimized in a different way. Those settings control a certain aspect of SELECT evaluation. If you can locate the query, we should see if it can be improved. – Rick James Jun 27 at 21:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.