4 ms·

Thank you for your thoughtful reply. It matches my thinking, except one part -- I'm constantly wondering if implementing the MQ over an RDBMS is a more pragmati

Thank you for your thoughtful reply. It matches my thinking, except one part -- I'm constantly wondering if implementing the MQ over an RDBMS is a more pragmatic approach for us at this point. Conceptually it's still a queue and still affords us the benefits of decoupling. Downside is probably locking issues in the DB. Upside is that is uses components in our stack that we already understand really well. Thoughts?

davismwfl 12y ago
Your welcome, hope it helps even if just a little. There are a number of companies/people that use the RDBMS as a queue, usually they utilize their same database server as the primary datastore. And a few will separate it onto a new server, but either way it is generally not the best idea. Ironically we just had a client do this, we built them a system using a message queue (RabbitMQ) and then they removed the queue and put an RDBMS in its place, because of a lack of familiarity. When we delivered the system we had to prove the solution scaled which it did, easily processing over 20 million messages a day with a single small application server. However, they replaced RabbitMQ and put SQL server in the middle to act as the queue. Their reasons were similar in that they know SQL Server very well, but with that move their throughput went to around a little less than 5 million messages per day using the same small application server. Additionally, they found they had to write a huge amount of code to make up for the queue processing, failure states, dead letter queue etc. Sadly, they put it on their primary SQL Server box as well, which has degraded the performance of their other applications as well, and increased the SQL maintenance drastically. Also, moving between environments, dev, staging and production became a bigger chore too. But even aside from pure performance, or deployment pains, a RDBMS is really a poor queue. You point out the locking issues, which you are absolutely right they can be a pain, but it is more than just that. Messages by their nature are highly volatile and transitional, which when ran through a database increases the pressure on the tempdb, the transaction log and overall general maintenance is increased many times over. It is tempting to use a RDBMS, because you have a strong background and team with a known skill set and you likely do not see all the differences between storing some messages in a table and a queue (yet). There is a world of difference though when you really start writing code and having to maintain it. By no means am I knocking RDBMS systems, as they are very good for what they do, but queuing systems are superior for what they do. But to make one a queuing system, you need to write a large amount of robust code to handle dead letters, dropped messages, failure states, acknowledgements etc. All of which sound pretty easy on the surface but are really complex to do correctly. Queues have already figured this out, and been proven. For example, lets say you have a service that picks up a message off an Amazon SQS queue, and then your service crashes before the message is processed. Based on a timeout value, SQS will automatically place that message back in the queue for the next service to pick up. If you do this in an RDBMS you will have to write all that logic into the database layer, or have other services monitoring and polling the database to figure it all out etc. This is actually quite a tough problem to solve correctly, so it is usually better left to one of the queuing technologies out there. As for what queues to use, that is dependent on your environment and needs, but RabbitMQ is very good, very scalable and flexible. But there are other systems too, even Amazon SQS for AWS users. The only ones I would typically avoid or I would try to persuade most people to avoid are MSMQ and SQL Server Service Broker, as they are not, in my opinion, robust queuing platforms (Although SSSB isn't horrible). Also, I believe this link was on HN a week or two ago, I didn't write it, nor do I know the author, but I believe the points are pretty valid and may help you: https://functionwhatwhat.com/why-you-should-not-use-an-rdbms-for-messaging/ https://functionwhatwhat.com/why-you-should-not-use-an-rdbms...

davismwfl 12y ago

Your welcome, hope it helps even if just a little. There are a number of companies/people that use the RDBMS as a queue, usually they utilize their same database server as the primary datastore. And a few will separate it onto a new server, but either way it is generally not the best idea. Ironically we just had a client do this, we built them a system using a message queue (RabbitMQ) and then they removed the queue and put an RDBMS in its place, because of a lack of familiarity. When we delivered the system we had to prove the solution scaled which it did, easily processing over 20 million messages a day with a single small application server. However, they replaced RabbitMQ and put SQL server in the middle to act as the queue. Their reasons were similar in that they know SQL Server very well, but with that move their throughput went to around a little less than 5 million messages per day using the same small application server. Additionally, they found they had to write a huge amount of code to make up for the queue processing, failure states, dead letter queue etc. Sadly, they put it on their primary SQL Server box as well, which has degraded the performance of their other applications as well, and increased the SQL maintenance drastically. Also, moving between environments, dev, staging and production became a bigger chore too. But even aside from pure performance, or deployment pains, a RDBMS is really a poor queue. You point out the locking issues, which you are absolutely right they can be a pain, but it is more than just that. Messages by their nature are highly volatile and transitional, which when ran through a database increases the pressure on the tempdb, the transaction log and overall general maintenance is increased many times over. It is tempting to use a RDBMS, because you have a strong background and team with a known skill set and you likely do not see all the differences between storing some messages in a table and a queue (yet). There is a world of difference though when you really start writing code and having to maintain it. By no means am I knocking RDBMS systems, as they are very good for what they do, but queuing systems are superior for what they do. But to make one a queuing system, you need to write a large amount of robust code to handle dead letters, dropped messages, failure states, acknowledgements etc. All of which sound pretty easy on the surface but are really complex to do correctly. Queues have already figured this out, and been proven. For example, lets say you have a service that picks up a message off an Amazon SQS queue, and then your service crashes before the message is processed. Based on a timeout value, SQS will automatically place that message back in the queue for the next service to pick up. If you do this in an RDBMS you will have to write all that logic into the database layer, or have other services monitoring and polling the database to figure it all out etc. This is actually quite a tough problem to solve correctly, so it is usually better left to one of the queuing technologies out there. As for what queues to use, that is dependent on your environment and needs, but RabbitMQ is very good, very scalable and flexible. But there are other systems too, even Amazon SQS for AWS users. The only ones I would typically avoid or I would try to persuade most people to avoid are MSMQ and SQL Server Service Broker, as they are not, in my opinion, robust queuing platforms (Although SSSB isn't horrible). Also, I believe this link was on HN a week or two ago, I didn't write it, nor do I know the author, but I believe the points are pretty valid and may help you: https://functionwhatwhat.com/why-you-should-not-use-an-rdbms-for-messaging/ https://functionwhatwhat.com/why-you-should-not-use-an-rdbms...