Optimize Mysql & My.cnf to 32g Ram & 8 Core CPU?



  • please , what is the best configuration to Innodb because mysql eat CPU on linux server this is my configuration but I don`t sure it matches best solution

    innodb_buffer_pool_size =24G
    innodb_log_file_size = 1G
    innodb_buffer_pool_instances = 4
    join_buffer_size = 1G
    max_heap_table_size = 1G
    thread_cache_size = 32
    #max_allowed_packet = 1600M
    max_allowed_packet = 100M
    tmp_table_size = 1G
    innodb_buffer_pool_chunk_size=3G
    


  • No to these:

    join_buffer_size = 1G
    max_heap_table_size = 1G
    #max_allowed_packet = 1600M
    tmp_table_size = 1G
    

    Don't set any of them to more than 1% of RAM. Otherwise, you threaten to consume so much RAM as to cause swapping. Swapping hurts performance a lot. Anyway, big values for those are mostly in the realm of "diminishing returns".

    The most important setting is fine as you have it:

    innodb_buffer_pool_size = 24G
    

    You will probably never use all 8 cores unless you have some really inefficient queries. And they can probably be fixed with a better INDEX or query reformulation.


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2