MySQL schema size

I have a development MySQL (InnoDB only) server with several users. Each user has access on one exclusive schema. How can I limit the schema size so that each user can use only 1GB (for example)?

13.10.2009 17:07:11

MySQL itself does not offer a quota system. Using the method suggested by James McNellis would probably work, however having InnoDB reach a hard quota limit suddenly would certainly not benefit stability; all data files are still connected via the system table space which cannot get rid of.

Unfortunately I do not see a practical way to achieve what you want. If you are concerned about disk space usage exceeding predefined limits and do not want to go the way of external quota regulations, I suggest staying with the combined table space settings (i. e. no innodb_file_per_table) and remove the :autoextend from the configuration.

That way you still will not get user or schema specific limits, but at least prevent the disk from being filled up with data, because the table space will not grow past its initial size in this setup. With innodb_file_per_table there unfortunately is no way to configure each of them to stop at a certain maximum size.

This is one of the aspects MySQL differs from other, supposedly more enterprise-level databases. Don't get me wrong though, we use InnoDB with lots of data in several thousand installations, so it has certainly proven to be ready for production grade. Only the management features are a little lacking at times.

13.10.2009 17:18:06
That is a good point that MySQL probably wouldn't like running out of disk space. The innodb_file_per_table could be used to help maintain soft limits, since you'd know roughly how much disk space each database is using.
James McNellis 13.10.2009 17:32:32
Innodb should tolerate running out of disc space quite nicely. MyISAM does not though; it blocks the process forever (this is because it does not have transactions so it cannot roll back any work it has already done). Do please test what happens when InnoDB runs out of disc space. You need innodb_file_per_table.
MarkR 14.10.2009 06:30:35
Also note that you cannot STOP users from creating MyISAM tables very easily - the engine is built into mysql and not removable. Also temporary tables etc can use it. And Innodb temporary tables still go into the big "ibdata1" file when file_per_table is enabled.
MarkR 14.10.2009 06:31:35