3

I found out how much the innodb pool size has allocated memory from show innodb status but I couldn't figure out how much actually it uses. Maybe it needs more or I can take some memory off from it? How to find it out? Oh and one question, as I'm kinda new to the innodb configuration playground - it uses two different memories, right? One is ram and the other is disk, yes?

BUFFER POOL AND MEMORY
----------------------
Total memory allocated 7162316778; in additional pool allocated 1048576
Buffer pool size   393216
Free buffers       0
Database pages     380971
Modified db pages  1906
Pending reads 0
Pending writes: LRU 0, flush list 1, single page 0
Pages read 16986872, created 474559, written 9972877
0.00 reads/s, 39.33 creates/s, 353.48 writes/s
Buffer pool hit rate 1000 / 1000

I've currently set the pool size to (see below) of my 12GB server.

innodb_buffer_pool_size = 6G
Rihards
  • 769
  • 2
  • 12
  • 22

3 Answers3

2

From the output you've provided, your 6 gigabytes of RAM have been fully utilized. That doesn't mean that you're not using enough -- it just means that at some point that much data was read off the drive. What you need to be looking for are other details about your system utilization. If you have a lot of drive activity, that may be an indicator that you need more memory devoted to InnoDB. It may mean many other things.

Because there is so much to learn about this level of tuning, I suggest that you check out http://www.mysqlperformanceblog.com and do several hours of reading.

Jeff Ferland
  • 20,547
  • 2
  • 62
  • 85
1

InnoDB pool recommended 2/3 of the RAM you have.

No, all pool in memory, but it has a complex layout and uses additional memory pool plus some more RAM for internal need.

Most of the InnoDB pool is just a cache of your data. So there's really no way to determine how much is "needed". Probably not since in have pool more than size of your database.

In your output you can see that Freebuffers = 0. So all memory is used, but as I said before, that just means your database is able use all RAM.

"Buffer pool hit rate 1000 / 1000" means you have a high hit rate (all requests use the pool, not disk), so no need to worry or increase the buffer.

Community
  • 1
arheops
  • 708
  • 1
  • 5
  • 13
0

Usually, i just set innodb buffer size = (total innodb tables' cumulative size + 10-25%)

some queries i found to help find total innodb table size at http://vbtechsupport.com/462/

p4guru
  • 963
  • 1
  • 8
  • 16