Hi
I started a new droplet on DigitalOcean (48GB ram, 960GB SSD, 12 vCPU) and installed WordOps on it. (php7.3) Then I migrated my present WordPress to it. Once I changed the DNS, CPU usage for mysql went through the roof.
On running top
, it shows 900 to 1000% CPU usage for mysql
😮
I ran mysqltuner and made a few changes, but it still wasn't enough. PHP-FPM and nginx is running smoothly.
performance_schema = ON
max_connections = 1000
connect_timeout = 5
wait_timeout = 60
max_allowed_packet = 64M
thread_cache_size = 128
sort_buffer_size = 4M
bulk_insert_buffer_size = 16M
tmp_table_size = 256M
max_heap_table_size = 256M
default_storage_engine = InnoDB
# you can't just change log file size, requires special procedure
innodb_log_file_size = 1811M
innodb_buffer_pool_size = 14488M
innodb_log_buffer_size = 3622M
innodb_file_per_table = 1
innodb_open_files = 500000
innodb_io_capacity = 500000
innodb_flush_method = O_DIRECT
innodb_read_io_threads=16
innodb_write_io_threads=16
innodb_buffer_pool_instances = 15
Also I tried editing the /etc/sysctl.conf
file and added the following entries, still CPU usage is not lowering:
fs.file-max = 2097152
# Do less swapping
vm.swappiness = 10
vm.dirty_ratio = 60
vm.dirty_background_ratio = 2
### GENERAL NETWORK SECURITY OPTIONS ###
# Number of times SYNACKs for passive TCP connection.
net.ipv4.tcp_synack_retries = 2
# Allowed local port range
net.ipv4.ip_local_port_range = 2000 65535
# Protect Against TCP Time-Wait
net.ipv4.tcp_rfc1337 = 1
# Decrease the time default value for tcp_fin_timeout connection
net.ipv4.tcp_fin_timeout = 15
# Decrease the time default value for connections to keep alive
net.ipv4.tcp_keepalive_time = 300
net.ipv4.tcp_keepalive_probes = 5
net.ipv4.tcp_keepalive_intvl = 15
### TUNING NETWORK PERFORMANCE ###
# Default Socket Receive Buffer
net.core.rmem_default = 31457280
# Maximum Socket Receive Buffer
net.core.rmem_max = 12582912
# Default Socket Send Buffer
net.core.wmem_default = 31457280
# Maximum Socket Send Buffer
net.core.wmem_max = 12582912
# Increase number of incoming connections
net.core.somaxconn = 65536
# Increase number of incoming connections backlog
net.core.netdev_max_backlog = 65536
# Increase the maximum amount of option memory buffers
net.core.optmem_max = 25165824
# Increase the maximum total buffer-space allocatable
# This is measured in units of pages (4096 bytes)
net.ipv4.tcp_mem = 65536 131072 262144
net.ipv4.udp_mem = 65536 131072 262144
# Increase the read-buffer space allocatable
net.ipv4.tcp_rmem = 8192 87380 16777216
net.ipv4.udp_rmem_min = 16384
# Increase the write-buffer-space allocatable
net.ipv4.tcp_wmem = 8192 65536 16777216
net.ipv4.udp_wmem_min = 16384
# Increase the tcp-time-wait buckets pool size to prevent simple DOS attacks
net.ipv4.tcp_max_tw_buckets = 1440000
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_tw_reuse = 1
Please help.