Go to Hackademy website

Benchmark Activerecord - SQL dans une application Rails

Jonathan François

Posté par Jonathan François dans les catégories back

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.

Contexte

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.

Requête concernée

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

Les Datas

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.

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).

Place aux tests

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: Graph

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 :

Couches

Dans un premier temps, réalisons le même test. Graph

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. Graph

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.

Conclusion

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.

Articles connexes

Une brève histoire d'Elixir et Erlang/OTP

31/01/2019

Je développe depuis plusieurs années en Ruby. Depuis mon arrivée chez Synbioz, j’expérimente en plus avec Elixir de façon assez naturelle. En quoi Elixir est-il différent, me demanderez-vous ? Pour...

Écrire une thread pool en Ruby

10/01/2019

Pouvoir exécuter plusieurs tâches en parallèle, que ce soit dans un script ou une application, peut être vraiment très utile, surtout dans le cas où le traitement de ces tâches peut être très long....

Translation temporelle

31/05/2018

Cette semaine, je me suis essayé à un nouveau format d’article qui se présente sous la forme d’une nouvelle de Science-Fiction. Je tiens en passant à remercier Valentin pour ses illustrations....

Authentifier l'accès à vos ressources avec Dragonfly

11/05/2017

Pour ceux qui ne connaissent pas Dragonfly, c’est une application Rack qui peut être utilisée seule ou via un middleware. Le fait que ce soit une application Rack la rend compatible avec toutes les...