Database increased in size 200GB+ in 6 hours

Keywords: WordPress Multisite - AWS - Technical issue - Other
bnsupport ID: ebb054f4-ba13-eb02-91ff-c74c3d4d507f
Description:
This is the code from the support tool.

ebb054f4-ba13-eb02-91ff-c74c3d4d507f

Last Friday, 11/22/19, my bitnami WP shut down unexpectedly. All services stopped running and would not come back online. After further investigation I found the disk was 100% full. I tried to increase my disk space and then start the services again. PHP and Apache would start but MySQL failed every time. After several hours of trying to get MySQL running, I finally had to launch a new instance with a snapshot from the prior day.

After I launched the new instance I checked the size of the mysql directory, it was 8GB. Compared to the mysql directory on the instance that had shut down, I was able to determine the mysql directory had grown from 8GB to approximately 238GB in less than 12 hours.

I am trying to find out what caused my DB to increase 230GB in size. Can you help me troubleshoot this problem? One thing I have found in the instance that grew in size so fast is there are 157 binlog files, each are 1.1GB in size.

Hi @scott13,

The MySQL’s binary logging is the problem.

mysql/data:
total 164787076
drwxr-x---  7 mysql root       12288 Nov 25 00:44 .
drwxr-xr-x 11 root  root        4096 Nov 25 00:51 ..
-rw-r-----  1 mysql root          56 Apr  9  2019 auto.cnf
-rw-r-----  1 mysql mysql 1073779017 Nov 15 17:50 binlog.000025
-rw-r-----  1 mysql mysql 1073751295 Nov 18 08:47 binlog.000026
-rw-r-----  1 mysql mysql 1073741903 Nov 19 21:57 binlog.000027 

It helps you recover the database in case of a huge crash but you can also disable it. Can you check this other community thread in which we helped the user to disable it and remove the data.

Happy to help!


Was my answer helpful? Click on :heart:

Hey @jota,

I will look at that thread. In the meantime I have noticed the DB on my new instance is starting to grow rapidly also. I have opened up the mysql/data folder and am looking at the individual .idb files. wp_usermeta is now 30g and wp_2_options is 5.2g table which seems extremely large for those tables. I only have about 150 registered users on this site. I have opened the usermeta table through phpmyadmin but I can’t find anything out of the ordinary about that table. I’m not sure how the .ibd file can be that large when there’s nothing out of the ordinary in the table. Here is a support key on the new instance through your support tool.

77d7df06-0a27-fa44-9ddb-3893203f6e3f

I’ll post back after I review the support article you suggested.

Scott

@jota,

Ok I have looked through the support link you sent. Disabling bin_logs will clear up approximately 60g, but I don’t see how that will solve the problem of the options and usermeta table growing so large so fast. As an example, since the time I posted my first reply to when I started typing this one, approximately 20 minutes, wp_2_options has grown another 100mb.

Here are the results from my instance on the commands you asked the user on the other ticket to run.

df -ih
Filesystem Inodes IUsed IFree IUse% Mounted on
udev 984K 324 983K 1% /dev
tmpfs 986K 420 985K 1% /run
/dev/nvme0n1p1 62M 446K 61M 1% /
tmpfs 986K 1 986K 1% /dev/shm
tmpfs 986K 3 986K 1% /run/lock
tmpfs 986K 16 986K 1% /sys/fs/cgroup
/dev/loop1 15 15 0 100% /snap/amazon-ssm-agent/1455
/dev/loop0 15 15 0 100% /snap/amazon-ssm-agent/1480
/dev/loop2 13K 13K 0 100% /snap/core/8039
/dev/loop3 13K 13K 0 100% /snap/core/7917
tmpfs 986K 4 986K 1% /run/user/1000

df -h
udev 3.9G 0 3.9G 0% /dev
tmpfs 789M 17M 772M 3% /run
/dev/nvme0n1p1 485G 105G 380G 22% /
tmpfs 3.9G 0 3.9G 0% /dev/shm
tmpfs 5.0M 0 5.0M 0% /run/lock
tmpfs 3.9G 0 3.9G 0% /sys/fs/cgroup
/dev/loop1 18M 18M 0 100% /snap/amazon-ssm-agent/1455
/dev/loop0 18M 18M 0 100% /snap/amazon-ssm-agent/1480
/dev/loop2 90M 90M 0 100% /snap/core/8039
/dev/loop3 90M 90M 0 100% /snap/core/7917
tmpfs 789M 0 789M 0% /run/user/1000

