Comments (15)
ZhangJiaQiao
commented on April 27, 2024
Machine memory usage:
MyRocks occupy the 99%.
from mysql-5.6.
ZhangJiaQiao
commented on April 27, 2024
Rocksdb LOG with many TTL compactions:
2023/01/27-21:00:44.355762 7f52a4d08700 [db/compaction/compaction_job.cc:2073] [default] [JOB 6649] Compacting 1@5 + 5@6 files to L6, score 0.96
2023/01/27-21:00:44.355780 7f52a4d08700 [db/compaction/compaction_job.cc:2077] [default] Compaction start summary: Base version 5652 Base level 5, inputs: [80907(34MB)], [85331(32MB) 85332(16MB) 73154(32MB) 73156(32MB) 73157(29MB)]
2023/01/27-21:00:44.355836 7f52a4d08700 EVENT_LOG_v1 {"time_micros": 1674824444355798, "job": 6649, "event": "compaction_started", "compaction_reason": "Ttl", "files_L5": [80907], "files_L6": [85331, 85332, 73154, 73156, 73157], "score": 0.96485, "input_data_size": 186679575}
2023/01/27-21:00:44.420016 7f52a02f5700 [file/delete_scheduler.cc:77] Deleted file ./.rocksdb/085325.sst immediately, rate_bytes_per_sec 0, total_trash_size 0 max_trash_db_ratio 0.250000
2023/01/27-21:00:44.420124 7f52a02f5700 EVENT_LOG_v1 {"time_micros": 1674824444420054, "job": 0, "event": "table_file_deletion", "file_number": 85325}
2023/01/27-21:00:44.426320 7f52a02f5700 [file/delete_scheduler.cc:77] Deleted file ./.rocksdb/085324.sst immediately, rate_bytes_per_sec 0, total_trash_size 0 max_trash_db_ratio 0.250000
2023/01/27-21:00:44.426331 7f52a02f5700 EVENT_LOG_v1 {"time_micros": 1674824444426328, "job": 0, "event": "table_file_deletion", "file_number": 85324}
2023/01/27-21:00:44.429978 7f52a02f5700 [file/delete_scheduler.cc:77] Deleted file ./.rocksdb/075451.sst immediately, rate_bytes_per_sec 0, total_trash_size 0 max_trash_db_ratio 0.250000
2023/01/27-21:00:44.429987 7f52a02f5700 EVENT_LOG_v1 {"time_micros": 1674824444429986, "job": 0, "event": "table_file_deletion", "file_number": 75451}
2023/01/27-21:00:44.433584 7f52a02f5700 [file/delete_scheduler.cc:77] Deleted file ./.rocksdb/073153.sst immediately, rate_bytes_per_sec 0, total_trash_size 0 max_trash_db_ratio 0.250000
2023/01/27-21:00:44.433594 7f52a02f5700 EVENT_LOG_v1 {"time_micros": 1674824444433592, "job": 0, "event": "table_file_deletion", "file_number": 73153}
2023/01/27-21:00:44.436888 7f52a02f5700 [file/delete_scheduler.cc:77] Deleted file ./.rocksdb/056252.sst immediately, rate_bytes_per_sec 0, total_trash_size 0 max_trash_db_ratio 0.250000
2023/01/27-21:00:44.436896 7f52a02f5700 EVENT_LOG_v1 {"time_micros": 1674824444436895, "job": 0, "event": "table_file_deletion", "file_number": 56252}
2023/01/27-21:00:44.440711 7f52a02f5700 [file/delete_scheduler.cc:77] Deleted file ./.rocksdb/056233.sst immediately, rate_bytes_per_sec 0, total_trash_size 0 max_trash_db_ratio 0.250000
2023/01/27-21:00:44.440720 7f52a02f5700 EVENT_LOG_v1 {"time_micros": 1674824444440719, "job": 0, "event": "table_file_deletion", "file_number": 56233}
2023/01/27-21:00:44.444319 7f52a02f5700 [file/delete_scheduler.cc:77] Deleted file ./.rocksdb/056228.sst immediately, rate_bytes_per_sec 0, total_trash_size 0 max_trash_db_ratio 0.250000
2023/01/27-21:00:44.444328 7f52a02f5700 EVENT_LOG_v1 {"time_micros": 1674824444444326, "job": 0, "event": "table_file_deletion", "file_number": 56228}
2023/01/27-21:00:44.448087 7f52a02f5700 [file/delete_scheduler.cc:77] Deleted file ./.rocksdb/056219.sst immediately, rate_bytes_per_sec 0, total_trash_size 0 max_trash_db_ratio 0.250000
2023/01/27-21:00:44.448096 7f52a02f5700 EVENT_LOG_v1 {"time_micros": 1674824444448094, "job": 0, "event": "table_file_deletion", "file_number": 56219}
2023/01/27-21:00:48.703947 7f52a4d08700 [db/compaction/compaction_job.cc:1704] [default] [JOB 6649] Generated table #85333: 3058947 keys, 34353630 bytes
2023/01/27-21:00:48.704368 7f52a4d08700 EVENT_LOG_v1 {"time_micros": 1674824448704028, "cf_name": "default", "job": 6649, "event": "table_file_creation", "file_number": 85333, "file_size": 34353630, "file_checksum": "", "file_checksum_func_name": "Unknown", "table_properties": {"data_size": 33555144, "index_size": 9499385, "index_partitions": 0, "top_level_index_size": 0, "index_key_is_user_key": 1, "index_value_is_delta_encoded": 1, "filter_size": 0, "raw_key_size": 572197275, "raw_average_key_size": 187, "raw_value_size": 724945138, "raw_average_value_size": 236, "num_data_blocks": 51023, "num_entries": 3058947, "num_filter_entries": 0, "num_deletions": 0, "num_merge_operands": 0, "num_range_deletions": 0, "format_version": 0, "fixed_key_len": 0, "filter_policy": "rocksdb.BuiltinBloomFilter", "column_family_name": "default", "column_family_id": 0, "comparator": "RocksDB_SE_v3.10", "merge_operator": "nullptr", "prefix_extractor_name": "nullptr", "property_collectors": "[Rdb_tbl_prop_coll_factory]", "compression": "ZSTD", "compression_options": "window_bits=-14; level=1; strategy=0; max_dict_bytes=0; zstd_max_train_bytes=0; enabled=0; max_dict_buffer_bytes=0; ", "creation_time": 1672222487, "oldest_key_time": 0, "file_creation_time": 1674824444, "slow_compression_estimated_data_size": 0, "fast_compression_estimated_data_size": 0, "db_id": "ca458472-1fd6-42b8-ba92-d10a5e094f7f", "db_session_id": "OR0OH4B0YRW71HLHAF8V", "__indexstats__": "[...1 records...]"}}
2023/01/27-21:00:53.097961 7f52a4d08700 [db/compaction/compaction_job.cc:1704] [default] [JOB 6649] Generated table #85334: 3045913 keys, 34347031 bytes
2023/01/27-21:00:53.098318 7f52a4d08700 EVENT_LOG_v1 {"time_micros": 1674824453098023, "cf_name": "default", "job": 6649, "event": "table_file_creation", "file_number": 85334, "file_size": 34347031, "file_checksum": "", "file_checksum_func_name": "Unknown", "table_properties": {"data_size": 33554928, "index_size": 9461939, "index_partitions": 0, "top_level_index_size": 0, "index_key_is_user_key": 1, "index_value_is_delta_encoded": 1, "filter_size": 0, "raw_key_size": 569899813, "raw_average_key_size": 187, "raw_value_size": 722074796, "raw_average_value_size": 237, "num_data_blocks": 50807, "num_entries": 3045913, "num_filter_entries": 0, "num_deletions": 0, "num_merge_operands": 0, "num_range_deletions": 0, "format_version": 0, "fixed_key_len": 0, "filter_policy": "rocksdb.BuiltinBloomFilter", "column_family_name": "default", "column_family_id": 0, "comparator": "RocksDB_SE_v3.10", "merge_operator": "nullptr", "prefix_extractor_name": "nullptr", "property_collectors": "[Rdb_tbl_prop_coll_factory]", "compression": "ZSTD", "compression_options": "window_bits=-14; level=1; strategy=0; max_dict_bytes=0; zstd_max_train_bytes=0; enabled=0; max_dict_buffer_bytes=0; ", "creation_time": 1672222487, "oldest_key_time": 0, "file_creation_time": 1674824448, "slow_compression_estimated_data_size": 0, "fast_compression_estimated_data_size": 0, "db_id": "ca458472-1fd6-42b8-ba92-d10a5e094f7f", "db_session_id": "OR0OH4B0YRW71HLHAF8V", "__indexstats__": "[...1 records...]"}}
2023/01/27-21:00:57.418708 7f52a4d08700 [db/compaction/compaction_job.cc:1704] [default] [JOB 6649] Generated table #85335: 3080983 keys, 34360057 bytes
2023/01/27-21:00:57.419162 7f52a4d08700 EVENT_LOG_v1 {"time_micros": 1674824457418756, "cf_name": "default", "job": 6649, "event": "table_file_creation", "file_number": 85335, "file_size": 34360057, "file_checksum": "", "file_checksum_func_name": "Unknown", "table_properties": {"data_size": 33555128, "index_size": 9565580, "index_partitions": 0, "top_level_index_size": 0, "index_key_is_user_key": 1, "index_value_is_delta_encoded": 1, "filter_size": 0, "raw_key_size": 576268903, "raw_average_key_size": 187, "raw_value_size": 730151704, "raw_average_value_size": 236, "num_data_blocks": 51390, "num_entries": 3080983, "num_filter_entries": 0, "num_deletions": 0, "num_merge_operands": 0, "num_range_deletions": 0, "format_version": 0, "fixed_key_len": 0, "filter_policy": "rocksdb.BuiltinBloomFilter", "column_family_name": "default", "column_family_id": 0, "comparator": "RocksDB_SE_v3.10", "merge_operator": "nullptr", "prefix_extractor_name": "nullptr", "property_collectors": "[Rdb_tbl_prop_coll_factory]", "compression": "ZSTD", "compression_options": "window_bits=-14; level=1; strategy=0; max_dict_bytes=0; zstd_max_train_bytes=0; enabled=0; max_dict_buffer_bytes=0; ", "creation_time": 1672222487, "oldest_key_time": 0, "file_creation_time": 1674824453, "slow_compression_estimated_data_size": 0, "fast_compression_estimated_data_size": 0, "db_id": "ca458472-1fd6-42b8-ba92-d10a5e094f7f", "db_session_id": "OR0OH4B0YRW71HLHAF8V", "__indexstats__": "[...1 records...]"}}
2023/01/27-21:01:01.797137 7f52a4d08700 [db/compaction/compaction_job.cc:1704] [default] [JOB 6649] Generated table #85336: 3064988 keys, 34352792 bytes
2023/01/27-21:01:01.797704 7f52a4d08700 EVENT_LOG_v1 {"time_micros": 1674824461797159, "cf_name": "default", "job": 6649, "event": "table_file_creation", "file_number": 85336, "file_size": 34352792, "file_checksum": "", "file_checksum_func_name": "Unknown", "table_properties": {"data_size": 33554601, "index_size": 9520427, "index_partitions": 0, "top_level_index_size": 0, "index_key_is_user_key": 1, "index_value_is_delta_encoded": 1, "filter_size": 0, "raw_key_size": 573523052, "raw_average_key_size": 187, "raw_value_size": 726600357, "raw_average_value_size": 237, "num_data_blocks": 51127, "num_entries": 3064988, "num_filter_entries": 0, "num_deletions": 0, "num_merge_operands": 0, "num_range_deletions": 0, "format_version": 0, "fixed_key_len": 0, "filter_policy": "rocksdb.BuiltinBloomFilter", "column_family_name": "default", "column_family_id": 0, "comparator": "RocksDB_SE_v3.10", "merge_operator": "nullptr", "prefix_extractor_name": "nullptr", "property_collectors": "[Rdb_tbl_prop_coll_factory]", "compression": "ZSTD", "compression_options": "window_bits=-14; level=1; strategy=0; max_dict_bytes=0; zstd_max_train_bytes=0; enabled=0; max_dict_buffer_bytes=0; ", "creation_time": 1672222487, "oldest_key_time": 0, "file_creation_time": 1674824457, "slow_compression_estimated_data_size": 0, "fast_compression_estimated_data_size": 0, "db_id": "ca458472-1fd6-42b8-ba92-d10a5e094f7f", "db_session_id": "OR0OH4B0YRW71HLHAF8V", "__indexstats__": "[...1 records...]"}}
2023/01/27-21:01:05.511930 7f52a4d08700 [db/compaction/compaction_job.cc:1704] [default] [JOB 6649] Generated table #85337: 2591168 keys, 28869861 bytes
2023/01/27-21:01:05.512432 7f52a4d08700 EVENT_LOG_v1 {"time_micros": 1674824465511980, "cf_name": "default", "job": 6649, "event": "table_file_creation", "file_number": 85337, "file_size": 28869861, "file_checksum": "", "file_checksum_func_name": "Unknown", "table_properties": {"data_size": 28194048, "index_size": 8042703, "index_partitions": 0, "top_level_index_size": 0, "index_key_is_user_key": 1, "index_value_is_delta_encoded": 1, "filter_size": 0, "raw_key_size": 484582472, "raw_average_key_size": 187, "raw_value_size": 614149660, "raw_average_value_size": 237, "num_data_blocks": 43217, "num_entries": 2591168, "num_filter_entries": 0, "num_deletions": 0, "num_merge_operands": 0, "num_range_deletions": 0, "format_version": 0, "fixed_key_len": 0, "filter_policy": "rocksdb.BuiltinBloomFilter", "column_family_name": "default", "column_family_id": 0, "comparator": "RocksDB_SE_v3.10", "merge_operator": "nullptr", "prefix_extractor_name": "nullptr", "property_collectors": "[Rdb_tbl_prop_coll_factory]", "compression": "ZSTD", "compression_options": "window_bits=-14; level=1; strategy=0; max_dict_bytes=0; zstd_max_train_bytes=0; enabled=0; max_dict_buffer_bytes=0; ", "creation_time": 1672222487, "oldest_key_time": 0, "file_creation_time": 1674824461, "slow_compression_estimated_data_size": 0, "fast_compression_estimated_data_size": 0, "db_id": "ca458472-1fd6-42b8-ba92-d10a5e094f7f", "db_session_id": "OR0OH4B0YRW71HLHAF8V", "__indexstats__": "[...1 records...]"}}
2023/01/27-21:01:05.560152 7f52a4d08700 [db/compaction/compaction_job.cc:1777] [default] [JOB 6649] Compacted 1@5 + 5@6 files to L6 => 166283371 bytes
2023/01/27-21:01:05.570863 7f52a4d08700 (Original Log Time 2023/01/27-21:01:05.570226) [db/compaction/compaction_job.cc:844] [default] compacted to: base level 3 level multiplier 10.00 max bytes base 536870912 files[3 0 0 15 9 301 5436] max score 0.96, MB/sec: 8.8 rd, 7.9 wr, level 6, files in(1, 5) out(5 +0 blob) MB in(34.1, 143.9 +0.0 blob) out(158.6 +0.0 blob), read-write-amplify(9.9) write-amplify(4.6) OK, records in: 14841999, records dropped: 0 output_compression: ZSTD
2023/01/27-21:01:05.570871 7f52a4d08700 (Original Log Time 2023/01/27-21:01:05.570289) EVENT_LOG_v1 {"time_micros": 1674824465570261, "job": 6649, "event": "compaction_finished", "compaction_time_micros": 21157908, "compaction_time_cpu_micros": 21081351, "output_level": 6, "num_output_files": 5, "total_output_size": 166283371, "num_input_records": 14841999, "num_output_records": 14841999, "num_subcompactions": 1, "output_compression": "ZSTD", "num_single_delete_mismatches": 0, "num_single_delete_fallthrough": 0, "lsm_state": [3, 0, 0, 15, 9, 301, 5436]}
2023/01/27-21:01:05.571448 7f52a5509700 [db/compaction/compaction_job.cc:2073] [default] [JOB 6650] Compacting 1@5 + 3@6 files to L6, score 0.96
2023/01/27-21:01:05.571484 7f52a5509700 [db/compaction/compaction_job.cc:2077] [default] Compaction start summary: Base version 5653 Base level 5, inputs: [80908(34MB)], [85337(27MB) 73212(32MB) 73213(32MB)]
2023/01/27-21:01:05.571527 7f52a5509700 EVENT_LOG_v1 {"time_micros": 1674824465571506, "job": 6650, "event": "compaction_started", "compaction_reason": "Ttl", "files_L5": [80908], "files_L6": [85337, 73212, 73213], "score": 0.96485, "input_data_size": 133382587}
2023/01/27-21:01:06.340723 7f52a02f5700 [file/delete_scheduler.cc:77] Deleted file ./.rocksdb/085332.sst immediately, rate_bytes_per_sec 0, total_trash_size 0 max_trash_db_ratio 0.250000
2023/01/27-21:01:06.340778 7f52a02f5700 EVENT_LOG_v1 {"time_micros": 1674824466340771, "job": 0, "event": "table_file_deletion", "file_number": 85332}
2023/01/27-21:01:06.347712 7f52a02f5700 [file/delete_scheduler.cc:77] Deleted file ./.rocksdb/085331.sst immediately, rate_bytes_per_sec 0, total_trash_size 0 max_trash_db_ratio 0.250000
2023/01/27-21:01:06.347726 7f52a02f5700 EVENT_LOG_v1 {"time_micros": 1674824466347723, "job": 0, "event": "table_file_deletion", "file_number": 85331}
2023/01/27-21:01:06.352229 7f52a02f5700 [file/delete_scheduler.cc:77] Deleted file ./.rocksdb/080907.sst immediately, rate_bytes_per_sec 0, total_trash_size 0 max_trash_db_ratio 0.250000
2023/01/27-21:01:06.352287 7f52a02f5700 EVENT_LOG_v1 {"time_micros": 1674824466352272, "job": 0, "event": "table_file_deletion", "file_number": 80907}
2023/01/27-21:01:06.357771 7f52a02f5700 [file/delete_scheduler.cc:77] Deleted file ./.rocksdb/073157.sst immediately, rate_bytes_per_sec 0, total_trash_size 0 max_trash_db_ratio 0.250000
2023/01/27-21:01:06.357802 7f52a02f5700 EVENT_LOG_v1 {"time_micros": 1674824466357796, "job": 0, "event": "table_file_deletion", "file_number": 73157}
2023/01/27-21:01:06.364796 7f52a02f5700 [file/delete_scheduler.cc:77] Deleted file ./.rocksdb/073156.sst immediately, rate_bytes_per_sec 0, total_trash_size 0 max_trash_db_ratio 0.250000
2023/01/27-21:01:06.364815 7f52a02f5700 EVENT_LOG_v1 {"time_micros": 1674824466364811, "job": 0, "event": "table_file_deletion", "file_number": 73156}
2023/01/27-21:01:06.371504 7f52a02f5700 [file/delete_scheduler.cc:77] Deleted file ./.rocksdb/073154.sst immediately, rate_bytes_per_sec 0, total_trash_size 0 max_trash_db_ratio 0.250000
2023/01/27-21:01:06.371519 7f52a02f5700 EVENT_LOG_v1 {"time_micros": 1674824466371516, "job": 0, "event": "table_file_deletion", "file_number": 73154}
2023/01/27-21:01:10.262987 7f52a5509700 [db/compaction/compaction_job.cc:1704] [default] [JOB 6650] Generated table #85338: 3098889 keys, 34363339 bytes
2023/01/27-21:01:10.263155 7f52a5509700 EVENT_LOG_v1 {"time_micros": 1674824470263031, "cf_name": "default", "job": 6650, "event": "table_file_creation", "file_number": 85338, "file_size": 34363339, "file_checksum": "", "file_checksum_func_name": "Unknown", "table_properties": {"data_size": 33555146, "index_size": 9617667, "index_partitions": 0, "top_level_index_size": 0, "index_key_is_user_key": 1, "index_value_is_delta_encoded": 1, "filter_size": 0, "raw_key_size": 579488346, "raw_average_key_size": 186, "raw_value_size": 734396033, "raw_average_value_size": 236, "num_data_blocks": 51685, "num_entries": 3098889, "num_filter_entries": 0, "num_deletions": 0, "num_merge_operands": 0, "num_range_deletions": 0, "format_version": 0, "fixed_key_len": 0, "filter_policy": "rocksdb.BuiltinBloomFilter", "column_family_name": "default", "column_family_id": 0, "comparator": "RocksDB_SE_v3.10", "merge_operator": "nullptr", "prefix_extractor_name": "nullptr", "property_collectors": "[Rdb_tbl_prop_coll_factory]", "compression": "ZSTD", "compression_options": "window_bits=-14; level=1; strategy=0; max_dict_bytes=0; zstd_max_train_bytes=0; enabled=0; max_dict_buffer_bytes=0; ", "creation_time": 1672222487, "oldest_key_time": 0, "file_creation_time": 1674824465, "slow_compression_estimated_data_size": 0, "fast_compression_estimated_data_size": 0, "db_id": "ca458472-1fd6-42b8-ba92-d10a5e094f7f", "db_session_id": "OR0OH4B0YRW71HLHAF8V", "__indexstats__": "[...1 records...]"}}
2023/01/27-21:01:14.781854 7f52a5509700 [db/compaction/compaction_job.cc:1704] [default] [JOB 6650] Generated table #85339: 3077306 keys, 34356484 bytes
2023/01/27-21:01:14.782224 7f52a5509700 EVENT_LOG_v1 {"time_micros": 1674824474782154, "cf_name": "default", "job": 6650, "event": "table_file_creation", "file_number": 85339, "file_size": 34356484, "file_checksum": "", "file_checksum_func_name": "Unknown", "table_properties": {"data_size": 33554914, "index_size": 9558104, "index_partitions": 0, "top_level_index_size": 0, "index_key_is_user_key": 1, "index_value_is_delta_encoded": 1, "filter_size": 0, "raw_key_size": 575715161, "raw_average_key_size": 187, "raw_value_size": 729538395, "raw_average_value_size": 237, "num_data_blocks": 51331, "num_entries": 3077306, "num_filter_entries": 0, "num_deletions": 0, "num_merge_operands": 0, "num_range_deletions": 0, "format_version": 0, "fixed_key_len": 0, "filter_policy": "rocksdb.BuiltinBloomFilter", "column_family_name": "default", "column_family_id": 0, "comparator": "RocksDB_SE_v3.10", "merge_operator": "nullptr", "prefix_extractor_name": "nullptr", "property_collectors": "[Rdb_tbl_prop_coll_factory]", "compression": "ZSTD", "compression_options": "window_bits=-14; level=1; strategy=0; max_dict_bytes=0; zstd_max_train_bytes=0; enabled=0; max_dict_buffer_bytes=0; ", "creation_time": 1672222487, "oldest_key_time": 0, "file_creation_time": 1674824470, "slow_compression_estimated_data_size": 0, "fast_compression_estimated_data_size": 0, "db_id": "ca458472-1fd6-42b8-ba92-d10a5e094f7f", "db_session_id": "OR0OH4B0YRW71HLHAF8V", "__indexstats__": "[...1 records...]"}}
2023/01/27-21:01:19.033671 7f52a5509700 [db/compaction/compaction_job.cc:1704] [default] [JOB 6650] Generated table #85340: 3091827 keys, 34362513 bytes
2023/01/27-21:01:19.033781 7f52a5509700 EVENT_LOG_v1 {"time_micros": 1674824479033715, "cf_name": "default", "job": 6650, "event": "table_file_creation", "file_number": 85340, "file_size": 34362513, "file_checksum": "", "file_checksum_func_name": "Unknown", "table_properties": {"data_size": 33554988, "index_size": 9608180, "index_partitions": 0, "top_level_index_size": 0, "index_key_is_user_key": 1, "index_value_is_delta_encoded": 1, "filter_size": 0, "raw_key_size": 578704656, "raw_average_key_size": 187, "raw_value_size": 732823232, "raw_average_value_size": 237, "num_data_blocks": 51583, "num_entries": 3091827, "num_filter_entries": 0, "num_deletions": 0, "num_merge_operands": 0, "num_range_deletions": 0, "format_version": 0, "fixed_key_len": 0, "filter_policy": "rocksdb.BuiltinBloomFilter", "column_family_name": "default", "column_family_id": 0, "comparator": "RocksDB_SE_v3.10", "merge_operator": "nullptr", "prefix_extractor_name": "nullptr", "property_collectors": "[Rdb_tbl_prop_coll_factory]", "compression": "ZSTD", "compression_options": "window_bits=-14; level=1; strategy=0; max_dict_bytes=0; zstd_max_train_bytes=0; enabled=0; max_dict_buffer_bytes=0; ", "creation_time": 1672222487, "oldest_key_time": 0, "file_creation_time": 1674824474, "slow_compression_estimated_data_size": 0, "fast_compression_estimated_data_size": 0, "db_id": "ca458472-1fd6-42b8-ba92-d10a5e094f7f", "db_session_id": "OR0OH4B0YRW71HLHAF8V", "__indexstats__": "[...1 records...]"}}
2023/01/27-21:01:20.350647 7f52a5509700 [db/compaction/compaction_job.cc:1704] [default] [JOB 6650] Generated table #85341: 888596 keys, 9906103 bytes
2023/01/27-21:01:20.351353 7f52a5509700 EVENT_LOG_v1 {"time_micros": 1674824480351271, "cf_name": "default", "job": 6650, "event": "table_file_creation", "file_number": 85341, "file_size": 9906103, "file_checksum": "", "file_checksum_func_name": "Unknown", "table_properties": {"data_size": 9674564, "index_size": 2754691, "index_partitions": 0, "top_level_index_size": 0, "index_key_is_user_key": 1, "index_value_is_delta_encoded": 1, "filter_size": 0, "raw_key_size": 166116998, "raw_average_key_size": 186, "raw_value_size": 210584262, "raw_average_value_size": 236, "num_data_blocks": 14820, "num_entries": 888596, "num_filter_entries": 0, "num_deletions": 0, "num_merge_operands": 0, "num_range_deletions": 0, "format_version": 0, "fixed_key_len": 0, "filter_policy": "rocksdb.BuiltinBloomFilter", "column_family_name": "default", "column_family_id": 0, "comparator": "RocksDB_SE_v3.10", "merge_operator": "nullptr", "prefix_extractor_name": "nullptr", "property_collectors": "[Rdb_tbl_prop_coll_factory]", "compression": "ZSTD", "compression_options": "window_bits=-14; level=1; strategy=0; max_dict_bytes=0; zstd_max_train_bytes=0; enabled=0; max_dict_buffer_bytes=0; ", "creation_time": 1672222487, "oldest_key_time": 0, "file_creation_time": 1674824479, "slow_compression_estimated_data_size": 0, "fast_compression_estimated_data_size": 0, "db_id": "ca458472-1fd6-42b8-ba92-d10a5e094f7f", "db_session_id": "OR0OH4B0YRW71HLHAF8V", "__indexstats__": "[...1 records...]"}}
2023/01/27-21:01:20.409751 7f52a5509700 [db/compaction/compaction_job.cc:1777] [default] [JOB 6650] Compacted 1@5 + 3@6 files to L6 => 112988439 bytes
2023/01/27-21:01:20.422570 7f52a5509700 (Original Log Time 2023/01/27-21:01:20.422120) [db/compaction/compaction_job.cc:844] [default] compacted to: base level 3 level multiplier 10.00 max bytes base 536870912 files[3 0 0 15 9 300 5437] max score 0.96, MB/sec: 9.0 rd, 7.6 wr, level 6, files in(1, 3) out(4 +0 blob) MB in(34.1, 93.1 +0.0 blob) out(107.8 +0.0 blob), read-write-amplify(6.9) write-amplify(3.2) OK, records in: 10156618, records dropped: 0 output_compression: ZSTD
2023/01/27-21:01:20.422582 7f52a5509700 (Original Log Time 2023/01/27-21:01:20.422164) EVENT_LOG_v1 {"time_micros": 1674824480422145, "job": 6650, "event": "compaction_finished", "compaction_time_micros": 14780412, "compaction_time_cpu_micros": 14355394, "output_level": 6, "num_output_files": 4, "total_output_size": 112988439, "num_input_records": 10156618, "num_output_records": 10156618, "num_subcompactions": 1, "output_compression": "ZSTD", "num_single_delete_mismatches": 0, "num_single_delete_fallthrough": 0, "lsm_state": [3, 0, 0, 15, 9, 300, 5437]}
from mysql-5.6.
yoshinorim
commented on April 27, 2024
You can disable TTL compactions by setting relevant RocksDB column family options (ttl and periodic_compaction_seconds) to 0. In MyRocks, you can set by rocksdb_default_cf_options='ttl=0;periodic_compaction_seconds=0' in my.cnf.
Also you may need to tune (reduce) compaction unit size. For example, set max_compaction_bytes=400m through rocksdb_default_cf_options.
from mysql-5.6.
ZhangJiaQiao
commented on April 27, 2024
RocksDB engine status:
mysql> show engine rocksdb status \G
*************************** 1. row ***************************
Type: STATISTICS
Name: rocksdb
Status: rocksdb.block.cache.miss COUNT : 463853910
rocksdb.block.cache.hit COUNT : 187009233139
rocksdb.block.cache.add COUNT : 65592909
rocksdb.block.cache.add.failures COUNT : 0
rocksdb.block.cache.index.miss COUNT : 41440
rocksdb.block.cache.index.hit COUNT : 41302872402
rocksdb.block.cache.index.add COUNT : 41440
rocksdb.block.cache.index.bytes.insert COUNT : 95646153221
rocksdb.block.cache.index.bytes.evict COUNT : 0
rocksdb.block.cache.filter.miss COUNT : 21845
rocksdb.block.cache.filter.hit COUNT : 101503710835
rocksdb.block.cache.filter.add COUNT : 21845
rocksdb.block.cache.filter.bytes.insert COUNT : 44512007753
rocksdb.block.cache.filter.bytes.evict COUNT : 0
rocksdb.block.cache.data.miss COUNT : 463790625
rocksdb.block.cache.data.hit COUNT : 44202649902
rocksdb.block.cache.data.add COUNT : 65529624
rocksdb.block.cache.data.bytes.insert COUNT : 1064313575560
rocksdb.block.cache.bytes.read COUNT : 302069664055343549
rocksdb.block.cache.bytes.write COUNT : 1204471736534
rocksdb.bloom.filter.useful COUNT : 178613768919
rocksdb.bloom.filter.full.positive COUNT : 9340352131
rocksdb.bloom.filter.full.true.positive COUNT : 7738555670
rocksdb.bloom.filter.micros COUNT : 0
rocksdb.persistent.cache.hit COUNT : 0
rocksdb.persistent.cache.miss COUNT : 0
rocksdb.sim.block.cache.hit COUNT : 0
rocksdb.sim.block.cache.miss COUNT : 0
rocksdb.memtable.hit COUNT : 8817500013
rocksdb.memtable.miss COUNT : 41416553625
rocksdb.l0.hit COUNT : 1006277847
rocksdb.l1.hit COUNT : 0
rocksdb.l2andup.hit COUNT : 36473223378
rocksdb.compaction.key.drop.new COUNT : 776699369
rocksdb.compaction.key.drop.obsolete COUNT : 7725134
rocksdb.compaction.key.drop.range_del COUNT : 0
rocksdb.compaction.key.drop.user COUNT : 4351880
rocksdb.compaction.range_del.drop.obsolete COUNT : 0
rocksdb.compaction.optimized.del.drop.obsolete COUNT : 3902806
rocksdb.compaction.cancelled COUNT : 0
rocksdb.number.keys.written COUNT : 6675933618
rocksdb.number.keys.read COUNT : 50234053638
rocksdb.number.keys.updated COUNT : 0
rocksdb.bytes.written COUNT : 2624473266454
rocksdb.bytes.read COUNT : 2660372573363
rocksdb.number.db.seek COUNT : 6457981
rocksdb.number.db.next COUNT : 84486229986
rocksdb.number.db.prev COUNT : 1254791765
rocksdb.number.db.seek.found COUNT : 3939090
rocksdb.number.db.next.found COUNT : 84483327510
rocksdb.number.db.prev.found COUNT : 1254789475
rocksdb.db.iter.bytes.read COUNT : 16735204920744
rocksdb.no.file.closes COUNT : 0
rocksdb.no.file.opens COUNT : 36383
rocksdb.no.file.errors COUNT : 0
rocksdb.l0.slowdown.micros COUNT : 0
rocksdb.memtable.compaction.micros COUNT : 0
rocksdb.l0.num.files.stall.micros COUNT : 0
rocksdb.stall.micros COUNT : 0
rocksdb.db.mutex.wait.micros COUNT : 0
rocksdb.rate.limit.delay.millis COUNT : 0
rocksdb.num.iterators COUNT : 0
rocksdb.number.multiget.get COUNT : 0
rocksdb.number.multiget.keys.read COUNT : 0
rocksdb.number.multiget.bytes.read COUNT : 0
rocksdb.number.deletes.filtered COUNT : 0
rocksdb.number.merge.failures COUNT : 0
rocksdb.bloom.filter.prefix.checked COUNT : 0
rocksdb.bloom.filter.prefix.useful COUNT : 0
rocksdb.number.reseeks.iteration COUNT : 1429386304
rocksdb.getupdatessince.calls COUNT : 0
rocksdb.block.cachecompressed.miss COUNT : 0
rocksdb.block.cachecompressed.hit COUNT : 0
rocksdb.block.cachecompressed.add COUNT : 0
rocksdb.block.cachecompressed.add.failures COUNT : 0
rocksdb.wal.synced COUNT : 86236805
rocksdb.wal.bytes COUNT : 1314904133959
rocksdb.write.self COUNT : 176363500
rocksdb.write.other COUNT : 26933
rocksdb.write.timeout COUNT : 0
rocksdb.write.wal COUNT : 352780866
rocksdb.compact.read.bytes COUNT : 865493516581
rocksdb.compact.write.bytes COUNT : 874948111479
rocksdb.flush.write.bytes COUNT : 48679110594
rocksdb.compact.read.marked.bytes COUNT : 0
rocksdb.compact.read.periodic.bytes COUNT : 72580346774
rocksdb.compact.read.ttl.bytes COUNT : 190309781377
rocksdb.compact.write.marked.bytes COUNT : 0
rocksdb.compact.write.periodic.bytes COUNT : 71801190436
rocksdb.compact.write.ttl.bytes COUNT : 183749552154
rocksdb.number.direct.load.table.properties COUNT : 0
rocksdb.number.superversion_acquires COUNT : 342755
rocksdb.number.superversion_releases COUNT : 5380
rocksdb.number.superversion_cleanups COUNT : 28
rocksdb.number.block.compressed COUNT : 425917019
rocksdb.number.block.decompressed COUNT : 463694257
rocksdb.number.block.not_compressed COUNT : 137618
rocksdb.merge.operation.time.nanos COUNT : 14117
rocksdb.filter.operation.time.nanos COUNT : 0
rocksdb.row.cache.hit COUNT : 0
rocksdb.row.cache.miss COUNT : 0
rocksdb.read.amp.estimate.useful.bytes COUNT : 0
rocksdb.read.amp.total.read.bytes COUNT : 0
rocksdb.number.rate_limiter.drains COUNT : 0
rocksdb.number.iter.skip COUNT : 81729823819
rocksdb.blobdb.num.put COUNT : 0
rocksdb.blobdb.num.write COUNT : 0
rocksdb.blobdb.num.get COUNT : 0
rocksdb.blobdb.num.multiget COUNT : 0
rocksdb.blobdb.num.seek COUNT : 0
rocksdb.blobdb.num.next COUNT : 0
rocksdb.blobdb.num.prev COUNT : 0
rocksdb.blobdb.num.keys.written COUNT : 0
rocksdb.blobdb.num.keys.read COUNT : 0
rocksdb.blobdb.bytes.written COUNT : 0
rocksdb.blobdb.bytes.read COUNT : 0
rocksdb.blobdb.write.inlined COUNT : 0
rocksdb.blobdb.write.inlined.ttl COUNT : 0
rocksdb.blobdb.write.blob COUNT : 0
rocksdb.blobdb.write.blob.ttl COUNT : 0
rocksdb.blobdb.blob.file.bytes.written COUNT : 0
rocksdb.blobdb.blob.file.bytes.read COUNT : 0
rocksdb.blobdb.blob.file.synced COUNT : 0
rocksdb.blobdb.blob.index.expired.count COUNT : 0
rocksdb.blobdb.blob.index.expired.size COUNT : 0
rocksdb.blobdb.blob.index.evicted.count COUNT : 0
rocksdb.blobdb.blob.index.evicted.size COUNT : 0
rocksdb.blobdb.gc.num.files COUNT : 0
rocksdb.blobdb.gc.num.new.files COUNT : 0
rocksdb.blobdb.gc.failures COUNT : 0
rocksdb.blobdb.gc.num.keys.overwritten COUNT : 0
rocksdb.blobdb.gc.num.keys.expired COUNT : 0
rocksdb.blobdb.gc.num.keys.relocated COUNT : 0
rocksdb.blobdb.gc.bytes.overwritten COUNT : 0
rocksdb.blobdb.gc.bytes.expired COUNT : 0
rocksdb.blobdb.gc.bytes.relocated COUNT : 0
rocksdb.blobdb.fifo.num.files.evicted COUNT : 0
rocksdb.blobdb.fifo.num.keys.evicted COUNT : 0
rocksdb.blobdb.fifo.bytes.evicted COUNT : 0
rocksdb.txn.overhead.mutex.prepare COUNT : 0
rocksdb.txn.overhead.mutex.old.commit.map COUNT : 0
rocksdb.txn.overhead.duplicate.key COUNT : 0
rocksdb.txn.overhead.mutex.snapshot COUNT : 0
rocksdb.txn.get.tryagain COUNT : 0
rocksdb.number.multiget.keys.found COUNT : 0
rocksdb.num.iterator.created COUNT : 2450239
rocksdb.num.iterator.deleted COUNT : 2450239
rocksdb.block.cache.compression.dict.miss COUNT : 0
rocksdb.block.cache.compression.dict.hit COUNT : 0
rocksdb.block.cache.compression.dict.add COUNT : 0
rocksdb.block.cache.compression.dict.bytes.insert COUNT : 0
rocksdb.block.cache.compression.dict.bytes.evict COUNT : 0
rocksdb.block.cache.add.redundant COUNT : 1033179
rocksdb.block.cache.index.add.redundant COUNT : 112
rocksdb.block.cache.filter.add.redundant COUNT : 0
rocksdb.block.cache.data.add.redundant COUNT : 1033067
rocksdb.block.cache.compression.dict.add.redundant COUNT : 0
rocksdb.files.marked.trash COUNT : 0
rocksdb.files.deleted.immediately COUNT : 33902
rocksdb.error.handler.bg.errro.count COUNT : 0
rocksdb.error.handler.bg.io.errro.count COUNT : 0
rocksdb.error.handler.bg.retryable.io.errro.count COUNT : 0
rocksdb.error.handler.autoresume.count COUNT : 0
rocksdb.error.handler.autoresume.retry.total.count COUNT : 0
rocksdb.error.handler.autoresume.success.count COUNT : 0
rocksdb.db.get.micros P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.db.write.micros P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.compaction.times.micros P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.compaction.times.cpu_micros P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.subcompaction.setup.times.micros P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.table.sync.micros P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.compaction.outfile.sync.micros P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.wal.file.sync.micros P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.manifest.file.sync.micros P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.table.open.io.micros P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.db.multiget.micros P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.read.block.compaction.micros P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.read.block.get.micros P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.write.raw.block.micros P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.l0.slowdown.count P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.memtable.compaction.count P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.num.files.stall.count P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.hard.rate.limit.delay.count P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.soft.rate.limit.delay.count P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.numfiles.in.singlecompaction P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.db.seek.micros P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.db.write.stall P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.sst.read.micros P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.num.subcompactions.scheduled P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.bytes.per.read P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.bytes.per.write P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.bytes.per.multiget P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.bytes.compressed P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.bytes.decompressed P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.compression.times.nanos P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.decompression.times.nanos P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.read.num.merge_operands P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.blobdb.key.size P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.blobdb.value.size P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.blobdb.write.micros P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.blobdb.get.micros P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.blobdb.multiget.micros P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.blobdb.seek.micros P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.blobdb.next.micros P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.blobdb.prev.micros P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.blobdb.blob.file.write.micros P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.blobdb.blob.file.read.micros P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.blobdb.blob.file.sync.micros P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.blobdb.gc.micros P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.blobdb.compression.micros P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.blobdb.decompression.micros P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.db.flush.micros P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.sst.batch.size P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.num.index.and.filter.blocks.read.per.level P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.num.data.blocks.read.per.level P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.num.sst.read.per.level P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.error.handler.autoresume.retry.count P50 : 0.000000 P95 : 0.000000 P99 : 0.000000 P100 : 0.000000 COUNT : 0 SUM : 0
rocksdb.commit_latency statistics Percentiles :=> 50 : 0.76 95 : 101.27 99 : 184.44 100 : 1209109.0rocksdb.is_write_stopped COUNT : 0
rocksdb.actual_delayed_write_rate COUNT : 0
*************************** 2. row ***************************
Type: DBSTATS
Name: rocksdb
Status:
** DB Stats **
Uptime(secs): 2921100.9 total, 381.5 interval
Cumulative writes: 176M writes, 6675M keys, 176M commit groups, 1.0 writes per commit group, ingest: 2444.23 GB, 0.86 MB/s
Cumulative WAL: 176M writes, 0 syncs, 176390433.00 writes per sync, written: 1224.60 GB, 0.43 MB/s
Cumulative stall: 00:00:0.000 H:M:S, 0.0 percent
Interval writes: 1531 writes, 859K keys, 1531 commit groups, 1.0 writes per commit group, ingest: 321.89 MB, 0.84 MB/s
Interval WAL: 1531 writes, 0 syncs, 1531.00 writes per sync, written: 0.16 MB, 0.42 MB/s
Interval stall: 00:00:0.000 H:M:S, 0.0 percent
*************************** 3. row ***************************
Type: CF_COMPACTION
Name: __system__
Status:
** Compaction Stats [__system__] **
Level Files Size Score Read(GB) Rn(GB) Rnp1(GB) Write(GB) Wnew(GB) Moved(GB) W-Amp Rd(MB/s) Wr(MB/s) Comp(sec) CompMergeCPU(sec) Comp(cnt) Avg(sec) KeyIn KeyDrop Rblob(GB) Wblob(GB)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
L0 17/0 231.19 KB 0.8 0.0 0.0 0.0 0.0 0.0 0.0 1.0 0.0 3.3 1.24 1.08 297 0.004 0 0 0.0 0.0
L6 1/0 22.99 KB 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.1 23.5 1.8 0.18 0.15 15 0.012 87K 67K 0.0 0.0
Sum 18/0 254.17 KB 0.0 0.0 0.0 0.0 0.0 0.0 0.0 1.1 3.0 3.2 1.42 1.23 312 0.005 87K 67K 0.0 0.0
Int 0/0 0.00 KB 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.00 0.00 0 0.000 0 0 0.0 0.0
** Compaction Stats [__system__] **
Priority Files Size Score Read(GB) Rn(GB) Rnp1(GB) Write(GB) Wnew(GB) Moved(GB) W-Amp Rd(MB/s) Wr(MB/s) Comp(sec) CompMergeCPU(sec) Comp(cnt) Avg(sec) KeyIn KeyDrop Rblob(GB) Wblob(GB)
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Low 0/0 0.00 KB 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 23.5 1.8 0.18 0.15 15 0.012 87K 67K 0.0 0.0
High 0/0 0.00 KB 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 3.3 1.24 1.08 297 0.004 0 0 0.0 0.0
Blob file count: 0, total size: 0.0 GB
Uptime(secs): 2921100.9 total, 381.5 interval
Flush(GB): cumulative 0.004, interval 0.000
AddFile(GB): cumulative 0.000, interval 0.000
AddFile(Total Files): cumulative 0, interval 0
AddFile(L0 Files): cumulative 0, interval 0
AddFile(Keys): cumulative 0, interval 0
Cumulative compaction: 0.00 GB write, 0.00 MB/s write, 0.00 GB read, 0.00 MB/s read, 1.4 seconds
Interval compaction: 0.00 GB write, 0.00 MB/s write, 0.00 GB read, 0.00 MB/s read, 0.0 seconds
Stalls(count): 0 level0_slowdown, 0 level0_slowdown_with_compaction, 0 level0_numfiles, 0 level0_numfiles_with_compaction, 0 stop for pending_compaction_bytes, 0 slowdown for pending_compaction_bytes, 0 memtable_compaction, 0 memtable_slowdown, interval 0 total count
Block cache LRUCache@0x14d0a888 capacity: 78.00 GB collections: 7 last_copies: 0 last_secs: 1.18037 secs_since: 0
Block cache entry stats(count,size,portion): DataBlock(3178838,48.21 GB,61.8103%) FilterBlock(2527,4.22 GB,5.41535%) IndexBlock(9741,25.08 GB,32.1521%) Misc(1,0.00 KB,0%)
** File Read Latency Histogram By Level [__system__] **
** Level 0 read latency histogram (micros):
Count: 2260 Average: 31.3774 StdDev: 122.43
Min: 0 Median: 1.9535 Max: 4001
Percentiles: P50: 1.95 P75: 6.20 P99: 374.15 P99.9: 1119.40 P99.99: 4001.00
------------------------------------------------------
[ 0, 1 ] 453 20.044% 20.044% ####
( 1, 2 ] 710 31.416% 51.460% ######
( 2, 3 ] 174 7.699% 59.159% ##
( 3, 4 ] 175 7.743% 66.903% ##
( 4, 6 ] 167 7.389% 74.292% #
( 6, 10 ] 325 14.381% 88.673% ###
( 10, 15 ] 21 0.929% 89.602%
( 15, 22 ] 17 0.752% 90.354%
( 22, 34 ] 5 0.221% 90.575%
( 110, 170 ] 7 0.310% 90.885%
( 170, 250 ] 107 4.735% 95.619% #
( 250, 380 ] 80 3.540% 99.159% #
( 380, 580 ] 13 0.575% 99.735%
( 580, 870 ] 2 0.088% 99.823%
( 870, 1300 ] 3 0.133% 99.956%
( 2900, 4400 ] 1 0.044% 100.000%
** Level 6 read latency histogram (micros):
Count: 161 Average: 67.5466 StdDev: 144.84
Min: 0 Median: 3.5833 Max: 664
Percentiles: P50: 3.58 P75: 11.88 P99: 664.00 P99.9: 664.00 P99.99: 664.00
------------------------------------------------------
[ 0, 1 ] 25 15.528% 15.528% ###
( 1, 2 ] 29 18.012% 33.540% ####
( 2, 3 ] 16 9.938% 43.478% ##
( 3, 4 ] 18 11.180% 54.658% ##
( 4, 6 ] 18 11.180% 65.839% ##
( 6, 10 ] 14 8.696% 74.534% ##
( 10, 15 ] 2 1.242% 75.776%
( 22, 34 ] 5 3.106% 78.882% #
( 34, 51 ] 4 2.484% 81.366%
( 110, 170 ] 3 1.863% 83.230%
( 170, 250 ] 9 5.590% 88.820% #
( 250, 380 ] 7 4.348% 93.168% #
( 380, 580 ] 8 4.969% 98.137% #
( 580, 870 ] 3 1.863% 100.000%
*************************** 4. row ***************************
Type: CF_COMPACTION
Name: default
Status:
** Compaction Stats [default] **
Level Files Size Score Read(GB) Rn(GB) Rnp1(GB) Write(GB) Wnew(GB) Moved(GB) W-Amp Rd(MB/s) Wr(MB/s) Comp(sec) CompMergeCPU(sec) Comp(cnt) Avg(sec) KeyIn KeyDrop Rblob(GB) Wblob(GB)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
L0 1/0 8.29 MB 0.1 0.0 0.0 0.0 45.3 45.3 0.0 1.0 0.0 14.5 3193.64 3101.02 2790 1.145 0 0 0.0 0.0
L3 15/0 470.78 MB 0.9 450.1 45.9 404.2 425.6 21.4 0.0 9.3 19.3 18.2 23920.08 23196.22 931 25.693 25G 769M 0.0 0.0
L4 53/0 1.58 GB 1.0 48.5 19.3 29.2 48.2 19.0 2.0 2.5 18.9 18.7 2635.25 2580.08 648 4.067 2905M 5832K 0.0 0.0
L5 66/0 906.65 MB 0.1 81.8 20.2 61.7 81.7 20.0 0.8 4.0 18.4 18.4 4547.95 4432.79 678 6.708 5068M 3036K 0.0 0.0
L6 5409/0 159.95 GB 0.0 280.3 34.8 245.4 259.3 13.9 0.0 7.4 7.7 7.1 37436.99 36989.14 3608 10.376 40G 5825K 0.0 0.0
Sum 5544/0 162.89 GB 0.0 860.8 120.3 740.5 860.2 119.7 2.8 19.0 12.3 12.3 71733.91 70299.25 8655 8.288 74G 784M 0.0 0.0
Int 0/0 0.00 KB 0.0 0.0 0.0 0.0 0.0 0.0 0.0 1.0 0.0 9.8 0.84 0.83 1 0.843 0 0 0.0 0.0
** Compaction Stats [default] **
Priority Files Size Score Read(GB) Rn(GB) Rnp1(GB) Write(GB) Wnew(GB) Moved(GB) W-Amp Rd(MB/s) Wr(MB/s) Comp(sec) CompMergeCPU(sec) Comp(cnt) Avg(sec) KeyIn KeyDrop Rblob(GB) Wblob(GB)
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Low 0/0 0.00 KB 0.0 860.8 120.3 740.5 814.9 74.4 0.0 0.0 12.9 12.2 68540.27 67198.23 5865 11.686 74G 784M 0.0 0.0
High 0/0 0.00 KB 0.0 0.0 0.0 0.0 45.3 45.3 0.0 0.0 0.0 14.5 3193.64 3101.02 2790 1.145 0 0 0.0 0.0
Blob file count: 0, total size: 0.0 GB
Uptime(secs): 2921102.1 total, 381.5 interval
Flush(GB): cumulative 45.331, interval 0.008
AddFile(GB): cumulative 0.000, interval 0.000
AddFile(Total Files): cumulative 0, interval 0
AddFile(L0 Files): cumulative 0, interval 0
AddFile(Keys): cumulative 0, interval 0
Cumulative compaction: 860.19 GB write, 0.30 MB/s write, 860.77 GB read, 0.30 MB/s read, 71733.9 seconds
Interval compaction: 0.01 GB write, 0.02 MB/s write, 0.00 GB read, 0.00 MB/s read, 0.8 seconds
Stalls(count): 0 level0_slowdown, 0 level0_slowdown_with_compaction, 0 level0_numfiles, 0 level0_numfiles_with_compaction, 0 stop for pending_compaction_bytes, 0 slowdown for pending_compaction_bytes, 0 memtable_compaction, 0 memtable_slowdown, interval 0 total count
Block cache LRUCache@0x14d0a888 capacity: 78.00 GB collections: 8 last_copies: 0 last_secs: 1.1833 secs_since: 0
Block cache entry stats(count,size,portion): DataBlock(3178838,48.21 GB,61.8103%) FilterBlock(2527,4.22 GB,5.41535%) IndexBlock(9741,25.08 GB,32.1521%) Misc(1,0.00 KB,0%)
** File Read Latency Histogram By Level [default] **
** Level 0 read latency histogram (micros):
Count: 15297048 Average: 4.2511 StdDev: 116.62
Min: 0 Median: 1.7230 Max: 91338
Percentiles: P50: 1.72 P75: 2.69 P99: 19.15 P99.9: 232.50 P99.99: 1329.43
------------------------------------------------------
[ 0, 1 ] 4506235 29.458% 29.458% ######
( 1, 2 ] 4346335 28.413% 57.871% ######
( 2, 3 ] 3807769 24.892% 82.763% #####
( 3, 4 ] 1409822 9.216% 91.980% ##
( 4, 6 ] 613348 4.010% 95.989% #
( 6, 10 ] 337852 2.209% 98.198%
( 10, 15 ] 63650 0.416% 98.614%
( 15, 22 ] 99684 0.652% 99.266%
( 22, 34 ] 21085 0.138% 99.403%
( 34, 51 ] 8282 0.054% 99.458%
( 51, 76 ] 8111 0.053% 99.511%
( 76, 110 ] 24673 0.161% 99.672%
( 110, 170 ] 15590 0.102% 99.774%
( 170, 250 ] 24723 0.162% 99.935%
( 250, 380 ] 4644 0.030% 99.966%
( 380, 580 ] 1510 0.010% 99.976%
( 580, 870 ] 1191 0.008% 99.983%
( 870, 1300 ] 988 0.006% 99.990%
( 1300, 1900 ] 536 0.004% 99.993%
( 1900, 2900 ] 180 0.001% 99.995%
( 2900, 4400 ] 96 0.001% 99.995%
( 4400, 6600 ] 143 0.001% 99.996%
( 6600, 9900 ] 225 0.001% 99.998%
( 9900, 14000 ] 197 0.001% 99.999%
( 14000, 22000 ] 77 0.001% 99.999%
( 22000, 33000 ] 80 0.001% 100.000%
( 33000, 50000 ] 39 0.000% 100.000%
( 50000, 75000 ] 2 0.000% 100.000%
( 75000, 110000 ] 1 0.000% 100.000%
** Level 3 read latency histogram (micros):
Count: 144735637 Average: 6.5296 StdDev: 15.10
Min: 0 Median: 0.7311 Max: 804888
Percentiles: P50: 0.73 P75: 1.26 P99: 86.21 P99.9: 348.60 P99.99: 10107.48
------------------------------------------------------
[ 0, 1 ] 98985488 68.391% 68.391% ##############
( 1, 2 ] 37136069 25.658% 94.048% #####
( 2, 3 ] 3794824 2.622% 96.670% #
( 3, 4 ] 756079 0.522% 97.193%
( 4, 6 ] 356427 0.246% 97.439%
( 6, 10 ] 307707 0.213% 97.652%
( 10, 15 ] 208080 0.144% 97.795%
( 15, 22 ] 552529 0.382% 98.177%
( 22, 34 ] 643285 0.444% 98.622%
( 34, 51 ] 237845 0.164% 98.786%
( 51, 76 ] 129091 0.089% 98.875%
( 76, 110 ] 602170 0.416% 99.291%
( 110, 170 ] 457621 0.316% 99.607%
( 170, 250 ] 376134 0.260% 99.867%
( 250, 380 ] 62693 0.043% 99.910%
( 380, 580 ] 25486 0.018% 99.928%
( 580, 870 ] 27612 0.019% 99.947%
( 870, 1300 ] 19772 0.014% 99.961%
( 1300, 1900 ] 11210 0.008% 99.969%
( 1900, 2900 ] 6374 0.004% 99.973%
( 2900, 4400 ] 5209 0.004% 99.977%
( 4400, 6600 ] 7515 0.005% 99.982%
( 6600, 9900 ] 11551 0.008% 99.990%
( 9900, 14000 ] 7755 0.005% 99.995%
( 14000, 22000 ] 3860 0.003% 99.998%
( 22000, 33000 ] 2273 0.002% 99.999%
( 33000, 50000 ] 960 0.001% 100.000%
( 50000, 75000 ] 18 0.000% 100.000%
( 75000, 110000 ] 13 0.000% 100.000%
( 110000, 170000 ] 10 0.000% 100.000%
( 170000, 250000 ] 11 0.000% 100.000%
( 250000, 380000 ] 3 0.000% 100.000%
( 570000, 860000 ] 2 0.000% 100.000%
** Level 4 read latency histogram (micros):
Count: 17078992 Average: 11.0592 StdDev: 206.14
Min: 0 Median: 0.7877 Max: 284284
Percentiles: P50: 0.79 P75: 1.42 P99: 209.14 P99.9: 459.42 P99.99: 12711.09
------------------------------------------------------
[ 0, 1 ] 10841566 63.479% 63.479% #############
( 1, 2 ] 4736486 27.733% 91.212% ######
( 2, 3 ] 542863 3.179% 94.390% #
( 3, 4 ] 107544 0.630% 95.020%
( 4, 6 ] 51159 0.300% 95.320%
( 6, 10 ] 32794 0.192% 95.512%
( 10, 15 ] 21320 0.125% 95.636%
( 15, 22 ] 62382 0.365% 96.002%
( 22, 34 ] 115703 0.677% 96.679%
( 34, 51 ] 66314 0.388% 97.067%
( 51, 76 ] 55817 0.327% 97.394%
( 76, 110 ] 84146 0.493% 97.887%
( 110, 170 ] 71746 0.420% 98.307%
( 170, 250 ] 241897 1.416% 99.723%
( 250, 380 ] 28175 0.165% 99.888%
( 380, 580 ] 5039 0.030% 99.918%
( 580, 870 ] 3857 0.023% 99.940%
( 870, 1300 ] 2847 0.017% 99.957%
( 1300, 1900 ] 1771 0.010% 99.967%
( 1900, 2900 ] 938 0.005% 99.973%
( 2900, 4400 ] 555 0.003% 99.976%
( 4400, 6600 ] 701 0.004% 99.980%
( 6600, 9900 ] 1082 0.006% 99.987%
( 9900, 14000 ] 849 0.005% 99.992%
( 14000, 22000 ] 400 0.002% 99.994%
( 22000, 33000 ] 605 0.004% 99.997%
( 33000, 50000 ] 416 0.002% 100.000%
( 50000, 75000 ] 24 0.000% 100.000%
( 75000, 110000 ] 4 0.000% 100.000%
( 110000, 170000 ] 3 0.000% 100.000%
( 170000, 250000 ] 3 0.000% 100.000%
( 250000, 380000 ] 1 0.000% 100.000%
** Level 5 read latency histogram (micros):
Count: 33397857 Average: 11.8939 StdDev: 54.41
Min: 0 Median: 0.8177 Max: 351715
Percentiles: P50: 0.82 P75: 1.47 P99: 206.24 P99.9: 533.59 P99.99: 12926.35
------------------------------------------------------
[ 0, 1 ] 20421860 61.147% 61.147% ############
( 1, 2 ] 9788257 29.308% 90.455% ######
( 2, 3 ] 1063468 3.184% 93.639% #
( 3, 4 ] 151979 0.455% 94.095%
( 4, 6 ] 71309 0.214% 94.308%
( 6, 10 ] 53440 0.160% 94.468%
( 10, 15 ] 41074 0.123% 94.591%
( 15, 22 ] 130881 0.392% 94.983%
( 22, 34 ] 314878 0.943% 95.926%
( 34, 51 ] 219623 0.658% 96.583%
( 51, 76 ] 176859 0.530% 97.113%
( 76, 110 ] 227498 0.681% 97.794%
( 110, 170 ] 205791 0.616% 98.410%
( 170, 250 ] 434822 1.302% 99.712%
( 250, 380 ] 54874 0.164% 99.877%
( 380, 580 ] 10217 0.031% 99.907%
( 580, 870 ] 8378 0.025% 99.932%
( 870, 1300 ] 6542 0.020% 99.952%
( 1300, 1900 ] 3823 0.011% 99.963%
( 1900, 2900 ] 2440 0.007% 99.971%
( 2900, 4400 ] 1425 0.004% 99.975%
( 4400, 6600 ] 1578 0.005% 99.980%
( 6600, 9900 ] 2146 0.006% 99.986%
( 9900, 14000 ] 1836 0.005% 99.991%
( 14000, 22000 ] 982 0.003% 99.994%
( 22000, 33000 ] 1136 0.003% 99.998%
( 33000, 50000 ] 778 0.002% 100.000%
( 50000, 75000 ] 33 0.000% 100.000%
( 75000, 110000 ] 13 0.000% 100.000%
( 110000, 170000 ] 10 0.000% 100.000%
( 170000, 250000 ] 3 0.000% 100.000%
( 250000, 380000 ] 7 0.000% 100.000%
** Level 6 read latency histogram (micros):
Count: 253446441 Average: 6.1001 StdDev: 11.77
Min: 0 Median: 0.6294 Max: 281450
Percentiles: P50: 0.63 P75: 0.94 P99: 136.49 P99.9: 299.11 P99.99: 1662.02
------------------------------------------------------
[ 0, 1 ] 201326468 79.436% 79.436% ################
( 1, 2 ] 38565913 15.217% 94.652% ###
( 2, 3 ] 1684767 0.665% 95.317%
( 3, 4 ] 259958 0.103% 95.419%
( 4, 6 ] 258863 0.102% 95.522%
( 6, 10 ] 335274 0.132% 95.654%
( 10, 15 ] 323456 0.128% 95.781%
( 15, 22 ] 812474 0.321% 96.102%
( 22, 34 ] 926820 0.366% 96.468%
( 34, 51 ] 1661377 0.656% 97.123%
( 51, 76 ] 1733199 0.684% 97.807%
( 76, 110 ] 2507845 0.989% 98.797%
( 110, 170 ] 1167644 0.461% 99.257%
( 170, 250 ] 1554681 0.613% 99.871%
( 250, 380 ] 196560 0.078% 99.948%
( 380, 580 ] 53213 0.021% 99.969%
( 580, 870 ] 31196 0.012% 99.982%
( 870, 1300 ] 17090 0.007% 99.988%
( 1300, 1900 ] 7124 0.003% 99.991%
( 1900, 2900 ] 5347 0.002% 99.993%
( 2900, 4400 ] 4871 0.002% 99.995%
( 4400, 6600 ] 4319 0.002% 99.997%
( 6600, 9900 ] 4083 0.002% 99.998%
( 9900, 14000 ] 2762 0.001% 100.000%
( 14000, 22000 ] 2018 0.001% 100.000%
( 22000, 33000 ] 933 0.000% 100.001%
( 33000, 50000 ] 595 0.000% 100.001%
( 50000, 75000 ] 27 0.000% 100.001%
( 75000, 110000 ] 20 0.000% 100.001%
( 110000, 170000 ] 17 0.000% 100.001%
( 170000, 250000 ] 11 0.000% 100.001%
( 250000, 380000 ] 5 0.000% 100.001%
*************************** 5. row ***************************
Type: MEMORY_STATS
Name: rocksdb
Status:
MemTable Total: 1726893760
MemTable Unflushed: 41947136
Table Readers Total: 276512
Cache Total: 83508018272
Default Cache Capacity: 0
Cache Capacity: 83751862272
*************************** 6. row ***************************
Type: BG_THREADS
Name: 139992895604480
Status:
thread_type: High Pri
cf_name:
operation_type:
operation_stage:
elapsed_time_ms:
state_type:
*************************** 7. row ***************************
Type: BG_THREADS
Name: 139992903997184
Status:
thread_type: High Pri
cf_name:
operation_type:
operation_stage:
elapsed_time_ms:
state_type:
*************************** 8. row ***************************
Type: BG_THREADS
Name: 139992912389888
Status:
thread_type: Low Pri
cf_name:
operation_type:
operation_stage:
elapsed_time_ms:
state_type:
*************************** 9. row ***************************
Type: BG_THREADS
Name: 139992920782592
Status:
thread_type: Low Pri
cf_name:
operation_type:
operation_stage:
elapsed_time_ms:
state_type:
*************************** 10. row ***************************
Type: BG_THREADS
Name: 139992929175296
Status:
thread_type: Low Pri
cf_name:
operation_type:
operation_stage:
elapsed_time_ms:
state_type:
*************************** 11. row ***************************
Type: BG_THREADS
Name: 139992937568000
Status:
thread_type: Low Pri
cf_name:
operation_type:
operation_stage:
elapsed_time_ms:
state_type:
*************************** 12. row ***************************
Type: BG_THREADS
Name: 139992945960704
Status:
thread_type: Low Pri
cf_name:
operation_type:
operation_stage:
elapsed_time_ms:
state_type:
*************************** 13. row ***************************
Type: BG_THREADS
Name: 139992954353408
Status:
thread_type: Low Pri
cf_name:
operation_type:
operation_stage:
elapsed_time_ms:
state_type:
13 rows in set (2.38 sec)
from mysql-5.6.
ZhangJiaQiao
commented on April 27, 2024
You can disable TTL compactions by setting relevant RocksDB column family options (ttl and periodic_compaction_seconds) to 0. In MyRocks, you can set by rocksdb_default_cf_options='ttl=0;periodic_compaction_seconds=0' in my.cnf.
Also you may need to tune (reduce) compaction unit size. For example, set max_compaction_bytes=400m through rocksdb_default_cf_options.
Thanks for your reply. We have already disabled TTL and periodic compactions. But this problem should happen again when there are many compaction, we want to know why compaction make memory grow unlimitedly. Is there memory leak or untracked memory usage that can't be controled by configuration?
from mysql-5.6.
ZhangJiaQiao
commented on April 27, 2024
Here is our MyRocks configuration:
##### rocksdb #####
rocksdb_max_open_files = -1
rocksdb_max_background_jobs = 8
rocksdb_max_total_wal_size = 4294967296
rocksdb_block_size = 16384
rocksdb_block_cache_size = 83751862272
rocksdb_table_cache_numshardbits = 6
rocksdb_deadlock_detect = ON
rocksdb_bytes_per_sync = 4194304
rocksdb_wal_bytes_per_sync = 4194304
rocksdb_rate_limiter_bytes_per_sec = 0
rocksdb_stats_dump_period_sec = 622080000
rocksdb_max_row_locks = 100000000
rocksdb_large_prefix = ON
rocksdb_flush_log_at_trx_commit = 1
rocksdb_default_cf_options = write_buffer_size=512m;target_file_size_base=32m;max_bytes_for_level_base=512m;level0_file_num_compaction_trigger=20;level0_slowdown_writes_trigger=30;level0_stop_writes_trigger=30;max_write_buffer_number=4;compression_per_level=kLZ4Compression;bottommost_compression=kZSTD;compression_opts=-14:1:0;block_based_table_factory={cache_index_and_filter_blocks=1;filter_policy=bloomfilter:10:false;whole_key_filtering=1};level_compaction_dynamic_level_bytes=true;optimize_filters_for_hits=true;compaction_pri=kMinOverlappingRatio
from mysql-5.6.
ZhangJiaQiao
commented on April 27, 2024
We use tcmalloc as the allocator by setting the 'malloc-lib' option in the my.cnf.
##### other #####
malloc-lib = /usr/lib/libtcmalloc_minimal.so.4
from mysql-5.6.
yoshinorim
commented on April 27, 2024
It's unlikely a leak, but more likely because compaction unit size was too high (too many files were involved in a compaction at a time). You can see from LOGs about how many files were involved for compactions (grep by "Compacting "). Check OPTIONS* file (under $datadir/.rocksdb/) and see the value of max_compaction_bytes, which defines the compaction size. If you set low enough (e.g. 400mb), it will not trigger one giant compaction but trigger multiple small compactions so mem usage will be more limited.
Regarding memory allocator, we recommend jemalloc, since most RocksDB developments and tests were done with jemalloc.
from mysql-5.6.
ZhangJiaQiao
commented on April 27, 2024
It's unlikely a leak, but more likely because compaction unit size was too high (too many files were involved in a compaction at a time). You can see from LOGs about how many files were involved for compactions (grep by "Compacting "). Check OPTIONS* file (under $datadir/.rocksdb/) and see the value of max_compaction_bytes, which defines the compaction size. If you set low enough (e.g. 400mb), it will not trigger one giant compaction but trigger multiple small compactions so mem usage will be more limited.
Regarding memory allocator, we recommend jemalloc, since most RocksDB developments and tests were done with jemalloc.
It should be our fault not to set max_compaction_bytes and it is target_file_size_base * 25 by default (800M in our configuration).
tcmalloc and jemalloc seem be both great and we don't know whether choosing jemalloc or tcmalloc makes different. We will try jemalloc later.
In our observation of MyRocks memory usage, some memory used in compaction will never be released. Neither memtable or block cache owns these memory. This should be a problem because the doc of Rocksdb memory usage doesn't mention it.
from mysql-5.6.
yoshinorim
commented on April 27, 2024
Try jemalloc, disable ttl compaction, and set lower max_compaction_bytes and if you still see problems on large compactions.
You can trigger a whole column family compaction by "set global rocksdb_compact_cf='default';" so you can try on your test systems.
from mysql-5.6.
ZhangJiaQiao
commented on April 27, 2024
Try jemalloc, disable ttl compaction, and set lower max_compaction_bytes and if you still see problems on large compactions.
You can trigger a whole column family compaction by "set global rocksdb_compact_cf='default';" so you can try on your test systems.
After replacing tcmalloc with jemalloc, loading data into block cache can also make the memory usage growing problem. We configure the block cache to 95G and fill them with data. Finally, the memory usage of myrocks grows up to 120G, which is the lxc soft limit of our debian instance (using cgroup). There is no compaction when loading the data.
However, when we reduce the block cache size to 10G, we can't see the problem when loading data into the block cache. Is the fragmentation of allocation that cause the problem? What is the size of block cache that we should set in myrocks?
from mysql-5.6.
ZhangJiaQiao
commented on April 27, 2024
Here is the memory stats:
*************************** 5. row ***************************
Type: MEMORY_STATS
Name: rocksdb
Status:
MemTable Total: 1052672
MemTable Unflushed: 1052672
Table Readers Total: 812608
Cache Total: 100735449129
Default Cache Capacity: 0
Cache Capacity: 100737418240
memory usage:
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
xxx xxx 20 0 138.9g 119.8g 28500 S 115.0 66.5 19:00.69 mysqld
jeprof:
Total: 97582.5 MB
96080.2 98.5% 98.5% 96080.2 98.5% rocksdb::UncompressBlockContentsForCompressionType (inline)
567.0 0.6% 99.0% 637.6 0.7% rocksdb::LRUCacheShard::Insert@240130
446.0 0.5% 99.5% 446.0 0.5% rocksdb::BlockBasedTable::PutDataBlockToCache (inline)
150.2 0.2% 99.7% 150.2 0.2% pfs_malloc
70.6 0.1% 99.7% 70.6 0.1% rocksdb::LRUHandleTable::Resize
60.6 0.1% 99.8% 60.6 0.1% ut_allocator::allocate
50.3 0.1% 99.8% 50.3 0.1% std::string::_Rep::_S_create
40.0 0.0% 99.9% 40.0 0.0% ut_allocator::allocate [clone .constprop.74]
38.5 0.0% 99.9% 38.5 0.0% my_raw_malloc (inline)
32.0 0.0% 100.0% 37.0 0.0% std::_Rb_tree::_M_insert_unique (inline)
7.0 0.0% 100.0% 7.0 0.0% mem_heap_create_block_func (inline)
7.0 0.0% 100.0% 82.0 0.1% rocksdb::ReadProperties
5.0 0.0% 100.0% 272.0 0.3% rocksdb::BlockBasedTable::Open
from mysql-5.6.
yoshinorim
commented on April 27, 2024
loading data into block
Do you mean LOAD DATA INFILE or large bulk inserts (GBs of data) by one transaction?
from mysql-5.6.
ZhangJiaQiao
commented on April 27, 2024
loading data into block
Do you mean LOAD DATA INFILE or large bulk inserts (GBs of data) by one transaction?
No, just using read operation of select * from table to fill up the block cache, no insert operation
from mysql-5.6.
ZhangJiaQiao
commented on April 27, 2024
@yoshinorim
Finally, I solved this problem in two ways.
- Disable TTL compaction. But it will lead to a little more occupation of disk capacity.
- Set the TCMALLOC_RELEASE_RATE environment variable to 10(default 1) for tcmalloc then restart mysql. I guess it is the memory caching of tcmalloc that make mysql occupy too much memory. After I set TCMALLOC_RELEASE_RATE, the memory occupation is under the limit of our configuration.
Details about tcmalloc TCMALLOC_RELEASE_RATE
from mysql-5.6.
Related Issues (20)
- Warning from compiling clone_os.cc HOT 6
- advisable to use many databases or many more tables?
- MyRocks 8.0.28 hangs during the insert benchmark HOT 6
- force_flush_memtable_and_lzero_now always compacts to physical L1
- Make compilation faster HOT 1
- How to achieve ~20% replication throughput improvement using Read Free Replication (RFR) feature
- Keep long-running MyRocks mtr tests in their own suite HOT 1
- MyRocks 8.0.28 has poor performance of primary key range query HOT 1
- create secondary index needs attention
- issue while install HOT 1
- Error during create secondary index
- Update the Build Steps page HOT 1
- MyRocks engine should respect WITH_UNIT_TESTS
- Allow users to manually set the number of block cache shards HOT 1
- Provide a counter to show pending compaction bytes for RocksDB
- MyRocks does commit step non-durably under server group 2PC protocol HOT 1
- alter talbe add index optimization
- Range lock support HOT 2
- Cached RocksDB transaction object accessed after delete by XA COMMIT
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from mysql-5.6.