Using SKIP LOCKED on Postgres 9.5

[MSch]

It looks like lock_head is a perfect candidate for the new SKIP LOCKED feature in Postgres 9.5

If I'm not missing something then using SKIP LOCKED lock_head can be simplified to this:

UPDATE queue_classic_jobs
SET locked_at = now(), locked_by = pg_backend_pid()
WHERE id IN (
  SELECT id FROM queue_classic_jobs
  WHERE locked_at IS NULL AND q_name = $1 AND scheduled_at <= now()
  LIMIT 1
  FOR NO KEY UPDATE SKIP LOCKED
)
RETURNING *

[jipiboily]

If someone do it, it would be interesting to see benchmarks, because from experience this is the bottleneck with a lot of load...when we had say over 100 workers hitting lock_head at the same time, lock_head was using a lot of the DB CPU time.

What do you think @senny?

[senny]

@jipiboily I don't have a qualified opinion on it. I'm also no longer using QC as heavily as I used to.

[MSch]

I'd happily send a PR (I actually patched the SQL locally already and all tests pass) but don't have a benchmark harness set up.

Just thinking about what SKIP LOCKED does I'd bet money that it is a huge improvement, since it doesn't have to churn through subtransactions when NOWAIT raises and doesn't have to do a Count(*) either. After skimming the change I'm willing to raise that bet further ;)

[smathieu]

This is a promising idea. Would it be possible to preserve backward compatibility with postgres 9.3/9.4?

[jipiboily]

Just FYI, I am not using QC anymore at all...so I guess if you send a PR it might take some time for it to be reviewed & merged...

@smathieu are you still using QC?
@ukd1 you are still using QC, right? Anyone from your teams up for some QC maintenance? I think that if this turns into a PR, this could be super useful for you. /cc @shosti

[MSch]

I've forked queue-shootout here and updated it to queue_classic master here: https://github.com/MSch/queue-shootout

The results of comparing queue_classic master vs queue_classic with SKIP LOCKED vs que on my MacBook are here: https://gist.github.com/MSch/b4588b20d7116b4e78667cc474a19e8b

master:

queue_classic jobs per second: avg = 929.6, max = 1455.6, min = 659.2, stddev = 340.9
que jobs per second: avg = 2814.3, max = 3002.3, min = 2685.9, stddev = 123.6

With SKIP LOCKED from MSch@8394489:

queue_classic jobs per second: avg = 2352.9, max = 2862.4, min = 1803.1, stddev = 476.6
que jobs per second: avg = 2872.2, max = 2999.5, min = 2801.0, stddev = 77.9

[MSch]

Query plan with SKIP LOCKED: http://explain.depesz.com/s/uJ9g

lock_head doesn't utilize any indices, which makes me wonder if it wouldn't be worth it to remove those two indices since they will prevent HOT updates when locking:

    "idx_qc_on_name_only_unlocked" btree (q_name, id) WHERE locked_at IS NULL
    "idx_qc_on_scheduled_at_only_unlocked" btree (scheduled_at, id) WHERE locked_at IS NULL

I tried that and added a VACUUM FULL between runs, then reran the benchmarks: https://gist.github.com/MSch/7464aa8454cc3bfb5e64c7f10d9e4756

master:

queue_classic jobs per second: avg = 914.2, max = 1507.4, min = 449.1, stddev = 454.5
que jobs per second: avg = 2873.8, max = 3079.4, min = 2708.8, stddev = 179.6

SKIP LOCKED:

queue_classic jobs per second: avg = 2614.1, max = 2991.1, min = 2355.4, stddev = 238.5
que jobs per second: avg = 2788.1, max = 3087.1, min = 2637.2, stddev = 180.8

SKIP LOCKED + HOT updates:

queue_classic jobs per second: avg = 2900.3, max = 3013.3, min = 2746.0, stddev = 114.4
que jobs per second: avg = 2900.0, max = 3223.9, min = 2777.5, stddev = 184.4

[shosti]

@jipiboily we are still using it, but personally I don't think I'm up for serious maintenance work or a Big Upgrade (I think we'll probably migrate to another solution instead at some point). Maybe @ukd1 feels differently though?

[jipiboily]

@shosti totally understand that. "Back in the days", I have been an advocate to remove QC from RF's stack (for various reasons).

@MSch looks like pretty cool upgrades! I guess we now need to figure out what to do in terms of maintenance with QC...as apparently most maintainers are either inactive or are not using it anymore.

@smathieu are you using QC at your job?

[ukd1]

@shosti late reply; I think we should do this...though we need to move to 9.5 first...

[shalomabitan]

Hi,

@jipiboily just out of curiosity what are you using to accomplish the same QC does?

Thanks :)

[jipiboily]

@shalomabitan I'm using exclusively Sidekiq via ActiveJob nowadays for my Ruby projects. I am considering using SQS too at some point.

[ukd1]

@jipiboily you don't have beef with transactions?

[jipiboily]

@ukd1 not a problem for my use case. Not saying it would not be better, but nothing really bad can happen in my case.

[JasonHerr]

Heads up @MSch, the link in the header is dead and redirects to ad sites.

[ukd1]

@jipiboily @MSch @senny @smathieu - it's been ...years... but @JasonHerr's code was merged in #311! Closing!