MYSQL storage increasing unexpectedly (RDS)

Question

I am total newbie to database game. & I am using mysql 8.0 RDS 20GB free tier.

I don't have much data on it.(just 300 Mb) (wordpress running on ec2 is linked with this rds)

But I always get a mail that you have exceeded 85% of storage limit.(approx 17Gb)

Also Under monitoring tab in rds panel it shows:- Free Storage Space (MB) = 19.330Gb (Constantly without any drop)

I have turned off : general log, slow logs, binlog,

i have done these parameter changes:

binlog_expire_logs_seconds = 86400 general_log = 0 innodb_autoextend_increment = 1 innodb_buffer_pool_size = 5000000000 log_bin_trust_function_creators = 0 log_output = NONE max_binlog_cache_size = 5368709120 max_connections = 1000 slow_query_log = 0 innodb_file_per_table = 1

SELECT name, count FROM information_schema.innodb_metrics ORDER BY count;

Highest counts seen on: buffer_data_written = 6936939008 buffer_pool_size = 402653184 os_log_bytes_written = 313484800 buffer_pool_bytes_data = 204668928

Can someone please help me whats consuming these storage. Is this something to do with INNODB?

Have you checked the error logs? Have you tried taking a dump of the DB and having a look see inside? What have you tried to do? — Uberhumus
– Uberhumus, Commented May 30, 2020 at 6:34
i have done these parameter changes,(binlog_expire_logs_seconds = 86400 / general_log = 0 / innodb_autoextend_increment = 1 / innodb_buffer_pool_size = 5000000000 / log_bin_trust_function_creators = 0 / log_output = NONE / max_binlog_cache_size = 5368709120 / max_connections = 1000 / slow_query_log = 0 / innodb_file_per_table = 1) — NewGeek
– NewGeek, Commented May 30, 2020 at 7:11
The information in these comments could help people help you. Please add it to the question. — Uberhumus
– Uberhumus, Commented May 30, 2020 at 13:27

Rick James · Accepted Answer · 2020-06-01 19:41:10Z

Ouch! innodb_buffer_pool_size = 5000000000 inside 1GB of RAM! This leads to swapping, which uses disk space and slows down MySQL terribly.

Don't set that about, say, 250M. Even that may be too high. Do you have other things running in the db.t2.micro?

max_connections = 1000 -- also too high. Set it to 20

If you want to change anything else, get some advice first. The general advice is: 1gb of RAM is very limited; raising tunables can lead to bad results.

I have edited innodb_buffer_pool_size = 100mb max_connection = 20 Thankyou for responding to the question what else should i change, @WilsonHauck I will soon post required info on pastebin
@NewGeek Analysis will begin after you post data requested on June 1, 2020. Thanks
@NewGeek - did the links go away? I'll review the SHOWs, too.

Collectives™ on Stack Overflow

MYSQL storage increasing unexpectedly (RDS)

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related