1

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:

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

Your Answer

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

Browse other questions tagged or ask your own question.