Today another DBA suggested that perhaps increasing wal_buffers might be an
option. Currently our wal_buffers is 32MB. Total system memory is 128GB,
FYI.
I'm curious if wal_buffers being full and/or undersized would present
itself in higher COMMIT times as we are observing. We're considering
On Mon, Jan 11, 2021 at 9:06 AM Craig Jackson
wrote:
> How far apart are the min/max connection settings on your application
> connection pool? We had a similar issue with connection storms in the past
> on Oracle. One thing we did to minimize the storms was make sure there was
> not a wide gap
How far apart are the min/max connection settings on your application
connection pool? We had a similar issue with connection storms in the past
on Oracle. One thing we did to minimize the storms was make sure there was
not a wide gap between the min/max, say no more than a 5-10 connection
On Sat, Jan 9, 2021 at 2:07 PM Jeff Janes wrote:
>
> How are you monitoring the COMMIT times? What do you generally see in
> pg_stat_activity.wait_event during the spikes/stalls?
>
Right now we just observe the COMMIT duration posted in the postgresql log
(we log anything over 100ms).
One
On Wed, Jan 6, 2021 at 11:19 AM Don Seiler wrote:
> Good morning,
>
> This week we've noticed that we're starting to see spikes where COMMITs
> are taking much longer than usual. Sometimes, quite a few seconds to
> finish. After a few minutes they disappear but then return seemingly at
> random.
Yes, these were deferrable foreign key constraints.
On Fri, Jan 8, 2021 at 2:05 AM Laurenz Albe
wrote:
> On Thu, 2021-01-07 at 10:49 -0700, Craig Jackson wrote:
> > We had a similar situation recently and saw high commit times that were
> caused
> > by having unindexed foreign key columns when
On Fri, Jan 8, 2021 at 11:52 AM José Arthur Benetasso Villanova <
jose.art...@gmail.com> wrote:
> Do you have standby databases and synchronous_commit = 'remote_apply'?
>
We have standby databases, but synchronous_commit is "on".
I found a pair of unindexed foreign key child tables but they
On Thu, Jan 7, 2021 at 3:03 PM Don Seiler wrote:
> On Thu, Jan 7, 2021 at 11:50 AM Craig Jackson
> wrote:
>
>> We had a similar situation recently and saw high commit times that were
>> caused by having unindexed foreign key columns when deleting data with
>> large tables involved. You might
On Thu, 2021-01-07 at 10:49 -0700, Craig Jackson wrote:
> We had a similar situation recently and saw high commit times that were caused
> by having unindexed foreign key columns when deleting data with large tables
> involved.
> You might check to see if any new foreign key constraints have
On Thu, Jan 7, 2021 at 11:50 AM Craig Jackson
wrote:
> We had a similar situation recently and saw high commit times that were
> caused by having unindexed foreign key columns when deleting data with
> large tables involved. You might check to see if any new foreign key
> constraints have been
We had a similar situation recently and saw high commit times that were
caused by having unindexed foreign key columns when deleting data with
large tables involved. You might check to see if any new foreign key
constraints have been added recently or if any foreign key indexes may have
On Wed, 2021-01-06 at 10:19 -0600, Don Seiler wrote:
> This week we've noticed that we're starting to see spikes where COMMITs are
> taking much longer than usual.
> Sometimes, quite a few seconds to finish.
>
> This is PostgreSQL 12.4 on Ubuntu 18.04, all running in MS Azure (*not*
> managed
Azure VMs do have their own IOPS limits that increase with increasing VM
"size". In this current case our VM size puts that VM IOPS limit well above
anything the disks are rated at, so it shouldn't be a bottleneck.
On Wed, Jan 6, 2021 at 1:15 PM Kenneth Marshall wrote:
> On Wed, Jan 06, 2021 at
On Wed, Jan 06, 2021 at 12:06:27PM -0600, Don Seiler wrote:
> On Wed, Jan 6, 2021 at 10:51 AM Joshua Drake wrote:
>
> Looking at the Azure portal metric, we are nowhere close to the advertised
> maximum IOPS or MB/s throughput (under half of the maximum IOPS and under a
> quarter of the MB/s
>
> Looking at the Azure portal metric, we are nowhere close to the advertised
> maximum IOPS or MB/s throughput (under half of the maximum IOPS and under a
> quarter of the MB/s maximum). So there must be some other bottleneck in
> play. The IOPS limit on this VM size is even higher so that
On Wed, Jan 6, 2021 at 10:51 AM Joshua Drake wrote:
> I have the stats_temp_directory in a tmpfs mount. I *do* have pg_wal on
>> the same premium SSD storage volume as the data directory. Normally I would
>> know to separate these but I was told with the cloud storage that it's all
>>
> I have the stats_temp_directory in a tmpfs mount. I *do* have pg_wal on
> the same premium SSD storage volume as the data directory. Normally I would
> know to separate these but I was told with the cloud storage that it's all
> virtualized anyway, plus storage IOPS are determined by disk size
17 matches
Mail list logo