SEO

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

> -----Original Message-----
> From: Artem Kuchin [mailto:matrix@itlegion.ru]
> 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?

Steve

Artem

set

my.cnf in unix
my.ini in windows
set max_connections

max_connections=100

M--
----- Original Message -----
From: "Steven Buehler" <steve@ibushost.com>
To: "'Artem Kuchin'" <matrix@itlegion.ru>; <mysql@lists.mysql.com>
Sent: Friday, December 07, 2007 8:11 AM
Subject: RE: How to auto repair db on hosting with quota


> > -----Original Message-----
> > From: Artem Kuchin [mailto:matrix@itlegion.ru]
> > 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?
>
> Steve
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=mgainty@hotmail.com
>
>

Steven Buehler wrote:
>> -----Original Message-----
>> From: Artem Kuchin [mailto:matrix@itlegion.ru]
>> 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

I see topposting i popular in this mail list

Well, thank you for the idea. It seems to be workable. I
hope that while data is being repaired and it is located
in tmp the rights are set correctly so noone else can read the
files. I'll see it myself. Thank you very much.

Any idea on how to limit connection per user? (so one user
cannot abuse the server and take all max_connections connections
for himself).

--
Regards,
Artem

Russell E Glaue wrote:
> 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.
> 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

Regards,
Baron

Yes it is. Even worse, people love to convert a top-posted thread to
bottom-posted and verse vice-a!

> I see topposting i popular in this mail list

And now for the bottom-posting part of this reply...

> Any idea on how to limit connection per user? (so one user
> cannot abuse the server and take all max_connections connections
> for himself).

See http://dev.mysql.com/doc/refman/5.0/en/grant.html and look for the
GRANT OPTION options.

Those of us who view our mail in threads also post in threads. ;-)

Here is some info for your concerns.

We assume the client is going to run their database under their client
unix-system username.
You specify the temp directory in the my.cnf file.
The documentation (when I last read it) says temp tables are created in the temp
directory.
If MySQL does not set the permissions you want on temp files, you can set the
shell umask so permissions can be read-only to the user.

umask = The user file-creation mask is set to mode.

any-unix$ man umask

-RG


Artem Kuchin wrote:
>
> I see topposting i popular in this mail list
>
> Well, thank you for the idea. It seems to be workable. I hope that
> while data is being repaired and it is located
> in tmp the rights are set correctly so noone else can read the
> files. I'll see it myself. Thank you very much.
>
> Any idea on how to limit connection per user? (so one user
> cannot abuse the server and take all max_connections connections
> for himself).
>
> --
> Regards,
> Artem
>
> Russell E Glaue wrote:
>> 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. 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
>

Baron Schwartz wrote:
> Regards,
> Baron
>
> Yes it is. Even worse, people love to convert a top-posted thread to
> bottom-posted and verse vice-a!
>
>> I see topposting i popular in this mail list
>
> And now for the bottom-posting part of this reply...
>
>> Any idea on how to limit connection per user? (so one user
>> cannot abuse the server and take all max_connections connections
>> for himself).
>
> See http://dev.mysql.com/doc/refman/5.0/en/grant.html and look for the
> GRANT OPTION options.

Argh!!

"The MAX_USER_CONNECTIONS count option, implemented in MySQL 5.0.3, limits the maximum number of simultaneous connections that the
account can make. If count is 0 (the default), the max_user_connections system variable determines the number of simultaneous
connections for the account. "

We are still kinda stuch to 4.1. Well, its time to upgrade.

--
Regards,
Artem

I won't convert thread style, so, i'm top-posting

All this would be great if it worked. The main problem is that
temporary directory option for mysqld iS NOT USED while doing
table repair. All files while repairing tables are placed
in the same directoty with the same owner and group as the original
database files (i mean, for example, .TMD files which can be huge!).

So, i am currenctly stuck with no solultion to the problem.

--
Artem



Russell E Glaue wrote:
> 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

Hmm..,
I thought the temp tables were created in the temp directory.
What version of MySQL are you using?

Perhaps you can temporarily increase the user's hard_limit, and not increase the soft_limit. When they have finished recovering, you can reset the hard_limit.

Or perhaps it is possible to copy the corrupted database to the temp directory (with increased hard_limit), perform the recovery there, then copy it back.
The data directory does not have to remain in the same location in order to be recoverable.

This would be the same as the last e-mailed proposed solution, but with two extra steps to copy back and forth from the temp directory.

-RG


----- Original Message -----
From: "Artem Kuchin" <matrix@itlegion.ru>
To: "Russell E Glaue" <rglaue@cait.org>, mysql@lists.mysql.com
Sent: Sunday, December 9, 2007 9:03:45 AM (GMT-0600) America/Chicago
Subject: Re: How to auto repair db on hosting with quota

I won't convert thread style, so, i'm top-posting

All this would be great if it worked. The main problem is that
temporary directory option for mysqld iS NOT USED while doing
table repair. All files while repairing tables are placed
in the same directoty with the same owner and group as the original
database files (i mean, for example, .TMD files which can be huge!).

So, i am currenctly stuck with no solultion to the problem.

--
Artem



Russell E Glaue wrote:
> 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