Database Storage – Raw and Compressed Size: Quick Tip

Posted October 8, 2018 by Phil Molea, Sr. Information Developer, Vertica

white cloud in vault type room representing cloud computing
Jim Knicely authored this post.

One of the files generated by the /opt/vertica/scripts/collect_diag_dump.sh script details the raw and compressed size for every table in the Vertica database.

At the bottom of the report there is a “total” line that shows the database raw and compressed size!

Example [dbadmin@vertica8 ~]$ /opt/vertica/scripts/collect_diag_dump.sh -c -s false Database Password: Exporting profiling data........ Done Exporting catalog and design.. Done Computing compression Done Forming archive file Done Diagnostics dump available at ./diag_dump_20181008102602.tar.gz [dbadmin@vertica8 ~]$ tar -xvzf ./diag_dump_20181008102602.tar.gz tmp/Out_20181008102602/ tmp/Out_20181008102602/execution_engine_profiling_plans tmp/Out_20181008102602/out_execution_engine_profiles tmp/Out_20181008102602/out_dc_plans tmp/Out_20181008102602/out_dc_plan_steps tmp/Out_20181008102602/out_dc_plan_step_properties tmp/Out_20181008102602/out_dc_plan_step_output_schema tmp/Out_20181008102602/out_dc_plan_parents tmp/Out_20181008102602/design tmp/Out_20181008102602/planning_objects tmp/Out_20181008102602/version tmp/Out_20181008102602/compression [dbadmin@vertica8 ~]$ cat tmp/Out_20181008102602/compression TableName PjCnt PjType RowCount RawSize(MB) Vertica(MB) Compr.Ratio ================================================================================ public.test 1 S 268435456 986.18 257.74 3.83 some_schema.some_table 1 S 4194304 8.00 0.02 400.00 _________________________________________________________________________________ TOTAL 994.18 257.76 3.86 Helpful link:

https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/AdministratorsGuide/Diagnostics/ExportingProfilingData.htm

Have fun!