0

I'm checking into a buffer pool issue on a MySql database. According to the query to get database size shown here, the total database size is about 11GB. Unfortunately I know I have much less RAM than that; only about 4.5GB total for this VM. So I check InnoDB buffer pool usage, expecting to see it almost at capacity. Instead I see the opposite:

show engine innodb status \G;
...
Buffer pool size    163839
Free buffers        132864
...

It seems the buffer pool is hardly touched. And, yes, these are all InnoDB tables:

SELECT Engine, COUNT(*) "Count" 
FROM Information_Schema.Tables 
WHERE table_schema = 'My_Database'
GROUP BY Engine;
Engine   Count
-----    -----
InnoDB    169

What could be going on here?

2

Fool that I am... the server was restarted this morning. I realized this as I was finishing the question. The buffer pool won't fill up until the database actually needs to use that much data. Indeed, if I re-run the buffer pool query I can see the "Free buffers" number slowly decrease.

I could run some queries to force tables into memory, but given I know I don't have enough memory to go around (hope to address this soon) I should just wait and let my users tell the DB what data is more important.

0

If you're running 5.6+ you can have the server dump and preload the buffer pool by adding a couple of settings to my.cnf, rather than waiting for it to warm up after each restart.

innodb_buffer_pool_dump_at_shutdown=ON innodb_buffer_pool_load_at_startup=ON

https://dev.mysql.com/doc/refman/5.6/en/innodb-preload-buffer-pool.html

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.