WL#4878: PERFORMANCE_SCHEMA Trace

The initial description here has been shifted from the "Trace"
section of WL#2360 Performance Schema. This task depends on WL#2360.

"Trace" is not a requirement. However, this task description
must be here to show that, with WL#2360 as a basis,
we could produce trace files if we wanted.

Providers could dump to a trace file as well as copy to performance-structures.
It's certain to be slow (10%?) so doing it from the start would be an error.
The benefit is mostly to professional performance analysts.

Enabling Trace
--------------

There will be a new column in SETUP_INSTRUMENTS:
TRACE_ENABLED ENUM('yes','no')
It's in SETUP_INSTRUMENTS rather than SETUP_CONSUMERS because
it affects the instrument code path, and because we want to
enable/disable depending whether the event is a WAIT, a STATEMENT, etc.
The default value is 'no'.
It is an error to set TRACE_ENABLED='yes' if ENABLED='no'.

There will be a new column in the SETUP_ACTORS table (see WL#4674):
TRACE_ENABLED ENUM('yes','no')
The default value is 'no'.
Use of this column is affected by the FILE privilege and --secure-file-priv.

Trace file writing will occur for a particular instrumentation if:
SETUP_INSTRUMENTS.TRACE_ENABLED='yes', and
SETUP_ACTORS.TRACE_ENABLED='yes'.

There is no maximum length of a trace file.

Creating a trace file
---------------------

For each new thread,
or for each existing thread when a user updates a TRACE_ENABLED value:
  If (this thread is a thread for this setup_actors value)
    Set thread file name = datadir/"thread".actor.thread.
    For example, for datadir 'd' actor 'a' thread 1, = d/thread.a.1
    Create this file.

If a thread is associated with multiple actors,
for example host 'h' user 'a' role 'r', MySQL
will combine all names that fit the thread.

One File Per Thread
-------------------

We turn on tracing "per actor" but we have
one file "per thread". This is necessary
because we don't want two threads writing
to the same file at the same time -- either
thread#1 would wait for thread#2 (which is bad),
or thread#1 would not wait for thread#2 (which is bad).

It should be simple to make a utility
which combines all per-thread files and sorts
the rows by one of the timer columns (but not EVENT_ID
because EVENT_ID is now a per-thread counter).

Writing
-------

The instrument start_wait() and instrument end_wait()
code (both!) will write to the trace file.

There is no attempt to fsync, flush, or whatever.
You crash, you lose some of the trace.

The writing looks like
write(buffer);

This is a good possible spot for a plugin,
that is, we could replace "write(buffer)"
with "plugin(buffer)" if instead of writing
we wanted to examine the contents, cause a
crash, send an email, etc. We'll decide not
to do this.

File Errors
-----------

All kinds of errors can occur.
For example, the create fails due to privileges missing,
or the write fails due to full disk.

MySQL will not display an error message and stop
the statement processing. That is not what the
user, who is issuing the statement, expects.

MySQL will disable the trace for all threads
for the current actor. Thus, users will be able
to discover that a failure happened by seeing
that SETUP_ACTORS.TRACE_ENABLED becomes 'no'.
Thus, any error will disable tracing permanently.

Content and Format
------------------

This is a text file containing lines which
are equivalent to "events" rows in PERFORMANCE_SCHEMA
tables. Doubtless it would be quicker to dump raw
data from underlying structures, but when you ask
for a trace file you've given up on speed.
Doubtless it would be interesting to output
things in summary or state tables, but then
it wouldn't be tracing.

The header row is:
"/* MySQL Trace File for (actor_name) on (datetime) */".
This is redundant. Users can find out the
time the file was created, the actor, and the
thread, by saying 'ls -l'.

Each detail row takes one line and looks like an
INSERT statement, with all the columns of a row of
EVENTS_WAITS_CURRENT, EVENTS_STATEMENTS_CURRENT, etc.

Since different lines might be for rows of
different 'tables', a simple CSV format is not
possible. But it's easy to produce a database
file from INSERT statements.

Since writing occurs both before and after the
instrumentation point, everything is duplicated.

Example
-------

Suppose user Harry has thread 5.
Harry has privileges to update performance_schema tables.

Harry says:
UPDATE performance_schema.SETUP_INSTRUMENTS
  SET enabled='yes', trace_enabled='yes';
INSERT INTO performance_schema.SETUP_ACTORS
  VALUES ('%','%','%','yes');
This causes creation of trace files.

Harry now executes a statement
SET @a=5;
which happens to cause one mutex lock, no stages.

As a result, there is a trace file named
/usr/local/mysql/var/trace.harry.5
It contains
/* MySQL Trace File for Harry on 2009-04-07 14:18:00 */
INSERT INTO events_statements_current VALUES ('SET @a=5',...);
INSERT INTO events_waits_current VALUES ('mutex',...);
INSERT INTO events_waits_current VALUES ('mutex',...);
INSERT INTO events_statements_current VALUES ('SET @a=5',...);

To distinguish between 'start' and 'end' rows, check if TIMER_END is NULL.

Overhead
--------

The cost of taking a performance-struc and formatting it as
an INSERT statement is between 100 and 200 cycles. Most of
the time is string-move instructions. The cost of invoking
a write function is about 20 cycles on Peter's x86. The writing is deferred
so you don't immediately see the actual cost of writing to disk.
We have Kelly Long's test for "how many transactions per second on a
loaded system". We'll run it with "trace on and no update of
performance schema tables" versus "trace off and update of
performance schema tables".

mysqld option
-------------

Marc Alff pointed out: if one can enable a trace file with a mysqld option,
there's no need to worry about adding dozens more mysqld options that would
enable particular performance schema instruments for analyzing operations
that happen before the first user connects, for example crash recoveries.

For this, we could say that, if the server started with
mysqld --performance-schema-trace-enabled=true,
then any instrument which is 'enabled' is considered to be 'trace_enabled'.

More settings
-------------

We've said that there is no maximum length for a trace file,
and said that the destination is datadir/"thread".actor.thread.
But we know that users sometimes need to choose such things somehow.
So we could say that these things are
(a) specifiable in SETUP_INSTRUMENTS,
(b) specifiable via global variables similar to Oracle 11g's
    USER_DUMP_DEST and MAX_DUMP_FILE_SIZE,
(c) specifiable in one of the ways described by WL#3824 Resource Limits.

We've said that errors will be visible because SETUP_ACTORS.TRACE_ENABLED
becomes 'no'. But we can guess that users sometimes want to choose
what action to take on error. They could want to increment a counter,
or change file destination, or cause a warning from the SQL statement.