This article is the second of the new series dedicated on how a DBA can find the info he needs with MySQL Database Service in Oracle Cloud Infrastructure.
The first article was dedicated on Backups, this one is about Disk Space Utilization.
This time we have two options to retrieve useful information related to disk space:
- Metrics
- Performance_Schema
Metrics
In the OCI Web Console, there is a dedicated metric for the disk usage:
As for the backup, we can create Alarms for this metric to get informed when we reach the end of the DB System’s capacity:
We will create 2 different alerts (see Scott’s article about alerts) the first one will be a warning when the disk space usage reaches 50% and the second one, a critical alert when the disk space utilization reaches 80%:
And if the system reaches 50% of disk capacity, we get the mail:
Performance_Schema
The MySQL DBA has also access to the disk space usage via the SQL interface using Performance_Schema. In MySQL Database Service, Performance_Schema provides some extra tables that are part of the Health Monitor:
select * from health_block_device order by timestamp desc limit 10;
+--------+---------------------+--------------+-----------------+-------------+-------------+
| DEVICE | TIMESTAMP | TOTAL_BYTES | AVAILABLE_BYTES | USE_PERCENT | MOUNT_POINT |
+--------+---------------------+--------------+-----------------+-------------+-------------+
| xfs | 2023-03-16 12:27:56 | 107317563392 | 89610473472 | 16.50 | /db |
| xfs | 2023-03-16 12:26:56 | 107317563392 | 89610489856 | 16.50 | /db |
| xfs | 2023-03-16 12:25:56 | 107317563392 | 89610485760 | 16.50 | /db |
| xfs | 2023-03-16 12:24:56 | 107317563392 | 89610485760 | 16.50 | /db |
| xfs | 2023-03-16 12:23:56 | 107317563392 | 89610485760 | 16.50 | /db |
| xfs | 2023-03-16 12:22:56 | 107317563392 | 89610489856 | 16.50 | /db |
| xfs | 2023-03-16 12:21:56 | 107317563392 | 89610489856 | 16.50 | /db |
| xfs | 2023-03-16 12:20:57 | 107317563392 | 89610485760 | 16.50 | /db |
| xfs | 2023-03-16 12:19:56 | 107317563392 | 89610485760 | 16.50 | /db |
| xfs | 2023-03-16 12:18:56 | 107317563392 | 89610485760 | 16.50 | /db |
+--------+---------------------+--------------+-----------------+-------------+-------------+
10 rows in set (0.0028 sec)
If you take a look at the other tables from the Health Monitor that are related to disk, you will see that those are created for the MDS operators.
Using performance_schema you can also find the size of your dataset and the space used on disk:
SELECT format_bytes(sum(data_length)) DATA_SIZE,
format_bytes(sum(index_length)) INDEX_SIZE,
format_bytes(sum(data_length+index_length)) TOTAL_SIZE,
format_bytes(sum(data_free)) DATA_FREE,
format_bytes(sum(FILE_SIZE)) FILE_SIZE,
format_bytes((sum(FILE_SIZE)/10 - (sum(data_length)/10 +
sum(index_length)/10))*10) WASTED_SIZE
FROM information_schema.TABLES as t
JOIN information_schema.INNODB_TABLESPACES as it
ON it.name = concat(table_schema,"/",table_name)
ORDER BY (data_length + index_length);
+-----------+------------+------------+-----------+-----------+-------------+
| DATA_SIZE | INDEX_SIZE | TOTAL_SIZE | DATA_FREE | FILE_SIZE | WASTED_SIZE |
+-----------+------------+------------+-----------+-----------+-------------+
| 2.37 GiB | 4.70 GiB | 7.07 GiB | 43.00 MiB | 7.75 GiB | 694.17 MiB |
+-----------+------------+------------+-----------+-----------+-------------+But don’t forget that on disk you also have plenty of other files like redo logs, undo logs, binary logs, …
Extra
In the result of the previous SQL statement, we can see in the last column (WASTED_SIZE) that there are almost 650MB of wasted disk space. This column represents gaps in tablespaces.
Let’s find out for which tables and how to recover it:
SELECT NAME, TABLE_ROWS, format_bytes(data_length) DATA_SIZE,
format_bytes(index_length) INDEX_SIZE,
format_bytes(data_length+index_length) TOTAL_SIZE,
format_bytes(data_free) DATA_FREE,
format_bytes(FILE_SIZE) FILE_SIZE,
format_bytes((FILE_SIZE/10 - (data_length/10 +
index_length/10))*10) WASTED_SIZE
FROM information_schema.TABLES as t
JOIN information_schema.INNODB_TABLESPACES as it
ON it.name = concat(table_schema,"/",table_name)
ORDER BY (data_length + index_length) desc LIMIT 10;
+-----------------------------+------------+------------+------------+------------+------------+------------+-------------+
| NAME | TABLE_ROWS | DATA_SIZE | INDEX_SIZE | TOTAL_SIZE | DATA_FREE | FILE_SIZE | WASTED_SIZE |
+-----------------------------+------------+------------+------------+------------+------------+------------+-------------+
| airportdb/booking | 54082619 | 2.11 GiB | 4.62 GiB | 6.74 GiB | 4.00 MiB | 7.34 GiB | 615.03 MiB |
| airportdb/weatherdata | 4617585 | 215.80 MiB | 0 bytes | 215.80 MiB | 7.00 MiB | 228.00 MiB | 12.20 MiB |
| airportdb/flight | 461286 | 25.55 MiB | 73.64 MiB | 99.19 MiB | 4.00 MiB | 108.00 MiB | 8.81 MiB |
| airportdb/seat_sold | 462241 | 11.52 MiB | 0 bytes | 11.52 MiB | 4.00 MiB | 21.00 MiB | 9.48 MiB |
| airportdb/passengerdetails | 35097 | 4.52 MiB | 0 bytes | 4.52 MiB | 4.00 MiB | 12.00 MiB | 7.48 MiB |
| airportdb/passenger | 36191 | 2.52 MiB | 1.52 MiB | 4.03 MiB | 4.00 MiB | 12.00 MiB | 7.97 MiB |
| airportdb/airplane_type | 302 | 1.52 MiB | 0 bytes | 1.52 MiB | 4.00 MiB | 9.00 MiB | 7.48 MiB |
| airportdb/airport_geo | 9561 | 1.52 MiB | 0 bytes | 1.52 MiB | 4.00 MiB | 11.00 MiB | 9.48 MiB |
| airportdb/flightschedule | 9633 | 528.00 KiB | 736.00 KiB | 1.23 MiB | 4.00 MiB | 9.00 MiB | 7.77 MiB |
| airportdb/airport | 9698 | 448.00 KiB | 656.00 KiB | 1.08 MiB | 4.00 MiB | 9.00 MiB | 7.92 MiB |
+-----------------------------+------------+------------+------------+------------+------------+------------+-------------+We can see that the itis in the table airportdb.booking that we have the most waste of disk space. Optimizing that table (this is not an online operation!) will recover some of the wasted disk space:
optimize table airportdb.booking ;
+-------------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-------------------+----------+----------+-------------------------------------------------------------------+
| airportdb.booking | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| airportdb.booking | optimize | status | OK |
+-------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (14 min 45.5530 sec)
set information_schema_stats_expiry=0;
SELECT NAME, TABLE_ROWS, format_bytes(data_length) DATA_SIZE,
format_bytes(index_length) INDEX_SIZE,
format_bytes(data_length+index_length) TOTAL_SIZE,
format_bytes(data_free) DATA_FREE,
format_bytes(FILE_SIZE) FILE_SIZE,
format_bytes((FILE_SIZE/10 - (data_length/10 +
index_length/10))*10) WASTED_SIZE
FROM information_schema.TABLES as t
JOIN information_schema.INNODB_TABLESPACES as it
ON it.name = concat(table_schema,"/",table_name)
ORDER BY (data_length + index_length) desc LIMIT 10;
+-----------------------------+------------+------------+------------+------------+------------+------------+-------------+
| NAME | TABLE_ROWS | DATA_SIZE | INDEX_SIZE | TOTAL_SIZE | DATA_FREE | FILE_SIZE | WASTED_SIZE |
+-----------------------------+------------+------------+------------+------------+------------+------------+-------------+
| airportdb/booking | 54163810 | 2.59 GiB | 2.72 GiB | 5.31 GiB | 4.00 MiB | 5.37 GiB | 63.06 MiB |
| airportdb/weatherdata | 4617585 | 215.80 MiB | 0 bytes | 215.80 MiB | 7.00 MiB | 228.00 MiB | 12.20 MiB |
| airportdb/flight | 461286 | 25.55 MiB | 73.64 MiB | 99.19 MiB | 4.00 MiB | 108.00 MiB | 8.81 MiB |
| airportdb/seat_sold | 462241 | 11.52 MiB | 0 bytes | 11.52 MiB | 4.00 MiB | 21.00 MiB | 9.48 MiB |
| airportdb/passengerdetails | 35097 | 4.52 MiB | 0 bytes | 4.52 MiB | 4.00 MiB | 12.00 MiB | 7.48 MiB |
| airportdb/passenger | 36191 | 2.52 MiB | 1.52 MiB | 4.03 MiB | 4.00 MiB | 12.00 MiB | 7.97 MiB |
| airportdb/airplane_type | 302 | 1.52 MiB | 0 bytes | 1.52 MiB | 4.00 MiB | 9.00 MiB | 7.48 MiB |
| airportdb/airport_geo | 9561 | 1.52 MiB | 0 bytes | 1.52 MiB | 4.00 MiB | 11.00 MiB | 9.48 MiB |
| airportdb/flightschedule | 9633 | 528.00 KiB | 736.00 KiB | 1.23 MiB | 4.00 MiB | 9.00 MiB | 7.77 MiB |
| airportdb/airport | 9698 | 448.00 KiB | 656.00 KiB | 1.08 MiB | 4.00 MiB | 9.00 MiB | 7.92 MiB |
| airportdb/airplane | 5583 | 224.00 KiB | 144.00 KiB | 368.00 KiB | 0 bytes | 448.00 KiB | 80.00 KiB |
| airportdb/employee | 1000 | 208.00 KiB | 48.00 KiB | 256.00 KiB | 0 bytes | 336.00 KiB | 80.00 KiB |
| airportdb/airline | 113 | 16.00 KiB | 32.00 KiB | 48.00 KiB | 0 bytes | 144.00 KiB | 96.00 KiB |
| airportdb/flight_log | 0 | 16.00 KiB | 16.00 KiB | 32.00 KiB | 0 bytes | 128.00 KiB | 96.00 KiB |
| sys/sys_config | 6 | 16.00 KiB | 0 bytes | 16.00 KiB | 0 bytes | 112.00 KiB | 96.00 KiB |
| airportdb/airport_reachable | 0 | 16.00 KiB | 0 bytes | 16.00 KiB | 0 bytes | 112.00 KiB | 96.00 KiB |
+-----------------------------+------------+------------+------------+------------+------------+------------+-------------+We can see that we saved several hundreds MB.
Conclusion
Now you know how to find the information to monitor your disk space and be alerted directly via OCI’s alerting system or by using a third party tool.
By controlling the disk space usage, you know exactly when it’s time to expand the disk space of your DB system (or migrate to a bigger Shape).
