quick update from down under...

I'm pleased to report that so far the Soccer World Cup has been pulled off rather successfully, with only minor incidents reported. I have however noticed local news showing some 'feel-good' stories that are obviously written to give a false impression to the international media of the real situation here. Let's hope the unions and Eskom workers don't mess things up by holding a gun to the Country's head with protesting/striking too much in the international media's light.. Hold thumbs..

BTW, I hate the vuvuzela :)

I thought I should share two important MySQL bugs with you today. In case you ever used YaSSL to establish SSL connections, you were at risk of hitting random crashes due to bug #34236 (Various possibly related SSL crashes) if more than one concurrent connection was ever made. The reason is the YaSSL code was built without mutexes as if for single threaded apps...

Next bug I think is widespread enough to mention is optimizer/query plan related. Examine the testcase on bug #48537 (difference of index selection between rpm binary and .tar.gz, windows vs linux..) And read the changeset notes:

   On Intel x86 machines index selection by the MySQL query
   optimizer could sometimes depend on the compiler version and
   optimization flags used to build the server binary.

   The problem was a result of a known issue with floating point
   calculations on x86: since internal FPU precision (80 bit)
   differs from precision used by programs (32-bit float or 64-bit
   double), the result of calculating a complex expression may
   depend on how FPU registers are allocated by the compiler and
   whether intermediate values are spilled from FPU to memory. In
   this particular case compiler versions and optimization flags
   had an effect on cost calculation when choosing the best index
   in best_access_path().

   A possible solution to this problem which has already been
   implemented in mysql-trunk is to limit FPU internal precision
   to 64 bits. So the fix is a backport of the relevant code to
   5.1 from mysql-trunk.

Now I'll get back to enjoying the public holiday and bugs reporting ;-)

vmstat/iostat replacement for windows ?

I dislike the old perfmon interface and it's unreadable graphs and logs. For a long time I've been searching for a basic vmstat and/or iostat windows port, and one that doesn't rely on that nonsensical cygwin. If anybody knows of one, please leave a comment.

Here's a proof on concept I cooked up in 20 minutes using the PDH (performance data helper) functions. In a nutshell, it queries the PDH counters directly and I'm be free to display

the data however I like.

Here's what I got so far:

proc_q_len  pagefile  interrupt/s   cswitch/s  %cpu_user  %cpu_sys  %cpu_idle  %disk_busy   %disk_read  %disk_write
  10      2308780032  2016          2555         64         26         9         100            0           100
   3      2308911104  2863          3669         62         13        23         100            1           100
   0      2309206016  1857          3057         79         10         9           2            0             2
   0      2310217728  2579          3664         64          9        26         100            0           100
   1      2309140480  2195          2985         71          3        25         100            0           100
   2      2309140480  2241          3042         76          4        18         100            0           100

Not perfect, and I'm still trying to devise a proper layout for the stuff I want to display.

The columns shown correspond to the counters:

• \System\Processor Queue Length
• \Process(_Total)\Page File Bytes
• \Processor(_Total)\Interrupts/sec
• \System\Context Switches/sec
• \Processor(_Total)\% User Time
• \Processor(_Total)\% Privileged Time
• \Processor(_Total)\% Idle Time
• \PhysicalDisk(_Total)\% Disk Time
• \PhysicalDisk(_Total)\% Disk Read Time
• \PhysicalDisk(_Total)\% Disk Write Time

The general flow of code is like this (there are samples online):

1. PdhOpenQuery

2. PdhAddCounter for each counter

3. for each X seconds:

3.1 PdhCollectQueryData

3.2 PdhGetFormattedCounterValue for each counter

3.3 print the result for each counter

4. PdhCloseQuery

Beware of RBR and tables without indexes

I always knew RBR and unindexed tables didn't play along very well, but never realized just how much you can distress a slave can in some cases.
Consider this statement (yeah yeah, i know :)

mysql> delete from t1 order by rand();

Query OK, 78130 rows affected (2.61 sec)

t1 has no indexes and is an int field with numbers from 1 to 78130. However, this will cause the slave to re-read entire table for each row deleted! Here it's still running, causing 100% cpu usage:

---TRANSACTION 0 1799, ACTIVE 2390 sec, OS thread id 3672 fetching rows mysql tables in use 1, locked 1 153 lock struct(s), heap size 30704, 78281 row lock(s), undo log entries 35423

Number of rows inserted 78130, updated 0, deleted 35423, read 1076560253 0.00 inserts/s, 0.00 updates/s, 17.58 deletes/s, 367099.91 reads/s

Over a billion row reads 40 minutes later and it's not even half done yet.For a large table this could take weeks or years to complete. It would be nice if there was a way to prevent this situation from happening.

debugging mysqld corefile on AIX

I recently had the pleasure of logging into an AIX 5.3 machine for the first time ever, to debug a corefile.

Firstly, having the mysqld binary and core is not enough, unless you have an identical machine on which to study the corefile. Library mismatches can be a problem.. IBM was kind enough to provide the snapcore utility to solve this easily.

Snapcore will gather all the libraries and create a single archive contain libs, binary, core.

So we now have a file called something like: snapcore_555060.pax

On our dev box, extract the pax archive:

gunzip snapcore_555060.pax.Z

pax -r -f snapcore_555060.pax

On your dev AIX box, make sure you have DBX installed!!

bash-3.00# lslpp -l | grep bos.adt.debug

bos.adt.debug 5.3.8.0 COMMITTED Base Application Development

Now, we are ready to debug a core. But we have to instruct DBX to read the libraries

that we got from the pax archive, instead of the default libraries on this system.

dbx -F -p /usr=/home/sbester/core/usr ./mysqld ./core

And since this isn't a DBX tutorial, I'll stop here, but you'll use normal DBX commands to print a stack trace, move up/down frames, print variables, list the source code.

a little challenge

How do you make mysqld write a DELETE to the binlog just by entering a SELECT statement ?
No triggers and no stored routines/functions are involved.