InnoDB memory usage discrepancies
Posted by: Ankur Shukla
Date: December 27, 2022 05:26AM
Date: December 27, 2022 05:26AM
MySQL v8.0.23
I am trying to understand memory used by mysql server, with InnoDb engine. This is a dedicated server for mysql only. I pulled memory stats using free, top, and from within mysql. Considering major memory for buffer pool and connections, there is a different of ~20GB in output of top/free and mysql internal calculations.
free -mh
total used free shared buff/cache available
Mem: 119G 104G 876M 908K 14G 13G
Swap: 0B 0B 0B
top -o %MEM
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
26502 mysql 20 0 0.103t 0.102t 12120 S 113.3 86.7 265894:41 mysqld
129344 root 19 -1 352404 138448 133868 S 0.0 0.1 9:12.45 systemd-journal
mysql> select @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
| 83751862272 |
+---------------------------+
mysql> show global status like 'max_used_connections';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Max_used_connections | 423 |
+----------------------+-------+
SELECT ( @@key_buffer_size + @@innodb_buffer_pool_size + 423 * (@@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size + @@join_buffer_size + @@tmp_table_size )) / (1024*1024*1024)
AS MAX_MEMORY_GB;
+---------------+
| MAX_MEMORY_GB |
+---------------+
| 86.5277 |
+---------------+
mysql> show variables like '%innodb%';
+------------------------------------------+------------------------+
| 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_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 | 2 |
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_in_core_file | ON |
| innodb_buffer_pool_instances | 8 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 83751862272 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
| innodb_checksum_algorithm | crc32 |
| 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_directories | |
| innodb_disable_sort_file_cache | OFF |
| innodb_doublewrite | ON |
| innodb_doublewrite_batch_size | 0 |
| innodb_doublewrite_dir | |
| innodb_doublewrite_files | 2 |
| innodb_doublewrite_pages | 32 |
| innodb_extend_and_initialize | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_per_table | ON |
| innodb_fill_factor | 100 |
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 0 |
| innodb_flush_method | fsync |
| innodb_flush_neighbors | 0 |
| innodb_flush_sync | ON |
| innodb_flushing_avg_loops | 30 |
| innodb_force_load_corrupted | OFF |
| innodb_force_recovery | 0 |
| 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_idle_flush_pct | 100 |
| innodb_io_capacity | 200 |
| innodb_io_capacity_max | 2000 |
| innodb_lock_wait_timeout | 50 |
| innodb_log_buffer_size | 33554432 |
| innodb_log_checksums | ON |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 536870912 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_log_spin_cpu_abs_lwm | 80 |
| innodb_log_spin_cpu_pct_hwm | 50 |
| innodb_log_wait_for_flush_spin_hwm | 400 |
| innodb_log_write_ahead_size | 8192 |
| innodb_log_writer_threads | ON |
| 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_monitor_disable | |
| innodb_monitor_enable | |
| innodb_monitor_reset | |
| innodb_monitor_reset_all | |
| innodb_numa_interleave | OFF |
| innodb_old_blocks_pct | 37 |
| innodb_old_blocks_time | 1000 |
| innodb_online_alter_log_max_size | 134217728 |
| innodb_open_files | 4000 |
| innodb_optimize_fulltext_only | OFF |
| innodb_page_cleaners | 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 | 32 |
| innodb_read_only | OFF |
| innodb_redo_log_archive_dirs | |
| innodb_redo_log_encrypt | OFF |
| innodb_replication_delay | 0 |
| innodb_rollback_on_timeout | OFF |
| innodb_rollback_segments | 128 |
| innodb_sort_buffer_size | 1048576 |
| innodb_spin_wait_delay | 6 |
| innodb_spin_wait_pause_multiplier | 50 |
| 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_spin_loops | 30 |
| innodb_table_locks | ON |
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
| innodb_temp_tablespaces_dir | ./#innodb_temp/ |
| innodb_thread_concurrency | 0 |
| innodb_thread_sleep_delay | 10000 |
| innodb_tmpdir | |
| innodb_undo_directory | ./ |
| innodb_undo_log_encrypt | OFF |
| innodb_undo_log_truncate | ON |
| innodb_undo_tablespaces | 2 |
| innodb_use_native_aio | ON |
| innodb_validate_tablespace_paths | ON |
| innodb_version | 8.0.23 |
| innodb_write_io_threads | 32 |
+------------------------------------------+------------------------+
146 rows in set (0.00 sec)
The used memory is ~104GB with no other process using significant RAM. What else could be contributing to mysql memory usage?
I am trying to understand memory used by mysql server, with InnoDb engine. This is a dedicated server for mysql only. I pulled memory stats using free, top, and from within mysql. Considering major memory for buffer pool and connections, there is a different of ~20GB in output of top/free and mysql internal calculations.
free -mh
total used free shared buff/cache available
Mem: 119G 104G 876M 908K 14G 13G
Swap: 0B 0B 0B
top -o %MEM
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
26502 mysql 20 0 0.103t 0.102t 12120 S 113.3 86.7 265894:41 mysqld
129344 root 19 -1 352404 138448 133868 S 0.0 0.1 9:12.45 systemd-journal
mysql> select @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
| 83751862272 |
+---------------------------+
mysql> show global status like 'max_used_connections';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Max_used_connections | 423 |
+----------------------+-------+
SELECT ( @@key_buffer_size + @@innodb_buffer_pool_size + 423 * (@@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size + @@join_buffer_size + @@tmp_table_size )) / (1024*1024*1024)
AS MAX_MEMORY_GB;
+---------------+
| MAX_MEMORY_GB |
+---------------+
| 86.5277 |
+---------------+
mysql> show variables like '%innodb%';
+------------------------------------------+------------------------+
| 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_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 | 2 |
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_in_core_file | ON |
| innodb_buffer_pool_instances | 8 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 83751862272 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
| innodb_checksum_algorithm | crc32 |
| 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_directories | |
| innodb_disable_sort_file_cache | OFF |
| innodb_doublewrite | ON |
| innodb_doublewrite_batch_size | 0 |
| innodb_doublewrite_dir | |
| innodb_doublewrite_files | 2 |
| innodb_doublewrite_pages | 32 |
| innodb_extend_and_initialize | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_per_table | ON |
| innodb_fill_factor | 100 |
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 0 |
| innodb_flush_method | fsync |
| innodb_flush_neighbors | 0 |
| innodb_flush_sync | ON |
| innodb_flushing_avg_loops | 30 |
| innodb_force_load_corrupted | OFF |
| innodb_force_recovery | 0 |
| 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_idle_flush_pct | 100 |
| innodb_io_capacity | 200 |
| innodb_io_capacity_max | 2000 |
| innodb_lock_wait_timeout | 50 |
| innodb_log_buffer_size | 33554432 |
| innodb_log_checksums | ON |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 536870912 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_log_spin_cpu_abs_lwm | 80 |
| innodb_log_spin_cpu_pct_hwm | 50 |
| innodb_log_wait_for_flush_spin_hwm | 400 |
| innodb_log_write_ahead_size | 8192 |
| innodb_log_writer_threads | ON |
| 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_monitor_disable | |
| innodb_monitor_enable | |
| innodb_monitor_reset | |
| innodb_monitor_reset_all | |
| innodb_numa_interleave | OFF |
| innodb_old_blocks_pct | 37 |
| innodb_old_blocks_time | 1000 |
| innodb_online_alter_log_max_size | 134217728 |
| innodb_open_files | 4000 |
| innodb_optimize_fulltext_only | OFF |
| innodb_page_cleaners | 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 | 32 |
| innodb_read_only | OFF |
| innodb_redo_log_archive_dirs | |
| innodb_redo_log_encrypt | OFF |
| innodb_replication_delay | 0 |
| innodb_rollback_on_timeout | OFF |
| innodb_rollback_segments | 128 |
| innodb_sort_buffer_size | 1048576 |
| innodb_spin_wait_delay | 6 |
| innodb_spin_wait_pause_multiplier | 50 |
| 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_spin_loops | 30 |
| innodb_table_locks | ON |
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
| innodb_temp_tablespaces_dir | ./#innodb_temp/ |
| innodb_thread_concurrency | 0 |
| innodb_thread_sleep_delay | 10000 |
| innodb_tmpdir | |
| innodb_undo_directory | ./ |
| innodb_undo_log_encrypt | OFF |
| innodb_undo_log_truncate | ON |
| innodb_undo_tablespaces | 2 |
| innodb_use_native_aio | ON |
| innodb_validate_tablespace_paths | ON |
| innodb_version | 8.0.23 |
| innodb_write_io_threads | 32 |
+------------------------------------------+------------------------+
146 rows in set (0.00 sec)
The used memory is ~104GB with no other process using significant RAM. What else could be contributing to mysql memory usage?
Subject
Written By
Posted
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.