Wp_options.ibd File is Growing Huge Everyday

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?

Yes, you need to connect to your database and execute the following:

  1. In order to show the available binary logs:
mysql> SHOW BINARY LOGS;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |    594771 | No        |
| binlog.000002 |       178 | No        |
| binlog.000003 |       636 | No        |
| binlog.000004 |     97125 | No        |
+---------------+-----------+-----------+
4 rows in set (0.00 sec)
  1. In order to show the events from a particular log
mysql> SHOW BINLOG EVENTS IN 'binlog.000003';
+---------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                                                                                                             |
+---------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------+
| binlog.000003 |   4 | Format_desc    |         1 |         124 | Server ver: 8.0.17, Binlog ver: 4                                                                                                |
| binlog.000003 | 124 | Previous_gtids |         1 |         155 |                                                                                                                                  |
| binlog.000003 | 155 | Anonymous_Gtid |         1 |         234 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                             |
| binlog.000003 | 234 | Query          |         1 |         445 | ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*40C990A6957E559FDAC9C1DBD4FC7BA80E0A3C72' /* xid=2 */ |
| binlog.000003 | 445 | Anonymous_Gtid |         1 |         522 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                             |
| binlog.000003 | 522 | Query          |         1 |         613 | FLUSH PRIVILEGES;                                                                                                                |
| binlog.000003 | 613 | Stop           |         1 |         636 |                                                                                                                                  |
+---------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)

I still can’t find what and how the database table is growing everyday. :frowning:
Don’t how to find with that log

Hi @nico1, we have found similar issues in the past. For instance in this topic: Mysql unable to start due to full space

In summary, some plugins misbehave and bloat the wp_options table with useless data.

I would recommend you to execute these MySQL commands to identify the problematic row (which will let us know which plugin is causing the issue):

OPTIMIZE TABLE wp_options;
FLUSH QUERY CACHE;
SELECT COUNT(*) FROM wp_options;
SELECT option_name, LENGTH(option_id) + LENGTH(option_name) + LENGTH(option_value) + LENGTH(autoload) AS total_length FROM wp_options ORDER BY total_length DESC LIMIT 10;

Please share the results of the commands with us. The 3rd row will show the amount of rows in wp_options, and the 4th the number of characters for the biggest rows. If a row has a very high amount of characters (i.e. >1M), it could be the problematic one.

If you search its option_name in Google and find it related to any of the plugin you have enabled, disable it (as it is misbehaving) and report the issue to the plugin author. Only then can you run the command below:

DELETE FROM wp_options WHERE option_id = ID;

But be very careful with that command not to remove the wrong option id.

Thanks @marcos.

here the result
Select count and get 6071 value.

And for the fourth row:

Hi @nico1, it doesn’t seem like you have huge rows in MySQL. In the other case we found a row with more than a million characters, but only about 1000 rows in total. We do see you have many rows though, but that shouldn’t be problematic.

Could you run the Bitnami Support tool again? How to Run the Bitnami Support Tool (note that you need to paste the code ID that is shown at the end)

After that execute the commands below in your phpMyAdmin:

SELECT COUNT(*) FROM wp_options;
SELECT option_name, LENGTH(option_id) + LENGTH(option_name) + LENGTH(option_value) + LENGTH(autoload) AS total_length FROM wp_options ORDER BY total_length DESC LIMIT 10;

That way we can also find the amount of free disk space you currently have together with table sizes on disk, as it doesn’t seem like the wp_options table is very big right now. Don’t delete any data until executed, we just want to check the current status of your system.

Here’s my code ID from support tool:
0cd79c42-b24d-d468-2f53-2024183e59f0

I don’t know about if it can be determined as big or not.
But, everyday my wp_options table is growing constantly every day, about 500MB.

Right now on my staging server, I got 600+MB
Yesterday only 40MB something after I run optimize command, from 6GB big.

Hi @nico1

It looks like the transient feeds are the one that make your database to grow exponentially. In the blog below, they suggest using the Advance Database Cleaner plugin to solve the issue. Could you give it a try?

https://sigmaplugin.com/blog/what-are-wordpress-dashboard-transient-feeds-and-how-to-clean-them

I’m pretty sure that the reason behind this behaviour is some plugin and you have so many (I checked your /opt/bitnami/apps/wordpress/htdocs/wp-content/plugins directory using the support code and there are dozens of them). I highly recommend you to decrease the number of plugins you’re using and deactivate/remove all the ones you’re not using. The less plugins you use, the more secure your website is.

Best regards,

Juan ariza

Deleting transient data is not make any difference on my case.
Already do several ways of deleting transients data with several plugin.
And it’s not decreasing the size of my wp_options table. And if this help to decrease the table size, the table size will keep growing.

Still don’t know what triggered it.
Maybe it’s because one of my plugin, but it’s hard to find the problem.

I will try delete all inactive plugin on my staging instance.

Hello nico1, I’m having a similar issue to the one you describe. Were you able to identify a culprit plugin?

Hi @nico1,
Did you find the misbehaving plugin ?

Unfortunately I can’t find the culprit. I still need to periodically delete the data by using OPTIMIZE command on SQL.

Hi @nico1,
You mentioned a staging server. Couldn’t you, in that environment, disable all the plugins and start enabling them one by one until you find the culprit ? (or the other way around, disabling them one by one).

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.