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.

Optimal my.cnf settings for a VPS running MariaDB?

Discussion in 'Workarounds and Optimization' started by Legendary, Oct 14, 2015.

  1. Legendary

    Legendary Member

    Joined:
    Aug 13, 2015
    Messages:
    24
    Likes Received:
    1
    Trophy Points:
    3
    Location:
    US
    cPanel Access Level:
    Root Administrator
    Hello,

    Need help in tweaking a friend's SSD VPS running cPanel with MariaDB. Someone else worked on it before and has this in their config:

    Code:
    [mysqld]
    performance-schema=0
    default-storage-engine=MyISAM
    innodb_file_per_table=1
    max_allowed_packet=268435456
    
    max_connections=75
    max_user_connections=25
    symbolic-links=0
    query_cache_limit=2M
    query_cache_size=32M
    key_buffer_size=32M
    innodb_buffer_pool_size=64M
    table_open_cache=1024
    table_definition_cache=1024
    thread_cache_size=4
    tmp_table_size=24M
    max_heap_table_size=24M
    open_files_limit=10000
    
    local-infile=0
    We're looking to improve performance as it can be slow at times. The server hosts one IPB forum and three WordPress sites. The server has 2GB ram and access to 4 CPU cores.


    mysqltuner:

    Code:
    >>  MySQLTuner 1.5.1 - Major Hayden <major@mhtx.net>
    >>  Bug reports, feature requests, and downloads at [URL='http://mysqltuner.com/']MySQLTuner-perl by major[/URL]
    >>  Modified by George Liu (eva2000) at [URL='http://vbtechsupport.com/']vbtechsupport.com[/URL]
    >>  Run with '--help' for additional options and output filtering
    [OK] Currently running supported MySQL version 10.0.21-MariaDB
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MyISAM
    [--] Data in MyISAM tables: 35M (Tables: 317)
    [--] Data in InnoDB tables: 1M (Tables: 35)
    [!!] Total fragmented tables: 47
    
    -------- Security Recommendations  -------------------------------------------
    [OK] There is no anonymous account in all database users
    [OK] All database users have passwords assigned
    [!!] There is not basic password file list !
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 19h 26m 42s (403K q [5.763 qps], 16K conn, TX: 1B, RX: 53M)
    [--] Reads / Writes: 83% / 17%
    [--] Binary logging is disabled
    [--] Total buffers: 296.0M global + 2.8M per thread (75 max threads)
    [OK] Maximum reached memory usage: 329.4M (17.63% of installed RAM)
    [OK] Maximum possible memory usage: 504.6M (27.00% of installed RAM)
    [OK] Slow queries: 0% (0/403K)
    [OK] Highest usage of available connections: 16% (12/75)
    [OK] Aborted connections: 0.34%  (57/16692)
    [OK] Query cache efficiency: 41.9% (196K cached / 468K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2K sorts)
    [!!] Temporary tables created on disk: 59% (13K on disk / 23K total)
    [OK] Thread cache hit rate: 72% (4K created / 16K connections)
    [OK] Table cache hit rate: 84% (769 open / 913 opened)
    [OK] Open file limit used: 9% (993/10K)
    [OK] Table locks acquired immediately: 100% (119K immediate / 119K locks)
    
    -------- MyISAM Metrics -----------------------------------------------------
    [!!] Key buffer used: 28.7% (9M used / 33M cache)
    [OK] Key buffer size / total MyISAM indexes: 32.0M/6.0M
    [OK] Read Key buffer hit rate: 99.4% (535K cached / 3K reads)
    [!!] Write Key buffer hit rate: 69.6% (91K cached / 27K writes)
    
    -------- InnoDB Metrics -----------------------------------------------------
    [--] InnoDB is enabled.
    [OK] InnoDB buffer pool / data size: 64.0M/1.2M
    [!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1).
    [!!] InnoDB Used buffer: 17.88% (732 used/ 4095 total)
    [OK] InnoDB Read buffer efficiency: 99.82% (331804 hits/ 332391 total)
    [!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
    [OK] InnoDB log waits: 0.00% (0 waits / 5645 writes)
    
    -------- AriaDB Metrics -----------------------------------------------------
    [--] AriaDB is disabled.
    
    -------- Replication Metrics -------------------------------------------------
    [--] No replication slave(s) for this server.
    [--] This is a standalone server.
    I'd appreciate any recommendations.
     
    #1 Legendary, Oct 14, 2015
    Last edited by a moderator: Oct 14, 2015
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    653
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Hello :)

    You may want to run a command such as "mysqladmin processlist" during the times it's slow to see if any particular database is the culprit. I'll leave this thread open for additional user-feedback about the optimization to your MySQL configuration.

    Thank you.
     
  3. soaringeagle

    soaringeagle Member

    Joined:
    Oct 24, 2015
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    usa
    cPanel Access Level:
    Root Administrator
    well i'm no expert but i have been tuning my own server awhile learning along the way
    your query cache hit rates low so increase the query cache size
    you only have 2 gigs ram wich isn't a whole lot but your not using much of it at all the more ram you use (within reason) the better the performance
    so i would start by doubling the query cache size
    take it from there
    you also didn't include the recommendations from the tuner script
    which i bet said to increase query cache size?

    you can safely allocate up to about 70% of available ram to database caches and buffers and the more ram is utilized the less it will have to access tables from disk

    generally after its been running 24 hours the recommendations provided can be trusted usually, some may require a little research before you tweak them
    i'd also consider doubling your ram, and then using more of it for cache and buffers
    the query cache hit rate you want around 99%
     
  4. anton_latvia

    anton_latvia Well-Known Member
    PartnerNOC

    Joined:
    May 11, 2004
    Messages:
    348
    Likes Received:
    3
    Trophy Points:
    18
    Location:
    Latvia
    cPanel Access Level:
    Root Administrator
Loading...

Share This Page