CREATE TABLE `user_info` (
`id` INT(10) NOT NULL,
`doc` JSON NOT NULL,
`rev` INT(11) NOT NULL DEFAULT '0',
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
`updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (`id`),
INDEX `updated_at` (`updated_at`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB;
I'm simply using it for storing json data.
The size of the json field increases gradually from at least 15kb to have data within 100kb.
It was confirmed that about 82,317 rows occupied 90 GB.
Name: "user_info" Engine: "InnoDB" Version: 10 Row_format: "Dynamic" Rows: 82317 Avg_row_length: 1095193 Data_length: 90153074688 Max_data_length: 0 Index_length: 3555328 Data_free: 40894464 Auto_increment: null Create_time: "2020-05-19 10:56:14" Update_time: "2020-08-21 07:30:17" Check_time: null Collation: "utf8mb4_general_ci" Checksum: null Create_options: "" Comment: ""
The expected maximum disk usage is 8 GB.
Dump file size is 1.2 GB, and when restored, you are using a 1.9 GB disk.
Name: "user_info" Engine: "InnoDB" Version: 10 Row_format: "Dynamic" Rows: 84626 Avg_row_length: 22770 Data_length: 1926987776 Max_data_length: 0 Index_length: 3686400 Data_free: 14680064 Auto_increment: null Create_time: "2020-08-21 07:59:34" Update_time: "2020-08-21 08:02:05" Check_time: null Collation: "utf8mb4_general_ci" Checksum: null Create_options: "" Comment: "
I suspect that the json field is causing database fragmentation.
I would like to ask if you have similar experience.
Currently I'm looking at how to allocate a field with a fixed length of around 200kb.
session_variables --- | Variable_name | Value | | --- | --- | | innodb_adaptive_flushing | ON | | innodb_adaptive_flushing_lwm | 10 | | innodb_adaptive_hash_index | ON | | innodb_adaptive_hash_index_parts | 8 | | innodb_adaptive_max_sleep_delay | 150000 | | innodb_aggressive_pc | OFF | | innodb_aio_write_slots | 32 | | innodb_api_bk_commit_interval | 5 | | innodb_api_disable_rowlock | OFF | | innodb_api_enable_binlog | OFF | | innodb_api_enable_mdl | OFF | | innodb_api_trx_level | 0 | | innodb_autoextend_increment | 64 | | innodb_autoinc_lock_mode | 1 | | innodb_avoid_logwrite | OFF | | innodb_buffer_pool_chunk_size | 67108864 | | innodb_buffer_pool_dump_at_shutdown | OFF | | innodb_buffer_pool_dump_now | OFF | | innodb_buffer_pool_dump_pct | 25 | | innodb_buffer_pool_filename | ib_buffer_pool | | innodb_buffer_pool_in_core_file | ON | | innodb_buffer_pool_instances | 8 | | innodb_buffer_pool_load_abort | OFF | | innodb_buffer_pool_load_at_startup | OFF | | innodb_buffer_pool_load_now | OFF | | innodb_buffer_pool_size | 3758096384 | | innodb_change_buffer_max_size | 25 | | innodb_change_buffering | all | | innodb_checksum_algorithm | crc32 | | innodb_chunk_write_enabled | OFF | | innodb_cmp_per_index_enabled | OFF | | innodb_commit_concurrency | 0 | | innodb_compression_failure_threshold_pct | 5 | | innodb_compression_level | 6 | | innodb_compression_pad_pct_max | 50 | | innodb_concurrency_tickets | 5000 | | innodb_data_file_path | ibdata1:12M:autoextend | | innodb_data_home_dir | | | innodb_deadlock_detect | ON | | innodb_dedicated_server | OFF | | innodb_default_row_format | dynamic | | innodb_delay_datasync | OFF | | innodb_delay_logwrite | OFF | | innodb_directories | | | innodb_dirty_page_fflush | 10 | | innodb_disable_sort_file_cache | OFF | | innodb_diskfull_wa_enabled | OFF | | innodb_doublewrite | OFF | | innodb_dynamic_page_cleaners | OFF | | innodb_fast0ext_enabled | OFF | | innodb_fast_shutdown | 1 | | innodb_fatal_semaphore_wait_threshold | 600 | | innodb_file_per_table | OFF | | innodb_filesize_update_interval | 0 | | innodb_fill_factor | 100 | | innodb_flush_data_file_before_checkpoint | ON | | innodb_flush_log_at_timeout | 1 | | innodb_flush_log_at_trx_commit | 1 | | innodb_flush_log_checkpoint | ON | | innodb_flush_method | unbuffered | | innodb_flush_neighbors | 0 | | innodb_flush_sync | ON | | innodb_flushing_avg_loops | 30 | | innodb_force_load_corrupted | OFF | | innodb_force_pacing | OFF | | innodb_force_recovery | 0 | | innodb_fs_block_size | 8192 | | innodb_fsync_threshold | 0 | | innodb_ft_aux_table | | | innodb_ft_cache_size | 8000000 | | innodb_ft_enable_diag_print | OFF | | innodb_ft_enable_stopword | ON | | innodb_ft_max_token_size | 84 | | innodb_ft_min_token_size | 3 | | innodb_ft_num_word_optimize | 2000 | | innodb_ft_result_cache_limit | 2000000000 | | innodb_ft_server_stopword_table | | | innodb_ft_sort_pll_degree | 2 | | innodb_ft_total_cache_size | 640000000 | | innodb_ft_user_stopword_table | | | innodb_harness_checkpoint | OFF | | innodb_ignore_fsctl_set_sparse | ON | | innodb_ignore_fsctl_set_zero_data | ON | | innodb_inflight_syncio | 100 | | innodb_io_capacity | 200 | | innodb_io_capacity_calibceil | 300 | | innodb_io_capacity_calibrate | 0 | | innodb_io_capacity_max | 2000 | | innodb_io_hosed_threshold | 2048000 | | innodb_io_throttled_threshold | 2048000 | | innodb_list_dir_files_name_only | OFF | | innodb_lock_wait_timeout | 50 | | innodb_log_buffer_size | 16777216 | | innodb_log_checkpoint_every | 1000 | | innodb_log_checksums | ON | | innodb_log_closer_spin_delay | 0 | | innodb_log_closer_timeout | 1000 | | innodb_log_compressed_pages | ON | | innodb_log_file_size | 268435456 | | innodb_log_files_in_group | 2 | | innodb_log_flush_events | 2048 | | innodb_log_flush_notifier_spin_delay | 0 | | innodb_log_flush_notifier_timeout | 10 | | innodb_log_flusher_spin_delay | 250000 | | innodb_log_flusher_timeout | 10 | | innodb_log_group_home_dir | .\ | | innodb_log_recent_closed_size | 2097152 | | innodb_log_recent_written_size | 1048576 | | innodb_log_spin_cpu_abs_lwm | 80 | | innodb_log_spin_cpu_pct_hwm | 50 | | innodb_log_wait_for_flush_spin_delay | 25000 | | innodb_log_wait_for_flush_spin_hwm | 400 | | innodb_log_wait_for_flush_timeout | 1000 | | innodb_log_wait_for_write_spin_delay | 25000 | | innodb_log_wait_for_write_timeout | 1000 | | innodb_log_write_ahead_size | 8192 | | innodb_log_write_backoff | OFF | | innodb_log_write_events | 2048 | | innodb_log_write_max_size | 4096 | | innodb_log_write_notifier_spin_delay | 0 | | innodb_log_write_notifier_timeout | 10 | | innodb_log_writer_spin_delay | 250000 | | innodb_log_writer_timeout | 10 | | innodb_logwr_prio_checkpoint | 0 | | innodb_lru_scan_depth | 1024 | | innodb_max_dirty_pages_pct | 90.000000 | | innodb_max_dirty_pages_pct_lwm | 10.000000 | | innodb_max_purge_lag | 0 | | innodb_max_purge_lag_delay | 0 | | innodb_max_undo_log_size | 1073741824 | | innodb_min_tsc_freq | 2000 | | innodb_monitor_disable | | | innodb_monitor_enable | | | innodb_monitor_reset | | | innodb_monitor_reset_all | | | innodb_old_blocks_pct | 37 | | innodb_old_blocks_time | 1000 | | innodb_online_alter_log_max_size | 134217728 | | innodb_open_files | 300 | | innodb_optimize_fulltext_only | OFF | | innodb_page_cleaner_priority | 0 | | innodb_page_cleaners | 4 | | innodb_page_cleaners_enforced | 4 | | innodb_page_size | 16384 | | innodb_parallel_read_threads | 4 | | innodb_print_all_deadlocks | OFF | | innodb_print_ddl_logs | OFF | | innodb_purge_batch_size | 300 | | innodb_purge_rseg_truncate_frequency | 128 | | innodb_purge_threads | 4 | | innodb_random_read_ahead | OFF | | innodb_read_ahead_threshold | 56 | | innodb_read_io_threads | 4 | | innodb_read_io_threads_enforced | 4 | | innodb_read_only | OFF | | innodb_read_throttle_hint | OFF | | innodb_redo_log_encrypt | OFF | | innodb_replication_delay | 0 | | innodb_rollback_on_timeout | OFF | | innodb_rollback_segments | 128 | | innodb_skip_check_ibd | OFF | | innodb_sort_buffer_size | 1048576 | | innodb_spin_wait_delay | 6 | | innodb_standalone_systmp | OFF | | innodb_stats_auto_recalc | ON | | innodb_stats_include_delete_marked | OFF | | innodb_stats_method | nulls_equal | | innodb_stats_on_metadata | OFF | | innodb_stats_persistent | ON | | innodb_stats_persistent_sample_pages | 20 | | innodb_stats_transient_sample_pages | 8 | | innodb_status_output | OFF | | innodb_status_output_locks | OFF | | innodb_strict_mode | ON | | innodb_sync_array_size | 1 | | innodb_sync_log_slots | 0 | | innodb_sync_spin_loops | 30 | | innodb_table_locks | ON | | innodb_temp_data_file_path | C:\temp\ibtmp1:12M:autoextend | | innodb_temp_tablespaces_dir | C:\temp\ | | innodb_thread_concurrency | 0 | | innodb_thread_sleep_delay | 10000 | | innodb_tmpdir | C:\temp | | innodb_undo_directory | .\ | | innodb_undo_log_encrypt | OFF | | innodb_undo_log_truncate | ON | | innodb_undo_tablespaces | 2 | | innodb_use_enforced | OFF | | innodb_use_native_aio | ON | | innodb_version | 8.0.15 | | innodb_write_io_threads | 4 | | innodb_write_io_threads_enforced | 4 | | innodb_write_throttle_hint | OFF |
