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.

Optimize my.cnf with MariaDB

Discussion in 'Workarounds and Optimization' started by jazz1611, Jun 27, 2013.

  1. jazz1611

    jazz1611 Well-Known Member

    Joined:
    Jun 5, 2012
    Messages:
    83
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Hi,

    My server (using VMware) just change from MySQL to MariaDB with tutorial /http://www.prajith.in/install-mariadb-in-cpanelwhm/

    4 Core CPUs
    Code:
    Processor #4
    Vendor GenuineIntel
    Name Intel(R) Xeon(R) CPU L5640 @ 2.27GHz
    Speed 2261.000 MHz
    Cache 12288 KB
    4GB RAM + 10GB Swap
    Code:
                 total       used       free     shared    buffers     cached
    Mem:       3912340    3881344      30996          0     105956    3057816
    -/+ buffers/cache:     717572    3194768
    Swap:     10239992      83480   10156512
    Total:    14152332    3964824   10187508
    200GB HDD (RAID-6)
    Code:
    Filesystem            Size  Used Avail Use% Mounted on
    /dev/mapper/VolGroup-LogVol01  187G  9.4G  168G   6% /
    tmpfs                 1.9G     0  1.9G   0% /dev/shm
    /dev/sda1             485M   73M  388M  16% /boot
    /usr/tmpDSK           1.8G   69M  1.6G   5% /tmp
    There is my.cnf after change to MariaDB
    Code:
    #
    # This group is read both both by the client and the server
    # use it for options that affect everything
    #
    [client-server]
    
    #
    # include all files from the config directory
    #
    !includedir /etc/my.cnf.d
    
    [mysqld]
    local-infile=0
    innodb_file_per_table=1
    
    datadir=/var/lib/mysql
    tmp_table_size=256M
    max_heap_table_size=256M
    query_cache_limit=256M
    query_cache_size=256M
    query_cache_type=1
    max_user_connections=45
    max_connections=300
    innodb_buffer_pool_size = 256M
    
    collation_server=utf8_unicode_ci
    character_set_server=utf8
    
    delayed_insert_timeout=40
    
    interactive_timeout=10
    wait_timeout=60
    connect_timeout=20
    thread_cache_size=128
    key_buffer=64M
    join_buffer_size=64M
    max_connect_errors=20
    max_allowed_packet=64M
    table_cache=4096
    sort_buffer_size=8M
    read_buffer_size=8M
    read_rnd_buffer_size=8M
    myisam_sort_buffer_size=64M
    server-id=1
    
    [mysql.server]
    user=mysql
    
    [safe_mysqld]
    err-log=/var/log/mysqld.log
    pid-file=/var/lib/mysql/mysql.pid
    
    [mysqldump]
    quick
    max_allowed_packet=32M
    
    [mysql]
    no-auto-rehash
    Result showing of mysqltuner.pl
    Code:
    root@serv4 [~]# ./mysqltuner.pl
    
     >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at [url]http://mysqltuner.com/[/url]
     >>  Run with '--help' for additional options and output filtering
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.31-MariaDB
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 21M (Tables: 314)
    [--] Data in InnoDB tables: 960K (Tables: 60)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 0B (Tables: 2)
    [!!] Total fragmented tables: 1
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 5s (9 q [1.800 qps], 5 conn, TX: 14K, RX: 561)
    [--] Reads / Writes: 100% / 0%
    [--] Total buffers: 848.0M global + 88.3M per thread (300 max threads)
    [!!] Maximum possible memory usage: 26.7G (715% of installed RAM)
    [OK] Slow queries: 0% (0/9)
    [OK] Highest usage of available connections: 0% (1/300)
    [OK] Key buffer size / total MyISAM indexes: 64.0M/3.4M
    [!!] Query cache efficiency: 0.0% (0 cached / 4 selects)
    [OK] Query cache prunes per day: 0
    [OK] Temporary tables created on disk: 0% (0 on disk / 2 total)
    [OK] Thread cache hit rate: 80% (1 created / 5 connections)
    [OK] Table cache hit rate: 100% (26 open / 0 opened)
    [OK] Open file limit used: 0% (21/8K)
    [OK] Table locks acquired immediately: 100% (36 immediate / 36 locks)
    [OK] InnoDB data size / buffer pool: 960.0K/256.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Reduce your overall MySQL memory footprint for system stability
        Enable the slow query log to troubleshoot bad queries
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        query_cache_limit (> 256M, or use smaller result sets)
    Anybody can give me some tutorial for better?

    Regards,
     
  2. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    correct those:

    tmp_table_size=50M
    max_heap_table_size=50M
    query_cache_limit=20M
    query_cache_size=20M
    max_connections=100
    innodb_buffer_pool_size = 100M
    connect_timeout=2
    join_buffer_size=2M
    max_allowed_packet=10M
    table_cache=1000
    sort_buffer_size=256K
    read_buffer_size=128K
    read_rnd_buffer_size=4M
    key_buffer=500M


    and run mysqltuner after at least few hours of mysql running
     
  3. jazz1611

    jazz1611 Well-Known Member

    Joined:
    Jun 5, 2012
    Messages:
    83
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Why set it to low values?

    Current, i have 3 other server with 8GB RAM and 4 vs 6 Core CPUs and running MariaDB. With same config i posted.

    Regards,
     
  4. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    [!!] Maximum possible memory usage: 26.7G (715% of installed RAM)



    And most of the settings that you set high doesnt give you any benefit
    some of them like sort_buffer_size, and read_buffer_size might even give lower performance when high
    checkout google for benchmarks
     
  5. jazz1611

    jazz1611 Well-Known Member

    Joined:
    Jun 5, 2012
    Messages:
    83
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Hi,

    There is my.cnf of Server 6 Core CPUs, 8GB RAM (KVM) with MariaDB

    Code:
    [mysqld]
    local-infile=0
    innodb_file_per_table=1
    
    datadir=/var/lib/mysql
    tmp_table_size=256M
    max_heap_table_size=256M
    query_cache_limit=256M
    query_cache_size=256M
    query_cache_type=1
    max_user_connections=45
    max_connections=300
    innodb_buffer_pool_size = 256M
    
    collation_server=utf8_unicode_ci
    character_set_server=utf8
    
    delayed_insert_timeout=40
    
    interactive_timeout=10
    wait_timeout=60
    connect_timeout=20
    thread_cache_size=128
    key_buffer=64M
    join_buffer_size=32M
    max_connect_errors=20
    max_allowed_packet=16M
    table_cache=2048
    sort_buffer_size=2M
    read_buffer_size=2M
    read_rnd_buffer_size=2M
    myisam_sort_buffer_size=32M
    server-id=1
    
    [mysql.server]
    user=mysql
    
    [safe_mysqld]
    err-log=/var/log/mysqld.log
    pid-file=/var/lib/mysql/mysql.pid
    
    [mysqldump]
    quick
    max_allowed_packet=16M
    
    [mysql]
    no-auto-rehash
    
    Code:
    root@serv2 [~]# ./mysqltuner.pl
    
     >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at [url]http://mysqltuner.com/[/url]
     >>  Run with '--help' for additional options and output filtering
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.31-MariaDB
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 2G (Tables: 9382)
    [--] Data in InnoDB tables: 194M (Tables: 1082)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 95M (Tables: 1018)
    [!!] Total fragmented tables: 1221
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 11h 39m 50s (5M q [134.218 qps], 252K conn, TX: 83B, RX: 925M)
    [--] Reads / Writes: 81% / 19%
    [--] Total buffers: 848.0M global + 38.3M per thread (300 max threads)
    [!!] Maximum possible memory usage: 12.0G (157% of installed RAM)
    [OK] Slow queries: 0% (5/5M)
    [OK] Highest usage of available connections: 8% (24/300)
    [OK] Key buffer size / total MyISAM indexes: 64.0M/348.0M
    [OK] Key buffer hit rate: 99.5% (7M cached / 42K reads)
    [OK] Query cache efficiency: 71.8% (3M cached / 4M selects)
    [!!] Query cache prunes per day: 240755
    [OK] Sorts requiring temporary tables: 0% (7 temp sorts / 181K sorts)
    [!!] Temporary tables created on disk: 39% (63K on disk / 163K total)
    [OK] Thread cache hit rate: 99% (24 created / 252K connections)
    [!!] Table cache hit rate: 2% (2K open / 77K opened)
    [OK] Open file limit used: 65% (2K/4K)
    [OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
    [OK] InnoDB data size / buffer pool: 194.2M/256.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Reduce your overall MySQL memory footprint for system stability
        Enable the slow query log to troubleshoot bad queries
        Increasing the query_cache size over 128M may reduce performance
        Temporary table size is already large - reduce result set size
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        query_cache_size (> 256M) [see warning above]
        table_cache (> 2048)
    Help me.

    Regards,
     
  6. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    tmp_table_size=50M
    max_heap_table_size=50M
    query_cache_limit=50M
    query_cache_size=50M
    query_cache_type=1
    max_user_connections=45
    max_connections=200
    innodb_buffer_pool_size = 500M
    connect_timeout=2


    key_buffer=1000M
    join_buffer_size=4M
    table_cache=5000
    sort_buffer_size=256K
    read_buffer_size=128K
    read_rnd_buffer_size=8M
    myisam_sort_buffer_size=256M


    in both cases its good to enable slow query log

    slow_query_log=1
    slow_query_log_file=mysql-slow.log
    long_query_time=0.1
    log-queries-not-using-indexes
     
  7. jazz1611

    jazz1611 Well-Known Member

    Joined:
    Jun 5, 2012
    Messages:
    83
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Code:
    [!!] Total fragmented tables: 26
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 4s (569 q [142.250 qps], 23 conn, TX: 7M, RX: 128K)
    [--] Reads / Writes: 92% / 8%
    [--] Total buffers: 1.6G global + 12.7M per thread (200 max threads)
    [OK] Maximum possible memory usage: 4.1G (52% of installed RAM)
    [OK] Slow queries: 4% (28/569)
    [OK] Highest usage of available connections: 1% (3/200)
    [OK] Key buffer size / total MyISAM indexes: 1000.0M/348.5M
    [OK] Key buffer hit rate: 98.6% (24K cached / 355 reads)
    [OK] Query cache efficiency: 27.5% (128 cached / 466 selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 100 sorts)
    [!!] Temporary tables created on disk: 44% (73 on disk / 164 total)
    [OK] Thread cache hit rate: 86% (3 created / 23 connections)
    [OK] Table cache hit rate: 121% (149 open / 123 opened)
    [OK] Open file limit used: 1% (202/10K)
    [OK] Table locks acquired immediately: 100% (573 immediate / 573 locks)
    [OK] InnoDB data size / buffer pool: 194.2M/500.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
        tmp_table_size (> 50M)
        max_heap_table_size (> 50M)
    I changed follow values you give there. I need change more?

    Regards,
     
  8. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    mysqltuner should be run at least after few hours of mysql use, best is at least 24h

    all looks good, except
    [!!] Temporary tables created on disk: 44% (73 on disk / 164 total)

    you probably have some queries that creates temporary tables on disk,
    you should check slow query log, and run explain on queries to see which ones creates temp tables on disk, and then try to rewrite it to not use temp tables on disk
     
  9. faisikhan

    faisikhan Well-Known Member

    Joined:
    Dec 12, 2011
    Messages:
    88
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Islamabad, Pakistan
    cPanel Access Level:
    Root Administrator
    Most of the time, I use approximately the similar settings in my.cnf for the optimization of the Database resulting in better performance of the website.

    - - - Updated - - -

    Initiall you should test with the above values given out there and then gradually increase them if you are able to see the visible changes in the optimization.
     
  10. jazz1611

    jazz1611 Well-Known Member

    Joined:
    Jun 5, 2012
    Messages:
    83
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Hi,

    I am running serv3 (Dedicated Server Malaysia) with 4 Core CPUs (Intel(R) Core(TM) i5-3570 CPU @ 3.40GHz) - 8GB RAM (16GB Swap) - 500GB HDD (RAID-1)

    There is my.cnf with MariaDB

    Code:
    #
    # This group is read both both by the client and the server
    # use it for options that affect everything
    #
    [client-server]
    
    #
    # include all files from the config directory
    #
    !includedir /etc/my.cnf.d
    
    [mysqld]
    local-infile=0
    innodb_file_per_table=1
    
    datadir=/var/lib/mysql
    tmp_table_size=50M
    max_heap_table_size=50M
    query_cache_limit=50M
    query_cache_size=50M
    query_cache_type=1
    max_user_connections=45
    max_connections=150
    innodb_buffer_pool_size = 500M
    
    collation_server=utf8_unicode_ci
    character_set_server=utf8
    
    delayed_insert_timeout=40
    
    interactive_timeout=10
    wait_timeout=60
    connect_timeout=2
    thread_cache_size=128
    key_buffer=1000M
    join_buffer_size=4M
    max_connect_errors=20
    max_allowed_packet=16M
    table_cache=5000
    sort_buffer_size=256K
    read_buffer_size=128K
    read_rnd_buffer_size=8M
    myisam_sort_buffer_size=256M
    server-id=1
    
    slow_query_log=1
    slow_query_log_file=mysql-slow.log
    long_query_time=0.1
    log-queries-not-using-indexes
    
    [mysql.server]
    user=mysql
    
    [safe_mysqld]
    err-log=/var/log/mysqld.log
    pid-file=/var/lib/mysql/mysql.pid
    
    [mysqldump]
    quick
    max_allowed_packet=16M
    
    [mysql]
    no-auto-rehash
    But my server load user mysql very high
    Ảnh chụp màn hình_2013-06-28_231838.png

    Help me.

    Regards,
     
  11. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    If you got 4 cores
    4 x 100% = 400%

    so you got usage 40.9% out of 400%

    This is how its presented in TOP (and you got top result there on the img)
     
  12. faisikhan

    faisikhan Well-Known Member

    Joined:
    Dec 12, 2011
    Messages:
    88
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Islamabad, Pakistan
    cPanel Access Level:
    Root Administrator
    You didn't mention the Size of your database/s, isn't it in GB's? What is the output of top command, which process is consuming much CPU & memory resources?
     
  13. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

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

    jazz1611 Well-Known Member

    Joined:
    Jun 5, 2012
    Messages:
    83
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Hi,

    Currently, my server load high ram. I removed all value of config. Who can give me some value to config?

    MySQL Governor can use for MariaDB?
    Regards,
     
  15. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    I don't understand what you mean now

    "Currently, my server load high ram."

    Load is high or RAM usage is high ?
    Can you provide munin graphs for CPU and Load ?

    "I removed all value of config. Who can give me some value to config?"
    Why did you remove that config lines provided by supporters here on forum ?

    "MySQL Governor can use for MariaDB?"
    What do you mean ?
    MySQL Governor is a set of tools to monitor/restrict MySQL usage on shared hosting. and that's on cloudlinux
     
  16. jazz1611

    jazz1611 Well-Known Member

    Joined:
    Jun 5, 2012
    Messages:
    83
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Hi,

    RAM usage is high with config on top.

    Config lines make ram load higher.
     
  17. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    RAM doesn't have load, but "usage"
    for example RAM used 6GB out of 8GB
    but it doesn't have "load"

    RAM is quite complicated topic, most of it is used by OS cache/buffers
    let me know where you read your RAM usage, and do you have munin installed ?

    it would be easier if you copied munin graphs for CPU, load, memory usage for us to see


    In the config that I proposed all per query buffers are smaller than the ones you had, but key_buffer_size is bigger
    But it's not used at max possible value, until you got so many indexes loaded at once, and based on mysqltuner - you don't

    So based on that, I think most of your RAM usage is cache/buffers, which is normal

    Copy the munin graphs to make sure
     
  18. jazz1611

    jazz1611 Well-Known Member

    Joined:
    Jun 5, 2012
    Messages:
    83
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Hi,

    How can i replace from MariaDB to MySQL (default)?

    Regards,
     
  19. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    You will need to undo the steps you took in the instructions from:

    How to replace MySQL with MariaDB in 11.36

    Thank you.
     
  20. jazz1611

    jazz1611 Well-Known Member

    Joined:
    Jun 5, 2012
    Messages:
    83
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Hi,

    Can you give me tutorial (steps) for replace it?

    Regards,
     
Loading...

Share This Page