Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 !

share|improve this question
    
Do you really need max_connections = 500? What happens if you lower that to 100? –  ypercube Dec 31 '13 at 9:44
    
You have assigned 4GB for the Innodb buffer pool size and another 5GB for the TokuDB cache table. Plus a maximum of 500 x memory_per_connection. Note that some buffers are assigned per connection (like the read, sort and join buffers.) –  ypercube Dec 31 '13 at 10:06
    
Besides lowering the above, you should investigate whether your application is opening (and closing) too many connections or is creating a lot of temp tables. –  ypercube Dec 31 '13 at 10:16
1  
And consider increasing the server's RAM. Your databases are about 9+45 = 54GB in total. Increasing RAM to 64GB (or more) would certainly help you tune mysql. –  ypercube Dec 31 '13 at 10:19
    
Thanks will look into that. Unfortunately increasing RAM is impossible due to financial bounds. –  pyro Jan 4 at 10:26
add comment

3 Answers

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.

share|improve this answer
    
I've tried to lower cache sizes - unfortunately it didn't help. Prime reason behind using InnoDB and TokuDB at the same time is that TokuDB doesn't support foreign keys. –  pyro Dec 31 '13 at 6:35
    
top shows: PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 8626 mysql 20 0 23,8g 13g 5276 S 62,5 87,5 722:47.37 mysqld –  pyro Dec 31 '13 at 6:37
    
Have you restarted mysql since changing variables in my.cnf? I see that tokudb_cache_size is set to 5G in your my.cnf but the output from "show global status" confirms that it's set to the default (50% of server RAM). In that configuration (8G for TokuDB and 4G for InnoDB), the 13G RSS seems reasonable. –  tmcallaghan Dec 31 '13 at 11:34
add comment

I have written earlier posts about MySQL being a little swap happy

SUGGESTION

CAVEAT

TokuDB is a little new to me, so consult the TokuDB docs.

Give it a Try !!!

share|improve this answer
    
Thank you for your suggestions. Will look into it. –  pyro Dec 31 '13 at 6:45
    
The TokuDB plugin cannot be installed on a generic MySQL/Percona/MariaDB server, as it requires patches specific to the server. –  tmcallaghan Dec 31 '13 at 11:35
add comment

Check that kernel swappiness is 0.

cat /proc/sys/vm/swappiness echo 0 > /proc/sys/vm/swappiness

What is your workload? Perhaps you have lots of poor SQL queries with high concurrency that are killing your per session buffers (sort_buffer_size, join_buffer_size, etc) or there is a lot of tmp_table usage in memory in your workload. Reviewing the status variable deltas from start to swapping could tell you more about what MySQL is doing internally.

SHOW GLOBAL STATUS;

share|improve this answer
    
I've changed swappiness from 60 to 10. Unfortunately mariadb isn't the only service run on my server. I've also included output of show global status when mariadb almost reaches its limits. –  pyro Dec 31 '13 at 6:50
    
Well if it's not the only service running then you need to really investigate what is causing this. It could not be mysql to blame. Are you running a web server here too? Investigate memory usage using /proc/meminfo and the instrumentation provided by MySQL. Some math will help you provide proof of the memory use across your services. –  eroomydna Dec 31 '13 at 23:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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