Hi wise monks,

First, a short description of the perl application. At startup a server process forks of approx. 5 child processes. These persistent child processes (let's name them A, B, C, D and E) each query a MySQL database queue to see if messages are waiting. Simplified database record example: 

Message     Process
abc         A
def         D
ghi         D
jkl         C

[download]
Each child process queries the database approx. 2 times per second, processes only its own messages and deletes the record from the queue, so process A only processes the records where the Process field equals A etc.

Everything is working fine, but the database is queried a lot. Five processes means approx. 5x2 = 10 queries per second just to determine if there are any messages waiting for the process.

A more efficient solution would be to let the server process do the querying, and then inform the corresponding child process(es) that messages are waiting to be processed: 

SELECT DISTINCT(Process) FROM Queue;

[download]
Then a child process only accesses the database when needed, preventing a lot of database selects.

Now my question: I was thinking about using signals. Each time the server process finds messages in the queue, it sends a signal to the corresponding child process. The child process set a flag upon receiving the signal: 

$SIG{USR1} = \&CatchSignal;

...

sub CatchSignal {
    my $signal = shift;

    if ($signal eq "USR1") {
        $SIG_CHECK_DATABASE = 1;
    }
}

...

if ($SIG_CHECK_DATABASE) {
    # Process all messages from the database queue
    $SIG_CHECK_DATABASE = 0;
}

[download]


- Has anybody used a similar approach using signals?
- Is it a good solution?
- What are the disadvantages of such a solution?
- Any other options?

Any information is highly appreciated!

TIA, Marcel

In reply to Database queueing and signals by Marcello

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.