Thread: Delete huge Table under XFS
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!
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
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: 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
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