How to reclaim storage space with Azure Database for MySQL - Flexible Server

[Guest SiddhantSawant]

As you use an Azure Database for MySQL flexible server, especially for production workloads, databases and tables grow depending on the operations on that server.

 

 

 

In this blog post, I’ll explain what consumes storage space on an Azure Database for MySQL flexible server and how to reclaim some of that storage space for use by the server. I’ll also discuss some MySQL queries that you can use to identify database and table sizes, which will help identify the database that’s consuming the most space and the tables that contain space you can reclaim.

 

 

Contents of flexible server storage space

 

 

Storage space on a flexible server contains data files, which are comprised of table files and log files.

 

• Table files. By default, Azure Database for MySQL - Flexible Server uses the InnoDB storage engine with the value of the innodb_file_per_table server parameter set to ON (as recommended). With this parameter enabled, the flexible server stores a separate file for each table for better storage and file management.
  

 

Note

: For more information about the advantages of using this parameter, see

File-Per-Table Tablespaces

.

 

 

Table files are generated for both user-created tables and MySQL system tables.

 

 

• Log files. The log files on a flexible server include:
  
  • Binary log files - Binlogs are responsible for string data modifications made to a MySQL server instance for replicating data to a replica server. For more information, see Accessing binary logs from Azure Database for MySQL – Flexible Server.
    
  • Redo logs - Log files that encode requests from SQL statements or low-level API calls to change table data. The server parameter responsible size of this file is innodb_log_file_size.
    
  • Server logs (if enabled) - Server logs help you monitor and troubleshoot an Azure Database for MySQL flexible server, providing detailed insights into activities that have run on the server. Server logs have separate storage space that’s currently limited to 7 GB.
    

 

 

 

Checking the size of databases and tables

 

 

You can determine the size of the databases and tables on a flexible server by using a variety of queries that obtain information from the information_schema file in MySQL. The information_schema file stores the metadata for your databases, information about the MySQL flexible server (e.g., the name of a database or table), the data type of a column, access privileges, etc.

 

 

 

Additional information about these queries is provided in the following sections.

 

 

 

Determine the size of databases

 

 

To determine the size of the databases on your flexible server, run the following query:

 

 

 

SELECT S.SCHEMA_NAME, T.ENGINE, CONCAT(IFNULL(ROUND(SUM(T.DATA_LENGTH)/1024/1024,2),0.00),"Mb") as DATA_SIZE,

CONCAT(IFNULL(ROUND(SUM(T.INDEX_LENGTH)/1024/1024,2),0.00),"Mb") as INDEX_SIZE,COUNT(TABLE_NAME) TOTAL_TABLES FROM

INFORMATION_SCHEMA.SCHEMATA S LEFT JOIN INFORMATION_SCHEMA.TABLES T ON S.SCHEMA_NAME= T.TABLE_SCHEMA WHERE

S.SCHEMA_NAME not in ("mysql","information_schema","test","performance_schema","sys") GROUP BY S.SCHEMA_NAME, T.ENGINE order

by DATA_SIZE DESC;

 

 

 

The results of the query will appear similar to the following:

 

 

 

+---------------+--------+------------+------------+--------------+

| SCHEMA_NAME | ENGINE | DATA_SIZE | INDEX_SIZE | TOTAL_TABLES |

+---------------+--------+------------+------------+--------------+

| space_reclaim | InnoDB | 37132.97Mb | 2241.75Mb | 5 |

| demo | InnoDB | 20598.00Mb | 1486.56Mb | 10 |

| demo_2 | InnoDB | 0.02Mb | 0.02Mb | 1 |

+---------------+--------+------------+------------+--------------+

 

 

 

Determine the size of a tables in particular database

 

 

To determine the size of an individual table in your database, run the following query:

 

 

 

SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table', CONCAT(ROUND(((DATA_LENGTH +

INDEX_LENGTH + DATA_FREE) / 1024 / 1024),2)," Mb") AS Size

FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = "<database_name>"

order by table_rows;

 

 

 

The results of the query will appear similar to the following:

 

 

 

+----------+----------+------------+

| Database | Table | Size |

+----------+----------+------------+

| demo | sbtest5 | 3756.00 Mb |

| demo | sbtest2 | 3711.00 Mb |

| demo | sbtest8 | 3781.00 Mb |

| demo | sbtest12 | 3532.00 Mb |

| demo | sbtest9 | 3845.00 Mb |

+---------+-----------+------------+

 

Determine the 10 largest tables in a database

 

 

To determine the 10 largest tables in your database, run the following query:

 

 

 

SELECT TABLE_SCHEMA AS `DB`, TABLE_NAME AS `TABLE`, ROUND((DATA_LENGTH + INDEX_LENGTH +

DATA_FREE) / 1024 / 1024) AS

`Size (MB)`, ROUND((DATA_FREE) / 1024 / 1024," Mb") AS `FREE(MB)` FROM

INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA not

in ("mysql","information_schema","test","performance_schema","sys" ) ORDER BY DATA_LENGTH

