it so we didn't use
CONCURRENTLY either. The size went from 83GB to 48GB, which also happens to
be the size of the table partition.
--
Don Seiler
www.seiler.us
the template (using an older version of pg_partman).
Don.
--
Don Seiler
www.seiler.us
s run.
>
What do you mean by "absorb" the inserts?
It sounds like the answer will be "No", but: Would rebuilding the index
after the month-end (when inserts have stopped on this partition) change
anything?
Don.
--
Don Seiler
www.seiler.us
smaller?
Both indexes were created using CONCURRENTLY, as each was created during
its month when we started testing. The September index was created toward
the end of the month (Sep 26), whereas the October one was created Oct 1.
Both table partitions are getting regularly autovacuum/autoanalyze work.
Don.
--
Don Seiler
www.seiler.us
On Wed, Oct 30, 2024 at 10:35 AM Peter Geoghegan wrote:
> On Wed, Oct 30, 2024 at 11:24 AM Don Seiler wrote:
> > One thing worth mentioning is that the table is 4 columns, the index is
> on two of them and includes the other two. I can't think of an explanation
> for the
r
the index being so much larger than its table, especially compared to last
month's index.
Curious if anyone has any thoughts on what might be causing this.
--
Don Seiler
www.seiler.us
7;s tighter nowadays, but I really doubt that
> it's exact-to-the-kilobyte-at-all-times.
>
In this case, the total volume size was 60GB and we had the parameter set
to 58GB but I imagine that can still be overwhelmed quickly. Maybe we
should target a 20% buffer zone? We have wal_kee
deals with
situations where the replication slot lag is a factor?
Don.
--
Don Seiler
www.seiler.us
led)
Does this mean that downgrading libpq wouldn't help? He'd have to downgrade
openssl instead (or wait for a fix from somewhere upstream)?
Don.
--
Don Seiler
www.seiler.us
ences or if this is a
known issue?
--
Don Seiler
www.seiler.us
On Tue, Oct 3, 2023 at 10:27 AM Don Seiler wrote:
> On the source (PG 12.15) instance, we have bumped max_replication_slots
> and max_wal_senders to 50, and max_sync_workers_per_subscription to 10.
>
Forgot to note that on the subscriber (PG 15.4) instance,
max_sync_workers_per_subscr
20 replication
slots created (out of 50 max), 3 of which are the subscriptions and the
rest are the tablesyncs workers.
So I'm trying to make sense of why it would tell me to increase
max_replication_slots when I don't appear to be anywhere near the max.
--
Don Seiler
www.seiler.us
es VM sizes. There's
obviously going to be a little extra HugePages that goes unused, but these
VMs are dedicated for postgresql usage and shared_buffers_size defaults to
25% of VM memory so there's still plenty to spare. But we use this so we
can configure vm.nr_hugepages at deployment time via Chef.
Don.
--
Don Seiler
www.seiler.us
is from a different pid, maybe some kind of cleanup process? It
was doing this for what seemed like all of the sync slots:
2023-08-03 18:47:47.975 UTC [3303] migrator@foo - sub01 ERROR: replication
slot "pg_19742_sync_17238_7263122209699118815" does not exist
2023-08-03 18:47:47.975 UTC [3303] migrator@foo - sub01 STATEMENT:
DROP_REPLICATION_SLOT pg_19742_sync_17238_7263122209699118815 WAIT
--
Don Seiler
www.seiler.us
sync workers in action so this was a big surprise. Is this
expected behavior?
--
Don Seiler
www.seiler.us
On Wed, Jun 7, 2023 at 4:30 PM Jeremy Schneider
wrote:
> On 6/7/23 2:12 PM, Don Seiler wrote:
> > On the logical replication front, the concern is with the initial data
> > import that happens when the subscription is created (by default). I
> > know that you can tell th
not. I know to only use the minimal
indexes required on the destination side (ie identity-related indexes) and
omit other indexes and constraints until after the data is loaded, but that
is true for either method.
Thanks,
Don.
--
Don Seiler
www.seiler.us
ondering if I'm on the right trail or if there is a much firmer
explanation for what happened.
Thanks,
Don.
--
Don Seiler
www.seiler.us
On Mon, Mar 29, 2021 at 5:22 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Mon, Mar 29, 2021 at 3:20 PM Adrian Klaver
> wrote:
>
>> On 3/29/21 3:00 PM, Don Seiler wrote:
>> >
>> > I'm wondering if this is expected behavior that PG us
logic was used in an UPDATE and
ended up locking all rows in a 5M row table and brought many apps to a
grinding halt. Thankfully it was caught and killed before it actually
updated anything.
Thanks,
Don.
--
Don Seiler
www.seiler.us
#x27;m
wondering if there is risk or harm in running an UPDATE pg_database command
on postgres/template0/template1 as needed and re-indexing afterward.
--
Don Seiler
www.seiler.us
. We shouldn't
be at risk of hitting wraparound though (only 52% there).
Don.
--
Don Seiler
www.seiler.us
> by reducing "autovacuum_vacuum_cost_delay" to 2ms or less, and by
> increasing
> "maintenance_work_mem".
>
All autovacuum settings on this DB are default. Cost delay is at the
default 20ms. maintenance_work_mem I've already increased to 512MB (this VM
has 8GB RAM).
--
Don Seiler
www.seiler.us
bles
in our app DB triggering the autovacuum "to prevent wrap-around" when they
reach 200M. That's what had me concerned to see template0 with an age over
1B and no autovacuum even trying to clean up for it.
Don.
--
Don Seiler
www.seiler.us
progress, with over 850M dead tuples according to
pg_stat_all_tables. I estimate 3-4 more days to go on that one. Once that's
done I'll be scheduling manual vacuum jobs. Just wondering if that would
somehow affect regular template0 cleanup though.
I don't see anything in postgres log relat
On Tue, Apr 7, 2020 at 11:41 AM Don Seiler wrote:
>
> Follow-up question, the locale setting on the host would still be set to
> en_US (as would the postgres and template0 databases). Should I look to
> change that locale on the system to en_US.UTF-8, or even just for the
> pos
On Mon, Mar 30, 2020 at 4:39 PM Don Seiler wrote:
> On Mon, Mar 30, 2020 at 4:30 PM Tom Lane wrote:
>
>> Don Seiler writes:
>> > Actually, would I need to re-index on text columns that we know contain
>> > UUID strings? UUID characters seem to be pretty basic alph
On Mon, Mar 30, 2020 at 4:30 PM Tom Lane wrote:
> Don Seiler writes:
> > Actually, would I need to re-index on text columns that we know contain
> > UUID strings? UUID characters seem to be pretty basic alphanumeric ASCII
> > characters.
>
> I think you're all ri
On Sun, Mar 22, 2020 at 4:48 PM Don Seiler wrote:
>
> Here's the fun part. A lot of the tables use UUIDv4 strings for primary
> keys. However these are stored in text/varchar columns.
>
Actually, would I need to re-index on text columns that we know contain
UUID strings? UUID c
best course of action in my opinion if you have any
> doubts, because that's safe even if it has a higher cost.
>
Here's the fun part. A lot of the tables use UUIDv4 strings for primary
keys. However these are stored in text/varchar columns.
--
Don Seiler
www.seiler.us
after testing all of that heavily)?
What are the ramifications of changing collation like that? Should we
consider rebuilding indexes ASAP after that?
Don.
--
Don Seiler
www.seiler.us
On Tue, Mar 17, 2020 at 8:06 AM Don Seiler wrote:
> On Mon, Mar 16, 2020 at 10:28 AM Tom Lane wrote:
>
>>
> Well, in principle you could likewise manually update pg_database's
>> datcollate and datctype columns to say "en_US.utf8". However, there's
>
would logical replication safely replicate
and convert the data until we could then cut over?
Thanks,
Don.
--
Don Seiler
www.seiler.us
be sure.
Is logical replication an option here? Either maintaining the mixed
environment or converting everything to en_US.UTF-8? I'm relatively new in
this shop but I'm told they didn't mean to use en_US and there's no reason
they wouldn't want to just use the standard/default UTF-8.
Thanks,
Don.
--
Don Seiler
www.seiler.us
On Mon, Mar 2, 2020, 12:30 Vik Fearing wrote:
> On 02/03/2020 18:09, Don Seiler wrote:
> > The REAL reason for this is that I'm wondering if I created a NOT NULL
> > check constraint with "NOT VALID" would that then NOT be considered in
> such
> > a "
Planning time: 0.390 ms
Execution time: 2339.274 ms
(6 rows)
--
Don Seiler
www.seiler.us
optimizer/planner when
evaluating execution plans.
--
Don Seiler
www.seiler.us
provides a clean CLI for building
and using all the various supported versions:
https://github.com/theory/pgenv
Don.
--
Don Seiler
www.seiler.us
lication will only read from the WAL files in the
$PGDATA/pg_xlog directory. It will not read from archives. So, yes, you
would need your NFS mount on the replica (or otherwise copy the archive
files to the replica).
Don.
--
Don Seiler
www.seiler.us
n index on that
field.
Even as I experiment with some query rewrites, the EXPLAIN ANALYZE always
says rows=75. I'm *very* curious to see why it is using that value.
Don.
--
Don Seiler
www.seiler.us
ecords IS NOT NULL;
The foo.gifts table is pretty much the core table of our database. It's big
and very active. There is an index on date_added but not yet on
date_gifted.
I'm working to re-write the query while the dev sees if we even need this
query anymore.
On Wed, Aug 15, 2018 at 2:39
s that is far more selective and avoids throwing rows away. However I'm
very interested in why every node dealing with the gifts table thinks
rows=75 when the actual is much, much higher. And 75 seems like too round
of a number to be random?
--
Don Seiler
www.seiler.us
probably look to set it but make sure
to point out that it is just a safety net to let DB maintenance run and
they should make sure their work is committed cleanly if they want to keep
it.
Don.
--
Don Seiler
www.seiler.us
way. But if they're also NOT closing cursors, that seems like
another bad practice to correct.
Would commit/rollback automatically close cursors opened in that
transaction?
Don.
--
Don Seiler
www.seiler.us
On Mon, Aug 13, 2018 at 5:55 PM, Vick Khera wrote:
> On Mon, Aug 13, 2018 at 5:19 PM, Don Seiler wrote:
>
>>
>> I don't quite follow this. What circumstances would lead to this
>> situation?
>>
>
> BEGIN WORK;
> DECLARE CURSOR ... ;
> FETCH ...;
On Mon, Aug 13, 2018 at 4:04 PM, Don Seiler wrote:
>
>
> Anyway, my next step is getting the OK to terminate those idle in
> transaction sessions to see if that gets my vacuum job moving. Meanwhile
> I'll ask a dev to sort out why they might be sitting idle in transaction,
>
or what its worth, these sessions are backend reporting jobs, not user
interfacing at all.
Don.
--
Don Seiler
www.seiler.us
e I
could/should also look to get to the root cause of this.
Don.
--
Don Seiler
www.seiler.us
On Wed, Jun 13, 2018 at 11:16 AM, wrote:
>
>
> Can anyoen explain WHY there is such a big difference? Is it the SQL
> statement or a bug in the pgdump ?
>
>
Did you analyze the database after upgrading? Or at least the tables in
question? Those are very different plan
On Tue, Jun 12, 2018 at 10:48 AM, Don Seiler wrote:
> On Tue, Jun 12, 2018 at 2:26 AM, Haug Bürger
> wrote:
>
>>
>> "availability_cache_ac_session_id_fkey" FOREIGN KEY (ac_session_id)
>> REFERENCES zpg_data.session(id) ON DELETE CA
atically indexed and that can lead to horrible performance on
cascading operations like this.
--
Don Seiler
www.seiler.us
ld
> do this.
>
Are you using the PGDG repo packages, or the default CentOS repo packages?
You should use PGDG and those should install under /var/lib/pgsql.
Don.
--
Don Seiler
www.seiler.us
judgment on the site design until it is functioning.
Those links work fine for me on the new site. I haven't hit any issues yet
in my casual click-testing.
--
Don Seiler
www.seiler.us
On Wed, Apr 11, 2018 at 1:38 PM, Don Seiler wrote:
>
> Yeah, I saw the same with a 132 row insert. Now imagine that with a
> monthly 50 million row insert or delete. :p Thanks for the confirmation!
>
I went back to look at the postgres logs on my dev server. These logs are
rotated o
rameters: $1 = '3',
> $2 = 'three', $3 = 'f'
>
> So much for that idea(:=
Yeah, I saw the same with a 132 row insert. Now imagine that with a monthly
50 million row insert or delete. :p Thanks for the confirmation!
I'm definitely leaning towards the copy/load/delete method.
Don.
--
Don Seiler
www.seiler.us
On Wed, Apr 11, 2018 at 1:13 PM, Don Seiler wrote:
>
> I had considered this as well, as this would allow me to rollback the
> delete (assuming my intel on postgres_fdw transactions was correct, which
> it may not be after all). I wondered if a remote insert would be broken up
>
like the remote delete was, as that would be
equally unappealing for the same reasons. But obviously worth confirming.
Don.
--
Don Seiler
www.seiler.us
to hear what
others might be doing for tasks like this.
Don.
--
Don Seiler
www.seiler.us
than symlinking anyway. I'll look to do
that in my next round of config changes.
--
Don Seiler
www.seiler.us
They aren't needed for database
restore or recovery.
Don.
--
Don Seiler
www.seiler.us
t ready
to make the changes necessary in the production environment.
--
Don Seiler
www.seiler.us
ut for these
cases I just recover it to the first consistent point and open it for
testing (or backups in this case).
Thanks for all your help!
Don.
--
Don Seiler
www.seiler.us
ession as well but that
pg_basebackup does do.
I did come up with a sort of Rube Goldberg-esque workaround for now
involving using a clone of the prod standby VM from Veeam backup to use as
the backup source (after stopping recovery and opening it as a standalone
DB).
Don.
--
Don Seiler
www.seiler.us
On Mon, Feb 19, 2018 at 8:23 AM, David Steele wrote:
>
> Either is fine with me, but as Michael says I might miss postings to
> -general. I'm sure somebody else would catch it, though.
>
OK, I'll make use of the issues tracker going forward.
--
Don Seiler
www.seiler.us
hed my master/replica clone setup but I'll
test there as well. I just had a couple questions about the mechanics.
--
Don Seiler
www.seiler.us
see any mention of a forum
or list on their website, and there have been lots of pgBackRest questions
on this list in the past so I settled on this one.
Don.
--
Don Seiler
www.seiler.us
Does pgBackRest need to be installed and configured on the primary as
well?
Thanks,
Don.
--
Don Seiler
www.seiler.us
alyze-only
I wonder if it wouldn't be a bad idea to also mention the --jobs=N
parameter option in that blurb. Yes it's in the --help text but it wouldn't
be bad to highlight its availability.
Don.
--
Don Seiler
www.seiler.us
I don't see any errors in /var/log/messages that would incidate any
filesystem issues either. Obviously the worry is for possible corruption.
Granted this is "only" pre-prod but there will be concerns from the
business side prior to upgrading our prod DB (currently 9.2.22).
What else can/should I check here to make sure there isn't something wrong
with this database cluster?
--
Don Seiler
www.seiler.us
69 matches
Mail list logo
