Sample Header Ad - 728x90

MariaDB memory usage growing

1 vote
0 answers
890 views
I am using MariaDB 10.2.6 + TokuDB plugin on Debian Stretch. Each storage eninge, InnoDB and TokuDB, is set to a buffer/cache size of 12G. This configuration worked fine with MariaDB 10.1.25 on Debain Jessie. Since upgrading the database, the memory consumption knows no bounds and grows steadily (currently 50G resident!). The configuration files did not change in the upgrade process. Some memory measurements: (memory usage did not drop significantly at any point in time) * Tuesday 8AM: Database restart * Wednesday 3PM: VIRT 45.1G, RES 29.2G * Friday 5PM: VIRT 56.5G, RES 42.2G * Saturday 11PM: VIRT 65.8G, RES 51.4G Output of InnoDB/TokuDB Status for each of the last three measurepoints: * SHOW ENGINE INNODB STATUS; https://pastebin.com/raw/04fA1SpX * SHOW ENGINE TOKUDB STATUS; https://pastebin.com/raw/jeRTRyKC Hardware: * Intel Xeon 6C/12T * 128G RAM * Database runs on RAID 1 (2x SSD) ## Config ## cat my.cnf conf.d/* mariadb.conf.d/* [mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp lc_messages_dir = /usr/share/mysql lc_messages = en_US skip-external-locking bind-address = 127.0.0.1 max_connections = 100 connect_timeout = 5 wait_timeout = 600 max_allowed_packet = 16M thread_cache_size = 128 sort_buffer_size = 4M bulk_insert_buffer_size = 16M tmp_table_size = 32M max_heap_table_size = 32M myisam_recover_options = BACKUP key_buffer_size = 128M table_open_cache = 400 myisam_sort_buffer_size = 512M concurrent_insert = 2 read_buffer_size = 2M read_rnd_buffer_size = 1M query_cache_limit = 128K query_cache_size = 64M log_warnings = 2 slow_query_log_file = /var/log/mysql/mariadb-slow.log long_query_time = 10 log_slow_verbosity = query_plan log_bin = /var/log/mysql/mariadb-bin log_bin_index = /var/log/mysql/mariadb-bin.index expire_logs_days = 10 max_binlog_size = 100M default_storage_engine = InnoDB innodb_buffer_pool_size = 256M innodb_log_buffer_size = 8M innodb_file_per_table = 1 innodb_open_files = 400 innodb_io_capacity = 400 innodb_flush_method = O_DIRECT [isamchk] key_buffer = 16M !includedir /etc/mysql/conf.d/ # conf.d/ [mysqld] wait_timeout = 28800 interactive_timeout = 28800 [mysqld] explicit_defaults_for_timestamp innodb_file_per_table innodb_flush_method = O_DIRECT innodb_log_file_size = 2G innodb_buffer_pool_instances = 2 innodb_buffer_pool_size = 12G innodb_log_buffer_size = 96M innodb_read_io_threads = 4 innodb_write_io_threads = 4 table_open_cache = 650 key_buffer_size = 128M max_allowed_packet = 16M query_cache_type = 1 query_cache_limit = 64M query_cache_size = 256M join_buffer_size = 2M max_connections = 501 slow_query_log = 1 [mysqld] !includedir /etc/mysql/mariadb.conf.d/ [mysqld_safe] skip_log_error syslog [mariadb] plugin-load-add=ha_tokudb.so [mysqld] tokudb_create_index_online = on tokudb_cache_size = 12G tokudb_load_save_space = 1 tokudb_disable_prefetching = on # mariadb.conf.d/ [mariadb] plugin-load-add=ha_tokudb.so ## Global Variables ## SHOW GLOBAL VARIABLES WHERE Variable_name LIKE 'innodb%' OR Variable_name LIKE 'tokudb%' innodb_adaptive_flushing ON innodb_adaptive_flushing_lwm 10.000000 innodb_adaptive_hash_index ON innodb_adaptive_hash_index_partitions 8 innodb_adaptive_hash_index_parts 8 innodb_adaptive_max_sleep_delay 150000 innodb_autoextend_increment 64 innodb_autoinc_lock_mode 1 innodb_background_scrub_data_check_interval 3600 innodb_background_scrub_data_compressed OFF innodb_background_scrub_data_interval 604800 innodb_background_scrub_data_uncompressed OFF innodb_buf_dump_status_frequency 0 innodb_buffer_pool_chunk_size 134217728 innodb_buffer_pool_dump_at_shutdown ON innodb_buffer_pool_dump_now OFF innodb_buffer_pool_dump_pct 25 innodb_buffer_pool_filename ib_buffer_pool innodb_buffer_pool_instances 2 innodb_buffer_pool_load_abort OFF innodb_buffer_pool_load_at_startup ON innodb_buffer_pool_load_now OFF innodb_buffer_pool_populate OFF innodb_buffer_pool_size 12884901888 innodb_change_buffer_max_size 25 innodb_change_buffering all innodb_checksum_algorithm crc32 innodb_checksums ON innodb_cleaner_lsn_age_factor DEPRECATED innodb_cmp_per_index_enabled OFF innodb_commit_concurrency 0 innodb_compression_algorithm zlib innodb_compression_default OFF innodb_compression_failure_threshold_pct 5 innodb_compression_level 6 innodb_compression_pad_pct_max 50 innodb_concurrency_tickets 5000 innodb_corrupt_table_action deprecated innodb_data_file_path ibdata1:12M:autoextend innodb_data_home_dir innodb_deadlock_detect ON innodb_default_encryption_key_id 1 innodb_default_row_format dynamic innodb_defragment OFF innodb_defragment_fill_factor 0.900000 innodb_defragment_fill_factor_n_recs 20 innodb_defragment_frequency 40 innodb_defragment_n_pages 7 innodb_defragment_stats_accuracy 0 innodb_disable_sort_file_cache OFF innodb_disallow_writes OFF innodb_doublewrite ON innodb_empty_free_list_algorithm DEPRECATED innodb_encrypt_log OFF innodb_encrypt_tables OFF innodb_encryption_rotate_key_age 1 innodb_encryption_rotation_iops 100 innodb_encryption_threads 0 innodb_fake_changes OFF innodb_fast_shutdown 1 innodb_fatal_semaphore_wait_threshold 600 innodb_file_format Barracuda innodb_file_format_check ON innodb_file_format_max Barracuda innodb_file_per_table ON innodb_fill_factor 100 innodb_flush_log_at_timeout 1 innodb_flush_log_at_trx_commit 1 innodb_flush_method O_DIRECT innodb_flush_neighbors 1 innodb_flush_sync ON innodb_flushing_avg_loops 30 innodb_force_load_corrupted OFF innodb_force_primary_key OFF innodb_force_recovery 0 innodb_foreground_preflush DEPRECATED innodb_ft_aux_table innodb_ft_cache_size 8000000 innodb_ft_enable_diag_print OFF innodb_ft_enable_stopword ON innodb_ft_max_token_size 84 innodb_ft_min_token_size 3 innodb_ft_num_word_optimize 2000 innodb_ft_result_cache_limit 2000000000 innodb_ft_server_stopword_table innodb_ft_sort_pll_degree 2 innodb_ft_total_cache_size 640000000 innodb_ft_user_stopword_table innodb_idle_flush_pct 100 innodb_immediate_scrub_data_uncompressed OFF innodb_instrument_semaphores OFF innodb_io_capacity 400 innodb_io_capacity_max 2000 innodb_kill_idle_transaction 0 innodb_large_prefix ON innodb_lock_schedule_algorithm vats innodb_lock_wait_timeout 50 innodb_locking_fake_changes OFF innodb_locks_unsafe_for_binlog OFF innodb_log_arch_dir innodb_log_arch_expire_sec 0 innodb_log_archive OFF innodb_log_block_size 0 innodb_log_buffer_size 100663296 innodb_log_checksum_algorithm DEPRECATED innodb_log_checksums ON innodb_log_compressed_pages ON innodb_log_file_size 2147483648 innodb_log_files_in_group 2 innodb_log_group_home_dir ./ innodb_log_write_ahead_size 8192 innodb_lru_scan_depth 1024 innodb_max_bitmap_file_size 0 innodb_max_changed_pages 0 innodb_max_dirty_pages_pct 75.000000 innodb_max_dirty_pages_pct_lwm 0.000000 innodb_max_purge_lag 0 innodb_max_purge_lag_delay 0 innodb_max_undo_log_size 10485760 innodb_mirrored_log_groups 0 innodb_monitor_disable innodb_monitor_enable innodb_monitor_reset innodb_monitor_reset_all innodb_mtflush_threads 8 innodb_old_blocks_pct 37 innodb_old_blocks_time 1000 innodb_online_alter_log_max_size 134217728 innodb_open_files 400 innodb_optimize_fulltext_only OFF innodb_page_cleaners 2 innodb_page_size 16384 innodb_prefix_index_cluster_optimization OFF innodb_print_all_deadlocks OFF innodb_purge_batch_size 300 innodb_purge_rseg_truncate_frequency 128 innodb_purge_threads 4 innodb_random_read_ahead OFF innodb_read_ahead_threshold 56 innodb_read_io_threads 4 innodb_read_only OFF innodb_replication_delay 0 innodb_rollback_on_timeout OFF innodb_rollback_segments 128 innodb_sched_priority_cleaner 0 innodb_scrub_log OFF innodb_scrub_log_speed 256 innodb_show_locks_held 0 innodb_show_verbose_locks 0 innodb_sort_buffer_size 1048576 innodb_spin_wait_delay 6 innodb_stats_auto_recalc ON innodb_stats_include_delete_marked OFF innodb_stats_method nulls_equal innodb_stats_modified_counter 0 innodb_stats_on_metadata OFF innodb_stats_persistent ON innodb_stats_persistent_sample_pages 20 innodb_stats_sample_pages 8 innodb_stats_traditional ON innodb_stats_transient_sample_pages 8 innodb_status_output OFF innodb_status_output_locks OFF innodb_strict_mode ON innodb_support_xa ON innodb_sync_array_size 1 innodb_sync_spin_loops 30 innodb_table_locks ON innodb_temp_data_file_path ibtmp1:12M:autoextend innodb_thread_concurrency 0 innodb_thread_sleep_delay 10000 innodb_tmpdir innodb_track_changed_pages OFF innodb_track_redo_log_now OFF innodb_undo_directory ./ innodb_undo_log_truncate OFF innodb_undo_logs 128 innodb_undo_tablespaces 0 innodb_use_atomic_writes ON innodb_use_fallocate OFF innodb_use_global_flush_log_at_trx_commit OFF innodb_use_mtflush OFF innodb_use_native_aio ON innodb_use_stacktrace OFF innodb_use_trim ON innodb_version 5.7.14 innodb_write_io_threads 4 tokudb_alter_print_error OFF tokudb_analyze_delete_fraction 1.000000 tokudb_analyze_in_background OFF tokudb_analyze_mode TOKUDB_ANALYZE_STANDARD tokudb_analyze_throttle 0 tokudb_analyze_time 5 tokudb_auto_analyze 0 tokudb_block_size 4194304 tokudb_bulk_fetch ON tokudb_cache_size 12884901888 tokudb_cachetable_pool_threads 0 tokudb_cardinality_scale_percent 50 tokudb_check_jemalloc ON tokudb_checkpoint_lock OFF tokudb_checkpoint_on_flush_logs OFF tokudb_checkpoint_pool_threads 0 tokudb_checkpointing_period 60 tokudb_cleaner_iterations 5 tokudb_cleaner_period 1 tokudb_client_pool_threads 0 tokudb_commit_sync ON tokudb_compress_buffers_before_eviction ON tokudb_create_index_online ON tokudb_data_dir tokudb_debug 0 tokudb_dir_per_db OFF tokudb_directio OFF tokudb_disable_hot_alter OFF tokudb_disable_prefetching ON tokudb_disable_slow_alter OFF tokudb_empty_scan rl tokudb_enable_partial_eviction ON tokudb_fanout 16 tokudb_fs_reserve_percent 5 tokudb_fsync_log_period 0 tokudb_hide_default_row_format ON tokudb_killed_time 4000 tokudb_last_lock_timeout tokudb_load_save_space ON tokudb_loader_memory_size 100000000 tokudb_lock_timeout 4000 tokudb_lock_timeout_debug 1 tokudb_log_dir tokudb_max_lock_memory 1610612736 tokudb_optimize_index_fraction 1.000000 tokudb_optimize_index_name tokudb_optimize_throttle 0 tokudb_pk_insert_mode 1 tokudb_prelock_empty ON tokudb_read_block_size 65536 tokudb_read_buf_size 131072 tokudb_read_status_frequency 10000 tokudb_row_format tokudb_zlib tokudb_rpl_check_readonly ON tokudb_rpl_lookup_rows ON tokudb_rpl_lookup_rows_delay 0 tokudb_rpl_unique_checks ON tokudb_rpl_unique_checks_delay 0 tokudb_strip_frm_data OFF tokudb_support_xa ON tokudb_tmp_dir tokudb_version 5.6.35-80.0 tokudb_write_status_frequency 1000 Why is the memory consumption that high? Is there something I missed? Are there any memory profiling tools available for MariaDB, which doesn't kill performance? Sadly MariaDB does not have the sys schema or performance_schema.memory_summary_* tables introduced in MySQL 5.7. I don't think this is connected to this problem, but it's worth mentioning: If I run the mail command-line program, I get this error: /usr/bin/mailx: /usr/lib/x86_64-linux-gnu/libmariadbclient.so.18: no version information available (required by /usr/lib/x86_64-linux-gnu/libmu_auth.so.5) But sending the mail works. This started happending after the Debian and MariaDB upgrade. Thanks in advance
Asked by Gabscap (111 rep)
Jul 29, 2017, 11:55 PM
Last activity: Aug 30, 2017, 03:02 PM