Google Cloud SQL Timeseries Statistics

Question

I have a massive table that records events happening on our website. It has tens of millions of rows.

I've already tried adding indexing and other optimizations.

However, it's still very taxing on our server (even though we have quite a powerful one) and takes 20 seconds on some large graph/chart queries. So long in fact that our daemon intervenes to kill the queries often.

Currently we have a Google Compute instance on the frontend and a Google SQL instance on the backend.

So my question is this - is there some better way of storing an querying time series data using the Google Cloud?

I mean, do they have some specialist server or storage engine?

I need something I can connect to my php application.

Answer 1

Elasticsearch is awesome for time series data.

You can run it on compute engine, or they have a hosted version.

It is accessed via an HTTP JSON API, and there are several PHP clients (although I tend to make the API calls directly as i find it better to understand their query language that way).

https://www.elastic.co

They also have an automated graphing interface for time series data. It's called Kibana.

Enjoy!!

Answer 2

Update: I missed the important part of the question "using the Google Cloud?" My answer does not use any specialized GC services or infrastructure.

---

I have used ElasticSearch for storing events and profiling information from a web site. I even wrote a statsd backend storing stat information in elasticsearch.

After elasticsearch changed kibana from 3 to 4, I found the interface extremely bad for looking at stats. You can only chart 1 metric from each query, so if you want to chart time, average time, and avg time of 90% you must do 3 queries, instead of 1 that returns 3 values. (the same issue existing in 3, just version 4 looked more ugly and was more confusing to my users)

My recommendation is to choose a Time Series Database that is supported by graphana - a time series charting front end. OpenTSDB stores information in a hadoop-like format, so it will be able to scale out massively. Most of the others store events similar to row-based information.

For capturing statistics, you can either use statsd or reimann (or reimann and then statsd). Reimann can add alerting and monitoring before events are sent to your stats database, statsd merely collates, averages, and flushes stats to a DB.

• http://docs.grafana.org/
• https://github.com/markkimsal/statsd-elasticsearch-backend
• https://github.com/etsy/statsd
• http://riemann.io/