3

Imagine we have a table which has 100 Million rows and 80 GB size.

Every rows has Text column and multiply integer columns.

we set Innodb_buffer_pool_size to 40G

I executing this Query:

select text,id,like_count from example where time > 'xxx-xx-xx' and time < 'xxx-xx-xx'

So if this query need to read 50G of data file (because of query) and move to Buffer Pool. I want to know how buffer pool handle this data.

because We set buffer pool to 40G but query need 50G to handle it.

  • Have you already tried this on a server and have any failure messages? If so, can you add them to your question. If you have not yet tried this, please try to run your query with your configuration. I have looked after many large systems, some with tables of 250Gb, but buffer pool sizes of just 12Gb which worked fine. Bear in mind that the buffer pool is memory resident, so a 40Gb bool would need to be on a server with at least 50Gb of RAM – Dave Rix Nov 23 '16 at 10:32
  • Check the following MySQL documentation page for more information - dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool.html – Dave Rix Nov 23 '16 at 10:32
1

Your scenario sounds exactly like what a mysqldump will do : Push every data and index page out of the InnoDB Buffer Pool. You can see that this is the case because when a mysqldump is in progress, the processlist will have something like SELECT /*!N SQL_NO_CACHE */ from .... See an example of the appearance of a SELECT from a mysqldump in MySQL Slow Query Log - SELECT /*!N SQL_NO_CACHE */

It makes sense that any full table scan will push every data page from the table into the Buffer Pool, causing every old page in the Buffer Pool to get evicted. If you push 50G of data pages into your 40GB Buffer Pool, your Buffer Pool will contains the last 80% of that table because the first 20% would have been pushed into the Buffer Pool and then get pushed right out. I mentioned about 4 months ago (See my old post Is it safe to run parallel innodb single-transaction dumps of individual tables?)

SUGGESTION

If your goal is to keep Buffer Pool's contents looking the same before and after a huge query, there is only one thing you can do: Dump the map of the Buffer Pool to disk, run your big query, and restore the Buffer Pool from the map.

mysql> SET GLOBAL innodb_buffer_pool_dump_now = 1;
mysql> ( Your Big 50GB query )
mysql> SET GLOBAL innodb_buffer_pool_load_now = 1;

Please see MySQL Documentation on these options

  • Thank you Ronaldo. What about Fulltext query. do FTS query use buffer pool ? or FTS has another algorithm. – HamoonDBA Nov 26 '16 at 6:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.