floatman posted:

There was recently a jobs queue system implemented in SQL.
Jobs went in a table, then something picked jobs out. Jobs were then done.
Because there was multiple users who could queue jobs, a random picking algorithm was required so that the job system doesn't get stuck doing all the jobs for user A before user B (gotta be fair).
BUT then there was the concept that some jobs were of higher priority than other jobs, so there must be a random picking of jobs, but "randomly" pick the higher priority jobs first. And it was decided that random was not fair enough, so we must pick user jobs by round robin (i.e. high priority job for user A, high priority job for userB, normal job for user A, etc).

So how did this pan out? Some loving poo poo giant rear end SQL statement embedded in the codebase that used recursive CTEs. Since jobs were either waiting status or completed status, whenever a job was picked, the job record needed a lock to lock the job so if the SQL did run again while this job was running it wouldn't pick up the same job. This lock also touched the other tables that were involved in the job (I E. Updating of user details, etc you can imagine the loving shitshow). The recursive CTE query also had this group/order by statement that did JACK poo poo and was not needed but formed 90% of the query plan. Database just loving mysteriously died one-day once the amount of data that needed to be scanned through surpassed the query planned limits and the SQL server just loving gave up and decided to chug along row by row.
And here's the kicker:
The codebase called that SQL query to pick up a "semi-random" job if it wanted to get the next job to process. But if the codebase KNEW the primary key of the job it wanted to process and needed to get that job, it would still call this idiot hellfucker SQL query but with the primary key specified so even though you're getting just that one record you could have gotten with a straight up select you're still going to pay the costs of the recursive CTE and the 90% group/order by statement.

All these loving problems could have been avoided if we just did a naive random select from a queue of jobs. "But what about higher priority jobs," You may ask.
Welp, JUST USE TWO SEPARATE QUEUES and empty the priority one first duhhhhhhhhhhhhhh

abigserve posted:

for you sql gurus how good is it at handling billions/trillions of very small rows (like <100 byte average length)

I am writing an application that does tens of thousands of rows per second and a standard interface is much nicer than writing it raw to disk and also writing some handlers for retrieving it.

The code is modular so I can write to anything, I'm going to try mongodb as well because it is web scale, maybe I'll do hadoop as well but I don't even know where to start with something like that

abigserve posted:

it's network flow data, consists of a single solitary table and looks like this (sorry for the formatting)

code:

Field, Type, Null, Key, Default, Extra
last_switched,datetime,YES,MUL,NULL,
src_ip,"int(4) unsigned",YES,,NULL,
src_port,"int(2) unsigned",NO,,NULL,
dst_ip,"int(4) unsigned",YES,,NULL,
dst_port,"int(2) unsigned",NO,,NULL,
in_bytes,"int(8) unsigned",NO,,NULL,
in_pkts,"int(8) unsigned",NO,,NULL,
protocol,"int(1) unsigned",NO,,6,
src_ipv6,varbinary(16),YES,,NULL,
dst_ipv6,varbinary(16),YES,,NULL,

I guess the intended use is to store and have the ability to search through the data within reasonably narrow timeframes.

cinci zoo sniper posted:

this is going to be a really lovely table to query like that, assuming you'll use last_switched, a nullable field, as the key

abigserve posted:

thanks for noticing that, that was an error in my schema as it wasn't supposed to be a nullable field

cinci zoo sniper posted:

if it's a non-nullable, monotonically increasing or decreasing field that will be used in every single query, then in postgres you can use last_switched as partitioning key or brin index key. that should make this table decently well manageable at least until low-mid billions of records

abigserve posted:

it'd be pretty easy to write psql as another supported datastore. I guess I should probably just bite the bullet, pay the :tenbux: for a db.t2.xlarge instance and a couple terabytes of storage then just see what happens

floatman posted:

There was recently a jobs queue system implemented in SQL.
Jobs went in a table, then something picked jobs out. Jobs were then done.
Because there was multiple users who could queue jobs, a random picking algorithm was required so that the job system doesn't get stuck doing all the jobs for user A before user B (gotta be fair).
BUT then there was the concept that some jobs were of higher priority than other jobs, so there must be a random picking of jobs, but "randomly" pick the higher priority jobs first. And it was decided that random was not fair enough, so we must pick user jobs by round robin (i.e. high priority job for user A, high priority job for userB, normal job for user A, etc).

