Re: Recommendation to run vacuum FULL in parallel

Look also at pg_stat_all_tables.n_dead_tup for tables which are candidates for vacuuming. On 4/10/19 11:49 PM, Perumal Raj wrote: Thanks Kevin for the inputs, In my Case there are 500+ Tables and biggest chunk 30GB ( Table only) + its indexes. So i have created 6 batches and executed in

When do vacuumed pages/tuples become available for reuse?

If an autovacuum job on a huge table runs for 5-6 hours, do its freed pages/tuples become available for reuse immediately when they are marked as free, or only at the end of the multi-hour vacuum operation?

Re: Fwd: Postgresql with nextcloud in Windows Server

1. It spends more resources but I think this setting endure that problem. Actually, after this setting, Windows server(include PostgreSQL server) endures that problem more. But I know it's not a solution. 2. No. MS-SQL SERVER, PostgreSQL Server, and Tomcat. What do you think of appropriate value

Re: shared_buffers on Big RAM systems

I tested. The shared buffers works better, then an OS level filesystem cache. The more shared_buffers (but less then database size), the better. With huge_pages is more better. But you must reserve enough free memory for OS and PostgeSQL itself. > 13 дек. 2018 г., в 18:17, Ron написал(а): >

Customizing the PSQL prompt with environment variables using value-dependant coloring

Hello everyone, I'm trying to create a custom PSQL prompt with a part of it colored in function of its value. To be more explicit, we are using a physical replication and I'd like to display in the prompt "MASTER" in red or "slave" in green, when we are connected on the database server. #

Re: When do vacuumed pages/tuples become available for reuse?

On 04/11/2019 07:40 PM, Jeff Janes wrote: On Thu, Apr 11, 2019 at 10:28 AM rihad > wrote: Yup, it's just that n_dead_tuples grows by several hundred thousand (the table sees much much more updates than inserts) and disk usage grows constantly between

Re: When do vacuumed pages/tuples become available for reuse?

On 04/11/2019 07:04 PM, Alvaro Herrera wrote: On 2019-Apr-11, rihad wrote: On 04/11/2019 06:41 PM, Alvaro Herrera wrote: Perhaps it'd be better to vacuum this table much more often. Each run took 5-6 hours, now it takes 2-3 hours after I've tweaked some cost-based vacuum knobs. But how

Re: When do vacuumed pages/tuples become available for reuse?

On Thu, Apr 11, 2019 at 11:44 AM rihad wrote: > On 04/11/2019 07:40 PM, Jeff Janes wrote: > > > The disk usage doesn't reach a steady state after one or two autovacs? Or > it does, but you are just unhappy about the ratio between the steady state > size and the theoretical fully packed size? >

Re: When do vacuumed pages/tuples become available for reuse?

On 04/11/2019 08:09 PM, Jeff Janes wrote: On Thu, Apr 11, 2019 at 11:44 AM rihad > wrote: On 04/11/2019 07:40 PM, Jeff Janes wrote: The disk usage doesn't reach a steady state after one or two autovacs?  Or it does, but you are just unhappy about the ratio

Re: When do vacuumed pages/tuples become available for reuse?

On 04/11/2019 05:48 PM, Tom Lane wrote: rihad writes: If an autovacuum job on a huge table runs for 5-6 hours, do its freed pages/tuples become available for reuse immediately when they are marked as free, or only at the end of the multi-hour vacuum operation? They'll be freed in batches,

Re: When do vacuumed pages/tuples become available for reuse?

On 04/11/2019 06:09 PM, Alvaro Herrera wrote: On 2019-Apr-11, rihad wrote: Thanks! Our autovacuum_work_mem = 1GB, so this probably means any space would be available for reuse only at the end of the vacuum? Are there any downsides in decreasing it to, say, 64MB? I see only pluses ) Yes, each

Re: When do vacuumed pages/tuples become available for reuse?

On 2019-Apr-11, rihad wrote: > On 04/11/2019 06:20 PM, Tom Lane wrote: > > rihad writes: > > > Thanks! Our autovacuum_work_mem = 1GB, so this probably means any space > > > would be available for reuse only at the end of the vacuum? > > It's six bytes per dead tuple, last I checked ... you do

Re: When do vacuumed pages/tuples become available for reuse?

rihad writes: > If an autovacuum job on a huge table runs for 5-6 hours, do its freed > pages/tuples become available for reuse immediately when they are marked > as free, or only at the end of the multi-hour vacuum operation? They'll be freed in batches, where the size of a batch depends on

Re: When do vacuumed pages/tuples become available for reuse?

On 04/11/2019 06:20 PM, Tom Lane wrote: rihad writes: Thanks! Our autovacuum_work_mem = 1GB, so this probably means any space would be available for reuse only at the end of the vacuum? It's six bytes per dead tuple, last I checked ... you do the math. Are there any downsides in decreasing

Re: When do vacuumed pages/tuples become available for reuse?

