Поиск по этому блогу

четверг, 8 декабря 2016 г.

Understand right file_per_table=ON

Let's imagine situation:

You have 100 DBs on your MySQL server and in some point of time you realised that your InnoDB space had been consumed on 80% and you chosen to migrate to file_per_table, luckily it is a dynamic parameter. Well, it's a good idea if you have enough disk space on the host. It easer than adding additional InnoDB file right? You ACKed the alert in your monitoring system and forgot this problem.

This is wrong approach, I will explain why:
It will use separate tablespace only for newly created tables, and if you have some growing tables in system tablespace, it will keep on consuming system INNODB. Don't forget to move tables to separate file by altering table.

How to find tables in SYSTEM InnoDB space:
Select NAME from information_schema.INNODB_SYS_TABLES where SPACE =0 and name not like "SYS_%";


According to MySQL docs SPACE =0 means that it is using SYSTEM InnoDB space.





Комментариев нет:

Отправить комментарий