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.

MariaDB tuning help needed

Discussion in 'Workarounds and Optimization' started by zaquria, Jun 15, 2016.

Tags:
  1. zaquria

    zaquria Member

    Joined:
    Dec 15, 2015
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    UK
    cPanel Access Level:
    Website Owner
    We are currently having issues with high CPU and memory usages. We believe that this could be down to our database.

    I was hoping that someone could kindly have a look over our settings and could give us some help in tuning MariaDB for our server?

    I have included the pltuner file and our my.cnf as attachments.

    Thanks in advance,

    Code:
    [mysqld]
    #performance-schema=0
    #innodb_file_per_table=1
    #innodb_buffer_pool_size=89128960
    #max_allowed_packet=268435456
    #open_files_limit=10000
    #default-storage-engine=MyISAM
    
    max_allowed_packet=268435456
    open_files_limit=10000
    [client]
    #password    = [your_password]
    #port        = 3306
    #socket        = /var/lib/mysql/mysql.sock
    
    # *** Application-specific options follow here ***
    #
    # The MariaDB server
    #
    [mysqld]
    # generic configuration options
    #port        = 3306
    #socket        = /var/lib/mysql/mysql.sock
    back_log = 50
    
    #skip-networking
    max_connections = 500
    max_connect_errors = 10
    table_open_cache = 400
    
    #external-locking
    max_allowed_packet=268435456
    binlog_cache_size = 1M
    max_heap_table_size = 16M
    read_buffer_size = 2M
    read_rnd_buffer_size = 16M
    sort_buffer_size = 8M
    join_buffer_size = 8M
    
    thread_cache_size = 4
    thread_concurrency = 8
    query_cache_size = 8M
    query_cache_limit = 2M
    
    ft_min_word_len = 4
    
    #memlock
    default-storage-engine = InnoDB
    thread_stack = 240K
    transaction_isolation = REPEATABLE-READ
    tmp_table_size = 16M
    log-bin=mysql-bin
    expire-logs-days  = 14
    binlog_format=mixed
    
    #log
    #log_warnings
    slow_query_log
    long_query_time = 2
    #tmpdir = /tmp
    
    #*** MyISAM Specific options
    key_buffer_size = 32M
    bulk_insert_buffer_size = 64M
    myisam_sort_buffer_size = 128M
    myisam_max_sort_file_size = 10G
    myisam_repair_threads = 1
    myisam_recover
    
    # *** INNODB Specific options ***
    #skip-innodb
    innodb_additional_mem_pool_size = 16M
    innodb_buffer_pool_size = 512M
    innodb_data_file_path = ibdata1:10M:autoextend
    
    #innodb_data_home_dir = <directory>
    innodb_write_io_threads = 8
    innodb_read_io_threads = 8
    #innodb_force_recovery=1
    innodb_thread_concurrency = 16
    innodb_flush_log_at_trx_commit = 1
    #innodb_fast_shutdown
    innodb_log_buffer_size = 8M
    innodb_log_file_size = 256M
    innodb_log_files_in_group = 3
    #innodb_log_group_home_dir
    innodb_max_dirty_pages_pct = 90
    #innodb_flush_method=O_DSYNC
    innodb_lock_wait_timeout = 120
    innodb_buffer_pool_instances = 1
    
    [mysqldump]
    # Do not buffer the whole result set in memory before writing it to
    # file. Required for dumping very large tables quick
    max_allowed_packet = 16M
    
    [mysql]
    no-auto-rehash
    
    # Only allow UPDATEs and DELETEs that use keys.
    #safe-updates
    
    [myisamchk]
    key_buffer_size = 20M
    sort_buffer_size = 20M
    read_buffer = 2M
    write_buffer = 2M
    
    [mysqlhotcopy]
    interactive-timeout
    
    [mysqld_safe]
    # Increase the amount of open files allowed per process. Warning: Make
    # sure you have set the global system limit high enough! The high value
    # is required for a large number of opened tables
    open-files-limit = 8192
    
    innodb_file_per_table
    innodb_flush_method = O_DIRECT
    innodb_flush_log_at_trx_commit = 0
    innodb_buffer_pool_size = 1G
    innodb_buffer_pool_instances = 1
    innodb_buffer_pool_dump_at_shutdown = 1
    innodb_buffer_pool_load_at_startup = 1
    transaction-isolation = READ-COMMITTED
    thread_handling = pool-of-threads
    table_open_cache = 1000
    join_buffer_size = 2M
    read_buffer_size = 128K
    sort_buffer_size = 256K
    tmp_table_size = 128M
    max_heap_table_size = 128M
    
    skip-name-resolve

    Code:
    [root@server tmp]# ./tuner.pl
    >>  MySQLTuner 1.6.13 - Major Hayden <major@mhtx.net>
    >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >>  Run with '--help' for additional options and output filtering
    
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 10.1.14-MariaDB
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -----------------------------------------------------------------
    [--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
    [--] Data in MyISAM tables: 47M (Tables: 131)
    [--] Data in InnoDB tables: 1G (Tables: 659)
    [OK] Total fragmented tables: 0
    
    -------- Security Recommendations ------------------------------------------------------------------
    [OK] There are no anonymous accounts for any database users
    [!!] User 'leechprotect@localhost' has no password set.
    [!!] There is no basic password file list!
    
    -------- CVE Security Recommendations --------------------------------------------------------------
    [--] Skipped due to --cvefile option undefined
    
    -------- Performance Metrics -----------------------------------------------------------------------
    [--] Up for: 3d 7h 1m 54s (4M q [16.460 qps], 186K conn, TX: 39G, RX: 1G)
    [--] Reads / Writes: 91% / 9%
    [--] Binary logging is enabled (GTID MODE: OFF)
    [--] Physical Memory     : 6.0G
    [--] Max MySQL memory    : 17.4G
    [--] Other process memory: 1.4G
    [--] Total buffers: 720.0M global + 34.2M per thread (500 max threads)
    [--] P_S Max memory usage: 0B
    [--] Galera GCache Max memory usage: 0B
    [OK] Maximum reached memory usage: 2.1G (35.40% of installed RAM)
    [!!] Maximum possible memory usage: 17.4G (288.07% of installed RAM)
    [!!] Overall possible memory usage with other process exceeded memory
    [OK] Slow queries: 0% (1K/4M)
    [OK] Highest usage of available connections: 8% (43/500)
    [OK] Aborted connections: 0.07%  (125/186091)
    [!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
    [OK] Query cache efficiency: 33.8% (1M cached / 5M selects)
    [!!] Query cache prunes per day: 198037
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 213K sorts)
    [!!] Joins performed without indexes: 2634
    [!!] Temporary tables created on disk: 74% (24K on disk / 32K total)
    [!!] Table cache hit rate: 2% (400 open / 18K opened)
    [OK] Open file limit used: 1% (141/8K)
    [OK] Table locks acquired immediately: 100% (2M immediate / 2M locks)
    [OK] Binlog cache memory access: 100.00% ( 361069 Memory / 361069 Total)
    
    -------- ThreadPool Metrics ------------------------------------------------------------------------
    [--] ThreadPool stat is enabled.
    [--] Thread Pool Size: 2 thread(s).
    [--] Using default value is good enough for your version (10.1.14-MariaDB)
    
    -------- Performance schema ------------------------------------------------------------------------
    [--] Performance schema is disabled.
    
    -------- MyISAM Metrics ----------------------------------------------------------------------------
    [!!] Key buffer used: 18.9% (6M used / 33M cache)
    [OK] Key buffer size / total MyISAM indexes: 32.0M/13.5M
    [!!] Read Key buffer hit rate: 93.2% (189K cached / 12K reads)
    [!!] Write Key buffer hit rate: 65.5% (19K cached / 6K writes)
    
    -------- AriaDB Metrics ----------------------------------------------------------------------------
    [--] AriaDB is enabled.
    [OK] Aria pagecache size / total Aria indexes: 128.0M/1B
    [OK] Aria pagecache hit rate: 99.9% (259M cached / 368K reads)
    
    -------- InnoDB Metrics ----------------------------------------------------------------------------
    [--] InnoDB is enabled.
    [!!] InnoDB buffer pool / data size: 512.0M/1.1G
    [OK] InnoDB buffer pool instances: 1
    [OK] InnoDB Used buffer: 81.54% (26718 used/ 32767 total)
    [OK] InnoDB Read buffer efficiency: 100.00% (619034558 hits/ 619059290 total)
    [!!] InnoDB Write Log efficiency: 60.25% (741292 hits/ 1230368 total)
    [OK] InnoDB log waits: 0.00% (0 waits / 489076 writes)
    
    -------- TokuDB Metrics ----------------------------------------------------------------------------
    [--] TokuDB is disabled.
    
    -------- Galera Metrics ----------------------------------------------------------------------------
    [--] Galera is disabled.
    
    -------- Replication Metrics -----------------------------------------------------------------------
    [--] Galera Synchronous replication: NO
    [--] No replication slave(s) for this server.
    [--] This is a standalone server.
    
    -------- Recommendations ---------------------------------------------------------------------------
    General recommendations:
        Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
        Reduce your overall MySQL memory footprint for system stability
        Dedicated this server to your database for highest performance.
        Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
        Adjust your join queries to always utilize indexes
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries which have no LIMIT clause
        Increase table_open_cache gradually to avoid file descriptor limits
        Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
        Beware that open_files_limit (8192) variable
        should be greater than table_open_cache ( 400)
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        query_cache_size (> 8M)
        join_buffer_size (> 8.0M, or always use indexes with joins)
        tmp_table_size (> 16M)
        max_heap_table_size (> 16M)
        table_open_cache (> 400)
        innodb_buffer_pool_size (>= 1G) if possible.
     
    #1 zaquria, Jun 15, 2016
    Last edited by a moderator: Jun 15, 2016
  2. 24x7server

    24x7server Well-Known Member

    Joined:
    Apr 17, 2013
    Messages:
    1,146
    Likes Received:
    34
    Trophy Points:
    48
    Location:
    India
    cPanel Access Level:
    Root Administrator
    Hello :),

    Have you tried to update this MySQLTuner scripts recommendations on your server? Please update it on your server and monitor mysql server performance for the next 24 hours. Also try to find out any particular user/databases is using high CPU and memory on your server.
     
  3. zaquria

    zaquria Member

    Joined:
    Dec 15, 2015
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    UK
    cPanel Access Level:
    Website Owner
    Hi,

    Thanks for answering :) No we haven't yet, we wanted to make sure that the information given by the Tuner was in the right direction before we started, but to see if there was any other additional tweak, changes or information that could help here.

    Regards,
     
  4. zaquria

    zaquria Member

    Joined:
    Dec 15, 2015
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    UK
    cPanel Access Level:
    Website Owner
    Just an update.

    We were given this config for MariaDB, but when restarting the Database it throws out an error:

    Code:
    [mysqld]
    symbolic-links=0
    max_connections=200
    max_user_connections=200
    wait_timeout=300
    interactive_timeout=300
    skip-name-resolve
    max-connect-errors=1000000
    
    #Slow Queries
    slow_query_log = 0
    long_query_time=5
    log_slow_verbosity=Query_plan,explain,Innodb
    
    #InnoDB
    innodb_file_per_table
    innodb_flush_method=O_DIRECT
    innodb_flush_log_at_trx_commit=0
    innodb_buffer_pool_size =2G
    innodb_log_file_size=256M
    innodb_read_io_threads=4
    innodb_write_io_threads=4
    innodb_buffer_pool_instances=2
    innodb_buffer_pool_dump_at_shutdown = 1
    innodb_buffer_pool_load_at_startup = 1
    transaction-isolation = READ-COMMITTED
    innodb-defragment=0
    innodb_file_format=BARRACUDA
    innodb_large_prefix=1
    
    #MyISAM Aria
    key_buffer_size=32M
    aria_pagecache_buffer_size=256M
    ft_min_word_len=3
    
    #Thread Cache
    thread_handling = pool-of-threads
    
    #Table cache
    table_open_cache=1000
    open_files_limit=2000
    
    #Buffers
    join_buffer_size=2M
    read_buffer_size=128K
    sort_buffer_size=256K
    
    #Query Cache
    query_cache_type=1
    query_cache_size=128M
    query_cache_limit=6M
    query_cache_strip_comments=1
    
    #Temporary Tables
    tmp_table_size=256M
    max_heap_table_size=256M
    
    This is the error that is thrown out:

    Code:
    160619 21:47:14 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
    2016-06-19 21:47:14 140260490676256 [Note] /usr/sbin/mysqld (mysqld 10.1.14-MariaDB) starting as process 22323 ...
    2016-06-19 21:47:14 140260490676256 [Note] InnoDB: Using mutexes to ref count buffer pool pages
    2016-06-19 21:47:14 140260490676256 [Note] InnoDB: The InnoDB memory heap is disabled
    2016-06-19 21:47:14 140260490676256 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
    2016-06-19 21:47:14 140260490676256 [Note] InnoDB: Memory barrier is not used
    2016-06-19 21:47:14 140260490676256 [Note] InnoDB: Compressed tables use zlib 1.2.3
    2016-06-19 21:47:14 140260490676256 [Note] InnoDB: Using Linux native AIO
    2016-06-19 21:47:14 140260490676256 [Note] InnoDB: Using SSE crc32 instructions
    2016-06-19 21:47:14 140260490676256 [Note] InnoDB: Initializing buffer pool, size = 2.0G
    InnoDB: mmap(1107296256 bytes) failed; errno 12
    InnoDB: Error: Block 0x7f90ecd2e1e8 incorrect state BUF_BLOCK_POOL_WATCH in buf_LRU_block_free_non_file_page()
    160619 21:47:14 [ERROR] mysqld got signal 11 ;
    This could be because you hit a bug. It is also possible that this binary
    or one of the libraries it was linked against is corrupt, improperly built,
    or misconfigured. This error can also be caused by malfunctioning hardware.
    Anyone know why this would be the case?
     
  5. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,767
    Likes Received:
    662
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Hello,

    You can search for each specific variable listed under "Variables To Adjust" to determine which configuration value it's referring to. For instance, with "query_cache_size", search engine results show a link to this page:

    MySQL :: MySQL 5.6 Reference Manual :: 8.10.3 The MySQL Query Cache

    You can review the comments section of this page to see how other users have utilized this option.

    As far as the issue when MySQL starts, try disabling the InnoDB variables one by one and restarting MySQL to see which one is the culprit.

    Thank you.
     
  6. twhiting9275

    twhiting9275 Well-Known Member

    Joined:
    Sep 26, 2002
    Messages:
    538
    Likes Received:
    15
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Twitter:
    High load isn't always high CPU. They can be, and often are very different.
    Start with adding SSD into your server if you can, offload MySQL onto that.
    Tweak settings, so you're using RAM not disk for storage and buffering
    These should both help your situation
     
  7. zaquria

    zaquria Member

    Joined:
    Dec 15, 2015
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    UK
    cPanel Access Level:
    Website Owner
    Just an update:

    This was added to the end of the my.cng and once we removed the highlighted line, the issue went away.

    We already have a SSD on the server.

    You will have to forgive me, but I was kinda thrown into the deep end when it comes to managing a server and I am learning as I go.

    The above my.cng was kindly worked out for me by someone else. I was under the impression that we were already using RAM rather than disk already?

    Some long winded info.

    Orginally the server we were on (CENTOS 6.8 x86_64 virtuozzo – server) had 1 gig of RAM and 2 gig of swap space while we were setting up. This had OPcache, Memcached and Pagespeed running and we had no memory issues at all.

    We then asked our hosting company to upgrade to 4gig, which they did and then we started having memory issues in WHM and our Forum software. We then contacted our hosting regarding this and they said that the forgot to add Swap memory to the build. However, we are still having issues with memory and slow server speeds
     
  8. zaquria

    zaquria Member

    Joined:
    Dec 15, 2015
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    UK
    cPanel Access Level:
    Website Owner
    Sorry the original server we were on was CENTOS 6.7 x86_64 kvm – server, we are now on CENTOS 6.8 x86_64 virtuozzo – server.

    Mem: 6340608k total, 5489540k used, 851068k free, 0k buffers
    Swap: 2097152k total, 267932k used, 1829220k free, 843660k cached
     
  9. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,767
    Likes Received:
    662
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Hello,

    Do you notice any VPS resource limits exceeded in the /proc/user_beancounters file (noticeable by fail counts)?

    Thank you.
     
  10. zaquria

    zaquria Member

    Joined:
    Dec 15, 2015
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    UK
    cPanel Access Level:
    Website Owner
    Sorry if a bit long winded.

    Code:
    Version: 2.5
      uid  resource           held              maxheld       barrier                              limit                                    failcnt
      41:  kmemsize       218483061  226496512  9223372036854775807  9223372036854775807    0
             lockedpages     5970            12070          9223372036854775807  9223372036854775807   0
             privvmpages    394736        1585152      1585152                          1585152                           493
             shmpages        8827            91275          9223372036854775807  9223372036854775807   0
             dummy            0                  0                  9223372036854775807  9223372036854775807   0
             numproc          295              432              9223372036854775807  9223372036854775807   0
             physpages       567035        1094965      9223372036854775807  9223372036854775807   0
             vmguarpages   0                  0                  9223372036854775807  9223372036854775807   0
             oomguarpages 273815        387012        9223372036854775807  9223372036854775807   0
             numtcpsock     37                97                9223372036854775807  9223372036854775807   0
             numflock          435              638              9223372036854775807  9223372036854775807   0
             numpty            0                  3                  9223372036854775807  9223372036854775807   0
             numsiginfo       0                  75                 9223372036854775807  9223372036854775807   0
             tcpsndbuf         667344        3159888      9223372036854775807  9223372036854775807   0
             tcprcvbuf          610568       862224         9223372036854775807  9223372036854775807   0
             othersockbuf    131784        1364560      9223372036854775807  9223372036854775807   0
             dgramrcvbuf     0                  13080          9223372036854775807  9223372036854775807   0
             numothersock  78                199              9223372036854775807  9223372036854775807   0
             dcachesize       195823133  196116566  9223372036854775807  9223372036854775807   0
             numfile             1962            3185            9223372036854775807  9223372036854775807   0
             dummy            0                  0                  9223372036854775807  9223372036854775807   0
             dummy            0                  0                  9223372036854775807  9223372036854775807   0
             dummy            0                  0                  9223372036854775807  9223372036854775807   0
             numiptent        74                74                9223372036854775807  9223372036854775807   0
    
    
     
    #10 zaquria, Jun 23, 2016
    Last edited by a moderator: Jun 23, 2016
  11. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,767
    Likes Received:
    662
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    The fail count for "privvmpages" suggests the VPS is running out of memory. You may want to consult with your VPS hosting provider to discuss adding additional memory to the VPS.

    Thank you.
     
Loading...

Share This Page