On 04/11/2019 06:41 PM, Alvaro Herrera wrote: On 2019-Apr-11, rihad wrote: On 04/11/2019 06:20 PM, Tom Lane wrote: rihad writes: Thanks! Our autovacuum_work_mem = 1GB, so this probably means any space would be available for reuse only at the end of the vacuum? It's six bytes per dead

Re: When do vacuumed pages/tuples become available for reuse?

On Thu, Apr 11, 2019 at 10:28 AM rihad wrote: > > Yup, it's just that n_dead_tuples grows by several hundred thousand (the > table sees much much more updates than inserts) and disk usage grows > constantly between several hour long vacuum runs. Running vacuum full > isn't an option. > The disk

Re: When do vacuumed pages/tuples become available for reuse?

On 2019-Apr-11, rihad wrote: > Thanks! Our autovacuum_work_mem = 1GB, so this probably means any space > would be available for reuse only at the end of the vacuum? Are there any > downsides in decreasing it to, say, 64MB? I see only pluses ) Yes, each vacuum will take longer and will use much

Re: When do vacuumed pages/tuples become available for reuse?

On 2019-Apr-11, rihad wrote: > On 04/11/2019 06:41 PM, Alvaro Herrera wrote: > > > Perhaps it'd be better to vacuum this table much more often. > > > Each run took 5-6 hours, now it takes 2-3 hours after I've tweaked some > cost-based vacuum knobs. But how often does it run? -- Álvaro Herrera

Re: When do vacuumed pages/tuples become available for reuse?

On Thu, Apr 11, 2019 at 11:14 AM rihad wrote: > autovacuum_vacuum_scale_factor = 0.01 > autovacuum_vacuum_threshold = 50 This seems counterproductive. You need to make the vacuum more efficient, not more frantic. > autovacuum_vacuum_cost_delay = 10ms > autovacuum_vacuum_cost_limit = 400 >

Re: Trigger when user logs in

Ron writes: > On 4/11/19 9:12 PM, Tom Lane wrote: >> PAM is the usual suggestion > Can you be more specific? I'm suggesting that you use PAM auth https://www.postgresql.org/docs/current/auth-pam.html and then configure the email behavior on the PAM side. The PAM doc link we provided there

Re: Trigger when user logs in

On 4/11/19 9:52 PM, Tom Lane wrote: Ron writes: On 4/11/19 9:12 PM, Tom Lane wrote: PAM is the usual suggestion Can you be more specific? I'm suggesting that you use PAM auth https://www.postgresql.org/docs/current/auth-pam.html and then configure the email behavior on the PAM side.

Re: When do vacuumed pages/tuples become available for reuse?

On Thu, Apr 11, 2019 at 11:13:17AM -0600, Michael Lewis wrote: > Wouldn't "dead but not yet removable" be high if there were long running > transactions holding onto old row versions? You got it right. You need to look at the number behind the tuples dead, but not removable which is usually a

Trigger when user logs in

Hi, PCI auditors have mandated that our databases (all running v9.6) send an email when certain users log in.  Thus, I've been searching for how to do this, but without much luck. https://www.postgresql.org/message-id/flat/20170720204733.40f2b7eb.nagata%40sraoss.co.jp This long thread from

Re: Trigger when user logs in

Ron writes: > PCI auditors have mandated that our databases (all running v9.6) send an > email when certain users log in.  Thus, I've been searching for how to do > this, but without much luck. PAM is the usual suggestion when you need off-the-beaten-path login behavior.

Re: Trigger when user logs in

On 4/11/19 9:12 PM, Tom Lane wrote: Ron writes: PCI auditors have mandated that our databases (all running v9.6) send an email when certain users log in.  Thus, I've been searching for how to do this, but without much luck. PAM is the usual suggestion Can you be more specific?  (All users

Re: When do vacuumed pages/tuples become available for reuse?

rihad writes: > Thanks! Our autovacuum_work_mem = 1GB, so this probably means any space > would be available for reuse only at the end of the vacuum? It's six bytes per dead tuple, last I checked ... you do the math. > Are there > any downsides in decreasing it to, say, 64MB? I see only

Re: When do vacuumed pages/tuples become available for reuse?

> > Way to many indexes. I'm going to have a hard time convincing our > programmers to get rid of any of them ) > You can create (concurrently) an identical index with a new name, then drop old version concurrently and repeat for each. It doesn't help you figure out the root cause and how to

Re: When do vacuumed pages/tuples become available for reuse?

On Thu, Apr 11, 2019 at 10:39:12PM -0600, Michael Lewis wrote: > You can create (concurrently) an identical index with a new name, then drop > old version concurrently and repeat for each. It doesn't help you figure > out the root cause and how to prevent it from happening again, but gets you > to

Re: When do vacuumed pages/tuples become available for reuse?

> > > 2019-04-11 19:39:44.450844500 tuples: 19150 removed, 2725811 remain, > 465 are dead but not yet removable > > What Jeff said. This vacuum spent a lot of time, only to remove miserly > 19k tuples, but 2.7M dead tuples remained ... probably because you have > long-running transactions

Re: When do vacuumed pages/tuples become available for reuse?

On 2019-Apr-11, Tom Lane wrote: > Alvaro Herrera writes: > > On 2019-Apr-11, rihad wrote: > >> 2019-04-11 19:39:44.450844500   tuples: 19150 removed, 2725811 remain, 465 > >> are dead but not yet removable > > > What Jeff said. This vacuum spent a lot of time, only to remove miserly > > 19k

Re: When do vacuumed pages/tuples become available for reuse?

On 04/11/2019 10:13 PM, Jeff Janes wrote: On Thu, Apr 11, 2019 at 12:18 PM rihad > wrote: On 04/11/2019 08:09 PM, Jeff Janes wrote: On Thu, Apr 11, 2019 at 11:44 AM rihad mailto:ri...@mail.ru>> wrote: Since we dump production DB daily into staging

Re: When do vacuumed pages/tuples become available for reuse?

On 2019-Apr-11, rihad wrote: > 2019-04-11 19:39:44.450844500   tuples: 19150 removed, 2725811 remain, 465 > are dead but not yet removable What Jeff said. This vacuum spent a lot of time, only to remove miserly 19k tuples, but 2.7M dead tuples remained ... probably because you have

Re: When do vacuumed pages/tuples become available for reuse?

On 4/11/19 12:24 PM, Tom Lane wrote: Alvaro Herrera writes: On 2019-Apr-11, rihad wrote: 2019-04-11 19:39:44.450844500   tuples: 19150 removed, 2725811 remain, 465 are dead but not yet removable What Jeff said. This vacuum spent a lot of time, only to remove miserly 19k tuples, but 2.7M

Re: When do vacuumed pages/tuples become available for reuse?

On Thu, Apr 11, 2019 at 12:18 PM rihad wrote: > On 04/11/2019 08:09 PM, Jeff Janes wrote: > > On Thu, Apr 11, 2019 at 11:44 AM rihad wrote: > >> >> Since we dump production DB daily into staging environment, the >> difference in size (as reported by psql's \l+) is 11GB in a freshly >> restored

Re: When do vacuumed pages/tuples become available for reuse?

On Thu, Apr 11, 2019 at 1:48 PM Jeff Janes wrote: > If we just want to do a slight re-wording, I don't know what it would need > to look like. "remain" includes live, recently dead, and uncommitted new, > and uncommitted old (I think) so we can't just change "recent" to "live". > Of course I

Re: When do vacuumed pages/tuples become available for reuse?

> > vacuum frees tuples just fine. It's just that by the time each run > finishes many more accumulate due to table update activity, ad nauseum. So > this unused space constantly grows. Here's a sample autovacuum run: > > 2019-04-11 19:39:44.450841500 [] LOG: automatic vacuum of table >

Re: When do vacuumed pages/tuples become available for reuse?

Alvaro Herrera writes: > On 2019-Apr-11, rihad wrote: >> 2019-04-11 19:39:44.450844500   tuples: 19150 removed, 2725811 remain, 465 >> are dead but not yet removable > What Jeff said. This vacuum spent a lot of time, only to remove miserly > 19k tuples, but 2.7M dead tuples remained ...

Re: shared_buffers on Big RAM systems

On Thu, Dec 13, 2018 at 11:51 PM Thomas Munro wrote: > On Fri, Dec 14, 2018 at 2:17 AM Ron wrote: > > https://www.postgresql.org/docs/9.6/runtime-config-resource.html > > > > The docs say, "If you have a dedicated database server with 1GB or more > of > > RAM, a reasonable starting value for

Re: Notification for Minor Release and Security Update

On Thu, Apr 11, 2019 at 10:17 PM Kumar, Virendra wrote: > Team, > > > > Is there a subscription URL we have to subscribe for to know about Minor > Version Release and Security Updates when they are released for public > usage. > > > There is a feed of new minor releases available at

Re: Notification for Minor Release and Security Update

Kumar, Virendra schrieb am 11.04.2019 um 22:16: Is there a subscription URL we have to subscribe for to know about Minor Version Release and Security Updates when they are released for public usage. They are sent to the "announce" mailing list, if I'm not mistaken:

Re: shared_buffers on Big RAM systems

Hi, On 2019-04-11 15:39:15 -0400, Jeff Janes wrote: > But I don't think I would recommend starting at 25% of RAM larger server. > Is that really good advice? I would usually start out at 1GB even if the > server has 128GB, and increase it only if there was evidence it needed to > be increased.

Notification for Minor Release and Security Update

Team, Is there a subscription URL we have to subscribe for to know about Minor Version Release and Security Updates when they are released for public usage. Regards, Virendra This message is intended only for the use of the addressee and may contain