0

Im running AWS aurora 5.7. The application stack is PHP. After moving to Aurora, I noticed that the CPU consumption is really high. When I checked the performance insights, its showing the SET command is consuming more. (Green colour is CPU) enter image description here

And I checked the Aurora Billed Read IOPS, which is almost zero, I think most of them are in InnoDB buffer pool, I didn't see any slow queries, all are executed within a second.

So what Im thinking, the number of SQL queries on this server is a bit high, and thats why the Set command is showing more CPU.

enter image description here

Can someone help to understand this?

4
  • What does the slowlog say?
    – Rick James
    Commented Oct 23, 2021 at 6:22
  • nothing is there, the max long-running query time is 2sec
    – TheDataGuy
    Commented Oct 23, 2021 at 6:42
  • Lower it to "0.2"; maybe it will pick up some fast but frequent queries.
    – Rick James
    Commented Oct 23, 2021 at 15:57
  • Additional information request, please. What is AWS AURORA type being used? Any SSD or NVME devices on MySQL Host server? Post on pastebin.com and share the links. From your SSH login root, Text results of: A) SELECT COUNT(*) FROM information_schema.tables; B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; G) SHOW ENGINE INNODB STATUS; for server workload tuning analysis to provide suggestions. Commented Oct 25, 2021 at 14:09

1 Answer 1

0

Unfortunately CPU time as a single metric is never helpful. I'm well aware that all monitoring software show it, but still. You should look at specific types of CPU times instead, like iowait, to find out if SET NAMES is making the CPU work or is blocked by other operations..

Anyway, the library you use to connect MySQL could have an option to disable SET NAMES. All SET NAMES does is to set a session value for character_set_client, character_set_connection, and character_set_results.

So, in your case, you can disable SET NAMES=utf8 and set these server variables:

character_set_client=utf8
character_set_connection=utf8
character_set_results=utf8

If the overall CPU time remains unchanged, SET NAMES was probably just waiting for something else.

1
  • Yes the other wait io/table/sql/handler is the most resource-consuming wait.
    – TheDataGuy
    Commented Oct 24, 2021 at 2:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.