1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

innodb_buffer_pool_size more data than pool size

Discussion in 'Programming/Internet' started by dansr23, Oct 8, 2018.

  1. dansr23

    dansr23 Guest

    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

    Login To add answer/comment
     

Share This Page