sites are running slow due to high TTFB

F

fullshop

Verified User
Joined
Jun 30, 2010
Messages
12
Hello,
For some time I'm struggling with websites heaving high TTFB (up to 5-6 seconds). The issue is not with a particular website as I'm seeing this behaviour with all the websites on the server.

I'll add some info what I have checked so far and what are the system resources.

Server is a VM on a cluster.
OS: Latest Cloudlinux 7.9 with all the updates installed.
Directadmin: latest version, all the custombuild updates are applied.

Workload on the machine is very low.
System resources: 32 cores, 80GB of ram.
CPU/RAM average load: CPU: 2%, ram usage 15gb out of 80GB.
System load average: 1.59, 1.85, 1.95

Testing website:
prestashop 1.7.8.8
mariaDB 10.5
PHP 7.4
CageFS enabled and disabled
CloudLinux LVE: do no limit enabled.
LiteSpeed server installed (tested with apache+nginx as well, same result).
LiveSpeed cache plugin for prestashop enabled.
No firewalls are present.
No plugins are installed, default installation with default theme for testing purposes.
MySQL Governor: off
Memory limit set to 1gb.

Tested with a local HTML file (everything is instant), DNS issue is excluded.
While testing with PHP/mysql the 5s TTFB is present.
On the website after everything is cached the load is instant. While adding a product to cart it's giving again TTFB up to 5sec as this part is not cached and it's triggering the DB.


Additional troubleshooting:
Installed mairadb on a different VM (same cluster) and connected the site to the new db via local network, same results.
Moved the website and mysql to a dummy ubuntu 20.04, everything runs instant.

IPv6 disabled, using IPv4
Connectivity / Internet is not an issue, 10G capacity.

Moved the entire VM with directadmin to a test new hdd, a samsung 990 pro nvme drive, to exclude a HDD fault and I have the same results.
No faults on dmesg
Low traffic nothing fancy on journalctl.
Jumbo frames activated with 9000 MTU

I'm stuck, no idea what to check from this point, maybe someone can give me some hints/help to continue the troubleshooting :)

Cheers!
 
Last edited:
What shows webpagetest website? check mysql configuration, check what shows "atop" when pages opened, maybe you will se bottleneck. check logs (mysql, litespeed)
 
Check your MySQL server settings using MySQLTuner, it may give you some hints as to your SQL performance issues. Best if SQL server has been running for at least a day for the SQL server to collect it's internal stats. If there is a lot of tables or InnoDB usage, defaults may not be enough for best performance. Don't go crazy increasing numbers too far beyond what the tool suggests or you may run into other problems. It's also good to run on normally running servers to catch problems early.

github.com

GitHub - major/MySQLTuner-perl: MySQLTuner is a script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability.

MySQLTuner is a script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability. - major/MySQLTuner-perl
github.com github.com

PS: jumbo frames 9000 will not help you with internet bound traffic, standard MTU is 1500 or less for clients, it's fine for backup/storage networks if configured correctly.
 
Thanks guys for the feedback!

Zhenyapan said:
What shows webpagetest website? check mysql configuration, check what shows "atop" when pages opened, maybe you will se bottleneck. check logs (mysql, litespeed)
Click to expand...
Regarding the atop, nothing fancy there: screenshot here
Nothing outstanding in the logs mysql/litespeed. Also if a move the db on another server the results are the same. On the same server if I move the site as well, everything is instant. As for the litespeed I have changed to default nginx+apache and had the same results.

cjd said:
Check your MySQL server settings using MySQLTuner
Click to expand...
Regarding MySQLTuner, I have used the tuner and everything is optimised:

Code: 
[client-server]
!includedir /etc/my.cnf.d

[mysqld]
tmp_table_size = 128M
join_buffer_size = 4M
key_buffer_size = 390M
skip-external-locking
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
max_heap_table_size = 128M
innodb_file_per_table = true
default-storage-engine = innodb
performance-schema = 0
innodb_buffer_pool_size = 48G
innodb_buffer_pool_instances = 32
innodb_log_file_size = 8G
max_connections = 300
query_cache_type = 0
query_cache_size = 0
performance_schema = on
max_allowed_packet = 512M
open_files_limit = 50000
innodb_large_prefix = true
innodb_file_format = barracuda
thread_cache_size = 100
table_definition_cache = 32768
table_open_cache = 32768
slow_query_log = 1
slow-query_log_file = /var/log/mysql-slow.log
long_query_time = 2
bind-address = 0.0.0.0
thread_concurrency = 32
local-infile=0

[mysqldump]
max_allowed_packet = 512M

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout


Code: 
# ./mysqltuner.pl
 >>  MySQLTuner 2.0.9
         * Jean-Marie Renouard <[email protected]>
         * Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.pl/
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 10.5.18-MariaDB-cll-lve-log
[OK] Operating on 64-bit architecture
 
-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /var/log/mysqld.log exists
[--] Log file: /var/log/mysqld.log (0B)
[--] Log file /var/log/mysqld.log is empty. Assuming log-rotation. Use --server-log={file} for explicit file
 
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in MyISAM tables: 5.7G (Tables: 2460)
[--] Data in InnoDB tables: 5.2G (Tables: 5564)
[!!] Total fragmented tables: 1
 
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
 
