Philip Willemse Posted April 11, 2023 Share Posted April 11, 2023 (edited) I hope someone can assist OR point me in the right direction... I am running WHMCS as streamlined as possible, yet it is very slow -> takes a few minutes to login etc. SETUP VM 12 Core vCPU 80GB RAM 15K SAS Storage SYSTEM CONFIGS WHMCS running on: OS: Ubuntu 20.04.5 LTS LATEST MYSQLTUNER RESULTS >> MySQLTuner 2.0.10 * Jean-Marie Renouard <jmrenouard@gmail.com> * Major Hayden <major@mhtx.net> >> 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] Logged in using credentials from Debian maintenance account. [OK] Currently running supported MySQL version 10.6.11-MariaDB-1:10.6.11+maria~ubu2004 [OK] Operating on 64-bit architecture -------- Log file Recommendations ------------------------------------------------------------------ [!!] Log file doesn't exist -------- Storage Engine Statistics ----------------------------------------------------------------- [--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE [--] Data in InnoDB tables: 2.4G (Tables: 275) [--] Data in Aria tables: 32.0K (Tables: 1) [--] Data in MyISAM tables: 682.1K (Tables: 24) [OK] Total fragmented tables: 0 -------- Analysis Performance Metrics -------------------------------------------------------------- [--] innodb_stats_on_metadata: OFF [OK] No stat updates during querying INFORMATION_SCHEMA. -------- Views Metrics ----------------------------------------------------------------------------- -------- Triggers Metrics -------------------------------------------------------------------------- -------- Routines Metrics -------------------------------------------------------------------------- -------- CVE Security Recommendations -------------------------------------------------------------- [--] Skipped due to --cvefile option undefined -------- Performance Metrics ----------------------------------------------------------------------- [--] Up for: 69d 20h 35m 1s (75M q [12.441 qps], 710K conn, TX: 149G, RX: 13G) [--] Reads / Writes: 73% / 27% [--] Binary logging is disabled [--] Physical Memory : 79.2G [--] Max MySQL memory : 54.0G [--] Other process memory: 0B [--] Total buffers: 28.3G global + 525.5M per thread (50 max threads) [--] Performance_schema Max memory usage: 106M [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 45.8G (57.88% of installed RAM) [OK] Maximum possible memory usage: 54.0G (68.26% of installed RAM) [OK] Overall possible memory usage with other process is compatible with memory available [OK] Slow queries: 0% (24/75M) [OK] Highest usage of available connections: 68% (34/50) [OK] Aborted connections: 0.00% (20/710380) [!!] Name resolution is active: a reverse name resolution is made for each new connection which can reduce performance [!!] Query cache may be disabled by default due to mutex contention. [OK] Query cache efficiency: 44.1% (41M cached / 95M selects) [!!] Query cache prunes per day: 82103 [OK] Sorts requiring temporary tables: 0% (93 temp sorts / 4M sorts) [!!] Joins performed without indexes: 67870 [!!] Temporary tables created on disk: 61% (2M on disk / 3M total) [OK] Thread cache hit rate: 99% (180 created / 710K connections) [OK] Table cache hit rate: 99% (107M hits / 107M requests) [OK] table_definition_cache (1000) is greater than number of tables (591) [OK] Open file limit used: 0% (115/32K) [OK] Table locks acquired immediately: 100% (379K immediate / 379K locks) -------- Performance schema ------------------------------------------------------------------------ [--] Performance_schema is activated. [--] Memory used by Performance_schema: 106.8M [--] Sys schema is installed. -------- ThreadPool Metrics ------------------------------------------------------------------------ [--] ThreadPool stat is disabled. -------- MyISAM Metrics ---------------------------------------------------------------------------- [!!] Key buffer used: 18.3% (23.4M used / 128.0M cache) [OK] Key buffer size / total MyISAM indexes: 128.0M/95.0K [OK] Read Key buffer hit rate: 100.0% (309K cached / 19 reads) [OK] Write Key buffer hit rate: 99.6% (242 cached / 241 writes) -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is enabled. [OK] InnoDB File per table is activated [OK] InnoDB buffer pool / data size: 20.0G / 2.4G [OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 5.0G * 1/20.0G should be equal to 25% [--] Number of InnoDB Buffer Pool Chunk: 160 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: 100.00% (1296534953432 hits / 1296534984618 total) [!!] InnoDB Write Log efficiency: 2043.86% (17600336 hits / 861134 total) [OK] InnoDB log waits: 0.00% (0 waits / 18461470 writes) -------- Aria Metrics ------------------------------------------------------------------------------ [--] Aria Storage Engine is enabled. [OK] Aria pagecache size / total Aria indexes: 128.0M/352.0K [OK] Aria pagecache hit rate: 99.1% (127M cached / 1M 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: 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). Temporary table size is already large: reduce result set size Reduce your SELECT DISTINCT queries without LIMIT clauses Variables to adjust: skip-name-resolve=1 query_cache_size (=0) query_cache_type (=0) query_cache_size (> 512K) join_buffer_size (> 3.0M, or always use indexes with JOINs) key_buffer_size (~ 24M) Edited April 11, 2023 by Philip Willemse Remove Info 0 Quote Link to comment Share on other sites More sharing options...
Kezkx Posted April 17, 2023 Share Posted April 17, 2023 Vérifiez les exigences du système: Assurez-vous que votre serveur Web répond aux exigences minimales recommandées par WHMCS pour le bon fonctionnement du logiciel. Vous pouvez trouver ces exigences sur le site Web de WHMCS. Mettez à jour votre version de PHP: Si vous utilisez une version de PHP obsolète, cela peut affecter les performances de WHMCS. Assurez-vous d'utiliser une version prise en charge et à jour de PHP. 0 Quote Link to comment Share on other sites More sharing options...
brianoz Posted April 17, 2023 Share Posted April 17, 2023 (edited) I'm afraid I can't offer much of a solution, but one starting point would be to enable MySQL's slow query log in /etc/my.cnf or on the MySQL command line. Instructions here. Run a few slow logins, take note of the time, then check out the logs and the output of mysqldumpslow. This tells you which MySQL queries are running slow; and you then can potentially tune or report those to WHMCS and here. You should also submit a ticket to WHMCS asap so they have a chance to follow up. You also need to say which version of WHMCS you're running on? Edited April 17, 2023 by brianoz updates 0 Quote Link to comment Share on other sites More sharing options...
Philip Willemse Posted April 19, 2023 Author Share Posted April 19, 2023 On 4/17/2023 at 7:53 AM, Kezkx said: Vérifiez les exigences du système: Assurez-vous que votre serveur Web répond aux exigences minimales recommandées par WHMCS pour le bon fonctionnement du logiciel. Vous pouvez trouver ces exigences sur le site Web de WHMCS. Mettez à jour votre version de PHP: Si vous utilisez une version de PHP obsolète, cela peut affecter les performances de WHMCS. Assurez-vous d'utiliser une version prise en charge et à jour de PHP. Merci pour vos retours, j'utilise ceci : PHP 7.4 WHMCS 8.6.1 Me conseillez-vous de passer à PHP 8.1 ? J'ai essayé de le faire la dernière fois et pour une raison quelconque, mon WHMCS exécuté sur NGINX ne voulait pas fonctionner, je ne sais pas où je dois changer cela sur NGINX/WHMCS pour utiliser PHP 8.1 au lieu de PHP 7.4. 0 Quote Link to comment Share on other sites More sharing options...
Philip Willemse Posted April 19, 2023 Author Share Posted April 19, 2023 On 4/17/2023 at 11:00 AM, brianoz said: I'm afraid I can't offer much of a solution, but one starting point would be to enable MySQL's slow query log in /etc/my.cnf or on the MySQL command line. Instructions here. Run a few slow logins, take note of the time, then check out the logs and the output of mysqldumpslow. This tells you which MySQL queries are running slow; and you then can potentially tune or report those to WHMCS and here. You should also submit a ticket to WHMCS asap so they have a chance to follow up. You also need to say which version of WHMCS you're running on? Thank you for the feedback, I am currently running PHP 7.4 & WHMCS 8.6.1, I will post my slow query outputs as soon as I have implemented this. 0 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.