Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

Making the switch to MariaDB

Discussion in 'Workarounds and Optimization' started by celiac101, Apr 16, 2018.

Tags:
  1. celiac101

    celiac101 Well-Known Member

    Joined:
    Dec 19, 2012
    Messages:
    73
    Likes Received:
    1
    Trophy Points:
    8
    cPanel Access Level:
    Website Owner
    So I ran the mysql updater months ago to switch my server to MariaDB 10.1. The issue is that I never made changes to the my.cnf file, so I am not sure if I am actually optimizing MariaDB and using it properly. Below is my my.cnf file. I have 2 main questions:

    1) Should I have specific entries for MariaDB in it?

    2) Should I be using the MYISAM storage engine. It looks like the Aria is better.

    How can I add the changes I need to utilize MariaDB and Aria?

    Code:
    [mysqld]
    log-error=/var/lib/mysql/mysqld.err
    performance-schema=0
    bind-address=127.0.0.1
    innodb_file_per_table=1
    open_files_limit=95535
    local-infile=0
    query_cache_limit=1M
    query_cache_type=1
    max_connections=750
    max_user_connections=0
    interactive_timeout=100
    wait_timeout=100
    connect_timeout=10
    thread_cache_size=128
    max_connect_errors=100
    innodb_log_buffer_size=10M
    table_open_cache=3500
    innodb_buffer_pool_size=2G
    low_priority_updates=1
    concurrent_insert=ALWAYS
    table_definition_cache=3000
    bulk_insert_buffer_size=500M
    
    # Global, Non Engine-Specific Buffers
    max_allowed_packet=64M
    max_heap_table_size=8192M
    query_cache_size=32M
    tmp_table_size=8192M
    query_cache_size=128M
    
    # Per-Thread Buffers
    read_buffer_size=8M
    join_buffer_size=64M
    
    # MyISAM
    key_buffer_size=7G
    myisam_sort_buffer_size=64M
    
    default-storage-engine=MyISAM
    [mysqld_safe]
    log-error="/var/log/mysqld.log"
    #log slow
    #slow_query_log=1
    #log-queries-not-using-indexes=1
    #long_query_time=1
    slow_query_log=1
    slow_query_log_file="/var/log/mysql-slow-queries.log"
    
     
  2. cPanelMichael

    cPanelMichael Technical Support Community Manager
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    44,749
    Likes Received:
    1,884
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    Hello @celiac101,

    MariaDB is considered a drop-in replacement for MySQL. Most of the configuration settings are cross-compatible, however it's possible the performance impact of those configuration settings will differ with MariaDB. Additionally, while most settings are cross-compatible, there are some compatibility issues to be aware of:

    MariaDB versus MySQL - Compatibility

    The following thread is a good starting point if you want to tune your current settings:

    mysqlmymonlite.sh server stats gathering tool for cPanel Server

    I recommend running the tuner with your existing settings to see what it recommends.

    Thank you.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    linux4me2 likes this.
  3. tecsys

    tecsys Member

    Joined:
    Sep 9, 2012
    Messages:
    14
    Likes Received:
    1
    Trophy Points:
    3
    Location:
    Nashik.India
    cPanel Access Level:
    Root Administrator
    While most MySQL configurations would work just fine when used with MariaDB, it would indeed beneficial to fine tune settings individually. A few things to check would be the amount of MySQL traffic you have on your domains and then identify what settings would be best for you.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
Loading...

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice