Re: INSERT - UPDATE throughput oscillating and SSD activity after stopping the client

Hi,
about SSD light:
I guessed it was WAL -> actual db files data traffic. It explains why the
light stops blinking after shutting down the server (I did it via kill
command) . But if so, I expected the light to restart blinking after
restarting the server (in order to continue WAL->db activity).
Regards

2016-12-05 20:02 GMT+01:00 Jeff Janes <jeff(dot)janes(at)gmail(dot)com>:

> On Fri, Dec 2, 2016 at 9:40 AM, Tom DalPozzo <t(dot)dalpozzo(at)gmail(dot)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
>