I am running a MySQL database on my computer (in Docker) as part of a local development environment. Since this is far from a production instance, it is basically just using default settings and hasn't had any real adjustments to the config.
The size of the Docker volume for the database is about 450MB, however when I start the server it immediately begins to use up an extremely high amount of memory. Within seconds it will use 14+ GB of memory, filling up physical RAM, then begins consuming swap space. It takes about 2.5 minutes to go from initial start to being ready for connections. During that time, it cycles between filling up RAM, then usage dropping, filling up again, and so on. Once the server has actually started and is ready for connections, it stabilizes with about 16GB of memory usage.
I have no idea what has changed, it used to only take less than a second to start, and I don't think it used to eat this much memory. I haven't made any significant changes to the database or the data contained within.
I tried searching around for ways to reduce memory, and so I've made a few small config tweaks, but they don't appear to have made any significant difference. This is the current start up command:
mysqld --sql-mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION" --innodb-flush-log-at-trx-commit=2 --innodb-flush-log-at-timeout=1 --performance_schema=0 --table-definition-cache=400 --table-open-cache=10 --thread-cache-size=8 --host_cache_size=128
The performance_schema and everything after that are options I've tried adding based on other answers I've found on StackExchange; the values are the minimums based on the MySQL docs and don't have any significance beyond that. Pretty much everything else is the default that ships with the official MySQL docker image.
Info:
MySQL Version: 5.7.40
Start up log (notice the amount of time within the first 5 lines):
db | 2023-03-14 20:06:11+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.40-1.el7 started.
// [removed unrelated logs]
db | 2023-03-14 20:07:18+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
db | 2023-03-14 20:07:18+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.40-1.el7 started.
db | '/var/lib/mysql/mysql.sock' -> '/var/run/mysqld/mysqld.sock'
db | 2023-03-14T20:08:40.679295Z 0 [ERROR] --verbose is for use with --help; did you mean --log-error-verbosity?
db | 2023-03-14T20:08:40.682960Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
db | 2023-03-14T20:08:40.684946Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
db | 2023-03-14T20:08:40.693672Z 0 [Note] mysqld (mysqld 5.7.40) starting as process 1 ...
db | 2023-03-14T20:08:40.755140Z 0 [Note] InnoDB: PUNCH HOLE support available
db | 2023-03-14T20:08:40.755755Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
db | 2023-03-14T20:08:40.755763Z 0 [Note] InnoDB: Uses event mutexes
db | 2023-03-14T20:08:40.755769Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
db | 2023-03-14T20:08:40.755773Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.12
db | 2023-03-14T20:08:40.755776Z 0 [Note] InnoDB: Using Linux native AIO
db | 2023-03-14T20:08:40.759080Z 0 [Note] InnoDB: Number of pools: 1
db | 2023-03-14T20:08:40.762827Z 0 [Note] InnoDB: Using CPU crc32 instructions
db | 2023-03-14T20:08:40.771104Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
db | 2023-03-14T20:08:40.781648Z 0 [Note] InnoDB: Completed initialization of buffer pool
db | 2023-03-14T20:08:40.783626Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
db | 2023-03-14T20:08:40.808146Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
db | 2023-03-14T20:08:40.926282Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
db | 2023-03-14T20:08:40.926380Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
db | 2023-03-14T20:08:40.965038Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
db | 2023-03-14T20:08:40.965902Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
db | 2023-03-14T20:08:40.965912Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
db | 2023-03-14T20:08:40.966292Z 0 [Note] InnoDB: Waiting for purge to start
db | 2023-03-14T20:08:41.016515Z 0 [Note] InnoDB: 5.7.40 started; log sequence number 54730465205
db | 2023-03-14T20:08:41.016820Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
db | 2023-03-14T20:08:41.017961Z 0 [Note] Plugin 'FEDERATED' is disabled.
db | 2023-03-14T20:08:41.039051Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
db | 2023-03-14T20:08:41.039071Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
db | 2023-03-14T20:08:41.039077Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
db | 2023-03-14T20:08:41.039080Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
db | 2023-03-14T20:08:41.041316Z 0 [Warning] CA certificate ca.pem is self signed.
db | 2023-03-14T20:08:41.041414Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
db | 2023-03-14T20:08:41.045509Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
db | 2023-03-14T20:08:41.046427Z 0 [Note] IPv6 is available.
db | 2023-03-14T20:08:41.047102Z 0 [Note] - '::' resolves to '::';
db | 2023-03-14T20:08:41.047116Z 0 [Note] Server socket created on IP: '::'.
db | 2023-03-14T20:08:41.050482Z 0 [Warning] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
db | 2023-03-14T20:08:41.059038Z 0 [Note] InnoDB: Buffer pool(s) load completed at 230314 20:08:41
db | 2023-03-14T20:08:41.125479Z 0 [Note] Event Scheduler: Loaded 0 events
db | 2023-03-14T20:08:41.125709Z 0 [Note] mysqld: ready for connections.
db | Version: '5.7.40' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server (GPL)
Dumbing all variables seemed like too much, so here's the size-related config variables:
binlog_cache_size............................................ 32768
binlog_stmt_cache_size....................................... 32768
binlog_transaction_dependency_history_size................... 25000
bulk_insert_buffer_size...................................... 8388608
delayed_queue_size........................................... 1000
host_cache_size.............................................. 128
innodb_buffer_pool_chunk_size................................ 134217728
innodb_buffer_pool_size...................................... 134217728
innodb_change_buffer_max_size................................ 25
innodb_ft_cache_size......................................... 8000000
innodb_ft_max_token_size..................................... 84
innodb_ft_min_token_size..................................... 3
innodb_ft_total_cache_size................................... 640000000
innodb_log_buffer_size....................................... 16777216
innodb_log_file_size......................................... 50331648
innodb_log_write_ahead_size.................................. 8192
innodb_max_undo_log_size..................................... 1073741824
innodb_online_alter_log_max_size............................. 134217728
innodb_page_size............................................. 16384
innodb_purge_batch_size...................................... 300
innodb_sort_buffer_size...................................... 1048576
innodb_sync_array_size....................................... 1
join_buffer_size............................................. 262144
key_buffer_size.............................................. 8388608
key_cache_block_size......................................... 1024
large_page_size.............................................. 0
max_binlog_cache_size........................................ 18446744073709547520
max_binlog_size.............................................. 1073741824
max_binlog_stmt_cache_size................................... 18446744073709547520
max_heap_table_size.......................................... 16777216
max_join_size................................................ 18446744073709551615
max_relay_log_size........................................... 0
metadata_locks_cache_size.................................... 1024
myisam_data_pointer_size..................................... 6
myisam_max_sort_file_size.................................... 9223372036853727232
myisam_mmap_size............................................. 18446744073709551615
myisam_sort_buffer_size...................................... 8388608
ngram_token_size............................................. 2
optimizer_trace_max_mem_size................................. 16384
parser_max_mem_size.......................................... 18446744073709551615
performance_schema_accounts_size............................. 0
performance_schema_digests_size.............................. 0
performance_schema_events_stages_history_long_size........... 0
performance_schema_events_stages_history_size................ 0
performance_schema_events_statements_history_long_size....... 0
performance_schema_events_statements_history_size............ 0
performance_schema_events_transactions_history_long_size..... 0
performance_schema_events_transactions_history_size.......... 0
performance_schema_events_waits_history_long_size............ 0
performance_schema_events_waits_history_size................. 0
performance_schema_hosts_size................................ 0
performance_schema_session_connect_attrs_size................ 0
performance_schema_setup_actors_size......................... 0
performance_schema_setup_objects_size........................ 0
performance_schema_users_size................................ 0
preload_buffer_size.......................................... 32768
profiling_history_size....................................... 15
query_alloc_block_size....................................... 8192
query_cache_size............................................. 1048576
query_prealloc_size.......................................... 8192
range_alloc_block_size....................................... 4096
range_optimizer_max_mem_size................................. 8388608
read_buffer_size............................................. 131072
read_rnd_buffer_size......................................... 262144
slave_pending_jobs_size_max.................................. 16777216
sort_buffer_size............................................. 262144
thread_cache_size............................................ 8
tmp_table_size............................................... 16777216
transaction_alloc_block_size................................. 8192
transaction_prealloc_size.................................... 4096
Additional debug info:
Here's a picture of the memory usage as the server begins starting up. The moment I start MySQL is the fairly obvious jump in resource usage on the left of all three graphs.
Memory usage once the server has finished start up and is ready for connections. The point when the graphs stabilize toward the left corresponds with the logs starting at 2023-03-14T20:08:40, right when the server is finally ready.
