I am having an issue with MySQL gradually consuming more memory every day which seems to have coincided with converting some tables from MyISAM to InnoDB two weeks ago. You can see the trend in memory usage .
I am running a server with 4GB RAM and a LAMP stack. My web site traffic hasn't changed much over this time. The traffic is very seasonal though and typical %MEM for mysqld in previous years at this time was in the 15-20% range.
Also of note is that I upgraded to MySQL 8.0 in November (prior to the memory trace shown in the link). I guess I thought the upgrade and conversion of most tables to InnoDB would help performance. And performance is fine, but this memory increase is troubling and I assume it will eventually degrade performance.
Here if my my.cnf:
[mysqld] disable-log-bin=1 default-authentication-plugin=mysql_native_password default-storage-engine=innodb innodb_file_per_table=1 innodb_buffer_pool_size=1024M innodb_log_file_size=128M max_connections=500 # query_cache_type=2 skip_name_resolve performance_schema=off key_buffer_size = 300M max_allowed_packet=128M open_files_limit=40000 local_infile=on
Any guidance would be appreciated!
Update (Jan 1 2021): I made the following changes...
- Converted remaining tables to InnoDB
- Made data types/collation consistent across all tables
- changed innodb_buffer_pool_size 1024M -> 1280M
- changed key_buffer_size 300M -> 10M
- turned performance schema on
I restarted mysql about 12 hours ago and memory usage is already at where it was before the restart and is continuing to increase linearly with time. CPU usage was always pretty low, but it's even marginally lower with the improvement in join efficiency. I am puzzled.
One thing I noticed is that I set the buffer pool to 1280M, but Innodb_buffer_pool_pages_total is shown as 131072 which means the the buffer pool size is actual 2GB. Can I not set the pool to a value between 1GB and 2GB?