CODEWITHSUNDEEP
ruby-on-railsdatabaseperformancecaching
Joe Czucha
Joe Czucha

Reputation: 4295

Optimising a large number of rows in a Rails app database

We have a number of Meters which read a number of Parameters at a given interval and uploads the data (in CSV format) to a MYSQL database.

I have modelled this in Rails as follows:

Meter
  has_many :parameters

Parameter
  belongs_to :meter
  has_many :readings

Reading
  belongs_to :parameter

(I've used normal foreign keys - meter_id and parameter_id - to link the tables)

This is working great with my seed data and I'm using self.readings.pluck(:value).latest in my Parameter model in order to grab the latest value and pass it to the view.

The only problem is that the meters upload the data every 30 seconds. This means that - as there are currently 20 parameters - just over a months worth of data has left me with over 20,000,000 rows in my Readings table and this means that the queries to grab the latest are taking around 500ms each.

I'm after suggestions of ways to optimise this. I've added an index to the parameter_id field but, other than that, I'm not really sure of the best way to proceed...

It may be that I need to rethink the way that my database is structured, but this seemed to make most sense as I want to be able to dynamically add new parameters down the line (hence why I couldn't just make my columns the parameter names) and this seems to be the way that Rails stores data by default.

Thanks in advance.

Upvotes: 0

Views: 41

Answers (1)

Paulo Fidalgo
Paulo Fidalgo

Reputation: 22296

If you are using Rails 3 and want to keep using a relational database your best option is to use table partitioning.

If you use PostgreSQL you can use the partitioned gem and check this slides to get an overview.

If you want to use Rails 4, since the partitioned gem is not compatible with ActiveRecord 4, I would advise you to use manual partitioning, you can use the year as your partition point, for example.

Check this blog post on Sharding and Partitioning and evaluate what should work best.

Upvotes: 1

Related Questions