How to Resolve Storage Overuse Locking
Unduh
Mode fokus
Ukuran font
As you use a TencentDB for MySQL instance, data files grow and storage space gradually becomes fully occupied. To prevent data loss, the system automatically locks the instance, after which write operations cannot be performed.
Issue Description
When the data files of a TencentDB for MySQL instance fill up its storage space, the instance enters a storage overuse lock state, and its running status becomes "Locked (Capacity Limit Exceeded)".
Cause of the Issue
When the storage space of a TencentDB for MySQL instance is fully occupied, the system automatically locks the instance to prevent data loss. After the instance is locked, write operations cannot be performed.
Problem-solving Ideas
1. Expand the disk capacity of the instance. When you create a TencentDB for MySQL instance, you specify a certain amount of disk space for it. You can later increase the disk capacity by scaling it out. For more information, see Expanding Instance Disk Space.
2. Clean up useless data. Regularly clean up unneeded historical data. For more information, see Cleaning Up Useless Data.
Solutions
1. Scaling Up Instance Disk Space
1. Log in to the TencentDB for MySQL console.
2. In the instance list, click the Instance ID or Manage in the operation column to go to the instance management page.
3. On the Instance Details > Specification Configurations page, click Adjust Configurations.
4. On the redirected page, expand the disk capacity.
2. Cleaning Up Unnecessary Data or Tables
Note:
Exercise caution when cleaning up data. We do not recommend cleaning up data unless necessary. Instead, use scale-out methods to remove the lock. If cleanup is required, back up the database before cleaning it up to prevent data loss.
In TencentDB for MySQL, the
delete command cannot free up storage space. We recommend that you use the drop or truncate command to free up storage space.When a MySQL table contains a large amount of data, deleting data with the DELETE statement does not directly release disk space. It only marks database records or data pages as reusable. To actually reclaim tablespace and reduce disk usage, you can use
OPTIMIZE TABLE. Reference: Use the OPTIMIZE TABLE command to release the tablespace of a MySQL instance.Cleaning up data files may be delayed. Please wait patiently for the used space of the instance to decrease.
To use asynchronous big table deletion, check whether the kernel minor version supports it. For details, see Asynchronous Big Table Deletion. If the kernel minor version does not meet the requirement, upgrade it before you proceed. For the upgrade operation, see Upgrading the Kernel Minor Version.
1. Connect to your TencentDB for MySQL instance. For connection methods, see Connecting to a MySQL Instance.
2. Run the following command to view the table sizes in the database and identify historical or useless data that can be deleted.
SELECT file_name, concat(TOTAL_EXTENTS,'M') as 'FIle_size' FROM INFORMATION_SCHEMA.FILES order by TOTAL_EXTENTS DESC
3. Use the
drop or truncate command to clean up data.The drop command:
drop table <database name>.<table name>;
The truncate command:
truncate table <database name>.<table name>;
Subsequent Maintenance
If the instance status changes from "Locked (Capacity Limit Exceeded)" to "Running", the storage overuse lock has been released. In subsequent use, pay attention to the following points to maintain your database instance regularly and avoid storage overuse locking again.
1. Set an alarm for storage overuse lock events. The system monitors instance storage usage in real time and can push warning notifications to help you stay informed of storage status. For configuration instructions, see How to Set an Alarm for Disk Overuse Lock Events.
2. Set an appropriate retention period for automatic backup files. For configuration instructions, see Backing Up a Database.
3. Regularly delete unnecessary historical manual backups. For the operation procedure, see Deleting Backups.
4. Subscribe to monitoring metric alarms (for instructions, see Alarm Policy), and clean up data or split data in a timely manner to keep disk usage below 80%. You can check this in the console under Instance Management > Instance Monitoring.
5. For scenarios with large temporary files, optimize SQL statements to avoid frequent use of
ORDER BY and GROUP BY operations.Appendix: How to View Disk Space (Storage Space) Usage of an Instance
1. Log in to the TencentDB for MySQL console.
2. In the instance list, click the Instance ID or Manage in the operation column to go to the instance management page.
3. In Instance Details > Disk Usage, view the storage usage.
Bantuan dan Dukungan
Apakah halaman ini membantu?
Anda juga dapat Menghubungi Penjualan atau Mengirimkan Tiket untuk meminta bantuan.
masukan