Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have an averages table that should keep track of an average value over time. I don't want to have a row for each value, just a single row that continuously updates the average. What I've come up with is this:

set @value=4;
set @name="myAverageValue";
UPDATE `timing` SET
  `max` = greatest(`max`,@value),
  `average` = `average` + ((@value - `average`) / (`count` + 1)),
  `count` = `count` + 1
WHERE `name` = @name

Many clients may be doing this at the same time, and I don't want there to be any locking issues. I don't care what order the updates are run in, since in the end it will all end up the same. I just want to have a query that sends the UPDATE to the database, and it will process it eventually, similar to an INSERT DELAYED. Does UPDATE LOW_PRIORITY do this, or does that cause the client to wait until it is available?

share|improve this question
    
Your example uses static variables, but where do these things that you're averaging actually come from? Are you averaging values from another table? –  Michael - sqlbot Jan 29 '13 at 22:35
    
The values com from a PHP script. –  Ed Marty Jan 30 '13 at 3:08
    
You should be storing "sum" and "count" - not the "average" - and calculate it ("sum"/"count"), when you need it. The way you are doing it now, you will have (subtle or big) calculation errors in the "average" values. –  ypercube Feb 28 '13 at 20:55
    
I don't really care that much about calculation errors, I'm just looking for a ballpark value. –  Ed Marty Mar 1 '13 at 15:57
add comment

2 Answers

While there is no such command as UPDATE DELAYED, you can create this mechanism in a rather strange way. Please follow my rather crazy train of thought...

SCENARIO : Let's say you have just a single DB Server called M1

  • You want to fire UPDATEs at a single table called mydb.timing
  • You don't want UPDATEs to log jam the table

SOLUTION : Serialize UPDATEs

MECHANISM : Would you believe MySQL Replication ?

That's right, I said MySQL Replication.

You are probably saying right now

How in the world would having a Slave create UPDATE DELAYED ?

Here is how you do it:

STEP 01 : Using another DB Server (call it M2), create a MySQL Instance with binary logging enabled.

STEP 02 : mysqldump all user databases with no data, just structures (called it MySQLSchema.sql)

STEP 03 : Edit MySQLSchema.sql, change all ENGINE=MyISAM into ENGINE=BLACKHOLE

STEP 04 : Edit MySQLSchema.sql, change all ENGINE=InnoDB into ENGINE=BLACKHOLE

STEP 05 : Run source MySQLSchema.sql on M2.

STEP 06 : Run RESET MASTER; on M2

STEP 07 : Add replicate-do-table=mydb.timing to /etc/my.cnf on M1

STEP 08 : service mysql restart on M1

STEP 09 : Setup replication from M2 to M1

STEP 10 : In your application, fire your code

set @value=4;
set @name="myAverageValue";
UPDATE `mydb.timing` SET
  `max` = greatest(`max`,@value),
  `average` = `average` + ((@value - `average`) / (`count` + 1)),
  `count` = `count` + 1
WHERE `name` = @name

only at M2

That's it. No locking can ever happen on M2 since mydb.timing on M2 uses the BLACKHOLE Storage Engine (a.k.a /dev/null Storage Engine). Having MySQL Replication from M2 to M1 forms a queue that serializes UPDATEs. The delay appears in the form of MySQL Replication's asynchronous operation. Replication lag (in this instance only) is totally acceptable since you are looking for UPDATE DELAYED.

Give it a Try !!!

CAVEAT

This turns MySQL Replication upside down because the production server becomes a Slave to a BlackHole Server. With this strange topology, you can also implement DELETE DELAYED.

Make sure you include this option

[mysqld]
expire-logs-days=1

on M2. Otherwise, lots of UPDATEs makes for lots of binlogs

share|improve this answer
add comment

I don't believe that that will do what you want because it will be very dependent on SELECT timing. Even if it waits until their are no selects pending (which it will only do for some table types, eg MyISAM, and not InnoDB, which you are probably using), the update could still take time, which would block new SELECTS. So you would still run into locking errors.

I don't believe the UPDATE returns until it actually runs.

http://dev.mysql.com/doc/refman/5.0/en/update.html

share|improve this answer
    
I don't care considerably about SELECTs. There aren't really many SELECTs on this table at all. I'm essentially just using this as a way to update the average atomically. –  Ed Marty Jan 29 '13 at 19:48
    
If you don't care about SELECTs then UPDATE LOW_PRIORITY doesn't buy you anything. It sounds like what you really want is a lightweight messaging endpoint. How important is the data? If you can afford a little potential loss you could use a memory table and just periodically sync to a disk table. –  Shannon Russell Jan 29 '13 at 20:19
    
What I'm worried about is if a hundred clients are trying to run the query at the same time, will 99 of them have to wait? Or can the client simply tell the server that this query needs to be run at some point? –  Ed Marty Jan 30 '13 at 3:07
    
So what if they wait a few milliseconds? If your table is using InnoDB and the primary key is name, the lockings will happen only for clients that want to update the same row. –  ypercube Feb 28 '13 at 21:10
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.