pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedDelete huge Table under XFS
5+ messages / 4 participants
[nested] [flat]
* Delete huge Table under XFS
@ 2019-09-19 15:59 Joao Junior <jcoj2006@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Joao Junior @ 2019-09-19 15:59 UTC (permalink / raw)
To: pgsql-performance
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!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Delete huge Table under XFS
@ 2019-09-19 16:50 Andreas Kretschmer <andreas@a-kretschmer.de>
parent: Joao Junior <jcoj2006@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Andreas Kretschmer @ 2019-09-19 16:50 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Delete huge Table under XFS
@ 2019-09-19 17:00 Joao Junior <jcoj2006@gmail.com>
parent: Andreas Kretschmer <andreas@a-kretschmer.de>
0 siblings, 2 replies; 5+ messages in thread
From: Joao Junior @ 2019-09-19 17:00 UTC (permalink / raw)
To: pgsql-performance
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
>
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Delete huge Table under XFS
@ 2019-09-19 17:27 Christoph Berg <myon@debian.org>
parent: Joao Junior <jcoj2006@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Christoph Berg @ 2019-09-19 17:27 UTC (permalink / raw)
To: Joao Junior <jcoj2006@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Delete huge Table under XFS
@ 2019-10-06 20:54 Tomas Vondra <tomas.vondra@2ndquadrant.com>
parent: Joao Junior <jcoj2006@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Tomas Vondra @ 2019-10-06 20:54 UTC (permalink / raw)
To: Joao Junior <jcoj2006@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2019-10-06 20:54 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-09-19 15:59 Delete huge Table under XFS Joao Junior <jcoj2006@gmail.com>
2019-09-19 16:50 ` Andreas Kretschmer <andreas@a-kretschmer.de>
2019-09-19 17:00 ` Joao Junior <jcoj2006@gmail.com>
2019-09-19 17:27 ` Christoph Berg <myon@debian.org>
2019-10-06 20:54 ` Tomas Vondra <tomas.vondra@2ndquadrant.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