Обсуждение: INSERT - UPDATE throughput oscillating and SSD activity after stopping the client

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

On Fri, Dec 2, 2016 at 9:40 AM, Tom DalPozzo <t.dalpozzo@gmail.com> 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?

Yes, that is normal.  It is also very complicated.  It depends on pretty much everything.  PostgreSQL, kernel, filesystem, IO controller, firmware, hardware, other things going on on the computer simultaneously, etc.

 

Also, when I stop the client I see the SSD light still heavily working.

This is normal.  It writes out critical data to a WAL log first, and then leisurely writes out the changes to the actual data files later.  In the case of a crash, the WAL will be used to replay the data file changes which may or may not have made it to disk.

It would last quite a while unless I stop the postgresql server, in this case it suddenly stops.

Do you stop postgresql with fast or immediate shutdown?

 

If I restart the server it remains off.

I'm wondering if it's normal. I'd like to be sure that my data are safe once commited.

If your kernel/fs/SSD doesn't lie about syncing the data, then your data is safe once committed. (It is possible there are bugs in PostgreSQL, of course, but nothing you report indicates you have found one).

If you really want to be sure that the full stack, from PostgreSQL down to the hardware on the SSD, is crash safe, the only real way is to do some "pull the plug" tests.

Cheers,

Jeff