I have a Ubuntu 20.04 machine with 64 GB of RAM that is used both as a web server and for MySQL version 8.0.35. I am using InnoDB, but even if the innodb_buffer_pool_size variable is set to 128MB, the memory consumption from MySQL is taking most of the 64GB of RAM plus Swap.
The size of the databases is just 5GB. The the access is in the order of several requests per seconds to read, and every ~10 min to write. The tables are nothing big either. Each database can contain 1000 tables with 1000-5000 rows each.
I understand that optimizing the MySQL server would require changing one parameter at the time, monitor, and iterate towards the optimum values. But here it feels that there is something fundamentally wrong that I am missing on the first place.
Any idea on what this could be?
htop
Database sizes
+--------------------------+---------------+
| DB Name | DB Size in MB |
+--------------------------+---------------+
| mysql | 17.0 |
| sys | 0.0 |
| information_schema | 0.0 |
| database_1 | 3878.9 |
| database_2 | 227.4 |
| database_3 | 3.1 |
| database_4 | 1.0 |
| database_5 | 937.3 |
| database_6 | 0.0 |
| database_7 | 281.8 |
| database_7 | 280.7 |
| sessions_data | 0.0 |
| database_8 | 53.7 |
| database_9 | 0.2 |
+--------------------------+---------------+
InnoDB settings
+------------------------------------------+------------------------+
| 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 | 1 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 134217728 |
| 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_ddl_buffer_size | 1048576 |
| innodb_ddl_threads | 4 |
| 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 | 4 |
| 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 | 1 |
| 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 | 16777216 |
| innodb_log_checksums | ON |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 50331648 |
| 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 | 1 |
| 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_only | OFF |
| innodb_redo_log_archive_dirs | |
| innodb_redo_log_capacity | 104857600 |
| innodb_redo_log_encrypt | OFF |
| innodb_replication_delay | 0 |
| innodb_rollback_on_timeout | OFF |
| innodb_rollback_segments | 128 |
| innodb_segment_reserve_factor | 12.500000 |
| 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_fdatasync | OFF |
| innodb_use_native_aio | ON |
| innodb_validate_tablespace_paths | ON |
| innodb_version | 8.0.35 |
| innodb_write_io_threads | 4 |
+------------------------------------------+------------------------+
Max settings
+----------------------------+----------------------+
| Variable_name | Value |
+----------------------------+----------------------+
| max_allowed_packet | 67108864 |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 104857600 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| max_connect_errors | 100 |
| max_connections | 151 |
| max_delayed_threads | 20 |
| max_digest_length | 1024 |
| max_error_count | 1024 |
| max_execution_time | 0 |
| max_heap_table_size | 16777216 |
| max_insert_delayed_threads | 20 |
| max_join_size | 18446744073709551615 |
| max_length_for_sort_data | 4096 |
| max_points_in_geometry | 65536 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 18446744073709551615 |
| max_sort_length | 1024 |
| max_sp_recursion_depth | 0 |
| max_user_connections | 0 |
| max_write_lock_count | 18446744073709551615 |
+----------------------------+----------------------+
Query settings
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| query_alloc_block_size | 8192 |
| query_prealloc_size | 8192 |
+------------------------+-------+
SHOW GLOBAL STATUS LIKE 'Max_used_connections';? I agree that 128M is too low for that one setting. I can't tell ifmax_connectionsis too high -- it leads to more memory being allocated. Don't even think about tweaking the hundreds of settings one at a time; nearly all of them won't make a noticeable difference. 9.8% = only 6.328GB. – Rick James Jan 24 '24 at 16:34htop 1st page, if available, TERMINATE, top -b -n 1 for most active apps, top -b -n 1 -H for details on your mysql threads memory and cpu usage, ulimit -a for list of limits, iostat -xm 5 3 for IOPS by device & core/cpu count, df -h for Used - Free space by device, df -i for inode info by device, free -h for Used - Free Mem: and Swap:, cat /proc/meminfo includes VMallocUused,
for server workload tuning analysis to provide suggestions. – Wilson Hauck Jan 24 '24 at 17:04
Max_used_connectionsgives 152, and I havemax_connectionsto 151. – marcos Jan 24 '24 at 19:46