Steven Buehler wrote:
-----Original Message-----
From: Artem Kuchin [mailto:[EMAIL PROTECTED]
Sent: Friday, December 07, 2007 4:35 AM
To: mysql@lists.mysql.com
Subject: How to auto repair db on hosting with quota
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.
Have you tried doing the repair as the root user for MySQL instead of
the client user?
It does not help. Quota is set for GROUP, so, when mysql create a temporary file
in the database directory group is inhereted from this directory, and group is
the
user group, so, the temporary file is counted in the user quota.
--
Regards,
Artem
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
