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:
- Using a sample of SELECT statements drupal is running, I added indexes to the ‘table’ in “WHERE ‘table’ …”.
- 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.
[size=large]Server specs[/size]
Virtual machine
4 cores on an Intel(R) Xeon(R) 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
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
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
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
Server version: Apache/2.4.10 (Debian)
cat /etc/apache2/mods-enabled/mpm_prefork.conf
<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[/code]
phpmyadmin reports the following variables as having ‘alert’ values:
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