I am running some queries on some large tables. I tuned my settings so that innodb_buffer_pool_size = 16G and when I look at show variables, I see that 16G is allocated to the instance.
I am running a 64bit machine with 24G of ram and have mysql server 64 bit installed. While my query is running, only 6.08GB of physical memory is being used total and only about 2GB for mysql.
Resource Monitor tells me that the mysqld.exe process has a commit of 18,699,968 but a working set of 2,069,572.
What do I need to do to make this Windows server use the full 16G of ram during queries?
I am using the 32 bit version of MySQL Workbench to run my queries btw. Using the command line query also only uses about 2GB of memory as well.
Result of requested query:
INNODB_BUFFER_POOL_PAGES_DATA 426.80 MB
INNODB_BUFFER_POOL_PAGES_DIRTY 0.00 B
INNODB_BUFFER_POOL_PAGES_FLUSHED 16.00 KB
INNODB_BUFFER_POOL_PAGES_FREE 15.58 GB
INNODB_BUFFER_POOL_PAGES_MISC 16.00 KB
INNODB_BUFFER_POOL_PAGES_TOTAL 16.00 GB
Just a little background, I am not running a live server for a web page or application. I basically imported a 600k row table and a 33k row table and need to perform matches against each and then output the results into csvs. The queries are taking forever, and I would like the server to use the maximum amount of ram to hurry things up. Based on my results above, let me know what I can do.
Best Answer
You need to be aware of what the InnoDB Buffer Pool breakdown is from the status variables
Please run the following query
This will gives a status of the Buffer Pool's current usage and how much data has been flushed from it.
Example
I have the following
Why is
INNODB_BUFFER_POOL_PAGES_FLUSHED
so sky high at179.59 GB
?That is the amount of flushing the buffer pool has had to commit to the InnoDB Architecture.
Here is a diagram
In my case,
MySQL has been up for 43 days. It has flushed 179GB out of the Buffer Pool.
The Buffer Pool has to flush dirty pages to three places
I have a rather busy server and it's a VM.
In your case, 18,699,968 K of flushes from a 2,069,572 K Buffer Pool is nothing to worry about.