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)
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.
| Reporter | ||
Updated•12 years ago
|
Assignee: server-ops-database → mpressman
| Reporter | ||
Comment 1•12 years ago
|
||
TABLES to be moved
blocklist_requests_by_day
blocklist_requests_by_day_th
blocklist_requests_from_hive
blocklist_requests_raw
| Reporter | ||
Comment 2•12 years ago
|
||
^^^ 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
| Assignee | ||
Comment 3•12 years ago
|
||
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
| Assignee | ||
Comment 4•12 years ago
|
||
blocklist_requests_by_day is 2104580196942 ~ 2TB raw
| Assignee | ||
Comment 5•12 years ago
|
||
blocklist_requests_by_day_th is 389448168334 ~ 363GB raw
| Assignee | ||
Comment 6•12 years ago
|
||
blocklist_requests_from_hive is 8262 ~ 8KB raw
| Assignee | ||
Comment 7•12 years ago
|
||
blocklist_requests_raw is 92487744092 ~ 86GB raw
| Assignee | ||
Comment 8•12 years ago
|
||
download_products 4816225 ~ 4.5MB raw
download_requests_by_day 256051303707 ~ 238.5GB raw
| Assignee | ||
Comment 9•12 years ago
|
||
download_requests_by_day_th 24856546234 ~ 23GB raw
| Assignee | ||
Comment 10•12 years ago
|
||
download_requests_raw 25259480836 ~ 23.5GB raw
| Reporter | ||
Comment 11•12 years ago
|
||
mpressman: blocklist_requests_raw_summary is 2104580196942 ~ 2TB raw
[4:25pm]
| Assignee | ||
Comment 12•12 years ago
|
||
amo_download_requests_by_day is 205782533202 ~ 192GB raw
| Reporter | ||
Comment 13•12 years ago
|
||
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
| Reporter | ||
Comment 14•12 years ago
|
||
(will esimate again when loose audit is complete)
| Assignee | ||
Comment 15•12 years ago
|
||
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
| Assignee | ||
Comment 16•12 years ago
|
||
amo_download_requests_raw is 904990440 ~ 863MB raw
| Reporter | ||
Comment 17•12 years ago
|
||
AH. OK. Bummer. There goes 2 TB :/
| Assignee | ||
Comment 18•12 years ago
|
||
amo_download_valid_sources is 1519 ~ 1.5KB raw
| Assignee | ||
Comment 19•12 years ago
|
||
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
| Assignee | ||
Comment 20•12 years ago
|
||
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
| Reporter | ||
Comment 21•12 years ago
|
||
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)
| Reporter | ||
Updated•12 years ago
|
Flags: needinfo?(aelliott)
| Reporter | ||
Comment 22•12 years ago
|
||
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)
| Reporter | ||
Comment 23•12 years ago
|
||
Anurag - please add your commentary as the the veracity and utility of the version check and aus data here.
Flags: needinfo?(aphadke)
| Reporter | ||
Comment 24•12 years ago
|
||
Hi Matt - please also look at the FHR tables and bugzilka snapshots. They maybe candidates for MySQL marts
Flags: needinfo?(mpressman)
Comment 25•12 years ago
|
||
(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)
| Assignee | ||
Comment 26•12 years ago
|
||
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)
Comment 27•12 years ago
|
||
** 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)
Comment 28•12 years ago
|
||
** 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.
| Reporter | ||
Comment 29•12 years ago
|
||
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)
Comment 30•12 years ago
|
||
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)
| Reporter | ||
Comment 31•12 years ago
|
||
Matt's immediate TODOs:
1) purge anallysis
| Reporter | ||
Comment 32•12 years ago
|
||
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
| Reporter | ||
Comment 33•12 years ago
|
||
Annie's immediate TODO:
1) create a gdoc listing sources to move off
2) link in this bug
| Assignee | ||
Comment 34•12 years ago
|
||
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
| Reporter | ||
Comment 35•12 years ago
|
||
working on that spreadsheet, fyi
| Reporter | ||
Comment 36•12 years ago
|
||
All - Here is the spreadsheet. I believe I have shared correctly. If you cannot see it, just let me know.
| Reporter | ||
Comment 37•12 years ago
|
||
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)
Comment 38•12 years ago
|
||
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).
Comment 39•12 years ago
|
||
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...
Comment 40•12 years ago
|
||
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)
Updated•12 years ago
|
Product: mozilla.org → Data & BI Services Team
Updated•11 years ago
|
Whiteboard: [data: data_quality]
Updated•11 years ago
|
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.
Description
•