{"action":"created","issue":{"url":"https://api.github.com/repos/mozilla/prowac/issues/1","repository_url":"https://api.github.com/repos/mozilla/prowac","labels_url":"https://api.github.com/repos/mozilla/prowac/issues/1/labels{/name}","comments_url":"https://api.github.com/repos/mozilla/prowac/issues/1/comments","events_url":"https://api.github.com/repos/mozilla/prowac/issues/1/events","html_url":"https://github.com/mozilla/prowac/issues/1","id":130621852,"number":1,"title":"Reconsider database choice","user":{"login":"zalun","id":97885,"avatar_url":"https://avatars.githubusercontent.com/u/97885?v=3","gravatar_id":"","url":"https://api.github.com/users/zalun","html_url":"https://github.com/zalun","followers_url":"https://api.github.com/users/zalun/followers","following_url":"https://api.github.com/users/zalun/following{/other_user}","gists_url":"https://api.github.com/users/zalun/gists{/gist_id}","starred_url":"https://api.github.com/users/zalun/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/zalun/subscriptions","organizations_url":"https://api.github.com/users/zalun/orgs","repos_url":"https://api.github.com/users/zalun/repos","events_url":"https://api.github.com/users/zalun/events{/privacy}","received_events_url":"https://api.github.com/users/zalun/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":0,"created_at":"2016-02-02T09:13:53Z","updated_at":"2016-02-03T05:15:06Z","closed_at":null,"body":"I've been thinking about that (writing some dry code)\r\nThere are at least 3 ways to represent our data (essentially redis db is represented as a top-level JSON):\r\n1. JSON of current snapshot\r\n```\r\n{ \r\n  snapshotKey: JSON.stringify(fetchedDataFromTopSites),\r\n  currentSnapshot: snapshotKey\r\n}\r\n```\r\nThat doesn't allow us to go deep to dynamically show data for each site separately\r\n\r\n2. Object of JSON records:\r\n```\r\n{ \r\n  snapshotKey: {\r\n    'someUrl': JSON.stringify(siteData),\r\n    // 1M records ...\r\n  },\r\n  currentSnapshot: snapshotKey\r\n}\r\n```\r\nThis usage allows for dynamic browsing the site information (JSON.parse is needed on one record only), but fails if we want to analyze the data directly from database. If we would decide to save fetched data to database and do the analysis after all sites are fetched and saved, we would have to get entire list from db then do a lot of JSON.parse. Then we've got 2 options:\r\n 1. push the Object to a big array in memory and do calculations later on\r\n 2. create arrays with data prepared for analysis\r\n\r\n3. Array of hashtags with unpacked information:\r\n```\r\n{\r\n  `http://somesite': {\r\n    'manifest': '...',\r\n    'httpredirect': true\r\n    // other fields ...\r\n  },\r\n  // 1M records ...\r\n  currentSites: ['http://somesite', 1M items ...],\r\n  snapshotKey: JSON.stringify(fetchedDataFromTopSites)\r\n}\r\n```\r\nThis solution might be easier to work on (programatically), as we may create a query for all httpredirects, etc. This however requires a bunch of multiple redis commands to be run in one call, and I've been reading that Redis has issues if there is 100000+. We can split it into 20 bunches, but still it is a pain...\r\n\r\nMy choice would be the second attempt, but we need to think if relational database wouldn't be a better option."},"comment":{"url":"https://api.github.com/repos/mozilla/prowac/issues/comments/179017188","html_url":"https://github.com/mozilla/prowac/issues/1#issuecomment-179017188","issue_url":"https://api.github.com/repos/mozilla/prowac/issues/1","id":179017188,"user":{"login":"TimAbraldes","id":955084,"avatar_url":"https://avatars.githubusercontent.com/u/955084?v=3","gravatar_id":"","url":"https://api.github.com/users/TimAbraldes","html_url":"https://github.com/TimAbraldes","followers_url":"https://api.github.com/users/TimAbraldes/followers","following_url":"https://api.github.com/users/TimAbraldes/following{/other_user}","gists_url":"https://api.github.com/users/TimAbraldes/gists{/gist_id}","starred_url":"https://api.github.com/users/TimAbraldes/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/TimAbraldes/subscriptions","organizations_url":"https://api.github.com/users/TimAbraldes/orgs","repos_url":"https://api.github.com/users/TimAbraldes/repos","events_url":"https://api.github.com/users/TimAbraldes/events{/privacy}","received_events_url":"https://api.github.com/users/TimAbraldes/received_events","type":"User","site_admin":false},"created_at":"2016-02-03T05:15:06Z","updated_at":"2016-02-03T05:15:06Z","body":"Thanks for looking into this @zalun!! Your analysis looks great and I appreciate you writing it up here.\r\n\r\nJust to get this all straight in my head, I'll start by listing what I believe our database requirements are.\r\n\r\nI believe that our database requirements from the dashboard are:\r\n\r\n- Retrieve aggregate info (e.g. % of sites using Manifest) from the last complete crawl\r\n- Retrieve historical aggregate info from each of the last 12 (or so) complete crawls\r\n- Retrieve current/historical data about a specific URL\r\n\r\nAnd that our database requirements from the crawler are:\r\n\r\n- Retrieve the next URL to fetch/process for the current crawl\r\n- Store the processed information for a specific URL for the current crawl (ideally atomic with next step)\r\n- Remove URL from the list of URLs to fetch/process for the current crawl (ideally atomic with prev step)\r\n- When the list of URLs to fetch/process is empty, aggregate all info from the current crawl and store the aggregated data\r\n\r\nI like the \"unpacked information\" design that you suggested. I'm envisioning something like this:\r\n\r\n```json\r\n{\r\n  \"historical-data\": {\r\n    \"a-url.com\": [\r\n      {\r\n        \"timestamp\": \"a JS Date here\",\r\n        \"hasProbe1\": true,\r\n        \"hasProbe2\": false,\r\n        \"hasProbe3\": true\r\n      },\r\n\r\n      {\r\n        \"timestamp\": \"another JS Date\",\r\n        \"hasProbe1\": false,\r\n        \"hasProbe2\": false,\r\n        \"hasProbe3\": false\r\n      }\r\n    ],\r\n\r\n    \"another-url.com\": [\r\n      {\r\n        \"timestamp\": \"a JS Date here\",\r\n        \"hasProbe1\": false,\r\n        \"hasProbe2\": false,\r\n        \"hasProbe3\": true\r\n      },\r\n\r\n      {\r\n        \"timestamp\": \"another JS Date\",\r\n        \"hasProbe1\": false,\r\n        \"hasProbe2\": false,\r\n        \"hasProbe3\": true \r\n      }\r\n    ],\r\n\r\n    \"a-third-url.com\": [\r\n      {\r\n        \"timestamp\": \"a JS Date here\",\r\n        \"hasProbe1\": true,\r\n        \"hasProbe2\": true,\r\n        \"hasProbe3\": true\r\n      },\r\n\r\n      {\r\n        \"timestamp\": \"another JS Date\",\r\n        \"hasProbe1\": false,\r\n        \"hasProbe2\": false,\r\n        \"hasProbe3\": false\r\n      }\r\n    ],\r\n\r\n    \"a-fourth-url.com\": [\r\n      {\r\n        \"timestamp\": \"a JS Date here\",\r\n        \"hasProbe1\": true,\r\n        \"hasProbe2\": true,\r\n        \"hasProbe3\": false\r\n      },\r\n\r\n      {\r\n        \"timestamp\": \"another JS Date\",\r\n        \"hasProbe1\": false,\r\n        \"hasProbe2\": true,\r\n        \"hasProbe3\": false\r\n      }\r\n    ]\r\n  },\r\n  \"current-crawl-results\": [\r\n    {\r\n      \"a-url.com\": {\r\n        \"timestamp\": \"a JS Date here\",\r\n        \"hasProbe1\": true,\r\n        \"hasProbe2\": false,\r\n        \"hasProbe3\": true\r\n      },\r\n      \"another-url.com\": {\r\n        \"timestamp\": \"a JS Date here\",\r\n        \"hasProbe1\": false,\r\n        \"hasProbe2\": false,\r\n        \"hasProbe3\": true\r\n      }\r\n    }\r\n  ],\r\n  \"current-crawl-remaining-urls\": [\r\n      \"a-third-url.com\",\r\n      \"a-fourth-url.com\"\r\n  ],\r\n  \"aggregated-info\": [\r\n    {\r\n      \"date-aggregated\": \"a JS Date\",\r\n      \"totalSites\": 1000000,\r\n      \"haveProbe1\": 2351,\r\n      \"haveProbe2\": 71925,\r\n      \"haveProbe3\": 10234\r\n    },\r\n\r\n    {\r\n      \"date-aggregated\": \"a JS Date\",\r\n      \"totalSites\": 1000000,\r\n      \"haveProbe1\": 1234,\r\n      \"haveProbe2\": 60124,\r\n      \"haveProbe3\": 9123\r\n    }\r\n  ]\r\n}\r\n```\r\n\r\nThe crawler can spin through 'current-crawl-remaining-urls', processing each url and appending the result in 'current-crawl-results'. When 'current-crawl-remaining-urls' is empty, the crawler can aggregate the data in 'current-crawl-results', append the new aggregated data in 'aggregated-info', then it can empty 'current-crawl-results' into the appropriate URL-specific children of 'historical-data'.\r\n\r\nThis seems to exhibit the \"bunch of multiple redis commands to be run in one call\" issue that you mentioned, but only when the crawler is finished with a crawl and is putting the info from the last crawl into 'historical-data'. That should only happen a couple times per month I believe.\r\n\r\nFrom my limited knowledge of Redis and its data types, I'm not sure if this layout is possible. What do you think? Is it possible to create something like this? Does this design do a good enough job of incorporating your analysis from above?\r\n\r\nThanks again! "}}