Re: Tracking of page changes for backup purposes. PTRACK [POC]

2017-12-27 Thread Magnus Hagander
On Thu, Dec 21, 2017 at 1:51 AM, Michael Paquier wrote: > On Thu, Dec 21, 2017 at 7:35 AM, Robert Haas > wrote: > > On Wed, Dec 20, 2017 at 3:45 PM, Tomas Vondra > > wrote: > >>> Isn't more effective hold this info in Postgres than in backup sw? > >>> Then any backup sw can use this implementat

Re: Tracking of page changes for backup purposes. PTRACK [POC]

2017-12-27 Thread Andrey Borodin
Hi! > 21 дек. 2017 г., в 5:51, Michael Paquier > написал(а): > > On Thu, Dec 21, 2017 at 7:35 AM, Robert Haas wrote: >> On Wed, Dec 20, 2017 at 3:45 PM, Tomas Vondra >> wrote: Isn't more effective hold this info in Postgres than in backup sw? Then any backup sw can use this implemen

Re: Tracking of page changes for backup purposes. PTRACK [POC]

2017-12-20 Thread Michael Paquier
On Thu, Dec 21, 2017 at 7:35 AM, Robert Haas wrote: > On Wed, Dec 20, 2017 at 3:45 PM, Tomas Vondra > wrote: >>> Isn't more effective hold this info in Postgres than in backup sw? >>> Then any backup sw can use this implementation. >> >> I don't think it means it can't be implemented in Postgres,

Re: Tracking of page changes for backup purposes. PTRACK [POC]

2017-12-20 Thread Robert Haas
On Wed, Dec 20, 2017 at 3:45 PM, Tomas Vondra wrote: >> Isn't more effective hold this info in Postgres than in backup sw? >> Then any backup sw can use this implementation. > > I don't think it means it can't be implemented in Postgres, but does it > need to be done in backend? > > For example, i

Re: Tracking of page changes for backup purposes. PTRACK [POC]

2017-12-20 Thread Tomas Vondra
On 12/20/2017 09:29 PM, Pavel Stehule wrote: > > > 2017-12-20 21:18 GMT+01:00 Robert Haas >: > > On Wed, Dec 20, 2017 at 3:15 PM, Pavel Stehule > mailto:pavel.steh...@gmail.com>> wrote: > >> > So I'm somewhat hesitant to proclaim option 5 as the clear

Re: Tracking of page changes for backup purposes. PTRACK [POC]

2017-12-20 Thread Pavel Stehule
2017-12-20 21:18 GMT+01:00 Robert Haas : > On Wed, Dec 20, 2017 at 3:15 PM, Pavel Stehule > wrote: > >> > So I'm somewhat hesitant to proclaim option 5 as the clear winner, > here. > >> > >> I agree. I think (4) is better. > > > > Can depends on load? For smaller intensive updated databases the

Re: Tracking of page changes for backup purposes. PTRACK [POC]

2017-12-20 Thread Robert Haas
On Wed, Dec 20, 2017 at 3:15 PM, Pavel Stehule wrote: >> > So I'm somewhat hesitant to proclaim option 5 as the clear winner, here. >> >> I agree. I think (4) is better. > > Can depends on load? For smaller intensive updated databases the 5 can be > optimal, for large less updated databases the 4

Re: Tracking of page changes for backup purposes. PTRACK [POC]

2017-12-20 Thread Pavel Stehule
2017-12-20 21:11 GMT+01:00 Robert Haas : > On Tue, Dec 19, 2017 at 5:37 PM, Tomas Vondra > wrote: > > On 12/18/2017 11:18 AM, Anastasia Lubennikova wrote: > >> 1. Use file modification time as a marker that the file has changed. > >> 2. Compute file checksums and compare them. > >> 3. LSN-based m

Re: Tracking of page changes for backup purposes. PTRACK [POC]

