MySQL Insert Message Queue

Question

We are creating an ajax application into which user input is sent for processing in a php script. We are currently writing each request to a log file for tracking. I would like to move this tracking to the database table, but I do not want to run the insert statement after the query. What I would like to do is create a "queue" of transactions (inserts and updates) that need to be processed in the MySQL database. Then I set up a cron job or process to check and process transactions in the queue. Is there something there that we could rely on, or do we just need to write to text log files and process them?

Answer 1

You want Gearman - it will queue queries and insert them as and when the database is ready for them, so you do not overload the database server.

Gearman provides a general framework application for working with other machines or processes that are better suited to the job. This allows you to do parallel work, load balance processing and call functions between languages. It can be used in various applications, from high-availability websites to replicating events database replication. In other words, it is the nervous system for distribution that processes the message. Some strengths about Gearman:

A recent (and quality) post about using databases for registration here , which (in short) says:

• Use MyISAM with parallel inserts
• Rotate the tables daily and use UNION to query
• Use delayed inserts with MySQL or a job-processing agent such as Gearman (although MySQL has a limit on the number of them, it will queue before silently dropping them!)

HighScalability write to Gearman .

If you really want to avoid this, you can write the original SQL statements to a file and process them using this cronjob:

mysql loggingDB logTable < fullLog.sql && > fullLog.sql 

Answer 2

MySQL can do some work for you:

INSERT DETAILS

http://dev.mysql.com/doc/refman/5.1/en/insert-delayed.html

"The DELAYED parameter for the INSERT statement is the MySQL extension for standard SQL, which is very useful if you have clients that do not need or do not need to wait for INSERT to complete. This is a common situation when you use MySQL for logging ..."

Unfortunately, the DELAY option is available only for INSERT, not UPDATE.

Answer 3

I think the Zend_Queue class is your lost example here is also http://www.zfsnippets.com/snippets/view/id/60