• 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. Excluding a table or database from MySQL replication
  2. Beginners Guide to Implementing Table Maintenance in MySQL
  3. MySQL – How to Backup User Privileges as CREATE USER and/or GRANT Statements
  4. MySQL ‘show processlist’ statement
  5. How To Reset MySQL 8.0 Root Password On Windows
  6. How To Restore an NDBCluster Backup Using ndb_restore
  7. How to Restart a MySQL Cluster without downtime
  8. MySQL : Stored Procedure For Rotating the Audit Log
  9. Granting All Privileges On All databases Except One Specific Table in MySQL
  10. MySQL Fails to Start Using systemctl On systemd Linux Distributions

You May Also Like

Primary Sidebar

Recent Posts

  • How to Disable IPv6 on Ubuntu 18.04 Bionic Beaver Linux
  • How to Capture More Logs in /var/log/dmesg for CentOS/RHEL
  • Unable to Start RDMA Services on CentOS/RHEL 7
  • How to rename a KVM VM with virsh
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary