2

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

closed as off-topic by Mark Kaplun, Tom J Nowell Oct 22 '17 at 22:28

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions that are too localized (such as syntax errors, code with restricted access, hacked sites, hosting or support issues) are not in scope. See how do I ask a good question?" – Mark Kaplun, Tom J Nowell
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    It is running on Apache – Ameer Elsherif Oct 21 '17 at 1:03
  • 1
    How can I check if it is RAID problem – Ameer Elsherif Oct 21 '17 at 1:04
  • 1
    Do you have access to your mysql slow query logs? That is the first thing I would check. You could run a plugin like Query Monitor which will show you the database queries. – user42826 Oct 22 '17 at 4:53
  • 1
    @CKMacLeod Yes, error logs are clear – Ameer Elsherif Oct 22 '17 at 21:12
  • 1
    @CKMacLeod I've disabled most of the plugins (It is a production site and some plugins are essential to its work). Right now I'm creating a replica of the website so I can completely disable all the plugins. But, as for DB repairs and optimizations, yes I've done that several times. – Ameer Elsherif Oct 22 '17 at 21:46