Thread: Schema Consolidation in PostgreSQL
Hi all!
I have a database with multiple schemas consolidated within a postgres database.
I have 2 issues when working with this setup.
1. How can I ensure that there's no resource contention between the schemas? Is there any resource manager.
2. Can I get schema-level performance statistics? If I see that this schema is overloaded, I may move it to another server.
Thanks!
--
Regards,
Ang Wei Shan
Ang Wei Shan
Wei Shan wrote: > I have a database with multiple schemas consolidated within a postgres database. > > I have 2 issues when working with this setup. > > 1. How can I ensure that there's no resource contention between the schemas? Is there any resource > manager. > 2. Can I get schema-level performance statistics? If I see that this schema is overloaded, I may move > it to another server. It is a bit weird to think of resource consumption by schema; wouldn't it make more sense to assess resource consumption by user or session? The only resources that can be measured by schema would be I/O or memory for shared buffers, I guess. There is pg_statio_all_tables and similar views that tell you how many blocks were read from disk and from shared buffers. You could aggregate that information by schema. For measuring shared memory usage you could install the pg_buffercache extension, out of that view you could get the information how many buffers are used by objects in each schema. Yours, Laurenz Albe
Hi all!I have a database with multiple schemas consolidated within a postgres database.I have 2 issues when working with this setup.1. How can I ensure that there's no resource contention between the schemas
This doesn't make sense and, regardless, the use of "resource" here to too generic to comment upon.
Is there any resource manager.
Both the O/S and the database manage resources...
2. Can I get schema-level performance statistics? If I see that this schema is overloaded, I may move it to another server.
Like Albe said - you can get statistics by object, and know in which schemas those objects reside.
David J.
Hi all,
Thanks for the replies so far.
When I say resource manager, I actually meant if there's a way to ensure the maximum resource utilization by a certain user. For example in Oracle, we can actually limit a certain user by setting a resource plan on it. Resource can be CPU.
Thanks!
On 9 May 2015 12:16 am, "David G. Johnston" <david.g.johnston@gmail.com> wrote:
Hi all!I have a database with multiple schemas consolidated within a postgres database.I have 2 issues when working with this setup.1. How can I ensure that there's no resource contention between the schemasThis doesn't make sense and, regardless, the use of "resource" here to too generic to comment upon.Is there any resource manager.Both the O/S and the database manage resources...2. Can I get schema-level performance statistics? If I see that this schema is overloaded, I may move it to another server.Like Albe said - you can get statistics by object, and know in which schemas those objects reside.David J.
Wei Shan wrote: > When I say resource manager, I actually meant if there's a way to ensure the maximum > resource utilization by a certain user. For example in Oracle, we can actually limit > a certain user by setting a resource plan on it. Resource can be CPU. There is no such thing in PostgreSQL. You can limit the storage a user can use by keeping his data in a table space with a certain size. Yours, Laurenz Albe
You can to some extent do the following
ALTER USER name SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER USER name SET configuration_parameter FROM CURRENT
ALTER USER name RESET configuration_parameter- configuration_parameter
value Set this role's session default for the specified configuration parameter to the given value. If value is DEFAULT or, equivalently, RESET is used, the role-specific variable setting is removed, so the role will inherit the system-wide default setting in new sessions. Use RESET ALL to clear all role-specific settings. SET FROM CURRENT saves the session's current value of the parameter as the role-specific value. If IN DATABASE is specified, the configuration parameter is set or removed for the given role and database only.
Role-specific variable settings take effect only at login; SET ROLE and SET SESSION AUTHORIZATION do not process role-specific variable settings.
See SET and Chapter 18 for more information about allowed parameter names and values.
On Sun, May 10, 2015 at 3:01 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
Wei Shan wrote:
> When I say resource manager, I actually meant if there's a way to ensure the maximum
> resource utilization by a certain user. For example in Oracle, we can actually limit
> a certain user by setting a resource plan on it. Resource can be CPU.
There is no such thing in PostgreSQL.
You can limit the storage a user can use by keeping his data in a
table space with a certain size.
Yours,
Laurenz Albe
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Thanks,
Jorge Torralba
----------------------------
Note: This communication may contain privileged or other confidential information. If you are not the intended recipient, please do not print, copy, retransmit, disseminate or otherwise use the information. Please indicate to the sender that you have received this email in error and delete the copy you received. Thank You.
Jorge Torralba
----------------------------
Note: This communication may contain privileged or other confidential information. If you are not the intended recipient, please do not print, copy, retransmit, disseminate or otherwise use the information. Please indicate to the sender that you have received this email in error and delete the copy you received. Thank You.
Jorge Torralba wrote:
> On Sun, May 10, 2015 at 3:01 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
>> Wei Shan wrote:
>>> When I say resource manager, I actually meant if there's a way to ensure the maximum
>>> resource utilization by a certain user. For example in Oracle, we can actually limit
>>> a certain user by setting a resource plan on it. Resource can be CPU.
>> There is no such thing in PostgreSQL.
>> You can limit the storage a user can use by keeping his data in a table space with a certain size.
> You can to some extent do the following
>
> ALTER USER name SET configuration_parameter { TO | = } { value | DEFAULT }
But that won't limit the resources used by a user.
A user can have several connections.
You could maybe limit memory usage somewhat by setting work_mem low for
a user and limiting their connections with
ALTER ROLE ... CONNECTION LIMIT ...
But that's a big step from something like Oracle's Resource Manager.
Yours,
Laurenz Albe