Closed Bug 962322 Opened 12 years ago Closed 11 years ago

Need to move raw blocklist tables to an alternate location off of Vertica

Categories

(Data & BI Services Team :: DB: MySQL, task)

x86
macOS
task
Not set
major

Tracking

(Not tracked)

RESOLVED WONTFIX

People

(Reporter: aelliott, Assigned: mpressman)

Details

(Whiteboard: [data: data_quality])

There are "raw" data tables for blocklist and downloads in Vertica. We need to plan/prepare for the worst case scenario and move our data to a safe environment. Please create export vertica blocklist and download raw to text file(s), and once they are safely off, prepare to move them to an archival MySQL location (in tables by year). This is important because our current backups are in Vertica's proprietary format.
Assignee: server-ops-database → mpressman
TABLES to be moved blocklist_requests_by_day blocklist_requests_by_day_th blocklist_requests_from_hive blocklist_requests_raw
^^^ Those above have highest priority Followed by: download_products download_requests_by_day download_requests_by_day_th download_requests_raw amo_download_requests_by_day amo_download_requests_raw amo_download_valid_sources
I'm running a loose audit() to get an idea of the raw table size of blocklist_requests_by_day. This will then determine the best way to get the data exported
blocklist_requests_by_day is 2104580196942 ~ 2TB raw
blocklist_requests_by_day_th is 389448168334 ~ 363GB raw
blocklist_requests_from_hive is 8262 ~ 8KB raw
blocklist_requests_raw is 92487744092 ~ 86GB raw
download_products 4816225 ~ 4.5MB raw download_requests_by_day 256051303707 ~ 238.5GB raw
download_requests_by_day_th 24856546234 ~ 23GB raw
download_requests_raw 25259480836 ~ 23.5GB raw
mpressman: blocklist_requests_raw_summary is 2104580196942 ~ 2TB raw [4:25pm]
amo_download_requests_by_day is 205782533202 ~ 192GB raw
So far I am getting 2TB + 2TB + ~750GB... Almost 5TB. I would imagine we need to retain some number of days back on Vertica, so not quite the whole lot can be wiped, but it looks like maybe 4TB of the almost 5 could. For my own notes: 16.5TB - 4 TB = ~13.5TB, need to maintain some buffer for performance. $4700/TB * 15TB = $70k for 1 year term
(will esimate again when loose audit is complete)
Annie, just to clarify my irc comment blocklist_requests_raw_summary is 17551232122 ~ 16GB raw I meant to give you the rough blocklist_requests_by day. That table is 2TB raw
amo_download_requests_raw is 904990440 ~ 863MB raw
AH. OK. Bummer. There goes 2 TB :/
amo_download_valid_sources is 1519 ~ 1.5KB raw
I'm not sure how it's counted per https://my.vertica.com/docs/6.1.x/HTML/index.htm#15447.htm Calculating the Database Size->Excluding Data from Raw Data Size Estimate *Multiple projections (underlying physical copies) of data from a logical database entity (table). Data appearing in multiple projections of the same table is counted only once. If this means that the first projection using the underlying table is counted, but each subsequent one is not, we may be able to count everything that the first projection is using if they are using any of the above tables
Also, for whatever value, here are whole table raw sizes for the next five largest tables determined by their compressed sizes. The largest table compressed is blocklist_requests_by_day which is already on the list. Vertica allows for querying external tables and is not counted towards total size compliance. An idea would be to store some of these and even query it in HDFS without copying it into Vertica, thus HDFS for storage Vertica and Hadoop for analytics. Or a combination where only a subset is loaded into Vertica versioncheck_requests_by_day is 4627889309214 ~ 4.2TB raw opt_f_weblog_lightweight_themes is 1646993212157 ~ 1.5TB raw opt_f_weblog_synccollection is 2103344463174 ~ 1.9TB raw aus_requests_by_day is 2589386137905 ~ 2.4TB raw opt_f_weblog_sync is 1072823437122 ~ 1TB raw
None of the raw tables We are looking into drive our dashboards to my knowledge, unless the aggregations are projections of them. But holy cow, the version check and aus by day tables are awesome news!
Flags: needinfo?(aelliott)
Flags: needinfo?(aelliott)
Ricardo - please comment on the safety of offloading the tables enumerated in the various comments above, and how it would affect ETL.
Flags: needinfo?(rfradinho)
Anurag - please add your commentary as the the veracity and utility of the version check and aus data here.
Flags: needinfo?(aphadke)
Hi Matt - please also look at the FHR tables and bugzilka snapshots. They maybe candidates for MySQL marts
Flags: needinfo?(mpressman)
(In reply to Annie Elliott from comment #23) > Anurag - please add your commentary as the the veracity and utility of the > version check and aus data here. annie - rfradinho and bsullins have better insights than me in terms of who consumes the vertica data in raw and tableau format. AFAIK, we no longer use AUS data as it sends more than one ping/day for updates.
Flags: needinfo?(aphadke)
All fhr tables f_fhr_searches_daily_aphadke is 55942714888 ~ 52GB raw f_fhr_searches_daily is 65204108581 ~ 60GB raw f_fhr_addon_counts is 24054881490 ~ 22.5GB raw f_fhr_profile_counts is 27127234422 ~ 25GB raw fhr_daily_search_counts_aphadke is 6434301323 ~ 6GB raw fhr_daily_search_counts is 2870272657 ~ 2.6GB raw f_fhr_profile_age is 4242005061 ~ 4GB raw f_fhr_adi_latest_aphadke is 3964226665 ~ 3.7GB raw fhr_profile_age_aphadke is 2400105519 ~ 2GB raw fhr_f_crashes is 473172111 ~ 451MB raw fhr_adi_counts_aphadke is 2329866773 ~ 2GB raw fhr_daily_session_counts is 234336274 ~ 223.5MB raw fhr_profile_age is 137762728 ~ 131MB raw f_fhr_searches_daily_tmary is 230759203 ~ 220MB raw f_fhr_profile_age_tmary is 213945616 ~ 204MB raw f_fhr_profile_age_buckets is 166601250 ~ 159MB raw f_fhr_profile_counts_tmary is 143630887 ~ 127MB raw fhr_daily_search_counts_v3 is 45170351 ~ 43MB raw f_fhr_adi_last_x_days_aphadke is 35509904 ~ 34MB raw fhr_socialapi_aphadke is 72431423 ~ 69MB raw fhr_date_range_adi_counts_aphadke is 30031374 ~ 28.6MB raw f_fhr_profile_age_buckets_tmary is 27881269 ~ 26.5MB raw fhr_product_channels is 8551 ~ 8KB raw fhr_locations is 4187 ~ 4KB raw fhr_operating_systems is 2985 ~ 3KB raw fhr_locales is 1500 ~ 1.5KB raw fhr_products is 1563 ~ 1.5KB raw fhr_f_crashes_from_hdfs is 4840 ~ 4.7KB raw fhr_search_engines is 2900 ~ 2.8KB raw fhr_search_contexts is 1480 ~ 1.4KB raw fhr_bucketed_profile_age is 1016 ~ 1KB raw fhr_bucketed_addon_count is 1000 ~ 1KB raw all bugs snapshot tables f_bugs_snapshot_v2 is 26933316417 ~ 25GB raw f_bugs_snapshot is 29655896808 ~ 27.6GB raw f_bugs_snapshot2 is 2481073 ~ 2.4MB raw
Flags: needinfo?(mpressman)
** Data Exports ** There's already a manual, compressed, txt snapshot of the DB in HFDS (/user/rfradinho/vdumps4) It's stored in a txt format suited for vertica imports but it's basically csv and can be easily converted to another import format (unfortunately cannot be used directly by Hive but should be possible to use a custom csv parser) That export finished on 2013-09-19 and took 3 days to complete. The total size of the compressed data is 2.2 TB and most of the tables are partitioned by quarter and we have a file for each partition (the initial plan was to create incremental backups using snapshots of the current quarter).
Flags: needinfo?(rfradinho)
** Migration Strategy ** Most of the tables are partitioned by quarter, so export/importing into another DB only the latest quarters should be fast. This allows the reports and dashboards to be up again using another DB. There is also an tool we use to sync from MySQL to Vertica. It relies only on jdbc/sql so it can be used to sync Vertica to another DB after export/importing the quarters. Regarding ETL, it has no dependencies on Vertica features so it can be easily changed to use another DB.
Ricardo - Thank you! Very good to know. One more thing at the moment... These "raw" and raw-like tables (blocklist_requests_raw, by_day, etc) does any of the ETL, or any of the aggregation jobs depend on that data living permanently on Vertica?
Flags: needinfo?(rfradinho)
There's no need to have all data in Vertica: The *_raw tables are staging tables. They only have about 3 days of data and they are a map of 1:1 of weblog records into table records. At the end of the day (EOD), the previous day's data is grouped up to the day granularity into the respective *_by_day table. The _raw tables are constantly getting new data and purging old data. ETL is designed to fetch, parse and load all data into _raw tables automatically if it's not there. There are two levels of aggregations: *_raw => *_by_day => a_* The a_* tables are just aggs of the _by_day with some dimensions collapsed. All of them have the day granularity, so there's actually no need for long term data as ETL only needs yesterday's data to maintain these aggs.
Flags: needinfo?(rfradinho)
Matt's immediate TODOs: 1) purge anallysis
Matt's immediate TODOs: 1) purge analysis 2) archive version check (~4.2TB) 3) archive aus (~2.4TB) 4) delete and purge all but last 7 days version check 5) delete and purge all but last 7 days aus
Annie's immediate TODO: 1) create a gdoc listing sources to move off 2) link in this bug
I'm looking into the system tables to correlate Ancient History Mark (AHM) to tables in order to sort the largest amount of data to be purged
working on that spreadsheet, fyi
All - Here is the spreadsheet. I believe I have shared correctly. If you cannot see it, just let me know.
Ben - are you using blocklist requests by day for anything? If so, how many workbooks, and what would be the down time to repoint to an archival mysql source?
Flags: needinfo?(bsullins)
Hi Annie - I can see the spreadsheet. For folks who want a direct URL - https://docs.google.com/a/mozilla.com/spreadsheet/ccc?key=0AkRB7GSGQUZUdHlJWnVMZDFQNjZOWl9xOU5pQ0RIV0E&usp=drive_web (although I found it in the "Shared with me" part of Google Drive).
I don't believe I do but am going to check a couple first. There may have been one recently that I published because of missing dimensions in adi_dimensional_by_date. Will report back today...
feel free to move forward, all I could find were a couple queries I was using this table for testing, nothing that is certified as of yet.
Flags: needinfo?(bsullins)
Product: mozilla.org → Data & BI Services Team
Whiteboard: [data: data_quality]
Status: NEW → RESOLVED
Closed: 11 years ago
Resolution: --- → WONTFIX
You need to log in before you can comment on or make changes to this bug.