MySQL Forums
Forum List  »  Performance

Re: buffer pool read/write?
Posted by: Rick James
Date: January 05, 2013 02:37PM

Everything InnoDB does goes through the buffer_pool. I prefer to look at other STATUS values for getting insight into InnoDB activity. Example:

(These are rather small values, since the server has not been up long.)
| Innodb_buffer_pool_read_requests        | 45932      |
| Innodb_buffer_pool_reads                | 420        |
SELECTs (etc) asked for a block 45,932 times. But only 420 times did it have to hit the disk; the rest were cached. So, 99% "cache hit ratio" -- a good value.

Sorting (GROUP BY, ORDER BY, etc) does not usually touch the buffer_pool. This is because temp tables (as needed for sorting) are ENGINE = MEMORY or MyISAM.

Let's dissect a slightly complex statement:
INSERT INTO foo SELECT * FROM bar ORDER BY x;
(Assuming foo and bar are both InnoDB.)
1. Read all the rows from bar -- This will (I think) involve Innodb_buffer_pool_reads equal to the number of 16KB _blocks_ in the data part of the table. (Or fewer, if they are already cached.) And it will involve Innodb_buffer_pool_read_requests equal to the number of _rows_ in the table bar.
2. Create a temp table (MEMORY or MyISAM, depending of factors not discussed here).
3. Write all the rows to that temp table
4. Sort that table.
5. Copy the rows into foo. This will involve Innodb_buffer_pool_write_requests (rows) and Innodb_pages_written (blocks).

Note: blocks are not written to disk immediately, so Innodb_pages_written may not be updated immediately.

Caveat: I may have some of the details of this answer incorrect, but the gist should be correct.

Where are you headed with your question? Perhaps you need to study the actual code? And, if you want the latest, I suggest you look both at Oracle-MySQL's 5.6 and at Percona's Xtradb.

Options: ReplyQuote


Subject
Views
Written By
Posted
3279
January 03, 2013 08:38PM
Re: buffer pool read/write?
1839
January 05, 2013 02:37PM
1158
January 05, 2013 04:50PM
1139
January 06, 2013 06:01PM
1121
January 08, 2013 06:18AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.