Re: AQ vs. PL/SQL for implementing a messaging queue

> >Has anyone tried to simulate a messaging queue using SQL?
>
> Yes, I was a project where we implemented a message queue in PL/SQL
> using a method similar do what you described. Two systems have been in
> production for about 4 years now.

I'm interested in hearing more about the details of how you implemented your queue. If you have a minute could you describe how (via SQL) you enqueue and dequeue from your database table?

For our prototyping we have one process inserting a record into the queue, and we have another process that selects for update (with an order by clause to find the min() record), then update that record so other threads doing the same SELECT for UPDATE don't access the same record.  

> >What kind of results did you have?
>
> Good, the queue is certainly not the bottleneck. It processes about a
> million messages a day (almost all during business hours). During peak
> load the queue gets a little backed up (because the consumer is the
> system's bottleneck) but it's no problem.
>
> Keep in mind though that an implementation like this will be
> synchronous, not asynchronous. For example, for certain messages we
> needed to send out an email and we had to send it out ASAP - at the
> time the message was put in the queue. Most of the time that wasn't a
> problem, but sometimes the mail server was slow to respond and so the
> whole queue got locked up. We had to change a few things so we could
> send email asynchronously.
>
> I have never used AQ so I can't comment on if that would have worked
> better.
>
> No matter which approach you choose I highly recommend you test with
> the expected load. And don't just test on an empty database, test on a
> database similar in size to what a fully loaded production database
> will be.

I totally agree with this, it is mandatory to execute performance tests against a real database. This will be a part of our analysis.

Thanks again for your ideas. Received on Mon Oct 18 2004 - 11:44:50 CDT