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.

partitioning + auto_increment is buggy!

Folks, I just want to warn you how buggy partitioned tables with negative values are. Never use negative values for InnoDB auto_increment columns!!!!

• Assertion failed: next_insert_id >= auto_inc_interval_for_cur_row.minimum()
• partitioned innodb tables end up with duplicate primary key values!
• AUTO_INCREMENT errors with partitioned InnoDB tables in 5.1.31
• Assertion failure in file row/row0mysql.c line 1386

There are more bugs than I listed here..