Bonjour à vous, aujourd’hui nous allons nous intéresser aux différents choix que nous faisons lors de nos développements rails pour réaliser nos requêtes en base.
Le framework rails et ActiveRecord nous permet de simplifier en écriture nos requêtes, le rendre plus maintenable et lisible. Toutefois, s’il est utilisé naïvement dans les grandes requêtes, il peut générer du SQL totalement inefficace. Selon les données à traiter il peut également ralentir notre application.
On se rendra compte au cours de cet article qu’il est parfois préférable selon les situations de prendre le temps de construire ses requêtes SQL à la main.
Pour identifier et comprendre le phénomène, nous allons réaliser des benchmarks en fonction du type de requête et du nombre de données à traiter pour une requête précise mais largement utilisée.
Les tests de cet article sont réalisés sur un Mac mini 2,6 Ghz i7, 8Go DDR3. Afin de réaliser les tests, j’ai choisi d’utiliser une association assez simple, l’association 1-N.
class User < ActiveRecord::Base
has_many :ratings
has_many :posts
end
class Post < ActiveRecord::Base
belongs_to :user
has_many :ratings
end
class Rating < ActiveRecord::Base
belongs_to :post
belongs_to :user
end
Ceci n’est qu’un exemple, les articles peuvent devenir des voitures, des photos ou même des lapins de patagonie… enfin vous l’avez compris, cette relation se retrouvera dans toutes vos applications.
Nous allons nous intéresser à trier nos articles dans l’ordre de leur moyenne de votes et pour cela créer deux scopes, l’un en ActiveRecord pur et l’autre en SQL :
class Post < ActiveRecord::Base
belongs_to :user
has_many :ratings
#ActiveRecord
scope :order_by_rating_ac, -> { includes(:ratings).sort_by(&:avg_rating) }
#SQL
scope :order_by_rating_sql, -> { find_by_sql("
SELECT posts.*, AVG(ratings.score) AS average FROM posts
LEFT OUTER JOIN ratings ON posts.id = ratings.post_id
GROUP BY posts.id, posts.user_id, posts.created_at, posts.updated_at
ORDER BY average DESC NULLS LAST, posts.created_at DESC") }
def avg_rating
ratings.average(:score) || 0
end
end
Afin de réaliser nos benchmarks nous devons peupler notre base de données. D’ailleurs il faut qu’à chaque tour (de la boucle “by step”), la base de donnée soit vidée et repeuplée avec la bonne quantité de donnée. Afin de permettre un temps d’exécution rapide, nous codons notre requête en SQL pur :
def populate_db(max_user, post_by_user)
# Suprimons les données
User.delete_all
Post.delete_all
Rating.delete_all
#Mise en place des données pour le prochain benchmark
sql_user = "insert into users
select t.id as id, now() as created_at, now() as updated_at, ('user' || (t.id::text)) as name
from (select * from generate_series(1,#{max_user}) as id)
as t;"
ActiveRecord::Base.connection.execute(sql_user)
sql_post = "insert into posts (user_id, created_at, updated_at)
select t.user_id as user_id,
now() as created_at,
now() as updated_at
from (select users.id as user_id from users, (select * from generate_series(1,#{post_by_user}) as id) as u) as t;"
ActiveRecord::Base.connection.execute(sql_post)
sql_rating = "insert into ratings (score, post_id, created_at, updated_at)
select (random() * 5)::integer as score, t.post_id as post_id, now() as created_at, now() as updated_at
from (select posts.id as post_id from posts, (select * from generate_series(1,3) as id) as p) as t;"
ActiveRecord::Base.connection.execute(sql_rating)
end
Les paramètres max_user et post_by_user permettent respectivement de définir un nombre d’utilisateur et leur nombre d’articles associés (par défaut il y aura 3 votes par article). Cette méthode sera appelée à chaque tour afin de réinitialiser notre base avant chaque benchmark.
Dans mon cas je fais tourner le benchmark dans un worker sidekiq et enregistre les données en base afin de générer les graphiques qui vont suivre, mais vous pouvez l’utilisez autrement (en console ou autre). Afin d’avoir une vision de l’ensemble du comportement, utilisons une boucle “by step” pour contrôler la précision du résultat obtenu (mais également le temps d’exécution…) en fonction du nombre de données traiter par la requête.
class BenchWorker
include Sidekiq::Worker
include Benchmark
require 'benchmark'
sidekiq_options :retry => false
def perform(max_user, post_by_user, step)
# Calcul max_count_post
max_count_post = max_user * post_by_user
@labels = []
@real_ac = []
@real_sql = []
(1..max_count_post).step(step).each do |u|
# Data for the next benchmark
populate_db(max_user, (u / max_user) )
#Benchmark
@bench = Benchmark.bmbm do |x|
tf = x.report("SQL:") { Post.order_by_rating_sql }
tt = x.report("Activerecord:") { Post.order_by_rating_ac.to_a }
end
@labels << u
@real_ac << @bench.last.real * 1000 # convert en miliseconds
@real_sql << @bench.first.real * 1000 # convert en miliseconds
end
#Save for the graph
Benchresult.create(step: step, range_data_min: @labels.first, range_data_max: @labels.last, label: @labels.join(','), real_ac: @real_ac.join(','), real_sql: @real_sql.join(','))
end
end
Nous lançons ensuite notre worker avec les paramètres voulus, ex: BenchWorker.perform_async(1000, 10, 1000).
Commençons par générer un test avec un faible nombre d’articles pour visualiser le résultat et éviter d’attendre 30 minutes d’exécution pour un résultat sans intérêt. Prenons max_user= 1000, post_by_user=10 et step= 1000. Soit un benchmark tous les 1000 articles (avec nouvelle base de données à chaque fois).
Pendant le test, vous pouvez consulter les logs de l’application ou de Sidekiq pour vérifier que tout se passe bien.
Voici le graph que nous obtenons:
En bleu: SQL En rouge: ActiveRecord Axe des abscisses: Nombre d’articles en base de données Axe des ordonnées: Temps d’éxécution (milliseconds - Elapsed real time)
Nous sommes content d’avoir limité le nombre d’articles maximum durant ce test car sinon nous aurions pu attendre lonnnnngtemps. En effet, la courbe d’activerecord monte au dessous de celle d’SQL à partir de 1000 articles traités dans la requête. Le temps d’exécution de la requête d’activerecord monte en flèche : 5.5 sec pour 9000 articles contre moins d’une seconde pour la requête SQL.
Il est vrai que l’on peut améliorer notre requête ActiveRecord, et d’ailleurs cela est nécessaire si on veut aller plus loin dans nos tests.
class Post < ActiveRecord::Base
...
scope :order_by_rating_ac_improve, -> { joins('LEFT OUTER JOIN ratings ON posts.id = ratings.post_id').select("posts.*, AVG(ratings.score) as average").group('posts.id, posts.user_id, posts.created_at, posts.updated_at').order('average DESC NULLS LAST, posts.created_at DESC') }
...
Attention, cette requête n’est pas identique à notre requête SQL car elle n’instancie pas les objets mais effectue juste une requête en base sans prendre les objets. Nous allons donc ajouter un “to_a” à notre code benchmarké ActiveRecord. Cette version améliorée correspond à l’utilisation d’Arel, ce schéma vous permet de visualiser les différentes couches :
Dans un premier temps, réalisons le même test.
Ici les résultats des deux requêtes sont similaires jusqu’à 9000 articles.
Contrairement à la première requête en “Full AR” ou le tri était effectué en ruby, ici le tri se réalise en sql pour les deux. Essayons d’aller plus loin avec 2 000 000 articles et 6 000 000 de votes.
Les petites variations des courbes sont dûes à l’utilisation de la machine hôte (sublime text + navigateur) pendant les tests. Ce graphique nous montre que l’utilisation d’arel est une bonne alternative à celle du SQL pur, surtout qu’il est plus facile à lire et maintenir. Vous pouvez retrouver notre présentation d’arel ici. Dès que l’on sait que notre requête va traiter souvent un grand nombre de données, il peut être judicieux de réaliser ce genre de test pour connaître les limites.
Cette étude succincte nous permet d’avoir un ordre idée mais également une façon de faire pour ajuster nos requêtes en base suivant les situations et les données à traiter ; cela dépend également de la requête en question. L’utilisation d’ActiveRecord et d’un système de cache permet parfois de palier aux ralentissements de l’application ou de la tâche en question, mais à partir d’un certain nombre de données à traiter, il sera conseillé de passer sur du SQL pur.
Pour aller plus loin, à la suite de cette article, je vais certainement développer une application permettant de venir tester ces requêtes en fonction de différentes bases de données( postgres, mysql, sqlite etc…) et des quantités de données à traiter. Je vous en parlerai dans un prochain article.
L’équipe Synbioz.
Libres d’être ensemble.
