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

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • 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 : how to set (change) user password
  2. How to take Logical Backups on a MySQL Replication Slave using shell script
  3. MySQL Table Maintenance – InnoDB, MyISAM, MEMORY, ARCHIVE
  4. How to reset MySQL database root password
  5. Troubleshooting MySQL Query hung – “Waiting for Table Flush”
  6. Can MySQL Cluster Run Multiple Nodes On a Single Server
  7. How to use a Wrapper Script to set Custom Per-Process Attributes for MySQL Server
  8. MySQL : How To Find the Slowest Queries
  9. How to List and Set SELinux Context for MySQL Server
  10. MySQL 8.0 : Persisted Variables

You May Also Like

Primary Sidebar

Recent Posts

  • aws ec2: CLI for AWS EC2 (Command Examples)
  • aws cur – Create, query, and delete AWS usage report definitions (Command Examples)
  • aws configure – Manage configuration for the AWS CLI (Command Examples)
  • aws cognito-idp: Manage Amazon Cognito user pool and its users and groups using the CLI

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright