MySQL circular buffer

Hello,

I'm looking at implementing a database-level stack for a (multi stack)
mail merge queue (able to queue up to 1 million messages per stack).
Current workflow:

server 1 (containing main db) transmits data sets (used to populate
mail merge template) to server 2
server 2 web-facing script (script 1) puts data sets onto stack (db)
server 2 mail process script (script 2) pulls single data block (say
100 rows) from front of stack (fifo), merges the data with the
template and sends data to smtp process
server 2 script 2 removes "processed" block of rows

The problems I am considering include keeping track of the value of
the primary key across multiple instances of script 2 (the script will
run from a cron), whether to select by limit or range (i.e. stack_id >
500 && stack_id < 601 vs where stack_id = 500 limit 100) and looping
the index back to zero while ensuring there is no data that hasn't
been deleted.

So - it seems easier to avoid these problems and implement a circular
buffer :) What I would like to know is if anybody has experience
implementing this sort of data structure in MySQL (linked list?) or
any advice.

There don't seem to be any current implementations so the last
question is - is there a good reason for that? Too many overheads? I
know this sort of structure is best kept in memory and not on disk,
but I am not sure of any other solution to a queue this size.

Any comments welcome. Many thanks,


Andy

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: MySQL circular buffer

> (i.e. stack_id > 500 && stack_id < 601 vs where stack_id = 500 limit 100)
stack_id between 501 and 600 (stack_id > 500 && stack_id < 601) is much
better


> What I would like to know is if anybody has experience
> implementing this sort of data structure in MySQL (linked list?) or
> any advice.
>
tables:
process_table:
IDProcess PK

mail_table
IDMail PK
IDPrrocess FK references process_table.IDProcess ON DELETE SET NULL
Mail TEXT
From VCH(255)
TO VCH(255)
DateModified DATE ON UPDATE CURRENT_TIMESTAMP
Mailed TINYINT DEFAULT 0;
--
code:
define('MaxProccessTimeMinutes', 30);
define('BatchCount', 100);

INSERT INTO process_table VALUES ();
$lnIdProcess = GetLastIDProcess();

label send_mail:
$ldDateExpired = time() - MaxProccessTimeMinutes * 60;
UPDATE mail_table
SET IDProcess = $lnIdProcess
WHERE
Mailed = 0 AND
(
IDProcess IS NULL OR
(
IDProcess IS NOT NULL AND
DateModified <= $ldDateExpired
);
)
LIMIT BatchCount;
while ($result = SELECT IDMail, MailText, From, To FROM mail_table WHERE
IDProcess = $lnIDProcess)
{
if (send_mail())
{
UPDATE mail_table SET Mailed = 1 WHERE IDMail = $result['IDMail'];
}
}
if there are other mails goto send_mail;
else DELETE FROM process_table WHERE IDProcess = $lnIDProcess;

this way more than one process can send mails, also if one process exits
prematurely the other can send his emails later.
Time to time run cron:
DELETE FROM mail_table WHERE Mailed = 1;
rebuild indexes on mail_table;

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php