Tag: hack

Opening tables v2!

PMP on demand revealed one of reasons why we’ve been seeing ‘Opening tables’ during proper operations, not just during startup (see my previous post on this topic).

We had a thousand or so threads waiting on LOCK_open, and the only thread holding the mutex was this darling:

Thread 902 (Thread 1637624128 (LWP 22113)):
#3  mutex_spin_wait (mutex=0x2aaaac3232b8,
        file_name=0x8b3bf7 "trx0trx.c", line=220) at sync0sync.c:565
#4  trx_allocate_for_mysql
#5  ha_innobase::allocate_trx
#6  check_trx_exists
#7  ha_innobase::info
#8  ha_innobase::open
#9  handler::ha_open
#10 openfrm
#11 open_unireg_entry
#12 open_table
#13 open_tables
#14 open_and_lock_tables
#15 mysql_insert

So, kernel mutex, which is quite contended, will escalate to LOCK_open on table open, which will block all queries from happening on the server. Fix? Nearly same code as previous “Opening tables” fix – don’t call ha_innobase::info() as part of open(), or move the transaction establishment code outside of info().

stupid dtrace trick

So, if there is no way to flush status variables of a server, one could use dtrace (and run SHOW GLOBAL STATUS ;-)

#!/usr/sbin/dtrace -Fws
int *zero;
BEGIN { zero=alloca(4); *zero=0; }
pid$target:mysqld:add_to_status*:1b {
        copyout(zero,uregs[R_ECX],4);
        copyout(zero,uregs[R_EDX],4);
}

P.S. This is just simple proof of concept, that can warp counters back, ignore some variables, and eat babies. Code position and registers are architecture, version and build dependent.
P.P.S. I’m giving dtrace talk at mysql conference as well (yay plugs ;-)

stupid innodb tricks

When it comes to extreme tuning, this is how I got +5% performance in one of my tests on 8-cpu machine (contended at mtr_commit()):

gdb -p $(pidof mysqld) -ex "set srv_spin_wait_delay=50" -batch

P.S. was probably worth posting this just to point out that there’re ways to fill the gap between recompile and my.cnf settings :)