WordPress DB went too large
It’s my first time dealing with this kind of problem. The situation was the entire 160GB of server storage was totally used up by MySQL database files, and due to insufficient storage restart MySQL was also failed. Finally, I found the problem is caused by wp_options and binary log of MySQL.
Based on the situation, there are two major things that you have to check.
- The size of binary log in MySQL.
- Size of table “
wp_options” in WordPress database.
That was totally a disaster. 🙁
MySQL binary log.
If you just ignored it, the binary log will become super large garbage in your server.
Check binary logs.
You could use this MySQL command to see how many binary logs were stored in server and its size.
|
1 |
SHOW BINARY LOGS; |
There is another way to find out how much disk space has been occupied by MySQL.
|
1 2 3 4 5 6 |
$ ls -lh /var/lib/mysql/binlog* -rw-r-----. 1 mysql mysql 7.5M Apr 1 19:35 /var/lib/mysql/binlog.000319 -rw-r-----. 1 mysql mysql 9.1M Apr 2 02:34 /var/lib/mysql/binlog.000320 -rw-r-----. 1 mysql mysql 64 Apr 1 19:35 /var/lib/mysql/binlog.index $ ls -lh /var/lib/mysql/YOUR_DB_NAME |
Remove binary logs.
IF you want to restart MySQL immediately, but free space is almost run out by MySQL and you are afraid of restarting failed, then you could execute this SQL command first.
The time interval is adjustable, it depends on your situation.
|
1 |
PURGE BINARY LOGS BEFORE (NOW() - INTERVAL 4 HOUR); |
Directly purge binary logs is a temporary solution instead. To resolve this problem completely, you could refer to this post I wrote which is related to binary logs.
WordPress table “wp_options”.
This table stored many temporary settings or parameters for WordPress’ plugins. In general, parameters would be expired automatically, but sometimes too many expired data didn’t been remove from this table, and it caused this problem of taking up disk space.
You could run this SQL script under below, it will return the size of each table in the database.
|
1 2 3 4 5 |
SELECT table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` FROM information_schema.TABLES WHERE table_schema = 'YOUR_DB_NAME' |
Install WP-Optimize.
I recommend a plugin, WP-Optimize, to optimize the database of WordPress. You could install it from Plugins in WordPress admin.
Optimize wp_options.
It’s quite simple to execute optimization. You could click the button which is called “Database” and within cloud icon. After then you could find a checkbox named “Remove expired transient options”, and click “Run optimization” button which is on the right side of the checkbox.
One thing I have to mention is that you’d better NOT check the “Remove all transient options” before you execute optimization. Because it may cause your plugin failed.
This way can resolve your problem immediately, but we still need a long term solution. In step 3 will show you how to set a schedule to optimize database automatically.
Save as a schedule.
You could switch to tab “Settings”, then check “Enable scheduled clean-up and optimization” in block of “Scheduled clean-up settings”.
Change the frequency to daily, and check the option “Remove expired transient options”.
Before you click “Save settings”, I have to mention that WP-Optimize will store the execution schedule as current, and the next execution schedule will be the same time tomorrow. However, if you are a premium user of WP-Optimize, you could choose a specific time that you preferred.
Pingback:MySQL #01 binary logs settings & variables - BrilliantCode.net
Pingback:Wordpress 資料庫大小猛增 - BrilliantCode.net