We have a lot of apps using different databases running on our server. The mariadb process is using like 85% of the CPU currently which is really high. We want to find out where we could try and optimize things. Is it somehow possible to track CPU usage by db user, database or table? (the more finegrained the better obviously, but even something like % per user would already help a lot)
MySQL slow query log is already running with not many results. We've also tried doing SHOW PROCESSLIST; when CPU is spiking and looking at CPU_TIME in the user statistics, but it hasn't really been of much help.

CodePudding user response：

When having the userstat plugin enabled you can find CPU usage information per user in the INFORMATION_SCHEMA.USER_STATISTICS table. See

https://mariadb.com/kb/en/user-statistics/

and

https://mariadb.com/kb/en/information-schema-user_statistics-table/

CodePudding user response：

Turn on the slowlog and have long_query_time set rather low.

A few hours later, use pt-query-digest or mysqldumpslow -s t to summarize the output file.

Note: The slowlog records elapsed time, not CPU time. But generally elapsed it more important than CPU. And there is a correlation between the two.

More: SlowLog