0

Recently there has been a drop in our db size and we want to compare the size of the tables back then with that of now. For this purpose we need the exact size of each table one month back. We can extract license_size for an old date, but how to extract size of each table say a month ago.

  • In general, the answer is: You start collecting information that you care about before deciding that you need a particular answer. If Vertica comes configured with the counters you are asking about, then the data you are looking for may already be present. But I would not assume that this has been done. – RLF Feb 23 '17 at 20:53
1

Unless you keep a log of it , you cannot. There is no history mechanism to keep track of tbl sizes.

you also can get a lot of scripts and articles on Vertica here www.aodba.com

What i do to keep an eye on this kind of stuff is logging this info in a dba use table using a cron job.

##will run every 30 minutes
*/30 * * * * /home/dbadmin/scripts/dba/database_growth.sh

Content .sh script

#!/bin/bash
#!/bin/bash

. /home/dbadmin/.profile
export VSQLDIR=/opt/vertica/bin
export SQLDIR=/home/dbadmin/scripts/dba
export data=`date`
echo " Execution date -" $data >> /home/dbadmin/scripts/dba/log/database_growth.log
echo "#################################################" >> /home/dbadmin/scripts/dba/log/database_growth.log
$VSQLDIR/vsql  -U $username -w $password -d analytics -f $SQLDIR/database_growth.sql >> /home/dbadmin/scripts/dba/log/database_growth.log
echo "#################################################" >> /home/dbadmin/scripts/dba/log/database_growth.log

Note:/home/dbadmin/.profile -- holds all the parameters values (this is to avoid text passwords - they are all logged as text - you wanna avoid this ) Content database_growth.sql

 insert INTO dba.database_growth(time,schema_name,size_gb) SELECT * from dba.pre_database_growth;

Content view dba.pre_database_growth

CREATE VIEW
    dba.pre_database_growth AS
SELECT
    (statement_timestamp())::TIMESTAMP AS GETDATE,
    p.anchor_table_name,
    s.schema_name,
    ROUND(((((SUM(s.used_bytes) / 1024::NUMERIC(18,0)) / 1024::NUMERIC(18,0)) /
    1024::NUMERIC(18,0)))::FLOAT, 3) AS size_in_bytes_gb
FROM
    v_monitor.storage_containers s join v_catalog.projections p
    on s.projection_id=p.projection_id
GROUP BY
    s.schema_name,p.anchor_table_name;

You can use this then to create growth reports on your objects over time.

I hope this helped.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.