-------- Views Metrics -----------------------------------------------------------------------------
 
-------- Triggers Metrics --------------------------------------------------------------------------
 
-------- Routines Metrics --------------------------------------------------------------------------
 
-------- Security Recommendations ------------------------------------------------------------------
[!!] User ''@'localhost' is an anonymous account. Remove with DROP USER ''@'localhost';
[OK] All database users have passwords assigned
[!!] There is no basic password file list!
 
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
 
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 2d 23h 36m 7s (4M q [16.226 qps], 147K conn, TX: 28G, RX: 981M)
[--] Reads / Writes: 93% / 7%
[--] Binary logging is disabled
[--] Physical Memory     : 78.5G
[--] Max MySQL memory    : 203.6G
[--] Other process memory: 0B
[--] Total buffers: 48.6G global + 528.3M per thread (300 max threads)
[--] Performance_schema Max memory usage: 215M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 62.3G (79.32% of installed RAM)
[!!] Maximum possible memory usage: 203.6G (259.39% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (47/4M)
[OK] Highest usage of available connections: 8% (26/300)
[!!] Aborted connections: 3.96% (5829/147079)
[!!] Name resolution is active: a reverse name resolution is made for each new connection which can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (9 temp sorts / 397K sorts)
[!!] Joins performed without indexes: 57336
[!!] Temporary tables created on disk: 30% (71K on disk / 237K total)
[OK] Thread cache hit rate: 99% (26 created / 147K connections)
[OK] Table cache hit rate: 99% (5M hits / 5M requests)
[OK] table_definition_cache (32768) is greater than number of tables (8220)
[OK] Open file limit used: 15% (5K/32K)
[OK] Table locks acquired immediately: 99% (861K immediate / 861K locks)
 
-------- Performance schema ------------------------------------------------------------------------
[--] Performance_schema is activated.
[--] Memory used by Performance_schema: 215.9M
[--] Sys schema is not installed.
 
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
 
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 19.2% (74.8M used / 390.0M cache)
[OK] Key buffer size / total MyISAM indexes: 390.0M/408.3M
[OK] Read Key buffer hit rate: 99.9% (3M cached / 2K reads)
[!!] Write Key buffer hit rate: 72.3% (14K cached / 10K writes)
 
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 48.0G / 5.2G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (16.6666666666667%): 8.0G * 1 / 48.0G should be equal to 25%
[--] Number of InnoDB Buffer Pool Chunk: 384 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.91% (325045833 hits / 325351603 total)
[!!] InnoDB Write Log efficiency: 500.38% (143373 hits / 28653 total)
[OK] InnoDB log waits: 0.00% (0 waits / 172026 writes)
 
-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/320.0K
[!!] Aria pagecache hit rate: 94.9% (1M cached / 66K reads)
 
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
 
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
 
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
 
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: MIXED
[--] XA support enabled: ON
[--] Semi synchronous replication Master: OFF
[--] Semi synchronous replication Slave: OFF
[--] This is a standalone server
 
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
      OPTIMIZE TABLE `test`.`test`; -- can free 143 MB
    Total freed space after theses OPTIMIZE TABLE : 143 Mb
    Remove Anonymous User accounts - there are 2 anonymous accounts.
    Restrict Host for 'supervizare'@'%' to 'supervizare'@LimitedIPRangeOrLocalhost
    RENAME USER 'supervizare'@'%' TO 'supervizare'@LimitedIPRangeOrLocalhost;
    Reduce your overall MySQL memory footprint for system stability
    Dedicate this server to your database for highest performance.
    Reduce or eliminate unclosed connections and network issues
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
             See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
             (specially the conclusions at the bottom of the page).
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Consider installing Sys schema from https://github.com/mysql/mysql-sys for MySQL
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    skip-name-resolve=1
    join_buffer_size (> 4.0M, or always use indexes with JOINs)
    tmp_table_size (> 128M)
    max_heap_table_size (> 128M)
    key_buffer_size (~ 78M)
    innodb_log_file_size should be (=12G) if possible, so InnoDB total log files size equals 25% of buffer pool size.

cjd said:
PS: jumbo frames 9000 will not help you with internet bound traffic, standard MTU is 1500 or less for clients, it's fine for backup/storage networks if configured correctly
Click to expand...
The network is 9000 and I do have some folders mounted via NFS for backup/storage and this helps with massive backups. In my network everything is 12k. There are no issues with latency/networking.

Cheers!
 
skip-name-resolve=1
tmp_table_size=512M
max_heap_table_size=512M
innodb_buffer_pool_size = 32G
innodb_read_io_threads=24
innodb_write_io_threads=12
innodb_flush_log_at_trx_commit=2
----also---- if nvme
innodb_io_capacity=4000
innodb_io_capacity_max=8000
if sata-ssd
innodb_io_capacity=400
innodb_io_capacity_max=1200
 
but as you said - on another clean ubuntu all works by default faster - maybe main case is LVE or Governor or something similar, maybe it not fully turned off?
 
Max MySQL memory usage is concerning.
InnoDB settings seems set a little high.
Reduce Max Connections (it shows you had max 26, and you have it at 300, reduce to say 100-150), using way too much memory.
Adding skip name resolve doesn't hurt.


Also, you should probably check your kernel swappiness i would recommend setting it to 1

www.howtoforge.com

How to change the Swappiness of your Linux system

Swappiness is the kernel parameter that defines how much (and how often) your Linux kernel will copy RAM contents to swap. This parameters default va...
www.howtoforge.com

Is the system currently using any swap space?

That's fine with the jumbo frames, was just commenting on it as you had mentioned it. I always make my storage/backup networks completely separate interface/network from internet bound traffic (and un-routeable private switched networks when ever possible). Any of this will have no bearing on your current issue.
 
Zhenyapan said:
skip-name-resolve=1
tmp_table_size=512M
max_heap_table_size=512M
innodb_buffer_pool_size = 32G
innodb_read_io_threads=24
innodb_write_io_threads=12
innodb_flush_log_at_trx_commit=2
----also---- if nvme
innodb_io_capacity=4000
innodb_io_capacity_max=8000
if sata-ssd
innodb_io_capacity=400
innodb_io_capacity_max=1200
Click to expand...
Applied those values, thank you. There is no improvement. I do have an issue with skip-name-resolve after this is activated the logins to MySQL are not working.
Code: 
[Warning] Access denied for user 'the_user'@'127.0.0.1' (using password: YES)
Disabled the skip-name-resolve to clear the issue.

Zhenyapan said:
but as you said - on another clean ubuntu all works by default faster - maybe main case is LVE or Governor or something similar, maybe it not fully turned off?
Click to expand...
This might be, is there a way to check this?
What I did from CLI was: dbctl unrestrict-all
From the GUI I have: MySQL Governor Mode of operation => off, monitoring only.


cjd said:
Is the system currently using any swap space?
Click to expand...
I have 0 swap usage.

cjd said:
Also, you should probably check your kernel swappiness i would recommend setting it to 1
Click to expand...
I have changed it to 1. No improvement. But free ram memory was never an issue. Using less than 10%.
Code: 
~]# cat /proc/sys/vm/swappiness
1

Thanks guys for the help!
 
Last edited:
I have increased the cores to 72 for test purposes, and enabled the multiqueue to 64.
A snip with the load can be found here
 
cloudlinux now with nginx+apache +lsphp? it installed with cldeploy or from clean cloudlinux os? try enable governor and check is it correctly installed 105 version for 10.5 maria. Also all cloudlinux licenses has support, you can ask them to check - they helped me few times.
 
Zhenyapan said:
cloudlinux now with nginx+apache +lsphp? it installed with cldeploy or from clean cloudlinux os? try enable governor and check is it correctly installed 105 version for 10.5 maria. Also all cloudlinux licenses has support, you can ask them to check - they helped me few times.
Click to expand...
Cloudlinux is set with LiteSpeed + lsphp.
P.S. If you guys are using mod_lsapi read this, it will help. The config for DirectAdmin can be found here: /etc/httpd/conf/extra/mod_lsapi.conf

Regarding the installation I don't remember, to be honest, it was installed many years back 2014-2015 I guess when I migrated from centos6 :))

I have raised a ticket to cloudlinux, and provided them access to the server, waiting for feedback. So far I don't have high hopes based on the replies in the ticker provided, fingers crossed :)

I'll try with the governor after the support is done to not mess with their work.
 
I don't like upgrades, it takes a lot of old stuff from old OS - better to reinstall, just installed fresh Cloudlinux 8.7 from their ISO this friday - all works fine. With 16core cpu +nvme 500gb backups 330 accounts restored in 3 hours, OS installation and configuration takes more :)
 
Zhenyapan said:
I don't like upgrades, it takes a lot of old stuff from old OS - better to reinstall, just installed fresh Cloudlinux 8.7 from their ISO this friday - all works fine. With 16core cpu +nvme 500gb backups 330 accounts restored in 3 hours, OS installation and configuration takes more :)
Click to expand...
I keep postponing moving everything to a cloudlinux 8.7 as I might miss moving or mess something up :)) Need to get up to speed with the cloudlinux settings migration procedure and then do a backup/restore on directadmin. But before getting more work into the bucket I just want to get to the bottom of this issue as I'm getting crazy. Maybe a fresh reinstall will clear it, who knows :)
 
you can try install another VPS vith clean cloudlinux, if their support will not solve problem on current server.
 
Zhenyapan said:
you can try install another VPS vith clean cloudlinux, if their support will not solve problem on current server.
Click to expand...
Well, they escalated to 2nd level and they are asking again the same questions answered. Nowadays the support is mostly template based :)
The feedback I got is that I need to optimize my website, not to mention that I have provided them 2 Prestashop URLs with the default install, one on DirectAdmin and another one on a different VM, same cluster, same resources, no tunning with centos7 default install to compare the same thing...
 
I have uninstalled Governor and reverted to default DirectAdmin custombuild mariadb10.5, with no improvements.
 
You must log in or register to reply here.
Back
Top