Using Quota on Unix, you can specify a different quota soft_limit for temporary space. You can set MySQL to use /tmp as the temporary space, then set a soft_limit of 10mb and a hard_limit of 300MB. This quota should be separate from the normal user space quota.
With MySQL temp dir set to /tmp, and a soft/hard quota as 10MB/300MB, the recovery should occur successfully. And going above the soft_limit gives the user X amount of days to get back down below the soft_limit. And the user cannot go above the hard_limit. You can set the soft_limit recovery days to like 1 day. They can go over the 10MB soft_limit for 1 day, up to the hard_limit of 300MB, but after one day the temp files will be deleted until the soft_limit is satisfied. This is a great way to control temp space, and not let users abuse it, but still give them a lot of space to perform things like large mysql database recoveries. -RG Artem Kuchin wrote: > I am asking this question as a hosting PROVIDER, not > as a hosting client. > > The sitation is like this (this is a simple example with small > numbers): > > 1) a client has 100mb quota for site and db files (set on unix group) > 2) 80mb is already taken by 75MB database in ONE TABLE and 5mb of files > 3) databases gets broken > 4) mysql tried to auto repair the table > 5) to repair data it must create a temporary file and this files take > almost as much as 75MB > 6) user goes over quota becauase mysql needs another 75MB to reapir table, > and this summs up like 75+75+5 a lot > than 100mb in quota > 7) mysql gets stuck on this db > 8) people continue to acccess site and connections build up and each > connection waits for repair to be finished > 9) eventually mysql runs out of connections and this stop other clients > too. > 10) day is ruined. > > SO the questions are: > > 1) Is the way to specify USER/GROUP and PLACE for temporary files > for table repair? > 2) Maybe there is some OTHER way to set quota for databases? > 3) Is there way to specify max number of connections for a user? > > Current solution for this as i see it is to make a daemon which > must connect to database first with administrative privs and > monitor number of connections and repairs using processlist. If some user > does more than allowed number of connection then just kill them off. If > repair accures - then turn off that host for a while and turn if back on > again > when repair finished. > > I wonder how it is solved on mass hosting servers. > > -- > Regards, > Artem > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
