I tried to tune some parameters without appreciable changes in this behaviour. I tried to play with: checkpoint timeout wal size shared buffers commit delay checkpoijnt completion target
No meaningful info found in the log file. Regards 2016-12-04 4:02 GMT+01:00 Tomas Vondra <tomas.von...@2ndquadrant.com>: > On Fri, 2016-12-02 at 13:45 -0800, Adrian Klaver wrote: > > > > On 12/02/2016 09:40 AM, Tom DalPozzo wrote: > > > > > > > > > Hi, > > > I've two tables, t1 and t2, both with one bigint id indexed field > > > and > > > one 256 char data field; t1 has always got 10000 row, while t2 is > > > increasing as explained in the following. > > > > > > My pqlib client countinously updates one row in t1 (every time > > > targeting a different row) and inserts a new row in t2. All this in > > > blocks of 1000 update-insert per commit, in order to get better > > > performance. > > > Wal_method is fsync, fsync is on, attached my conf file. > > > I've a 3.8ghz laptop with evo SSD. > > > > > > Performance is measured every two executed blocks and related to > > > these > > > blocks. > > > > > > Over the first few minutes performance is around 10Krow/s then it > > > slowly > > > drops, over next few minutes to 4Krow/s, then it slowly returns > > > high and > > > so on, like a wave. > > > I don't understand this behaviour. Is it normal? What does it > > > depend on? > > Have you looked at the Postgres log entries that cover these > > episodes? > > > > Is there anything of interest there? > > > In particular look at checkpoints. In the config file you've changed > checkpoint_timeout, but you haven't changed max_wal_size, so my guess > is the checkpoints happen every few minutes, and run for about 1/2 the > time (thanks for completion_target=0.5). That would be consistent with > pattern of good/bad performance. > > regards > > -- > Tomas Vondra http://www.2ndQuadrant.com > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services > -- > Tomas Vondra http://www.2ndQuadrant.com > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services > >
