diff options
| -rw-r--r-- | sql/stats/2020-02-24_stats.txt | 482 | ||||
| -rw-r--r-- | sql/stats/README.md | 6 |
2 files changed, 488 insertions, 0 deletions
diff --git a/sql/stats/2020-02-24_stats.txt b/sql/stats/2020-02-24_stats.txt new file mode 100644 index 0000000..e7a00e8 --- /dev/null +++ b/sql/stats/2020-02-24_stats.txt @@ -0,0 +1,482 @@ + +## SQL Table Sizes + + SELECT + table_name, + pg_size_pretty(table_size) AS table_size, + pg_size_pretty(indexes_size) AS indexes_size, + pg_size_pretty(total_size) AS total_size + FROM ( + SELECT + table_name, + pg_table_size(table_name) AS table_size, + pg_indexes_size(table_name) AS indexes_size, + pg_total_relation_size(table_name) AS total_size + FROM ( + SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name + FROM information_schema.tables + WHERE table_schema = 'public' + ) AS all_tables + ORDER BY total_size DESC + ) AS pretty_sizes; + + + Size: 271.83G + + table_name | table_size | indexes_size | total_size + -------------------------------+------------+--------------+------------ + "public"."cdx" | 42 GB | 36 GB | 78 GB + "public"."grobid_shadow" | 61 GB | 6553 MB | 68 GB + "public"."grobid" | 47 GB | 7213 MB | 54 GB + "public"."file_meta" | 26 GB | 12 GB | 38 GB + "public"."shadow" | 8303 MB | 9216 MB | 17 GB + "public"."fatcat_file" | 5206 MB | 2094 MB | 7300 MB + "public"."ingest_file_result" | 1831 MB | 2454 MB | 4285 MB + "public"."ingest_request" | 2006 MB | 2122 MB | 4128 MB + "public"."petabox" | 403 MB | 594 MB | 997 MB + "public"."pdftrio" | 78 MB | 64 MB | 142 MB + (10 rows) + + +## File Metadata + +(skipping, no update) + + +## CDX + +Total and unique-by-sha1 counts: + + SELECT COUNT(DISTINCT sha1hex) as unique_sha1, COUNT(*) as total FROM cdx; + +Processed or not: + + # TODO: + +## GROBID + +Counts: + + SELECT COUNT(DISTINCT fatcat_release) AS unique_releases, COUNT(*) AS total FROM grobid; + + unique_releases | total + -----------------+---------- + 15,632,810 | 76,555,791 + (1 row) + +Status? + + SELECT status_code, COUNT(*) FROM grobid GROUP BY status_code ORDER BY COUNT DESC LIMIT 10; + + status_code | count + -------------+---------- + 200 | 70656028 + 500 | 5896836 + -4 | 2295 + 503 | 111 + (4 rows) + + What version used? + + SELECT grobid_version, COUNT(*) FROM grobid WHERE status_code = 200 GROUP BY grobid_version ORDER BY COUNT DESC LIMIT 10; + + grobid_version | count + ----------------+---------- + 0.5.5-fatcat | 56001631 + | 14654496 + (2 rows) + +## Petabox + +Counts: + + SELECT COUNT(DISTINCT sha1hex) as unique_sha1, COUNT(*) as total FROM petabox; + + unique_sha1 | total + -------------+--------- + 2,868,825 | 2,887,834 + (1 row) + +## Ingests + +Requests by source: + + SELECT ingest_type, link_source, COUNT(*) FROM ingest_request GROUP BY ingest_type, link_source ORDER BY COUNT DESC LIMIT 25; + + ingest_type | link_source | count + -------------+-------------+--------- + pdf | doi | 6591633 + pdf | pmc | 2030279 + pdf | arxiv | 630743 + pdf | unpaywall | 1400 + pdf | spn | 82 + pdf | pubmed | 2 + (6 rows) + + SELECT ingest_type, link_source, ingest_request_source, COUNT(*) FROM ingest_request GROUP BY ingest_type, link_source, ingest_request_source ORDER BY COUNT DESC LIMIT 25; + + ingest_type | link_source | ingest_request_source | count + -------------+-------------+-------------------------+--------- + pdf | doi | fatcat-ingest-container | 3515873 + pdf | doi | | 2943896 + pdf | pmc | fatcat-ingest-container | 2028825 + pdf | arxiv | | 629719 + pdf | doi | fatcat-changelog | 129932 + pdf | doi | fatcat-ingest | 1935 + pdf | pmc | | 1454 + pdf | unpaywall | unpaywall | 1400 + pdf | arxiv | fatcat-ingest | 998 + pdf | spn | | 64 + pdf | arxiv | fatcat-ingest-container | 26 + pdf | spn | savepapernow-web | 18 + pdf | pubmed | | 2 + pdf | doi | savepapernow-web | 1 + (14 rows) + + SELECT count(*) FROM ingest_request WHERE ingest_type = 'pdf' AND link_source = 'doi' AND ingest_request_source IS NULL; + UPDATE ingest_request SET ingest_request_source = 'fatcat-changelog' WHERE ingest_type = 'pdf' AND link_source = 'doi' AND ingest_request_source IS NULL; + => UPDATE 2943896 + + SELECT count(*) FROM ingest_request WHERE ingest_type = 'pdf' AND link_source = 'spn' AND ingest_request_source IS NULL; + UPDATE ingest_request SET ingest_request_source = 'savepapernow-web' WHERE ingest_type = 'pdf' AND link_source = 'spn' AND ingest_request_source IS NULL; + => UPDATE 64 + + SELECT count(*) FROM ingest_request WHERE ingest_type = 'pdf' AND link_source = 'arxiv' AND ingest_request_source IS NULL; + UPDATE ingest_request SET ingest_request_source = 'fatcat-ingest' WHERE ingest_type = 'pdf' AND link_source = 'arxiv' AND ingest_request_source IS NULL; + => UPDATE 629719 + + SELECT count(*) FROM ingest_request WHERE ingest_type = 'pdf' AND link_source = 'pmc' AND ingest_request_source IS NULL; + UPDATE ingest_request SET ingest_request_source = 'fatcat-ingest' WHERE ingest_type = 'pdf' AND link_source = 'pmc' AND ingest_request_source IS NULL; + => UPDATE 1454 + + SELECT count(*) FROM ingest_request WHERE link_source = 'pubmed'; + DELETE FROM ingest_request WHERE link_source = 'pubmed'; + => DELETE 2 + + SELECT ingest_type, link_source, COUNT(*) FROM ingest_request GROUP BY ingest_type, link_source ORDER BY COUNT DESC LIMIT 25; + + ingest_type | link_source | count + -------------+-------------+--------- + pdf | doi | 6591637 + pdf | pmc | 2030279 + pdf | arxiv | 630743 + pdf | unpaywall | 1400 + pdf | spn | 82 + (5 rows) + + SELECT ingest_type, link_source, ingest_request_source, COUNT(*) FROM ingest_request GROUP BY ingest_type, link_source, ingest_request_source ORDER BY COUNT DESC LIMIT 25; + + ingest_type | link_source | ingest_request_source | count + -------------+-------------+-------------------------+--------- + pdf | doi | fatcat-ingest-container | 3515873 + pdf | doi | fatcat-changelog | 3073828 + pdf | pmc | fatcat-ingest-container | 2028825 + pdf | arxiv | fatcat-ingest | 630717 + pdf | doi | fatcat-ingest | 1935 + pdf | pmc | fatcat-ingest | 1454 + pdf | unpaywall | unpaywall | 1400 + pdf | spn | savepapernow-web | 82 + pdf | arxiv | fatcat-ingest-container | 26 + pdf | doi | savepapernow-web | 1 + (10 rows) + +Uncrawled requests by source: + + # TODO: verify this? + SELECT ingest_request.ingest_type, ingest_request.link_source, COUNT(*) + FROM ingest_request + LEFT JOIN ingest_file_result + ON ingest_request.base_url = ingest_file_result.base_url + AND ingest_request.ingest_type = ingest_file_result.ingest_type + WHERE ingest_file_result.base_url IS NULL + GROUP BY ingest_request.ingest_type, ingest_request.link_source ORDER BY COUNT DESC LIMIT 25; + + none? + +Results by source: + + SELECT + ingest_request.ingest_type, + ingest_request.link_source, + COUNT(*) as attempts, + COUNT(CASE WHEN ingest_file_result.hit THEN 1 END) hits, + ROUND(1.0 * COUNT(CASE WHEN ingest_file_result.hit THEN 1 END) / COUNT(*), 3) as fraction + FROM ingest_request + LEFT JOIN ingest_file_result + ON ingest_request.base_url = ingest_file_result.base_url + AND ingest_request.ingest_type = ingest_file_result.ingest_type + AND ingest_file_result.ingest_type IS NOT NULL + GROUP BY ingest_request.ingest_type, ingest_request.link_source ORDER BY attempts DESC LIMIT 25; + + + ingest_type | link_source | attempts | hits | fraction + -------------+-------------+----------+---------+---------- + pdf | doi | 6591637 | 1622702 | 0.246 + pdf | pmc | 2030279 | 1241836 | 0.612 + pdf | arxiv | 630743 | 500620 | 0.794 + pdf | unpaywall | 1400 | 851 | 0.608 + pdf | spn | 82 | 62 | 0.756 + (5 rows) + +Ingest result by status: + + SELECT ingest_type, status, COUNT(*) FROM ingest_file_result GROUP BY ingest_type, status ORDER BY COUNT DESC LIMIT 50; + + ingest_type | status | count + -------------+-------------------------------------+--------- + pdf | success | 3366189 + pdf | no-pdf-link | 2902620 + pdf | no-capture | 1672025 + pdf | redirect-loop | 388844 + pdf | cdx-error | 272780 + pdf | terminal-bad-status | 171878 + pdf | spn-remote-error | 163843 + pdf | spn-error | 108070 + pdf | null-body | 66778 + pdf | link-loop | 43403 + pdf | skip-url-blocklist | 34705 + pdf | wrong-mimetype | 31343 + pdf | wayback-error | 13012 + pdf | spn2-cdx-lookup-failure | 6100 + pdf | gateway-timeout | 5633 + pdf | other-mimetype | 5114 + pdf | spn2-error:proxy-error | 538 + pdf | spn2-error:job-failed | 470 + pdf | petabox-error | 415 + pdf | spn2-error:browser-running-error | 136 + pdf | spn2-error | 127 + pdf | spn2-error:soft-time-limit-exceeded | 71 + pdf | bad-redirect | 39 + pdf | spn2-error:unknown | 30 + pdf | spn2-error:browsing-timeout | 25 + pdf | pending | 3 + pdf | invalid-host-resolution | 1 + (27 rows) + + +## Fatcat Files + +(skipping, no update) + +## Recent Success/Failure of Ingest by Domain + +NOTE: just finished a bunch of "backfill" ingest from OA-DOI crawl; only a +small fraction of this is from changelog. + + # "problem domains" and statuses + SELECT domain, status, COUNT((domain, status)) + FROM (SELECT status, updated, substring(terminal_url FROM '[^/]+://([^/]*)') AS domain FROM ingest_file_result) t1 + WHERE t1.domain != '' + AND t1.status != 'success' + AND t1.updated >= NOW() - '1 day'::INTERVAL + GROUP BY domain, status + ORDER BY COUNT DESC + LIMIT 10; + + domain | status | count + -------------------------+----------------+------- + linkinghub.elsevier.com | no-capture | 2579 + www.mdpi.com | wrong-mimetype | 1313 + onlinelibrary.wiley.com | no-pdf-link | 785 + americanarchivist.org | no-pdf-link | 756 + journals.sagepub.com | redirect-loop | 503 + link.springer.com | redirect-loop | 432 + iopscience.iop.org | no-capture | 392 + www.tandfonline.com | no-pdf-link | 389 + pubs.rsc.org | no-capture | 361 + www.persee.fr | no-capture | 344 + (10 rows) + + + # "what type of errors" + SELECT ingest_type, status, COUNT(*) + FROM ingest_file_result + WHERE updated >= NOW() - '1 day'::INTERVAL + GROUP BY ingest_type, status + ORDER BY COUNT DESC + LIMIT 25; + + ingest_type | status | count + -------------+-------------------------------------+------- + pdf | success | 40578 + pdf | cdx-error | 14982 + pdf | no-capture | 7747 + pdf | no-pdf-link | 7111 + pdf | redirect-loop | 3265 + pdf | wrong-mimetype | 1629 + pdf | spn2-cdx-lookup-failure | 657 + pdf | link-loop | 538 + pdf | null-body | 517 + pdf | terminal-bad-status | 400 + pdf | wayback-error | 79 + pdf | spn2-error:job-failed | 53 + pdf | gateway-timeout | 38 + pdf | spn2-error:soft-time-limit-exceeded | 7 + pdf | spn2-error | 6 + pdf | petabox-error | 5 + pdf | spn2-error:browsing-timeout | 4 + pdf | spn2-error:unknown | 2 + pdf | bad-redirect | 1 + pdf | pending | 1 + (20 rows) + + # "throughput per day for last N days" + SELECT ingest_type, + date(updated), + COUNT(*) as total, + COUNT(CASE status WHEN 'success' THEN 1 ELSE null END) as success + FROM ingest_file_result + WHERE updated >= NOW() - '1 month'::INTERVAL + GROUP BY ingest_type, date(updated) + ORDER BY date(updated) DESC; + + ingest_type | date | total | success + -------------+------------+---------+--------- + pdf | 2020-02-25 | 32660 | 14322 + pdf | 2020-02-24 | 44967 | 26263 + pdf | 2020-02-23 | 58795 | 18874 + pdf | 2020-02-22 | 844249 | 272606 + pdf | 2020-02-21 | 1287378 | 433487 + pdf | 2020-02-20 | 1455943 | 492408 + pdf | 2020-02-19 | 21453 | 7529 + pdf | 2020-02-18 | 5863 | 2926 + pdf | 2020-02-17 | 3737 | 970 + pdf | 2020-02-16 | 13779 | 4862 + pdf | 2020-02-15 | 1021020 | 623020 + pdf | 2020-02-14 | 1036036 | 632830 + pdf | 2020-02-13 | 13503 | 5824 + pdf | 2020-02-12 | 20078 | 11422 + pdf | 2020-02-11 | 13499 | 6781 + pdf | 2020-02-10 | 2275 | 961 + pdf | 2020-02-09 | 3231 | 1494 + pdf | 2020-02-08 | 8967 | 4400 + pdf | 2020-02-07 | 7022 | 2430 + pdf | 2020-02-06 | 1291 | 516 + pdf | 2020-02-05 | 8586 | 6596 + pdf | 2020-02-04 | 3681 | 3593 + pdf | 2020-02-03 | 284 | 284 + pdf | 2020-02-02 | 480 | 480 + pdf | 2020-02-01 | 489 | 336 + pdf | 2020-01-31 | 1187 | 1130 + pdf | 2020-01-30 | 1613 | 1288 + pdf | 2020-01-29 | 947 | 279 + pdf | 2020-01-28 | 667 | 323 + (29 rows) + +Top "no-capture" domains (will need to re-ingest using live tool): + + SELECT domain, status, COUNT((domain, status)) + FROM (SELECT status, updated, substring(terminal_url FROM '[^/]+://([^/]*)') AS domain FROM ingest_file_result) t1 + WHERE t1.domain != '' + AND t1.status = 'no-capture' + GROUP BY domain, status + ORDER BY COUNT DESC + LIMIT 25; + + domain | status | count + --------------------------+------------+-------- + linkinghub.elsevier.com | no-capture | 320065 + iopscience.iop.org | no-capture | 46858 + pubs.rsc.org | no-capture | 43331 + www.persee.fr | no-capture | 38971 + www.doiserbia.nb.rs | no-capture | 27112 + academic.oup.com | no-capture | 18877 + www.osapublishing.org | no-capture | 17113 + osf.io | no-capture | 16978 + scripts.iucr.org | no-capture | 14844 + www.degruyter.com | no-capture | 8093 + mab-online.nl | no-capture | 6603 + insights.ovid.com | no-capture | 6457 + ir.lib.uth.gr | no-capture | 3625 + www.sciencedirect.com | no-capture | 3244 + www.tandfonline.com | no-capture | 3201 + www.ccsenet.org | no-capture | 2849 + www.intechopen.com | no-capture | 2813 + primary-hospital-care.ch | no-capture | 2774 + www.nature.com | no-capture | 2484 + www.indianjournals.com | no-capture | 2432 + journals.aps.org | no-capture | 2197 + journals.sagepub.com | no-capture | 2064 + www.episodes.org | no-capture | 1805 + periodicos.uninove.br | no-capture | 1692 + escholarship.org | no-capture | 1666 + (25 rows) + +Top "no-pdf-link" domains: + + SELECT domain, status, COUNT((domain, status)) + FROM (SELECT status, updated, substring(terminal_url FROM '[^/]+://([^/]*)') AS domain FROM ingest_file_result) t1 + WHERE t1.domain != '' + AND t1.status = 'no-pdf-link' + GROUP BY domain, status + ORDER BY COUNT DESC + LIMIT 25; + + domain | status | count + -----------------------------+-------------+-------- + plutof.ut.ee | no-pdf-link | 685315 + www.gbif.org | no-pdf-link | 670647 + doi.pangaea.de | no-pdf-link | 301984 + www.plate-archive.org | no-pdf-link | 209218 + onlinelibrary.wiley.com | no-pdf-link | 84890 + figshare.com | no-pdf-link | 72892 + zenodo.org | no-pdf-link | 45768 + www.tandfonline.com | no-pdf-link | 43848 + data.mendeley.com | no-pdf-link | 42367 + springernature.figshare.com | no-pdf-link | 35941 + dhz.uni-passau.de | no-pdf-link | 29187 + www.frontiersin.org | no-pdf-link | 17925 + digital.ucd.ie | no-pdf-link | 16769 + mr.crossref.org | no-pdf-link | 14999 + journals.lww.com | no-pdf-link | 12122 + musewide.aip.de | no-pdf-link | 10854 + datadryad.org | no-pdf-link | 10686 + www.jstor.org | no-pdf-link | 9159 + koreascience.or.kr | no-pdf-link | 9067 + easy.dans.knaw.nl | no-pdf-link | 8264 + scielo.conicyt.cl | no-pdf-link | 8069 + www.degruyter.com | no-pdf-link | 7989 + www.kci.go.kr | no-pdf-link | 6990 + www.m-hikari.com | no-pdf-link | 6941 + cshprotocols.cshlp.org | no-pdf-link | 6553 + (25 rows) + +Top block-ish domains: + + SELECT domain, status, COUNT((domain, status)) + FROM (SELECT status, updated, substring(terminal_url FROM '[^/]+://([^/]*)') AS domain FROM ingest_file_result) t1 + WHERE t1.domain != '' + AND (t1.status = 'redirect-loop' OR t1.status = 'link-loop' OR t1.status = 'terminal-bad-status') + GROUP BY domain, status + ORDER BY COUNT DESC + LIMIT 25; + + domain | status | count + ---------------------------------+---------------------+------- + journals.openedition.org | redirect-loop | 30395 + ieeexplore.ieee.org | redirect-loop | 28926 + www.degruyter.com | redirect-loop | 18891 + www.cairn.info | link-loop | 8919 + www.frontiersin.org | terminal-bad-status | 6786 + projecteuclid.org | link-loop | 6098 + www.mdpi.com | terminal-bad-status | 5189 + medicalforum.ch | terminal-bad-status | 4596 + jrnl.nau.edu.ua | link-loop | 4238 + www.revistas.unam.mx | link-loop | 3926 + journals.aps.org | redirect-loop | 3696 + www.ijcseonline.org | redirect-loop | 3567 + www.researchsquare.com | terminal-bad-status | 3453 + www.persee.fr | terminal-bad-status | 3221 + www.baltistica.lt | link-loop | 2098 + osf.io | redirect-loop | 2004 + seer.ufrgs.br | terminal-bad-status | 2002 + jtd.amegroups.com | link-loop | 1738 + www.hindawi.com | terminal-bad-status | 1613 + linkinghub.elsevier.com | redirect-loop | 1612 + www.scienceopen.com | terminal-bad-status | 1580 + atm.amegroups.com | link-loop | 1571 + scielo.conicyt.cl | terminal-bad-status | 1491 + repozytorium.ur.edu.pl | redirect-loop | 1279 + agupubs.onlinelibrary.wiley.com | link-loop | 1182 + (25 rows) + diff --git a/sql/stats/README.md b/sql/stats/README.md index ea61fa0..52642f6 100644 --- a/sql/stats/README.md +++ b/sql/stats/README.md @@ -41,6 +41,10 @@ Total and unique-by-sha1 counts: SELECT COUNT(DISTINCT sha1hex) as unique_sha1, COUNT(*) as total FROM cdx; +mimetype counts: + + SELECT mimetype, COUNT(*) FROM cdx GROUP BY mimetype ORDER BY COUNT(*) DESC; + Processed or not: # TODO: @@ -71,6 +75,8 @@ Requests by source: SELECT ingest_type, link_source, COUNT(*) FROM ingest_request GROUP BY ingest_type, link_source ORDER BY COUNT DESC LIMIT 25; + SELECT ingest_type, link_source, ingest_request_source, COUNT(*) FROM ingest_request GROUP BY ingest_type, link_source, ingest_request_source ORDER BY COUNT DESC LIMIT 25; + Uncrawled requests by source: # TODO: verify this? |