2017-12-20 Thread Robert Haas
On Tue, Dec 19, 2017 at 5:37 PM, Tomas Vondra wrote: > On 12/18/2017 11:18 AM, Anastasia Lubennikova wrote: >> 1. Use file modification time as a marker that the file has changed. >> 2. Compute file checksums and compare them. >> 3. LSN-based mechanisms. Backup pages with LSN >= last backup LSN. >

Re: Tracking of page changes for backup purposes. PTRACK [POC]

2017-12-19 Thread Tomas Vondra
Hi, a couple of months ago there was proposal / patch with the similar goals, from Andrey Borodin. See these two threads [1] https://www.postgresql.org/message-id/flat/843D96CC-7C55-4296-ADE0-622A7ACD4978%40yesql.se#843d96cc-7c55-4296-ade0-622a7acd4...@yesql.se [2] https://www.postgresql.org/mes

Re: Tracking of page changes for backup purposes. PTRACK [POC]

2017-12-19 Thread Aleksander Alekseev
Hello Robert, > I think this doesn't really show much because it's apparently limited > by the speed of fsync() on your filesystem. You might try running the > test with synchronous_commit=off. You are right, synchronous_commit=off revealed a noticeable performance degradation. Also I realized t

Re: Tracking of page changes for backup purposes. PTRACK [POC]

2017-12-18 Thread Michael Paquier
On Tue, Dec 19, 2017 at 2:57 AM, Robert Haas wrote: > I think this doesn't really show much because it's apparently limited > by the speed of fsync() on your filesystem. You might try running the > test with synchronous_commit=off. You may want to run Postgres on scissors as much as possible by

Re: Tracking of page changes for backup purposes. PTRACK [POC]

2017-12-18 Thread Michael Paquier
On Mon, Dec 18, 2017 at 7:18 PM, Anastasia Lubennikova wrote: > Patches for v 10.1 and v 9.6 are attached. Why no patch for HEAD? If you are planning to show some performance numbers of some kind you had better run on the latest development version, which would also avoid interference with any lo

Re: Tracking of page changes for backup purposes. PTRACK [POC]

2017-12-18 Thread Robert Haas
On Mon, Dec 18, 2017 at 8:34 AM, Aleksander Alekseev wrote: > 10.1, without ptrack > > transaction type: > scaling factor: 1 > query mode: simple > number of clients: 4 > number of threads: 4 > duration: 300 s > number of transactions actually processed: 28622 > latency average = 41.928 ms > late

Re: Tracking of page changes for backup purposes. PTRACK [POC]

2017-12-18 Thread Aleksander Alekseev
Hello hackers, > I have two concerns about this: > 1. Does this affect the performance of the database when PTRACK is not > enabled? > 2. What is the cost of having PTRACK enabled? I played with this patch a bit and did a simple benchmark on my laptop. Configuration: ``` make distclean && ./co

Re: Tracking of page changes for backup purposes. PTRACK [POC]

2017-12-18 Thread Andrey Borodin
Hello! Thanks for sharing this work! I think this is important feature to make backups more efficient. > 18 дек. 2017 г., в 15:18, Anastasia Lubennikova > написал(а): > > Patches for v 10.1 and v 9.6 are attached. > Since ptrack is basically just an API for use in backup tools, it is > impossi

Re: Tracking of page changes for backup purposes. PTRACK [POC]

2017-12-18 Thread Aleksander Alekseev
Hello Anastasia, > ptrack_9.6.6_v1.4.patch Also I'm surprised you proposed a patch for 9.6. Since PTRACK is a new feature I don't believe we are going to backport it. -- Best regards, Aleksander Alekseev signature.asc Description: PGP signature

Re: Tracking of page changes for backup purposes. PTRACK [POC]

2017-12-18 Thread Aleksander Alekseev
Hello Anastasia, Personally I'm very glad PTRACK is finally proposed to be ported to the community PostgreSQL. > Since ptrack is basically just an API for use in backup tools, it is > impossible to test the patch independently. I believe it's worth to create an extension that will provide access