Обсуждение: Multiple databases and shared_buffers
We will be creating multiple databases in a cluster (instance). Is there any way to separate shared_buffers for each database? Looks like not since PG does not allow user created shared buffers.
thanks.
On Thu, Feb 18, 2016 at 1:54 AM, Data Cruncher <dcruncher4@aim.com> wrote:
We will be creating multiple databases in a cluster (instance). Is there any way to separate shared_buffers for each database? Looks like not since PG does not allow user created shared buffers.
shared_buffers parameter is for the whole PostgreSQL cluster shared by all the databases in the cluster.
Regards,
Venkata B N
Fujitsu Australia
On 2/17/2016 6:54 AM, Data Cruncher wrote:
We will be creating multiple databases in a cluster (instance). Is there any way to separate shared_buffers for each database? Looks like not since PG does not allow user created shared buffers.
you would need to run multiple instances if you feel you need that level of control over shared_buffers.
-- john r pierce, recycling bits in santa cruz
I think this begs the question "Why do you think you need to separate the shared_buffers"?
What version of PostgreSQL are you using?On Wed, Feb 17, 2016 at 5:21 PM, John R Pierce <pierce@hogranch.com> wrote:
On 2/17/2016 6:54 AM, Data Cruncher wrote:We will be creating multiple databases in a cluster (instance). Is there any way to separate shared_buffers for each database? Looks like not since PG does not allow user created shared buffers.
you would need to run multiple instances if you feel you need that level of control over shared_buffers.-- john r pierce, recycling bits in santa cruz
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
It is a business requirement that we store our clients data in separate databases. Our sales folks insist it is non negotiable. Our current technology does
support that and also maintain buffer pools for each db independently. That's why I brought this up. Personally I don't think this is a serious limitation at all.
-----Original Message-----
From: Melvin Davidson <melvin6925@gmail.com>
To: pgsql-general <pgsql-general@postgresql.org>
Sent: Wed, Feb 17, 2016 9:53 pm
Subject: Re: [GENERAL] Multiple databases and shared_buffers
What is your O/S?
How many CPU's on your server?
How much memory?
--
From: Melvin Davidson <melvin6925@gmail.com>
To: pgsql-general <pgsql-general@postgresql.org>
Sent: Wed, Feb 17, 2016 9:53 pm
Subject: Re: [GENERAL] Multiple databases and shared_buffers
I think this begs the question "Why do you think you need to separate the shared_buffers"?
What version of PostgreSQL are you using?On Wed, Feb 17, 2016 at 5:21 PM, John R Pierce <pierce@hogranch.com> wrote:
On 2/17/2016 6:54 AM, Data Cruncher wrote:We will be creating multiple databases in a cluster (instance). Is there any way to separate shared_buffers for each database? Looks like not since PG does not allow user created shared buffers.
you would need to run multiple instances if you feel you need that level of control over shared_buffers.-- john r pierce, recycling bits in santa cruz
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
On Thu, Feb 18, 2016 at 4:23 AM, Rakesh Kumar <dcruncher4@aim.com> wrote:
It is a business requirement that we store our clients data in separate databases. Our sales folks insist it is non negotiable. Our current technology doessupport that and also maintain buffer pools for each db independently. That's why I brought this up. Personally I don't think this is a serious limitation at all.-----Original Message-----
From: Melvin Davidson <melvin6925@gmail.com>
To: pgsql-general <pgsql-general@postgresql.org>
Sent: Wed, Feb 17, 2016 9:53 pm
Subject: Re: [GENERAL] Multiple databases and shared_buffersHow much memory?How many CPU's on your server?What is your O/S?I think this begs the question "Why do you think you need to separate the shared_buffers"?What version of PostgreSQL are you using?On Wed, Feb 17, 2016 at 5:21 PM, John R Pierce <pierce@hogranch.com> wrote:On 2/17/2016 6:54 AM, Data Cruncher wrote:We will be creating multiple databases in a cluster (instance). Is there any way to separate shared_buffers for each database? Looks like not since PG does not allow user created shared buffers.
you would need to run multiple instances if you feel you need that level of control over shared_buffers.-- john r pierce, recycling bits in santa cruz
>It is a business requirement that we store our clients data in separate databases. Our sales folks insist it is non negotiable. Our current technology does
>support that and also maintain buffer pools for each db independently.
NOTE: It is very important you provide the version of PostgreSQL and O/S when addressing this mail list. That is so others searching the archives in the future
can determine it's pertenance.
That being said, It looks to me like you have a misunderstanding about how PostgreSQL uses shared_buffers. Data is not actually stored in the shared_buffers.
Please refer to the following url which explains it's usage.
http://leopard.in.ua/2013/09/05/postgresql-sessting-shared-memory
-- http://leopard.in.ua/2013/09/05/postgresql-sessting-shared-memory
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
What do you mean that the data is actually not stored in the shared buffer.
From the link you provided :
"The “shared_buffers” configuration parameter determines how much memory
is dedicated to PostgreSQL to use for caching data."
This seems to be the same like other RDBMS. Typically the terminology used is Buffer Pool.
BP is used to cache the changes done to a page and the page cleaning
From the link you provided :
"The “shared_buffers” configuration parameter determines how much memory
is dedicated to PostgreSQL to use for caching data."
This seems to be the same like other RDBMS. Typically the terminology used is Buffer Pool.
BP is used to cache the changes done to a page and the page cleaning
process periodically flushes the dirty pages to disk.
Checkpoints typically gurantees that at a given time a page in cache is same as on the disk and is usually
Checkpoints typically gurantees that at a given time a page in cache is same as on the disk and is usually
used as the start time for roll forward recovery.
Does PG follow a different architecture ?
-----Original Message-----
From: Melvin Davidson <melvin6925@gmail.com>
That being said, It looks to me like you have a misunderstanding about how PostgreSQL uses shared_buffers. Data is not actually stored in the shared_buffers.
Please refer to the following url which explains it's usage. --
From: Melvin Davidson <melvin6925@gmail.com>
NOTE: It is very important you provide the version of PostgreSQL and O/S when addressing this mail list. That is so others searching the archives in the future
can determine it's pertenance.
That being said, It looks to me like you have a misunderstanding about how PostgreSQL uses shared_buffers. Data is not actually stored in the shared_buffers.
Please refer to the following url which explains it's usage.
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
NOTE: It is very important you provide the version of PostgreSQL and O/S when addressing this mail list.
Since it is a new project, we are going with:
PG: 9.5.1
Since it is a new project, we are going with:
PG: 9.5.1
OS: RHEL 6.4
On 2/18/2016 12:04 PM, Rakesh Kumar wrote:
NOTE: It is very important you provide the version of PostgreSQL and O/S when addressing this mail list.
Since it is a new project, we are going with:
PG: 9.5.1OS: RHEL 6.4
off topic for this list, but RHEL 6.4 was a 'snapshot' 3 years ago, and is missing 100s and 100s of security and bug updates since then. as soon as you run `yum update`, you should be at the current 6.7 snapshot plus any incremental patches since 6.7 was released last July.
-- john r pierce, recycling bits in santa cruz
>What do you mean that the data is actually not stored in the shared buffer.
>From the link you provided :
>"The “shared_buffers” configuration parameter determines how much memory
>is dedicated to PostgreSQL to use for caching data."
>From the link you provided :
>"The “shared_buffers” configuration parameter determines how much memory
>is dedicated to PostgreSQL to use for caching data."
Again, you misunderstand. Cached data (and queries) is for sharing only to the same database.
So if user A in Database abc does a "SELECT some_column FROM table1 WHERE col2 = 4" Then the results/data from that query are available to any other user (who has permission) in database abc and does the same
exact query. However, users from database xyz CANNOT see data and/or results from database abc unless they specifically connect to it. Further to the point, Beginning with 9.4, PostgreSQL also makes better use of O/S memory for shared_buffers. But the bottom line is, you do not need to split shared_buffers up among different databases. PostgreSQL just uses it to make queries more efficient. There is no security problem because users in one database cannot request buffer information about another.
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
aha ok it is clear now.
The splitting of buffers for each db is not for the reasons you stated below, but for better management of RAM.
In our current RDBMS we allocate BPs for each database based on its usage/size. With that being said, in case
The splitting of buffers for each db is not for the reasons you stated below, but for better management of RAM.
In our current RDBMS we allocate BPs for each database based on its usage/size. With that being said, in case
of PG. having no control on BP is not a big deal.
Also, just curious , is it possible to shut down only one database in an instance,
Also, just curious , is it possible to shut down only one database in an instance,
in case a rogue session connected to it is causing havoc. I know there are other ways
of achieving it (like killing all sessions and revoking grant priv), but if there is an easier
way to knock out a db temporarily, it will be great.
-----Original Message-----
From: Melvin Davidson <melvin6925@gmail.com>
To: Rakesh Kumar <dcruncher4@aim.com>; pgsql-general <pgsql-general@postgresql.org>
Sent: Thu, Feb 18, 2016 4:12 pm
Subject: Re: [GENERAL] Multiple databases and shared_buffers
--
From: Melvin Davidson <melvin6925@gmail.com>
To: Rakesh Kumar <dcruncher4@aim.com>; pgsql-general <pgsql-general@postgresql.org>
Sent: Thu, Feb 18, 2016 4:12 pm
Subject: Re: [GENERAL] Multiple databases and shared_buffers
>What do you mean that the data is actually not stored in the shared buffer.
>From the link you provided :
>"The “shared_buffers” configuration parameter determines how much memory
>is dedicated to PostgreSQL to use for caching data."
>From the link you provided :
>"The “shared_buffers” configuration parameter determines how much memory
>is dedicated to PostgreSQL to use for caching data."
Again, you misunderstand. Cached data (and queries) is for sharing only to the same database.
So if user A in Database abc does a "SELECT some_column FROM table1 WHERE col2 = 4" Then the results/data from that query are available to any other user (who has permission) in database abc and does the same
exact query. However, users from database xyz CANNOT see data and/or results from database abc unless they specifically connect to it. Further to the point, Beginning with 9.4, PostgreSQL also makes better use of O/S memory for shared_buffers. But the bottom line is, you do not need to split shared_buffers up among different databases. PostgreSQL just uses it to make queries more efficient. There is no security problem because users in one database cannot request buffer information about another.
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
On Thu, Feb 18, 2016 at 5:15 PM, Rakesh Kumar <dcruncher4@aim.com> wrote:
aha ok it is clear now.
The splitting of buffers for each db is not for the reasons you stated below, but for better management of RAM.
In our current RDBMS we allocate BPs for each database based on its usage/size. With that being said, in caseof PG. having no control on BP is not a big deal.
Also, just curious , is it possible to shut down only one database in an instance,in case a rogue session connected to it is causing havoc. I know there are other waysof achieving it (like killing all sessions and revoking grant priv), but if there is an easierway to knock out a db temporarily, it will be great.-----Original Message-----
From: Melvin Davidson <melvin6925@gmail.com>
To: Rakesh Kumar <dcruncher4@aim.com>; pgsql-general <pgsql-general@postgresql.org>
Sent: Thu, Feb 18, 2016 4:12 pm
Subject: Re: [GENERAL] Multiple databases and shared_buffers>What do you mean that the data is actually not stored in the shared buffer.
>From the link you provided :
>"The “shared_buffers” configuration parameter determines how much memory
>is dedicated to PostgreSQL to use for caching data."Again, you misunderstand. Cached data (and queries) is for sharing only to the same database.So if user A in Database abc does a "SELECT some_column FROM table1 WHERE col2 = 4" Then the results/data from that query are available to any other user (who has permission) in database abc and does the sameexact query. However, users from database xyz CANNOT see data and/or results from database abc unless they specifically connect to it. Further to the point, Beginning with 9.4, PostgreSQL also makes better use of O/S memory for shared_buffers. But the bottom line is, you do not need to split shared_buffers up among different databases. PostgreSQL just uses it to make queries more efficient. There is no security problem because users in one database cannot request buffer information about another.
--Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
>also, just curious , is it possible to shut down only one database in an instance,
>in case a rogue session connected to it is causing havoc. I know there are other ways
>of achieving it (like killing all sessions and revoking grant priv)
You are getting off topic, but no, you cannot "shutdown" just one database. However, you can use the attached flip_database_connect.sh to temporarily prevent connections to a specific database.
and then reallow.
You can also use cancel_all_queries.sh to cancel ALL current queries, but that is for all users except the superuser running it.FYI, revoking a grant will not cancel current queries, only prevent future access.
From your questions, it appears you are a little weak on PostgreSQL Database Administration. I respectfully suggest you obtain a copy of the following book to get a clearer understandings of how things work.
https://www.packtpub.com/big-data-and-business-intelligence/postgresql-9-administration-cookbook-second-edition/?utm_source=PoD&utm_medium=referral&utm_campaign=1849519064
https://www.packtpub.com/big-data-and-business-intelligence/postgresql-9-administration-cookbook-second-edition/?utm_source=PoD&utm_medium=referral&utm_campaign=1849519064
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
Вложения
On 02/18/2016 02:15 PM, Rakesh Kumar wrote: > aha ok it is clear now. > > The splitting of buffers for each db is not for the reasons you stated > below, but for better management of RAM. > In our current RDBMS we allocate BPs for each database based on its > usage/size. With that being said, in case > of PG. having no control on BP is not a big deal. > > Also, just curious , is it possible to shut down only one database in an > instance, > in case a rogue session connected to it is causing havoc. I know there > are other ways > of achieving it (like killing all sessions and revoking grant priv), but > if there is an easier > way to knock out a db temporarily, it will be great. In 9.5: http://www.postgresql.org/docs/9.5/interactive/sql-alterdatabase.html ALTER DATABASE name [ [ WITH ] option [ ... ] ] where option can be: ALLOW_CONNECTIONS allowconn So: postgres=# alter database test allow_connections false; postgres=# \c test FATAL: database "test" is not currently accepting connections FATAL: database "test" is not currently accepting connections Previous connection kept It will not shutdown existing sessions, but you can use the functions below to do that: http://www.postgresql.org/docs/9.5/interactive/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL > > -----Original Message----- > From: Melvin Davidson <melvin6925@gmail.com> > To: Rakesh Kumar <dcruncher4@aim.com>; pgsql-general > <pgsql-general@postgresql.org> > Sent: Thu, Feb 18, 2016 4:12 pm > Subject: Re: [GENERAL] Multiple databases and shared_buffers > > >What do you mean that the data is actually not stored in the shared > buffer. >From the link you provided : >>"The “shared_buffers” configuration parameter determines how much memory >>is dedicated to PostgreSQL to use for caching data." > > Again, you misunderstand. Cached data (and queries) is for sharing only > to the same database. > So if user A in Database abc does a "SELECT some_column FROM table1 > WHERE col2 = 4" Then the results/data from that query are available to > any other user (who has permission) in database abc and does the same > exact query. However, users from database xyz CANNOT see data and/or > results from database abc unless they specifically connect to it. > Further to the point, Beginning with 9.4, PostgreSQL also makes better > use of O/S memory for shared_buffers. But the bottom line is, you do not > need to split shared_buffers up among different databases. PostgreSQL > just uses it to make queries more efficient. There is no security > problem because users in one database cannot request buffer information > about another. > > -- > *Melvin Davidson* > I reserve the right to fantasize. Whether or not you > wish to share my fantasy is entirely up to you. -- Adrian Klaver adrian.klaver@aklaver.com
On Thu, Feb 18, 2016 at 05:38:45PM -0500, Melvin Davidson wrote: > From your questions, it appears you are a little weak on PostgreSQL > Database Administration. I respectfully suggest you obtain a copy of the > following book to get a clearer understandings of how things work. The PostgreSQL documentation actually is a great read, too. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346