This article is contributed. See the original author and article here.
We sometimes see customers asking questions related to a discrepancy between the server storage usage and their expectations on the actual data usage. In this blog we will go through what can cause that and how to overcome from this situation.
In this section, I am listing down some thoughtful insights and recommendations to breakdown the storage usage to some extent.
1) First and foremost, monitor the server storage usage using the available Azure MySQL Metrics:
|Storage percentage||Percent||The percentage of storage used out of the server’s maximum.|
|Storage used||Bytes||The amount of storage in use. The storage used by the service may include the database files, transaction logs, and the server logs.|
|Server Log storage percent||Percent||The percentage of server log storage used out of the server’s maximum server log storage.|
|Server Log storage used||Bytes||The amount of server log storage in use.|
|Server Log storage limit||Bytes||The maximum server log storage for this server.|
|Storage limit||Bytes||The maximum storage for this server.|
2) The following queries can help you to have insights upon the database storage usage:
- run below query to know each schema usage with respect to data and index space
SELECT table_schema, SUM(data_length + index_length)/1024/1024 AS total_mb, SUM(data_length)/1024/1024 AS data_mb, SUM(index_length)/1024/1024 AS index_mb, COUNT(*) AS tables, CURDATE() AS today FROM information_schema.tables GROUP BY table_schema ORDER BY 2 DESC;
- Leverage below query to get insights on tablespaces capacity
SELECT FILE_NAME, TABLESPACE_NAME, TABLE_NAME, ENGINE, INDEX_LENGTH, TOTAL_EXTENTS, EXTENT_SIZE, (TOTAL_EXTENTS * EXTENT_SIZE)/1024/1024 AS "size in MB" from INFORMATION_SCHEMA.FILES ORDER BY 8 DESC;
- Filter out to get temporary tablespaces information
SELECT FILE_NAME, TABLESPACE_NAME, TABLE_NAME, ENGINE, INDEX_LENGTH, TOTAL_EXTENTS, EXTENT_SIZE, (TOTAL_EXTENTS * EXTENT_SIZE)/1024/1024 AS "size in MB" from INFORMATION_SCHEMA.FILES where file_name like '%ibtmp%';
- To get the actual file size on the disk, run below query against INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES:
SELECT * FROM information_schema.INNODB_SYS_TABLESPACES order by file_size desc;
- Look for the top 10 tables using below query.
SELECT CONCAT(table_schema, '.', table_name), CONCAT(ROUND(table_rows / 1000000, 2), 'M') rows, CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') DATA, CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') idx, CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size, ROUND(index_length / data_length, 2) idxPct FROM information_schema.TABLES ORDER BY data_length + index_length DESC LIMIT 10;
3) Examine the following server parameters which might contribute into the storage usage growth
This setting will tell InnoDB if it should store data and indexes in the shared tablespace or in a separate .ibd file for each table. Having a file per table enables the server to reclaim space when tables are dropped, truncated, or rebuilt. Databases containing a large number of tables should not use the table per file configuration. More information, see MySQL :: MySQL 5.7 Reference Manual :: 220.127.116.11 File-Per-Table Tablespaces.
show binary logs;
|Note:- OPTMIZE TABLE will trigger an exclusive table lock. it’s recommended that you DO NOT run in peak hours.|
5) Enable Storage Auto-grow and set up an alert
Last but not least, we always recommend that you enable storage auto-grow or set up an alert to notify you when your server storage is approaching the threshold so you can avoid getting into the read-only state. For more information, see the documentation on how to set up an alert.
|Note:- Keep in mind that storage can only be scaled up, not down.|
I hope you find this article helpful. If you have any feedback, please do not hesitate to provide it in the comment section below.
Ahmed S. Mazrouh
Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.