Thread: Delete huge Table under XFS

Delete huge Table under XFS

From
Joao Junior
Date:
Hi, 
I am running Postgresql 9.6  XFS as filesystem , kernel Linux 2.6.32.

I have a table that Is not being use anymore, I want to drop it.
The table is huge, around 800GB and it has some index on it.

When I execute the drop table command it goes very slow, I realised that the problem is the filesystem.
It seems that XFS doesn't handle well big files, there are some discussion about it in some lists.

I have to find a way do delete the table in chunks.

My first attempt was:

Iterate from the tail of the table until the beginning.
Delete some blocks of the table.
Run vacuum on it
iterate again....

The plan is delete some amount of blocks at the end of the table, in chunks of some size and vacuum it  waiting  for vacuum shrink the table.
it seems work, the table has  been shrink but each vacuum takes a huge amount of time, I suppose it is because of the index. there is another point, the index still huge and will be.

I am thinking of another way of doing this.
I can get  the relfilenode of the table, in this way I can get the files that belongs to the table and simply delete batches of files in a way that don't put so much load on disk.
Do the same for the index.
Once I delete all table's files and index's files, I could simply execute the command drop table and the entries from the catalog would deleted.

I would appreciate any kind of comments.
thanks!




Re: Delete huge Table under XFS

From
Andreas Kretschmer
Date:

Am 19.09.19 um 17:59 schrieb Joao Junior:
>
>
> I have a table that Is not being use anymore, I want to drop it.
> The table is huge, around 800GB and it has some index on it.
>
> When I execute the drop table command it goes very slow, I realised 
> that the problem is the filesystem.
> It seems that XFS doesn't handle well big files, there are some 
> discussion about it in some lists.

PG doesn't create one big file for this table, but about 800 files with 
1GB size each.

>
> I have to find a way do delete the table in chunks.

Why? If you want to delete all rows, just use TRUNCATE.


Regards, Andreas

-- 
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com




Re: Delete huge Table under XFS

From
Joao Junior
Date:
A table with 800 gb means 800 files of 1 gb. When I use truncate or drop table,  xfs that is a log based filesystem,  will write lots of data in its log and this is the problem. The problem is not postgres, it is the way that xfs works with big files , or being more clear, the way that it handles lots of files.

Regards,
Joao 

On Thu, Sep 19, 2019, 18:50 Andreas Kretschmer <andreas@a-kretschmer.de> wrote:


Am 19.09.19 um 17:59 schrieb Joao Junior:
>
>
> I have a table that Is not being use anymore, I want to drop it.
> The table is huge, around 800GB and it has some index on it.
>
> When I execute the drop table command it goes very slow, I realised
> that the problem is the filesystem.
> It seems that XFS doesn't handle well big files, there are some
> discussion about it in some lists.

PG doesn't create one big file for this table, but about 800 files with
1GB size each.

>
> I have to find a way do delete the table in chunks.

Why? If you want to delete all rows, just use TRUNCATE.


Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com



Re: Delete huge Table under XFS

From
Christoph Berg
Date:
Re: Joao Junior 2019-09-19 <CABnPa_hdHsdypn7HtXU81B9HcrVcimotnwfzE-MWwO1etWYJzA@mail.gmail.com>
> A table with 800 gb means 800 files of 1 gb. When I use truncate or drop
> table,  xfs that is a log based filesystem,  will write lots of data in its
> log and this is the problem. The problem is not postgres, it is the way
> that xfs works with big files , or being more clear, the way that it
> handles lots of files.

Why is the runtime of a DROP TABLE command important? Is anything
waiting for it?

Christoph



Re: Delete huge Table under XFS

From
Tomas Vondra
Date:
On Thu, Sep 19, 2019 at 07:00:01PM +0200, Joao Junior wrote:
>A table with 800 gb means 800 files of 1 gb. When I use truncate or drop
>table,  xfs that is a log based filesystem,  will write lots of data in its
>log and this is the problem. The problem is not postgres, it is the way
>that xfs works with big files , or being more clear, the way that it
>handles lots of files.
>

I'm a bit skeptical about this explanation. Yes, XFS has journalling,
but only for metadata - and I have a hard time believing deleting 800
files (or a small multiple of that) would write "lots of data" into the
jornal, and noticeable performance issues. I wonder how you concluded
this is actually the problem.

That being said, TRUNCATE is unlikely to perform better than DROP,
because it also deletes all the files at once. What you might try is
dropping the indexes one by one, and then the table. That should delete
files in smaller chunks.


regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services