I'm trying to face difficulties with my database lately. I've migrated from MyISAM to TokuDB v7.0.4 and InnoDB. I'm using MariaDB – 5.5.34-MariaDB-1~wheezy-log. My problem is that MariaDB fills up RAM to about 90% and then starts swapping. After swap is full, performance goes really bad – top
shows load at 40 avg. When i restart(kill -9 to mysql process) RAM and SWAP empties and everything starts from the scratch.
I have two databases.
First one is write and read intensive:
- 1152 tables with avg of 3 million rows
- all tables uses TokuDB engine
Second one:
- 113 tables with avg of 1 million rows
- all tables uses InnoDB engine
My config /etc/mysql/my.cnf:
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
skip-external-locking
tmp_table_size = 128MB
key_buffer = 75M
max_allowed_packet = 1000M
thread_stack = 192K
thread_cache_size = 8
myisam-recover = BACKUP
max_connections = 500
query_cache_limit = 3M
query_cache_size = 150M
log_error = /var/log/mysql/error.log
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 30
log-queries-not-using-indexes
expire_logs_days = 1
max_binlog_size = 500M
innodb_buffer_pool_size = 4G
innodb_log_file_size = 100M
innodb_log_buffer_size = 128M
innodb_data_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_mirrored_log_groups = 1
innodb_log_group_home_dir = /var/lib/mysql
innodb_log_files_in_group = 2
innodb_stats_on_metadata=0
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_flush_log_at_trx_commit = 2
innodb_max_dirty_pages_pct = 90
[mysqldump]
quick
quote-names
max_allowed_packet = 24M
[isamchk]
key_buffer = 120M
[myisamchk]
key_buffer = 120M
sort_buffer_size = 20M
read_buffer = 20M
write_buffer = 20M
tokudb_cache_size = 5G
Server specs:
- OS: Debian Wheezy 64Bit
- Intel Xeon E5506@2.13Ghz with 4 cores
- 16 GB RAM
- 2TB SATA Hard Drive
Output of mysqltuner script:
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.34-MariaDB-1~wheezy-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster
[--] Data in TokuDB tables: 45G (Tables: 2956)
[--] Data in MyISAM tables: 88B (Tables: 10)
[--] Data in InnoDB tables: 9G (Tables: 250)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 2983
-------- Performance Metrics -------------------------------------------------
[--] Up for: 12h 16m 36s (40M q [906.321 qps], 81K conn, TX: 7B, RX: 15B)
[--] Reads / Writes: 30% / 70%
[--] Total buffers: 4.4G global + 2.7M per thread (500 max threads)
[OK] Maximum possible memory usage: 5.7G (36% of installed RAM)
[OK] Slow queries: 1% (613K/40M)
[OK] Highest usage of available connections: 3% (17/500)
[OK] Key buffer size / total MyISAM indexes: 75.0M/123.0K
[OK] Key buffer hit rate: 95.0% (202 cached / 10 reads)
[OK] Query cache efficiency: 38.4% (7M cached / 18M selects)
[!!] Query cache prunes per day: 3629953
[OK] Sorts requiring temporary tables: 0% (6 temp sorts / 69K sorts)
[OK] Temporary tables created on disk: 0% (2K on disk / 655K total)
[OK] Thread cache hit rate: 99% (53 created / 81K connections)
[!!] Table cache hit rate: 0% (400 open / 911K opened)
[OK] Open file limit used: 0% (5/2K)
[OK] Table locks acquired immediately: 100% (41M immediate / 41M locks)
[!!] InnoDB data size / buffer pool: 9.1G/4.0G
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Increasing the query_cache size over 128M may reduce performance
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
long_query_time (<= 10)
query_cache_size (> 150M) [see warning above]
table_cache (> 400)
innodb_buffer_pool_size (>= 9G)
I'm not experienced DBA and I've no ideas left. I've tried to play with tweaking of innodb buffer poll and tokudb cache size. I've tried with turning off query cache. Nothing helped. So I've decided to ask You for help.
Update
I've decided to show you output of show global status
. Maybe someone could spot something suspicious.
Variable_name Value
Aborted_clients 6
Aborted_connects 9
Access_denied_errors 15
Aria_pagecache_blocks_not_flushed 0
Aria_pagecache_blocks_unused 15737
Aria_pagecache_blocks_used 11974
Aria_pagecache_read_requests 3266804
Aria_pagecache_reads 2723
Aria_pagecache_write_requests 82149
Aria_pagecache_writes 0
Aria_transaction_log_syncs 0
Binlog_commits 0
Binlog_group_commits 0
Binlog_snapshot_file
Binlog_snapshot_position 0
Binlog_bytes_written 0
Binlog_cache_disk_use 0
Binlog_cache_use 0
Binlog_stmt_cache_disk_use 0
Binlog_stmt_cache_use 0
Busy_time 0.000000
Bytes_received 28113353524
Bytes_sent 12542509166
Com_admin_commands 5
Com_alter_db 0
Com_alter_db_upgrade 0
Com_alter_event 0
Com_alter_function 0
Com_alter_procedure 0
Com_alter_server 0
Com_alter_table 1
Com_alter_tablespace 0
Com_analyze 0
Com_assign_to_keycache 0
Com_begin 44501
Com_binlog 0
Com_call_procedure 0
Com_change_db 4437
Com_change_master 0
Com_check 0
Com_checksum 0
Com_commit 44498
Com_create_db 1
Com_create_event 0
Com_create_function 0
Com_create_index 0
Com_create_procedure 0
Com_create_server 0
Com_create_table 490956
Com_create_trigger 0
Com_create_udf 0
Com_create_user 0
Com_create_view 0
Com_dealloc_sql 0
Com_delete 59
Com_delete_multi 0
Com_do 0
Com_drop_db 1
Com_drop_event 0
Com_drop_function 0
Com_drop_index 0
Com_drop_procedure 0
Com_drop_server 0
Com_drop_table 1
Com_drop_trigger 0
Com_drop_user 1
Com_drop_view 0
Com_empty_query 0
Com_execute_sql 0
Com_flush 1
Com_grant 3
Com_ha_close 0
Com_ha_open 0
Com_ha_read 0
Com_help 0
Com_insert 46287224
Com_insert_select 0
Com_install_plugin 0
Com_kill 0
Com_load 0
Com_lock_tables 0
Com_optimize 0
Com_preload_keys 0
Com_prepare_sql 0
Com_purge 0
Com_purge_before_date 0
Com_release_savepoint 0
Com_rename_table 0
Com_rename_user 0
Com_repair 0
Com_replace 0
Com_replace_select 0
Com_reset 0
Com_resignal 0
Com_revoke 4
Com_revoke_all 0
Com_rollback 0
Com_rollback_to_savepoint 0
Com_savepoint 0
Com_select 27056617
Com_set_option 1919230
Com_show_authors 0
Com_show_binlog_events 0
Com_show_binlogs 44
Com_show_charsets 0
Com_show_client_statistics 0
Com_show_collations 0
Com_show_contributors 0
Com_show_create_db 0
Com_show_create_event 0
Com_show_create_func 0
Com_show_create_proc 0
Com_show_create_table 2966
Com_show_create_trigger 0
Com_show_databases 43
Com_show_engine_logs 0
Com_show_engine_mutex 0
Com_show_engine_status 0
Com_show_errors 0
Com_show_events 0
Com_show_fields 1031
Com_show_function_status 1
Com_show_grants 8
Com_show_index_statistics 0
Com_show_keys 9
Com_show_master_status 14
Com_show_open_tables 0
Com_show_plugins 134
Com_show_privileges 0
Com_show_procedure_status 1
Com_show_processlist 27
Com_show_profile 0
Com_show_profiles 0
Com_show_relaylog_events 0
Com_show_slave_hosts 0
Com_show_slave_status 14
Com_show_status 4
Com_show_storage_engines 1
Com_show_table_statistics 0
Com_show_table_status 3091
Com_show_tables 44570
Com_show_triggers 1480
Com_show_user_statistics 0
Com_show_variables 1513
Com_show_warnings 0
Com_signal 0
Com_slave_start 0
Com_slave_stop 0
Com_stmt_close 0
Com_stmt_execute 0
Com_stmt_fetch 0
Com_stmt_prepare 0
Com_stmt_reprepare 0
Com_stmt_reset 0
Com_stmt_send_long_data 0
Com_truncate 0
Com_uninstall_plugin 0
Com_unlock_tables 0
Com_update 415025
Com_update_multi 1
Com_xa_commit 0
Com_xa_end 0
Com_xa_prepare 0
Com_xa_recover 0
Com_xa_rollback 0
Com_xa_start 0
Compression OFF
Connections 100165
Cpu_time 0.000000
Created_tmp_disk_tables 2947
Created_tmp_files 38
Created_tmp_tables 697141
Delayed_errors 0
Delayed_insert_threads 0
Delayed_writes 0
Empty_queries 4693258
Executed_events 0
Executed_triggers 0
Feature_dynamic_columns 0
Feature_fulltext 0
Feature_gis 0
Feature_locale 0
Feature_subquery 34643715
Feature_timezone 0
Feature_trigger 0
Feature_xml 0
Flush_commands 2
Handler_commit 52953374
Handler_delete 176
Handler_discover 0
Handler_icp_attempts 540485778
Handler_icp_match 540448062
Handler_mrr_init 0
Handler_mrr_key_refills 0
Handler_mrr_rowid_refills 0
Handler_prepare 0
Handler_read_first 419
Handler_read_key 302247165
Handler_read_last 424
Handler_read_next 3416576982
Handler_read_prev 998710623
Handler_read_rnd 10721555
Handler_read_rnd_deleted 1437
Handler_read_rnd_next 680507748
Handler_rollback 1731700
Handler_savepoint 0
Handler_savepoint_rollback 0
Handler_tmp_update 346421
Handler_tmp_write 115890114
Handler_update 145750746
Handler_write 282321902
Innodb_adaptive_hash_cells 8850461
Innodb_adaptive_hash_hash_searches 2868200294
Innodb_adaptive_hash_heap_buffers 48717
Innodb_adaptive_hash_non_hash_searches 129200278
Innodb_background_log_sync 80520
Innodb_buffer_pool_bytes_data 3496755200
Innodb_buffer_pool_bytes_dirty 78446592
Innodb_buffer_pool_pages_data 213425
Innodb_buffer_pool_pages_dirty 4788
Innodb_buffer_pool_pages_flushed 1714891
Innodb_buffer_pool_pages_free 0
Innodb_buffer_pool_pages_LRU_flushed 950
Innodb_buffer_pool_pages_made_not_young 0
Innodb_buffer_pool_pages_made_young 3224808
Innodb_buffer_pool_pages_misc 48718
Innodb_buffer_pool_pages_old 78764
Innodb_buffer_pool_pages_total 262143
Innodb_buffer_pool_read_ahead 999051
Innodb_buffer_pool_read_ahead_evicted 65880
Innodb_buffer_pool_read_ahead_rnd 0
Innodb_buffer_pool_read_requests 9880015915
Innodb_buffer_pool_reads 1489673
Innodb_buffer_pool_wait_free 3
Innodb_buffer_pool_write_requests 416862563
Innodb_checkpoint_age 62569176
Innodb_checkpoint_max_age 169158206
Innodb_checkpoint_target_age 163872013
Innodb_current_row_locks 0
Innodb_data_fsyncs 163114
Innodb_data_pending_fsyncs 0
Innodb_data_pending_reads 0
Innodb_data_pending_writes 0
Innodb_data_read 42152087552
Innodb_data_reads 1589888
Innodb_data_writes 12690787
Innodb_data_written 81596944384
Innodb_dblwr_pages_written 1714891
Innodb_dblwr_writes 45021
Innodb_deadlocks 0
Innodb_descriptors_memory 8000
Innodb_dict_tables 258
Innodb_have_atomic_builtins ON
Innodb_history_list_length 3440
Innodb_ibuf_discarded_delete_marks 0
Innodb_ibuf_discarded_deletes 0
Innodb_ibuf_discarded_inserts 0
Innodb_ibuf_free_list 65
Innodb_ibuf_merged_delete_marks 38713
Innodb_ibuf_merged_deletes 7446
Innodb_ibuf_merged_inserts 2751499
Innodb_ibuf_merges 87150
Innodb_ibuf_segment_size 67
Innodb_ibuf_size 1
Innodb_log_waits 0
Innodb_log_write_requests 38893068
Innodb_log_writes 12298675
Innodb_lsn_current 359356342987
Innodb_lsn_flushed 359356017372
Innodb_lsn_last_checkpoint 359293773811
Innodb_master_thread_1_second_loops 91913
Innodb_master_thread_10_second_loops 9079
Innodb_master_thread_background_loops 3086
Innodb_master_thread_main_flush_loops 3083
Innodb_master_thread_sleeps 91773
Innodb_max_trx_id 123944571
Innodb_mem_adaptive_hash 869003616
Innodb_mem_dictionary 19665864
Innodb_mem_total 4408213504
Innodb_mutex_os_waits 647322
Innodb_mutex_spin_rounds 22796538
Innodb_mutex_spin_waits 2620935
Innodb_oldest_view_low_limit_trx_id 123944434
Innodb_os_log_fsyncs 54231
Innodb_os_log_pending_fsyncs 0
Innodb_os_log_pending_writes 0
Innodb_os_log_written 25398928384
Innodb_page_size 16384
Innodb_pages_created 281917
Innodb_pages_read 2572625
Innodb_pages_written 1714891
Innodb_purge_trx_id 123926312
Innodb_purge_undo_no 0
Innodb_read_views_memory 216
Innodb_row_lock_current_waits 0
Innodb_row_lock_time 36
Innodb_row_lock_time_avg 5
Innodb_row_lock_time_max 13
Innodb_row_lock_waits 7
Innodb_rows_deleted 174
Innodb_rows_inserted 31651778
Innodb_rows_read 3639327429
Innodb_rows_updated 145697669
Innodb_s_lock_os_waits 135027
Innodb_s_lock_spin_rounds 4627415
Innodb_s_lock_spin_waits 475342
Innodb_truncated_status_writes 0
Innodb_x_lock_os_waits 550959
Innodb_x_lock_spin_rounds 20838578
Innodb_x_lock_spin_waits 517023
Key_blocks_not_flushed 0
Key_blocks_unused 62954
Key_blocks_used 4
Key_blocks_warm 0
Key_read_requests 203
Key_reads 11
Key_write_requests 17
Key_writes 13
Last_query_cost 0.000000
Max_used_connections 17
Not_flushed_delayed_rows 0
Open_files 5
Open_streams 0
Open_table_definitions 400
Open_tables 400
Opened_files 1007046
Opened_table_definitions 510655
Opened_tables 1125113
Opened_views 0
Performance_schema_cond_classes_lost 0
Performance_schema_cond_instances_lost 0
Performance_schema_file_classes_lost 0
Performance_schema_file_handles_lost 0
Performance_schema_file_instances_lost 0
Performance_schema_locker_lost 0
Performance_schema_mutex_classes_lost 0
Performance_schema_mutex_instances_lost 0
Performance_schema_rwlock_classes_lost 0
Performance_schema_rwlock_instances_lost 0
Performance_schema_table_handles_lost 0
Performance_schema_table_instances_lost 0
Performance_schema_thread_classes_lost 0
Performance_schema_thread_instances_lost 0
Prepared_stmt_count 0
Qcache_free_blocks 25955
Qcache_free_memory 71441808
Qcache_hits 18495047
Qcache_inserts 7339583
Qcache_lowmem_prunes 5231512
Qcache_not_cached 1220143
Qcache_queries_in_cache 67517
Qcache_total_blocks 161103
Queries 76417873
Questions 76417873
Rows_read 5355811398
Rows_sent 62045727
Rows_tmp_read 21212607
Rpl_status AUTH_MASTER
Select_full_join 62
Select_full_range_join 0
Select_range 6794385
Select_range_check 0
Select_scan 700903
Slave_heartbeat_period 1800.000
Slave_open_temp_tables 0
Slave_received_heartbeats 0
Slave_retried_transactions 0
Slave_running OFF
Slow_launch_threads 0
Slow_queries 639282
Sort_merge_passes 9
Sort_range 64818
Sort_rows 5371097
Sort_scan 21126
Sphinx_error
Sphinx_time
Sphinx_total
Sphinx_total_found
Sphinx_word_count
Sphinx_words
Ssl_accept_renegotiates 0
Ssl_accepts 0
Ssl_callback_cache_hits 0
Ssl_cipher
Ssl_cipher_list
Ssl_client_connects 0
Ssl_connect_renegotiates 0
Ssl_ctx_verify_depth 0
Ssl_ctx_verify_mode 0
Ssl_default_timeout 0
Ssl_finished_accepts 0
Ssl_finished_connects 0
Ssl_session_cache_hits 0
Ssl_session_cache_misses 0
Ssl_session_cache_mode NONE
Ssl_session_cache_overflows 0
Ssl_session_cache_size 0
Ssl_session_cache_timeouts 0
Ssl_sessions_reused 0
Ssl_used_session_cache_entries 0
Ssl_verify_depth 0
Ssl_verify_mode 0
Ssl_version
Subquery_cache_hit 0
Subquery_cache_miss 29396
Syncs 3009
Table_locks_immediate 89481327
Table_locks_waited 0
Tc_log_max_pages_used 0
Tc_log_page_size 4096
Tc_log_page_waits 0
Threadpool_idle_threads 0
Threadpool_threads 0
Threads_cached 6
Threads_connected 3
Threads_created 111
Threads_running 2
Tokudb_DB_OPENS 1212408
Tokudb_DB_CLOSES 1211449
Tokudb_DB_OPEN_CURRENT 959
Tokudb_DB_OPEN_MAX 1368
Tokudb_CHECKPOINT_PERIOD 60
Tokudb_CHECKPOINT_LAST_BEGAN Tue Dec 31 07:36:41 2013
Tokudb_CHECKPOINT_LAST_COMPLETE_BEGAN Tue Dec 31 07:36:41 2013
Tokudb_CHECKPOINT_LAST_COMPLETE_ENDED Tue Dec 31 07:36:44 2013
Tokudb_CHECKPOINT_TAKEN 1455
Tokudb_CHECKPOINT_FAILED 0
Tokudb_CHECKPOINT_BEGIN_TIME 4658342
Tokudb_CHECKPOINT_LONG_BEGIN_TIME 0
Tokudb_CHECKPOINT_LONG_BEGIN_COUNT 0
Tokudb_CACHETABLE_MISS 132275
Tokudb_CACHETABLE_MISS_TIME 836440287
Tokudb_CACHETABLE_PREFETCHES 13724
Tokudb_CACHETABLE_SIZE_CURRENT 8421028143
Tokudb_CACHETABLE_SIZE_LIMIT 8958926848
Tokudb_CACHETABLE_SIZE_WRITING 0
Tokudb_CACHETABLE_SIZE_NONLEAF 7649372
Tokudb_CACHETABLE_SIZE_LEAF 8413376851
Tokudb_CACHETABLE_SIZE_ROLLBACK 1920
Tokudb_CACHETABLE_SIZE_CACHEPRESSURE 292993
Tokudb_CACHETABLE_EVICTIONS 139222
Tokudb_CACHETABLE_CLEANER_EXECUTIONS 103154
Tokudb_CACHETABLE_CLEANER_PERIOD 1
Tokudb_CACHETABLE_CLEANER_ITERATIONS 5
Tokudb_CACHETABLE_WAIT_PRESSURE_COUNT 0
Tokudb_CACHETABLE_WAIT_PRESSURE_TIME 0
Tokudb_CACHETABLE_LONG_WAIT_PRESSURE_COUNT 0
Tokudb_CACHETABLE_LONG_WAIT_PRESSURE_TIME 0
Tokudb_LOCKTREE_MEMORY_SIZE 0
Tokudb_LOCKTREE_MEMORY_SIZE_LIMIT 1052756992
Tokudb_LOCKTREE_ESCALATION_NUM 0
Tokudb_LOCKTREE_ESCALATION_SECONDS 0.000000
Tokudb_LOCKTREE_LATEST_POST_ESCALATION_MEMORY_SIZE 0
Tokudb_LOCKTREE_OPEN_CURRENT 961
Tokudb_LOCKTREE_PENDING_LOCK_REQUESTS 0
Tokudb_LOCKTREE_STO_ELIGIBLE_NUM 0
Tokudb_LOCKTREE_STO_ENDED_NUM 106
Tokudb_LOCKTREE_STO_ENDED_SECONDS 0.097662
Tokudb_LOCKTREE_WAIT_COUNT 38
Tokudb_LOCKTREE_WAIT_TIME 10668280
Tokudb_LOCKTREE_LONG_WAIT_COUNT 4
Tokudb_LOCKTREE_LONG_WAIT_TIME 8913942
Tokudb_LOCKTREE_TIMEOUT_COUNT 1
Tokudb_LOCKTREE_WAIT_ESCALATION_COUNT 0
Tokudb_LOCKTREE_WAIT_ESCALATION_TIME 0
Tokudb_LOCKTREE_LONG_WAIT_ESCALATION_COUNT 0
Tokudb_LOCKTREE_LONG_WAIT_ESCALATION_TIME 0
Tokudb_DICTIONARY_UPDATES 0
Tokudb_DICTIONARY_BROADCAST_UPDATES 0
Tokudb_DESCRIPTOR_SET 2150
Tokudb_MESSAGES_IGNORED_BY_LEAF_DUE_TO_MSN 911895
Tokudb_LEAF_NODES_FLUSHED_NOT_CHECKPOINT 24913
Tokudb_LEAF_NODES_FLUSHED_NOT_CHECKPOINT_BYTES 41927164416
Tokudb_LEAF_NODES_FLUSHED_NOT_CHECKPOINT_UNCOMPRESSED_BYTES 41930114613
Tokudb_LEAF_NODES_FLUSHED_NOT_CHECKPOINT_SECONDS 358.312359
Tokudb_NONLEAF_NODES_FLUSHED_TO_DISK_NOT_CHECKPOINT 5244
Tokudb_NONLEAF_NODES_FLUSHED_TO_DISK_NOT_CHECKPOINT_BYTES 80217088
Tokudb_NONLEAF_NODES_FLUSHED_TO_DISK_NOT_CHECKPOINT_UNCOMPRESSE 78373409
Tokudb_NONLEAF_NODES_FLUSHED_TO_DISK_NOT_CHECKPOINT_SECONDS 70.766602
Tokudb_LEAF_NODES_FLUSHED_CHECKPOINT 109957
Tokudb_LEAF_NODES_FLUSHED_CHECKPOINT_BYTES 198141904896
Tokudb_LEAF_NODES_FLUSHED_CHECKPOINT_UNCOMPRESSED_BYTES 198100386193
Tokudb_LEAF_NODES_FLUSHED_CHECKPOINT_SECONDS 1588.077420
Tokudb_NONLEAF_NODES_FLUSHED_TO_DISK_CHECKPOINT 11964
Tokudb_NONLEAF_NODES_FLUSHED_TO_DISK_CHECKPOINT_BYTES 197548544
Tokudb_NONLEAF_NODES_FLUSHED_TO_DISK_CHECKPOINT_UNCOMPRESSED_BY 193219858
Tokudb_NONLEAF_NODES_FLUSHED_TO_DISK_CHECKPOINT_SECONDS 101.451135
Tokudb_NONLEAF_NODE_PARTIAL_EVICTIONS 2243
Tokudb_NONLEAF_NODE_PARTIAL_EVICTIONS_BYTES 475479
Tokudb_LEAF_NODE_PARTIAL_EVICTIONS 130527
Tokudb_LEAF_NODE_PARTIAL_EVICTIONS_BYTES 23918330873
Tokudb_LEAF_NODE_FULL_EVICTIONS 101982
Tokudb_LEAF_NODE_FULL_EVICTIONS_BYTES 67069576676
Tokudb_NONLEAF_NODE_FULL_EVICTIONS 37240
Tokudb_NONLEAF_NODE_FULL_EVICTIONS_BYTES 113897114
Tokudb_LEAF_NODES_CREATED 4359
Tokudb_NONLEAF_NODES_CREATED 286
Tokudb_LEAF_NODES_DESTROYED 211939
Tokudb_NONLEAF_NODES_DESTROYED 49204
Tokudb_MESSAGES_INJECTED_AT_ROOT_BYTES 69875936
Tokudb_MESSAGES_FLUSHED_FROM_H1_TO_LEAVES_BYTES 78773311
Tokudb_MESSAGES_IN_TREES_ESTIMATE_BYTES 18446744073700654241
Tokudb_MESSAGES_INJECTED_AT_ROOT 1500645
Tokudb_BROADCASE_MESSAGES_INJECTED_AT_ROOT 0
Tokudb_BASEMENTS_DECOMPRESSED_TARGET_QUERY 3019
Tokudb_BASEMENTS_DECOMPRESSED_PRELOCKED_RANGE 409
Tokudb_BASEMENTS_DECOMPRESSED_PREFETCH 0
Tokudb_BASEMENTS_DECOMPRESSED_FOR_WRITE 8130
Tokudb_BUFFERS_DECOMPRESSED_TARGET_QUERY 36606
Tokudb_BUFFERS_DECOMPRESSED_PRELOCKED_RANGE 2031
Tokudb_BUFFERS_DECOMPRESSED_PREFETCH 0
Tokudb_BUFFERS_DECOMPRESSED_FOR_WRITE 75381
Tokudb_PIVOTS_FETCHED_FOR_QUERY 70130
Tokudb_PIVOTS_FETCHED_FOR_QUERY_BYTES 731984896
Tokudb_PIVOTS_FETCHED_FOR_QUERY_SECONDS 252.842367
Tokudb_PIVOTS_FETCHED_FOR_PREFETCH 13724
Tokudb_PIVOTS_FETCHED_FOR_PREFETCH_BYTES 449675776
Tokudb_PIVOTS_FETCHED_FOR_PREFETCH_SECONDS 149.464950
Tokudb_PIVOTS_FETCHED_FOR_WRITE 1901
Tokudb_PIVOTS_FETCHED_FOR_WRITE_BYTES 33412096
Tokudb_PIVOTS_FETCHED_FOR_WRITE_SECONDS 20.615276
Tokudb_BASEMENTS_FETCHED_TARGET_QUERY 99068
Tokudb_BASEMENTS_FETCHED_TARGET_QUERY_BYTES 6364541952
Tokudb_BASEMENTS_FETCHED_TARGET_QUERY_SECONDS 414.452038
Tokudb_BASEMENTS_FETCHED_PRELOCKED_RANGE 44959
Tokudb_BASEMENTS_FETCHED_PRELOCKED_RANGE_BYTES 5496332288
Tokudb_BASEMENTS_FETCHED_PRELOCKED_RANGE_SECONDS 44.115034
Tokudb_BASEMENTS_FETCHED_PREFETCH 287803
Tokudb_BASEMENTS_FETCHED_PREFETCH_BYTES 35601422848
Tokudb_BASEMENTS_FETCHED_PREFETCH_SECONDS 304.268452
Tokudb_BASEMENTS_FETCHED_FOR_WRITE 102644
Tokudb_BASEMENTS_FETCHED_FOR_WRITE_BYTES 13071339520
Tokudb_BASEMENTS_FETCHED_FOR_WRITE_SECONDS 208.484657
Tokudb_BUFFERS_FETCHED_TARGET_QUERY 3628
Tokudb_BUFFERS_FETCHED_TARGET_QUERY_BYTES 1894912
Tokudb_BUFFERS_FETCHED_TARGET_QUERY_SECONDS 3.171866
Tokudb_BUFFERS_FETCHED_PRELOCKED_RANGE 4620
Tokudb_BUFFERS_FETCHED_PRELOCKED_RANGE_BYTES 2748416
Tokudb_BUFFERS_FETCHED_PRELOCKED_RANGE_SECONDS 0.004685
Tokudb_BUFFERS_FETCHED_PREFETCH 0
Tokudb_BUFFERS_FETCHED_PREFETCH_BYTES 0
Tokudb_BUFFERS_FETCHED_PREFETCH_SECONDS 0.000000
Tokudb_BUFFERS_FETCHED_FOR_WRITE 20512
Tokudb_BUFFERS_FETCHED_FOR_WRITE_BYTES 21059584
Tokudb_BUFFERS_FETCHED_FOR_WRITE_SECONDS 6.374809
Tokudb_LEAF_COMPRESSION_TO_MEMORY_SECONDS 141.270891
Tokudb_LEAF_SERIALIZATION_TO_MEMORY_SECONDS 554.255095
Tokudb_LEAF_DECOMPRESSION_TO_MEMORY_SECONDS 36.843302
Tokudb_LEAF_DESERIALIZATION_TO_MEMORY_SECONDS 189.921701
Tokudb_NONLEAF_COMPRESSION_TO_MEMORY_SECONDS 1.267383
Tokudb_NONLEAF_SERIALIZATION_TO_MEMORY_SECONDS 1.102747
Tokudb_NONLEAF_DECOMPRESSION_TO_MEMORY_SECONDS 0.332357
Tokudb_NONLEAF_DESERIALIZATION_TO_MEMORY_SECONDS 2.638986
Tokudb_PROMOTION_ROOTS_SPLIT 271
Tokudb_PROMOTION_LEAF_ROOTS_INJECTED_INTO 90293
Tokudb_PROMOTION_H1_ROOTS_INJECTED_INTO 300114
Tokudb_PROMOTION_INJECTIONS_AT_DEPTH_0 90649
Tokudb_PROMOTION_INJECTIONS_AT_DEPTH_1 2640443
Tokudb_PROMOTION_INJECTIONS_AT_DEPTH_2 15175801
Tokudb_PROMOTION_INJECTIONS_AT_DEPTH_3 2527856
Tokudb_PROMOTION_INJECTIONS_LOWER_THAN_DEPTH_3 0
Tokudb_PROMOTION_STOPPED_NONEMPTY_BUFFER 1196160
Tokudb_PROMOTION_STOPPED_AT_HEIGHT_1 3797
Tokudb_PROMOTION_STOPPED_CHILD_LOCKED_OR_NOT_IN_MEMORY 58
Tokudb_PROMOTION_STOPPED_CHILD_NOT_FULLY_IN_MEMORY 1
Tokudb_PROMOTION_STOPPED_AFTER_LOCKING_CHILD 236
Tokudb_TXN_BEGIN 301685794
Tokudb_TXN_BEGIN_READ_ONLY 4054465
Tokudb_TXN_COMMITS 52337225
Tokudb_TXN_ABORTS 253403034
Tokudb_LOGGER_WRITES 170699
Tokudb_LOGGER_WRITES_BYTES 3321447066
Tokudb_LOGGER_WRITES_UNCOMPRESSED_BYTES 3321447066
Tokudb_LOGGER_WRITES_SECONDS 73.974175
Tokudb_LOADER_NUM_CREATED 0
Tokudb_LOADER_NUM_CURRENT 0
Tokudb_LOADER_NUM_MAX 0
Tokudb_MEM_ESTIMATED_MAXIMUM_MEMORY_FOOTPRINT 0
Tokudb_FILESYSTEM_THREADS_BLOCKED_BY_FULL_DISK 0
Tokudb_FILESYSTEM_FSYNC_TIME 2596297102
Tokudb_FILESYSTEM_FSYNC_NUM 1661099
Tokudb_FILESYSTEM_LONG_FSYNC_TIME 211408334
Tokudb_FILESYSTEM_LONG_FSYNC_NUM 119
Uptime 87347
Uptime_since_flush_status 87347
Really appreciate your help ! Thank you !
Best Answer
Why are you using InnoDB for some tables and TokuDB for others? As for memory usage, what is "top" reporting for the mysqld process on the server? Looking at the my.cnf you provided your MySQL server shouldn't be using more than 9G of RAM. If mysqld RSS is indeed growing to the 16G mark try lowering both the InnoDB and TokuDB cache sizes to see if that lowers the RSS.