I've been struggling with this website for a while now so any kind of help is deeply appreciated.
Server configurations
The website is running on a dedicated server from hostgator with the following specs:
- Intel Xeon-D CPU (8 Core / 16 Thread)
- 16 GB RAM
- CENTOS 6.9
- PHP 7.2
- PHP-FPM
PHP.ini
max_execution_time = 30
memory_limit = 1024M
PHP-FPM settings
pm = static
pm.max_children = 30
pm.max_requests = 500
MySQL conf.
query_cache_type=0
query_cache_size=0
thread_cache_size=10
table_open_cache=64M
tmp_table_size=512M
max_heap_table_size=512M
wait_timeout=3
interactive_timeout=10
key_buffer_size=256M
max_connections=150
innodb_file_per_table=1
innodb_buffer_pool_size=11G
innodb_log_file_size=1408M
innodb_log_buffer_size=128M
innodb_buffer_pool_instances=11
innodb_flush_log_at_trx_commit=0
innodb_read_io_threads=16
innodb_write_io_threads=8
DB total size: 3.9G
wp_posts: 438,724 rows
wp_postmeta: 3,091,747 rows partitioned by post_id as the following:
- p0 post_id < 608883 691655 rows (posts from 2014 and older)
- p1 post_id < 671052 621995 rows (posts from 2015)
- p2 post_id < 736922 869995 rows (posts from 2016)
- p3 post_id < MAXVALUE 999652 rows
The website is using W3 Total Cache plugin along with CloudFlare
The problems:
- When publishing posts to the website it sometimes take up to 3 mins just to publish one post.
- Publishing posts also causes very high load on the server causing it to completely shutdown and I get error 524 message from cloudflare.
- Admin panel pages suddenly becomes very slow and then very fast again with no reason.
- Finally, widgets admin page is extremely slow and almost never completes loading due to very large page size (firefox network inspector showed that downloaded page size was about 30MB!!)
I can provide whatever additional info that may help.
Thanks...
UPDATE:
Slow query log is showing the following 2 queries a lot:
# Query_time: 3.495343 Lock_time: 0.000042 Rows_sent: 0 Rows_examined: 1
# Rows_affected: 0
SET timestamp=1508709787;
UPDATE `wp_options` SET `option_value` = '1', `autoload` = 'no' WHERE `option_name` = 'nxs_v4APIMn';
# Query_time: 2.290696 Lock_time: 0.000108 Rows_sent: 1 Rows_examined: 282657
# Rows_affected: 0
SET timestamp=1508710080;
SELECT post_date_gmt FROM wp_posts WHERE post_status = 'publish' AND post_type IN ('post', 'page', 'attachment', 'pdfs', 'articles') ORDER BY post_date_gmt DESC LIMIT 1;
The first one is related to this plugin
