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 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 !
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:
Show quoted text
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:
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.
*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.
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