0

all the pages on the website are taking more than 1 minutes to be displayed, and sometimes they are showing Connection Reset, I had a previous issue which caused the server to be down once I clear the cache / enable a module and to solve it I have added the Asynchronous Prefetch Database Query Cache Module, I have even switched to a new Server.

Error that's being logged is the following:

[27-Sep-2016 14:55:10 UTC] PHP Warning:  Error while sending QUERY packet. PID=11602 in /home/****/public_html/includes/database/database.inc on line 2204
[27-Sep-2016 14:55:10 UTC] PHP Warning:  Error while sending QUERY packet. PID=11592 in /home/****/public_html/sites/all/modules/apdqc/apdqc.mysql.inc on line 871
[27-Sep-2016 14:55:10 UTC] PHP Warning:  Error while sending QUERY packet. PID=11592 in /home/****/public_html/includes/database/database.inc on line 2204
[27-Sep-2016 14:55:10 UTC] PHP Warning:  Error while sending QUERY packet. PID=11593 in /home/****/public_html/includes/database/database.inc on line 2204
[27-Sep-2016 14:55:10 UTC] PHP Warning:  Error while sending QUERY packet. PID=11597 in /home/****/public_html/sites/all/modules/apdqc/apdqc.mysql.inc on line 871
[27-Sep-2016 14:55:10 UTC] PHP Warning:  Error while sending QUERY packet. PID=11597 in /home/****/public_html/includes/database/database.inc on line 2204

Setting / specification of server and website are right below:

  • Drupal 7
  • PHP Version 5.6.x
  • realpath_cache_size 150k
  • realpath_cache_ttl 4000
  • memory_limit 1200M
  • Apache 2.4
  • MYSQL DB Server Version 5.6.X

$conf['cache_backends'][] = 'sites/all/modules/apdqc/apdqc.cache.inc';

$conf['cache_default_class'] = 'APDQCache';

$databases['default']['default']['unix_socket'] = '/var/lib/mysql/mysql.sock';

$databases['default']['default']['host'] = NULL;

$conf['lock_inc'] = 'sites/all/modules/apdqc/apdqc.lock.inc';

$databases['default']['default']['init_commands']['innodb_lock_wait_timeout'] = "SET SESSION innodb_lock_wait_timeout = 100";

$databases['default']['default']['init_commands']['wait_timeout'] = "SET SESSION wait_timeout = 1000";

$databases['default']['default']['init_commands']['read_rnd_buffer_size'] = "SET SESSION read_rnd_buffer_size = 8388608";

$conf['session_inc'] = 'sites/all/modules/apdqc/apdqc.session.inc';

$databases['default']['default']['init_commands']['isolation'] = "SET SESSION tx_isolation='READ-COMMITTED'";

$databases['default']['default']['init_commands']['join_buffer_size'] = "SET SESSION join_buffer_size = 8388608";

in an Average day, we have almost 300 concurrent users. the Process Manager is showing a huge CPU % usage from the following COMMAND

/usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/lib/mysql/****.*******.***.err --open-files-limit=10000 --pid-file=/var/lib/mysql/****.*******.***.pid

What I want is to know how to track what is the issue here? and why there's this huge of CPU Usage, and how to stop the Connection Reset of the pages / elements inside the website?

PS: I have disabled the ERROR logging, access log, no devel module is added, the cron is set to be run every day at midnight, my status report has zero errors/notices related to apqdc and there's an Expire caching module, in addition to XMLSITEMAP, and Metatags

[edit]

  • Model: HP - DL180 G9
  • Processor: 2x Intel Hexa-Core Xeon E5-2620v3 [ 6 Core(s) ]
  • RAM: 64 GB
  • HDD: 2x 480GB
  • Linux CENTOS 7.2
  • WHM version 58.0

my.cnf information:

core file size          (blocks, -c) 1000000
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 256494
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 4096
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 14335
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited


default-storage-engine=MyISAM
innodb_file_per_table=1
performance-schema=0
max_allowed_packet=268435456
open_files_limit=107000
innodb_buffer_pool_size=5G
innodb_checksum_algorithm=crc32
innodb_flush_method=O_DIRECT
innodb_read_io_threads=50
innodb_spin_wait_delay=30
innodb_sync_spin_loops=200
innodb_write_io_threads=20
metadata_locks_hash_instances=256
binlog_format=ROW
innodb_flush_log_at_trx_commit=2
innodb_autoinc_lock_mode=2
table_open_cache_instances=250
table_open_cache=10000
thread_cache_size=100
max_user_connections=800
max_connections=7000
innodb_lock_wait_timeout=25
wait_timeout=600
read_rnd_buffer_size=8388608
join_buffer_size=8388608
query_cache_size=2000000

php.ini information:

 max_execution_time = 200
max_input_time = 120
memory_limit = 250M

closed as too broad by Kevin, Pierre.Vriens, Shawn Conn, Adrian Cid Almaguer, Yusef Mohamadi Sep 28 '16 at 6:24

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • This problem was solved, by hosting provider, where they have re installed the whole web service Apache, which fixed all the performance issues on the website. thank you all for your support. – Rafah Nehmeh Oct 4 '16 at 11:59
0

The php errors you listed happen in core and in apdqc. This php bug report points to the max_allowed_packet being too small. The php memory limit shouldn't cause the issue you are describing. Another cause could be a low wait_timeout value, but you are setting that to 1000.

If you can move the 'init_commands' from the settings.php into your my.conf file that has a small chance of improving your issue. You can remove them and then go to the status report page and apdqc should tell you what to add to my.conf.

In terms of getting to the bottom of this issue; knowing the stacktrace of what caused this is key. This core patch is a must: Add backtrace to all errors and add $conf['error_level'] = 2; to your settings.php file. If you have syslog use that, if not re-enable the dblog in order to log this issue.

  • Thanks :) , I will follow the steps and update you, but I think it was related to a module called advagg for compressing and bundling JS and CSS, because once it was disabled the website is back to normal, I am still keeping an eye on the mysql CPU Usage,and I will definitely follow your mentioned steps. Thanks again @miketown2 – Rafah Nehmeh Sep 28 '16 at 5:25
  • Hey, I having in the log error a lot of the following sentence [core:error] [pid 25305] [client 154.73.30.40:39378] AH00126: Invalid URI in request Connection: Close – Rafah Nehmeh Sep 28 '16 at 14:57
  • @RafahNehmeh all that is saying is that the settings used for connecting to the db is failing. – mikeytown2 Sep 28 '16 at 15:23
  • I guess it's something to go with the actual configuration that I am using, it's not optimized, I will update my questions to list all the specification of the server that I am using in addition to my.cnf and php.ini max values, Can you confirm if it's actually optimized or should be updated? – Rafah Nehmeh Sep 29 '16 at 6:35
  • The APDQC status report covers most of the basic things that need to be adjusted in my.conf. Invalid URI my guess is this is an issue with the db connection configuration, which is usually inside of your setting.php file (don't post the contents of it for security reasons). How did tracking down your original issue go PHP Warning: Error while sending QUERY packet? Do you have a backtrace of the error now? – mikeytown2 Sep 29 '16 at 20:04
1

Reduce the php memory limit to no more than 512M. Depending on the version of PHP you have, anything higher (even 512) is ineffective and can cause issues.

Secondly, completely uninstall apdqc module, and see if that alleviates the issue.

Third, profile your application with xhprof and/or New Relic and/or Blackfire.io.

Without knowing more about your setup, you might want to review the issue queue for APDQC module to narrow down what the issue could be:

https://www.drupal.org/project/issues/apdqc?categories=All

  • Thanks for your reply, I will try to follow the above steps, but have one concern, that's before apdqc module, the whole server was going down because of metadata lock issue, and this module fixed this issue for me , maybe I have missed some configuration, while adding the module , the module owner is @mikeytown2 – Rafah Nehmeh Sep 27 '16 at 17:50
  • what kind of more setup information do you need? – Rafah Nehmeh Sep 27 '16 at 17:51
  • This question looks very similar. drupal.stackexchange.com/a/108673/57 – Kevin Sep 27 '16 at 17:53
  • how is it similar? the module is already installed and the deadlock was removed, and the solution is already integrated in my website, my problem is that I have a lot of an ERR_CONNECTION_RESET, and then if I refreshed the page the page will take up 60 seconds to work. – Rafah Nehmeh Sep 27 '16 at 18:03
  • You'll need to profile that yourself though @RafahNehmeh, it can't be done from here. All we can give you are generic, broad suggestions that may or may not be relevant; it's why this type of question isn't really suited to Q+A – Clive Sep 27 '16 at 18:54

Not the answer you're looking for? Browse other questions tagged or ask your own question.