aboutsummaryrefslogtreecommitdiffstats
path: root/sql
diff options
context:
space:
mode:
authorBryan Newbold <bnewbold@archive.org>2020-03-30 16:34:54 -0700
committerBryan Newbold <bnewbold@archive.org>2020-03-30 16:34:54 -0700
commit35ebee00c38436ea8c8a075689d987d80841255a (patch)
tree5e2da78ad4726d4457be93548804d710eccf396d /sql
parent7a9994cc63e7816159b91a2f20dca64203268e4b (diff)
downloadsandcrawler-35ebee00c38436ea8c8a075689d987d80841255a.tar.gz
sandcrawler-35ebee00c38436ea8c8a075689d987d80841255a.zip
more monitoring queries
Diffstat (limited to 'sql')
-rw-r--r--sql/monitoring_queries.md34
1 files changed, 29 insertions, 5 deletions
diff --git a/sql/monitoring_queries.md b/sql/monitoring_queries.md
index 35eef8f..fd0ce51 100644
--- a/sql/monitoring_queries.md
+++ b/sql/monitoring_queries.md
@@ -27,7 +27,9 @@ Broken domains, past 3 days:
LEFT JOIN ingest_request
ON ingest_file_result.ingest_type = ingest_request.ingest_type
AND ingest_file_result.base_url = ingest_request.base_url
- WHERE ingest_request.created >= NOW() - '3 day'::INTERVAL
+ WHERE
+ -- ingest_request.created >= NOW() - '3 day'::INTERVAL
+ ingest_file_result.updated >= NOW() - '3 day'::INTERVAL
AND ingest_request.ingest_type = 'pdf'
AND ingest_request.ingest_request_source = 'fatcat-changelog'
) t1
@@ -50,8 +52,8 @@ Throughput per day, and success, for past month:
WHERE ingest_request.created >= NOW() - '1 month'::INTERVAL
AND ingest_request.ingest_type = 'pdf'
AND ingest_request.ingest_request_source = 'fatcat-changelog'
- GROUP BY ingest_request.ingest_type, ingest_file_result.ingest_type, date(ingest_file_result.updated)
- ORDER BY date(ingest_file_result.updated) DESC;
+ GROUP BY ingest_request.ingest_type, ingest_file_result.ingest_type, date(ingest_request.created)
+ ORDER BY date(ingest_request.created) DESC;
## fatcat-ingest
@@ -67,7 +69,9 @@ Broken domains, past 7 days:
LEFT JOIN ingest_request
ON ingest_file_result.ingest_type = ingest_request.ingest_type
AND ingest_file_result.base_url = ingest_request.base_url
- WHERE ingest_request.created >= NOW() - '7 day'::INTERVAL
+ WHERE
+ -- ingest_request.created >= NOW() - '7 day'::INTERVAL
+ ingest_file_result.updated >= NOW() - '24 hour'::INTERVAL
AND ingest_request.ingest_type = 'pdf'
AND ingest_request.ingest_request_source = 'fatcat-ingest'
) t1
@@ -87,8 +91,28 @@ Throughput per day, and success, for past 7 days:
LEFT JOIN ingest_request
ON ingest_file_result.ingest_type = ingest_request.ingest_type
AND ingest_file_result.base_url = ingest_request.base_url
- WHERE ingest_request.created >= NOW() - '7 day'::INTERVAL
+ WHERE
+ -- ingest_request.created >= NOW() - '7 day'::INTERVAL
+ ingest_file_result.updated >= NOW() - '24 hour'::INTERVAL
AND ingest_request.ingest_type = 'pdf'
AND ingest_request.ingest_request_source = 'fatcat-ingest'
GROUP BY ingest_request.ingest_type, ingest_file_result.ingest_type, date(ingest_file_result.updated)
ORDER BY date(ingest_file_result.updated) DESC;
+
+Overall status, updated requests past 3 days:
+
+ SELECT ingest_request.ingest_type,
+ ingest_file_result.status,
+ COUNT(*)
+ FROM ingest_file_result
+ LEFT JOIN ingest_request
+ ON ingest_file_result.ingest_type = ingest_request.ingest_type
+ AND ingest_file_result.base_url = ingest_request.base_url
+ WHERE
+ -- ingest_file_result.updated >= NOW() - '3 day'::INTERVAL
+ ingest_file_result.updated >= NOW() - '48 hour'::INTERVAL
+ AND ingest_request.ingest_type = 'pdf'
+ AND ingest_request.ingest_request_source = 'fatcat-ingest'
+ GROUP BY ingest_request.ingest_type, ingest_file_result.status
+ ORDER BY COUNT(*) DESC;
+