We are using MariaDB 10.3.32 on a Ubuntu 20.04.4 machine with 6 GB of memory, about 20 applications running on it. Databases are all InnoDB.
Even with mostly default settings (see on the bottom), memory usage increases from day to day, seemingly non-stop. After about a month of uptime the OOM killer starts doing its work.
According to this SQL statement, peak memory usage should be around 600 MB. I know these calculations are not exact, but even after a restart of the server yesterday, memory usage is already at 1,6 GB now (about 200 MB at startup).
SELECT ROUND(
( @@GLOBAL.key_buffer_size
+ @@GLOBAL.query_cache_size
+ @@GLOBAL.tmp_table_size
+ @@GLOBAL.innodb_buffer_pool_size
+ @@GLOBAL.innodb_log_buffer_size
+ @@GLOBAL.max_connections * (
@@GLOBAL.sort_buffer_size
+ @@GLOBAL.read_buffer_size
+ @@GLOBAL.read_rnd_buffer_size
+ @@GLOBAL.join_buffer_size
+ @@GLOBAL.thread_stack
+ @@GLOBAL.binlog_cache_size)
) / 1024 / 1024, 1) `total MB`;
What am I missing here?
SHOW VARIABLES;
alter_algorithm=DEFAULT
aria_block_size=8192
aria_checkpoint_interval=30
aria_checkpoint_log_activity=1048576
aria_encrypt_tables=OFF
aria_force_start_after_recovery_failures=0
aria_group_commit=none
aria_group_commit_interval=0
aria_log_file_size=1073741824
aria_log_purge_type=immediate
aria_max_sort_file_size=9223372036853727232
aria_page_checksum=ON
aria_pagecache_age_threshold=300
aria_pagecache_buffer_size=134217728
aria_pagecache_division_limit=100
aria_pagecache_file_hash_size=512
aria_recover_options=BACKUP,QUICK
aria_repair_threads=1
aria_sort_buffer_size=268434432
aria_stats_method=nulls_unequal
aria_sync_log_dir=NEWFILE
aria_used_for_temp_tables=ON
auto_increment_increment=1
auto_increment_offset=1
autocommit=ON
automatic_sp_privileges=ON
back_log=80
basedir=/usr
big_tables=OFF
bind_address=::ffff:127.0.0.1
binlog_annotate_row_events=ON
binlog_cache_size=32768
binlog_checksum=CRC32
binlog_commit_wait_count=0
binlog_commit_wait_usec=100000
binlog_direct_non_transactional_updates=OFF
binlog_file_cache_size=16384
binlog_format=MIXED
binlog_optimize_thread_scheduling=ON
binlog_row_image=FULL
binlog_stmt_cache_size=32768
bulk_insert_buffer_size=8388608
character_set_client=utf8mb4
character_set_connection=utf8mb4
character_set_database=utf8
character_set_filesystem=binary
character_set_results=utf8mb4
character_set_server=utf8mb4
character_set_system=utf8
character_sets_dir=/usr/share/mysql/charsets/
check_constraint_checks=ON
collation_connection=utf8mb4_general_ci
collation_database=utf8_general_ci
collation_server=utf8mb4_general_ci
column_compression_threshold=100
column_compression_zlib_level=6
column_compression_zlib_strategy=DEFAULT_STRATEGY
column_compression_zlib_wrap=OFF
completion_type=NO_CHAIN
concurrent_insert=AUTO
connect_timeout=10
core_file=OFF
datadir=/var/lib/mysql/
date_format=%Y-%m-%d
datetime_format=%Y-%m-%d %H:%i:%s
deadlock_search_depth_long=15
deadlock_search_depth_short=4
deadlock_timeout_long=50000000
deadlock_timeout_short=10000
debug_no_thread_alarm=OFF
default_master_connection=
default_regex_flags=
default_storage_engine=InnoDB
default_tmp_storage_engine=
default_week_format=0
delay_key_write=ON
delayed_insert_limit=100
delayed_insert_timeout=300
delayed_queue_size=1000
div_precision_increment=4
encrypt_binlog=OFF
encrypt_tmp_disk_tables=OFF
encrypt_tmp_files=OFF
enforce_storage_engine=
eq_range_index_dive_limit=0
error_count=0
event_scheduler=OFF
expensive_subquery_limit=100
expire_logs_days=10
explicit_defaults_for_timestamp=OFF
external_user=
extra_max_connections=1
extra_port=0
flush=OFF
flush_time=0
foreign_key_checks=ON
ft_boolean_syntax=+ -><()~*:""&|
ft_max_word_len=84
ft_min_word_len=4
ft_query_expansion_limit=20
ft_stopword_file=(built-in)
general_log=OFF
general_log_file=samson.log
group_concat_max_len=1048576
gtid_binlog_pos=
gtid_binlog_state=
gtid_current_pos=
gtid_domain_id=0
gtid_ignore_duplicates=OFF
gtid_pos_auto_engines=
gtid_seq_no=0
gtid_slave_pos=
gtid_strict_mode=OFF
have_compress=YES
have_crypt=YES
have_dynamic_loading=YES
have_geometry=YES
have_openssl=NO
have_profiling=YES
have_query_cache=YES
have_rtree_keys=YES
have_ssl=DISABLED
have_symlink=YES
histogram_size=0
histogram_type=SINGLE_PREC_HB
host_cache_size=279
hostname=samson.company.com
identity=0
idle_readonly_transaction_timeout=0
idle_transaction_timeout=0
idle_write_transaction_timeout=0
ignore_builtin_innodb=OFF
ignore_db_dirs=
in_predicate_conversion_threshold=1000
in_transaction=0
init_connect=
init_file=
init_slave=
innodb_adaptive_flushing=ON
innodb_adaptive_flushing_lwm=10.000000
innodb_adaptive_hash_index=ON
innodb_adaptive_hash_index_parts=8
innodb_adaptive_max_sleep_delay=150000
innodb_autoextend_increment=64
innodb_autoinc_lock_mode=1
innodb_background_scrub_data_check_interval=3600
innodb_background_scrub_data_compressed=OFF
innodb_background_scrub_data_interval=604800
innodb_background_scrub_data_uncompressed=OFF
innodb_buf_dump_status_frequency=0
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_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_checksums=ON
innodb_cmp_per_index_enabled=OFF
innodb_commit_concurrency=0
innodb_compression_algorithm=zlib
innodb_compression_default=OFF
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_default_encryption_key_id=1
innodb_default_row_format=dynamic
innodb_defragment=OFF
innodb_defragment_fill_factor=0.900000
innodb_defragment_fill_factor_n_recs=20
innodb_defragment_frequency=40
innodb_defragment_n_pages=7
innodb_defragment_stats_accuracy=0
innodb_disable_sort_file_cache=OFF
innodb_disallow_writes=OFF
innodb_doublewrite=ON
innodb_encrypt_log=OFF
innodb_encrypt_tables=OFF
innodb_encrypt_temporary_tables=OFF
innodb_encryption_rotate_key_age=1
innodb_encryption_rotation_iops=100
innodb_encryption_threads=0
innodb_fast_shutdown=1
innodb_fatal_semaphore_wait_threshold=600
innodb_file_format=
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=1
innodb_flush_sync=ON
innodb_flushing_avg_loops=30
innodb_force_load_corrupted=OFF
innodb_force_primary_key=OFF
innodb_force_recovery=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_immediate_scrub_data_uncompressed=OFF
innodb_instant_alter_column_allowed=add_last
innodb_io_capacity=200
innodb_io_capacity_max=2000
innodb_large_prefix=
innodb_lock_schedule_algorithm=fcfs
innodb_lock_wait_timeout=50
innodb_locks_unsafe_for_binlog=OFF
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_optimize_ddl=OFF
innodb_log_write_ahead_size=8192
innodb_lru_scan_depth=1024
innodb_max_dirty_pages_pct=75.000000
innodb_max_dirty_pages_pct_lwm=0.000000
innodb_max_purge_lag=0
innodb_max_purge_lag_delay=0
innodb_max_purge_lag_wait=4294967295
innodb_max_undo_log_size=10485760
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=2000
innodb_optimize_fulltext_only=OFF
innodb_page_cleaners=1
innodb_page_size=16384
innodb_prefix_index_cluster_optimization=OFF
innodb_print_all_deadlocks=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_replication_delay=0
innodb_rollback_on_timeout=OFF
innodb_rollback_segments=128
innodb_scrub_log=OFF
innodb_scrub_log_speed=256
innodb_sort_buffer_size=1048576
innodb_spin_wait_delay=4
innodb_stats_auto_recalc=ON
innodb_stats_include_delete_marked=OFF
innodb_stats_method=nulls_equal
innodb_stats_modified_counter=0
innodb_stats_on_metadata=OFF
innodb_stats_persistent=ON
innodb_stats_persistent_sample_pages=20
innodb_stats_sample_pages=8
innodb_stats_traditional=ON
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_thread_concurrency=0
innodb_thread_sleep_delay=10000
innodb_tmpdir=
innodb_undo_directory=./
innodb_undo_log_truncate=OFF
innodb_undo_logs=128
innodb_undo_tablespaces=0
innodb_use_atomic_writes=ON
innodb_use_native_aio=ON
innodb_version=10.3.32
innodb_write_io_threads=4
insert_id=0
interactive_timeout=28800
join_buffer_size=262144
join_buffer_space_limit=2097152
join_cache_level=2
keep_files_on_create=OFF
key_buffer_size=65536
key_cache_age_threshold=300
key_cache_block_size=1024
key_cache_division_limit=100
key_cache_file_hash_size=512
key_cache_segments=0
large_files_support=ON
large_page_size=0
large_pages=OFF
last_gtid=
last_insert_id=0
lc_messages=en_US
lc_messages_dir=/usr/share/mysql
lc_time_names=en_US
license=GPL
local_infile=OFF
lock_wait_timeout=86400
locked_in_memory=OFF
log_bin=OFF
log_bin_basename=
log_bin_compress=OFF
log_bin_compress_min_len=256
log_bin_index=
log_bin_trust_function_creators=OFF
log_disabled_statements=sp
log_error=/var/log/mysql/error.log
log_output=FILE
log_queries_not_using_indexes=OFF
log_slave_updates=OFF
log_slow_admin_statements=ON
log_slow_disabled_statements=sp
log_slow_filter=admin,filesort,filesort_on_disk,filesort_priority_queue,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
log_slow_rate_limit=1
log_slow_slave_statements=ON
log_slow_verbosity=
log_tc_size=24576
log_warnings=2
long_query_time=10.000000
low_priority_updates=OFF
lower_case_file_system=OFF
lower_case_table_names=0
master_verify_checksum=OFF
max_allowed_packet=67108864
max_binlog_cache_size=18446744073709547520
max_binlog_size=1073741824
max_binlog_stmt_cache_size=18446744073709547520
max_connect_errors=100
max_connections=151
max_delayed_threads=20
max_digest_length=1024
max_error_count=64
max_heap_table_size=16777216
max_insert_delayed_threads=20
max_join_size=18446744073709551615
max_length_for_sort_data=1024
max_long_data_size=67108864
max_prepared_stmt_count=16382
max_recursive_iterations=4294967295
max_relay_log_size=1073741824
max_seeks_for_key=4294967295
max_session_mem_used=9223372036854775807
max_sort_length=1024
max_sp_recursion_depth=0
max_statement_time=0.000000
max_tmp_tables=32
max_user_connections=0
max_write_lock_count=4294967295
metadata_locks_cache_size=1024
metadata_locks_hash_instances=8
min_examined_row_limit=0
mrr_buffer_size=262144
multi_range_count=256
myisam_block_size=1024
myisam_data_pointer_size=6
myisam_max_sort_file_size=9223372036853727232
myisam_mmap_size=18446744073709551615
myisam_recover_options=BACKUP,QUICK
myisam_repair_threads=1
myisam_sort_buffer_size=134216704
myisam_stats_method=NULLS_UNEQUAL
myisam_use_mmap=OFF
mysql56_temporal_format=ON
net_buffer_length=16384
net_read_timeout=30
net_retry_count=10
net_write_timeout=60
old=OFF
old_alter_table=DEFAULT
old_mode=
old_passwords=OFF
open_files_limit=32186
optimizer_prune_level=1
optimizer_search_depth=62
optimizer_selectivity_sampling_limit=100
optimizer_switch=index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on
optimizer_use_condition_selectivity=1
performance_schema=OFF
performance_schema_accounts_size=-1
performance_schema_digests_size=-1
performance_schema_events_stages_history_long_size=-1
performance_schema_events_stages_history_size=-1
performance_schema_events_statements_history_long_size=-1
performance_schema_events_statements_history_size=-1
performance_schema_events_waits_history_long_size=-1
performance_schema_events_waits_history_size=-1
performance_schema_hosts_size=-1
performance_schema_max_cond_classes=80
performance_schema_max_cond_instances=-1
performance_schema_max_digest_length=1024
performance_schema_max_file_classes=50
performance_schema_max_file_handles=32768
performance_schema_max_file_instances=-1
performance_schema_max_mutex_classes=200
performance_schema_max_mutex_instances=-1
performance_schema_max_rwlock_classes=40
performance_schema_max_rwlock_instances=-1
performance_schema_max_socket_classes=10
performance_schema_max_socket_instances=-1
performance_schema_max_stage_classes=160
performance_schema_max_statement_classes=200
performance_schema_max_table_handles=-1
performance_schema_max_table_instances=-1
performance_schema_max_thread_classes=50
performance_schema_max_thread_instances=-1
performance_schema_session_connect_attrs_size=-1
performance_schema_setup_actors_size=100
performance_schema_setup_objects_size=100
performance_schema_users_size=-1
pid_file=/run/mysqld/mysqld.pid
plugin_dir=/usr/lib/x86_64-linux-gnu/mariadb19/plugin/
plugin_maturity=gamma
port=3306
preload_buffer_size=32768
profiling=OFF
profiling_history_size=15
progress_report_time=5
protocol_version=10
proxy_protocol_networks=
proxy_user=
pseudo_slave_mode=OFF
pseudo_thread_id=97113
query_alloc_block_size=16384
query_cache_limit=1048576
query_cache_min_res_unit=4096
query_cache_size=0
query_cache_strip_comments=OFF
query_cache_type=OFF
query_cache_wlock_invalidate=OFF
query_prealloc_size=24576
rand_seed1=158762979
rand_seed2=20392611
range_alloc_block_size=4096
read_binlog_speed_limit=0
read_buffer_size=131072
read_only=OFF
read_rnd_buffer_size=262144
relay_log=
relay_log_basename=
relay_log_index=
relay_log_info_file=relay-log.info
relay_log_purge=ON
relay_log_recovery=OFF
relay_log_space_limit=0
replicate_annotate_row_events=ON
replicate_do_db=
replicate_do_table=
replicate_events_marked_for_skip=REPLICATE
replicate_ignore_db=
replicate_ignore_table=
replicate_wild_do_table=
replicate_wild_ignore_table=
report_host=
report_password=
report_port=3306
report_user=
rowid_merge_buff_size=8388608
rpl_semi_sync_master_enabled=OFF
rpl_semi_sync_master_timeout=10000
rpl_semi_sync_master_trace_level=32
rpl_semi_sync_master_wait_no_slave=ON
rpl_semi_sync_master_wait_point=AFTER_COMMIT
rpl_semi_sync_slave_delay_master=OFF
rpl_semi_sync_slave_enabled=OFF
rpl_semi_sync_slave_kill_conn_timeout=5
rpl_semi_sync_slave_trace_level=32
secure_auth=ON
secure_file_priv=
secure_timestamp=NO
server_id=1
session_track_schema=ON
session_track_state_change=OFF
session_track_system_variables=autocommit,character_set_client,character_set_connection,character_set_results,time_zone
session_track_transaction_info=OFF
skip_external_locking=ON
skip_name_resolve=OFF
skip_networking=OFF
skip_parallel_replication=OFF
skip_replication=OFF
skip_show_database=OFF
slave_compressed_protocol=OFF
slave_ddl_exec_mode=IDEMPOTENT
slave_domain_parallel_threads=0
slave_exec_mode=STRICT
slave_load_tmpdir=/tmp
slave_max_allowed_packet=1073741824
slave_net_timeout=60
slave_parallel_max_queued=131072
slave_parallel_mode=conservative
slave_parallel_threads=0
slave_parallel_workers=0
slave_run_triggers_for_rbr=NO
slave_skip_errors=OFF
slave_sql_verify_checksum=ON
slave_transaction_retries=10
slave_transaction_retry_errors=1213,1205
slave_transaction_retry_interval=0
slave_type_conversions=
slow_launch_time=2
slow_query_log=OFF
slow_query_log_file=samson-slow.log
socket=/run/mysqld/mysqld.sock
sort_buffer_size=2097152
sql_auto_is_null=OFF
sql_big_selects=ON
sql_buffer_result=OFF
sql_log_bin=ON
sql_log_off=OFF
sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
sql_notes=ON
sql_quote_show_create=ON
sql_safe_updates=OFF
sql_select_limit=18446744073709551615
sql_slave_skip_counter=0
sql_warnings=OFF
ssl_ca=
ssl_capath=
ssl_cert=
ssl_cipher=
ssl_crl=
ssl_crlpath=
ssl_key=
standard_compliant_cte=ON
storage_engine=InnoDB
stored_program_cache=256
strict_password_validation=ON
sync_binlog=0
sync_frm=ON
sync_master_info=10000
sync_relay_log=10000
sync_relay_log_info=10000
system_time_zone=CET
system_versioning_alter_history=ERROR
system_versioning_asof=DEFAULT
table_definition_cache=400
table_open_cache=2000
table_open_cache_instances=8
tcp_keepalive_interval=0
tcp_keepalive_probes=0
tcp_keepalive_time=0
thread_cache_size=151
thread_concurrency=10
thread_handling=one-thread-per-connection
thread_pool_idle_timeout=60
thread_pool_max_threads=65536
thread_pool_oversubscribe=3
thread_pool_prio_kickup_timer=1000
thread_pool_priority=auto
thread_pool_size=4
thread_pool_stall_limit=500
thread_stack=299008
time_format=%H:%i:%s
time_zone=SYSTEM
timed_mutexes=OFF
timestamp=1646638900.010190
tmp_disk_table_size=18446744073709551615
tmp_memory_table_size=16777216
tmp_table_size=16777216
tmpdir=/tmp
transaction_alloc_block_size=8192
transaction_prealloc_size=4096
tx_isolation=REPEATABLE-READ
tx_read_only=OFF
unique_checks=ON
updatable_views_with_limit=YES
use_stat_tables=NEVER
userstat=OFF
version=10.3.32-MariaDB-0ubuntu0.20.04.1
version_comment=Ubuntu 20.04
version_compile_machine=x86_64
version_compile_os=debian-linux-gnu
version_malloc_library=system
version_source_revision=a2f147af35480e27bd599462db59b9b95f71acd9
version_ssl_library=YaSSL 2.4.4
wait_timeout=28800
warning_count=0
wsrep_osu_method=TOI
wsrep_auto_increment_control=ON
wsrep_causal_reads=OFF
wsrep_certification_rules=strict
wsrep_certify_nonpk=ON
wsrep_cluster_address=
wsrep_cluster_name=my_wsrep_cluster
wsrep_convert_lock_to_trx=OFF
wsrep_data_home_dir=/var/lib/mysql/
wsrep_dbug_option=
wsrep_debug=OFF
wsrep_desync=OFF
wsrep_dirty_reads=OFF
wsrep_drupal_282555_workaround=OFF
wsrep_forced_binlog_format=NONE
wsrep_gtid_domain_id=0
wsrep_gtid_mode=OFF
wsrep_load_data_splitting=ON
wsrep_log_conflicts=OFF
wsrep_max_ws_rows=0
wsrep_max_ws_size=2147483647
wsrep_mysql_replication_bundle=0
wsrep_node_address=
wsrep_node_incoming_address=AUTO
wsrep_node_name=samson.company.com
wsrep_notify_cmd=
wsrep_on=OFF
wsrep_patch_version=wsrep_25.24
wsrep_provider=none
wsrep_provider_options=
wsrep_recover=OFF
wsrep_reject_queries=NONE
wsrep_replicate_myisam=OFF
wsrep_restart_slave=OFF
wsrep_retry_autocommit=1
wsrep_slave_fk_checks=ON
wsrep_slave_uk_checks=OFF
wsrep_slave_threads=1
wsrep_sst_auth=
wsrep_sst_donor=
wsrep_sst_donor_rejects_queries=OFF
wsrep_sst_method=rsync
wsrep_sst_receive_address=AUTO
wsrep_start_position=00000000-0000-0000-0000-000000000000:-1
wsrep_sync_wait=0
SHOW ENGINE INNODB STATUS;
=====================================
2022-03-07 10:41:32 0x7f9b94576700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 45 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 92093 srv_active, 0 srv_shutdown, 73474 srv_idle
srv_master_thread log flush and writes: 165567
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 70036135
OS WAIT ARRAY INFO: signal count 41866295
RW-shared spins 6620911, rounds 67614112, OS waits 937873
RW-excl spins 763078, rounds 9241292, OS waits 212931
RW-sx spins 47981, rounds 864436, OS waits 19051
Spin rounds per wait: 10.21 RW-shared, 12.11 RW-excl, 18.02 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 310769191
Purge done for trx's n:o < 310769191 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421781198373368, not started
mysql tables in use 2, locked 0
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421781198369192, not started
mysql tables in use 1, locked 0
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421781198360840, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421781198352488, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
57840777 OS file reads, 85219586 OS file writes, 48638891 OS fsyncs
776.40 reads/s, 16384 avg bytes/read, 74.24 writes/s, 21.80 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 3093, seg size 3095, 1230040 merges
merged operations:
insert 361053, delete mark 3219714, delete 84980
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 9 buffer(s)
Hash table size 34679, node heap has 43 buffer(s)
Hash table size 34679, node heap has 230 buffer(s)
Hash table size 34679, node heap has 24 buffer(s)
Hash table size 34679, node heap has 20 buffer(s)
Hash table size 34679, node heap has 24 buffer(s)
Hash table size 34679, node heap has 218 buffer(s)
Hash table size 34679, node heap has 18 buffer(s)
280073.24 hash searches/s, 32402.72 non-hash searches/s
---
LOG
---
Log sequence number 634119972911
Log flushed up to 634119972851
Pages flushed up to 634119953306
Last checkpoint at 634119946466
0 pending log flushes, 0 pending chkp writes
41824871 log i/o's done, 5.13 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 170590208
Dictionary memory allocated 4822048
Buffer pool size 8192
Free buffers 1021
Database pages 6585
Old database pages 2411
Modified db pages 47
Percent of dirty pages(LRU & free pages): 0.618
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 41881503, not young 4455855002
173.44 youngs/s, 18000.40 non-youngs/s
Pages read 57821248, created 1104771, written 42475934
776.40 reads/s, 0.64 creates/s, 66.64 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 19 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 6585, unzip_LRU len: 0
I/O sum[43405]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=3623495, Main thread ID=140305681893120, state: sleeping
Number of rows inserted 749019, updated 1991576, deleted 39997045, read 8188912012
5.38 inserts/s, 1.82 updates/s, 2.53 deletes/s, 293414.08 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
Edit 1:
After another day of operation, the mysqld process is now at 1,6 GB (RES according to top command). More info as requested:
Edit 2:
Over night the memory usage went up from 1,6 GB to 1,8 GB. Here's one line that sticks out (higher max_memory_used than memory_used) from the output of
SELECT db,command,progress,memory_used,max_memory_used,examined_rows FROM INFORMATION_SCHEMA.PROCESSLIST order by max_memory_used desc limit 10;
db;command;progress;memory_used;max_memory_used;examined_rows
(NULL);Query;0;95384;4253032;0
But it seems this is just my process used for exactly that query. So that seems usual? Excluding my query for it, the full processlist only contains InnoDB purger workers, a purge coordinator and a shutdown handler when no one is accessing anything.
