logging of queries in memory
Instead of writing into slow-log file, keep log records in memory and we should be able to retrieve them by SELECT * FROM PERCONA_
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
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://
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
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
the size of this table is restricted by variable
But I want this variable to be runtime variable, not just startup.
Beside that table events_
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.
- 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.