Optimal my.cnf settings for a VPS running MariaDB?

Legendary

• October 14, 2015 04:01

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:
[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:
>> MySQLTuner 1.5.1 - Major Hayden >> Bug reports, feature requests, and downloads at MySQLTuner-perl by major >> Modified by George Liu (eva2000) at vbtechsupport.com >> 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.

• 

  cPanelMichael

  • October 14, 2015 14:35

  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.

  0
• 

  soaringeagle

  • October 25, 2015 21:21

  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%

  0
• 

  anton_latvia

  • October 27, 2015 09:40

  Check out couple of my tests and recommendations Optimizing MySQL, Intermediate results - Norsk Webhotell og Domener

  0

Please sign in to leave a comment.