Redesign pt-stalk

The overall goal is a tool that can be started with the system init scripts
automatically at boot, and simply run forever, watching the server and capturing
data if anything bad happens. This should just be a normal part of a MySQL
server that's run with "best practices".

1. Make a main() function

Make a main() function like a proper script has, and see if any parts of it are testable.

2. Change the default trigger

Change the default trigger to Threads_running=25

3. Abstract the trigger into a function

Make the trigger a function, not something that is executed as inline code. To
change the function, the user should change the $TRIGGER_FUNCTION environment
variables, and the tool should have these built-in functions:

  * processlist
  * status

The function should take a single argument, such as "Threads_running", and
return (print/echo) an integer. In main(), compare the return value to the
threshold, e.g.:

  if [ $(${TRIGGER_FUNCTION} ${VARIABLE}) -gt ${THRESHOLD} ]; then
    ...
  fi

4. Create a processlist function

The processlist trigger function requires an additional environment option:
MATCH. VARIABLE is the column from the processlist, and MATCH is what that
column should match. All matching processes are counted, and this number is
compared to THRESHOLD. For example:

  TRIGGER_FUNCTION="processlist" VARIABLE="State" MATCH="statistics" THRESHOLD="10" pt-stalk

That triggers whenever > 10 processes are in the statistics state.

5. [Deferred - "magic" triggering]

6. Make the tool daemonizable

Add a --daemonize option. When this option is given, the tool should fork
itself and then exit, something like this:

  run_main_loop "$@" </dev/null >output 2>&1 &

We can't use nohup because it can only run a program, not a shell function. Be
certain that detecting --daemonize happens after parsing command-line options so
that mistakes are detected and printed to the terminal.

7. Make the tool accept command-line options

Use command-line options with defaults, instead of just hardcoding variables
into it. Ignore environment variables -- just use commandline options. Use the
following options:

--collect negatable; do (not) collect if triggerd (default yes)
--collect-gdb boolean; replaces GDB env var
--collect-oprofile boolean; replaces OPROFILE env var
--collect-strace boolean; replaces STRACE env var
--collect-tcpdump boolean; replaces TCPDUMP env var
--config Same as Perl tools
--cycles N (default 5) (this is a change)
--daemonize
--dest DIR (/var/lib/pt-stalk)
--disk-bytes-free replaces MB_THRESHOLD (default 100M)
--disk-pct-free replaces PCT_THRESHOLD (default 5) (reversed meaning)
--function FUNCTION (default status)
--help
--interval SECONDS (default 1s) (this is a change)
--iterations N (default none; infinite)
--log FILE (default /var/log/pt-stalk.log)
--match PATTERN (no default)
--notify-by-email EMAILS; replaces EMAIL (no default)
--pid FILE (default /var/run/pt-stalk.pid)
--prefix
--retention-time DAYS; replaces PURGE (default 30d)
--run-time SECONDS; replaces DURATION (default 30s)
--sleep SECONDS (default 300s)
--threshold N (default 25)
--variable VARIABLE (default Threads_running)
--version

Any additional command-line arguments are used as MySQL options, the same as
pt-mysql-summary.

Get rid of MAYBE_EMPTY behavior. Grep -c and other techniques can do the same
thing without messy code. If the return from the function isn't a number, it
shouldn't trigger anything.

--iterations is so that we can stop the tool after it catches a condition. At the default of 0, it will fire pt-collect, then start watching again after --sleep seconds, and continue forever. If we set it to 1, it will fire, then exit. We might want this behavior so that we can catch a single sample, but not risk the tool being disruptive by running repeatedly. Of course we could set it to 2, 3, etc also... but I expect we will usually set it to 1 or leave it at 0.

8. Make the tool read a proper configuration file

The semantics and locations should be exactly the same as the Perl tools.

9. Make the trigger function possible to load as a plugin