So how did this pan out? Some loving poo poo giant rear end SQL statement embedded in the codebase that used recursive CTEs. Since jobs were either waiting status or completed status, whenever a job was picked, the job record needed a lock to lock the job so if the SQL did run again while this job was running it wouldn't pick up the same job. This lock also touched the other tables that were involved in the job (I E. Updating of user details, etc you can imagine the loving shitshow). The recursive CTE query also had this group/order by statement that did JACK poo poo and was not needed but formed 90% of the query plan. Database just loving mysteriously died one-day once the amount of data that needed to be scanned through surpassed the query planned limits and the SQL server just loving gave up and decided to chug along row by row.
And here's the kicker:
The codebase called that SQL query to pick up a "semi-random" job if it wanted to get the next job to process. But if the codebase KNEW the primary key of the job it wanted to process and needed to get that job, it would still call this idiot hellfucker SQL query but with the primary key specified so even though you're getting just that one record you could have gotten with a straight up select you're still going to pay the costs of the recursive CTE and the 90% group/order by statement.

All these loving problems could have been avoided if we just did a naive random select from a queue of jobs. "But what about higher priority jobs," You may ask.
Welp, JUST USE TWO SEPARATE QUEUES and empty the priority one first duhhhhhhhhhhhhhh

floatman posted:

abigserve posted:

it's network flow data, consists of a single solitary table and looks like this (sorry for the formatting)

code:

Field, Type, Null, Key, Default, Extra
last_switched,datetime,YES,MUL,NULL,
src_ip,"int(4) unsigned",YES,,NULL,
src_port,"int(2) unsigned",NO,,NULL,
dst_ip,"int(4) unsigned",YES,,NULL,
dst_port,"int(2) unsigned",NO,,NULL,
in_bytes,"int(8) unsigned",NO,,NULL,
in_pkts,"int(8) unsigned",NO,,NULL,
protocol,"int(1) unsigned",NO,,6,
src_ipv6,varbinary(16),YES,,NULL,
dst_ipv6,varbinary(16),YES,,NULL,

I guess the intended use is to store and have the ability to search through the data within reasonably narrow timeframes.

NihilCredo posted:

for the sake of discussion, let's assume that we actually needed a complicated priority system (otherwise the whole thing is trivial as you noted)

seems like the right approach would have been to separate the priority scoring from the actual job queue

for the former, you can run that overly-complicated query at the READ UNCOMMITTED transaction isolation level so it doesn't acquire any locks. it's not displaying data to the customer or anything, so dirty reads aren't a problem

then, with the job_id in hand from the query, you can set the isolation level back to highest make full use of every locking feature your database provides to actually update that job to "processing" status and ensure that it gets run one and only one time. you're only touching the job table at this point so it doesn't affect the rest of the program

Powerful Two-Hander posted:

that would leave open the case of "job got set to processing and never finished because it halted half way" though which yeah i guess you could solve via a time out or whatever or through a db transaction or something buutttt why not just have you queue read by a single process dispatcher that passes the jobs to workers that do the actual thing and write success when they're done using the job id directly ?

any time the dispatcher fails it'd just start up and pick up the next x jobs and carry on and you don't have to worry about messing with db locking at all and the workers don't need to know or care about the priority.

Shaggar posted:

why would the single process dispatcher be any less likely to fail in the middle of processing than independent dispatchers? what if it assigns a process to a worker and the worker crashes? you have to handle retries some how and a single dispatcher doesn't really help.

abigserve posted:

for you sql gurus how good is it at handling billions/trillions of very small rows (like <100 byte average length)

I am writing an application that does tens of thousands of rows per second and a standard interface is much nicer than writing it raw to disk and also writing some handlers for retrieving it.

The code is modular so I can write to anything, I'm going to try mongodb as well because it is web scale, maybe I'll do hadoop as well but I don't even know where to start with something like that

Powerful Two-Hander posted:

that would leave open the case of "job got set to processing and never finished because it halted half way" though which yeah i guess you could solve via a time out or whatever or through a db transaction or something buutttt why not just have you queue read by a single process dispatcher that passes the jobs to workers that do the actual thing and write success when they're done using the job id directly ?

any time the dispatcher fails it'd just start up and pick up the next x jobs and carry on and you don't have to worry about messing with db locking at all and the workers don't need to know or care about the priority.

