Hello, i can't find an option to check the uncompressed size of tables in vertica, i need to migrate a cluster to new one with exports of data, and can't calculate the sizes on disk for the exports, without knowing the uncompressed size, vertica license checks the uncompressed size, so there should be an option to do it, can anyone help me with that?
p.s. i found user_audits, but it is not good because i need run audit on each table.
ok, i'v got answer in private message, thanks Daniel.
sorry i write here, but wanted to ask does it include the k-safety size or not?
edit:
ok, now i undesrtood it, thanks again Daniel, you are very helpful and solved my issue.
btw, why i can't answer pm's?
How to get uncompressed size of tables in vertica?
Moderator: NorbertKrupa
-
- Intermediate
- Posts: 163
- Joined: Tue Jun 19, 2012 2:44 pm
- Location: Rio de Janeiro
- Contact:
Re: How to get uncompressed size of tables in vertica?
--run the audit_license_size()
--this will populate the license_audits table
--it runs in the backgound but you can anticipate it's action
--Get your compression Ratio see the order by (will get you the last audit record)
--calculate the stored data size of your table
--calculate the raw data size of your table
-- You can also join all of this code and make a single script maybe to examine all of your tables.
Post the Solution that Daniel provided you ! Always great to have more resources on the forum.
Code: Select all
SELECT audit_license_size();
--it runs in the backgound but you can anticipate it's action
--Get your compression Ratio see the order by (will get you the last audit record)
Code: Select all
SELECT cast(database_size_bytes/(SELECT SUM(used_bytes) FROM v_monitor.projection_storage) as numeric(10,3)) as compression_ratio
FROM v_catalog.license_audits
ORDER BY audit_start_timestamp DESC LIMIT 1;
Code: Select all
select cast(SUM(used_bytes) / (1024 ^ 3) AS numeric(3,3))AS compressed_space
from projection_storage
where anchor_table_name = 'StockTransaction_Fact';
--calculate the raw data size of your table
Code: Select all
select cast((compressed_space * compression_ratio)as numeric(10,3)) as raw_size;
Post the Solution that Daniel provided you ! Always great to have more resources on the forum.
trying so hard !!!
Re: How to get uncompressed size of tables in vertica?
Using the queries above to determine the overall compression ratio is a starting point, but personally I don’t rely on general formulas like that since each table’s compression ratio will be different. I do in fact do an audit() on every large table in order to understand how each table is contributing to the total license consumption. By doing that I have observed tables that use a surprisingly large license percent - much more than you’d expect — because of unfortunate data model decisions.
—Sharon
—Sharon
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC
Vertica Consultant, Zazz Technologies LLC
-
- GURU
- Posts: 527
- Joined: Tue Oct 22, 2013 9:36 pm
- Location: Chicago, IL
- Contact:
Re: How to get uncompressed size of tables in vertica?
I don't think buddy projections should count towards compression.adrian.oprea wrote:--Get your compression Ratio see the order by (will get you the last audit record)Code: Select all
SELECT cast(database_size_bytes/(SELECT SUM(used_bytes) FROM v_monitor.projection_storage) as numeric(10,3)) as compression_ratio FROM v_catalog.license_audits ORDER BY audit_start_timestamp DESC LIMIT 1;
Checkout vertica.tips for more Vertica resources.
-
- Intermediate
- Posts: 163
- Joined: Tue Jun 19, 2012 2:44 pm
- Location: Rio de Janeiro
- Contact:
Re: How to get uncompressed size of tables in vertica?
Very good point , i have my testing lab on a one node DB (just need to add the is_super_projection = 't' to it)norbertk wrote:I don't think buddy projections should count towards compression.adrian.oprea wrote:--Get your compression Ratio see the order by (will get you the last audit record)Code: Select all
SELECT cast(database_size_bytes/(SELECT SUM(used_bytes) FROM v_monitor.projection_storage) as numeric(10,3)) as compression_ratio FROM v_catalog.license_audits ORDER BY audit_start_timestamp DESC LIMIT 1;
Code: Select all
SELECT cast(database_size_bytes/(SELECT SUM(used_bytes) FROM v_monitor.projection_storage where anchor_table_name in (select anchor_table_name from projections where is_super_projection = 't')) as numeric(10,3)) as compression_ratio
FROM v_catalog.license_audits order by audit_end_timestamp limit 1 ;
trying so hard !!!