pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedTable size is constantly growing and causing performance problems
14+ messages / 6 participants
[nested] [flat]
* Table size is constantly growing and causing performance problems
@ 2025-02-12 04:44 srinivasan s <srinioracledba7@gmail.com>
2025-02-12 06:39 ` Re: Table size is constantly growing and causing performance problems Thiemo Kellner <thiemo@gelassene-pferde.biz>
2025-02-12 06:56 ` Re: Table size is constantly growing and causing performance problems srinivasan s <srinioracledba7@gmail.com>
2025-02-12 07:29 ` Re: Table size is constantly growing and causing performance problems Laurenz Albe <laurenz.albe@cybertec.at>
2025-02-12 07:38 ` Re: Table size is constantly growing and causing performance problems shammat@gmx.net
2025-02-13 00:01 ` Re: Table size is constantly growing and causing performance problems Jeff Janes <jeff.janes@gmail.com>
0 siblings, 5 replies; 14+ messages in thread
From: srinivasan s @ 2025-02-12 04:44 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi Team,
One of the tables in our database suddenly started growing very fast
without any changes to the environment. it has grown over 10GB in the last
week and this is causing performance issues. We ended up adding an index to
solve the performance problem but the table growth didn't stop. It is
growing continuously. we are using postgres version 12 on ubuntu
We are running a vacuum analsye on a full database every weekend and an
auto vacuum is set up.
My observation on the DB so far.
1. The table is bloated around 50GB is bloat out of 95GB table size
* current_database | schemaname | tablename
| tbloat | wastedbytes*
*---------------------+------------+-----------------------------------------------------------------+--------+-------------*
*xyxyxyxy_production | public | xxxxxx
| 2.5 | 51894779904*
2. the last autovacuum
* table_name | last_autovacuum | last_autoanalyze
| last_vacuum | last_analyze*
*------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------*
* xxxxxx | 2025-02-09 03:09:46.938845+00 | 2025-02-07
23:45:11.367977+00 | 2025-02-08 03:12:33.468125+00 | 2025-02-10
21:09:57.461369+00*
*(1 row)*
3. Also noticed that there is a auto vacuum job running on the table with
(to prevent wraparound)
I am not sure if this auto vacuum (to prevent wraparound) is progressing,
it is running for more than 15 hours and status is active. Will this block
the regular auto vacuum?
My assumption is the bloated rows on the table are not reused and the table
is growing. I am trying to find out the cause and fix it.
Looking for some help around this topic, any suggestions or ideas to
troubleshoot and fix would be highly appreciated.
Thanks,
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Table size is constantly growing and causing performance problems
2025-02-12 04:44 Table size is constantly growing and causing performance problems srinivasan s <srinioracledba7@gmail.com>
@ 2025-02-12 06:39 ` Thiemo Kellner <thiemo@gelassene-pferde.biz>
4 siblings, 0 replies; 14+ messages in thread
From: Thiemo Kellner @ 2025-02-12 06:39 UTC (permalink / raw)
To: PostgreSQL Admin <pgsql-admin@lists.postgresql.org>
Hi
12.02.2025 05:45:36 srinivasan s <srinioracledba7@gmail.com>:
> One of the tables in our database suddenly started growing very fast without any changes to the environment.
> we are using postgres version 12 on ubuntu
Is there a reason, you stay on v12 when v17 is available?
Are we to understand that the table takes in news rows but also those get deleted in about the same number so only little grow is expected if at all? Does count(*) reflect the size growth? If not did you check your processes for insert/delete? Do they apply/affect the same amount of rows before? Has there popped up an imbalance? I am assuming the processes as in my second question.
Cheers Thiemo
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Table size is constantly growing and causing performance problems
2025-02-12 04:44 Table size is constantly growing and causing performance problems srinivasan s <srinioracledba7@gmail.com>
@ 2025-02-12 06:56 ` srinivasan s <srinioracledba7@gmail.com>
4 siblings, 0 replies; 14+ messages in thread
From: srinivasan s @ 2025-02-12 06:56 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
attaching vacuum related configuration parameter values to the thread
On Wed, Feb 12, 2025 at 10:14 AM srinivasan s <srinioracledba7@gmail.com>
wrote:
> Hi Team,
>
> One of the tables in our database suddenly started growing very fast
> without any changes to the environment. it has grown over 10GB in the last
> week and this is causing performance issues. We ended up adding an index to
> solve the performance problem but the table growth didn't stop. It is
> growing continuously. we are using postgres version 12 on ubuntu
>
> We are running a vacuum analsye on a full database every weekend and an
> auto vacuum is set up.
>
> My observation on the DB so far.
> 1. The table is bloated around 50GB is bloat out of 95GB table size
>
> * current_database | schemaname | tablename
> | tbloat | wastedbytes*
>
> *---------------------+------------+-----------------------------------------------------------------+--------+-------------*
> *xyxyxyxy_production | public | xxxxxx
> | 2.5 | 51894779904*
>
> 2. the last autovacuum
>
> * table_name | last_autovacuum | last_autoanalyze
> | last_vacuum | last_analyze*
>
> *------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------*
> * xxxxxx | 2025-02-09 03:09:46.938845+00 | 2025-02-07
> 23:45:11.367977+00 | 2025-02-08 03:12:33.468125+00 | 2025-02-10
> 21:09:57.461369+00*
> *(1 row)*
>
> 3. Also noticed that there is a auto vacuum job running on the table with
> (to prevent wraparound)
>
> I am not sure if this auto vacuum (to prevent wraparound) is progressing,
> it is running for more than 15 hours and status is active. Will this block
> the regular auto vacuum?
>
> My assumption is the bloated rows on the table are not reused and the
> table is growing. I am trying to find out the cause and fix it.
>
> Looking for some help around this topic, any suggestions or ideas to
> troubleshoot and fix would be highly appreciated.
>
> Thanks,
>
>
>
>
>
Attachments:
[text/csv] vacuum.csv (2.1K, ../../CAOEeMcWvLVzaEPPf5UURALESt0JgpEue1jHWEp8s326q3HugqQ@mail.gmail.com/3-vacuum.csv)
download | inline:
name,setting,unit,category,short_desc,extra_desc,context,vartype,source,min_val,max_val,enumvals,boot_val,reset_val,sourcefile,sourceline,pending_restart
autovacuum,on,,Autovacuum,Starts the autovacuum subprocess.,,sighup,bool,default,,,,on,on,,,f
autovacuum_analyze_scale_factor,0.1,,Autovacuum,"Number of tuple inserts, updates, or deletes prior to analyze as a fraction of reltuples.",,sighup,real,default,0,100,,0.1,0.1,,,f
autovacuum_analyze_threshold,50,,Autovacuum,"Minimum number of tuple inserts, updates, or deletes prior to analyze.",,sighup,integer,default,0,2147483647,,50,50,,,f
autovacuum_freeze_max_age,200000000,,Autovacuum,Age at which to autovacuum a table to prevent transaction ID wraparound.,,postmaster,integer,default,100000,2000000000,,200000000,200000000,,,f
autovacuum_max_workers,5,,Autovacuum,Sets the maximum number of simultaneously running autovacuum worker processes.,,postmaster,integer,configuration file,1,262143,,3,5,/etc/postgresql/12/main/postgresql.conf,70,f
autovacuum_multixact_freeze_max_age,400000000,,Autovacuum,Multixact age at which to autovacuum a table to prevent multixact wraparound.,,postmaster,integer,default,10000,2000000000,,400000000,400000000,,,f
autovacuum_naptime,60,s,Autovacuum,Time to sleep between autovacuum runs.,,sighup,integer,default,1,2147483,,60,60,,,f
autovacuum_vacuum_cost_delay,10,ms,Autovacuum,"Vacuum cost delay in milliseconds, for autovacuum.",,sighup,real,configuration file,-1,100,,2,10,/etc/postgresql/12/main/postgresql.conf,67,f
autovacuum_vacuum_cost_limit,200,,Autovacuum,"Vacuum cost amount available before napping, for autovacuum.",,sighup,integer,configuration file,-1,10000,,-1,200,/etc/postgresql/12/main/postgresql.conf,69,f
autovacuum_vacuum_scale_factor,0.15,,Autovacuum,Number of tuple updates or deletes prior to vacuum as a fraction of reltuples.,,sighup,real,configuration file,0,100,,0.2,0.15,/etc/postgresql/12/main/postgresql.conf,71,f
autovacuum_vacuum_threshold,50,,Autovacuum,Minimum number of tuple updates or deletes prior to vacuum.,,sighup,integer,default,0,2147483647,,50,50,,,f
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Table size is constantly growing and causing performance problems
2025-02-12 04:44 Table size is constantly growing and causing performance problems srinivasan s <srinioracledba7@gmail.com>
@ 2025-02-12 07:29 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-02-12 07:35 ` Re: Table size is constantly growing and causing performance problems srinivasan s <srinioracledba7@gmail.com>
4 siblings, 1 reply; 14+ messages in thread
From: Laurenz Albe @ 2025-02-12 07:29 UTC (permalink / raw)
To: srinivasan s <srinioracledba7@gmail.com>; pgsql-admin@lists.postgresql.org
On Wed, 2025-02-12 at 10:14 +0530, srinivasan s wrote:
> One of the tables in our database suddenly started growing very fast without
> any changes to the environment. it has grown over 10GB in the last week and
> this is causing performance issues. We ended up adding an index to solve the
> performance problem but the table growth didn't stop. It is growing continuously.
> we are using postgres version 12 on ubuntu
That's a mistake. Use a supported version. As it is, you could be suffering
from some already fixed (data corruption?) bug.
> We are running a vacuum analsye on a full database every weekend and an auto
> vacuum is set up.
VACUUM (FULL) is also a mistake. It should be a regular VACUUM.
Funny, you say that you are experiencing bloat. How can that be if you are
running VACUUM (FULL)? Perhaps something is blocking VACUUM from removing
dead rows:
https://www.cybertec-postgresql.com/en/reasons-why-vacuum-wont-remove-dead-rows/
> My observation on the DB so far.
>
> [...]
>
> 3. Also noticed that there is a auto vacuum job running on the table with
> (to prevent wraparound)
>
> I am not sure if this auto vacuum (to prevent wraparound) is progressing,
> it is running for more than 15 hours and status is active.
You should check "pg_stat_progress_vacuum" to see if the numbers are changing,
that is, if there is actually any progress. That may also give you a clue as
to how long it will still take.
Yours,
Laurenz Albe
--
*E-Mail Disclaimer*
Der Inhalt dieser E-Mail ist ausschliesslich fuer den
bezeichneten Adressaten bestimmt. Wenn Sie nicht der vorgesehene Adressat
dieser E-Mail oder dessen Vertreter sein sollten, so beachten Sie bitte,
dass jede Form der Kenntnisnahme, Veroeffentlichung, Vervielfaeltigung oder
Weitergabe des Inhalts dieser E-Mail unzulaessig ist. Wir bitten Sie, sich
in diesem Fall mit dem Absender der E-Mail in Verbindung zu setzen.
*CONFIDENTIALITY NOTICE & DISCLAIMER
*This message and any attachment are
confidential and may be privileged or otherwise protected from disclosure
and solely for the use of the person(s) or entity to whom it is intended.
If you have received this message in error and are not the intended
recipient, please notify the sender immediately and delete this message and
any attachment from your system. If you are not the intended recipient, be
advised that any use of this message is prohibited and may be unlawful, and
you must not copy this message or attachment or disclose the contents to
any other person.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Table size is constantly growing and causing performance problems
2025-02-12 04:44 Table size is constantly growing and causing performance problems srinivasan s <srinioracledba7@gmail.com>
2025-02-12 07:29 ` Re: Table size is constantly growing and causing performance problems Laurenz Albe <laurenz.albe@cybertec.at>
@ 2025-02-12 07:35 ` srinivasan s <srinioracledba7@gmail.com>
0 siblings, 0 replies; 14+ messages in thread
From: srinivasan s @ 2025-02-12 07:35 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-admin@lists.postgresql.org
Thanks @Laurenz Albe <laurenz.albe@cybertec.at> , let me take a look at the
bug and view that you shared.
I didn't mention Vacuum full in my message, I mentioned vacuum analyze; it
is scheduled to run every weekend.
On Wed, Feb 12, 2025 at 1:00 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:
> On Wed, 2025-02-12 at 10:14 +0530, srinivasan s wrote:
> > One of the tables in our database suddenly started growing very fast
> without
> > any changes to the environment. it has grown over 10GB in the last week
> and
> > this is causing performance issues. We ended up adding an index to solve
> the
> > performance problem but the table growth didn't stop. It is growing
> continuously.
> > we are using postgres version 12 on ubuntu
>
> That's a mistake. Use a supported version. As it is, you could be
> suffering
> from some already fixed (data corruption?) bug.
>
> > We are running a vacuum analsye on a full database every weekend and an
> auto
> > vacuum is set up.
>
> VACUUM (FULL) is also a mistake. It should be a regular VACUUM.
>
> Funny, you say that you are experiencing bloat. How can that be if you are
> running VACUUM (FULL)? Perhaps something is blocking VACUUM from removing
> dead rows:
>
> https://www.cybertec-postgresql.com/en/reasons-why-vacuum-wont-remove-dead-rows/
>
> > My observation on the DB so far.
> >
> > [...]
> >
> > 3. Also noticed that there is a auto vacuum job running on the table with
> > (to prevent wraparound)
> >
> > I am not sure if this auto vacuum (to prevent wraparound) is progressing,
> > it is running for more than 15 hours and status is active.
>
> You should check "pg_stat_progress_vacuum" to see if the numbers are
> changing,
> that is, if there is actually any progress. That may also give you a clue
> as
> to how long it will still take.
>
> Yours,
> Laurenz Albe
>
>
> --
>
> *E-Mail Disclaimer*
> Der Inhalt dieser E-Mail ist ausschliesslich fuer den
> bezeichneten Adressaten bestimmt. Wenn Sie nicht der vorgesehene Adressat
> dieser E-Mail oder dessen Vertreter sein sollten, so beachten Sie bitte,
> dass jede Form der Kenntnisnahme, Veroeffentlichung, Vervielfaeltigung
> oder
> Weitergabe des Inhalts dieser E-Mail unzulaessig ist. Wir bitten Sie, sich
> in diesem Fall mit dem Absender der E-Mail in Verbindung zu setzen.
>
> *CONFIDENTIALITY NOTICE & DISCLAIMER
> *This message and any attachment are
> confidential and may be privileged or otherwise protected from disclosure
> and solely for the use of the person(s) or entity to whom it is intended.
> If you have received this message in error and are not the intended
> recipient, please notify the sender immediately and delete this message
> and
> any attachment from your system. If you are not the intended recipient, be
> advised that any use of this message is prohibited and may be unlawful,
> and
> you must not copy this message or attachment or disclose the contents to
> any other person.
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Table size is constantly growing and causing performance problems
2025-02-12 04:44 Table size is constantly growing and causing performance problems srinivasan s <srinioracledba7@gmail.com>
@ 2025-02-12 07:38 ` shammat@gmx.net
2025-02-12 07:43 ` Re: Table size is constantly growing and causing performance problems srinivasan s <srinioracledba7@gmail.com>
4 siblings, 1 reply; 14+ messages in thread
From: shammat@gmx.net @ 2025-02-12 07:38 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
srinivasan s schrieb am 12.02.2025 um 05:44:
> One of the tables in our database suddenly started growing very
> fast without any changes to the environment. it has grown over 10GB
> in the last week and this is causing performance issues. We ended up
> adding an index to solve the performance problem but the table
> growth didn't stop. It is growing continuously. we are using
> postgres version 12 on ubuntu
>
> 3. Also noticed that there is a auto vacuum job running on the table with (to prevent wraparound)
Do you have any connections with the state "idle in transaction"? They would prevent (auto) vacuum.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Table size is constantly growing and causing performance problems
2025-02-12 04:44 Table size is constantly growing and causing performance problems srinivasan s <srinioracledba7@gmail.com>
2025-02-12 07:38 ` Re: Table size is constantly growing and causing performance problems shammat@gmx.net
@ 2025-02-12 07:43 ` srinivasan s <srinioracledba7@gmail.com>
2025-02-12 07:53 ` Re: Table size is constantly growing and causing performance problems Mahesh Shetty <maheshetty20@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: srinivasan s @ 2025-02-12 07:43 UTC (permalink / raw)
To: shammat@gmx.net; +Cc: pgsql-admin@lists.postgresql.org
Hi Shammat,
There were some very old idle sessions waiting for clientread, which we
have killed. no idle in transaction for a long time
On Wed, Feb 12, 2025 at 1:08 PM <shammat@gmx.net> wrote:
> srinivasan s schrieb am 12.02.2025 um 05:44:
> > One of the tables in our database suddenly started growing very
> > fast without any changes to the environment. it has grown over 10GB
> > in the last week and this is causing performance issues. We ended up
> > adding an index to solve the performance problem but the table
> > growth didn't stop. It is growing continuously. we are using
> > postgres version 12 on ubuntu
> >
> > 3. Also noticed that there is a auto vacuum job running on the table
> with (to prevent wraparound)
>
> Do you have any connections with the state "idle in transaction"? They
> would prevent (auto) vacuum.
>
>
>
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Table size is constantly growing and causing performance problems
2025-02-12 04:44 Table size is constantly growing and causing performance problems srinivasan s <srinioracledba7@gmail.com>
2025-02-12 07:38 ` Re: Table size is constantly growing and causing performance problems shammat@gmx.net
2025-02-12 07:43 ` Re: Table size is constantly growing and causing performance problems srinivasan s <srinioracledba7@gmail.com>
@ 2025-02-12 07:53 ` Mahesh Shetty <maheshetty20@gmail.com>
2025-02-12 08:12 ` Re: Table size is constantly growing and causing performance problems srinivasan s <srinioracledba7@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Mahesh Shetty @ 2025-02-12 07:53 UTC (permalink / raw)
To: srinivasan s <srinioracledba7@gmail.com>; +Cc: shammat@gmx.net; pgsql-admin@lists.postgresql.org
Do you see "duplicate key violations" in the logs ?
Regards,
Mahesh Shetty
On Wed, 12 Feb 2025 at 13:13, srinivasan s <srinioracledba7@gmail.com>
wrote:
> Hi Shammat,
>
> There were some very old idle sessions waiting for clientread, which we
> have killed. no idle in transaction for a long time
>
>
> On Wed, Feb 12, 2025 at 1:08 PM <shammat@gmx.net> wrote:
>
>> srinivasan s schrieb am 12.02.2025 um 05:44:
>> > One of the tables in our database suddenly started growing very
>> > fast without any changes to the environment. it has grown over 10GB
>> > in the last week and this is causing performance issues. We ended up
>> > adding an index to solve the performance problem but the table
>> > growth didn't stop. It is growing continuously. we are using
>> > postgres version 12 on ubuntu
>> >
>> > 3. Also noticed that there is a auto vacuum job running on the table
>> with (to prevent wraparound)
>>
>> Do you have any connections with the state "idle in transaction"? They
>> would prevent (auto) vacuum.
>>
>>
>>
>>
>>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Table size is constantly growing and causing performance problems
2025-02-12 04:44 Table size is constantly growing and causing performance problems srinivasan s <srinioracledba7@gmail.com>
2025-02-12 07:38 ` Re: Table size is constantly growing and causing performance problems shammat@gmx.net
2025-02-12 07:43 ` Re: Table size is constantly growing and causing performance problems srinivasan s <srinioracledba7@gmail.com>
2025-02-12 07:53 ` Re: Table size is constantly growing and causing performance problems Mahesh Shetty <maheshetty20@gmail.com>
@ 2025-02-12 08:12 ` srinivasan s <srinioracledba7@gmail.com>
2025-02-12 08:23 ` Re: Table size is constantly growing and causing performance problems srinivasan s <srinioracledba7@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: srinivasan s @ 2025-02-12 08:12 UTC (permalink / raw)
To: Mahesh Shetty <maheshetty20@gmail.com>; +Cc: shammat@gmx.net; pgsql-admin@lists.postgresql.org
Hi Mahesh, I see unique constraint violation errors on the logs, is this
anyway related to the issue that I explained?
On Wed, Feb 12, 2025 at 1:23 PM Mahesh Shetty <maheshetty20@gmail.com>
wrote:
> Do you see "duplicate key violations" in the logs ?
>
> Regards,
> Mahesh Shetty
>
>
>
> On Wed, 12 Feb 2025 at 13:13, srinivasan s <srinioracledba7@gmail.com>
> wrote:
>
>> Hi Shammat,
>>
>> There were some very old idle sessions waiting for clientread, which we
>> have killed. no idle in transaction for a long time
>>
>>
>> On Wed, Feb 12, 2025 at 1:08 PM <shammat@gmx.net> wrote:
>>
>>> srinivasan s schrieb am 12.02.2025 um 05:44:
>>> > One of the tables in our database suddenly started growing very
>>> > fast without any changes to the environment. it has grown over 10GB
>>> > in the last week and this is causing performance issues. We ended up
>>> > adding an index to solve the performance problem but the table
>>> > growth didn't stop. It is growing continuously. we are using
>>> > postgres version 12 on ubuntu
>>> >
>>> > 3. Also noticed that there is a auto vacuum job running on the table
>>> with (to prevent wraparound)
>>>
>>> Do you have any connections with the state "idle in transaction"? They
>>> would prevent (auto) vacuum.
>>>
>>>
>>>
>>>
>>>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Table size is constantly growing and causing performance problems
2025-02-12 04:44 Table size is constantly growing and causing performance problems srinivasan s <srinioracledba7@gmail.com>
2025-02-12 07:38 ` Re: Table size is constantly growing and causing performance problems shammat@gmx.net
2025-02-12 07:43 ` Re: Table size is constantly growing and causing performance problems srinivasan s <srinioracledba7@gmail.com>
2025-02-12 07:53 ` Re: Table size is constantly growing and causing performance problems Mahesh Shetty <maheshetty20@gmail.com>
2025-02-12 08:12 ` Re: Table size is constantly growing and causing performance problems srinivasan s <srinioracledba7@gmail.com>
@ 2025-02-12 08:23 ` srinivasan s <srinioracledba7@gmail.com>
2025-02-12 15:14 ` Re: Table size is constantly growing and causing performance problems Mahesh Shetty <maheshetty20@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: srinivasan s @ 2025-02-12 08:23 UTC (permalink / raw)
To: Mahesh Shetty <maheshetty20@gmail.com>; +Cc: shammat@gmx.net; pgsql-admin@lists.postgresql.org
Searched yesterday's log, there were 2000+ unique constraints error. I
don't know if it is the contributor
On Wed, 12 Feb, 2025, 1:42 pm srinivasan s, <srinioracledba7@gmail.com>
wrote:
> Hi Mahesh, I see unique constraint violation errors on the logs, is this
> anyway related to the issue that I explained?
>
> On Wed, Feb 12, 2025 at 1:23 PM Mahesh Shetty <maheshetty20@gmail.com>
> wrote:
>
>> Do you see "duplicate key violations" in the logs ?
>>
>> Regards,
>> Mahesh Shetty
>>
>>
>>
>> On Wed, 12 Feb 2025 at 13:13, srinivasan s <srinioracledba7@gmail.com>
>> wrote:
>>
>>> Hi Shammat,
>>>
>>> There were some very old idle sessions waiting for clientread, which we
>>> have killed. no idle in transaction for a long time
>>>
>>>
>>> On Wed, Feb 12, 2025 at 1:08 PM <shammat@gmx.net> wrote:
>>>
>>>> srinivasan s schrieb am 12.02.2025 um 05:44:
>>>> > One of the tables in our database suddenly started growing very
>>>> > fast without any changes to the environment. it has grown over 10GB
>>>> > in the last week and this is causing performance issues. We ended up
>>>> > adding an index to solve the performance problem but the table
>>>> > growth didn't stop. It is growing continuously. we are using
>>>> > postgres version 12 on ubuntu
>>>> >
>>>> > 3. Also noticed that there is a auto vacuum job running on the table
>>>> with (to prevent wraparound)
>>>>
>>>> Do you have any connections with the state "idle in transaction"? They
>>>> would prevent (auto) vacuum.
>>>>
>>>>
>>>>
>>>>
>>>>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Table size is constantly growing and causing performance problems
2025-02-12 04:44 Table size is constantly growing and causing performance problems srinivasan s <srinioracledba7@gmail.com>
2025-02-12 07:38 ` Re: Table size is constantly growing and causing performance problems shammat@gmx.net
2025-02-12 07:43 ` Re: Table size is constantly growing and causing performance problems srinivasan s <srinioracledba7@gmail.com>
2025-02-12 07:53 ` Re: Table size is constantly growing and causing performance problems Mahesh Shetty <maheshetty20@gmail.com>
2025-02-12 08:12 ` Re: Table size is constantly growing and causing performance problems srinivasan s <srinioracledba7@gmail.com>
2025-02-12 08:23 ` Re: Table size is constantly growing and causing performance problems srinivasan s <srinioracledba7@gmail.com>
@ 2025-02-12 15:14 ` Mahesh Shetty <maheshetty20@gmail.com>
2025-02-12 16:42 ` Re: Table size is constantly growing and causing performance problems srinivasan s <srinioracledba7@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Mahesh Shetty @ 2025-02-12 15:14 UTC (permalink / raw)
To: srinivasan s <srinioracledba7@gmail.com>; +Cc: shammat@gmx.net; pgsql-admin@lists.postgresql.org
Yes - The tuples are inserted into the heap and checked if they are valid
as per the constraint. If it fails, it’s marked as deleted.
regards,
Mahesh Shetty
On Wed, Feb 12, 2025 at 1:53 PM srinivasan s <srinioracledba7@gmail.com>
wrote:
> Searched yesterday's log, there were 2000+ unique constraints error. I
> don't know if it is the contributor
>
> On Wed, 12 Feb, 2025, 1:42 pm srinivasan s, <srinioracledba7@gmail.com>
> wrote:
>
>> Hi Mahesh, I see unique constraint violation errors on the logs, is this
>> anyway related to the issue that I explained?
>>
>> On Wed, Feb 12, 2025 at 1:23 PM Mahesh Shetty <maheshetty20@gmail.com>
>> wrote:
>>
>>> Do you see "duplicate key violations" in the logs ?
>>>
>>> Regards,
>>> Mahesh Shetty
>>>
>>>
>>>
>>> On Wed, 12 Feb 2025 at 13:13, srinivasan s <srinioracledba7@gmail.com>
>>> wrote:
>>>
>>>> Hi Shammat,
>>>>
>>>> There were some very old idle sessions waiting for clientread, which we
>>>> have killed. no idle in transaction for a long time
>>>>
>>>>
>>>> On Wed, Feb 12, 2025 at 1:08 PM <shammat@gmx.net> wrote:
>>>>
>>>>> srinivasan s schrieb am 12.02.2025 um 05:44:
>>>>> > One of the tables in our database suddenly started growing very
>>>>> > fast without any changes to the environment. it has grown over 10GB
>>>>> > in the last week and this is causing performance issues. We ended up
>>>>> > adding an index to solve the performance problem but the table
>>>>> > growth didn't stop. It is growing continuously. we are using
>>>>> > postgres version 12 on ubuntu
>>>>> >
>>>>> > 3. Also noticed that there is a auto vacuum job running on the table
>>>>> with (to prevent wraparound)
>>>>>
>>>>> Do you have any connections with the state "idle in transaction"? They
>>>>> would prevent (auto) vacuum.
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Table size is constantly growing and causing performance problems
2025-02-12 04:44 Table size is constantly growing and causing performance problems srinivasan s <srinioracledba7@gmail.com>
2025-02-12 07:38 ` Re: Table size is constantly growing and causing performance problems shammat@gmx.net
2025-02-12 07:43 ` Re: Table size is constantly growing and causing performance problems srinivasan s <srinioracledba7@gmail.com>
2025-02-12 07:53 ` Re: Table size is constantly growing and causing performance problems Mahesh Shetty <maheshetty20@gmail.com>
2025-02-12 08:12 ` Re: Table size is constantly growing and causing performance problems srinivasan s <srinioracledba7@gmail.com>
2025-02-12 08:23 ` Re: Table size is constantly growing and causing performance problems srinivasan s <srinioracledba7@gmail.com>
2025-02-12 15:14 ` Re: Table size is constantly growing and causing performance problems Mahesh Shetty <maheshetty20@gmail.com>
@ 2025-02-12 16:42 ` srinivasan s <srinioracledba7@gmail.com>
2025-02-12 20:40 ` Re: Table size is constantly growing and causing performance problems Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 14+ messages in thread
From: srinivasan s @ 2025-02-12 16:42 UTC (permalink / raw)
To: Mahesh Shetty <maheshetty20@gmail.com>; +Cc: shammat@gmx.net; pgsql-admin@lists.postgresql.org
What is the functional difference between regular autovaccum and autovaccum
(to prevent wrap around)
On Wed, 12 Feb, 2025, 8:45 pm Mahesh Shetty, <maheshetty20@gmail.com> wrote:
> Yes - The tuples are inserted into the heap and checked if they are valid
> as per the constraint. If it fails, it’s marked as deleted.
>
> regards,
> Mahesh Shetty
>
> On Wed, Feb 12, 2025 at 1:53 PM srinivasan s <srinioracledba7@gmail.com>
> wrote:
>
>> Searched yesterday's log, there were 2000+ unique constraints error. I
>> don't know if it is the contributor
>>
>> On Wed, 12 Feb, 2025, 1:42 pm srinivasan s, <srinioracledba7@gmail.com>
>> wrote:
>>
>>> Hi Mahesh, I see unique constraint violation errors on the logs, is this
>>> anyway related to the issue that I explained?
>>>
>>> On Wed, Feb 12, 2025 at 1:23 PM Mahesh Shetty <maheshetty20@gmail.com>
>>> wrote:
>>>
>>>> Do you see "duplicate key violations" in the logs ?
>>>>
>>>> Regards,
>>>> Mahesh Shetty
>>>>
>>>>
>>>>
>>>> On Wed, 12 Feb 2025 at 13:13, srinivasan s <srinioracledba7@gmail.com>
>>>> wrote:
>>>>
>>>>> Hi Shammat,
>>>>>
>>>>> There were some very old idle sessions waiting for clientread, which
>>>>> we have killed. no idle in transaction for a long time
>>>>>
>>>>>
>>>>> On Wed, Feb 12, 2025 at 1:08 PM <shammat@gmx.net> wrote:
>>>>>
>>>>>> srinivasan s schrieb am 12.02.2025 um 05:44:
>>>>>> > One of the tables in our database suddenly started growing very
>>>>>> > fast without any changes to the environment. it has grown over 10GB
>>>>>> > in the last week and this is causing performance issues. We ended up
>>>>>> > adding an index to solve the performance problem but the table
>>>>>> > growth didn't stop. It is growing continuously. we are using
>>>>>> > postgres version 12 on ubuntu
>>>>>> >
>>>>>> > 3. Also noticed that there is a auto vacuum job running on the
>>>>>> table with (to prevent wraparound)
>>>>>>
>>>>>> Do you have any connections with the state "idle in transaction"?
>>>>>> They would prevent (auto) vacuum.
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Table size is constantly growing and causing performance problems
2025-02-12 04:44 Table size is constantly growing and causing performance problems srinivasan s <srinioracledba7@gmail.com>
2025-02-12 07:38 ` Re: Table size is constantly growing and causing performance problems shammat@gmx.net
2025-02-12 07:43 ` Re: Table size is constantly growing and causing performance problems srinivasan s <srinioracledba7@gmail.com>
2025-02-12 07:53 ` Re: Table size is constantly growing and causing performance problems Mahesh Shetty <maheshetty20@gmail.com>
2025-02-12 08:12 ` Re: Table size is constantly growing and causing performance problems srinivasan s <srinioracledba7@gmail.com>
2025-02-12 08:23 ` Re: Table size is constantly growing and causing performance problems srinivasan s <srinioracledba7@gmail.com>
2025-02-12 15:14 ` Re: Table size is constantly growing and causing performance problems Mahesh Shetty <maheshetty20@gmail.com>
2025-02-12 16:42 ` Re: Table size is constantly growing and causing performance problems srinivasan s <srinioracledba7@gmail.com>
@ 2025-02-12 20:40 ` Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 14+ messages in thread
From: Laurenz Albe @ 2025-02-12 20:40 UTC (permalink / raw)
To: srinivasan s <srinioracledba7@gmail.com>; Mahesh Shetty <maheshetty20@gmail.com>; +Cc: shammat@gmx.net; pgsql-admin@lists.postgresql.org
On Wed, 2025-02-12 at 22:12 +0530, srinivasan s wrote:
> What is the functional difference between regular autovaccum and autovaccum (to prevent wrap around)
The latter will want to visit all pages that are not "all frozen", and it
won't silently die if its lock blocks a user process.
Yours,
Laurenz Albe
--
*E-Mail Disclaimer*
Der Inhalt dieser E-Mail ist ausschliesslich fuer den
bezeichneten Adressaten bestimmt. Wenn Sie nicht der vorgesehene Adressat
dieser E-Mail oder dessen Vertreter sein sollten, so beachten Sie bitte,
dass jede Form der Kenntnisnahme, Veroeffentlichung, Vervielfaeltigung oder
Weitergabe des Inhalts dieser E-Mail unzulaessig ist. Wir bitten Sie, sich
in diesem Fall mit dem Absender der E-Mail in Verbindung zu setzen.
*CONFIDENTIALITY NOTICE & DISCLAIMER
*This message and any attachment are
confidential and may be privileged or otherwise protected from disclosure
and solely for the use of the person(s) or entity to whom it is intended.
If you have received this message in error and are not the intended
recipient, please notify the sender immediately and delete this message and
any attachment from your system. If you are not the intended recipient, be
advised that any use of this message is prohibited and may be unlawful, and
you must not copy this message or attachment or disclose the contents to
any other person.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Table size is constantly growing and causing performance problems
2025-02-12 04:44 Table size is constantly growing and causing performance problems srinivasan s <srinioracledba7@gmail.com>
@ 2025-02-13 00:01 ` Jeff Janes <jeff.janes@gmail.com>
4 siblings, 0 replies; 14+ messages in thread
From: Jeff Janes @ 2025-02-13 00:01 UTC (permalink / raw)
To: srinivasan s <srinioracledba7@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
On Tue, Feb 11, 2025 at 11:45 PM srinivasan s <srinioracledba7@gmail.com>
wrote:
> Hi Team,
>
> One of the tables in our database suddenly started growing very fast
> without any changes to the environment. it has grown over 10GB in the last
> week and this is causing performance issues. We ended up adding an index to
> solve the performance problem but the table growth didn't stop. It is
> growing continuously. we are using postgres version 12 on ubuntu
>
> We are running a vacuum analsye on a full database every weekend and an
> auto vacuum is set up.
>
> My observation on the DB so far.
> 1. The table is bloated around 50GB is bloat out of 95GB table size
>
> * current_database | schemaname | tablename
> | tbloat | wastedbytes*
>
> *---------------------+------------+-----------------------------------------------------------------+--------+-------------*
> *xyxyxyxy_production | public | xxxxxx
> | 2.5 | 51894779904*
>
What is the query which produced that? That looks like a user-space query,
which exists in multiple different iterations.
You can use the pg_freespacemap extension to see if PostgreSQL knows how to
find the free space in order to reuse it.
> I am not sure if this auto vacuum (to prevent wraparound) is progressing,
> it is running for more than 15 hours and status is active.
>
You can use pg_stat_progress_vacuum to monitor the progress.
> Will this block the regular auto vacuum?
>
Yes, two vacuums won't run in the same table at the same time. But if the
bloat has been going on for a week, it is hard to see how it could be due
to an only 15 hour vacuum to prevent wraparound. It could be that this
gets interrupted repeatedly without ever finishing, but that seems
inconsistent with the last_autovacuum timestamp.
My best guess is that you have a long-held snapshot which is preventing
vacuum from being effective at its job.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2025-02-13 00:01 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-02-12 04:44 Table size is constantly growing and causing performance problems srinivasan s <srinioracledba7@gmail.com>
2025-02-12 06:39 ` Thiemo Kellner <thiemo@gelassene-pferde.biz>
2025-02-12 06:56 ` srinivasan s <srinioracledba7@gmail.com>
2025-02-12 07:29 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-02-12 07:35 ` srinivasan s <srinioracledba7@gmail.com>
2025-02-12 07:38 ` shammat@gmx.net
2025-02-12 07:43 ` srinivasan s <srinioracledba7@gmail.com>
2025-02-12 07:53 ` Mahesh Shetty <maheshetty20@gmail.com>
2025-02-12 08:12 ` srinivasan s <srinioracledba7@gmail.com>
2025-02-12 08:23 ` srinivasan s <srinioracledba7@gmail.com>
2025-02-12 15:14 ` Mahesh Shetty <maheshetty20@gmail.com>
2025-02-12 16:42 ` srinivasan s <srinioracledba7@gmail.com>
2025-02-12 20:40 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-02-13 00:01 ` Jeff Janes <jeff.janes@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox