Closed Bug 1617676 Opened 6 years ago Closed 6 years ago

Please get info around sync collections; usage and names in production.

Categories

(Cloud Services :: Operations: Miscellaneous, task, P3)

Product:
Cloud Services
Component:
Operations: Miscellaneous
Type:
task

Tracking

(Not tracked)

Status:
RESOLVED FIXED

People

(Reporter: rachel, Assigned: eolson)

Details

Stemming from an issue raised here, we'd like to collect some information around what Sync collection names are in use in production along with user counts, last updated, etc.

We plan to use this information to make naming decisions in the future, as well as understand if we need to add any restrictions/etc to collection naming moving forward.

Priority: -- → P3
Assignee: nobody → eolson

This is from a sync-py node in production:

MariadDB [weave0]> select * from collections;
+--------------+--------------+
| collectionid | name         |
+--------------+--------------+
|           99 |              |
|          105 | adblockplus  |
|          108 | adshield     |
|          110 | awe          |
|          102 | col1         |
|          100 | col2         |
|          104 | greasemonkey |
|          103 | meh          |
|          106 | mozcn.ntab   |
|          109 | reqpolsync   |
|          107 | userstyles   |
|          101 | xxxx         |
+--------------+--------------+
Status: NEW → RESOLVED
Closed: 6 years ago
Resolution: --- → FIXED

Erik - this is low priority, so feel free to ignore until you have time...but that's just one sync node, right? Ie, I assume they all have different datasets on them...would be helpful to have a snapshot into all users, not just those on a single sync node.

If you think this is going to be more easily done post-migration, that's completely fine too.

Status: RESOLVED → REOPENED
Resolution: FIXED → ---

I guess I'm not sure what you are looking for. I did some log scraping today to see what are the most frequently utilized collections using this query:

SELECT
  REGEXP_EXTRACT(jsonPayload.request, r" \/1\.5/\d{1,20}\/storage\/([a-z/]+)") AS collection,
  count(*),
  sum(jsonPayload.request_length) AS bytes_received,
  sum(jsonPayload.bytes_sent) AS bytes_sent,
  REGEXP_EXTRACT(jsonPayload.request, r"(.+) \/") AS method
FROM `moz-fx-sync-prod-3f0c.log_storage.stdout_20200402`
WHERE
  resource.type = "k8s_container" AND
  resource.labels.container_name = "nginx" AND
  REGEXP_CONTAINS(jsonPayload.request, r" \/1\.5/\d{1,20}\/storage\/")
GROUP BY
  collection, method
ORDER BY
  count(*)
DESC

I can provide the full results but here are the top 15 for yesterday:

collection   request_count  bytes_received   bytes_sent      method
forms        45037929       1157266051540.0  673584250232.0  POST
clients      13167556       15130264531.0    12259243126.0   GET
tabs         6276305        36400719219.0    408258544.0     POST
history      6061831        66062144971.0    1617403902.0    POST
meta/global  3501998        3956044294.0     1978793948.0    GET
crypto/keys  1970895        2192338720.0     801802116.0     GET
tabs         1790699        2064325619.0     5448053838.0    GET
clients      1568540        2845671579.0     102199228.0     POST
forms        1425125        1545907340.0     7493506108.0    GET
bookmarks    581392         8364192026.0     181995083.0     POST
history      530456         645358220.0      21684197253.0   GET
passwords    446076         473033019.0      633340263.0     GET
bookmarks    358196         376809032.0      7563030836.0    GET
passwords    252516         1043136996.0     52341823.0      POST
meta/global  190490         307592212.0      2448961.0       PUT

The full results would be interesting. The top results, less so, as we'd expect those to be used most often since they're our standard collections. If you want to link the full results, maybe....here that would be great for now.

I'm mostly asking so we can make decisions around naming conventions/etc in the future. We don't really have a good idea of the actual usage of "custom" collection names....and since we allow anything right now, I want to get a general idea of what's out there.

QA Contact: habib

Sure, I uploaded a csv with all the unique collections and their request_count, bytes_received, bytes_sent, and method from the access logs yesterday.

The top collections look a lot different now that we fixed the forms bug:

collection   f0_       bytes_received  bytes_sent     method
clients      14572074  16731120111.0   13759891522.0  GET
tabs         6765124   40169625294.0   439614873.0    POST
history      6749160   77119740951.0   1879474886.0   POST
meta/global  3985953   4504553646.0    2265978411.0   GET
crypto/keys  2252244   2505668070.0    915869682.0    GET
tabs         2036632   2348220341.0    6441342848.0   GET
clients      1786244   3246027928.0    116629399.0    POST
forms        1265744   9573606777.0    565189014.0    POST
bookmarks    639846    8965228991.0    205748728.0    POST
history      594412    723802640.0     25981135768.0  GET
passwords    494226    520363396.0     691139140.0    GET
bookmarks    413510    433776272.0     9148823508.0   GET
passwords    254853    1117665365.0    56670263.0     POST
forms        191231    215152605.0     4660523809.0   GET
meta/global  178811    289511789.0     2306884.0      PUT

And when you consolidate, eliminate all the sharding (?) of tabs/abc, clients/xyz, and others, these are the unique collection path prefixes:

adblocker
adblockplus
addons
addresses
bookmarks
clients
collection
crypto
forms
greasemonkey
history
meta
passwords
policy
prefs
tabs

Thanks, Erik!

Status: REOPENED → RESOLVED
Closed: 6 years ago → 6 years ago
Resolution: --- → FIXED

Ryan or Thom....do either of you know what the logic is around the tabs/a, tabs/b collection names? Just curious as it seems we have a lot of them, and I'm imagining maybe there's some logic baked into the client around this? Or, is this us testing somehow?

Flags: needinfo?(tchiovoloni)
Flags: needinfo?(rfkelly)

And when you consolidate, eliminate all the sharding (?) of tabs/abc, clients/xyz, and others, these are the unique collection path prefixes:
[...]
do either of you know what the logic is around the tabs/a, tabs/b collection names?

I'm not sure I fully understand the question here, but based on this:

REGEXP_EXTRACT(jsonPayload.request, r" /1.5/\d{1,20}/storage/([a-z/]+)") AS collection

I suspect you're seeing an artifact of how clients access different collections.

For most collections the client will access them only via bulk operations, such fetching all changed items via GET /storage/<collection> or uploading a batch of items via POST /storage/<collection>.

For some collections the client will instead access specific items in that collection by id, e.g. via GET /storage/<collection>/<id> or PUT /storage/<collection>/<id>. Access to /storage/meta/global is the most obvious, but I'm not surprised to hear that the tabs engine does similar things.

I think if you exclude the / from the pattern for matching collection names then you'll get accurate results. Per the API docs a collection name "must contain only characters from the urlsafe-base64 alphaebet (i.e. alphanumeric characters, underscore and hyphen) and the period" so I think you'd want something like:

REGEXP_EXTRACT(jsonPayload.request, r" /1.5/\d{1,20}/storage/([a-zA-Z0-9._-]+)") AS collection

(Assuming greedy match semantics for REGEXP_EXTRACT).

Flags: needinfo?(rfkelly)
Flags: needinfo?(tchiovoloni)

Got it, thanks, didn't think to read the api spec.

With meta/global and crypto/keys, I thought that I should capture everything up to the # or ?. This is the query for yesterday with the new regex extract:

collection    f0_       bytes_received   bytes_sent       method
clients       15259120  1.747933013E10   1.43405119E10    GET
history       7299451   8.3368663305E10  2.032112943E9    POST
tabs          7230695   4.2402556991E10  4.6987216E8      POST
meta          4410327   4.97598769E9     2.519422569E9    GET
crypto        2521200   2.798591061E9    1.02558859E9     GET
tabs          2199429   2.533771278E9    6.734748415E9    GET
clients       2012490   3.641914753E9    1.32001671E8     POST
forms         1280587   8.85998413E9     5.19587473E8     POST
bookmarks     737924    9.485818385E9    2.16158362E8     POST
history       608687    7.45056175E8     2.6552707237E10  GET
passwords     557533    5.85716174E8     7.82524938E8     GET
bookmarks     466362    4.88439459E8     2.3474440965E10  GET
passwords     273753    1.153506697E9    5.8192322E7      POST
meta          185127    2.99283742E8     2388391.0        PUT
forms         175053    1.96692263E8     3.618063512E9    GET
tabs          166666    1.69418882E9     2172846.0        PUT
passwords     151978    1.84729192E8     1978415.0        DELETE
prefs         138198    1.950337451E9    1.4498286E7      POST
crypto        90678     1.43540956E8     1178552.0        PUT
forms         75705     9.0299153E7      984489.0         DELETE
clients       71607     8.4674351E7      978977.0         DELETE
tabs          68724     8.1050291E7      932165.0         DELETE
addons        66828     1.42718368E8     8338523.0        POST
bookmarks     66715     7.9094717E7      867308.0         DELETE
history       66635     7.8821291E7      866294.0         DELETE
prefs         65875     7.7945319E7      856537.0         DELETE
addons        65494     7.7618618E7      851571.0         DELETE
addons        49511     6.0337175E7      4.1558449E7      GET
clients       27942     5.0630391E7      364439.0         PUT
prefs         21764     2.5461477E7      2.04346636E8     GET
addresses     8091      4.1511294E7      1160578.0        POST
addresses     3365      3959644.0        43745.0          DELETE
addresses     1183      1384500.0        8631780.0        GET
history       197       345260.0         2723.0           PUT
policy        117       118808.0         0.0              GET
bookmarks     80        142402.0         286.0            PUT
greasemonkey  4         4824.0           52.0             DELETE
greasemonkey  4         8165.0           458.0            POST
clients       2         803.0            0.0              HEAD
tabs          2         801.0            0.0              HEAD
adblockplus   1         1211.0           13.0             DELETE
meta          1         386.0            0.0              HEAD
creditcards   1         1179.0           13.0             DELETE
adblockplus   1         3702.0           89.0             POST

And indeed, Bigquery relies on the re2 library which does have greedy symantics for a+.

You need to log in before you can comment on or make changes to this bug.