Reference:-
https://blogs.oracle.com/MySQL/entry/comparing_innodb_to_myisam_performance
http://www.tocker.ca/2013/05/06/when-does-mysql-perform-io.html
https://www.percona.com/blog/2011/03/31/innodb-flushing-a-lot-of-memory-and-slow-disk/
https://www.percona.com/blog/2011/04/04/innodb-flushing-theory-and-solutions/
When does MySQL data get loaded in and out of cache?
A cold cache, or a poorly tuned cache can be responsible for a number of performance problems. If we look at the data and indexes of InnoDB, the cache responsible is called the InnoDB buffer pool.
In earlier versions of MySQL the default value for the settinginnodb_buffer_pool_size was 8M - which is a little out of date when you consider the recommended value to be 50-80% of system memory. In MySQL 5.5 the default value was increased to 128M, which is a comprimise made for users that may install MySQL on a development machine and not have it running as a dedicated server. In production, it is unlikely that you will buy a new server with less than 64GB of RAM, so it is quite typical that this setting is 50GB+
So lets talk about the behaviour of the InnoDB buffer pool -
Up until and including MySQL 5.5
When MySQL starts up, the buffer pool is completely empty. As queries are executed, MySQL will determine which pages are required - and if they are not in memory, they will be loaded from disk.
Initially, performance will be very poor - since there will be 100% cache misses, but over time as the buffer pool fills this should reduce.
Provided there is enough memory - MySQL will just keep loading pages into cache, and warm to a state where it will not have to perform any reads to the disk at all. Only writes will go to disk.
From MySQL 5.6 onwards
Because server warm time is becoming a more serious problem (it always existed, but it is exacerbated by us now having an abundance of RAM, but if we are using hard drives, they are not really any faster), Oracle introduced a feature in MySQL 5.6 - buffer pool warming.
What it does is on demand or on shutdown saves the addresses (space_id + page_id) of InnoDB pages to permanent storage.
Upon startup, these pages can then automatically be loaded back into memory straight away so that we can have cache misses for less time.
This feature is not turned on by default, but I suspect in a future version it may be. There are a number of other advantages to pre-warming such as being able to sort and merge read requests and load the data in much faster. Also, since it is only the addresses of pages being saved it only takes 8 bytes to point to each 16KB page, and there are no risks if there have been modifications since the last address saving operation ran.
Cache evictions
So we've described the "best case" so far, which is that data only gets loaded into cache and performance keeps getting better and better. However in practice, our cache is never unlimited. When our buffer pool gets to the point of being completely full it will need to free space in order to load new pages into memory. The behavior of this 'eviction' is version specific.
Up until MySQL 5.5
InnoDB previously used a classic Least Recently Used (LRU) algorithm. What this means is that each page has an order in a list. I like to think of it as a "Hot or not" score. Each time a page is accessed it gets promoted up the list to be considered "more hot". When it comes to freeing space, InnoDB just picks the least hot page and frees it from memory to make space for the next page.
In typical operation MySQL will not free pages from memory unless it needs to make space for another page. I say 'typical', because pages will be freed if a table is dropped for example.
MySQL 5.5 ONWARDS
There are a number of operational limitations with using a classic LRU implementation on a database which has many users, and typically more than one workload. For example lets say that everything is working fine and we've figured out what is "hot or not", noting that we do not need as much memory as we do data because in most cases there will be pages that are not accessed frequently. Now imagine that a series of queries come in from mysqldump that want to run tablescans to export the data. What can happen, is the pages loaded in from the tablescans push out some of the good pages in our buffer pool, and even worse, as soon as the mysqldump operation is complete, they will no longer be required. So now post-mysqldump we have random IO as we try and re-settle cache-contents again.
So in MySQL 5.5 the LRU was split into two sublists:
- A 'young' sublist for our frequently accessed pages (default: 63%)
- An 'old' sublist for our table scan page accesses (default: 37%)
The configuration variable innodb_old_blocks_pct was introduced to configure the old/new split, and a variable innodb_old_blocks_time(default in 5.5: 0, in 5.6: 1000) was introduced to specify a minimum amount of milliseconds that a freshly loaded page must stay in the old sublist before it is ellible to be promoted to the young sublist.
As mentioned in the manual, and in blog posts it frequently makes sense to configure the innodb_old_blocks_time to a larger value, for example 1000 (ms).
A note on page churn
If you do not have enough memory to hold all of your working set in memory, what will happen is that the buffer pool will start juggling as it churns pages out of memory only to load them back in soon after. A small amount of this is probably okay, but it can start to degrade performance. The traditional rule of thumb is called "the 5 minute rule". This means that if you load a page into memory and are going to need it again in the next five minutes - it should not need to be churned out.
The goal of caching is to avoid having to perform disk IO, which if we are talking about hard drives, each operation is about 20 million times slower than CPUs or about 100 thousand times slower than memory.
So today I want to cover the subject of when are we required to do IO?But before I get there I first have to describe how IO works on all our modern operating systems.
An Introduction to Buffered IO
When you read or write to a file, by default you are speaking to a layer in between you and your disks called buffered IO. This layer is designed to increase performance and adds important features such as caching. Without going into this in detail, it's important to note that:
When you READ data, you may be reading from the disk or a cache provided by the operating system.
When you WRITE data you are not writing to the disks, but to a queue which will be flushed in the background. This queue is a little bit different to how a message queue in an application may behave: it usually has a hard limit to the number of outstanding requests, and it does not always complete requests in purely FIFO order - but an optimized order that still makes sure there is a maximum time window a request can take (example).
WRITE operations are not crash-safe by default! The operating system does provide a way of syncing a file to make sure all of the buffers are flushed down to the disk; an fsync.
An fsync is usually very expensive because it forces the queue to empty, and reduces the amount of request reordering and merging. Sequential IO is really important for hard drives.
So now lets look at IO created by a MySQL Server using the InnoDB storage engine (default since MySQL 5.5).
Reading pages into memory
If a page is not located in cache already, InnoDB will load it into memory in the foreground while queries are waiting, unless the page loading was triggered via a read ahead operation in which case it will use one of its innodb_read_io_threads to make this happen in the background.
Writing modified pages back to disk
When you make a modification to an InnoDB page, InnoDB does notwrite it immediately to disk. Instead a delta is written to the transaction log (see below).
These modified-only-in-memory pages are typically reffered to as dirty pages. Writing down dirty pages happens as part of InnoDB's background process which continually performs work every 1 second, and it adjusts well to peaks and troughs in load with a feature introduced in MySQL 5.5 and further improved in MySQL 5.6 calledadaptive flushing. However, if there are too many dirty pages and there is not enough free space, InnoDB may be forced to synchronously flush a dirty page to make free space.
This delaying allows InnoDB to perform fewer random writes, since there will usually be multiple changes to the same page, or by default pages in the same extent (1MB allocation unit on disk). MySQL also 5.6 allows you to disable this for SSDs.
(Note: Domas makes a comment about the doublewrite buffer below, and how I might be over-selling request merging :) It's outside of the scope covered here, but described in Facebook's blog and the MySQL manual).
Writing the transaction log
Any time you modify an InnoDB page a record of this modification is made to InnoDB's transaction log system (ib_logfile1 and ib_logfile0on disk). This transaction log is designed to be sequentially written, and unless the server crashes the server will only ever write and not read from it.
The log files are cyclical by design. You can think of them as a concatentation of two (by default) that behave similar to tread on a tank. So the first one is filled, then the second, then InnoDB will start filling the first again. The writes to the transaction log will be 512B aligned and using the operating system's buffered IO. InnoDB actually relies on the operating system here to provide a cache of the log (which is not guaranteed). Since these writes are 512B - and a typical page is 4K what will happen without enough cache, is that the page will neeed to be read first, to make the partial-modification, then written back.
To reduce log file IO, InnoDB also has a log buffer (configured byinnodb_log_buffer_size) that is filled with pending modifications which will be written and synced on any COMMIT by default.
Writing the slow log and general log
The general log is written out as each event is generated. It will be a sequential write, and is never synced or never read back (should be fairly low cost).
The slow query log is written out after query execution has finished. Similiar to the general log, it should also be very log cost.
Why I say "should" is that this also depends on filesystem choice. Anfsync under ext3 does not flush one file, but all files. This has become known as the great fsync() bug. Also, the operating system will usually have a limit the amount of modifications queued up waiting to be written, so too much log activity could cause synchronous writing and impact other operations (see Domas' comment below).
IO on a Replication Master (aka binary log enabled)
The binary log is sequential IO written to as part of a COMMIToperation, but by default it is never flushed, so it is not crash-safe! It requires setting sync_binlog=1.
The binary log does not have its own in memory buffers, so it's possible if an out of date slave came online that the master server may need to read old binary log files in order to send previous server events. However in practice the master pushes changes to online slaves as they are committed so it should be write-only.
The server maps changes in the binary log to InnoDB's transaction log to ensure consistency by default.
IO on a Replication Slave
The relay logs are in the same format as the binary logs, but the IO semantics are a little different. The slave's IO_THREAD retrieves new events from the master and writes sequential IO relay log files on the slave. Then the SQL_THREAD then reads the changes from the relay logs and applies them. This read operation does not have any caches inside of MySQL to ensure no IO is performed, and instead relies on the operating system's buffered IO to assist. It's possible to decrease the amount of relay logs kept on the slave to assist in a better 'cache fit', but this also means that if the master fails there may be changes the slave was not aware of.
Writing the relay logs is not crash-safe by default, and requires settingsync_relay_log=1.
Also, the status files which maintain a mapping between the relay logs and master's binary logs (which are not a 1:1) are also not crash-safe. A new option in MySQL 5.6 allows you to use an internal table to maintain these status files for added safety, but it is not enabled by default. The new options are master-info-repository and relay-log-info-repository.
Summary
I am sure I made a couple of simplifications (for example not describing the doublewrite buffer), but hopefully it's a good start. There are a couple of key take away points I want to make:
Reads are foregrounded (except read-ahead) and don't provide the same opportunity reorder and merge - so that makes them typically random IO and very expensive. If you run a diagnostic command like iostat and you see a lot of reads on a warmed up server - this is something adding memory can usually solve.
Most writes are sequential or backgrounded. Both InnoDB and the operating system try to produce sequential IO and merge requests.
While InnoDB has always been crash safe by default, MySQL has not. There are new features in MySQL 5.6 which makes replication more reliable
https://blogs.oracle.com/MySQL/entry/comparing_innodb_to_myisam_performance
http://www.tocker.ca/2013/05/06/when-does-mysql-perform-io.html
https://www.percona.com/blog/2011/03/31/innodb-flushing-a-lot-of-memory-and-slow-disk/
https://www.percona.com/blog/2011/04/04/innodb-flushing-theory-and-solutions/
When does MySQL data get loaded in and out of cache?
A cold cache, or a poorly tuned cache can be responsible for a number of performance problems. If we look at the data and indexes of InnoDB, the cache responsible is called the InnoDB buffer pool.
In earlier versions of MySQL the default value for the settinginnodb_buffer_pool_size was 8M - which is a little out of date when you consider the recommended value to be 50-80% of system memory. In MySQL 5.5 the default value was increased to 128M, which is a comprimise made for users that may install MySQL on a development machine and not have it running as a dedicated server. In production, it is unlikely that you will buy a new server with less than 64GB of RAM, so it is quite typical that this setting is 50GB+
So lets talk about the behaviour of the InnoDB buffer pool -
Up until and including MySQL 5.5
When MySQL starts up, the buffer pool is completely empty. As queries are executed, MySQL will determine which pages are required - and if they are not in memory, they will be loaded from disk.
Initially, performance will be very poor - since there will be 100% cache misses, but over time as the buffer pool fills this should reduce.
Provided there is enough memory - MySQL will just keep loading pages into cache, and warm to a state where it will not have to perform any reads to the disk at all. Only writes will go to disk.
From MySQL 5.6 onwards
Because server warm time is becoming a more serious problem (it always existed, but it is exacerbated by us now having an abundance of RAM, but if we are using hard drives, they are not really any faster), Oracle introduced a feature in MySQL 5.6 - buffer pool warming.
What it does is on demand or on shutdown saves the addresses (space_id + page_id) of InnoDB pages to permanent storage.
Upon startup, these pages can then automatically be loaded back into memory straight away so that we can have cache misses for less time.
This feature is not turned on by default, but I suspect in a future version it may be. There are a number of other advantages to pre-warming such as being able to sort and merge read requests and load the data in much faster. Also, since it is only the addresses of pages being saved it only takes 8 bytes to point to each 16KB page, and there are no risks if there have been modifications since the last address saving operation ran.
Cache evictions
So we've described the "best case" so far, which is that data only gets loaded into cache and performance keeps getting better and better. However in practice, our cache is never unlimited. When our buffer pool gets to the point of being completely full it will need to free space in order to load new pages into memory. The behavior of this 'eviction' is version specific.
Up until MySQL 5.5
InnoDB previously used a classic Least Recently Used (LRU) algorithm. What this means is that each page has an order in a list. I like to think of it as a "Hot or not" score. Each time a page is accessed it gets promoted up the list to be considered "more hot". When it comes to freeing space, InnoDB just picks the least hot page and frees it from memory to make space for the next page.
In typical operation MySQL will not free pages from memory unless it needs to make space for another page. I say 'typical', because pages will be freed if a table is dropped for example.
MySQL 5.5 ONWARDS
There are a number of operational limitations with using a classic LRU implementation on a database which has many users, and typically more than one workload. For example lets say that everything is working fine and we've figured out what is "hot or not", noting that we do not need as much memory as we do data because in most cases there will be pages that are not accessed frequently. Now imagine that a series of queries come in from mysqldump that want to run tablescans to export the data. What can happen, is the pages loaded in from the tablescans push out some of the good pages in our buffer pool, and even worse, as soon as the mysqldump operation is complete, they will no longer be required. So now post-mysqldump we have random IO as we try and re-settle cache-contents again.
So in MySQL 5.5 the LRU was split into two sublists:
- A 'young' sublist for our frequently accessed pages (default: 63%)
- An 'old' sublist for our table scan page accesses (default: 37%)
The configuration variable innodb_old_blocks_pct was introduced to configure the old/new split, and a variable innodb_old_blocks_time(default in 5.5: 0, in 5.6: 1000) was introduced to specify a minimum amount of milliseconds that a freshly loaded page must stay in the old sublist before it is ellible to be promoted to the young sublist.
As mentioned in the manual, and in blog posts it frequently makes sense to configure the innodb_old_blocks_time to a larger value, for example 1000 (ms).
A note on page churn
If you do not have enough memory to hold all of your working set in memory, what will happen is that the buffer pool will start juggling as it churns pages out of memory only to load them back in soon after. A small amount of this is probably okay, but it can start to degrade performance. The traditional rule of thumb is called "the 5 minute rule". This means that if you load a page into memory and are going to need it again in the next five minutes - it should not need to be churned out.
The goal of caching is to avoid having to perform disk IO, which if we are talking about hard drives, each operation is about 20 million times slower than CPUs or about 100 thousand times slower than memory.
So today I want to cover the subject of when are we required to do IO?But before I get there I first have to describe how IO works on all our modern operating systems.
An Introduction to Buffered IO
When you read or write to a file, by default you are speaking to a layer in between you and your disks called buffered IO. This layer is designed to increase performance and adds important features such as caching. Without going into this in detail, it's important to note that:
When you READ data, you may be reading from the disk or a cache provided by the operating system.
When you WRITE data you are not writing to the disks, but to a queue which will be flushed in the background. This queue is a little bit different to how a message queue in an application may behave: it usually has a hard limit to the number of outstanding requests, and it does not always complete requests in purely FIFO order - but an optimized order that still makes sure there is a maximum time window a request can take (example).
WRITE operations are not crash-safe by default! The operating system does provide a way of syncing a file to make sure all of the buffers are flushed down to the disk; an fsync.
An fsync is usually very expensive because it forces the queue to empty, and reduces the amount of request reordering and merging. Sequential IO is really important for hard drives.
So now lets look at IO created by a MySQL Server using the InnoDB storage engine (default since MySQL 5.5).
Reading pages into memory
If a page is not located in cache already, InnoDB will load it into memory in the foreground while queries are waiting, unless the page loading was triggered via a read ahead operation in which case it will use one of its innodb_read_io_threads to make this happen in the background.
Writing modified pages back to disk
When you make a modification to an InnoDB page, InnoDB does notwrite it immediately to disk. Instead a delta is written to the transaction log (see below).
These modified-only-in-memory pages are typically reffered to as dirty pages. Writing down dirty pages happens as part of InnoDB's background process which continually performs work every 1 second, and it adjusts well to peaks and troughs in load with a feature introduced in MySQL 5.5 and further improved in MySQL 5.6 calledadaptive flushing. However, if there are too many dirty pages and there is not enough free space, InnoDB may be forced to synchronously flush a dirty page to make free space.
This delaying allows InnoDB to perform fewer random writes, since there will usually be multiple changes to the same page, or by default pages in the same extent (1MB allocation unit on disk). MySQL also 5.6 allows you to disable this for SSDs.
(Note: Domas makes a comment about the doublewrite buffer below, and how I might be over-selling request merging :) It's outside of the scope covered here, but described in Facebook's blog and the MySQL manual).
Writing the transaction log
Any time you modify an InnoDB page a record of this modification is made to InnoDB's transaction log system (ib_logfile1 and ib_logfile0on disk). This transaction log is designed to be sequentially written, and unless the server crashes the server will only ever write and not read from it.
The log files are cyclical by design. You can think of them as a concatentation of two (by default) that behave similar to tread on a tank. So the first one is filled, then the second, then InnoDB will start filling the first again. The writes to the transaction log will be 512B aligned and using the operating system's buffered IO. InnoDB actually relies on the operating system here to provide a cache of the log (which is not guaranteed). Since these writes are 512B - and a typical page is 4K what will happen without enough cache, is that the page will neeed to be read first, to make the partial-modification, then written back.
To reduce log file IO, InnoDB also has a log buffer (configured byinnodb_log_buffer_size) that is filled with pending modifications which will be written and synced on any COMMIT by default.
Writing the slow log and general log
The general log is written out as each event is generated. It will be a sequential write, and is never synced or never read back (should be fairly low cost).
The slow query log is written out after query execution has finished. Similiar to the general log, it should also be very log cost.
Why I say "should" is that this also depends on filesystem choice. Anfsync under ext3 does not flush one file, but all files. This has become known as the great fsync() bug. Also, the operating system will usually have a limit the amount of modifications queued up waiting to be written, so too much log activity could cause synchronous writing and impact other operations (see Domas' comment below).
IO on a Replication Master (aka binary log enabled)
The binary log is sequential IO written to as part of a COMMIToperation, but by default it is never flushed, so it is not crash-safe! It requires setting sync_binlog=1.
The binary log does not have its own in memory buffers, so it's possible if an out of date slave came online that the master server may need to read old binary log files in order to send previous server events. However in practice the master pushes changes to online slaves as they are committed so it should be write-only.
The server maps changes in the binary log to InnoDB's transaction log to ensure consistency by default.
IO on a Replication Slave
The relay logs are in the same format as the binary logs, but the IO semantics are a little different. The slave's IO_THREAD retrieves new events from the master and writes sequential IO relay log files on the slave. Then the SQL_THREAD then reads the changes from the relay logs and applies them. This read operation does not have any caches inside of MySQL to ensure no IO is performed, and instead relies on the operating system's buffered IO to assist. It's possible to decrease the amount of relay logs kept on the slave to assist in a better 'cache fit', but this also means that if the master fails there may be changes the slave was not aware of.
Writing the relay logs is not crash-safe by default, and requires settingsync_relay_log=1.
Also, the status files which maintain a mapping between the relay logs and master's binary logs (which are not a 1:1) are also not crash-safe. A new option in MySQL 5.6 allows you to use an internal table to maintain these status files for added safety, but it is not enabled by default. The new options are master-info-repository and relay-log-info-repository.
Summary
I am sure I made a couple of simplifications (for example not describing the doublewrite buffer), but hopefully it's a good start. There are a couple of key take away points I want to make:
Reads are foregrounded (except read-ahead) and don't provide the same opportunity reorder and merge - so that makes them typically random IO and very expensive. If you run a diagnostic command like iostat and you see a lot of reads on a warmed up server - this is something adding memory can usually solve.
Most writes are sequential or backgrounded. Both InnoDB and the operating system try to produce sequential IO and merge requests.
While InnoDB has always been crash safe by default, MySQL has not. There are new features in MySQL 5.6 which makes replication more reliable
No comments:
Post a Comment