2

I have 64gb ram on server. InnoDB buffer pool is set to 26.1gb

[OK] InnoDB buffer pool / data size: 26.1G/24.3G

Im wondering what happens if data over grow current pool size, as i understand it should store more important data and data more often used in pool and other access from hard drive ?

I had similar situation before, and as soon it has overgrown pool size mariadb server become unstable, i have been trying to lift up innodb_buffer_pool_size to 32gb but it caused stability problem as well. Even that most of time memory usage is 33gb/64gb and most of this ram is used by mariadb server, only 500mb ram is used by other applications. There other half seems not used. I have temporary solved it by truncating unnecessary data but this time i can't do it.

I have considered to use swap but since i have only 2x2TB Soft Raid Mode 1 and if InnoDb will read from disk anyways there is no point to do so i guess.

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes FROM information_schema.tables WHERE engine='InnoDB') A

Shows that i should use 60gb innodb pool size.

If it's important innodb_flush_method=O_DIRECT

Improve this question
1
  • When the recommended InnoDB buffer pool size is 60GB, total InnoDB table data are around 38 GB, Since the query recommends 60% additional memory. So having around 40 GB is enough for you to keep the entire database in memory. If it is a dedicated server, better allocate 80% of the total available memory to innodb_buffer_pool_size. Check this page for better understanding of allocating memory to innodb_buffer_pool_size rathishkumar.in/2017/01/…
    – Rathish Kumar B
    Commented Jan 9, 2017 at 12:36

1 Answer 1

Reset to default
0

If the buffer_pool is set at 26GB in a 64GB server, what is the rest of RAM being used for? Normally you should simply set it to 45-50G and not worry.

On the what-if question:

Case 1: There is high "locality of reference". A buffer_pool that is much smaller than the total data (24.3G in your case) works fine. The buffer_pool is a "cache", and this Case is an example where caching is important.

Case 2: Lots of random access or lots of table scans. In this Case performance will degrade, possibly seriously, if the buffer_pool is not big enough for the dataset. The caching fails, and I/O ensues.

UUID primary keys or other indexes can lead to random accesses and performance problems in Case 2.

I see no clues in what you said that could explain "32gb .. caused stability problem".

In most servers, O_DIRECT is good.

innodb_buffer_pool_instances should probably be 16 in your range.

Improve this answer
5
  • Thanks for reply, basically after i have set bigger ram to inno_db software called "mysqltuner" claimed that mysql is using too much memory, that it would be more than system have, so you tell me that this assumption is wrong when my system mostly have more than 50% free ram ? If yes, is there any other simple-like mysqltuner software which works properly ? ps. sorry for late reply.
    – user108196
    Commented Nov 4, 2016 at 10:27
  • "26.1G/24.3G" says that the buffer_pool is big enough today. What what about tomorrow, after you add more data? "Shows that i should use 60gb innodb pool size." contradicts the "26.1G" that mysqltuner found -- Which is correct??
    – Rick James
    Commented Nov 4, 2016 at 18:20
  • It's innodb pool size / current size - it's fine now. But i want to fix it before it reach maximum. The thing is, if i set larger pool size then 26gb, mysqltuner claims that it's too big comparing to system memory, but actually system is running usually 30/64gb used, so mysqltuner gives dummy result ? If yes what tool i can use to check basic informations about DB, like mysqltuner?
    – user108196
    Commented Nov 6, 2016 at 23:43
  • mysqltuner has a very pessimistic formula for worst case memory usage. (And it is not as pessimistic as it needs to be.) However, for 99.99% of situations, it is overly pessimistic. Watch for "swapping". Watch the memory usage (see top in nix or Task Manager in Win).
    – Rick James
    Commented Nov 12, 2016 at 1:22
  • I'll give you a different review if you provide SHOW VARIABLES; and SHOW GLOBAL STATUS;. (probably need post.it or other tool, due to the size.)
    – Rick James
    Commented Nov 12, 2016 at 1:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.