I've been struggling with this website for a while now so any kind of help is deeply appreciated.
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
max_execution_time = 30 memory_limit = 1024M
pm = static pm.max_children = 30 pm.max_requests = 500
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
- 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.
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