You probably haven’t noticed but I have not blogged since the UC. It is not because I am upset by the perspective of working for Oracle, I have simply been busy tracking down an issue we have with WaffleGrid. We discovered that under high load, with DBT2 on a tmpfs, we end up with error in a secondary index. In the error of MySQL, we have entries like this one:
InnoDB: error in sec index entry update in InnoDB: index `myidx1` of table `dbt2`.`new_order` InnoDB: tuple DATA TUPLE: 3 fields; 0: len 4; hex 80000001; asc ;; 1: len 4; hex 80000bea; asc ;; 2: len 4; hex 80000005; asc ;; InnoDB: record PHYSICAL RECORD: n_fields 3; compact format; info bits 32 0: len 4; hex 80000001; asc ;; 1: len 4; hex 80000bea; asc ;; 2: len 4; hex 80000004; asc ;; TRANSACTION 14469, ACTIVE 1 sec, process no 7982, OS thread id 2995481488 updating or deleting mysql tables in use 1, locked 1 26 lock struct(s), heap size 2496, 65 row lock(s), undo log entries 60 MySQL thread id 31, query id 1246503 localhost root updating DELETE FROM new_order WHERE no_o_id = 3050 AND no_w_id = 1 AND no_d_id = 5
That are triggered when transactions are purged. Basically, an entry in the secondary index has to be deleted an when MySQL access the page, the row is missing.
Matt and I have dig this issue to the limit of our sanity and although we gained knowledge of the InnoDB code, we are still stuck. Basically what we are looking for is a way for a file page to go to disk without hitting the LRU list or a possibility to have 2 pages in the buffer pool with the same space_id:offset pair. Anyone who has inputs on these topics, please, comments this post…