0

Does anyone have any recommendations how to implement this?

table1 will constantly be INSERTed into. This necessitates that every row on table2 be UPDATEd upon each table1 INSERT. Also, an algorithm that I don't know if MySQL would be best responsible for (vs PHP calculation speed) also has to be applied to each row of table2.

I wanted to have PHP handle it whenever the user did the INSERT, but I found out that PHP pages are not persistent after servering the connection to the user (or so I understand, please tell me that's wrong so I can go that route).

So now my problem is that if I use a total table UPDATE in a TRIGGER, I'll have locks galore (or so I understand from InnoDB's locking when UPDATing an entire table with a composite primary key since part of that key will be UPDATEd).

Now, I'm thinking of using a cron job, but I'd rather they fire upon a user's INSERT on table1 instead of on a schedule.

So I was thinking maybe a CURSOR...

What way would be fastest and "ABSOLUTELY" NO LOCKING on table2?

Many thanks in advance!

Table structure

table2 is all INTs for speed. However, it has a 2 column primary key. 1 of those columns is what's being UPDATEd. That key is for equally important rapid SELECTs.

table1 averages about 2.5x the number of rows of table2.

table2 is actually very small, ~200mb.

  • 1
    From first reading this shouts `TRIGGER`. To examine if deeper please provide some more details, p.e. data & table structure, load and so on. – Bjoern Jan 06 '13 at 18:21

1 Answers1

2

First of all: What you try is close to impossible - I don't know of an RDBMS, that can escalate INSERTs into one table into UPDATEs of another with "ABSOLUTELY NO LOCKING".

That said:

  • my first point of research would be, whether the schema could be overhauled to optimize this hotspot away.
  • if this cannot be achieved, you might want to look into making table2 an in-memory type that can be recreated from existing data (such as keeping snapshots of it together with the max PK of table1 and rolling forward if a DB restart is required). Since you need to update all rows on every INSERT into table1 it cannot be very big.
  • Next point of research would be to put the INSERT and the UPDATE into a stored procedure, that is called by the insertion logic. This would make a runaway situation with the resulting locking hell on catchup much less likely.
Eugen Rieck
  • 64,175
  • 10
  • 70
  • 92
  • Pretty sure I can't do point 1. Do you mean a temp table for point 2 and simply dump the temp table when done? Seems feasible since the table+indexes is only ~200mb. Could you elaborate why a proc would better maintain than a `TRIGGER`? Thanks for your knowledge! –  Jan 06 '13 at 18:55
  • With my 2nd bullet I meant `TYPE=MEMORY` (was `TYPE=HEAP` before 5.5). With the 3rd bullet I referred to a stored proc, as it has different locking characteristics than a trigger: The trigger will keep the row in `table1` locked while executing, the stored proc gives you the choice. – Eugen Rieck Jan 06 '13 at 19:03
  • Ah, well, I'm not so interested in releasing the table1 lock (should've been clearer). table2 is the prime concern since that's what's being constantly read (as well as written). What do you think of the temp table idea? Is a memory table faster than an ideally clustered InnoDB? Thanks again for your knowledge! –  Jan 06 '13 at 19:12
  • The in-memory table will release the lock much faster, as the lock is held until the write completes. This is obviously much faster (several orders of magnitude) with an in-memory table. – Eugen Rieck Jan 06 '13 at 19:36
  • So if I do it the digg way (highly targeted selects only by PK, no joins, etc), even though this http://dev.mysql.com/doc/refman/5.5/en/memory-storage-engine.html says "Despite the in-memory processing for MEMORY tables, they are not necessarily faster than InnoDB tables on a busy server, for general-purpose queries, or under a read/write workload. In particular, the table locking involved with performing updates can slow down concurrent usage of MEMORY tables from multiple sessions.". Is that still faster than dumping a temp table upon the table1 insert? Thanks again for all this knowledge! –  Jan 06 '13 at 19:43
  • Ofcourse you need to test, but this quite confirms my story: You don't have any general-purpose queries, you don't have a read/write workload (de-facto write only). In addition to that, IIUC, your workload doesn't come from many sessions, but from a single session doing the `INSERT`s – Eugen Rieck Jan 06 '13 at 19:47
  • OK. Last 2 questions: since the algorithm takes time to process, will there be locks on the mem table2 since another attempt to update may start while another update is happening? And, I'm reading from table2 1/2 as much as the UPDATE is writing. Does that still qualify as not being a read/write workload? Thanks again! –  Jan 06 '13 at 20:02
  • You really should try out - there are lots of variables around. My feeling is, that 1. Yes, there will be locks, but 2. lock **contention** will be manageable, as the table is small, so the updates are fast. – Eugen Rieck Jan 06 '13 at 20:05