Greenplum disk size

[muhammad ali]

Hello experts,
I'm using opensource GP version 6.20.5 with 4 segments. Its a single node setup.
Currently when I run the following query for total table size(along with index + toast),

 create view dbadmin.sizes as
  SELECT *
  FROM (
  SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
      SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
              , c.reltuples AS row_estimate
              , pg_total_relation_size(c.oid) AS total_bytes
              , pg_indexes_size(c.oid) AS index_bytes
              , pg_total_relation_size(reltoastrelid) AS toast_bytes
          FROM pg_class c
          LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
   
  ) a
) a
order by total_bytes desc;

select pg_size_pretty(sum(total_bytes)),pg_size_pretty(sum(index_bytes)),pg_size_pretty(sum(toast_bytes)),pg_size_pretty(sum(table_bytes)) from dbadmin.sizes;

It gives me the following result:

 totalsize  | totalindexsize  | totaltoast | totaltable
----------------+----------------+----------------
 2546GB   |       375 GB        | 19 GB       | 2152 GB

When I check database size, it says 7 TB consumed. 
(checked from df, \l+ and du -sh inside directories)

I've ran vacuum full analyze on almost all tables. Also checked segment directories for any other related file which are contributing in disk usage but only base folder is in TBs across all segments.
Now I need to know which object is taking space? Is this a bug in 6.20.5 ? Or is it related to column Alignment/padding which is leading to disk usage?

Looking forward for your expertise.
Thanks and Regards,
Muhammad Ali

[Luis Filipe de Macedo]

Do you have mirrors? If you do that is a 2x difference.

 

I can’t validate your query, but I think others here can. Sometimes those queries from PostgreSQL do not work properly with Greenplum due to some differences between the products.

 

 

Rgds,

 

Luis Filipe R Macedo

Adv Data Engineering

VMware Data Solutions

+55 11 988608596

Take care of the customers and the rest takes care of itself

--

 

From: muhammad ali <engmuha...@gmail.com>
Date: Saturday, 11 February 2023 16:39
To: Greenplum Users <gpdb-...@greenplum.org>
Subject: [gpdb-users] Greenplum disk size

!! External Email

--
You received this message because you are subscribed to the Google Groups "Greenplum Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to gpdb-users+...@greenplum.org.
To view this discussion on the web visit https://groups.google.com/a/greenplum.org/d/msgid/gpdb-users/220bdb34-e706-4b4e-ab79-1a97c51e1e5an%40greenplum.org.

 

!! External Email: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender.

 

[Kevin Huang]

Are most of you tables AO/AOCO? These tables do have take up some additional storage that is done in addition to the actual data which is normal. 

Also, the view is being run against 1 database from what I can tell. When you do du -sh on base, it will total up the space on all the databases. Would be good to confirm if you ran du -sh on just the one database oid's directory and it totaled up 7 TB.

Otherwise, would be good to have an example of your largest tables so we can compare the space with the size on disk.

Kevin

[muhammad ali]

Hello 

Thanks for your responses guys

1:There are no mirrors configured for this.

2: we only have 1 database which is around 7TB total.

3: 10-15 tables are Append optimized. Around 1500+ tables are heap. Large table is around 450GB(toast + index + table).
4: Vacuum full ran for all tables almost. 
5: Checked log files and no sign of any error.

Thanks and Regards,

Muhammad Ali 

[Luis Filipe de Macedo]

Muhammed,

 

There are views under gp_toolkit that will help you get the proper sizes:

 

https://docs.vmware.com/en/VMware-Tanzu-Greenplum/6/greenplum-database/GUID-ref_guide-gp_toolkit.html?hWord=N4IghgNiBcIOYAcD6AXA9miBrAlikAvkA

 

Based on these views you might be able to see what is missing from your query.

[muhammad ali]

Hello Luis,
I've tried the following views:

gp_size_of_schema_disk

gp_size_of_table_and_indexes_disk