Shaggar posted:

read uncommitted (aka nolock) wont work for that since multiple processes performing the same query will get the same dirty data and then all attempt to pull the same row for processing. you need to use readpast and updlock which will read past locked rows and then lock only your row you intend to process.

cinci zoo sniper posted:

eh, for testing maybe, for production im not a fan db.t2.xlarge or db.t2.2xlarge because they are postgresql 9.x. for you 10 is more or less needed, 11 is desired (which is only on rds preview right now i think) if you want to do partitions, rather than brin indexing.

NihilCredo posted:

when you set it to processing, also set a column with the unique ID of the dispatcher. if that ID is the same between restarts, then a dispatcher that crashes can pick up its own jobs when it restarts. failing that, have a way to get the IDs of all active dispatchers (can be just a time with last_seen time updated every minute) and treat as free any job marked as 'processing' by an absentee dispatcher

NihilCredo posted:

i think he's a perfect fit for https://www.timescale.com/how-it-works

NihilCredo posted:

i think he's a perfect fit for https://www.timescale.com/how-it-works

NihilCredo posted:

i think he's a perfect fit for https://www.timescale.com/how-it-works

NihilCredo posted:

when you set it to processing, also set a column with the unique ID of the dispatcher. if that ID is the same between restarts, then a dispatcher that crashes can pick up its own jobs when it restarts. failing that, have a way to get the IDs of all active dispatchers (can be just a time with last_seen time updated every minute) and treat as free any job marked as 'processing' by an absentee dispatcher


you use read uncommitted when selecting the job_id to process

you use for update skip locked (or the mssql equivalent) when you actually update the job_id you found and set it to 'processing'

if the update fails, you assume someone who read your same data beat you to the job and goto step 1. you may have to run the heavy query multiple times but this way you never actually locks the non-job queue tables

Shaggar posted:

wouldn't this cause a race condition wrt the update? like if 2 processes have selected the same job id and then theres a delay between when one attempts to update and its selection of job id, you run the risk of the faster process successfully updating and committing before the later tries its update. so when the second does its update the first process's lock will be gone so the update will work.

abigserve posted:

it's network flow data, consists of a single solitary table and looks like this (sorry for the formatting)

code:

Field, Type, Null, Key, Default, Extra
last_switched,datetime,YES,MUL,NULL,
src_ip,"int(4) unsigned",YES,,NULL,
src_port,"int(2) unsigned",NO,,NULL,
dst_ip,"int(4) unsigned",YES,,NULL,
dst_port,"int(2) unsigned",NO,,NULL,
in_bytes,"int(8) unsigned",NO,,NULL,
in_pkts,"int(8) unsigned",NO,,NULL,
protocol,"int(1) unsigned",NO,,6,
src_ipv6,varbinary(16),YES,,NULL,
dst_ipv6,varbinary(16),YES,,NULL,

I guess the intended use is to store and have the ability to search through the data within reasonably narrow timeframes.

Slurps Mad Rips posted:

fun

Shaggar posted:

at some point im gonna use service broker to do pub sub instead of polling, but i'll still have the same table structure for reporting and app needs. service broker would basically be running the same query to get the list if items to queue tho.

Cold on a Cob posted:

i was more responding to the op, but in your case why not just push messages to your queue when you update the operational table instead of updating an operational table to query later to queue messages? seems like an extra pointless step to me.

if the problem is they can't be processed until a later time, use a conversation timer.

if you're dealing with multiple dependencies then check and only queue the message when all dependencies are satisfied.

if the messages need to be "cancellable" things get hairy but you can still update the operational table to say "don't process that message we nulled the process timestamp" and then double check when processing that message you queued up for 1 am, and since you're always looking poo poo up via the pk of the table the check is fast.

basically i don't see the point of turning operational tables into queues and i really have to stretch to come up with scenarios where it would be the only choice

CRIP EATIN BREAD posted:

what would be your search parameters for this?

Shaggar posted:

its easier to just handle control and statusing in a table vs an opaque message queue and ive never seen a purpose built message queue that isn't fragile as hell and relies on the ability to dump everything in the queue and restart when things go wrong.

Cold on a Cob posted:

lol

please tell me this was in sql server

Cold on a Cob posted:

wait

they left the jobs in the "queue" ?!?!?!