Limiting the size of a mysql table

Question

Is it possible to limit the size (in disk size / rows) of a single mysql table?

If it is not possible, what is the easiest way to run multiple mysql engines on one physical computer? (my plan is to set one mysql instance's data files to a separate disk partition)

Answer 1

If you have a (relatively) small amount of data, you might try creating a table with the MEMORY storage engine. That does have a configurable size limit, but of course you're limited by the amount of available memory on your box.

If your data size requires disk-based tables, then one straight-forward way to limit your table size would be to run a job that would periodically purge parts of the table. This is probably a better option than using a small partition -- many MySQL commands behave poorly when the disk is full.

Also - you can run as many MySQL instances on your computer as you want -- if you're connecting via TCP/IP, you just have to assign each one a different port number.

Answer 2

You can use the myisampack utility that creates the compressed tables ( but only for the MyISAM engine). The tables become read-only with static data types.

Answer 3

If they are MyISAM tables, toy can specify the directories into which the Data and Index files will be stored.

According to the UI:

Data Directory: ___________________ Directory where to put the tables data file
Index Directory: __________________ Directory where to put the tables index file

This works only for MyISAM tables and not on some operating systems (Windows)

So I guess that means you can move the files for a table, if you're not on a Windows host.

Answer 4

This is very similar to Question #1561612, except there the limit was to be enforced per schema. Unfortunately there is not a really well supported way to do this. Please see the answers there and see if they answer your question, too.

Answer 5

To enforce quotas there are a few different approaches I think the one found on this site is probably the most sound I've run across.

To direct mysql to different data directories either put symlinks in your linux setup or try using a configuration file. MySQL will let you specify where the database files are saved via these conf files read here for windows setup or search around for information about my.cnf

Best of luck to you its late so I'm off to bed.

Answer 6

1. Quotas: not directly supported by MySQL. you can emulate something using a file system that supports per-user-quote by setting file ownership to your users, and setting the quota per user at the OS level. note that by default InnoDB uses a single table file for all databases, but there is an option to tell it to use a file per table. however - note that there may be performance implications to this.
2. Running multiple instances on the same machine is possible using a different configuration file for each server.

where at the very minimum, you will want to change those from from user to user:

[mysqld]
port=PORT_NUMBER
datadir=/home/USER/mysql
tmpdir=/home/USER/tmp/mysql

to start a sever, run:

mysqld_safe --defaults-file=user_specific_my.cnf

to stop a server, run:

mysqladmin -u root -pROOT_PASSWORD --port=3307 -h 127.0.0.1 shutdown

to connect to a server, a user should use:

mysql --port=USER_PORT -h 127.0.0.1 -pPASSWORD