MySql my.cnf recommended settings

FerdieQO

Well-known member
Joined
Jul 15, 2016
Messages
213
Points
28
Can anyone recommend me the best settings for MySQL my.cnf for two VPS 2GB RAM and 4GB of RAM?
I want to optimize mysql my.cnf to improve performance the best for my websites.
 

sh-admin

Active member
Registered
Joined
Sep 29, 2016
Messages
66
Points
0
use the mysql tuner script to get the exact parameters in order to improve your my.cnf configuration.

# wget http://mysqltuner.com/mysqltuner.pl
# chmod +x mysqltuner.pl
# ./mysqltuner.pl

and it will give you which values needs to be changed.
 

FerdieQO

Well-known member
Joined
Jul 15, 2016
Messages
213
Points
28
FerdieQO
Great share, this is the first time I have heard of this tool.

I am installing it and checking which it can give me best my.cnf.

Will have any questions if I don't know how to use mysqltuner


Thanks!
 

FerdieQO

Well-known member
Joined
Jul 15, 2016
Messages
213
Points
28
Hi sh admin,

Your link
Code:
wget http://mysqltuner.com/mysqltuner.pl
didn't work

when running it showing these lines

Code:
root@myvps[/usr/local/bin]# wget http://mysqltuner.pl/mysqltuner.pl
--2016-10-03 20:26:50--  http://mysqltuner.pl/mysqltuner.pl
Resolving mysqltuner.pl... 217.70.184.38
Connecting to mysqltuner.pl|217.70.184.38|:80... connected.
HTTP request sent, awaiting response... 301 Moved Permanently
Location: https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl/mysq      ltuner.pl [following]
--2016-10-03 20:26:50--  https://raw.github.com/major/MySQLTuner-perl/master/mys      qltuner.pl/mysqltuner.pl
Resolving raw.github.com... 151.101.48.133
Connecting to raw.github.com|151.101.48.133|:443... connected.
HTTP request sent, awaiting response... 301 Moved Permanently
Location: https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltu      ner.pl/mysqltuner.pl [following]
--2016-10-03 20:26:50--  https://raw.githubusercontent.com/major/MySQLTuner-perl      /master/mysqltuner.pl/mysqltuner.pl
Resolving raw.githubusercontent.com... 151.101.48.133
Connecting to raw.githubusercontent.com|151.101.48.133|:443... connected.
HTTP request sent, awaiting response... 404 Not Found
2016-10-03 20:26:51 ERROR 404: Not Found.
and I could not run next command

I tried to search on the internet and found the working link

Code:
cd /usr/local/bin

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

chmod +x mysqltuner.pl

/usr/local/bin/mysqltuner.pl
and I got the result after running mysql tuner

Code:
root@myvps [/usr/local/bin]# /usr/local/bin/mysqltuner.pl
 >>  MySQLTuner 1.6.20 - Major Hayden <[email protected]>
 >>  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 5.5.51-38.2
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics ---------------------------------------------      --------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +My      ISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 1G (Tables: 474)
[--] Data in InnoDB tables: 322M (Tables: 1377)
[OK] Total fragmented tables: 0

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 11d 19h 57m 3s (50K q [0.050 qps], 7K conn, TX: 7M, RX: 8M)
[--] Reads / Writes: 32% / 68%
[--] Binary logging is disabled
[--] Physical Memory     : 2.8G
[--] Max MySQL memory    : 583.2M
[--] Other process memory: 261.2M
[--] Total buffers: 168.0M global + 2.8M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 184.5M (6.41% of installed RAM)
[OK] Maximum possible memory usage: 583.2M (20.25% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (12/50K)
[OK] Highest usage of available connections: 3% (6/151)
[!!] Aborted connections: 37.83%  (3026/7999)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 65 sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 17% (191 on disk / 1K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 1% (400 open / 23K opened)
[OK] Open file limit used: 15% (162/1K)
[OK] Table locks acquired immediately: 99% (20K immediate / 20K locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 8 thread(s).
[!!] thread_pool_size between 16 and 36 when using InnoDB storage engine.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 21.7% (1M used / 8M cache)
[!!] Key buffer size / total MyISAM indexes: 8.0M/210.8M
[!!] Read Key buffer hit rate: 27.8% (2M cached / 1M reads)
[!!] Write Key buffer hit rate: 0.1% (173K cached / 173K writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[!!] InnoDB buffer pool / data size: 128.0M/322.7M
[OK] InnoDB buffer pool instances: 1
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 96.73% (425820 hits/ 440195 total)
[!!] InnoDB Write Log efficiency: 0% (8 hits/ 0 total)
[OK] InnoDB log waits: 0.00% (0 waits / 8 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:
    Enable the slow query log to troubleshoot bad queries
    Reduce or eliminate unclosed connections and network issues
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    Set thread_cache_size to 4 as a starting value
    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 (1024) variable
    should be greater than table_open_cache ( 400)
    Thread pool size for InnoDB usage (8)
Variables to adjust:
    query_cache_type (=0)
    thread_cache_size (start at 4)
    table_open_cache (> 400)
    thread_pool_size between 16 and 36 for InnoDB usage
    key_buffer_size (> 210.8M)
    innodb_buffer_pool_size (>= 322M) if possible.
 
Latest Threads

Latest Hosting OffersNew Reviews

Sponsors

Tag Cloud

You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.

Top