Learning from Expertise #6: Where is my server storage taken - Azure MySQL?

Overview:

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.

 

Solution:

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

• innodb_file_per_table

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 :: 14.6.3.2 File-Per-Table Tablespaces.

 

• binlog_expire_logs_seconds

In case you set the binlog_expire_logs_seconds to a higher value, then the binary logs will not get purged soon enough and can lead to increase in the storage billing. More information, see MySQL documentation. You can monitor the binary logs usage using MySQL command: 

show binary logs;

 

• slow_query_log

It worth to mention that when you configure slow query long log_output parameter to "File", slow query logs will be written to both the local server storage as well as Azure Monitor Diagnostic Logs, however, there are 7 GB storage limit for the server logs which is available free of cost and cannot be extended. More information in Azure MySQL documentation: Slow query logs - Azure Database for MySQL | Microsoft Docs.

 

4) Leverage MySQL OPTIMIZE TABLE or Rebuild Tables/Indexes to reclaim the unused space.

The bloated data can be cleaned by calling OPTIMIZE TABLE or Rebuild to reclaim some unused space.

 

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

Posted at https://sl.advdat.com/3JkqCa2