Drupal 7 dreadful TTFB

[Jesse Gersenson]

https://www.webpagetest.org/result/17092...view_step1

Description of problem: Long TTFB times, but not on every page load.

Basics: Site is running Drupal 7. DB and site are on the same box. In front of this is a proxy running apache and storing some static files to disk. We typically have about 17 apache processes running. We are not running Memcached or any other db caching layer.

What I've tried:
1. Using a sample of SELECT statements drupal is running, I added indexes to the 'table' in "WHERE 'table' ...".
2. tweaked a number of db variable values, mostly taken from "High Performance Drupal" (O'Reily, Oct 2013).

This is my first time working with Drupal and I assume this is a drupal-specific issue. Any direction and insight would be much appreciated.


Server specs
----------------
Virtual machine
4 cores on an Intel® Xeon® CPU E5-2630 v4 @ 2.20GHz
8 gb ram
~$ uname -a
3.16.0-4-amd64 #1 SMP Debian 3.16.x x86_64 GNU/Linux

cat /proc/meminfo

Code:

MemTotal:        8197700 kB
MemFree:          163740 kB
MemAvailable:    4570400 kB
Buffers:          301296 kB
Cached:          4108032 kB
SwapCached:        10888 kB
Active:          4809464 kB
Inactive:        2803576 kB
Active(anon):    2713264 kB
Inactive(anon):   561388 kB
Active(file):    2096200 kB
Inactive(file):  2242188 kB
Unevictable:           0 kB
Mlocked:               0 kB
SwapTotal:       1150972 kB
SwapFree:        1129336 kB
Dirty:                68 kB
Writeback:             0 kB
AnonPages:       3192988 kB
Mapped:           129272 kB
Shmem:             70940 kB
Slab:             345652 kB
SReclaimable:     321688 kB
SUnreclaim:        23964 kB
KernelStack:        2944 kB
PageTables:        24912 kB
NFS_Unstable:          0 kB
Bounce:                0 kB
WritebackTmp:          0 kB
CommitLimit:     5249820 kB
Committed_AS:    5890260 kB
VmallocTotal:   34359738367 kB
VmallocUsed:      163984 kB
VmallocChunk:   34359568916 kB
HardwareCorrupted:     0 kB
AnonHugePages:         0 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
DirectMap4k:      106432 kB
DirectMap2M:     8282112 kB
DirectMap1G:     2097152 kB

ENV
php -v

Code:

PHP 5.6.30 (cli) Copyright (c) 1997-2016 The PHP Group Zend Engine v2.6.0, Copyright (c) 1998-2016 Zend Technologies
     with Zend OPcache v7.0.6-dev, Copyright (c) 1999-2016, by Zend Technologies

mysql -V

Code:

mysql  Ver 14.14 Distrib 5.5.57, for debian-linux-gnu (x86_64) using readline 6.3

Current settings, attached 'mysql-show-variables.txt'

apache2 -v

Code:

Server version: Apache/2.4.10 (Debian)

cat /etc/apache2/mods-enabled/mpm_prefork.conf

Code:

<IfModule mpm_prefork_module>
        StartServers                     5
        MinSpareServers           5
        MaxSpareServers          15
        MaxRequestWorkers         180
        MaxConnectionsPerChild   0
</IfModule>

iostat -cx

Code:

Linux 3.16.0-4-amd64     09/20/2017     _x86_64_    (4 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
            3.96    0.00    0.32    0.94    0.00   94.78

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s
avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0.08     4.46    1.63   33.53    46.02  1301.15
76.62     0.12    3.38    9.30    3.09   0.54   1.89

phpmyadmin reports the following variables as having 'alert' values:

Code:

Aborted connects    2
Created tmp disk tables    16.2 k
Handler read rnd    28.4 M
Handler read rnd next    275.7 M
Innodb buffer pool pages dirty    6.2 k
Innodb buffer pool reads    84.3 k
Innodb row lock time avg    182
Innodb row lock time max    3.5 k
Innodb row lock waits    239
Opened tables    3.8 k
Qcache lowmem prunes    757.6 k
Select full join    7.2 k
Slow queries    2
Sort merge passes    1
Table locks waited    17

[beyondstop]

Hi Jesse,

Your issue seems to be your SSL certificate. Check if it's expired or something's wrong with it.

Have you tried running the same test from other WPT machines? I doubt anything's wrong with your test machine itself, but you never know. So I would try another machine. If you get the same results, I think the issue is with your certificate.

I'm not a Drupal expert, but I don't think it's the cause of this particular problem. Looking at the waterfall, there are no requests for a little over 1 second between the WPT machine and your server. There is little CPU usage and no BW usage during this time.

After your initial request to https://www.upce.cz/vvr/ved-projekty.html on request #3, I noticed in the color breakdown that communication stops after SSL negotiation. Then, about 1 second later, two HTTP requests go out to ocsp.digicert.com to check the revocation status of your certificate.

This wait time seems to be the reason why it takes so long for WPT to get the first byte from your server.

So I would take a look at any issues with the server's SSL certificate.

I would also look at what's happening on the backend, if you can. What communication is your server having with other servers that WPT does not see?

Lastly, if all else fails, I would run another WPT test with tcpdump enabled, and take a look at what's happening during the SSL key exchange.


Hope that helps.


- Jean

[datadiggers]

Notice that the first two requests are to SSL CA to verify your SSL cert. This is unnecessary. Enable OCSP stapling - that should do it.