MySQL Memory is too high

MySQL & MariaDB Other MySQL® Questions
1

I’m using the DELETE script periodically in MYSQL. The problem is that during the DELETE operation, the memory in the DB is going up and the memory usage keeps going up. Already, the Innodb Buffer pool usage is 99%.
What’s the reason? Or is there something I should look for?

2

This is normal. You configure innodb_buffer_pool_size to be a specific size. It will grow to use that memory, but not use more than configured. You must tune this setting to not use all memory in the server.

How many rows per DELETE? 1M? 10M? You should make smaller batches to keep resource utilization low. Make sure the DELETE queries are optimized too.

3

my server spec 16core 32GB

  • Innodb_buffe_pool_size : 16GB
  • Delete : 500 in a minute ( 09:00~18:00 )

The problem here is that the memory increases during the deletion batch
and the increased memory does not decrease after the end of the batch.

4

Yes, that is correct behavior. The buffer pool does not grow/shrink. It allocates a specific size and will use up to 100% of that size. It will not decrease. Why do you think need the memory needs to decrease?

5

I know the buffer pool doesn’t diminish. My situation is
already Innodb_buffer_pool Usage : 99% ( Workbench Check )
Delete batch is in progress, increasing memory usage each time a delete batch is performed (10 to 18)
The memory is not increasing or decreasing after the delete deployment is finished (18-09)

7

Make sure you are running COMMIT on each batch and closing the connection to MySQL between each batch. This forces the thread to free any associated memory with the batch connection. Also, switch the batch to READ-COMMITTED to reduce the number of undo pages needed.

8

I’m already read_commissioned and I’m using Autocommit.
The contents of my BASH script are as follows.

  1. Check Lock session
  2. Thread Running < 4 (check session longer than 4 seconds)
  3. DELETE FROM WHER PK CONDITION;
9

That all seems fine. I’m still not understanding the problem.

Is this the RSS of MySQL? Can you show some graphs with memory usage indicating the problem?

10

image
image1666×428 69.7 KB

This is a two-day graph. The batch starts at 09:00 and ends at 18:00, memory usage does not increase when the batch is not executed,
but when the batch is executed, memory usage increase and does not decrease;

11

You are closing all connections between each batch?
Does MySQL get killed via OOM?
What version of MySQL?

12
  1. Connection Close
  • Yes, Batch connection closed each batch
  1. MySQL Version
  • MYSQL Version 8.0.19
  1. Mysql Down?
  • MySQL doesn’t killed yet
13

8.0.19 is very old. Can you test with a more updated version? 8.0.32 at minimum. Also, are you using jemalloc or tcmalloc?

14

i using default maybe glibc ( malloc )