3 ms·

Everything You Always Wanted to Know About GitHub (But Were Afraid to Ask)

48 points by zX41ZdbW 6y ago

hodgesrm 6y ago
This is cool! If you hit the pencil link on query results in the article, it pops you into a browser where you can edit and run Github queries yourself.
al_t111 6y ago
What hardware does it run on? Is it a single server or a cluster?
- zX41ZdbW 6y ago
  It is single cloud instance (Yandex Cloud, 80 vCPU Intel Cascade Lake, network storage).
  - siradjev 6y ago
    Below query says there are 40 threads, not 80, and ram for Clickhouse is limited by 20GB =) select * from system.settings where name IN ('max_memory_usage', 'max_threads'); -- 20GB limit, 40 CPU -- 40 threads, 20GB RAM limit
    - zX41ZdbW 6y ago
      Yes, 80 vCPU are logical cores, but ClickHouse is using the number of threads equal to the number of physical cores by default (40 threads). This is a reasonable default - when setting up the number of threads to full vCPU count, performance of single query will be slightly better but this will affect the max number of concurrent queries badly.
pachico 6y ago
This is absolutely brilliant. Great work!!!
kitaisreal 6y ago
LGTM https://github.com/torvalds/linux/pull/684 https://github.com/torvalds/linux/pull/684
- zX41ZdbW 6y ago
  Most of Linux kernel development happens on maillists and pull requests on GitHub are just for lulz.
orloffv 6y ago
Amazing! You write: "all the events in all GitHub repositories since 2011" I have a question: When was the data loaded? Will the data in https://gh-api.clickhouse.tech/play https://gh-api.clickhouse.tech/play be updated?
- zX41ZdbW 6y ago
  The downloadable dataset was created two days ago and the queries were run on this dataset. Data on https://gh-api.clickhouse.tech/play https://gh-api.clickhouse.tech/play (for interactive queries) is updated every hour as described in the article, but downloadable datasets in .xz are not updated.
zX41ZdbW 6y ago
"Pull panda" for your repository: https://gh-api.clickhouse.tech/play?user=play#c2VsZWN0IGNvdW50KCksIGFjdG9yX2xvZ2luIGZyb20gZ2l0aHViX2V2ZW50cyB3aGVyZSByZXBvX25hbWUgPSAnQ2xpY2tIb3VzZS9DbGlja0hvdXNlJyBhbmQgZXZlbnRfdHlwZSA9ICdQdWxsUmVxdWVzdEV2ZW50JyBhbmQgbWVyZ2VkID0gMSBhbmQgY3JlYXRlZF9hdCA+ICcyMDIwLTA2LTAxIDAwOjAwOjAwJyBhbmQgY3JlYXRvcl91c2VyX2xvZ2luICE9IGFjdG9yX2xvZ2luIGFuZCBub3QgaGFzKGxhYmVscywgJ3ByLWJhY2twb3J0JykgZ3JvdXAgYnkgYWN0b3JfbG9naW4gb3JkZXIgYnkgY291bnQoKikgZGVzYw== https://gh-api.clickhouse.tech/play?user=play#c2VsZWN0IGNvdW... (replace condition on repo_name to your favorite repo)
- iamazat 6y ago
  Something more complex - How much time it takes to merge non trivial PRs https://gh-api.clickhouse.tech/play?user=play#U0VMRUNUCiAgICBleHAyKGZsb29yKGxvZzIoZGlmZikpKSBBUyBkYXlzX3RvX2Nsb3NlX29yX21lcmdlLAogICAgY291bnQoKQpGUk9NCigKICAgIFdJVEgKICAgICAgICBtYXgoYWN0aW9uID0gJ2Nsb3NlZCcpIEFTIGNsb3NlZF8sCiAgICAgICAgbWF4KG1lcmdlYWJsZV9zdGF0ZSkgQVMgc3RhdGVfLAogICAgICAgIG1pbihjcmVhdGVkX2F0KSBBUyBzdGFydF8sCiAgICAgICAgbWF4KGNyZWF0ZWRfYXQpIEFTIGVuZF8KICAgIFNFTEVDVAogICAgICAgIG51bWJlciwKICAgICAgICBhcmdNYXgodGl0bGUsIGNyZWF0ZWRfYXQpIEFTIHRpdGxlXywKICAgICAgICBkYXRlRGlmZignZGF5Jywgc3RhcnRfLCBlbmRfKSBBUyBkaWZmLAogICAgICAgIGFueUxhc3RJZihsYWJlbHMsIE5PVCBlbXB0eShsYWJlbHMpKSBBUyBsYWJlbHNfLAogICAgICAgIGNvdW50SWYoZXZlbnRfdHlwZSAhPSAnUHVsbFJlcXVlc3RFdmVudCcpIEFTIGFjdGl2aXR5CiAgICBGUk9NIGdpdGh1Yl9ldmVudHMKICAgIFdIRVJFIChyZXBvX25hbWUgSU4gKCdDbGlja0hvdXNlL0NsaWNrSG91c2UnLCAneWFuZGV4L0NsaWNrSG91c2UnKSkgQU5EIChjcmVhdGVkX2F0ID4gKG5vdygpIC0gdG9JbnRlcnZhbFllYXIoMikpKSBBTkQgKGV2ZW50X3R5cGUgSU4gKCdQdWxsUmVxdWVzdEV2ZW50JywgJ0lzc3VlQ29tbWVudEV2ZW50JywgJ1B1bGxSZXF1ZXN0UmV2aWV3Q29tbWVudEV2ZW50JywgJ1B1bGxSZXF1ZXN0UmV2aWV3RXZlbnQnKSkgQU5EIChhdXRob3JfYXNzb2NpYXRpb24gTk9UIElOICgnT1dORVInLCAnTUVNQkVSJykpCiAgICBHUk9VUCBCWSBudW1iZXIKICAgIEhBVklORyBjbG9zZWRfIEFORCAoTk9UIGhhcyhhcnJheU1hcCh4IC0+ICgoeCBMSUtFICdwci0lJykgT1IgKHggPSAnZG9jdW1lbnRhdGlvbicpKSwgbGFiZWxzXyksIDEpKSBBTkQgKHRpdGxlXyBOT1QgTElLRSAnUmVsZWFzZSBwdWxsIHJlcXVlc3QgJScpIEFORCAodGl0bGVfIE5PVCBMSUtFICdET0NTVVAtJScpIEFORCAodGl0bGVfIE5PVCBJTElLRSAnJVdJUCUnKSBBTkQgKHN0YXRlXyAhPSAnZHJhZnQnKSBBTkQgKGFjdGl2aXR5ID4gNSkKICAgIE9SREVSIEJZIGRpZmYgQVNDCikKR1JPVVAgQlkgZGF5c190b19jbG9zZV9vcl9tZXJnZQpPUkRFUiBCWSBkYXlzX3RvX2Nsb3NlX29yX21lcmdlIEFTQwo= https://gh-api.clickhouse.tech/play?user=play#U0VMRUNUCiAgIC...
