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
