BigW Consortium Gitlab

Skip to content

G
gitlab-ce
Switch branch/tag
  1. 03 Nov, 2015 1 commit
  3. 02 Nov, 2015 4 commits
  5. 01 Nov, 2015 2 commits
  7. 31 Oct, 2015 4 commits
  9. 30 Oct, 2015 10 commits
    • Merge branch 'gitlab-workhorse' into 'master' · 73353959
      Dmitriy Zaporozhets authored 
      Switch to gitlab-workhorse

This is a little annoying but it is better to change this name then
to be stuck with a bad name for a long time. Reasons for the name
change: https://gitlab.com/gitlab-org/gitlab-git-http-server/issues/13

See merge request !1707
    • Merge branch 'optimize-user-find-by-any-email' into 'master' · a0ba6c6c
      Yorick Peterse authored 
      Improve performance of User.find_by_any_email



See merge request !1698
    • Fix deadlink in docs for ci/examples · 31ea88cf
      Takuya Noguchi authored
    • Merge branch 'minor-ui-fixes' into 'master' · 49a73b6e
      Dmitriy Zaporozhets authored 
      Minor ui fixes



See merge request !1704
    • Adjusted ips/sec for find_by_any_email benchmarks · 6d3068be
      Yorick Peterse authored 
      While these benchmarks run at roughly 1500 i/sec setting the threshold
to 1000 leaves some room for deviations (e.g. due to different DB
setups).
    • Use a subquery with IDs only for find_by_any_email · a9df7147
      Yorick Peterse authored 
      This further improves performance of User.find_by_any_email and is
roughly twice as fast as the previous UNION setup.

Thanks again to @dlemstra for suggesting this.
    • Fixed UNION syntax for MySQL · bba46623
      Yorick Peterse authored 
      MySQL doesn't support the previous syntax.
    • Use a UNION for User.find_by_any_email · 24c8f422
      Yorick Peterse authored 
      This is significantly faster than using a sub-query, at least when run
on the GitLab.com production database. The benchmarks are a lot slower
now with these changes, most likely due to PostgreSQL choosing a
different (and less efficient) plan based on the amount of data present
in the test database.

Thanks to @dlemstra for suggesting the use of a UNION.
    • Changelog entry for User.find_by_any_email · 0a6aaf25
      Yorick Peterse authored
    • Improve performance of User.find_by_any_email · 49c081b9
      Yorick Peterse authored 
      This query used to rely on a JOIN, effectively producing the following
SQL:

    SELECT users.*
    FROM users
    LEFT OUTER JOIN emails ON emails.user_id = users.id
    WHERE (users.email = X OR emails.email = X)
    LIMIT 1;

The use of a JOIN means having to scan over all Emails and users, join
them together and then filter out the rows that don't match the criteria
(though this step may be taken into account already when joining).

In the new setup this query instead uses a sub-query, producing the
following SQL:

    SELECT *
    FROM users
    WHERE id IN (select user_id FROM emails WHERE email = X)
    OR email = X
    LIMIT 1;

This query has the benefit that it:

1. Doesn't have to JOIN any rows
2. Only has to operate on a relatively small set of rows from the
   "emails" table.

Since most users will only have a handful of Emails associated
(certainly not hundreds or even thousands) the size of the set returned
by the sub-query is small enough that it should not become problematic.

Performance of the old versus new version can be measured using the
following benchmark:

    # Save this in ./bench.rb
    require 'benchmark/ips'

    email = 'yorick@gitlab.com'

    def User.find_by_any_email_old(email)
      user_table = arel_table
      email_table = Email.arel_table

      query = user_table.
        project(user_table[Arel.star]).
        join(email_table, Arel::Nodes::OuterJoin).
        on(user_table[:id].eq(email_table[:user_id])).
        where(user_table[:email].eq(email).or(email_table[:email].eq(email)))

      find_by_sql(query.to_sql).first
    end

    Benchmark.ips do |bench|
      bench.report 'original' do
        User.find_by_any_email_old(email)
      end

      bench.report 'optimized' do
        User.find_by_any_email(email)
      end

      bench.compare!
    end

Running this locally using "bundle exec rails r bench.rb" produces the
following output:

    Calculating -------------------------------------
                original     1.000  i/100ms
               optimized    93.000  i/100ms
    -------------------------------------------------
                original     11.103  (± 0.0%) i/s -     56.000
               optimized    948.713  (± 5.3%) i/s -      4.743k

    Comparison:
               optimized:      948.7 i/s
                original:       11.1 i/s - 85.45x slower

In other words, the new setup is 85x faster compared to the old setup,
at least when running this benchmark locally.

For GitLab.com these improvements result in User.find_by_any_email
taking only ~170 ms to run, instead of around 800 ms. While this is
"only" an improvement of about 4.5 times (instead of 85x) it's still
significantly better than before.

Fixes #3242
  11. 29 Oct, 2015 13 commits
  13. 28 Oct, 2015 6 commits