iamazat 6y ago
There was [presentation](https://www.igvita.com/slides/2012/bigquery-github-strata.pdf https://www.igvita.com/slides/2012/bigquery-github-strata.pd...) from the creator of gharchive.org and github employee (I guess) about open source metrics, based on github data Here is an example using playground from this paper to calculate pull pull request latency (i.e. how much time user need after fork to submit a PR) - https://tinyurl.com/github-popular-repo-pr-latency https://tinyurl.com/github-popular-repo-pr-latency (I wouldn't post here full query, since it is not that compact, but it is not complex) It does not match with 2012: >80%+ pull requests come in within 1 day of the fork But new query calculates statistics only for popular repos.

48 points by zX41ZdbW 6y ago

This is cool! If you hit the pencil link on query results in the article, it pops you into a browser where you can edit and run Github queries yourself.

al_t111 6y ago

What hardware does it run on? Is it a single server or a cluster?

zX41ZdbW 6y ago
It is single cloud instance (Yandex Cloud, 80 vCPU Intel Cascade Lake, network storage).
- siradjev 6y ago
  Below query says there are 40 threads, not 80, and ram for Clickhouse is limited by 20GB =) select * from system.settings where name IN ('max_memory_usage', 'max_threads'); -- 20GB limit, 40 CPU -- 40 threads, 20GB RAM limit
  - zX41ZdbW 6y ago
    Yes, 80 vCPU are logical cores, but ClickHouse is using the number of threads equal to the number of physical cores by default (40 threads). This is a reasonable default - when setting up the number of threads to full vCPU count, performance of single query will be slightly better but this will affect the max number of concurrent queries badly.

pachico 6y ago

This is absolutely brilliant. Great work!!!

kitaisreal 6y ago

LGTM https://github.com/torvalds/linux/pull/684 https://github.com/torvalds/linux/pull/684

zX41ZdbW 6y ago
Most of Linux kernel development happens on maillists and pull requests on GitHub are just for lulz.

orloffv 6y ago

Amazing! You write: "all the events in all GitHub repositories since 2011" I have a question: When was the data loaded? Will the data in https://gh-api.clickhouse.tech/play https://gh-api.clickhouse.tech/play be updated?

zX41ZdbW 6y ago
The downloadable dataset was created two days ago and the queries were run on this dataset. Data on https://gh-api.clickhouse.tech/play https://gh-api.clickhouse.tech/play (for interactive queries) is updated every hour as described in the article, but downloadable datasets in .xz are not updated.

zX41ZdbW 6y ago

"Pull panda" for your repository: https://gh-api.clickhouse.tech/play?user=play#c2VsZWN0IGNvdW50KCksIGFjdG9yX2xvZ2luIGZyb20gZ2l0aHViX2V2ZW50cyB3aGVyZSByZXBvX25hbWUgPSAnQ2xpY2tIb3VzZS9DbGlja0hvdXNlJyBhbmQgZXZlbnRfdHlwZSA9ICdQdWxsUmVxdWVzdEV2ZW50JyBhbmQgbWVyZ2VkID0gMSBhbmQgY3JlYXRlZF9hdCA+ICcyMDIwLTA2LTAxIDAwOjAwOjAwJyBhbmQgY3JlYXRvcl91c2VyX2xvZ2luICE9IGFjdG9yX2xvZ2luIGFuZCBub3QgaGFzKGxhYmVscywgJ3ByLWJhY2twb3J0JykgZ3JvdXAgYnkgYWN0b3JfbG9naW4gb3JkZXIgYnkgY291bnQoKikgZGVzYw== https://gh-api.clickhouse.tech/play?user=play#c2VsZWN0IGNvdW... (replace condition on repo_name to your favorite repo)