find . -type f | cut -d “/” -f 2 | sort | uniq -c | sort -n
1 bnsupport-regex.ini
1 changelog.txt
1 ctlscript.sh
1 img
1 manager-linux-x64.run
1 properties.ini
1 README.txt
1 use_wordpressmultisite
3 bnsupport
6 sqlite
6 stats
8 config
8 letsencrypt
21 var
23 scripts
74 licenses
90 varnish
568 mysql
859 php
4342 common
5431 apache2
94405 apps

du -h -d 1
34M ./letsencrypt
172M ./apache2
3.3M ./sqlite
80M ./bnsupport
2.8M ./varnish
3.0M ./stats
120K ./var
116K ./scripts
115M ./common
1.2M ./licenses
52K ./config
79M ./php
1.8G ./apps
100G ./mysql
8.0K ./img
102G .

Scott

Hi @scott13,

It looks like you are using an old version of the bnsupport tool. Can you follow the guide below to download, install, and run the latest version? Please paste the new code here so we can check it.

https://docs.bitnami.com/installer/how-to/understand-bnsupport/#linux

Apart from that, this behaviour is usually caused by plugins using the database as a temporal data storage and not cleaning it up after. Are you using any plugin on your website? Can you check if any of them may be causing this issue? To do so, you can disable all plugins, start them one by one and check if the size of your database folder is increased after a period of time running your server with that plugin enabled?

Regards,
Gonzalo

Ok I’ll get that support tool updated and supply you with the new code. I’ll set up a new stage environment to test the plugins. My current stage environment is not having this problem it is specific to the production setup. The two environments are very close to the same but not exact. I’ll get that resolved today.

I’ve looked at the two tables that are growing and causing this problem but there are no “odd” records in either of them. Is there another way to troubleshoot those tables to see what’s going on rather than just viewing them through phpMyAdmin?

Thanks,

Scott

@gongomgra

Here is the code after I updated the support tool.

fab54d07-7f55-df62-6141-674c0f4eda13

I am still working on local stage test of the plugins. I have not found any so far that appear to be causing the problem. But the biggest issue I have is the size of these .ibd files is not represented by data in the table that is viewable through phpMyAdmin. I need to find a better way to analyze that data and see if I can determine how the data was written and possible by what process.

Thanks,

Scott

Hi @scott13,

Thanks for the new bnsupport code. We highly recommend you to create a server backup before doing any modification so you can safely restore it if anything goes wrong

https://docs.bitnami.com/aws/faq/administration/backup-restore-server/

By default, we configure MySQL to keep binlog files for 7 days with the property expire_logs_days=7 in /opt/bitnami/mysql/my.cnf. Setting that key, it instructs MySQL to remove binlogs files older than 7 days. You can decrease this number to keep less binlog files (eating less disk space) and restart MySQL for changes to take effect.

sudo /opt/bitnami/ctlscript.sh restart mysql

However, this won’t fix the root issue of MySQL generating that big files. Can you check the events registered in the binlog files to get more information of what is causing this? I found a guide about inspecting MySQL binlog files

https://www.thegeekstuff.com/2017/08/mysqlbinlog-examples/

Regards,
Gonzalo

@gongomgra

I have a stage site set up and I’ve got an automated process creating snapshots of the site every day. I’ll do some research on the binlog files and see what I can find. My concern at this point is if the logs are removed after 7 days the logs containing data from when everything went wrong may not still be in place. The size of the DB has finally settled and it’s not continuing to grow every day like it was before. That’s good but it might limit the research I can do.

I’ll post results today of what I find.

Thanks,

Scott

Hi @scott13,

Unfortunately, if your issue happened more than a week ago, the binlog file should have been already removed. Let us know if you find anything checking the current files.

Regards,
Gonzalo

@gongomgra,

I have not found any relevant information yet, but I did find where 51 binlog files were created on the same day. I made local copies of 20 of those files yesterday before they were deleted over night. I’m making an assumption that whatever occurred to cause 51 binlog files to be created on the same day will be evident in the 20 files I have local copies of. I have looked over the article you sent me to review and found there is some useful information that is helping, but nothing there has helped me find the underlying cause. The most helpful thing I found was using -s with the mysqlbinlog command to only see the queries that were run. I expected to see INSERT or UPDATE queries but that has not been the case so far.

I will keep researching this and post updates as I find more information.

Thanks,

Scott

