How do I fix CPU usage at 100% on a windows server running php?

Question

I am running php on a Windows server (2019) VM in Azure. On that VM I have IIS v10, PHP v8.2.3, and MySQL v8 running several Wordpress sites. In task manager and perfmon, I see that there can be 3 or 4 fastcgi processes running, that combined, use 100% cpu and slow my Wordpress sites down to a crawl. This will happen at random times for up to 8 hours and then resolve for days. I have seen answers for this but none that seem to fit or are very detailed. I am fairly certain that this is not php code errors - I have checked php error logs and don't see anything that should cause this. Thanks for any help!!! At this point I could really use it!

Answer 1

Rate Per Second = RPS

Suggestions to consider for your Azure portal, Settings, Server parameters.

read_rnd_buffer_size=16384  # from 256K to reduce handler_read_rnd_next RPS of 12,934.
thread_cache_size=48  # from 10 to support 46 concurrent max_used_connections.
tmp_table_size=64M  # from ~ 640M for a more reasonable size.
max_heap_table_size=64M  # from 16M for best practice of always = tmp_table_size.

There are many more opportunities to improve performance. View my profile.

Observations, We do not normally see both A) innodb_log_file_size and B) innodb_redo_log_capacity in a configuration. Eventually, innodb_redo_log_capacity will replace innodb_log_file_size, when Azure enforces the new name only. select_scan reported 114,985 full table scans. Appropriate indexes will relieve much CPU busy. JetProfiler.com has a free version to assist in identifying the tables needing indexes.