+ INDEX_LENGTH DESC limit 10;

 

 

 

The results of the query will appear similar to the following:

 

 

 

+---------------+----------+-----------+----------+

| DB | TABLE | Size (MB) | FREE(MB) |

+---------------+----------+-----------+----------+

| space_reclaim | sbtest3 | 11263 | 0 |

| space_reclaim | sbtest4 | 10415 | 0 |

| space_reclaim | sbtest1 | 7793 | 0 |

| space_reclaim | sbtest2 | 5381 | 0 |

| space_reclaim | sbtest5 | 4523 | 0 |

| demo | sbtest5 | 2252 | 0 |

| demo | sbtest8 | 2233 | 0 |

| demo | sbtest10 | 2224 | 0 |

| demo | sbtest3 | 2217 | 0 |

| demo | sbtest4 | 2213 | 0 |

+---------------+----------+-----------+----------+

Determine the 10 tables with the most reclaimable space

 

 

Tables can contain free space as a result of fragmentation, which is caused by random insertions or deletions from a table. Fragmentation can cause a table to take more space than it should ideally.

 

 

 

To determine the 10 tables that have the most reclaimable space, run the following query:

 

 

 

SELECT TABLE_SCHEMA AS `DB`, TABLE_NAME AS `TABLE`, ROUND((DATA_LENGTH + INDEX_LENGTH +

DATA_FREE) / 1024 / 1024) AS `Size (MB)`, ROUND((DATA_FREE) / 1024 / 1024," Mb") AS

`FREE(MB)`FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA not in

("mysql","information_schema","test","performance_schema","sys" )

ORDER BY DATA_FREE DESC;

 

 

 

The results of the query will appear similar to the following:

 

 

 

+---------------+----------+-----------+----------+

| DB | TABLE | Size (MB) | FREE(MB) |

+---------------+----------+-----------+----------+

| space_reclaim | sbtest2 | 17669 | 6313 |

| space_reclaim | sbtest1 | 15610 | 4284 |

| space_reclaim | sbtest5 | 7793 | 5 |

| space_reclaim | sbtest9 | 5381 | 3 |

| space_reclaim | sbtest3 | 4523 | 3 |

| demo | sbtest5 | 2252 | 3 |

| demo | sbtest8 | 2233 | 3 |

| demo | sbtest10 | 2224 | 3 |

| demo | sbtest3 | 2217 | 3 |

| demo | sbtest4 | 2213 | 3 |

+---------------+----------+-----------+----------+

Reclaim storage space

 

 

You can reclaim storage space in a variety of ways, which are covered in the following sections.

 

 

 

Editing the binlogs_expire_seconds parameter

 

 

You can quickly reclaim some space by editing the binlogs_expire_seconds server parameter. By default, the value of the parameter is set to 0, which does not purge binlogs from your server.

 

 

 

Before you edit the server parameter, verify the replication lag and binlogs requirement of your read-replicas.

 

 

 

Based on the replication latency and binlogs requirements, you can adjust the binlogs expiration time on your primary server. You can tweak the server parameter to particular days or even hours as required. For example, if you only require binlogs for a one-day period, then change the server parameter to 86400 seconds (which equals one day). For more information about configuring server parameters, see Configure server parameters using the Azure portal.

 

 

 

Executing NOOP (NULL) alter

 

 

Run a query to identify the 10 tables on your flexible server with free space to reclaim.

 

 

 

Note: If your workload doesn’t involve many random insertions or deletions, this technique may not gain that much more free space.

 

 

 

If the results of your query appear similar to the following, then it’s clear that there are tables from which you can reclaim space.

 

 

 

 

To reclaim the disk space, you can execute a NOOP (no-operations) alter or null alter on the table, which re-arranges the data and indexes and frees storage space for use.

 

 

 

Next, I’ll run the following command:

 

 

 

mysql> alter table space_reclaim.sbtest1 engine=INNODB, algorithm=INPLACE, lock=NONE;

Query OK, 0 rows affected (5 min 13.17 sec)

Records: 0 Duplicates: 0 Warnings: 0

 

 

 

Running the Optimize Table command

 

 

You can also reclaim space by running the OPTIMIZE TABLE command on your table.

 

 

 

mysql> optimize table space_reclaim.sbtest2.

 

+-----------------------+----------+----------+----------------------------------------------+

| Table | Op | Msg_type | Msg_text |

+-----------------------+----------+----------+----------------------------------------------+

| space_reclaim.sbtest2 | optimize | note | Table does not support optimize, doing recreate + analyze instead |

| space_reclaim.sbtest2 | optimize | status | OK |

+-----------------------+----------+----------+----------------------------------------------+

 

Note that when you alter a table, a flexible server creates a new table and copies records to that table, which requires at least some free space (equivalent to current table size).

 

 

 

Conclusion

 

 

You should now have the information required to reclaim storage space on your Azure Database for MySQL flexible server. If you have any feedback or questions about the information provided above, please leave a comment below or email us at AskAzureDBforMySQL@service.microsoft.com. Thank you!

 

Continue reading...