On Mon, 2010-11-01 at 21:15 +0000, RW wrote: > I don't think it's a matter of locking-out updates - presumably > token updates that occur after the start of a sync should go into the > new journal file. > It may easily be a locking problem: To quote from the MySQL manual:
MySQL uses table-level locking for MyISAM, MEMORY and MERGE tables, page-level locking for BDB tables, and row-level locking for InnoDB tables. IOW, if the OP is *not* using InnoDB for his token tables, then all locks are table locks and all update accesses, regardless of whether they are hitting a few rows such as adding tokens, updating counts in token or are processing the entire table, such as expiring tokens or processing the journal, will lock the entire table. As a result locking contention will be the norm rather than the exception and database performance will be sub-optimal. Migrating the tables to InnoDB storage should help considerably by minimising lock contention since table locks will be replaced by row locks. Its probable that changing over to direct token updating will also help performance, since it eliminates the periodic batch process that applies a large list of pending changes to the table. This process is both relatively long running and, worse, if it is a single transaction, will end up locking a fairly large proportion of the table. This in turn increases the probability that a row requested by SA will be locked by the batch update. OTOH, if SA applies the changes directly individual row locks are only held for the minimum time and the probability of locking conflicts are minimised because each update or query is only accessing a few rows in a large table. Martin
