The MySQL/MariaDB optimiser likes to know things like the cardinality of an index – that is, the number of distinct values the index holds. For a PRIMARY KEY, which only has unique values, the number is the same as the number of rows.  For an indexed column that is boolean (such as yes/no) the cardinality would be 2.

There’s more to it than that, but the point is that the optimiser needs some statistics from indexes in order to try and make somewhat sane decisions about which index to use for a particular query. The statistics also need to be updated when a significant number of rows have been added, deleted, or modified.

In MyISAM, ANALYZE TABLE does a tablescan where everything is tallied, and the index stats are updated. InnoDB, on the other hand, has always done “index dives”, looking at a small sample and deriving from that. That can be ok as a methodology, but unfortunately the history is awkward. The …

When InnoDB compresses a page it needs the result to fit into its predetermined compressed page size (specified with KEY_BLOCK_SIZE). When the result does not fit we call that a compression failure. In this case InnoDB needs to split up the page and try to compress again. That said, compression failures are bad for performance and should be minimized.

Whether the result of the compression will fit largely depends on the data being compressed and some tables and/or indexes may contain more compressible data than others. And so it would be nice if the compression failure rate, along with other compression stats, could be monitored on a per table or even on a per index basis, wouldn't it?

This is where the new INFORMATION_SCHEMA table in MySQL 5.6 kicks in. INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX provides exactly this helpful information. It contains the following fields:

MySQL provides a level of statistics for your INSERT, UPDATE, DELETE, REPLACE Data Manipulation Language (DML) commands using the STATUS output of various Com_ variables, however it is per server stats. I would like per table stats.

You can achieve this with tools such as MySQL Proxy and mk-query-digest, however there is actually a very simple solution that requires no additional tools.
The following 1 line Linux command (reformatted for ease of reading) gave me exactly what I wanted, and it had ZERO impact on the database.

$ mysqlbinlog /path/to/mysql-bin.000999 |  \
   grep -i -e "^update" -e "^insert" -e "^delete" -e "^replace" -e "^alter"  | \
   cut -c1-100 | tr '[A-Z]' '[a-z]' |  \
   sed -e "s/\t/ /g;s/\`//g;s/(.*$//;s/ set .*$//;s/ as .*$//" | sed -e "s/ where .*$//" |  \
   sort | uniq -c | sort -nr  

  33389 update e_acc
  17680 insert into r_b
  17680 insert into e_rec
  14332 insert into rcv_c
  13543 update e_rec
  10805 …

If you’ve looked at your WordPress.com blog stats today, you might have noticed the charts look a little different. We’ve replaced the old proprietary chart object with Open Flash Chart, an open source alternative.  Charts now look like this:

(Though I can’t guarantee you’ll see numbers like that).

All the old charts are still available in more or less the same form.  And we’re hoping to explore some of the new possibilities Open Flash Chart has to offer – so keep an eye on your stats.  Like we had to ask.

And in case you missed it: yes, blog stats now work in your time zone.

…

In April we introduced Instant Findability, TED video embeds, a springtime theme, a cool new domain, and reply-by-email for comments, now open to all. A pretty busy month, with more awesome features on the way, plus WordCamp San Francisco, on May 30.

If you’re in town or want a reason to be, be sure to register soon. We’ve …

When we sat down at an Austin cafe in 2005 and wrote the stats system, Matt and I had no idea what we were getting into. He created the databases and drew the little smiley face while I wrote the code. We had milk and cookies. It was really cute. We were naïve!

I swear it was Matt’s idea to store stats data as daily summaries in Universal Coordinated Time (UTC), which is why stats days have always ended at odd hours for non-Greenwichians. But even if I seem blameless, I failed to champion your cause soon enough. It is even more my fault than Matt’s.

And so today I present a gift. If you have set your blog’s time zone, your stats reports will honor that setting (in whole hours from -12 to +14). This upgrade is retroactive to the beginning of 2009. It affects all blogs using WordPress.com stats, even self-hosted blogs using the Stats …