Assume that I am using the default settings in Mysql 5.7 which has 8 buffer pools and each is allocated 1GB. Then assume that there are 3 tables, each table are 2GB each. Also, assume this is a test server and there is no traffic on it.
A query which needs 2GB of data is executed. What happens to the buffer pools? Will innodb fill up the pages for buffer_pool_1 then buffer_pool_2?
Then let say this same query executed again and it will be faster since the data is in the buffer pool already. But how does innodb know that the data is in buffer_pool_1 and buffer_pool_2?
Does the 8 buffer pools work independently (I believe so that it can work concurrently)? Or does innodb somehow manages these buffer pools and they are acting as a single buffer pool because how does innodb know that part of data in the query is in innodb_buffer_pool_1 and the other resides in innodb_buffer_pool_2?