gp_toolkit.gp_size_of_table_uncompressed

gp_size_of_database

=> total size(Schema) or total_size(Tables) is around 2-2.5 Tb with index + toast.
=> gp_size_of_database gives 7TB in output.

[Ashwin Agrawal]

On Tue, Feb 14, 2023 at 3:41 PM muhammad ali <engmuha...@gmail.com> wrote:

Hello Luis,
I've tried the following views:

gp_size_of_schema_disk

gp_size_of_table_and_indexes_disk

gp_toolkit.gp_size_of_table_uncompressed

gp_size_of_database

=> total size(Schema) or total_size(Tables) is around 2-2.5 Tb with index + toast.
=> gp_size_of_database gives 7TB in output.

Yet to play around and look into full details. Though one of the possibilities is non-relation files in database directory like relcache and such occupying space which gets counted by pg_database_size function (used by gp_size_of_database) and df but will not be by iterating over only relation sizes from pg_class.

So, in base/ directory perform ls -alh [^0-9]* and check if the size of these non-relation files maps to the difference, especially size of pg_internal.init file in all the segment directories.

 

--

Ashwin Agrawal (VMware)

[muhammad ali]

Hello Ashwin,

I just checked the size using  ls -alh [^0-9]* on all segments

pg_internal.init  136KB 
pg_filenode.map 8 KB

And some other files which makes a total of 4-5 GB.
1: Pg_catalog schema is around 130GB. Is it possible that after vacuuming catalog tables, we'll get correct stats?
2: Should I check with infra guys to validate disk health check?

Thanks and Regards,
Muhammad Ali

[Ashwin Agrawal]

On Tue, Feb 14, 2023 at 10:02 PM muhammad ali <engmuha...@gmail.com> wrote:

Hello Ashwin,

I just checked the size using  ls -alh [^0-9]* on all segments

pg_internal.init  136KB 
pg_filenode.map 8 KB

And some other files which makes a total of 4-5 GB.

Okay, good to know.

 

1: Pg_catalog schema is around 130GB. Is it possible that after vacuuming catalog tables, we'll get correct stats?
2: Should I check with infra guys to validate disk health check?

I don't think this is an artifact of bloat or disk issues. Bloated tables still should match in size between total table size accumulated vs database size as both use physical size and not estimates.

One possibility is orphan (relation) files in the database directory. If there are orphan files, the database size function would calculate them in size (as it just does directory lookup) vs gp_size_of_schema_disk which will go through pg_class and only report size for relations present in catalog.

I am attaching SQL script file which you can try to see if it reports any orphan files and does size match up to the difference you are finding. Do make note of these - 

- The function creates a table and an external table to gather and store information, it

is possible for tables to be created and dropped while the data is being collected, which could
result in files being incorrectly identified as orphaned

- It is recommended to use the script provided as a starting point for identifying potential orphan files on the Greenplum database. To supplement the information gathered by the script, it is advised to review the "last updated" and "file size" fields produced by the function. Additionally, cross- referencing the reported files with the "gp_dist_random('pg_class')" table or running the script multiple times at different times of the day can provide further insight into which files may actually be considered orphaned.

run the function as 

CHECKPOINT;

SELECT * FROM public.greenplum_check_orphan_files();

--

Ashwin Agrawal (VMware)

[muhammad ali]

Hello Ashwin,

Thanks for the function. I ran this function once after manual checkpoint and voila

It returned around 35329 records. Last updated values are mostly around Jan 2022. Checked using this query:

select to_char(to_date(last_updated,'MON DD YYYY'),'YYYY-MM-DD'),sum(file_size)/1024/1024/1024 as file_size_gbs from public.greenplum_check_orphan_files() group by 1 order by 1;

When I added file_size column, It gave me around 4.8TB which is almost equal to the missing space.

select sum(file_size)/1024/1024/1024 as  size_gb  from public.greenplum_check_orphan_files();

size_gb
-----------------------
 4800.8426322937011719

