How to Set Space limits for MySQL for database/schema/table

MySQL itself has not such quotas, but it is possible to partially manage quota limitation at engine level.

For MyISAM it is possible to limit table size using option myisam_data_pointer_size This variable limits default data pointer size: less this variable is, less rows would be allowed to be saved in the table. “Table is full” error will be reported if this option is considered.

For InnoDB it is possible to specify hard limit of shared tablespace: syntax is similar to “innodb_data_file_path=ibdata1:5000M;ibdata2:5000M” rather than “innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend”. However there is no way to setup single tablespace file for each of databases using this method.

There can be workarounds to implement some quota checks for InnoDB tablespaces (using one file per table), based on counting tables rows:

Counting Rows Of A Table In MySQL Server

Or based on the amount of data in tables retrieved from Information Schema:

SELECT 'Sum_index_length' as '', IFNULL(SUM(INDEX_LENGTH),0) from information_schema.TABLES where ENGINE='InnoDB';
SELECT 'Sum_data_length' as '', IFNULL(SUM(DATA_LENGTH),0) from information_schema.TABLES where ENGINE='InnoDB';

Or based also on the physical tablespace file:

SELECT FILE_SIZE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE ...

It is also possible to setup OS-level task (like a cron task checking periodically the size of MySQL data directory) which will remind when space available is less than a determined threshold.

Note:There is no built-in mechanism to stop provisioning and limit a table from growing, so this could be implemented as a scheduled task periodically checking certain measurements and setting the instance as read_only (as an example).

You May Also Like