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