What is your advice on this? How can we remove these Orphan files without crashing GP server?

Thanks and Regards,
Muhammad Ali

[Ashwin Agrawal]

On Fri, Feb 17, 2023 at 5:35 PM muhammad ali <engmuha...@gmail.com> wrote:

Hello Ashwin,

Thanks for the function. I ran this function once after manual checkpoint and voila

It returned around 35329 records. Last updated values are mostly around Jan 2022. Checked using this query:

select to_char(to_date(last_updated,'MON DD YYYY'),'YYYY-MM-DD'),sum(file_size)/1024/1024/1024 as file_size_gbs from public.greenplum_check_orphan_files() group by 1 order by 1;

When I added file_size column, It gave me around 4.8TB which is almost equal to the missing space.

select sum(file_size)/1024/1024/1024 as  size_gb  from public.greenplum_check_orphan_files();

size_gb
-----------------------
 4800.8426322937011719

What is your advice on this? How can we remove these Orphan files without crashing GP server?

Hmmmm, orphan files from more than a year back and that too 35329

- Just out of curiosity, what is the total number of files in directories?

- Are these orphan files corresponding to temporary relations if they have pattern "*_t" in file names? If they are super safe to remove them.

If not files corresponding to temp relations, then perform due diligence by taking the relfilnode reported against a given segment, cross check at least in pg_class for that segment if can find the relfilenode entry matching it or not. If the file has not been touched since last year and pg_class for that particular segment also doesn't have an entry for it in pg_class then for sure its orphan file and GPDB knows nothing about it, can be removed. One of the conservative way might be to instead of deleting the file directly, move it to some other temporary directory and continue to operate GPDB for some days and see.

Though I am confident if manually cross checking entry is not found in pg_class then safe to delete.

-- 

Ashwin Agrawal (VMware)

[muhammad ali]

Hello Ashwin,

Really thanks for the insights. I'll check it out

What if we upgrade to 6.23.1? Will that resolve the issue? Because we have only faced this 6.20.5

Other than this, what if we migrate to a new single node/cluster? Deleting/removing files is a little bit risky.  

[muhammad ali]

Hello Ashwin

Total no of files inside each segment is around 16000
I checked in pg_class for relfilenodes. Used the following query:

select * from pg_class  where relfilenode::text like '%1496%';
Where most of the files has 1496 as common value

Those entries doesn't exist inside pg_class. What's the best way to cater this

Thanks and Regards,
Muhammad Ali 

[Ashwin Agrawal]

On Tue, Feb 21, 2023 at 7:48 AM muhammad ali <engmuha...@gmail.com> wrote:

Hello Ashwin

Total no of files inside each segment is around 16000
I checked in pg_class for relfilenodes. Used the following query:

select * from pg_class  where relfilenode::text like '%1496%';
Where most of the files has 1496 as common value

Those entries doesn't exist inside pg_class. What's the best way to cater this

I am hoping you checked pg_class on segments and not master. Means will have to connect to segment via utility mode (advanced and support purpose usage only)

PGOPTIONS="-c gp_session_role=utility" psql -p <segment_port> <database name>

If checking on segments reveals the same result, no entry for these files, then it seems safe to remove these files with no impact to GPDB.

> What if we upgrade to 6.23.1? Will that resolve the issue? Because we have only faced this 6.20.5

No, upgrading doesn't resolve this situation. Neither does it have code to avoid orphan files so far. The case we consider in which orphan files will get created is extremely case like segment/cluster crash event while loading data and such.

> Other than this, what if we migrate to a new single node/cluster? Deleting/removing files is a little bit risky.

Migrating to new cluster is perfectly fine approach, if wish to go that route and have spare hardware to use. Only live objects in database will be migrated to new cluster. So, all these orphan files shouldn't matter. Just depends what route you are thinking for migration. Anything which logically copies the data is fine like gpbackup and such...

--

Ashwin Agrawal (VMware)