Hi @scott13,
I am glad you found relevant information, let us know how this investigation progresses.
Also, did you finally discard that the issue could be related to a plugin?

@miguelaeh,

I have not discarded that this could be related to a plugin, that is actually what I think has caused the problem. But I have not had any luck yet pinpointing which plugin might be causing the problem. I have restored a new copy of the site to a local staging server today to continue troubleshooting. The DB increased 250gb+ over this past weekend.

I’ll keep posting information here as I find out more. I am trying to find some tool or maybe even a paid utility that will help me analyze the data. What’s is still so odd about this is the data that’s causing the MySQL tables to be so large is not being stored in the tables. Or at least it’s not being stored in a manner that makes it apparent it is causing the problem.

My wp_usermeta table is now 135gb. But there are only 3,200 records in the table. So unless there is a field that was used to store some mega data for something, I can’t imagine how 3,200 records could ever lead to a 135gb table size.

Thanks,
Scott

@miguelaeh,

Here is something odd I just noticed, which might help explain what the problem is. The binlog files on my production site are clearly an issue, I identified this in the early part of this thread. The DB growth over this weekend was accompanied with approximately 200 new binlog files, all created on 12/6 and 12/7, and they are all 1.1gb in size.

When I exported my site and imported it into a new stage site I set up for debugging, the export process only copied over 3 of those binlog files. When I look at the size of the two tables in my production DB that the most oversized, wp_options (8gb) and wp_usermeta (135gb), those tables in the stage site are 11M and 9M respectively.

I wonder if the binlogs are storing data in some temporary format in those tables? Again there is no odd data in the tables when I look at them through phpMyAdmin. But I wonder if they are storing data in some transient state? I’m still trying to figure out how to determine if this is the case or not.

Thanks,
Scott

@miguelaeh,

/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1575689035596835 (2019-12-07 03:23:55.596835 UTC)
# immediate_commit_timestamp=1575689035596835 (2019-12-07 03:23:55.596835 UTC)
/*!80001 SET @@session.original_commit_timestamp=1575689035596835*//*!*/;
/*!80014 SET @@session.original_server_version=80015*//*!*/;
/*!80014 SET @@session.immediate_server_version=80015*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
SET TIMESTAMP=1575689035/*!*/;
BEGIN
/*!*/;
COMMIT/*!*/;

That entry is in my binlog files thousands of times. Any idea what it means?

Thanks,
Scott

@miguelaeh,

To update the number of times that entry showed up in the binlog files I did a grep count on “SET @@SESSION.GTID_NEXT=‘ANONYMOUS’” and it was found 994,343. I think I have found the problem, but I still have yet to find out what’s causing this to run. I’m still researching the issue.

Thanks,
Scott

@miguelaeh,

Ok I have deleted all of my binlog files except the most current. That change remove 210gb from my disk usage. It did not reduce the size of the two tables that have grown so big, wp_options and wp_usermeta. Those tables are still 8gb and 135gb respectively.

I found some queries to run that will give the size of each table column in MB. The results of that query is even more confusing. Here are some screenshots.

These are the column sizes for wp_usermeta. If I add these all up the total size is just over 1 MB.

But if you compare that to this screenshot of the table structure from phpMyAdmin, you will see the total DB size is 135gb. The major difference is the disparity between “Data” and “Index”. I am still trying to determine what would be causing this or where that data is being stored.

Same thing with wp_2_options table and columns.

Thanks,
Scott

Hi @scott13,

Thanks for your exhaustive investigation. Can you check with the WordPress developer in their forum in case they can give you more information about these SQL queries?

https://wordpress.org/support/view/all-topics/

Apart from that, can you try disabling some plugins to see if it stops generating all these binlogs files so you can identify the culprit?

Regards,
Gonzalo

@gongomgra,

I will post there and see if I can get any additional information. Yesterday I systematically updated all of the plugins on the stage site and once I confirmed the updates did not cause any conflicts or errors, I made those same updates in production. I am monitoring both environments to see if the stage site goes haywire like the production site did. There’s a chance, if this was a plugin issue, the update may resolve the problem. If I see it happen again I am going to start disabling plugins one at a time and see if I can pinpoint the issue. I am going to target plugins that normally interact with wp_usermeta in hopes I will find the culprit sooner rather than later. Otherwise it could take several weeks before I find which one is causing the problem.

Thanks,
Scott

Hi @scott13,

Thanks for the info. Let us know if the plugins update fixed the issue or if you find anything else.

Regards,
Gonzalo