Rewriting the scope_all benchmark in Sequel and Raw

1

@sam I wrote scope_all benchmark in raw sql to compare with sequel and AR. If the test is correctly written these are results I got:

activerecord/postgres_scope_all

  • its/s : 116
  • its/s deviation : 7.71
  • total allocated objects per iteration : 14128

sequel/postgres_scope_all

  • its/s : 414
  • its/s deviation : 9.65
  • total allocated objects per iteration : 5032

raw/postgres_scope_all

  • its/s : 307
  • its/s deviation : 5.53
  • total allocated objects per iteration : 5018
1 Like
2

How can raw be slower than sequel, this is quite interesting, can you link to the sequel bench.

Also I think we should probably maybe do something with the results, perhaps if we just formatted a giant string with the results each time?

str = ""
User.all.each do |u|
   str << "name #{u.name} email #{u.emal}\n"
end

This kind of makes the bench bit more honest cause it is actually trying to achieve something.

3

@sam Here is the same sequel bench.

How can raw be slower than sequel

I wasn’t expecting it neither. Maybe it’s slower because raw query is made through ActiveRecord::Base.connection. Perhaps raw sql queries in sequel and AR perform differently. I’ll write bench for raw sql through sequel to compare.

Also I think we should probably maybe do something with the results

Right know we convert to array after fetching. But doing something more specific is better idea, will fix it :thumbsup:

4

I added one more test to measure raw sql through sequel. Note that I added some operations on fetched records in scope_all benches, which caused baseline to go little lower, but differences remained the same.

Again, these are results I got:

activerecord

its/s : 77
deviation : 3.85
allocated objects : 20136

raw sql through activerecord

its/s : 285
deviation : 5
allocated objects : 6020

sequel

its/s : 345
deviation : 6
allocated objects : 6035

raw sql through sequel

its/s : 350
deviation : 8
allocated objects : 7040

Interesting thing to note here is performance in both sequel tests is similar.

5

@sam now I figured out that you weren’t probably thinking of executing raw sql commands through orm’s like AR and sequal, but directly via pg and mysql gems. :confused:

6

Oh yeah you got to use raw connections for raw tests:

So you would use:

 ActiveRecord::Base.connection.raw_connection
=> #<PG::Connection:0x00563053b11f78>

Don’t use the AR abstraction on top of connection as it is wasteful.

Also, keep in mind Sequel has tricks:

We got to test with this as well.

Note for Jeremy

Our long term plan is to make this graph/page here:

https://rubybench.org/rails/rails/commits?result_type=activerecord/postgres_scope_all_with_default_scope&display_count=2000

Show Sequel numbers and raw numbers as well.

But we got to get all the benches into good shape first.

7

Here is one raw test for postgres_scope_all, I did it this time directly via pg gem.

Results I got locally are following:

its/s : 942
allocated objects: 4004

1 Like
8

Its interesting to see 77 vs 942, it really drives home how much overhead AR brings to the table.

9

Can you update that post with all your results to date on this specific test?

Also, can you run it on Rails 2/3/4 for a quick and dirty comparison.

(I know it will be eventually backfilled, but I am curious) :wink:

1 Like
10

sure @sam, I’m on it :thumbsup:

1 Like
11 
// Activerecord

{
  "label":"activerecord/postgres_scope_all",
  "version":"3.2.22.5",
  "iterations_per_second":76.49321589608218,
  "iterations_per_second_standard_deviation":2.614610951534639,
  "total_allocated_objects_per_iteration":43179
}

{
  "label":"activerecord/postgres_scope_all",
  "version":"4.2.8",
  "iterations_per_second":80.02798516365223,
  "iterations_per_second_standard_deviation":3.748688654181643,
  "total_allocated_objects_per_iteration":21153
}

{
  "label":"activerecord/postgres_scope_all",
  "version":"5.1.1",
  "iterations_per_second":77.37787266441069,
  "iterations_per_second_standard_deviation":3.8770773823300333,
  "total_allocated_objects_per_iteration":20131
}

// Sequel

{
  "label":"sequel/postgres_scope_all",
  "version":"4.46.0",
  "iterations_per_second":342.1849354356875,
  "iterations_per_second_standard_deviation":5.2603133966378355,
  "total_allocated_objects_per_iteration":6033
}

// Raw pg

{
  "label":"raw_pg/postgres_scope_all",
  "version":"0.18.1",
  "iterations_per_second":810.0042539142364,
  "iterations_per_second_standard_deviation":7.530824647552864,
  "total_allocated_objects_per_iteration":4004
}

^ Here it is @sam :slight_smile:

Btw, I didn’t managed to run benchmark for rails 2, I couldn’t require rails gem in benchmark script. Not sure where’s the problem :confused:

1 Like
12

Awesome, now that you have this data. I want the graph amended so it can display a comparison of all these 5 results! Start hacking away at the web UI and the backfill.

Only focus on this single test, this is the only test you care about till we can render the 5 lines here and get it backfilled :wink:

https://rubybench.org/rails/rails/commits?result_type=activerecord/postgres_scope_all&display_count=2000

If you have to drop the whole “prepared” vs “non prepared” stuff and always do “prepared” for now so be it.

cc @system

Don’t worry about Rails 2 for now.

1 Like
13

@sem Just to make myself sure. Since this is graph with commit data, you want me to include here commits for sequel and pg. Therefore it would have 3 lines (AR, sequel, pg) and graph would look something like this:

chart.png1200Ă—800 56.3 KB

Maybe better place to display results for older Rails releases would be here?
https://rubybench.org/rails/rails/releases?result_type=activerecord/postgres_scope_all

14

Yes, absolutely, this is what I want you to work towards!

Possibly, let’s not worry about that for now, just for now worry about getting the graph with the 3 lines going and backfilling all the info. Work with @system on gaining access to prd.

1 Like
15

How are you going with this? Are you blocked on anything from our side?

16

@sam, I am not blocked on anything. Right now I am working on web to display graphs to compare. My first goal is to display sequel along with AR because sequel has been already added. Here’s wip PR on web.

17

Fantastic, focus on getting stuff merged and on the site as soon as you can. I prefer to have a slightly janky UI than wait weeks till we have the UI in perfect state.

1 Like
18

@sam
Little peek. This is with data from production imported in local. UI is not perfect and has some issues but before I get into fixing it I’ll make sure sequel benchmark graphs are being displayed.

image.png1366Ă—768 98.1 KB

Though I am not sure why sequel is not being displayed since it looks like been added :confused:

@system pls take a look at this one :arrow_down: :pray:

19

Cool!!! how far do you feel we are from having something on rubybench.org?

20

Hey @sam,

PR is some time in reviewing process. According to me it’s ready to be merged. When it’s merged we could compare benchmarks on rubybench.

Jeremy configured sequel repo with webhook to send push events on rubybench, so next time when he pushes something we should have sequel repo with it’s benchmarks on rubybench :tada:

2 Likes