Optimal my.cnf settings for a VPS running MariaDB?

Legendary

Member
Aug 13, 2015
24
1
3
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 <[email protected]>
>>  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.
 
Last edited by a moderator:

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,909
2,228
463
We're looking to improve performance as it can be slow at times
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.
 

soaringeagle

Member
Oct 24, 2015
11
0
1
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%