Wp_options.ibd File is Growing Huge Everyday

Keywords: WordPress - AWS - Technical issue - Other
bnsupport ID: b46a371c-c1c3-c549-9609-f6c06275372b
Description:
Hi All, this is my first time post to this forum and ask question, hopefully is not break any forum’s rule.

This happen to me several time without any clue what cause them.
And is happen again on my live site.
This database file is growing, it’s 7 GB now.

I already do set expire time for log, but the real problem is on this file.

Here’s my BN Support tool code : b46a371c-c1c3-c549-9609-f6c06275372b

Thank youu…

Hi @nico1,

I just fond this post that may explain your issue:

https://stackoverflow.com/questions/45839537/mysql-tablename-ibd-file-size-is-huge

Do you know if you are constantly deleting rows from that database table?

Thanks for your reply. But the solution on that post is not solve my problem. The query have a warning “Unrecognized alter operation”. And when I proceed, MySQL returend an empty result.

Did you select the database before running the command?

After connecting to mysql, you have to change your database to the wordpress one:

use bitnami_wordpress

Then you should be able to execute the alter command properly:

ALTER TABLE wp_options FORCE;

I run the alter command from SQL query at PHPMyAdmin dashboard. Select the database and run SQL.

Ok! I see here that that warning might be a phpMyAdmin bug but nothing related to your issue.

Can you execute this SQL command to get your databases size?

SELECT table_schema "Schema_Name", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM   information_schema.tables GROUP  BY table_schema;

Can you also execute this other command to check the size of your mysql folder?

du -h /opt/bitnami/mysql/

Hi, here you go

Hi @nico1,

The output of the du -h /opt/bitnami/mysql/ command is incomplete. I wanted to see the final lines of the command as you can see the total space. However, I can see that the folder /opt/bitnami/mysql/data/bitnami_wordpress is 266 MB.

Also, as you can see from the SQL command, your bitnami_wordpress database is only 181 MB.

Where did you see that your wp_options.ibd file was 7 GB?

I’m sorry, I capture the wrong instance.

And right now my wp_options.ibd as big as 12GB.

Can you try what suggested in this post?

It seems that the optimize sql command may help

Cool!
I run this SQL command

OPTIMZE TABLE wp_options;

on phpMyAdmin.

And it works! From SSH we can see my wp_options.ibd reduce from 13GB to 18MB
BUT why on my phpMyAdmin interface my wp_options table size is still huge 11.5GB?

Maybe it take some time until the value is updated. Does still shown 11.5 GB?

Correct, it takes time to be updated.
But, the database is still growing. Optimze is for cleaning one time, not to stop the problem.
You can see it’s now 800+ MB.

now wp_options.ibd is 1.8GB.
increase around 500MB per day.

Is it possible that you have any plugin continuously writing data into the database? Can you take a look to the plugins you have installed and see if there is any suspicious one?

I’m not sure, and not sure how to check it.
I have several instance running similar website, but some of them is have this problem some is don’t.
I don’t know what and how something trigger this problem.

It’s now 2,3GB :frowning:

Anyone knows how to find the problem?

Hi @nico1,

I just found this old case in this same forum that might help you:

Basically, a user suggests the plugin Delete Expired Transients. Please take a look at the plugin description as it contains an explanation about the database size.

Done that but still no luck. I do several ways to delete transient data, the size is still big.
The only way I succesfully decrease the size is by OPTIMIZE command, but it still growing everyday.

Is there any way to know the log what, who and when some data is written to database?

Hi @nico1,

Yes, it is possible. You can either check the events from the binary log or enable the general log. However, take into account that the second option is very verbose and the log file will grow fast. You could enable it for a few minutes and then disable it again.

Please check the official MariaDB documentation to learn about these logging methods.

There’s a binlog file at /opt/bitnami/mysql/data with name binlog.000103 for example
Can I use this log to analyze or I need different log?