You are here

Home

innodb_checkpoint_age in plain MySQL?

Sat, 2014-12-27 15:45 — oli

In Percona Server we have a STATUS variable indicating roughly possible recovery time:

Combined size of InnoDB log files defines how many changes not reflected in the tablespace we may have where innodb_checkpoint_age shows how much changes we actually have at the current moment, being an actual driving factor of recovery time. If you have very large log files allocated but for your workload innodb_checkpoint_age stays low chances are recovery will be quick. [ InnoDB crash recovery speed in MySQL 5.6 ]

These numbers are not available in plain MySQL. How can we calculate this value in plain MySQL?

Taxonomy upgrade extras: 
innodb
MySQL Architecture and Design

Looking at Percona Server source code we can find the following:

storage/innobase/srv/srv0srv.cc

export_vars.innodb_checkpoint_age     = (log_sys->lsn - log_sys->last_checkpoint_lsn);
export_vars.innodb_checkpoint_max_age = log_sys->max_checkpoint_age;

Looking at the code we can see how output of SHOW ENGINE INNODB STATUS\G is produced:

storage/innobase/log/log0log.cc

void log_print(FILE* file)
{

 fprintf(file,
         "Log sequence number " LSN_PF "\n"
         "Log flushed up to   " LSN_PF "\n"
         "Pages flushed up to " LSN_PF "\n"
         "Last checkpoint at  " LSN_PF "\n",
         log_sys->lsn,
         log_sys->flushed_to_disk_lsn,
         log_buf_pool_get_oldest_modification(),
         log_sys->last_checkpoint_lsn);

 fprintf(file,
         "Max checkpoint age    " LSN_PF "\n"
         "Checkpoint age target " LSN_PF "\n"
         "Modified age          " LSN_PF "\n"
         "Checkpoint age        " LSN_PF "\n",
         log_sys->max_checkpoint_age,
         log_sys->max_checkpoint_age_async,
         log_sys->lsn - log_buf_pool_get_oldest_modification(),
         log_sys->lsn - log_sys->last_checkpoint_lsn);

Further looking at the output of SHOW ENGINE INNODB STATUS\G we can see how those values are gathered:

---
LOG
---
Log sequence number 1609201967  --> log_sys->lsn
Log flushed up to   1609201967  --> log_sys->flushed_to_disk_lsn
Pages flushed up to 1609201967  --> log_buf_pool_get_oldest_modification()
Last checkpoint at  1609201967  --> log_sys->last_checkpoint_lsn
Max checkpoint age    869019772 --> log_sys->max_checkpoint_age
Checkpoint age target 841862905 --> log_sys->max_checkpoint_age_async
Modified age          0         --> log_sys->lsn - log_buf_pool_get_oldest_modification()
Checkpoint age        0         --> log_sys->lsn - log_sys->last_checkpoint_lsn
0 pending log writes, 0 pending chkp writes
8 log i/o's done, 0.00 log i/o's/second

Benchmarks of Percona have shown recovery speed of 4 - 8 Mbyte/s (CPU and/or I/O bound).

Another rough indication would be the number of dirty blocks and bytes Innodb_buffer_pool_pages_dirty and Innodb_buffer_pool_bytes_dirty.

Shinguzcomment Sat, 2014-12-27 15:56