All of us want to visit a fast loading website. No one wants to wait for a slower website to load.
In the effort to speed up our website that is running Ruby on Rails, we decide to use the first-party analytics for Rails: ahoy_matey gem.
This article shows how to speed up showing the visitor/event counts of ahoy gem from 13 sec to 0.01 sec, more than 1,000 times faster!
Why we choose ahoy gem
- It claims to be first-party analytics for Rails.
- We'll have the server's side control. Any third-party analytics won't let us have this.
- It supports Rails 6 and its webpacker.
- It allows us to specify which pages on our Rails website to track.
- And the last one is it works with AMP.
However, the journey with ahoy_matey isn't a smooth one. Contrary to our belief, we notice the pages with ahoy_matey are much slower than those without it.
Something is wrong there.
Investigation
We start our journey to investigate this problem. We begin with a page that's showing a list of articles. Every article there has a relation to ahoy_matey tables. At this point, we haven't realized the ahoy_matey is the cause.
We only notice such a page used to load under one second. Now, we see this:
Completed 200 OK in 13484ms
(Views: 427.5ms | ActiveRecord: 13016.8ms)
It loads in 13 seconds...very slow!
After that, we trace the SQL logs before that line. We notice many SQL statements like this:
Ahoy::Event Load (652.6ms) SELECT `ahoy_events`.* FROM `ahoy_events` WHERE `ahoy_events`.`name` = 'ViewEvent' AND (JSON_UNQUOTE(properties -> '$.id') = 123)
Each article on that page has an additional half seconds to load. There are more than 20 articles there. At this point, it begins to make sense that the ahoy Event query is the main suspect in this case.
Yet, it's too soon to declare it as the culprit. That's why we make a small experiment to get the undisputed proof.
Experiment
Here are what we do.
- Copy the database and make a back up of it
- Try the query directly on MySQL console. Play with it a little bit. But, there is no significant improvement here.
- Add an index to the properties column of ahoy_events table.
We get another challenge for the last one. The properties column is the json type column. That means we need to index it using an additional column.
The folks call such a column as a virtual column.
How do we apply it to this case?
Let's take a look at this SQL query once again.
Ahoy::Event Load (652.6ms) SELECT `ahoy_events`.* FROM `ahoy_events` WHERE `ahoy_events`.`name` = 'ViewEvent' AND (JSON_UNQUOTE(properties -> '$.id') = 123)
Notice that properties-> '$.id'. The query is using the ID attribute of that properties JSON data. The "properties" JSON data is likely something like this:
{"id": 123, "action": "show", "locale": "en", "controller": "pages"}
We need to index that "id" JSON attribute.
We achieve that using those two SQL queries:
ALTER TABLE ahoy_events ADD properties_id INT AS (JSON_UNQUOTE(properties->"$.id")) STORED;
ALTER TABLE ahoy_events ADD INDEX (properties_id);
The first query adds a new column on ahoy_events table. We call it properties_id and define it as an integer. Then, we specify it as the STORED generated column.
One of the reasons is we need to index this properties_id column. Not only that, but we also need to keep it up to date when Rails modifies the table.
The second query adds the index to that properties_id column.
As we're still experimenting at this point, we execute those two SQL queries directly on the MySQL console. That's why we made a copy and back up the database first.
The result of our experiment is stunning. The page that loads 13 seconds earlier now loads almost instantly. We are confident it's loading under one second now.
Finishing
Yet, we haven't finished our work. We need to run those two SQL queries in a Rails way. Therefore, we add another database migrations like this:
class AddVirtualColumnToIndexAhoyEventsProperties < ActiveRecord::Migration[6.0]
def change
ActiveRecord::Base.connection.execute (
"ALTER TABLE ahoy_events ADD properties_id INT AS (JSON_UNQUOTE(properties->"$.id")) STORED;"
)
ActiveRecord::Base.connection.execute (
"ALTER TABLE ahoy_events ADD INDEX (properties_id);"
)
end
end
Or here, on the Github gist
Final Conclusions
- In this case, the indexing of the JSON column solves the problem.
- We confirm this way with the gem's developer: https://github.com/ankane/ahoy/issues/455.
- So far, we don't notice any side effects. We covered our project with integration tests. All the tests relating ahoy_matey and the rest of them passed!
- In the end, we end up with the following ahoy_events table, just 0.01 sec as expected!