Score:1

high CPU usage of MySQL and recommendation from Mysqltunner

sn flag

we have noticed that our database server is getting so high CPU (more than 90 % used by mysqld process), the output of mysqltunner is shown below, can you help me please,

i'm okay about the innodb_buffer_pool_instances(=7), but is there some other suggestions ? enter image description here

enter image description here

enter image description here

Michael Hampton avatar
cz flag
Please post the complete output from mysqltuner as text, not as a screenshot.
Hamza AZIZ avatar
sn flag
all information are listed in the screenshots from mysqltuner, do you need further infos ?
us flag
It has to be text so that it is readable.
Wilson Hauck avatar
jp flag
Additional information request. 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: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) STATUS; not SHOW STATUS, just STATUS; AND Optional very helpful information, if available includes - htop OR top for most active apps, ulimit -a for a Linux/Unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions.
Score:2
ua flag

http://mysql.rjweb.org/doc.php/mysql_analysis

That has two parts -- one is a deeper dive into the settings on your machine. The other is capturing the slowlog.

High CPU almost always means poor indexing and/or poor formulation of queries. The slowlog is a very efficient way to find the "worst" queries. Then we can discuss how to improve them.

Tuner...

  • The number of instances is not worth changing.
  • 7G for the buffer_pool is too big for a 7.8G machine. Limit it to about 70% of RAM; let's say 5G.
  • Was MySQL swapping? That is terrible for performance (but won't show up as high CPU).
Hamza AZIZ avatar
sn flag
Thanks for your response, the slow query logs file list about 59781 lines of queries, what do you think, the problem more related to indexing queries... or to hardware resources : RAM
ua flag
Use `pt-query-digest` to summarize the 59781 lines; then study the first few. I prefer to improve indexes and/or formulation in preference to adding RAM.
Hamza AZIZ avatar
sn flag
this is the first time i heard about this tool : pt-query-digest, i google it, but i didn't find what i'm looking for, this tool will just have read access of logs right ? it is safe to use ?
ua flag
@HamzaAZIZ - https://www.percona.com/doc/percona-toolkit/LATEST/pt-query-digest.html -- Download the "Percona toolkit"; it is one of the tools. If you are running on Windows, send me the slowlog; I can get it to run.
ua flag
The tool does need access to the slowlog file. If you are running on a cloud, look in the UI for a way to download the log. It may also include ways to turn on logging.
Hamza AZIZ avatar
sn flag
yes, i'm trying now to download the file to a test machine so I can test that without installing the percona toolkit in the database server, thank you so much
mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.