public inbox for [email protected]help / color / mirror / Atom feed
Re: Controlling resource utilization 4+ messages / 3 participants [nested] [flat]
* Re: Controlling resource utilization @ 2024-04-16 20:29 Juan Rodrigo Alejandro Burgos Mella <[email protected]> 2024-04-17 07:10 ` Re: Controlling resource utilization [email protected] 0 siblings, 1 reply; 4+ messages in thread From: Juan Rodrigo Alejandro Burgos Mella @ 2024-04-16 20:29 UTC (permalink / raw) To: yudhi s <[email protected]>; +Cc: pgsql-general <[email protected]> In postgreSQL, that can be done at a session level, or at a general level (in the postgresql.conf configuration file) Atte JRBM El mar, 16 abr 2024 a las 15:18, yudhi s (<[email protected]>) escribió: > > > On Wed, 17 Apr, 2024, 1:32 am Juan Rodrigo Alejandro Burgos Mella, < > [email protected]> wrote: > >> Yes sir >> >> SET statement_timeout TO '<milliseconds>' >> >> Atte >> JRBM >> >> El mar, 16 abr 2024 a las 14:46, yudhi s (<[email protected]>) >> escribió: >> >>> Hi , >>> We want to have controls around the DB resource utilization by the adhoc >>> user queries, so that it won't impact the application queries negatively. >>> Its RDS postgresql database version 15.4. >>> >>> Saw one parameter as statement_timeout which restricts the queries to >>> not run after a certain time duration and queries will be automatically >>> killed/cancelled. However, I don't see any other options to set this at >>> user level, rather it's getting set for all or at session level. So I want >>> to know if there exists, anyway to control the database resource >>> utilization specific to users? >>> >>> Regards >>> Yudhi >>> >> > > This will set the timeout at session level. However, We want to > understand, if it can be done at user/role level, so that any such adhoc > user queries can be auto killed or cancelled after the set time. > >> >>> ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Controlling resource utilization 2024-04-16 20:29 Re: Controlling resource utilization Juan Rodrigo Alejandro Burgos Mella <[email protected]> @ 2024-04-17 07:10 ` [email protected] 2024-04-17 07:42 ` Re: Controlling resource utilization yudhi s <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: [email protected] @ 2024-04-17 07:10 UTC (permalink / raw) To: Juan Rodrigo Alejandro Burgos Mella <[email protected]>; +Cc: yudhi s <[email protected]>; pgsql-general <[email protected]> > De: "Juan Rodrigo Alejandro Burgos Mella" <[email protected]> > À: "yudhi s" <[email protected]> > Cc: "pgsql-general" <[email protected]> > Envoyé: Mardi 16 Avril 2024 22:29:35 > Objet: Re: Controlling resource utilization > In postgreSQL, that can be done at a session level, or at a general level (in > the postgresql.conf configuration file) > Atte > JRBM > El mar, 16 abr 2024 a las 15:18, yudhi s (< [ mailto:[email protected] > | [email protected] ] >) escribió: >> On Wed, 17 Apr, 2024, 1:32 am Juan Rodrigo Alejandro Burgos Mella, < [ >> mailto:[email protected] | [email protected] ] > wrote: >>> Yes sir >>> SET statement_timeout TO '<milliseconds>' >>> Atte >>> JRBM >>> El mar, 16 abr 2024 a las 14:46, yudhi s (< [ mailto:[email protected] >>> | [email protected] ] >) escribió: >>>> Hi , >>>> We want to have controls around the DB resource utilization by the adhoc user >>>> queries, so that it won't impact the application queries negatively. Its RDS >>>> postgresql database version 15.4. >>>> Saw one parameter as statement_timeout which restricts the queries to not run >>>> after a certain time duration and queries will be automatically >>>> killed/cancelled. However, I don't see any other options to set this at user >>>> level, rather it's getting set for all or at session level. So I want to know >>>> if there exists, anyway to control the database resource utilization specific >>>> to users? >>>> Regards >>>> Yudhi >> This will set the timeout at session level. However, We want to understand, if >> it can be done at user/role level, so that any such adhoc user queries can be >> auto killed or cancelled after the set time. Or also at role/user level : ALTER ROLE < your - username > SET statement_timeout = '<time_unit>' ; Regards Gilles ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Controlling resource utilization 2024-04-16 20:29 Re: Controlling resource utilization Juan Rodrigo Alejandro Burgos Mella <[email protected]> 2024-04-17 07:10 ` Re: Controlling resource utilization [email protected] @ 2024-04-17 07:42 ` yudhi s <[email protected]> 2024-04-17 08:14 ` Re: Controlling resource utilization [email protected] 0 siblings, 1 reply; 4+ messages in thread From: yudhi s @ 2024-04-17 07:42 UTC (permalink / raw) To: [email protected]; +Cc: Juan Rodrigo Alejandro Burgos Mella <[email protected]>; pgsql-general <[email protected]> On Wed, 17 Apr, 2024, 12:40 pm , <[email protected]> wrote: > > > ------------------------------ > > *De: *"Juan Rodrigo Alejandro Burgos Mella" <[email protected]> > *À: *"yudhi s" <[email protected]> > *Cc: *"pgsql-general" <[email protected]> > *Envoyé: *Mardi 16 Avril 2024 22:29:35 > *Objet: *Re: Controlling resource utilization > > ALTER ROLE <your-username> SET statement_timeout = '<time_unit>'; > > Regards > Gilles > > Thank you so much. That helps. This statement is succeeding for user as I executed. So it's working I believe. But to immediately verify without manually running queries and waiting for it to be auto killed to confirm, Is there any system table which we can verify to see if this setting is effective, as because I don't see any such columns in pg_user or pg_role which shows the statement_timeout. And is there a way to put similar cap/restrictions on other db resources like cpu, memory, I/O at specific user/role level? > ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Controlling resource utilization 2024-04-16 20:29 Re: Controlling resource utilization Juan Rodrigo Alejandro Burgos Mella <[email protected]> 2024-04-17 07:10 ` Re: Controlling resource utilization [email protected] 2024-04-17 07:42 ` Re: Controlling resource utilization yudhi s <[email protected]> @ 2024-04-17 08:14 ` [email protected] 0 siblings, 0 replies; 4+ messages in thread From: [email protected] @ 2024-04-17 08:14 UTC (permalink / raw) To: yudhi <[email protected]>; +Cc: Juan Rodrigo Alejandro Burgos Mella <[email protected]>; pgsql-general <[email protected]> > De: "yudhi" <[email protected]> > À: "gparc" <[email protected]> > Cc: "Juan Rodrigo Alejandro Burgos Mella" <[email protected]>, > "pgsql-general" <[email protected]> > Envoyé: Mercredi 17 Avril 2024 09:42:49 > Objet: Re: Controlling resource utilization > On Wed, 17 Apr, 2024, 12:40 pm , < [ mailto:[email protected] | [email protected] ] > > wrote: >>> De: "Juan Rodrigo Alejandro Burgos Mella" < [ >>> mailto:[email protected] | [email protected] ] > >>> À: "yudhi s" < [ mailto:[email protected] | >>> [email protected] ] > >>> Cc: "pgsql-general" < [ mailto:[email protected] | >>> [email protected] ] > >>> Envoyé: Mardi 16 Avril 2024 22:29:35 >>> Objet: Re: Controlling resource utilization >> ALTER ROLE <your-username> SET statement_timeout = '<time_unit>'; >> Regards >> Gilles > Thank you so much. That helps. > This statement is succeeding for user as I executed. So it's working I believe. > But to immediately verify without manually running queries and waiting for it to > be auto killed to confirm, Is there any system table which we can verify to see > if this setting is effective, as because I don't see any such columns in > pg_user or pg_role which shows the statement_timeout. > And is there a way to put similar cap/restrictions on other db resources like > cpu, memory, I/O at specific user/role level? To verify the setting, you can use this command in psql : \drds <your-username> Concerning system resources like CPUs it's not possible. You can use pg_settings view to see which setting you can change and in which context : [ https://www.postgresql.org/docs/current/view-pg-settings.html | https://www.postgresql.org/docs/current/view-pg-settings.html ] Regards Gilles ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-04-17 08:14 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-04-16 20:29 Re: Controlling resource utilization Juan Rodrigo Alejandro Burgos Mella <[email protected]> 2024-04-17 07:10 ` [email protected] 2024-04-17 07:42 ` yudhi s <[email protected]> 2024-04-17 08:14 ` [email protected]
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox