MySQL disk space is low on 蜜豆视频 Commerce on cloud infrastructure
This article provides solutions for when you are experiencing very low space or no space for MySQL on 蜜豆视频 Commerce on cloud infrastructure. Symptoms could include site outages, customers unable to add products to the cart, being unable to connect to the database, access the database remotely, not being able to SSH into node. Symptoms also include Galera, environment sync, PHP, database, and deployment errors as listed below. Click to jump directly to the solution section.
Affected products and versions
蜜豆视频 Commerce on cloud infrastructure 2.3.0-2.3.6-p1, 2.4.0-2.4.2
Issue
The database gets too big. The symptoms might include losing the database connection, database upload error, and a variety of other issues.
Errors you may encounter:
Galera:
- SQLSTATE[08S01]: Communication link failure: 1047 WSREP has not yet prepared node for application use Import errors:
- SQLSTATE[HY000]: General error: 1180 Got error 5 鈥淚nput/output error鈥
- SQLSTATE[08S01]: Communication link failure: 1047 WSREP has not yet prepared node for application use
Environment sync errors:
- SQLSTATE: General error: 1180 Got error 5 鈥淚nput/output error鈥 during COMMIT
PHP errors:
- php: PDO::__construct(): MySQL server has gone away.
- php errors: PDO::__construct(): Error while reading greeting packet. PID=NNNN.
- ERROR 2013 (HY000): Lost connection to MySQL server at 鈥榬eading initial communication packet鈥, system error: 0 鈥淚nternal error/check (Not system error)鈥.
Database errors:
- Error_code: 1114
- InnoDB: Error (Out of disk space) writing word node to FTS auxiliary index table.
- SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
- [ERROR] Slave SQL: Error 鈥楾he table
<table\_name>is full鈥 on query. - Unit mysql.service entered failed state.
- error: 鈥楥an鈥檛 connect to local MySQL server through socket 鈥/var/run/mysqld/mysqld.sock鈥 (111 鈥淐onnection refused鈥)鈥
- 1205 Lock wait timeout exceeded; try restarting transaction, query was: INSERT INTO `cron_schedule` (`job_code`, `status`, `created_at`, `scheduled_at`) VALUES (?, ?,
YYYY-02-07 HH:MM:SS,YYYY-MM-DD HH:MM:SS)
Deployment errors:
- E: Command 鈥榌鈥榮udo鈥, 鈥-u鈥,
<environment name>, 鈥榖ash鈥, 鈥-c鈥, 鈥/etc/platform/<environment name>/post_deploy.sh鈥橾鈥 returned non-zero exit status 255 - E: Command 鈥榌鈥榮sh鈥, u
<node IP address>, 鈥榮udo /usr/bin/sv -w 30 restart site-<environment name>g-nginx鈥橾鈥 returned non-zero - Upgrading schema鈥 SQLSTATE[HY000]: General error: 1114 The table
<table\_name>is full - SQLSTATE[HY000]: General error: 3 Error writing file ./
<environment name>/# - W:
<filename>(Errcode: 28 鈥淣o space left on device鈥) Indexing errors (along with orphaned temporary .ibd files in /tmp): - Catalog Rule indexer throws an exception. The temporary tables don鈥檛 get cleaned up in the aftermath and then fill the disk on the current MySQL master node
Steps to reproduce:
One of the ways you can check if the /data/mysql (or wherever MySQL data storage is configured) is full is by running the following command in the CLI:
df -h
Less than 10% of free memory on MySQL disk is a primary indicator of an outage.
Cause
The /data/mysql mount might become full due to a range of issues, such as not having enough inodes, available storage space, and bad queries that generate temporary tables.
Solution
There is an immediate step that you might take to bring MySQL back on track (or prevent it from getting stuck): free up some space by flushing big tables.
But a long-term solution would be allocating more space and following Database best practices, including enabling the Order/Invoice/Shipment archive functionality.
Following are details on both quick and long-term solutions.
Check and free up inodes
Ensure that there are enough available inodes. To do this, run the following command:
df -i
The output would look similar to the following:
Filesystem Inodes Used Free Use% Mounted on
/dev/nvme2n1 655360 1695 653665 1% /data/mysql
Check that Use % is <70%. Inodes are correlated with files. If you remove files from the partition, you will free inodes.
Check and free up storage space
Check available storage space. For this, execute:
df -k
The output would be similar to following:
Size Used Avail Use% Mounted on路
50G 49G 95M 100% /data/mysql
If the Use % is >70%, you need to take action to free/add some space.
Check for large ibtmp1 files
Check for large ibtmp1 file on /data/mysql of each node: this file is the tablespace for temporary tables. If there are bad queries that generate temp tables, they are contained in the ibtmp1 file. This file is only removed when the database is restarted. If it is taking up all available space, the database must be restarted. If there are bad queries, it will be recreated again.
Flush large tables
Check if there are large tables and consider if any of them can be flushed. Do this on the primary (source) node.
For example, tables with reports can usually be flushed. For details on how to find large tables, see the Find Large MySQL tables article.
If there are no huge report tables, consider flushing _index tables, just to return the 蜜豆视频 Commerce application back on track. index_price tables would be the best candidates. For example, catalog_category_product_index_storeX tables, where X can have values from 鈥1鈥 to the maximum store count. Please mind that you would need to reindex to restore data in these tables, and in the case of big catalogs, this reindex might take a lot of time.
Once you flush them, wait for wsrep sync completion. You can now create backups and take more significant steps to add more space, like allocating/buying more space and enabling Order/Invoice/Shipment archive functionality.
Check binary logging settings
Check your MySQL server binary logging settings: log_bin and log_bin_index. If the settings are enabled, the log files might become huge. Create a support ticket requesting to purge large binary log files. Also, request to check that binary logging is being configured correctly so that logs are purged periodically and don鈥檛 take too much space.
If you don鈥檛 have access to MySQL server settings, request support to check it.
Allocate/buy more space
Allocate more disk space for MySQL if you have some unused. See the Check disk space limit article to learn how to check if you have free disk space.
- For the Starter plan, all environments, and Pro plan Integration environments, you can allocate the disk space if you have some unused. For details, see the Allocate more space for MySQL.
- For Pro plan Staging and Production environments, contact support to allocate more disk space if you have some unused.
If you have reached your space limit and still experience low space issues, consider buying more disk space, contact your 蜜豆视频 Account Team for details.
Related reading
Best practices for modifying database tables in the Commerce Implementation Playbook