The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

my.cnf config MariaDB10

Discussion in 'Workarounds and Optimization' started by kodyxgen, Feb 21, 2015.

  1. kodyxgen

    kodyxgen Member

    Joined:
    Jul 9, 2013
    Messages:
    20
    Likes Received:
    1
    Trophy Points:
    3
    cPanel Access Level:
    Root Administrator
    Hi,

    I am trying to get the best result from mysql server, i have edited my.cnf 100 times but still i am not 100% happy with it.
    I am running a 64 GB ram server with Intel® Xeon® Processor E5-1620 v2 (10M Cache, 3.70 GHz)
    Mysq is MariaDB 10 with defaul storage engine ToKuDB.
    I use ToKuDb because i have large databases some tables are more then 100 mill. rows.
    i need fast select and insert :)
    can anyone help me with a good my.cnf configuration.
    any help is welcome.
    my.cnf now looks like.
    Code:
    # The MySQL server
    [mysqld]
    plugin-load=ha_tokudb.so
    default_storage_engine=TokuDB
    tokudb_load_save_space = 1
    tokudb_commit_sync = OFF
    #tokudb_fsync_log_period = 1000
    #tokudb_directio = 1
    tokudb_debug=0
    init_connect = 'SET collation_connection = utf8_general_ci,NAMES utf8'
    collation_server = utf8_general_ci
    character_set_client = utf8
    character_set_server = utf8
    skip-character-set-client-handshake
    concurrent_insert = 2
    skip-external-locking
    skip-name-resolve
    key_buffer_size = 4G
    max_allowed_packet = 512M
    table_open_cache = 8192
    thread_cache_size = 8
    thread_concurrency = 8
    query_cache_size = 64M
    query_cache_limit  = 32M
    # Try number of CPU's*2 for thread_concurrency
    innodb_buffer_pool_size = 300M
    join_buffer_size = 32M
    sort_buffer_size = 32M
    read_buffer_size = 32M
    read_rnd_buffer_size = 32M
    myisam_sort_buffer_size = 32M
    tmp_table_size = 1G
    max_heap_table_size = 1G
    bulk_insert_buffer_size = 1G
    #log-slow-queries
    local-infile=0
    innodb_file_per_table=1
    max_prepared_stmt_count=500000
    slow_query_log = ON
    slow_query_log_file =/var/lib/mysql/server1.host-web4u-slow.log
    tokudb_tmp_dir = /var/mysqltmp/
    #large-pages
    #large_pages = true
    #innodb_use_sys_malloc = 0
    # Point the following paths to different dedicated disks
    tmpdir          = /var/mysqltmp/
    max_connections = 2000
    open_files_limit= 9000
    [mysqldump]
    quick
    max_allowed_packet = 512M
    
    [mysql]
    no-auto-rehash
    # Remove the next comment character if you are not familiar with SQL
    #safe-updates
    
    [myisamchk]
    key_buffer_size = 256M
    sort_buffer_size = 256M
    read_buffer = 2M
    write_buffer = 2M
    
    Thank you
     
  2. kernow

    kernow Well-Known Member

    Joined:
    Jul 23, 2004
    Messages:
    865
    Likes Received:
    9
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    As your using MariaDB + query_cache you might benifit from adding:
    query_cache_strip_comments=1

    Note to those using Oracle MySQL, adding the above will stop MySQL from starting!!
     
  3. kodyxgen

    kodyxgen Member

    Joined:
    Jul 9, 2013
    Messages:
    20
    Likes Received:
    1
    Trophy Points:
    3
    cPanel Access Level:
    Root Administrator
    Thank you kernow i will try that.
    Can you tell me if the other values look ok?
     
  4. kernow

    kernow Well-Known Member

    Joined:
    Jul 23, 2004
    Messages:
    865
    Likes Received:
    9
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Impossible to tell without running tests and more tests. There is no one size fits all configuration, and even when you get it as best you can you will have to make further changes in future months.
    Have you run any tuning script that has shown you errors? What is it that you think isn't running perfect? For more tips on the query cache check this page:
    Optimize MySQL query_cache_size - Place of all Tech Info
     
  5. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
Loading...

Share This Page