Analysing App Engine logs with BigQuery
I created webfiddle.net which lets you easily add your own CSS and JavaScript to the web and share the results.
Part of the product includes a proxy server which injects your code, webfiddle.net is currently going fairly viral (2M requests in the last 5 days) and costing too much!
To fix this lets do some optimization!
Enable sending logs to BigQuery by going monitoring->logs->”cog”->”stream logs to bigquery”
What good is that?
It turns out BigQuery makes it fairly easy to find slow parts of your application.
Find urls returning large responses:
SELECT SUM(protoPayload.cost) as cost, protoPayload.resource, COUNT(protoPayload.resource) as times_requested, SUM(FLOAT(SUBSTR(protoPayload.latency, 0, LENGTH(protoPayload.latency) - 1))) as total_latency, MAX(protoPayload.responseSize) as size
FROM [webfiddlelogs.appengine_googleapis_com_request_log_20150205]
GROUP BY protoPayload.resource
ORDER BY size DESC
LIMIT 10
Which urls are costing me the most to run?
SELECT SUM(protoPayload.cost) as cost, protoPayload.resource, COUNT(protoPayload.resource) as times_requested, SUM(FLOAT(SUBSTR(protoPayload.latency, 0, LENGTH(protoPayload.latency) - 1))) as total_latency, MAX(protoPayload.responseSize) as size
FROM [webfiddlelogs.appengine_googleapis_com_request_log_20150205]
GROUP BY protoPayload.resource
ORDER BY cost DESC
LIMIT 10
What erroring urls are costing me the most to run?
(I found the webfiddle.net could more quickly and correctly handle these erroring urls)
SELECT SUM(protoPayload.cost) as cost, protoPayload.line.severity, protoPayload.resource, COUNT(protoPayload.resource) as times_requested, SUM(FLOAT(SUBSTR(protoPayload.latency, 0, LENGTH(protoPayload.latency) - 1))) as total_latency, MAX(protoPayload.responseSize) as size
FROM [webfiddlelogs.appengine_googleapis_com_request_log_20150205]
GROUP BY protoPayload.resource, protoPayload.line.severity
HAVING protoPayload.line.severity = 'ERROR'
ORDER BY cost DESC
LIMIT 10
Note: i found latency interesting but it turns out its not as related to cost as response size is.
latency may mean your app is overloaded and requests are waiting before they get to your app.
webfiddle.net is open source if you want to see how it works.