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

by | Dec 24, 2021 | Technology | 0 comments

This article is contributed. See the original author and article here.

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.

 


Ahmed_S_Mahmoud_1-1640341878019.png


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


 



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;


 


It worth to mention that when you configure slow-query-log 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.


 


Ahmed_S_Mahmoud_0-1640338983257.png

 








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.

Submit a Comment