Re: [GENERAL] Strange checkpoint behavior - checkpoints take a longtime

2017-10-05 Thread Andres Freund
Hi, On 2017-10-06 05:53:39 +0300, Vladimir Nicolici wrote: > Hello, it’s postgres 9.6. Consider setting checkpoint_flush_after to 16MB or something large like that. > I will probably try the compression on Monday or Tuesday, I can only > experiment with a single set of changes in a day, and I

Re: [GENERAL] Strange checkpoint behavior - checkpoints take a longtime

2017-10-05 Thread Vladimir Nicolici
Hello, it’s postgres 9.6 . I know reducing wal_buffers from 32 MB to 16MB is unlikely to help, but according to the documentation values larger than 16MB are unlikely to help either, at least at the default wal segment size, so I decided to go with 16 MB. I will probably try the compression on

Re: [GENERAL] Strange checkpoint behavior - checkpoints take a long time

2017-10-05 Thread Andres Freund
Hi, On 2017-10-05 22:58:31 +0300, Vladimir Nicolici wrote: > I changed some configuration parameters during the night to the values I was > considering yesterday: > > - shared_buffers = 144GB #previously 96 GB > - bgwriter_lru_maxpages = 100 #previously 400 > - checkpoint_timeout = 30min

Re: [GENERAL] Strange checkpoint behavior - checkpoints take a longtime

2017-10-05 Thread Vladimir Nicolici
My primary goal is to determine the current level of utilization of the current hardware + software combination, and how much more load it can handle. It’s quite likely that volume of work performed by that database will need to increase by 50% or more over the next months, and I don’t want to

Re: [GENERAL] Strange checkpoint behavior - checkpoints take a long time

2017-10-05 Thread Igor Polishchuk
Vladimir, Just curious, if your goal is to reduce checkpoint overhead, shouldn’t you decrease shared_buffers instead of increasing it? With bigger shared_buffers, you can accumulate more dirty buffers for checkpoint to take care. I remember in early versions ( around 8.4), when

Re: [GENERAL] Strange checkpoint behavior - checkpoints take a long time

2017-10-05 Thread Vladimir Nicolici
Some further updates about the issue. I did a bit of benchmarking on the disk system with iozone, and the during the test the SSDs seemed to be able to easily sustain 200 MB/second of writes each, they fluctuated between 200 MB/s and 400 MB/s when doing 96 GB of random writes in a file. That

Re: [GENERAL] Postgresql CDC tool recommendations ?

2017-10-05 Thread Steve Atkins
> On Oct 5, 2017, at 10:28 AM, avi Singh wrote: > > Guys > Any recommendation on a good CDC tool that can be used to push > postgresql changes to Kafka in json format ? Not sure whether json is a constraint, but I'd look at http://debezium.io and (maybe)

[GENERAL] Postgresql CDC tool recommendations ?

2017-10-05 Thread avi Singh
Guys Any recommendation on a good CDC tool that can be used to push postgresql changes to Kafka in json format ? Thanks Avi

Re: [GENERAL] delete a file everytime pg server starts/crashes

2017-10-05 Thread Ray Cote
> > On Thu, Oct 5, 2017 at 10:04 AM, athinivas wrote: > >> Hi, >> >> I'm having a requirement to delete a file in system whenever pg server is >> started/crashed. Any idea? >> >> Thanks, >> Athi >> >> If you’re running on Linux you can modify the init.d (or service) file and

Re: [GENERAL] delete a file everytime pg server starts/crashes

2017-10-05 Thread Melvin Davidson
On Thu, Oct 5, 2017 at 10:04 AM, athinivas wrote: > Hi, > > I'm having a requirement to delete a file in system whenever pg server is > started/crashed. Any idea? > > Thanks, > Athi > > > > -- > Sent from: http://www.postgresql-archive.org/PostgreSQL-general- > f1843780.html

[GENERAL] delete a file everytime pg server starts/crashes

2017-10-05 Thread athinivas
Hi, I'm having a requirement to delete a file in system whenever pg server is started/crashed. Any idea? Thanks, Athi -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to

Re: [GENERAL] a JOIN to a VIEW seems slow

2017-10-05 Thread Frank Millman
On 5 Oct 2017, at 8:20 AM, Frank Millman wrote: > If anyone wants to take this further, maybe this is a good place to start. I should have re-stated the reason for my original post. Exactly the same query, on exactly the same data, takes 1.8 seconds on Sql Server, 1.0 seconds on SQLite3, and

Re: [GENERAL] a JOIN to a VIEW seems slow

2017-10-05 Thread Frank Millman
On 4 Oct 2017, at 9:19 PM, Alban Hertroys wrote: > On 2 Oct 2017, at 8:32, Frank Millman wrote: > > > On Fri, Sep 22, 2017 at 7:34 AM, Frank Millman wrote: > > > > On Thu, Sep 21, 2017 at 3:54 PM, Merlin Moncure wrote: > > > Something is not adding up here. Can you