AWS RDS Aurora - Writer and Reader Showing Different Results

Question

Hi, not sure if someone already encounter this. 
We were trying to get the table free space from our databases using below command but we noticed that writer showing different results from read-only instance. Not sure where to check but could this be an issue with synchronization between two instances? Appreciate any help, thank you.

SELECT T.TABLE_SCHEMA, T.TABLE_NAME, T.DATA_FREE, ST.SPACE, SD.PATH 
FROM INFORMATION_SCHEMA.TABLES T 
LEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES ST ON ST.NAME = CONCAT(T.TABLE_SCHEMA,'/',T.TABLE_NAME) 
LEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_DATAFILES SD ON SD.SPACE = ST.SPACE 
WHERE T.ENGINE = 'InnoDB' AND T.TABLE_SCHEMA  IN ('db1','db2')
ORDER BY T.DATA_FREE DESC 
LIMIT 30;

Answer

Hi Koyla,

I am not sure if the query that you are using is correct or not but try to use the query as mentioned below to get the idea of how much space is free in the database and try to get the same information from both reader and writer endpoints.

```
SELECT 
  table_schema AS 'Db Name',
  Round( Sum( data_length + index_length ) / 1024 / 1024, 3) AS 'Db Size (MB)',
  Round( Sum( data_free ) / 1024 / 1024, 3 ) AS 'Free Space (MB)'
FROM information_schema.tables
GROUP BY table_schema ;
```

Also the more accurate way to find the file size of the innodb tablespace is as using the INFORMATION_SCHEMA.FILES and not information_schema.tables

```
select FILE_NAME, TABLESPACE_NAME, TABLE_NAME, ENGINE, INDEX_LENGTH, TOTAL_EXTENTS, EXTENT_SIZE from INFORMATION_SCHEMA.FILES

SELECT FILE_NAME, TABLESPACE_NAME, ROUND((TOTAL_EXTENTS * EXTENT_SIZE) / 1024 / 1024 / 1024, 4) AS SIZE_GB FROM INFORMATION_SCHEMA.FILES order by size_gb desc limit 10;
```

Try to use the above queries on your reader and writer endpoints and check the results.

AWS RDS Aurora - Writer and Reader Showing Different Results

Conteúdo relevante