RDS Mysql information_schema not updating

Question

Hello, I have an Amazon RDS instance with multiple databases.

Yesterday, I enabled the log, and I can see that the queries are now being saved in mysql.general_log.

Today, the table has 800k records, and I want to see how much space it occupies.

To do this, I run the following query:

```sql
SELECT 
    table_schema "Database",
    SUM(data_length + index_length) / 1024 / 1024 "Total Size (MB)"
FROM information_schema.tables 
GROUP BY table_schema;
```

The problem is that it returns the same result as yesterday, meaning it doesn't increase.

I'm also trying the following query, but it doesn't increase either:

```sql
SELECT 
    ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS "Total Size (GB)"
FROM information_schema.tables;
```

It's as if the information in the `information_schema` is not updating.

Answer

Hi,

In order for information_schema to not be painfully slow when retrieving this for large tables, it uses estimates, based on the cardinality of the primary key, for InnoDB tables. Otherwise it would end up having to do SELECT COUNT(*) FROM table_name, which for a table with billions of rows could take hours.

Look at SHOW INDEX FROM table_name and you will see that the number reported in information_schema is the same as the cardinality of the PK.

Running ANALYZE TABLE table_name will update the statistics which may make them more accurate, but it will still be an estimate rather than just-in-time checked row-count.

Best,

Didier

Answer

Hi there.

I'm a little confused because you mentioned a table with 800K rows but your query is getting the size of your entire database. If you want to know the size of a particular table, use the column TABLE_NAME. Try the query with this little modification:

```
SELECT 
    table_name "Table",
    SUM(data_length + index_length) / 1024 / 1024 "Total Size (MB)"
WHERE table_schema = [NAME OF YOUR DATABASE]
FROM information_schema.tables 
GROUP BY table_name;
```

I hope this helps.

RDS Mysql information_schema not updating

Relevant content