optimize my.cnf

[jlucho]

hi guys



I have a cpanel server with many websites. mostly in wordpress
server is dual xeon E5-2430 + 64Gb ram + disk 1Tb hdd

my setting my.cnf is

cat /etc/my.cnf

[mysqld]
performance-schema=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
local-infile=0
symbolic-links=0
performance-schema=ON
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
default-storage-engine = MyISAM
innodb_file_per_table = 1
innodb_buffer_pool_size = 134217728
max_allowed_packet = 268435456
open_files_limit=50000
join_buffer_size = 64M
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 1M
tmp_table_size = 256M
max_heap_table_size = 256M
table_open_cache = 1500
#innodb_buffer_pool_instances = 8
#innodb_buffer_pool_size = 8G
#innodb_log_file_size = 1G
#innodb_file_per_table = 1
#interactive_timeout = 100

after running MySQLtunner, the recommendation is

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Control warning line(s) into /var/log/mysqld.log file
    Control error line(s) into /var/log/mysqld.log file
    4 user(s) used basic or weak password.
    MySQL was started within the last 24 hours - recommendations may be inaccurate
    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
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
    Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/
    This is MyISAM only table_cache scalability problem, InnoDB not affected.
    See more details here: https://bugs.mysql.com/bug.php?id=49177
    This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
    Beware that open_files_limit (50000) variable
    should be greater than table_open_cache (1500)
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
    join_buffer_size (> 64.0M, or always use indexes with JOINs)
    table_open_cache (> 1500)
    innodb_buffer_pool_size (>= 2.6G) if possible.
    innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

please guys, will you have any suggestions to optimize mysql,

 

[GOT]

The primary things you can control are

join_buffer_size (> 64.0M, or always use indexes with JOINs)
table_open_cache (> 1500)
innodb_buffer_pool_size (>= 2.6G) if possible.

I would probably set innodb_buffer_pool_size to 4G.

As for the other two, you just start creeping them up until mysqltuner stops complaining.

As for the last one, you can change that settings after you finish changing innodb_buffer_pool_size because eaqch time you change that its going to change the sugegsted log setting size.

 

[cPanelLauren]

Thanks @GOT - @jlucho let us know if you have any further questions