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
Last activity: Aug 30, 2017, 03:02 PM