FIXME: most of this is general SQL stuff, applicable to Club SQL too.
Routine Maintenance
Giving Orgs Customized Databases
Presently we don't have a system for creating org databases (nevermind access to orgtracker/thebridge officer tables), so it's done manually. Very easy...
#come up with a password $ pwgen -s 12 #log into contrib-db as the root user $ ssh contrib-db $ mysql -u root -p #ask around for the mysql root password #create database and grant access, implicitly creating the user mysql> create database org_<shortname> mysql> grant select,insert,update,delete,create,drop,references,index,alter,create temporary tables,lock tables on org_<shortname>.* to org_<shortname>@'%' identified by '<password>';
Infrastructure Design
Disk Sizing
Since mysql uses it for scratch space, /tmp on any machine running mysql should be 2-3 times of the expected maximum size of the largest table on which serious operations will be performed. Similarly, the data partition should be sized with a buffer of the same amount. At least in /tmp, the scratch file is deleted after creation with the fd still open (invisible tmp file), so look at df and /proc/<..>/fd and not ls. Current behavior (c. version 5.1) is to block the query (often holding locks) on disk fillup, which is usually worthless unless there's stuff to delete and it's noticed quickly. If the misbehaving thread is killed, the query failure will rollback the transaction (innodb) possibly very resource-intensively or depending on the operation and db format, will require a table repair (e.g., myisamchk).
Resource Limits
Often, a large query can hose the entire machine by gobbling all CPU and RAM and filling /tmp with temporary table data. To reduce the impact of this, we set an RSS rlimit by running ulimit -m <...> in the mysqld startup script. Because no good place to insert "local" startup stuff seems to exist, this was just stuck somewhere in /usr/sbin/mysqld_safe and will get lost on a reinstall or upgrade.
Database Quotas
Mysql does not have an internal mechanism for limiting database or table sizes. Attempting to use fs quotas is hard (mysqld runs as one user), and will break things when a quota is hit (mysqld doesn't handle write errors very gracefully).
The most common way to apply quotas is to use a monitoring daemon or cron script that checks sizes and revokes insert/update/create rights to a database that is too big. Ultimately, it should mail the user warning them and automatically re-grant access when things get small enough again. Examples of programs that implement this general idea:
Since we haven't had an issue with anyone abusing database sizes so far, we haven't implemented anything like this, but it shouldn't be terribly hard if we needed it.