Wish list for MySQL thread polling events

It is great to draw inspiration from other Open Source communities. Brad Fitzpatrick recently wrote about Android Strict Mode. His twitter tag line for this post was “I see you were doing 120 ms in a 16 ms zone” which is all I needed to hear from somebody who also worries unreasonably about responsiveness (Web site quote).

How would I apply this to a MySQL context? This is what happens in Android. “Strict Mode lets you set a policy on a thread declaring what you’re not allowed to do on that thread, and what the penalty is if you violate the policy. Implementation-wise, this policy is simply a thread-local integer bitmask. By default everything is allowed and it won’t get in your way unless you want it to”

In a MySQL thread I would like to know certain things every time they occurs. Some events I’d consider relevant are:

  • Any disk writes (e.g. Created_tmp_disk_tables) occured
  • Any disk reads (e.g. data was not in buffer_pool for example)
  • Exceeded execution time obviously
  • Internal memory buffer usage, size/amount of read/read_rnd/join/sort buffer
  • Other paths triggered, e.g. Sort_merges, read from disk for table_open_cache etc

Also with this mode is not just the instrumentation of the thread, but the penalty if violated. Do you log, kill, change priority, report an annoying message (difficult in SQL world).
I feel it would be unwise to consider this on a per thread request due to the complexity of SQL, so the next consideration is how to you categorize or identify given SQL query paths to a given polling policy.

While I have already raised more questions then answers for a possible solution the principle is important in what can we do to make our job easier and more simpler. These appproaches are the next generation of requirements in performance tuning, where you are becoming proactive rather then reactive in identification, analysis and correction.