iamazat 6y ago
Something more complex - How much time it takes to merge non trivial PRs https://gh-api.clickhouse.tech/play?user=play#U0VMRUNUCiAgICBleHAyKGZsb29yKGxvZzIoZGlmZikpKSBBUyBkYXlzX3RvX2Nsb3NlX29yX21lcmdlLAogICAgY291bnQoKQpGUk9NCigKICAgIFdJVEgKICAgICAgICBtYXgoYWN0aW9uID0gJ2Nsb3NlZCcpIEFTIGNsb3NlZF8sCiAgICAgICAgbWF4KG1lcmdlYWJsZV9zdGF0ZSkgQVMgc3RhdGVfLAogICAgICAgIG1pbihjcmVhdGVkX2F0KSBBUyBzdGFydF8sCiAgICAgICAgbWF4KGNyZWF0ZWRfYXQpIEFTIGVuZF8KICAgIFNFTEVDVAogICAgICAgIG51bWJlciwKICAgICAgICBhcmdNYXgodGl0bGUsIGNyZWF0ZWRfYXQpIEFTIHRpdGxlXywKICAgICAgICBkYXRlRGlmZignZGF5Jywgc3RhcnRfLCBlbmRfKSBBUyBkaWZmLAogICAgICAgIGFueUxhc3RJZihsYWJlbHMsIE5PVCBlbXB0eShsYWJlbHMpKSBBUyBsYWJlbHNfLAogICAgICAgIGNvdW50SWYoZXZlbnRfdHlwZSAhPSAnUHVsbFJlcXVlc3RFdmVudCcpIEFTIGFjdGl2aXR5CiAgICBGUk9NIGdpdGh1Yl9ldmVudHMKICAgIFdIRVJFIChyZXBvX25hbWUgSU4gKCdDbGlja0hvdXNlL0NsaWNrSG91c2UnLCAneWFuZGV4L0NsaWNrSG91c2UnKSkgQU5EIChjcmVhdGVkX2F0ID4gKG5vdygpIC0gdG9JbnRlcnZhbFllYXIoMikpKSBBTkQgKGV2ZW50X3R5cGUgSU4gKCdQdWxsUmVxdWVzdEV2ZW50JywgJ0lzc3VlQ29tbWVudEV2ZW50JywgJ1B1bGxSZXF1ZXN0UmV2aWV3Q29tbWVudEV2ZW50JywgJ1B1bGxSZXF1ZXN0UmV2aWV3RXZlbnQnKSkgQU5EIChhdXRob3JfYXNzb2NpYXRpb24gTk9UIElOICgnT1dORVInLCAnTUVNQkVSJykpCiAgICBHUk9VUCBCWSBudW1iZXIKICAgIEhBVklORyBjbG9zZWRfIEFORCAoTk9UIGhhcyhhcnJheU1hcCh4IC0+ICgoeCBMSUtFICdwci0lJykgT1IgKHggPSAnZG9jdW1lbnRhdGlvbicpKSwgbGFiZWxzXyksIDEpKSBBTkQgKHRpdGxlXyBOT1QgTElLRSAnUmVsZWFzZSBwdWxsIHJlcXVlc3QgJScpIEFORCAodGl0bGVfIE5PVCBMSUtFICdET0NTVVAtJScpIEFORCAodGl0bGVfIE5PVCBJTElLRSAnJVdJUCUnKSBBTkQgKHN0YXRlXyAhPSAnZHJhZnQnKSBBTkQgKGFjdGl2aXR5ID4gNSkKICAgIE9SREVSIEJZIGRpZmYgQVNDCikKR1JPVVAgQlkgZGF5c190b19jbG9zZV9vcl9tZXJnZQpPUkRFUiBCWSBkYXlzX3RvX2Nsb3NlX29yX21lcmdlIEFTQwo= https://gh-api.clickhouse.tech/play?user=play#U0VMRUNUCiAgIC...

iamazat 6y ago

There was [presentation](https://www.igvita.com/slides/2012/bigquery-github-strata.pdf https://www.igvita.com/slides/2012/bigquery-github-strata.pd...) from the creator of gharchive.org and github employee (I guess) about open source metrics, based on github data Here is an example using playground from this paper to calculate pull pull request latency (i.e. how much time user need after fork to submit a PR) - https://tinyurl.com/github-popular-repo-pr-latency https://tinyurl.com/github-popular-repo-pr-latency (I wouldn't post here full query, since it is not that compact, but it is not complex) It does not match with 2012: >80%+ pull requests come in within 1 day of the fork But new query calculates statistics only for popular repos.