Re: Recommended value for pg_test_fsync
On Tue, Jun 30, 2020 at 10:32:13AM +0530, Nikhil Shetty wrote: > Hi Bruce, > > Based on pg_test_fsync results, should we choose open_datasync or fdatasync as > wal_sync_method? Can we rely on pg_test_fsync for choosing the best I would just pick the fastest method, but if the method is _too_ fast, it might mean that it isn't actually writing to durable storage. > wal_sync_method or is there any other way? pg_test_fsync is the only way I know of, which is why I wrote it. -- Bruce Momjian https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee
Re: Recommended value for pg_test_fsync
On Mon, Jun 29, 2020 at 5:27 AM Nikhil Shetty wrote: > Hi Team, > > We have a PostgreSQL 11.5.6 database running on VM. > RAM - 48GB > CPU - 6 cores > Disk - SSD on SAN > > We wanted to check how the WAL disk is performing using pg_test_fsync.We > ran a test and got around 870 ops/sec for opendatasync and fdatasync and > just 430 ops/sec for fsync.We feel it is quite low as compared to what we > get for local storage(2000 ops/sec for fsync). > It is not surprising to me that SAN would have higher latency than internal storage. What kind of connection do you have between your server and your SAN? > What is the recommended value for fsync ops/sec for PosgreSQL WAL disks on > SAN ? > You have the hardware you have. You can't change it the same way you can change a config file entry, so I don't think that "recommended value" really applies. Is the latency of sync requests a major bottleneck for your workload? pg_test_fsync can tell you what the latency is, but can't tell you how much you care. Cheers, Jeff >
Re: Recommended value for pg_test_fsync
On Tue, Jun 30, 2020 at 1:02 AM Nikhil Shetty wrote: > Hi Bruce, > > Based on pg_test_fsync results, should we choose open_datasync or > fdatasync as wal_sync_method? Can we rely on pg_test_fsync for choosing the > best wal_sync_method or is there any other way? > Probably the default of fdatasync. The place where pg_test_fsync would tell me not to use fdatasync is if it were so fast that it was not credible that it was honestly syncing the data. I don't think pg_test_fsync does a good job of exercising the realistic differences between fdatasync and open_datasync. So unless it shows that one of them is lying about the durability, it doesn't offer much help. Cheers, Jeff
Is there a known bug with SKIP LOCKED and "tuple to be locked was already moved to another partition due to concurrent update"?
Hi all, long time ago I devised with your help a task queuing system which uses SELECT ... FOR UPDATE SKIP LOCKED for many parallel workers to find tasks in the queue, and it used a partitioned table where the hot part of the queue is short and so the query for a job is quick and the skip locked locking makes sure that one job is only assigned to one worker. And this works pretty well for me, except that when we run many workers we find a lot of these failures occurring: "tuple to be locked was already moved to another partition due to concurrent update" This would not exactly look like a bug, because the message says "to be locked", so at least it's not allowing two workers to lock the same tuple. But it seems that the skip-locked mode should not make an error out of this, but treat it as the tuple was already locked. Why would it want to lock the tuple (representing the job) if another worker has already finished his UPDATE of the job to mark it as "done" (which is what makes the tuple move to the "completed" partition.) Either the SELECT for jobs to do returned a wrong tuple, which was already update, or there is some lapse in the locking. Either way it would seem to be a waste of time throwing all these errors when the tuple should not even have been selected for update and locking. I wonder if anybody knows anything about that issue? Of course you'll want to see the DDL and SQL queries, etc. but you can't really try it out unless you do some massively parallel magic. So I figured I just ask. regards, -Gunther
