Re: pgBackRest for multiple production servers
Hi On Mon, Jul 22, 2024 at 9:35 AM KK CHN wrote: > Hi list , > > I am exploring the PgBackRest tool for production deployment. ( My lab > setup with one Database server and another Reposerver deployed working > fine as in the official docs) > > Query: > > What may be the standard practice employed to backup multiple production > servers to one RepoServer ? ( the pgbackrest configuration on the > RepoServer part ) > > Is this the right way to achieve this (Defining multiple stanzas > Server1, Server 2 .. Server N and single [global] with repo1, repo2 > and repon N declarations ? > > Please correct me if I am wrong .. > Yes , normally there is one repo server which contains the configuration of all DB Servers in form of stanzas. Regards Kashif Zeeshan > > Thank you > Krishane > > > Please find the proposed pgbackrest.conf in the RepoServer for > backing up multiple database servers. > > /etc/pgbackrest/pgbackrest.conf on RepoServer > ## > [ Server _1] > pg1-host=10.20.20.6 > pg1-host-user= pgbackUser > pg1-path=/var/lib/pgsql/16/data > . . . . . . . . . . . . . . . . . . . . . . . . . > . . . . . . . . . . . . . . . . . . . . > . . . . . . . . . . . . . . . .. .. . .. . . . . > > [ Server _N] > pgN-host=10.20.20.N > pgN-host-user= pgbackUser > pgN-path=/var/lib/pgsql/16/data > > > [global] > repo1-path=/var/lib/ Server_1_Backup > repo1-retention-full=2 > repo1-cipher-type=aes-256-cbc > > repo1-cipher-pass=0oahu5f5dvH7eD4TI1eBEl8Vpn14hWEmgLGuXgpUHo9R2VQKCw6Sm99FnOfHBY > process-max=5 > log-level-console=info > log-level-file=debug > start-fast=y > delta=y > repo1-block=y > repo1-bundle=y > > repo2-path=/var/lib/ Server_2_Backup > repo2-retention-full=2 > repo2-cipher-type=aes-256-cbc > > repo2-cipher-pass=0oahu5f5dvH7eD4TI1eBEl8Vpn14hWEmgLGuXgpUHo9R2VQKCw6Sm99FnOfHBY > process-max=5 > log-level-console=info > log-level-file=debug > start-fast=y > delta=y > repo2-block=y > repo2-bundle=y > > . . . . . . . . . . . . . . . . . . . . . . . . > . . > . . . . . . . . . . . . . . . . . . . . . . . . > . . > . . . . . . . . . . . . . . . . . . . . . . . . > . . > > repoN-path=/var/lib Server_N_Backup > repoN-retention-full=2 > repoN-cipher-type=aes-256-cbc > > repoN-cipher-pass=0oahu5f5dvH7eD4TI1eBEl8Vpn14hWEmgLGuXgpUHo9R2VQKCw6Sm99FnOfHBY > process-max=5 > log-level-console=info > log-level-file=debug > start-fast=y > delta=y > repoN-block=y > repoN-bundle=y > > > [global:archive-push] > compress-level=3 > ### > >
Re: Slow performance
Hello: On Fri, 26 Jul 2024 at 07:31, sivapostg...@yahoo.com wrote: ... > Took backup (pg_dump) of first database (client_db) and restored the database > as second database (client_test). ... > The query when run against DB1 takes around 7 min 32 seconds. > The same query when run against DB2 takes around 124 msec. > Same computer, same PG cluster, same query. > Why it takes so much time when run against DB1 (client_db)? Can be bad luck, but the usual suspect would be different databases. I assume db1 is quiescent on the tests ( as it seems the production database, no heavy querying concurrent with your tests ). Bear in mind restoring leaves the database similar to what a vacuum full will do, so it can differ a lot from the original. > Already executed vacuum against client_db database. I think you already have pointed out this, but IIRC you have not told us if you have ANALYZED any of the databases. This is important. Bad stats in any of them could make the planner choose a bad plan ( or, if you are unlucky, make it choose a bad one ). Also, did you vacuum verbose? where your tables well packed? ( bad vacuuming can lead to huge tables with a lot of free space, but I doubt this is your case, but everything has to be checked, we only know what you write us ). And now, not being an expert in tracing explain I see this in plan-db1: " Join Filter: (((b.registrationnumber)::text = (p.registrationnumber)::text) AND ((c.subjectcode)::text = (p.subjectcode)::text) AND (a.semester = p.semester))" " Rows Removed by Join Filter: 13614738" " -> Index Scan using ""cl_student_semester_subject_IX3"" on cl_student_semester_subject p (cost=0.55..8.57 rows=1 width=60) (actual time=0.033..55.702 rows=41764 loops=1)" "Index Cond: (((companycode)::text = '100'::text) AND ((examheaderfk)::text = 'BA80952CFF8F4E1C3F9F44B62ED9BF37'::text))" Not an explain expert, but if i read correctly an index scan expecting 1 row recovers 41674, which hints at bad statistics ( or skewed data distribution and bad luck ) The plans are similar, but in the fast query cl_student_semester_subject is accessed using other index: " -> Index Scan using ""cl_student_semester_subject_IX1"" on cl_student_semester_subject p (cost=0.42..3.09 rows=1 width=60) (actual time=0.010..0.010 rows=1 loops=326)" "Index Cond: (((companycode)::text = '100'::text) AND ((subjectcode)::text = (a.subjectcode)::text) AND ((registrationnumber)::text = (a.registrationnumber)::text) AND (semester = a.semester))" Which seems much more selective and recovers just what it wants. I would start by analyzing ( and, if not too costly, reindexing ) that table. Francisco Olarte.
Re: Slow performance
Hi Siva, pg_dump taken from client_db seems not to have transferred full data to client_test. When I examine the query plans, the rows scanned seem to be extremely different. For example, there is 1 row in cl_level table on client_test, while clined_db shows that around 300,000 records were scanned. My suggestion would be to check the data counts in the tables you moved with count(*). Francisco Olarte , 26 Tem 2024 Cum, 10:55 tarihinde şunu yazdı: > Hello: > > On Fri, 26 Jul 2024 at 07:31, sivapostg...@yahoo.com > wrote: > ... > > Took backup (pg_dump) of first database (client_db) and restored the > database as second database (client_test). > ... > > The query when run against DB1 takes around 7 min 32 seconds. > > The same query when run against DB2 takes around 124 msec. > > Same computer, same PG cluster, same query. > > Why it takes so much time when run against DB1 (client_db)? > > Can be bad luck, but the usual suspect would be different databases. > > I assume db1 is quiescent on the tests ( as it seems the production > database, no heavy querying concurrent with your tests ). > > Bear in mind restoring leaves the database similar to what a vacuum > full will do, so it can differ a lot from the original. > > > Already executed vacuum against client_db database. > > I think you already have pointed out this, but IIRC you have not told > us if you have ANALYZED any of the databases. This is important. Bad > stats in any of them could make the planner choose a bad plan ( or, if > you are unlucky, make it choose a bad one ). > > Also, did you vacuum verbose? where your tables well packed? ( bad > vacuuming can lead to huge tables with a lot of free space, but I > doubt this is your case, but everything has to be checked, we only > know what you write us ). > > And now, not being an expert in tracing explain I see this in plan-db1: > " Join Filter: (((b.registrationnumber)::text = > (p.registrationnumber)::text) AND ((c.subjectcode)::text = > (p.subjectcode)::text) AND (a.semester = p.semester))" > " Rows Removed by Join Filter: 13614738" > " -> Index Scan using > ""cl_student_semester_subject_IX3"" on cl_student_semester_subject p > (cost=0.55..8.57 rows=1 width=60) (actual time=0.033..55.702 > rows=41764 loops=1)" > "Index Cond: (((companycode)::text = '100'::text) > AND ((examheaderfk)::text = > 'BA80952CFF8F4E1C3F9F44B62ED9BF37'::text))" > > Not an explain expert, but if i read correctly an index scan expecting > 1 row recovers 41674, which hints at bad statistics ( or skewed data > distribution and bad luck ) > > The plans are similar, but in the fast query > cl_student_semester_subject is accessed using other index: > > " -> Index Scan using > ""cl_student_semester_subject_IX1"" on cl_student_semester_subject p > (cost=0.42..3.09 rows=1 width=60) (actual time=0.010..0.010 rows=1 > loops=326)" > "Index Cond: (((companycode)::text = '100'::text) > AND ((subjectcode)::text = (a.subjectcode)::text) AND > ((registrationnumber)::text = (a.registrationnumber)::text) AND > (semester = a.semester))" > > Which seems much more selective and recovers just what it wants. > > I would start by analyzing ( and, if not too costly, reindexing ) that > table. > > Francisco Olarte. > > >
Unexpected Null Pointer For Static Shared Memory Segment
Hello, I hope this message finds you well. I am currently working on a PostgreSQL extension and have encountered an issue where a static pointer becomes null between different AM routines. My problem is as follows: I am working with a few AM routines, specifically “ambuild” and “amrescan”. During “ambuild”, I use “ShmemInitStruct” to initialize a segment of shared memory and save the pointer to this location in my static, global pointer. I then set some values of the structure that the pointer points to, which I believe works correctly. I have ensured to acquire, and later release, the “AddinShmemInitLock” as well as check if we have found a segment of the same name in shared memory. I can access the pointer and any data I save in the struct perfectly fine during this AM routine. When the extension later performs “amrescan”, the static pointer I had set is null. I am not quite sure why this is happening. I would greatly appreciate any guidance or suggestions! Perhaps I need to use the startup hooks when calling the “ShmemInitStruct” function (although I would like to avoid this as the size of the segment I am initializing varies at run time) or use dynamic shared memory? Please let me know if there are any more details I can provide or if anything is unclear. This is my first time working on a PostgreSQL extension and asking a question using this mailing list, so please let me know if there is a more appropriate forum I should use. Thanks for your time and assistance! Best, Aditya Gupta
Re: Slow performance
On Fri, 26 Jul 2024 at 19:55, Francisco Olarte wrote: > " -> Index Scan using > ""cl_student_semester_subject_IX3"" on cl_student_semester_subject p > (cost=0.55..8.57 rows=1 width=60) (actual time=0.033..55.702 > rows=41764 loops=1)" > "Index Cond: (((companycode)::text = '100'::text) > AND ((examheaderfk)::text = > 'BA80952CFF8F4E1C3F9F44B62ED9BF37'::text))" > > Not an explain expert, but if i read correctly an index scan expecting > 1 row recovers 41674, which hints at bad statistics ( or skewed data > distribution and bad luck ) You have correctly identified the reason the poor plan was chosen. If that row estimate was anything higher than 1, that plan wouldn't be picked. If ANALYZE cl_student_semester_subject; does not fix the issue, then increasing the statistics targets with something like: alter table cl_student_semester_subject alter column companycode set (default_statistics_target = 1000); alter table cl_student_semester_subject alter column examheaderfk set (default_statistics_target = 1000); analyze cl_student_semester_subject; (Warning, additional statistics targets can slow down planning a little) or if that does not help and there's some correlation between those columns and/or the values in question, then maybe the following might help get a more accurate estimate: create statistics on companycode, examheaderfk from cl_student_semester_subject; analyze cl_student_semester_subject; David
