• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

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

By admin

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).

Filed Under: mysql

Some more articles you might also be interested in …

  1. MySQL Interview Questions : Multi-source Replication in MySQL Server
  2. Troubleshooting MySQL Query hung – “Waiting for Table Flush”
  3. Understanding the Different Configuration files used for MySQL Server
  4. MySQL – How to undo (rollback) a set of SQL statements
  5. Managing MySQL Using Systemd As A Non Root User
  6. How to List Users and Privileges in MySQL
  7. How to Install MySQL Cluster on a single server for testing purposes
  8. How to set the default character set in MySQL and how to propagate it in a master-master replication scenario
  9. “expect” script to provide password to mysql_config_editor
  10. How to use a Wrapper Script to set Custom Per-Process Attributes for MySQL Server

You May Also Like

Primary Sidebar

Recent Posts

  • Basics of client connectivity in Oracle Data Guard configuration
  • ORA-354 ORA-353 and ORA-312: Possible corruption in Online Redo Log File Members in a Redo Log Group
  • How to relocate the redo log files to a different location on disk
  • Oracle Database: Redo log operations (Add/Drop/Change Location)
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary