1

I need suggestions for my.cnf optimization. Currently I have a server with 256GB Ram and 40(CPUs) along with website hosted on this server.

My Wordpress database size is around 1GB and using myISAM tables.

[mysqld]
bind-address    = 127.0.0.1
performance-schema=1
# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /var/lib/mysql/mysql.sock
pid-file                       = /var/lib/mysql/mysql.pid
#tmpdir                = /tmp
tmpdir                 = /dev/shm

# MyISAM #
key-buffer-size                = 1024M
myisam-sort-buffer-size        = 512M
join_buffer_size = 4M
read_buffer_size = 4M
sort_buffer_size = 4M
read_rnd_buffer_size = 4M
myisam-recover                 = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000
skip-external-locking
skip-name-resolve
# skip-networking

# CACHES AND LIMITS #

tmp_table_size=512M
max_heap_table_size=512M
query_cache_size=128M
query_cache_type = 1
query_cache_limit=3M
wait_timeout=60
interactive_timeout=60
thread_cache_size=128


#tmp-table-size                 = 32M
#max-heap-table-size            = 32M
#query-cache-type               = 1
#query-cache-size               = 0
max-connections                 = 20000

#thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 4096
table-open-cache               = 16384

# INNODB #
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 2048M
innodb_buffer_pool_instances = 2
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_thread_concurrency = 0
innodb_read_io_threads=16
innodb_write_io_threads=16

# SLOW QUERY
slow_query_log=1
slow_query_log_file="/var/log/mysql-slow.log"
long_query_time =10

#max_allowed_packet=268435456
max_allowed_packet=524288000
open_files_limit=30000
innodb_file_per_table=1

closed as too broad by Colin 't Hart, RDFozz, Marco, mustaccio, LowlyDBA May 16 '18 at 15:45

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 3
    Let me get this straight! You are hosting a 1GB WordPress site on a machine with 256GB Ram and 40(CPUs)? Look here - From my research about MyISAM vs InnoDB, I learned that InnoDB is capable of taking advantage of multiple cores, whereas MyISAM can only use one core and InnoDB also has other features like being able to recover better from a crash, and being more stable in general. Put the site on a server VM on using,max 2 CPUs and 4GB of RAM, use the rest of the machine for other work, it's wasted serving WordPress – Vérace May 14 '18 at 19:46
  • 2
    Does your site have any performance issues? You don't mention any. – Colin 't Hart May 14 '18 at 20:44
  • Additional information request, please. Post on pastebin.com or here. RAM on your Host server complete (not edited) my.cnf-ini Text results of: A) SHOW GLOBAL STATUS; B) SHOW GLOBAL VARIABLES; C) complete MySQLTuner.com report Optional very helpful information, if available includes - htop OR top for most active apps, ulimit -a for a linux/unix list of limits, iostat -x when system is busy for an idea of IOPS by device, df -h for a linux/unix free space list by device, includes hda & sda (SSD) filesystem type clues for server tuning analysis. – Wilson Hauck May 15 '18 at 14:50
  • Change innodb-buffer-pool-size to about 70% of available RAM. Don't worry about any other tunables until there is some reason. And do not use MyISAM. – Rick James May 18 '18 at 4:18
  • 1
    @Vérace - InnoDB will not use multiple CPUs for a query; it does have some background threads handling I/O, etc. This still applies even with Version 8.0, native partitioning, etc. (Still, the OP should switch away from MyISAM.) – Rick James May 27 '18 at 5:06
0

Suggestions for your my.cnf-ini [mysqld] section from the limited available information provided,

max_connect_errors=10  # from 1000000 - allow hackers/crackers nn pw guesses
sort_buffer_size=4M  # REMOVE or lead with # for default
read_buffer_size=4M  # REMOVE or lead with # for default
read_rnd_buffer_size=4M  # REMOVE or lead with # for default
thread_cache_size=100  # from 128 see V8 REFMAN for CAP to prevent OOM

Take 2 minutes with MySQLCalculator.com to see what your values have done to the RAM footprint requirements that are extreme and will negatively impact performance.

Eliminate the duplicate variables in your my.cnf-ini and be consistent with underscores rather than mixing in hyphens.

Provide the additional information requested a few hours ago for additional helpful suggestions to improve performance.

Good luck with this new venture on this nice equipment configuration. Any SSD's?

Not the answer you're looking for? Browse other questions tagged or ask your own question.