Skip to content

Instantly share code, notes, and snippets.

@ipbastola
Last active May 19, 2019 13:48
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save ipbastola/0834cd99279d51044ff8127259b76ec2 to your computer and use it in GitHub Desktop.
Save ipbastola/0834cd99279d51044ff8127259b76ec2 to your computer and use it in GitHub Desktop.
Wordpress database optimization and transients garbage collection

Nginx 502 Fix

References

https://www.digitalocean.com/community/questions/getting-nginx-fpm-sock-error

http://stackoverflow.com/questions/23443398/nginx-error-connect-to-php5-fpm-sock-failed-13-permission-denied/23487409#23487409

http://geektnt.com/how-to-disable-xmlrpc-php.html

https://www.digitalocean.com/community/tutorials/how-to-protect-an-nginx-server-with-fail2ban-on-ubuntu-14-04

https://lamosty.com/2015/04/securing-your-wordpress-site-running-on-nginx/

Before you start Snapshoot your Droplet

Paste this (at the end of the file) to /etc/sysctl.conf (using nano /etc/sysctl.conf) and save it (this change not only fixes your error it makes your System much better),

	### IMPROVE SYSTEM MEMORY MANAGEMENT ###

	# Increase size of file handles and inode cache
	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 = 65535

	# Increase number of incoming connections backlog
	net.core.netdev_max_backlog = 65535

	# 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 = 65535 131072 262144
	net.ipv4.udp_mem = 65535 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 65535 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

Paste this (at the end of the file) to /etc/security/limits.conf (using nano /etc/security/limits.conf) and save it,

	nginx           soft    nofile          2097152
	nginx           hard    nofile          2097152
	www-data        soft    nofile          2097152
	www-data        hard    nofile          2097152

Paste this (at the end of the file) to /etc/pam.d/common-session (using nano /etc/pam.d/common-session) and save it,

	session required pam_limits.so

Change listen.backlog in /etc/php5/fpm/pool.d/www.conf (using nano /etc/php5/fpm/pool.d/www.conf) and save it,

	listen.backlog = 65535

Change workerrlimitnofile in /etc/nginx/nginx.conf (using nano /etc/nginx/nginx.conf) and save it,

	worker_rlimit_nofile 99999;

Wordpress Site ( Block xmlrpc ), paste this on your wordpress nginx configuration

	# Block xmlrpc.php access
    location = /xmlrpc.php {
    	deny all;
    }

And finally reboot :-)

Wordpress DB Optimization

Reference

Transients garbage collection - Remove transients value from option table

Reference 1

Reference 2

Look into the existing transient data (you should have hundreds of thousands records)

SELECT * FROM `wp_options` WHERE `option_name` LIKE ('%\_transient\_%');	

Let's delete these data

DELETE FROM `wp_options` WHERE `option_name` LIKE ('%\_transient\_%');

Index autoload column

ALTER TABLE `wp_options` ADD INDEX(`autoload`);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment