Tracking Table Row Counts Over Time
Jim_Knicely
- Select Field - Administrator
I would like track the row counts from all of my database tables over time. Doing so will help me predict future growth.
How do I that? Like this…
Example:
dbadmin=> CREATE TABLE table_row_count(audit_date DATETIME, table_schema VARCHAR(128), table_name VARCHAR(128), row_count INT)
dbadmin-> ORDER BY table_schema, table_name, audit_date UNSEGMENTED ALL NODES;
CREATE TABLE
dbadmin=> \! vsql -Atc "SELECT 'SELECT sysdate || \$\$|' || table_schema || '|' || table_name || '|\$\$ || (SELECT COUNT(*) FROM \"' || table_schema || '\".\"' || table_name || '\")' || NVL2(LEAD(table_schema) OVER (ORDER BY table_schema), ' UNION', ';') FROM tables WHERE table_schema || '.' || table_name <> 'public.table_row_count';" | vsql -At | vsql -c "COPY table_row_count FROM STDIN DIRECT;"
dbadmin=> SELECT * FROM table_row_count ORDER BY row_count DESC;
audit_date | table_schema | table_name | row_count
---------------------------+--------------+-----------------+-----------
2019-02-14 08:19:22.406804 | Happy | Valentine's Day | 117440512
2019-02-14 08:19:22.406804 | public | table_b | 12288
2019-02-14 08:19:22.406804 | public | parts | 8
(3 rows)
Are the row counts accurate?
dbadmin=> SELECT COUNT(*) FROM "Happy"."Valentine's Day"; COUNT ----------- 117440512 (1 row)
Yup!
Now I can add my table row count job to CRONTAB so that it runs once a day at 11:59 PM.
[dbadmin@s18384357 ~]$ cat table_row_count.sh . ~/.bashrc /opt/vertica/bin/vsql -Atc "SELECT 'SELECT sysdate || \$\$|' || table_schema || '|' || table_name || '|\$\$ || (SELECT COUNT(*) FROM \"' || table_schema || '\".\"' || table_name || '\")' || NVL2(LEAD(table_schema) OVER (ORDER BY table_schema), ' UNION', ';') FROM tables WHERE table_schema || '.' || table_name <> 'public.table_row_count';" | /opt/vertica/bin/vsql -At | /opt/vertica/bin/vsql -c "COPY table_row_count FROM STDIN DIRECT;" [dbadmin@s18384357 ~]$ crontab -l # Minute Hour Day Month Day of Week Command 5 3 * * * /opt/vertica/oss/python/bin/python -m vertica.do_logrotate &> /dev/null 59 23 * * * /home/dbadmin/table_row_count.sh
In a few months I’ll have a pretty good history of how my tables are growing and/or shrinking!
Helpful Links:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Aggregate/COUNTAggregate.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/ConnectingToVertica/vsql/CommandLineOptions.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/ConnectingToVertica/vsql/CopyingDataUsingVsql.htm
Have fun!
0