Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

The Slotted Counter Pattern

planetscale.com

Post reply on HN
Print view View original

11–20 of 34 posts

Re: The Slotted Counter Pattern

#11

Post by teej »

wcarss wrote: post #7

At the extreme end of this, you could also append a new row for every event and count them. If the number of rows would be too big over some period of time, you could similarly aggregate them occasionally and clear the "scratch" table.

This "Slotted Counter" approach optimizes for a write-contention constraint, specifically row locks. From 10 seconds of Googling it seems InnoDB has other locks it uses on INSERT, I'd first check if moving the write contention to gap locks actually helps or not.

One side benefit of this approach is that getting the final aggregate is cheap, where compacting an append-only log table might not be.

Re: The Slotted Counter Pattern

#12

Post by samatman »

LesZedCB wrote: post #6
yccs27 wrote: post #5

Earlier quoted context omitted.

Yeah, it seems like it would be possible for the DB engine to aggregate all these increments into one update. If you have two increments by one each in the queue, why not make it a single increment by two? I'm not sure though how much computing power it would need to figure that out...

wouldn't that break the atomic and isolated rule of ACID?

Not necessarily. If both updates are in a single transaction then it's valid for the query planner to batch them, although that seems unlikely in the use case this table layout is designed for.

Re: The Slotted Counter Pattern

#14

Post by iv42 »

tantalor wrote: post #9

Basically same as "Sharding counters" (2008) https://download.huihoo.com/google/gdgdevkit/DVD1/developers... Also in Brett Slatkin's "Building Scalable Web Apps with App Engine" (2008) https://youtu.be/Oh9_t5W6MTE?t=1181

Also, it's basically identical to the pattern described as Counter Tables in "High Performance MySQL" (3rd edition), published in 2012.

Re: The Slotted Counter Pattern

#15

Post by [deleted] »

samatman wrote: post #12
LesZedCB wrote: post #6

Earlier quoted context omitted.

wouldn't that break the atomic and isolated rule of ACID?

Not necessarily. If both updates are in a single transaction then it's valid for the query planner to batch them, although that seems unlikely in the use case this table layout is designed for.

[deleted]

Re: The Slotted Counter Pattern

#16

Post by ukd1 »

Cool, but this method will still get locks in whatever percent of cases, regardless of if there are slots not in transactions. In Postgres you can probably do this with slots using SKIP LOCKED; though in practice I belive you have to deal with the case where everything is locked, by falling back to waiting for a lock.

UPDATE counters SET count = count + 1 WHERE name = ? AND slot = (SELECT slot FROM counters FOR UPDATE SKIP LOCKED LIMIT 1) LIMIT 1

Re: The Slotted Counter Pattern

#17

Post by asadawadia »

the fundamental problem with counters is that it does a read-modify-write cycle which is quite harsh on the DB - a better approach is to take advantage that counters are cumulative and we can keep the delta events only and 'merge' them on reads

or just buffer the counters in redis and then flush them out

I have a counters API that does precisely this

Docs are here: cmd+f: 'Counters API now live'

https://blog.aawadia.dev/api/

Re: The Slotted Counter Pattern

#18

Post by zimpenfish »

ris wrote: post #10
tantalor wrote: post #9

Basically same as "Sharding counters" (2008) https://download.huihoo.com/google/gdgdevkit/DVD1/developers... Also in Brett Slatkin's "Building Scalable Web Apps with App Engine" (2008) https://youtu.be/Oh9_t5W6MTE?t=1181

Was going to post this. "Sharding" seems like a better term for communicating the idea.

I dunno - "sharding" would generally imply (to me, at least) that you're spreading the counters amongst different databases (or tables in a smaller context.) All the counters are in the same table here which is counterintuitive to "sharding".

Re: The Slotted Counter Pattern

#19

Post by MauranKilom »

LesZedCB wrote: post #6
yccs27 wrote: post #5

Earlier quoted context omitted.

Yeah, it seems like it would be possible for the DB engine to aggregate all these increments into one update. If you have two increments by one each in the queue, why not make it a single increment by two? I'm not sure though how much computing power it would need to figure that out...

wouldn't that break the atomic and isolated rule of ACID?

Not any more than the slotted counter pattern...

Re: The Slotted Counter Pattern

#20

Post by robocat »

ukd1 wrote: post #16

Cool, but this method will still get locks in whatever percent of cases, regardless of if there are slots not in transactions. In Postgres you can probably do this with slots using SKIP LOCKED; though in practice I belive you have to deal with the case where everything is locked, by falling back to waiting for a lock. UPDATE counters SET count = count + 1 WHERE name = ? AND slot = (SELECT slot FROM counters FOR UPDAT…

How does that avoid race conditions?

SKIP LOCKED doesn’t seem to be designed for that purpose: https://www.enterprisedb.com/blog/what-skip-locked-postgresq...

Post reply on HN