3

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?

10
  • I cannot provide you with a full, proper answer, but you should reduce your key buffer size if no longer using myisam and checking memory allocated to the performance schema. – jynus Dec 29 '20 at 9:55
  • Thanks for the tip on performance schema. I'll turn that on. I plan to reduce key_buffer_size, but I still have a few MyISAM tables whose keys take up about 200MB. I do plan to switch them over to InnoDB once I get a handle on the memory issues. – Ken P Dec 30 '20 at 7:39
  • Then the issue is mostly that using 2 separate engines heavily is likely to cause more memory needs, as tou have 2 "database backends" running in parallel. Note I mentioned P_S as a way to save memory if disabled, it is a very useful tool, but memory hunggry. You can use it where memory is being allocated and tune it to save more. – jynus Dec 30 '20 at 21:49
  • Makes sense that using the 2 engines is suboptimal. It is just hard to understand how memory consumption in MySQL 8 is twice (and continuing to increase) from when I was running 5.6 despite little change in the underlying database structure and incoming requests. – Ken P Dec 30 '20 at 23:26
  • How much RAM? How big is your dataset? – Rick James Dec 31 '20 at 8:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.