MariaDB + MySQL Governor on CloudLinux: High Load

A new account is generating very high MySQL load and huge binary logs, with impact on overall server load. Impact on server seems higher that account usage.

Reviewing MariaDB USER_STATISTICS table from information_schema (enabled plugin), compared usage of this account with next big user in various rows:
- CPU_TIME 12x higher
- BYTES_RECEIVED 3x
- BYTES_SENT 6x
- BINLOG_BYTES_WRITTEN 36x (getting 20Gb logs for 1 day)

There are sites logging 3x more connections generating lower load, so issue is related to specific queries from this account.

Enabled "all" mode in MySQL Governor so accounts are always in LVE, updated to 10.3.20-MariaDB , optimized binary logs configuration:
binlog_row_image = MINIMAL
binlog_annotate_row_events = OFF
binlog_format = MIXED
max_binlog_size = 512M
log_bin_compress = 1

Account does not show using more than allocated CPU in CloudLinux (100% meaning 1 core) but impact on server is big, with MySQL staying at 80% and total server load around 5/8.

Binary logs were enabled for reliability, because sometimes mysql can not recover without manual intevention, showing "[ERROR] Can't init tc log" . Will also evaluate longer with current MariaDB version.
Disabling binary logs does not seem to impact performance (similar mysql and server load) but would reduce extra load associated with writing tens of Gb of data per day.

How to give this account resources paid for (an entry plan with 1 CPU core, 2 Gb memory) while also protecting server from higher impact?

Any other suggestions for optimizing load and balancing resources / restrictions?

 

You need to investigate why MySQL is eating resources.
Try https://github.com/major/MySQLTuner-perl

Find out slow queries, and notify the client the need to optimize them in shared hosting environment.