Percona Server with XtraDB

logging of queries in memory

Registered by Evgeniy Stepchenko on 2009-02-13

Instead of writing into slow-log file, keep log records in memory and we should be able to retrieve them by SELECT * FROM PERCONA_SCHEMA.SLOW_LOG.

We should be able to configure how much memory allocate for buffer.

Feature is enabled disable by variable
query_log_memory=0 | N , where N is size of memory allocated to store information on queries, 0 - disables it.

Variable should be changed dynamically without server restart.

When feature is enabled we store information in allocated memory similar to what we have for slow.log ( see
http://www.percona.com/docs/wiki/patches:slow_extended). And information in available via PERCONA_SCHEMA.SLOW_LOG table.
E.g. table will have columns: timestamp, thread_id, schema, innodb_trx_id, errno etc.

IMPORTANT: table has additional column EVENT_ID, monotonically increased , so automatic scripts can use to
retrieve only queries from previous EVENT_ID.

IMPORTANT: memory is used to store only LAST M events, where M is chosen to fit all information into allocated N bytes.

All filters that applied to slow.log are applied to queries stored in memory.

===

Additional notes we can use AUDIT PLUGIN capabilities to make it as PLUGIN which work with both MySQL and Percona Server.
Example of plugin http://sourceforge.net/projects/sqlstats/
Sqlstats takes queries at the start of execution, but in AUDIT PLUGIN we can take events at the end of query execution, when we know execution time.
PLUGIN allows to pass only minimal information, that is we will not have innodb_trx_id and extra info what available in Percona Server,
but it is good for start.

====

It seems that MySQL 5.6 in PERFORMANCE_SCHEMA provides similar functionality.

There is a table
http://dev.mysql.com/doc/refman/5.6/en/events-statements-history-long-table.html
events_statements_history_long
which contains an information I am looking for.

So I propose we create a compatible solution for PS 5.5.
That is in Percona Server 5.6 we create table
PERFORMANCE_SCHEMA.events_statements_history_long
the size of this table is restricted by variable
performance_schema_events_statements_history_long_size.
But I want this variable to be runtime variable, not just startup.

Beside that table events_statements_history_long should be extented with columns
specific for Percona Server (i.e. innodb metrics)

If some columns we are not able to fill, we should fill them with NA.

Along with that, in Percona Server 5.6, we should also extend tables
events_statements_* with columns specific for Percona Server.

Blueprint information

Status:
Not started
Approver:
Vadim Tkachenko
Priority:
Low
Drafter:
None
Direction:
Needs approval
Assignee:
None
Definition:
Approved
Series goal:
Accepted for 5.5
Implementation:
Deferred
Milestone target:
None

Related branches

Sprints

Whiteboard

Notes:
- Using MySQL audt plugin and information service plugin interfaces seems very plausible.
- In XtraDB, the innodb status (trx_id, io_reads, etc...) is stored within 'class THD' which is passed into the audit plugin interface before query results are returned to the client. These same values are used in the XtraDB extended slow query log. In InnoDB, these status values do not exist in 'class THD' and as such, any plugin developed for this purpose will likely need conditional compilation for XtraDB functionality.

(?)

Work Items

This blueprint contains Public information 
Everyone can see this information.