Thread: SOC & user quotas
Hello hackers, I was starting to think about next SOC and the project for it. And for a long time I wanted to implement the user quotas in PG. So, I'll try to explain my understanding of the implementation, and I'll be happy to hear any comments, objections, or pointings to my misunderstanding. This is very first very rough idea, but I still would like to hear whether it contains some obvious flaws... 1) The main idea is to implement the per-user quota (not per tablespace for example). So, during the creation of the new user some quota can be specified, and after that the size of all the relations *owned* by that user should be limited by that number. 2) I looked into the code, and from my understanding, the main part of the code which should be affected by the quotas is storage/smgr/md.c. If I understand correctly, only functions like mdcreate & mdextend really change the size of the user relations (I don't consider things like WAL, and I don't think it should be subject for quota). And it seems to me, that the machinery of smgr/md is moreless enough to control the space occupied by the relations (within some 1 block size precision). 3) How the quota should be controlled: I think, that generally, for all the users which have quotas, the shared memory should contain the number of blocks left from the quota. And each backend extending or truncating the relations owned by the user should appropriately change that number of blocks left in the shared memory. As soon as this number is equal to zero, all the mdcreate, mdextend functions shouldn't do anything but return the error. I don't know, but I hope these functions won't be invoked if the user will do DELETE and/or VACUUM to recover the space ? Also, I'm not completely sure that refusing the call of the mdextend function in the case of quota excess won't lead to any corruption ? (in the case of Btree splits for example ). Any comments ? Thank you. Regards, Sergey ******************************************************************* Sergey E. Koposov Max Planck Institute for Astronomy/Cambridge Institute for Astronomy/Sternberg Astronomical Institute Tel: +49-6221-528-349 Web: http://lnfm1.sai.msu.ru/~math E-mail: math@sai.msu.ru
"Sergey E. Koposov" <math@sai.msu.ru> writes:
> 1) The main idea is to implement the per-user quota (not per tablespace
> for example). So, during the creation of the new user some quota can be
> specified, and after that the size of all the relations *owned* by that
> user should be limited by that number.
This seems impractical as stated; there is no way to determine what a
user owns in some other database. Possibly you could do it if the quota
were both per-user and per-database.
> 2) I looked into the code, and from my understanding, the main part of the
> code which should be affected by the quotas is storage/smgr/md.c.
md.c is too low level to do catalog accesses and thus too low level to
know who owns what.
> 3) How the quota should be controlled: I think, that generally, for all
> the users which have quotas, the shared memory should contain the number
> of blocks left from the quota. And each backend extending or truncating
> the relations owned by the user should appropriately change that number of
> blocks left in the shared memory.
What will you do with ALTER TABLE OWNER? What if such a command is
rolled back? (Likewise for some other commands such as TRUNCATE, or
even just DROP TABLE.) What if there are too many users to fit in your
(necessarily fixed size) shared memory area? What sort of contention
will there be for access to this area?
regards, tom lane
Sergey E. Koposov wrote:
> Hello hackers,
>
> I was starting to think about next SOC and the project for it. And for a
> long time I wanted to implement the user quotas in PG.
> So, I'll try to explain my understanding of the implementation, and I'll
> be happy to hear any comments, objections, or pointings to my
> misunderstanding. This is very first very rough idea, but I still would
> like to hear whether it contains some obvious flaws...
>
> 1) The main idea is to implement the per-user quota (not per tablespace
> for example). So, during the creation of the new user some quota can be
> specified, and after that the size of all the relations *owned* by that
> user should be limited by that number.
I could see this being useful per database, maybe. It seems like kind of
an odd feature.
Sincerely,
Joshua D. Drake
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
On Wed, 28 Feb 2007, Tom Lane wrote: > "Sergey E. Koposov" <math@sai.msu.ru> writes: >> 1) The main idea is to implement the per-user quota (not per tablespace >> for example). So, during the creation of the new user some quota can be >> specified, and after that the size of all the relations *owned* by that >> user should be limited by that number. > > This seems impractical as stated; there is no way to determine what a > user owns in some other database. Possibly you could do it if the quota > were both per-user and per-database. yes, agreed. I didn't think of that. >> 3) How the quota should be controlled: I think, that generally, for all >> the users which have quotas, the shared memory should contain the number >> of blocks left from the quota. And each backend extending or truncating >> the relations owned by the user should appropriately change that number of >> blocks left in the shared memory. > > What will you do with ALTER TABLE OWNER? What if such a command is > rolled back? I don't know, but I guess the ALTER OWNER should be considered differently. It probably should proceed only if it sees that there are enough place to perform the whole operation. If there are, then it should block any writing to the tables of the user, perform the alter owner and unblock everything again. > (Likewise for some other commands such as TRUNCATE, or > even just DROP TABLE.) I didn't think of yet, but I will. > What if there are too many users to fit in your > (necessarily fixed size) shared memory area? We really don't need to create the array for all users. We only need to create that array for users 1) having quotas 2) the users, whose tables are accessed at the moment So I don't think that in that case the amount of required space is a problem here. > What sort of contention > will there be for access to this area? I think, that the only requirement is that the incrementation or decrementation of number of blocks left for each user should be atomic operation. regards, Sergey ******************************************************************* Sergey E. Koposov Max Planck Institute for Astronomy/Cambridge Institute for Astronomy/Sternberg Astronomical Institute Tel: +49-6221-528-349 Web: http://lnfm1.sai.msu.ru/~math E-mail: math@sai.msu.ru
On Wed, 28 Feb 2007, Joshua D. Drake wrote: > I could see this being useful per database, maybe. It seems like kind of > an odd feature. Per user AND per database (as Tom noted). But I dont see what's odd in it... It exists in Oracle, and I need quotas in the project on which I'm working. And I remember user requests for quotas in the mailing lists ... regards, Sergey ******************************************************************* Sergey E. Koposov Max Planck Institute for Astronomy/Cambridge Institute for Astronomy/Sternberg Astronomical Institute Tel: +49-6221-528-349 Web: http://lnfm1.sai.msu.ru/~math E-mail: math@sai.msu.ru
Sergey E. Koposov wrote: > On Wed, 28 Feb 2007, Joshua D. Drake wrote: > >> I could see this being useful per database, maybe. It seems like kind of >> an odd feature. > > Per user AND per database (as Tom noted). But I dont see what's odd in > it... It exists in Oracle, and I need quotas in the project on which I'm > working. And I remember user requests for quotas in the mailing lists ... Well Oracle isn't really our goal is it? I am not questioning that you are well intended but I just don't see a use case. For example, what happens if I hit my quota? Joshua D. Drake > > regards, > Sergey > > ******************************************************************* > Sergey E. Koposov > Max Planck Institute for Astronomy/Cambridge Institute for > Astronomy/Sternberg Astronomical Institute > Tel: +49-6221-528-349 > Web: http://lnfm1.sai.msu.ru/~math > E-mail: math@sai.msu.ru > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/
On Wed, 28 Feb 2007, Joshua D. Drake wrote: > Sergey E. Koposov wrote: >> On Wed, 28 Feb 2007, Joshua D. Drake wrote: >> Per user AND per database (as Tom noted). But I dont see what's odd in >> it... It exists in Oracle, and I need quotas in the project on which I'm >> working. And I remember user requests for quotas in the mailing lists ... > > Well Oracle isn't really our goal is it? I am not questioning that you > are well intended but I just don't see a use case. > > For example, what happens if I hit my quota? Then you cannot run any queries that extend the size of your relations (for example INSERT, UPDATE etc.). Unless you drop your tables or DELETE something The use case for that is the situation when you provide the access to different people to do something on the DB. The real world example (in which I'm interested) is when the large science project produce a huge amount of data, store it in large database, and let different scientists work on that data, having their little accounts there. (example http://casjobs.sdss.org/CasJobs/Guide.aspx ). That's the way how most of large astronomical projects start to work now. Regards, Sergey ******************************************************************* Sergey E. Koposov Max Planck Institute for Astronomy/Cambridge Institute for Astronomy/Sternberg Astronomical Institute Tel: +49-6221-528-349 Web: http://lnfm1.sai.msu.ru/~math E-mail: math@sai.msu.ru
> Then you cannot run any queries that extend the size of your relations > (for example INSERT, UPDATE etc.). Unless you drop your tables or DELETE > something Interesting. Well my two cents is don't go any deeper than database. I.e; don't try and track to the individual relation. Joshua D. Drake > > The use case for that is the situation when you provide the access to > different people to do something on the DB. The real world example (in > which I'm interested) is when the large science project produce a huge > amount of data, store it in large database, and let different scientists > work on that data, having their little accounts there. (example > http://casjobs.sdss.org/CasJobs/Guide.aspx ). That's the way how most of > large astronomical projects start to work now. > > Regards, > Sergey > > ******************************************************************* > Sergey E. Koposov > Max Planck Institute for Astronomy/Cambridge Institute for > Astronomy/Sternberg Astronomical Institute > Tel: +49-6221-528-349 > Web: http://lnfm1.sai.msu.ru/~math > E-mail: math@sai.msu.ru > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/
"Sergey E. Koposov" <math@sai.msu.ru> writes:
> Per user AND per database (as Tom noted). But I dont see what's odd in
> it... It exists in Oracle, and I need quotas in the project on which I'm
> working. And I remember user requests for quotas in the mailing lists ...
It hasn't ever made it onto the TODO list, which means there's not a
consensus that we need it. If it were a simple, small, low-impact patch
then you probably wouldn't need to do much convincing that it's an
important feature to have, but I'm afraid the patch will be none of
those things.
regards, tom lane
Tom Lane wrote:
> "Sergey E. Koposov" <math@sai.msu.ru> writes:
>> Per user AND per database (as Tom noted). But I dont see what's odd in
>> it... It exists in Oracle, and I need quotas in the project on which I'm
>> working. And I remember user requests for quotas in the mailing lists ...
>
> It hasn't ever made it onto the TODO list, which means there's not a
> consensus that we need it. If it were a simple, small, low-impact patch
> then you probably wouldn't need to do much convincing that it's an
> important feature to have, but I'm afraid the patch will be none of
> those things.
Tom what about at just the DB level?
E.g; if user foo then pg_database_size may not be > than X?
I guess the big question would be when do we check though? At each
transaction seems like it would add significant overhead, especially if
we had to rollback the transaction because it was going to go over their
quota.
Egad.
Joshua D. Drake
>
> regards, tom lane
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
On Wed, 28 Feb 2007, Tom Lane wrote: >> 2) I looked into the code, and from my understanding, the main part of the >> code which should be affected by the quotas is storage/smgr/md.c. > > md.c is too low level to do catalog accesses and thus too low level to > know who owns what. That's probably a dumb question(I dont know the PG infrastructrure that well), but Is it possible to put the information about the owner into SMgrRelation/Relation structures? As I see the smgrextend() in smgr.c get the SMgrRelation agrument... regards, Sergey ******************************************************************* Sergey E. Koposov Max Planck Institute for Astronomy/Cambridge Institute for Astronomy/Sternberg Astronomical Institute Tel: +49-6221-528-349 Web: http://lnfm1.sai.msu.ru/~math E-mail: math@sai.msu.ru
On Wed, Feb 28, 2007 at 09:58:52AM -0800, Joshua D. Drake wrote: > E.g; if user foo then pg_database_size may not be > than X? > > I guess the big question would be when do we check though? At each > transaction seems like it would add significant overhead, especially if > we had to rollback the transaction because it was going to go over their > quota. Generally, rolling back a transaction doesn't reduce the amount of disk used. Only VACUUM FULL actually shrinks relations. Seem to me if the RelationOpen stores a pointer to a counter that gets incremented on mdextend, it should work reasonably well. Extending doesn't happen that often relative to other database activity. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Martijn van Oosterhout wrote:
> On Wed, Feb 28, 2007 at 09:58:52AM -0800, Joshua D. Drake wrote:
>> E.g; if user foo then pg_database_size may not be > than X?
>>
>> I guess the big question would be when do we check though? At each
>> transaction seems like it would add significant overhead, especially if
>> we had to rollback the transaction because it was going to go over their
>> quota.
>
> Generally, rolling back a transaction doesn't reduce the amount of disk
> used. Only VACUUM FULL actually shrinks relations.
Right, but what I mean was -- if we rollback because we hit quota we
could potentially cause even more maintenance to have to happen (vacuum).
J
>
> Seem to me if the RelationOpen stores a pointer to a counter that gets
> incremented on mdextend, it should work reasonably well. Extending
> doesn't happen that often relative to other database activity.
>
> Have a nice day,
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
On Wed, 28 Feb 2007, Joshua D. Drake wrote:
> Tom Lane wrote:
>> "Sergey E. Koposov" <math@sai.msu.ru> writes:
>>> Per user AND per database (as Tom noted). But I dont see what's odd in
>>> it... It exists in Oracle, and I need quotas in the project on which I'm
>>> working. And I remember user requests for quotas in the mailing lists ...
>>
>> It hasn't ever made it onto the TODO list, which means there's not a
>> consensus that we need it. If it were a simple, small, low-impact patch
>> then you probably wouldn't need to do much convincing that it's an
>> important feature to have, but I'm afraid the patch will be none of
>> those things.
We need this kind of feature in our scientific project I and Sergey are
working on. We provide access to big pool of astronomical catalogs and
ability to match users data with these huge catalogs and we want to
be able to provide sort of QoS.
Pg became very popular in Russia, especially after the biggest
accounting and enteprize management software developer "1C"
(about 800,000 installations) has been supporting Pg and I expect
a large interest to Pg this year, especially from the application providers,
shared environment.
btw, this should be announced in -advocacy, I and Teodor
worked on Pg port, some patches we have submitted was grown from that work.
Regards, Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
"Joshua D. Drake" <jd@commandprompt.com> writes:
> Martijn van Oosterhout wrote:
>> Generally, rolling back a transaction doesn't reduce the amount of disk
>> used. Only VACUUM FULL actually shrinks relations.
> Right, but what I mean was -- if we rollback because we hit quota we
> could potentially cause even more maintenance to have to happen (vacuum).
It's worse than that, because VACUUM FULL will actually bloat the
indexes on the way to being able to reduce the table size (since it has
to make new index entries for rows it moves). If the limit is strictly
enforced then a user who has reached his quota is really totally
screwed: the only easy way to get back under quota will be to completely
drop tables, ie, discard data. VACUUM probably won't reduce the
physical table size much, and VACUUM FULL will fail, and other
approaches such as CLUSTER won't work either.
[ thinks for a bit... ] Possibly you could drop all your indexes,
VACUUM FULL, reconstruct indexes. But it would be painful and would
certainly prevent you from working normally until you finish that
maintenance. If the quota limit includes temp files you might find that
rebuilding the indexes fails, too, because of the transient space needed
to rebuild.
Plus, all that forced maintenance activity will be degrading response
for other users while it happens.
On the whole I'm not convinced that a quota is a good idea.
regards, tom lane
On Wed, 28 Feb 2007, Tom Lane wrote:
> "Joshua D. Drake" <jd@commandprompt.com> writes:
>> Martijn van Oosterhout wrote:
>>> Generally, rolling back a transaction doesn't reduce the amount of disk
>>> used. Only VACUUM FULL actually shrinks relations.
>
>> Right, but what I mean was -- if we rollback because we hit quota we
>> could potentially cause even more maintenance to have to happen (vacuum).
>
> It's worse than that, because VACUUM FULL will actually bloat the
> indexes on the way to being able to reduce the table size (since it has
> to make new index entries for rows it moves). If the limit is strictly
> enforced then a user who has reached his quota is really totally
> screwed: the only easy way to get back under quota will be to completely
> drop tables, ie, discard data. VACUUM probably won't reduce the
> physical table size much, and VACUUM FULL will fail, and other
> approaches such as CLUSTER won't work either.
>
> [ thinks for a bit... ] Possibly you could drop all your indexes,
> VACUUM FULL, reconstruct indexes. But it would be painful and would
> certainly prevent you from working normally until you finish that
> maintenance. If the quota limit includes temp files you might find that
> rebuilding the indexes fails, too, because of the transient space needed
> to rebuild.
>
> Plus, all that forced maintenance activity will be degrading response
> for other users while it happens.
>
> On the whole I'm not convinced that a quota is a good idea.
On database level it's possible to have soft user quote, just measure
disk usage and warn user if database size is over. This could be realized
using external tools. But Sergey wanted finer granulation. As a workaround,
we could have function which return size of db objects owned by user and
let administrator run cron job to realize soft quota. This will not provide
foundation for enterprize level of QoS, but we certainly don't want to
introduce too much overhead. It's interesting and challenging task though.
Regards, Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
On Wed, 28 Feb 2007, Tom Lane wrote: > "Joshua D. Drake" <jd@commandprompt.com> writes: >> Martijn van Oosterhout wrote: >>> Generally, rolling back a transaction doesn't reduce the amount of disk >>> used. Only VACUUM FULL actually shrinks relations. > >> Right, but what I mean was -- if we rollback because we hit quota we >> could potentially cause even more maintenance to have to happen (vacuum). > > It's worse than that, because VACUUM FULL will actually bloat the > indexes on the way to being able to reduce the table size (since it has > to make new index entries for rows it moves). If the limit is strictly > enforced then a user who has reached his quota is really totally > screwed: the only easy way to get back under quota will be to completely > drop tables, ie, discard data. VACUUM probably won't reduce the > physical table size much, and VACUUM FULL will fail, and other > approaches such as CLUSTER won't work either. I don't know, but in my opinion, I don't see anything bad in requiring dropping the data if the quota is full. That's what usually occurs in the case of normal filesystem quota... If you don't have a space there, you cannot edit files, copy them etc... And that solution should be definitely better than the filesystem quota for the PostgreSQL user for example. regards, Sergey ******************************************************************* Sergey E. Koposov Max Planck Institute for Astronomy/Cambridge Institute for Astronomy/Sternberg Astronomical Institute Tel: +49-6221-528-349 Web: http://lnfm1.sai.msu.ru/~math E-mail: math@sai.msu.ru
On Wed, Feb 28, 2007 at 03:57:56PM -0500, Tom Lane wrote: > It's worse than that, because VACUUM FULL will actually bloat the > indexes on the way to being able to reduce the table size (since it has > to make new index entries for rows it moves). If the limit is strictly I was thinking that indexes and temp tables wouldn't be counted. I thought it was more of a "stop people using up lots of disk space" rather than specifically stopping at a hard limit. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
> I don't know, but in my opinion, I don't see anything bad in requiring > dropping the data if the quota is full. That's what usually occurs in > the case of normal filesystem quota... If you don't have a space there, > you cannot edit files, copy them etc... > And that solution should be definitely better than the filesystem quota > for the PostgreSQL user for example. The bad point is not that we would rollback the transaction. The bad point is what happens when you need to rollback a transaction and in your scenario it is quite plausible that a large rollback could occur, more than once, causing the requirement of something like a vacuum full to clean things up. Sincerely, Joshua D. Drake > > regards, > Sergey > ******************************************************************* > Sergey E. Koposov > Max Planck Institute for Astronomy/Cambridge Institute for > Astronomy/Sternberg Astronomical Institute > Tel: +49-6221-528-349 > Web: http://lnfm1.sai.msu.ru/~math > E-mail: math@sai.msu.ru > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/
On Wed, Feb 28, 2007 at 12:56:13PM -0500, Tom Lane wrote: > It hasn't ever made it onto the TODO list, which means there's not a > consensus that we need it. Such a patch could improve the acceptance of PostgreSQL in shared hosting environments. Note that a database without quotas can be filled up easily and the database will stop serving requests to other users' databases. There is a quota implementation already in the archives but I don't know more about it than that it exists: http://archives.postgresql.org/pgsql-hackers/2004-07/msg00392.php Joachim
On Wed, 28 Feb 2007, Joshua D. Drake wrote: > The bad point is not that we would rollback the transaction. The bad > point is what happens when you need to rollback a transaction and in > your scenario it is quite plausible that a large rollback could occur, > more than once, causing the requirement of something like a vacuum full > to clean things up. Yes, I understand, but I think, the scenario of working and always hitting a quota is not normal. I think you shouldn't expect perfect, stable performance etc. in that case. It's similar to if you'll try to work with PG with very little memory and/or shared memory. You can work, but you can easily hit these limits, and than something will not work... regards, Sergey ******************************************************************* Sergey E. Koposov Max Planck Institute for Astronomy/Cambridge Institute for Astronomy/Sternberg Astronomical Institute Tel: +49-6221-528-349 Web: http://lnfm1.sai.msu.ru/~math E-mail: math@sai.msu.ru
On Wed, 28 Feb 2007, Joachim Wieland wrote: > On Wed, Feb 28, 2007 at 12:56:13PM -0500, Tom Lane wrote: >> It hasn't ever made it onto the TODO list, which means there's not a >> consensus that we need it. > > Such a patch could improve the acceptance of PostgreSQL in shared hosting > environments. Note that a database without quotas can be filled up easily > and the database will stop serving requests to other users' databases. Yes, I agree. > There is a quota implementation already in the archives but I don't know > more about it than that it exists: > > http://archives.postgresql.org/pgsql-hackers/2004-07/msg00392.php I remember that thread, but I think there was no patch at all, at least I didn't see it. Sergey ******************************************************************* Sergey E. Koposov Max Planck Institute for Astronomy/Cambridge Institute for Astronomy/Sternberg Astronomical Institute Tel: +49-6221-528-349 Web: http://lnfm1.sai.msu.ru/~math E-mail: math@sai.msu.ru
On Wednesday 28 February 2007 18:02, Sergey E. Koposov wrote: > On Wed, 28 Feb 2007, Joachim Wieland wrote: > > On Wed, Feb 28, 2007 at 12:56:13PM -0500, Tom Lane wrote: > >> It hasn't ever made it onto the TODO list, which means there's not a > >> consensus that we need it. > > > > Such a patch could improve the acceptance of PostgreSQL in shared hosting > > environments. Note that a database without quotas can be filled up easily > > and the database will stop serving requests to other users' databases. > > Yes, I agree. > > > There is a quota implementation already in the archives but I don't know > > more about it than that it exists: > > > > http://archives.postgresql.org/pgsql-hackers/2004-07/msg00392.php > > I remember that thread, but I think there was no patch at all, at least I > didn't see it. > Sergey, could you do a little research on what behavior other databases that support user quotes exhibit? This might help folks judge whether any proposed solution for postgres will be above or below potential users expectations. -- Robert Treat Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
On Wed, 28 Feb 2007, Robert Treat wrote: > > Sergey, could you do a little research on what behavior other databases that > support user quotes exhibit? This might help folks judge whether any > proposed solution for postgres will be above or below potential users > expectations. Ok, I will. Regards, Sergey ******************************************************************* Sergey E. Koposov Max Planck Institute for Astronomy/Cambridge Institute for Astronomy/Sternberg Astronomical Institute Tel: +49-6221-528-349 Web: http://lnfm1.sai.msu.ru/~math E-mail: math@sai.msu.ru
> > Then you cannot run any queries that extend the size of > your relations > > (for example INSERT, UPDATE etc.). Unless you drop your tables or > > DELETE something > > Interesting. Well my two cents is don't go any deeper than database. > I.e; don't try and track to the individual relation. I thought the intuitive level for such a feature would be tablespace. If you need per user each user would get it's own tablespace, if you need database each database ... Andreas
On Wed, Feb 28, 2007 at 02:29:52PM -0800, Joshua D. Drake wrote: > > > I don't know, but in my opinion, I don't see anything bad in requiring > > dropping the data if the quota is full. That's what usually occurs in > > the case of normal filesystem quota... If you don't have a space there, > > you cannot edit files, copy them etc... > > And that solution should be definitely better than the filesystem quota > > for the PostgreSQL user for example. > > The bad point is not that we would rollback the transaction. The bad > point is what happens when you need to rollback a transaction and in > your scenario it is quite plausible that a large rollback could occur, > more than once, causing the requirement of something like a vacuum full > to clean things up. ISTM that if the transaction is that big it's likely going to be extending the heap, which means you'd get space back on a plain vacuum. As for things like CLUSTER, and REINDEX it would probably be useful to make an exception, since we know that those operations are intended to shrink the size of a relation. I also think there's a lot to be said for a soft limit. -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
On Wed, 2007-02-28 at 12:56 -0500, Tom Lane wrote: > "Sergey E. Koposov" <math@sai.msu.ru> writes: > > Per user AND per database (as Tom noted). But I dont see what's odd in > > it... It exists in Oracle, and I need quotas in the project on which I'm > > working. And I remember user requests for quotas in the mailing lists ... > > It hasn't ever made it onto the TODO list, which means there's not a > consensus that we need it. If it were a simple, small, low-impact patch > then you probably wouldn't need to do much convincing that it's an > important feature to have, but I'm afraid the patch will be none of > those things. > If we allowed an admin to revoke CREATE from the pg_default tablespace, then anyone who needed that feature could limit people to their own tablespace (at which point it's the filesystem's job to limit their usage). Is there some reason that we can't do this now? Regards,Jeff Davis
Jeff Davis <pgsql@j-davis.com> writes:
> If we allowed an admin to revoke CREATE from the pg_default tablespace,
> then anyone who needed that feature could limit people to their own
> tablespace (at which point it's the filesystem's job to limit their
> usage). Is there some reason that we can't do this now?
The reason it appears not to work now is that the code is deliberately
set up not to check permissions on the database's default tablespace, on
the theory that if you have regular CREATE permissions in the database
then you probably are supposed to be able to create things. However,
if you want to restrict someone to a filesystem-limited tablespace, you
can surely make that tablespace be the default for their database.
This would make the quota include system catalogs as well as "user"
tables, which seems to me a good idea anyway because it's not hard for a
user to bloat the system catalogs. (The fact that pg_largeobject is a
communal table makes per-user quotas useless for BLOB users already.)
Now if you want quota-limited users to share a database with
not-quota-limited users, this doesn't work too well. The non-limited
users can surely be set up with access to a non-default tablespace,
but if the system catalogs hit the quota limit then everyone's gonna
be unhappy.
regards, tom lane
Jeff Davis wrote: > > If we allowed an admin to revoke CREATE from the pg_default tablespace, > then anyone who needed that feature could limit people to their own > tablespace (at which point it's the filesystem's job to limit their > usage). Is there some reason that we can't do this now? > > Hitting a file system quota limit is likely to bring you down pretty hard, isn't it? cheers andrew.
Andrew Dunstan wrote: > Jeff Davis wrote: >> >> If we allowed an admin to revoke CREATE from the pg_default tablespace, >> then anyone who needed that feature could limit people to their own >> tablespace (at which point it's the filesystem's job to limit their >> usage). Is there some reason that we can't do this now? >> >> > > > Hitting a file system quota limit is likely to bring you down pretty > hard, isn't it? Yes, and likely corrupt the database. Joshua D. Drake > > cheers > > andrew. > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/
"Joshua D. Drake" <jd@commandprompt.com> writes:
> Andrew Dunstan wrote:
>> Hitting a file system quota limit is likely to bring you down pretty
>> hard, isn't it?
> Yes, and likely corrupt the database.
As long as you don't keep WAL on the restricted filesystem, it won't
stop or corrupt your database. Whether you can get anything much done
is another story :-(
regards, tom lane
Tom Lane wrote: > "Joshua D. Drake" <jd@commandprompt.com> writes: > >> Andrew Dunstan wrote: >> >>> Hitting a file system quota limit is likely to bring you down pretty >>> hard, isn't it? >>> > > >> Yes, and likely corrupt the database. >> > > As long as you don't keep WAL on the restricted filesystem, it won't > stop or corrupt your database. Whether you can get anything much done > is another story :-( > > > Yeah. Including recovery. Maybe we could do something that would work in cooperation with FS quotas - I have no idea what though. cheers andrew
On Thu, 2007-03-01 at 14:13 -0800, Joshua D. Drake wrote: > Andrew Dunstan wrote: > > Jeff Davis wrote: > >> > >> If we allowed an admin to revoke CREATE from the pg_default tablespace, > >> then anyone who needed that feature could limit people to their own > >> tablespace (at which point it's the filesystem's job to limit their > >> usage). Is there some reason that we can't do this now? > >> > >> > > > > > > Hitting a file system quota limit is likely to bring you down pretty > > hard, isn't it? > > Yes, and likely corrupt the database. > Is it possible to make the database safe in this case? I can see why it would be difficult to handle pg_xlog filling up (or for that matter pg_log), but couldn't any other area be turned into just a simple ERROR? pg_xlog is cluster-wide, so even if you had the databases in separate quota-limited areas, that wouldn't mean pg_xlog would fill up. If it's possible to allow disk-is-full to become a simple error, that might satisfy this request without much effort. If it is a lot of effort, then I retract my suggestion. By the way, is there some good documentation about what actually happens in different cases when the disk is full? I treat it as "never let this happen" and I don't really know what to expect if it did happen. Regards,Jeff Davis
Jeff Davis <pgsql@j-davis.com> writes:
> Is it possible to make the database safe in this case? I can see why it
> would be difficult to handle pg_xlog filling up (or for that matter
> pg_log), but couldn't any other area be turned into just a simple ERROR?
It is --- Josh is mistaken about the severity of the code's response to
out-of-space. We have to panic if out of WAL or CLOG space because we
can't advance the transaction counter anymore in such cases, but
otherwise it's just ERROR. See the archives for some recorded cases of
PG's response to out-of-space situations.
The real problem though is whether you can get anything much done if up
against a hard limit; especially if that limit also affects the system
catalogs. Remember that UPDATE requires the ability to insert new tuple
versions, so there are a whole lot of things that will draw that ERROR.
regards, tom lane
On Thu, 2007-03-01 at 17:53 -0500, Tom Lane wrote: > The real problem though is whether you can get anything much done if up > against a hard limit; especially if that limit also affects the system > catalogs. Remember that UPDATE requires the ability to insert new tuple > versions, so there are a whole lot of things that will draw that ERROR. > You can pg_dump, drop the database, and make it again (maybe after deleting a few lines). I know it's not pretty, but the request is mostly centered around virtual hosting. The admin can easily generate emails when space is low, and when someone actually hits the quota the admin does have a path to get them out of the mess without disturbing other customers. It's not fool-proof. Someone can generate huge amounts of WAL traffic, hog the CPU, use the RAM, all kinds of things. But virtual hosting can't easily prevent that kind of thing anyway. So it seems like we already have a solution to quotas at the database level. Another point is that shared virtual hosting is becoming less important as OS virtualization (like Xen) becomes more prevalent. Regards,Jeff Davis
> So it seems like we already have a solution to quotas at the database > level. I still don't understand how FS quotas can help with DB quotas. .. All the FS quotas are setup for limiting the space for specific user and specific mount point. AFAIK They do not allow to limit the space in one directory. And since all the PG tablespaces will be owned by postgres user, I dont think the FS quotas can do the job. regards, Sergey ******************************************************************* Sergey E. Koposov Max Planck Institute for Astronomy/Cambridge Institute for Astronomy/Sternberg Astronomical Institute Tel: +49-6221-528-349 Web: http://lnfm1.sai.msu.ru/~math E-mail: math@sai.msu.ru
"Sergey E. Koposov" <math@sai.msu.ru> writes:
> I still don't understand how FS quotas can help with DB quotas. ..
> All the FS quotas are setup for limiting the space for specific user and
> specific mount point. AFAIK They do not allow to limit the space in one
> directory. And since all the PG tablespaces will be owned by postgres
> user, I dont think the FS quotas can do the job.
Well, you have to put the tablespace on a mount point that's not
otherwise used by Postgres, but beyond that I don't see the problem.
The point here is not that it's amazingly simple or convenient to use;
obviously it isn't. The point is that it's *possible* to get this sort
of effect today, and so we have to ask whether it's really worth a good
deal of development effort and code complexity to provide somewhat
more convenient, but basically duplicate, functionality within Postgres.
regards, tom lane
On Thu, 1 Mar 2007, Tom Lane wrote: > "Sergey E. Koposov" <math@sai.msu.ru> writes: >> I still don't understand how FS quotas can help with DB quotas. .. >> All the FS quotas are setup for limiting the space for specific user and >> specific mount point. AFAIK They do not allow to limit the space in one >> directory. And since all the PG tablespaces will be owned by postgres >> user, I dont think the FS quotas can do the job. > > Well, you have to put the tablespace on a mount point that's not > otherwise used by Postgres, but beyond that I don't see the problem. > And if you have 5-10 users which should be under quota , you need 5-10 different mount points. From my humble opinion that's an absolutely crazy solution and nobody will ever do that. > The point here is not that it's amazingly simple or convenient to use; > obviously it isn't. The point is that it's *possible* to get this sort > of effect today, and so we have to ask whether it's really worth a good > deal of development effort and code complexity to provide somewhat > more convenient, but basically duplicate, functionality within Postgres. > Yes, I completely agree that it's up to the comunity to decide whether the feature is worth the complexity of its development, but I don't agree that's a duplication of existing functionality. IMHO. regards, Sergey ******************************************************************* Sergey E. Koposov Max Planck Institute for Astronomy/Cambridge Institute for Astronomy/Sternberg Astronomical Institute Tel: +49-6221-528-349 Web: http://lnfm1.sai.msu.ru/~math E-mail: math@sai.msu.ru
On Fri, 2007-03-02 at 03:31 +0300, Sergey E. Koposov wrote: > > So it seems like we already have a solution to quotas at the database > > level. > > I still don't understand how FS quotas can help with DB quotas. .. > All the FS quotas are setup for limiting the space for specific user and > specific mount point. AFAIK They do not allow to limit the space in one > directory. And since all the PG tablespaces will be owned by postgres > user, I dont think the FS quotas can do the job. > Some filesystems help you more than others. In ZFS you can create a filesystem that uses a pre-existing storage pool and set a quota on that filesystem, and then mount it for the user that you want. In effect, it's basically a directory quota (no space reservation necessary, but a space limit). For the project you're working on, can you just set the default tablespaces to be restricted-size areas for the scientists? If you trust the scientists setting their default tablespace should be enough, and they don't need to be in a separate database. Or if you don't trust the scientists, could you use a separate database and the scientists could query with something like dblink? Regards,Jeff Davis
On Thursday 01 March 2007 17:35, Andrew Dunstan wrote:
> Tom Lane wrote:
> > "Joshua D. Drake" <jd@commandprompt.com> writes:
> >> Andrew Dunstan wrote:
> >>> Hitting a file system quota limit is likely to bring you down pretty
> >>> hard, isn't it?
> >>
> >> Yes, and likely corrupt the database.
> >
> > As long as you don't keep WAL on the restricted filesystem, it won't
> > stop or corrupt your database. Whether you can get anything much done
> > is another story :-(
>
> Yeah. Including recovery. Maybe we could do something that would work in
> cooperation with FS quotas - I have no idea what though.
>
I've actually run postgresql systems out of disk space both on data partitions
and wal partitions and never suffered corruption. Certainly I don't
recommend the practice, but pg can be amazingly resilient at times.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL