Huge tables, trying to delete OID's taking 6+hours per table

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Huge tables, trying to delete OID's taking 6+hours per table

Tory M Blue

The db is only 2TB, these tables are about 50% of the DB.


Just wondering what I can boost to give me some elevated temp performance for what I would think would be a fairly quick operation.

Give this query more work_mem? Mine are set pretty low, based on previous performance notes, this is a 9.5 server.

Thanks
Tory
Reply | Threaded
Open this post in threaded view
|

Re: Huge tables, trying to delete OID's taking 6+hours per table

Tory M Blue


On Tue, May 19, 2020 at 12:17 AM Tory M Blue <[hidden email]> wrote:

The db is only 2TB, these tables are about 50% of the DB.


Just wondering what I can boost to give me some elevated temp performance for what I would think would be a fairly quick operation.

Give this query more work_mem? Mine are set pretty low, based on previous performance notes, this is a 9.5 server.

Thanks
Tory

The command i'm using is

ALTER TABLE tablename SET WITHOUT OIDS; 

Would a drop column oid be better?

Tory
Reply | Threaded
Open this post in threaded view
|

Re: Huge tables, trying to delete OID's taking 6+hours per table

Tom Lane-2
Tory M Blue <[hidden email]> writes:
> The command i'm using is
> ALTER TABLE tablename SET WITHOUT OIDS;
> Would a drop column oid be better?

Unfortunately, you're kind of stuck.  OIDs are not like regular columns
(at least before v12) --- they are integrated into the tuple header in
a hackish way, and so there's no way to get rid of them without a table
rewrite.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: Huge tables, trying to delete OID's taking 6+hours per table

Tory M Blue


On Tue, May 19, 2020 at 6:40 AM Tom Lane <[hidden email]> wrote:
Tory M Blue <[hidden email]> writes:
> The command i'm using is
> ALTER TABLE tablename SET WITHOUT OIDS;
> Would a drop column oid be better?

Unfortunately, you're kind of stuck.  OIDs are not like regular columns
(at least before v12) --- they are integrated into the tuple header in
a hackish way, and so there's no way to get rid of them without a table
rewrite.

                        regards, tom lane

Poop :) kind of figured that, so it's just painful.

But  I guess if it's doing a table rewrite, is there any configuration params I could boost to help it? Shared_buffers, give it more, work mem, maintenance mem, temp buffers anything you can think of?

Thanks again Tom

Tory 
Reply | Threaded
Open this post in threaded view
|

Re: Huge tables, trying to delete OID's taking 6+hours per table

Ron-2
On 5/19/20 11:51 AM, Tory M Blue wrote:


On Tue, May 19, 2020 at 6:40 AM Tom Lane <[hidden email]> wrote:
Tory M Blue <[hidden email]> writes:
> The command i'm using is
> ALTER TABLE tablename SET WITHOUT OIDS;
> Would a drop column oid be better?

Unfortunately, you're kind of stuck.  OIDs are not like regular columns
(at least before v12) --- they are integrated into the tuple header in
a hackish way, and so there's no way to get rid of them without a table
rewrite.

                        regards, tom lane

Poop :) kind of figured that, so it's just painful.

But  I guess if it's doing a table rewrite, is there any configuration params I could boost to help it? Shared_buffers, give it more, work mem, maintenance mem, temp buffers anything you can think of?

There's an alternative if this is a "transaction table" (named, in this example, FOO) which never gets updated (only inserted into and selected from).

Create a new, partitioned, oid-free copy of the table (named NEW_FOO) that's populated with most of the records (all except the most recent).  When ready to cut over, you'd stop the applications, copy over the most current records from FOO to NEW_FOO and then rename FOO to OLD_FOO and FOO to OLD_FOO.

Then you can drop OLD_FOO.

--
Angular momentum makes the world go 'round.
Reply | Threaded
Open this post in threaded view
|

Re: Huge tables, trying to delete OID's taking 6+hours per table

Tory M Blue


On Tue, May 19, 2020 at 10:06 AM Ron <[hidden email]> wrote:
On 5/19/20 11:51 AM, Tory M Blue wrote:


On Tue, May 19, 2020 at 6:40 AM Tom Lane <[hidden email]> wrote:
Tory M Blue <[hidden email]> writes:
> The command i'm using is
> ALTER TABLE tablename SET WITHOUT OIDS;
> Would a drop column oid be better?

Unfortunately, you're kind of stuck.  OIDs are not like regular columns
(at least before v12) --- they are integrated into the tuple header in
a hackish way, and so there's no way to get rid of them without a table
rewrite.

                        regards, tom lane

Poop :) kind of figured that, so it's just painful.

But  I guess if it's doing a table rewrite, is there any configuration params I could boost to help it? Shared_buffers, give it more, work mem, maintenance mem, temp buffers anything you can think of?

There's an alternative if this is a "transaction table" (named, in this example, FOO) which never gets updated (only inserted into and selected from).

Create a new, partitioned, oid-free copy of the table (named NEW_FOO) that's populated with most of the records (all except the most recent).  When ready to cut over, you'd stop the applications, copy over the most current records from FOO to NEW_FOO and then rename FOO to OLD_FOO and FOO to OLD_FOO.

Then you can drop OLD_FOO.

--
Angular momentum makes the world go 'round.

Thanks Ron,

Looked into this but we have large indexes that take 8-12 hours to create. So my gut says this would not buy us anytime. Also this has been running for 16 hours now and still not done. I think it's forcing index creation regardless. Really a crappy situation!!!

Tory 
Reply | Threaded
Open this post in threaded view
|

Re: Huge tables, trying to delete OID's taking 6+hours per table

Ron-2
On 5/20/20 11:22 AM, Tory M Blue wrote:


On Tue, May 19, 2020 at 10:06 AM Ron <[hidden email]> wrote:
On 5/19/20 11:51 AM, Tory M Blue wrote:


On Tue, May 19, 2020 at 6:40 AM Tom Lane <[hidden email]> wrote:
Tory M Blue <[hidden email]> writes:
> The command i'm using is
> ALTER TABLE tablename SET WITHOUT OIDS;
> Would a drop column oid be better?

Unfortunately, you're kind of stuck.  OIDs are not like regular columns
(at least before v12) --- they are integrated into the tuple header in
a hackish way, and so there's no way to get rid of them without a table
rewrite.

                        regards, tom lane

Poop :) kind of figured that, so it's just painful.

But  I guess if it's doing a table rewrite, is there any configuration params I could boost to help it? Shared_buffers, give it more, work mem, maintenance mem, temp buffers anything you can think of?

There's an alternative if this is a "transaction table" (named, in this example, FOO) which never gets updated (only inserted into and selected from).

Create a new, partitioned, oid-free copy of the table (named NEW_FOO) that's populated with most of the records (all except the most recent).  When ready to cut over, you'd stop the applications, copy over the most current records from FOO to NEW_FOO and then rename FOO to OLD_FOO and FOO to OLD_FOO.

Then you can drop OLD_FOO.

--
Angular momentum makes the world go 'round.

Thanks Ron,

Looked into this but we have large indexes that take 8-12 hours to create. So my gut says this would not buy us anytime.

Since the index creation would be separate from your application's access, it doesn't actually matter.

Also this has been running for 16 hours now and still not done. I think it's forcing index creation regardless.


If it's rewriting the table's pages, then the indexes must be rewritten, too,

When removing OIDs, is the priority to minimize absolute time, or to minimize downtime/degradation as sen by the application?

--
Angular momentum makes the world go 'round.