How to get exact size of a table??

Merlin_Arockias

I have a database with k-safe=1. Hence there are two projections (suffixed with b0 and b1) in place of one super projection. So the data for each table is doubled(may be). In this scenario, how can I get the exact size of a table?? AUDIT function does not provide the exact size,so does COLUMN_STORAGE table. Can anyone help me in finding the exact size of a table as well as schema??

Navin_C

Hi Merlin, audit() gives Raw table size where as Column_storage gives compressed table size. For more information on this Please refer my post on this link http://www.vertica-forums.com/viewtopic.php?f=63&t=1361 For schema level size use this query

   SELECT SUM(column_used_bytes) AS table_size_in_bytes  ,TRIM ( TRAILING '0' FROM (CAST(round(SUM(column_used_bytes) / 1024 ,2) AS VARCHAR)))AS table_size_in_KB  ,TRIM ( TRAILING '0' FROM (CAST(round(SUM(column_used_bytes) / 1024 /1024,2) AS VARCHAR))) AS table_size_in_MB   FROM  (SELECT anchor_table_schema, SUM(used_bytes) AS column_used_bytes  FROM column_storage WHERE anchor_table_schema='DV0'  AND node_name IN (SELECT node_name FROM nodes)  GROUP BY anchor_table_schema HAVING anchor_table_schema ='DV0') sub  

Hope this helps you.

Merlin_Arockias

Thanks Navin!! This query considers the buddy projections also to get the schema size. If i want to have the size of a table which has multiple buddy projections as super projection, how to get the actual size excluding the duplicates?? I need to have the exact size as I am doing Migration from Oracle to Vertica. For validation purpose and to convince the client of size reduction using the metric, I have to exclude the duplicates due to buddy projection!! Any idea about this?? Anyway thanks a lot for your reply

Daniel_Leybovic

Hi! @Navin: your solution isn't good - your solution uses in COLUMN_STORAGE while topic starter claim: "AUDIT function does not provide the exact size,so does COLUMN_STORAGE table" @merlin Dude, with claims like above, I have to say - you know more than we are. So why you need help from us?

Prasanta_Pal

use  "projection_storage" table to get the size of a table, you can write a query to get the sum of, used_bytes, ros_used_bytes etc.

Merlin_Arockias

@Prasanta,
I tried the following query. The result seems ok!! Is this enough to get the total_size??
SELECT ANCHOR_TABLE_NAME,
       PROJECTION_SCHEMA,
       ((SUM(USED_BYTES)+SUM(ROS_USED_BYTES))/1024/1024/1024) AS TOTAL_SIZE
  FROM PROJECTION_STORAGE WHERE ANCHOR_TABLE_NAME=<TABLE_NAME>
   AND ANCHOR_TABLE_SCHEMA=<SCHEMA_NAME>
 GROUP BY PROJECTION_SCHEMA,ANCHOR_TABLE_NAME;


Thank a lot for your help!!

raj_ch

Im not expert but, i did not understand why we are adding  used bytes and ros used bytes. I took only used bytes.

SELECT
      ANCHOR_TABLE_NAME,
       PROJECTION_SCHEMA,
      ((SUM(USED_BYTES))/1024/1024/1024)  AS TOTAL_SIZE
  FROM PROJECTION_STORAGE WHERE ANCHOR_TABLE_NAME = <table_name>
  AND ANCHOR_TABLE_SCHEMA=<schema>
  AND PROJECTION_NAME like '%b0'
 GROUP BY PROJECTION_SCHEMA, ANCHOR_TABLE_NAME;

Sajan_Sangraula

Hi Raj,
Your observation is correct. Used Bytes is the sum of ROS and WOS used bytes, so Merlin's query is double counting the ROS storage.

If your projection naming convention doesn't end with b0 or you also want to report on store of tables with replicated projection, then the projection name filter in the where clause can also be changed to a subquery on the PROJECTIONS system table.

/Sajan

enricomarchesin

Last query is close, but it needs a few little tweaks:

SELECT anchor_table_schema,
anchor_table_name,
SUM(used_bytes) / 1024/1024/1024 AS TABLE_SIZE_GB
FROM v_monitor.projection_storage
GROUP BY anchor_table_schema,
anchor_table_name
order by sum(used_bytes) desc;