Thread: Performance hit if I create multiple DBs on same instance
Hi:
11.3 on linux
I've come up with a plan to archive data from my main DB which involves creating other DBs on the same server. But even though there will be zero activity on the archive DBs in terms of insert/update/delete, and almost no activity in terms of select, I'm still worried that the mere existence of these other DBs will steal resources away from the instance and degrade performance in my main DB. So my question is whether or not that worry is valid or not.
Thanks in Advance for any help !
On Tue, Nov 24, 2020 at 7:36 AM David Gauthier <davegauthierpg@gmail.com> wrote:
Hi:11.3 on linuxI've come up with a plan to archive data from my main DB which involves creating other DBs on the same server. But even though there will be zero activity on the archive DBs in terms of insert/update/delete, and almost no activity in terms of select, I'm still worried that the mere existence of these other DBs will steal resources away from the instance and degrade performance in my main DB. So my question is whether or not that worry is valid or not.Thanks in Advance for any help !
As long as you use the same cluster there shouldn't be any material difference between having multiple tables in the same database and those tables existing in another database in the same cluster. The cluster-level resources are the same in either case and have the same number of objects to worry about.
David J.
On 11/24/20 6:36 AM, David Gauthier wrote: > Hi: > > 11.3 on linux > > I've come up with a plan to archive data from my main DB which involves > creating other DBs on the same server. But even though there will be > zero activity on the archive DBs in terms of insert/update/delete, and > almost no activity in terms of select, I'm still worried that the mere > existence of these other DBs will steal resources away from the instance > and degrade performance in my main DB. So my question is whether or not > that worry is valid or not. The primary resource I see they taking is storage. If you have adequate space so that the primary and archive databases can grow into it then I don't see a problem on that score. There will also be some additional overhead for the automatic VACUUM and ANALYZE operations. Since the archive databases are quiescent that will be on the order of monitoring not really processing. > > Thanks in Advance for any help ! -- Adrian Klaver adrian.klaver@aklaver.com
Ok, thanks.
I was also planning on manually running vacuum, reindex and analyze on the main DB after removing the data from the main DB after archiving. Does that sound necessary and reasonable ?
On Tue, Nov 24, 2020 at 10:15 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 11/24/20 6:36 AM, David Gauthier wrote:
> Hi:
>
> 11.3 on linux
>
> I've come up with a plan to archive data from my main DB which involves
> creating other DBs on the same server. But even though there will be
> zero activity on the archive DBs in terms of insert/update/delete, and
> almost no activity in terms of select, I'm still worried that the mere
> existence of these other DBs will steal resources away from the instance
> and degrade performance in my main DB. So my question is whether or not
> that worry is valid or not.
The primary resource I see they taking is storage. If you have adequate
space so that the primary and archive databases can grow into it then I
don't see a problem on that score. There will also be some additional
overhead for the automatic VACUUM and ANALYZE operations. Since the
archive databases are quiescent that will be on the order of monitoring
not really processing.
>
> Thanks in Advance for any help !
--
Adrian Klaver
adrian.klaver@aklaver.com
On Tue, Nov 24, 2020 at 10:33:46AM -0500, David Gauthier wrote:
> Ok, thanks.
>
> I was also planning on manually running vacuum, reindex and analyze on the main
> DB after removing the data from the main DB after archiving. Does that sound
> necessary and reasonable ?
This blog entry summarizes the various levels of isolation and their
benefits:
https://momjian.us/main/blogs/pgblog/2012.html#April_23_2012
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com
The usefulness of a cup is in its emptiness, Bruce Lee
On 11/24/20 7:33 AM, David Gauthier wrote: > Ok, thanks. > > I was also planning on manually running vacuum, reindex and analyze on > the main DB after removing the data from the main DB after archiving. > Does that sound necessary and reasonable ? Sounds reasonable. > > On Tue, Nov 24, 2020 at 10:15 AM Adrian Klaver > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote: > > On 11/24/20 6:36 AM, David Gauthier wrote: > > Hi: > > > > 11.3 on linux > > > > I've come up with a plan to archive data from my main DB which > involves > > creating other DBs on the same server. But even though there > will be > > zero activity on the archive DBs in terms of > insert/update/delete, and > > almost no activity in terms of select, I'm still worried that the > mere > > existence of these other DBs will steal resources away from the > instance > > and degrade performance in my main DB. So my question is whether > or not > > that worry is valid or not. > > The primary resource I see they taking is storage. If you have adequate > space so that the primary and archive databases can grow into it then I > don't see a problem on that score. There will also be some additional > overhead for the automatic VACUUM and ANALYZE operations. Since the > archive databases are quiescent that will be on the order of monitoring > not really processing. > > > > > Thanks in Advance for any help ! > > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > -- Adrian Klaver adrian.klaver@aklaver.com
On 11/24/20 8:36 AM, David Gauthier wrote:
*Why?* If the data is just sitting there like a dead lump, it's not using any RAM or CPU cycles... And if you're afraid of autovacuum and autoanalyze stealing resources, then disable them (at the table level).
Hi:11.3 on linuxI've come up with a plan to archive data from my main DB which involves creating other DBs on the same server. But even though there will be zero activity on the archive DBs in terms of insert/update/delete, and almost no activity in terms of select, I'm still worried that the mere existence of these other DBs will steal resources away from the instance and degrade performance in my main DB.
*Why?* If the data is just sitting there like a dead lump, it's not using any RAM or CPU cycles... And if you're afraid of autovacuum and autoanalyze stealing resources, then disable them (at the table level).
--
Angular momentum makes the world go 'round.
Angular momentum makes the world go 'round.
On Tue, 2020-11-24 at 11:34 -0600, Ron wrote: > And if you're afraid of autovacuum and autoanalyze stealing resources, then disable them (at the table level). Ugh, bad advice. Better would be to VACUUM (FREEZE) these static table once, then autovacuum won't ever perform resource consuming activities on them again. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
On 11/25/20 8:59 AM, Laurenz Albe wrote: > On Tue, 2020-11-24 at 11:34 -0600, Ron wrote: >> And if you're afraid of autovacuum and autoanalyze stealing resources, then disable them (at the table level). > Ugh, bad advice. > > Better would be to VACUUM (FREEZE) these static table once, then autovacuum > won't ever perform resource consuming activities on them again. Good to know. -- Angular momentum makes the world go 'round.
On Wed, Nov 25, 2020 at 03:59:06PM +0100, Laurenz Albe wrote: > On Tue, 2020-11-24 at 11:34 -0600, Ron wrote: > > And if you're afraid of autovacuum and autoanalyze stealing resources, then disable them (at the table level). > > Ugh, bad advice. > > Better would be to VACUUM (FREEZE) these static table once, then autovacuum > won't ever perform resource consuming activities on them again. Yes, also, even if you never do that, autovacuum will eventually freeze those tables and never access them again. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee