High CPU overload, on VPS with lots of free RAM.

[upravitel]

Hi everyone.

Our e-commerce website (wordpress/woocommerce) uses a lot of CPU and very little RAM (on average only 20%).
Server handles traffic OK, till we have more than 60 clients at the same time (more than 100 page views per minute) server than suddenly starts to overload CPU and gets extremely slow and sometimes even locks, and there is a lot of unused RAM.

Using my mySQLtuner recommendations we managed to reduce the load a little, but the problem still persists whenever we have more than 60 people on the website at the same time.
Also enabling query cache reduced overload a little, although mysqlTuner reccomends we switch it off.

Server Information:
FastCGI, Worker with SSL on premium VPS with 6 xeon cores, 8 GB RAM and SSD with WHM and cPanel.

Database is 240MB with 55 MyISAM and 19 InnoDB tables.


Here is the output of MySQLtuner>

[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.6.33
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 236M (Tables: 55)
[--] Data in InnoDB tables: 816K (Tables: 19)
[OK] Total fragmented tables: 0

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] There are 612 basic passwords in the list.

-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 1d 9h 42m 16s (16M q [132.731 qps], 107K conn, TX: 85G, RX: 2G)
[--] Reads / Writes: 97% / 3%
[--] Binary logging is disabled
[--] Physical Memory     : 7.7G
[--] Max MySQL memory    : 673.9M
[--] Other process memory: 748.6M
[--] Total buffers: 504.0M global + 1.1M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 558.0M (7.09% of installed RAM)
[OK] Maximum possible memory usage: 673.9M (8.56% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/16M)
[OK] Highest usage of available connections: 31% (48/151)
[OK] Aborted connections: 0.01%  (16/107027)
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Sorts requiring temporary tables: 1% (6K temp sorts / 376K sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 74% (88K on disk / 117K total)
[OK] Thread cache hit rate: 98% (1K created / 107K connections)
[OK] Table cache hit rate: 86% (223 open / 258 opened)
[OK] Open file limit used: 4% (226/5K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[OK] Key buffer used: 100.0% (8M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/36.3M
[OK] Read Key buffer hit rate: 99.9% (631M cached / 387K reads)
[OK] Write Key buffer hit rate: 98.5% (6M cached / 99K writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 128.0M/816.0K
[OK] InnoDB buffer pool instances: 1
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 97.81% (23116 hits/ 23633 total)
[!!] InnoDB Write Log efficiency: 275% (11 hits/ 4 total)
[OK] InnoDB log waits: 0.00% (0 waits / 15 writes)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
Variables to adjust:
    query_cache_type (=0)
    tmp_table_size (> 96M)
    max_heap_table_size (> 96M)

Here is the content of my.cnf

[mysqld]
performance-schema=0
innodb_file_per_table=1
innodb_buffer_pool_size=134217728
max_allowed_packet=268435456
default-storage-engine=MyISAM
query_cache_limit=64M
query_cache_size=256M
tmp_table_size=96M
max_heap_table_size = 96M
innodb_buffer_pool_instances = 1
query_cache_type=1
skip-name-resolve

Thank you in advance for your time and the advice.

 

[cPanelMichael]

Hello,

You may also want to review the following thread, in addition to optimizing your MySQL configuration:

Troubleshooting high server loads on Linux servers

Thank you.

 

[upravitel]

Thank you, excellent thread indeed, and I will definitely read it thoroughly.

However I was hoping to get some specific advice for my exact configuration and problem.

Advice like add/remove/modify this in config files, or set this i WHM.

 

[PlotHost]

Did you try a WP cache plugin?

 

[upravitel]

Did you try a WP cache plugin?

Thank you for the suggestion, option of caching was explored.


However I'm looking specific advice for my.cnf and whm.

I will appreciate if someone with knowledge and experience looks into my initial post above and give me some specific suggestions on how to optimize the VPS server with my.cnf or WHM.

 

[ElviCities]

You may have more luck asking in a WP forum, as it appears that the server load is caused by WP and not cpanel/whm.