If the --function option is a file, then it should be sourced with "." and the trigger function should be assumed to be "trg_plugin". If the file can't be sourced the tool should exit with an error. This should allow us to write a plugin module with contents such as the following:

trg_plugin() {
   # Do some stuff
}

And then just execute the tool with the filename as the argument to --function, e.g. --function /path/to/plugin.sh

10. Use a temporary directory

Don't put any files in global /tmp places, create a temp directory with mktemp.

11. Bundle pt-collect and pt-stalk together

Make pt-stalk and pt-collect into a single tool. Make it possible to ignore the "stalking" behavior when you just want to manually collect some data.

12. Collect information more frequently.

Collect the following each cycle in the main "collect" loop:

  * SHOW FULL PROCESSLIST
  * InnoDB lock info from I_S (see 15)

13. Don't collect possibly risky information.

Before "collect" executes SHOW OPEN TABLES, check the value of Open_tables, and don't run SHOW OPEN TABLES if it's greater than 1000.

14. Make the tool be more cautious about disk space usage

In addition to refusing to run if the disk is too full at present, the tool should sum up the sizes of the files in the most recent sample that exists in the destination directory, and refuse to run if the disk would be too full after gathering another sample of the same size. If there is no such sample, it should assume 20MB.

15. Collect information from InnoDB lock tables

Make "collect" execute the following queries in the main loop:

   SELECT CONCAT('thread ', b.trx_mysql_thread_id, ' from ', p.host) AS who_blocks,
          IF(p.command = "Sleep", p.time, 0) AS idle_in_trx,
          MAX(TIMESTAMPDIFF(SECOND, r.trx_wait_started, CURRENT_TIMESTAMP)) AS max_wait_time,
          COUNT(*) AS num_waiters
   FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS AS w
      INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS b ON b.trx_id = w.blocking_trx_id
      INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS r ON r.trx_id = w.requesting_trx_id
      LEFT JOIN INFORMATION_SCHEMA.PROCESSLIST AS p ON p.id = b.trx_mysql_thread_id
   GROUP BY who_blocks ORDER BY num_waiters DESC\G

   SELECT r.trx_id AS waiting_trx_id, r.trx_mysql_thread_id AS waiting_thread,
          TIMESTAMPDIFF(SECOND, r.trx_wait_started, CURRENT_TIMESTAMP) AS wait_time,
          r.trx_query AS waiting_query,
          l.lock_table AS waiting_table_lock,
          b.trx_id AS blocking_trx_id, b.trx_mysql_thread_id AS blocking_thread,
          SUBSTRING(p.host, 1, INSTR(p.host, ':') - 1) AS blocking_host,
          SUBSTRING(p.host, INSTR(p.host, ':') +1) AS blocking_port,
          IF(p.command = "Sleep", p.time, 0) AS idle_in_trx,
          b.trx_query AS blocking_query
   FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS AS w
      INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS b ON b.trx_id = w.blocking_trx_id
      INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS r ON r.trx_id = w.requesting_trx_id
      INNER JOIN INFORMATION_SCHEMA.INNODB_LOCKS AS l ON w.requested_lock_id = l.lock_id
      LEFT JOIN INFORMATION_SCHEMA.PROCESSLIST AS p ON p.id = b.trx_mysql_thread_id
   ORDER BY wait_time DESC\G

16. Collect information from userstats

Make the tool support a --collect-userstats option or similar, which defaults to false. If true, the tool should check userstat_running at the beginning, and if it's disabled, enable it for the duration of the run. If it's enabled, or if it can be enabled successfully, then do a SELECT from each of the userstats tables into a file at the beginning and end of execution.

17. Collect XA RECOVER information

Save XA RECOVER's output into a file.

18. Log matching options.

When tool starts, print its current matching expression options in a log line, and then re-do this when it runs the collect sub, so we have a record of what it was matching against. I'd like to see "Threads_running 25, 5 times" or something so I know how the tool was run. We shouldn't print the whole $@ because that might have a mysql password in it.