High load into database

[bohdanmukovozov]

We are running PowerDNS on a FreeBSD 13.3 system with a MySQL backend. Occasionally, the server generates a high load on the MySQL database, primarily due to a specific query being executed repeatedly and excessively. The query in question is:
select domains.id, domains.name, records.content, domains.type, domains.master, domains.notified_serial, domains.last_check, domains.account, domains.catalog from domains LEFT JOIN records ON records.domain_id=domains.id AND records.type='SOA' AND records.name=domains.name WHERE records.disabled=0 OR 1;
Impacts DNS Server Performance:
• High database load results in slower responses from the PowerDNS server, degrading DNS query resolution times or causing timeouts.
I use this config for pdns:

	# Basic configuration
# The IP address PowerDNS will listen on
local-address=0.0.0.0
# The DNS port, default is 53
local-port=53
# MySQL pooling for connection management
# Enable and set size for packet cache
reuseport=yes
# The module to launch, set it to 'gmysql' for MySQL backend
launch=gmysql

# MySQL backend configuration
gmysql-host=localhost       # IP address of the MySQL server
gmysql-user=pdns_user       # Username for the MySQL database
gmysql-password=password   # Password for the MySQL user
gmysql-dbname=pdns       # The database name

# gmysql-dnssec should be enabled if DNSSEC is required
gmysql-dnssec=yes


# Logging settings
# Enable logging to the syslog
log-dns-queries=no
loglevel=9                   # Log level (0-9), 4 is informational

api=yes
api-key=api_key

# Webserver settings (for API access)
webserver=yes
webserver-address=192.168.0.1
webserver-port=8081
webserver-allow-from=192.168.0.0/24
setuid=pdns
setgid=pdns
resolver=127.0.0.1:5353

PDNS version 4.9.1. Maybe you can give advise

[zeha]

select domains.id, domains.name, records.content, domains.type, domains.master, domains.notified_serial, domains.last_check, domains.account, domains.catalog from domains LEFT JOIN records ON records.domain_id=domains.id AND records.type='SOA' AND records.name=domains.name WHERE records.disabled=0 OR 1;

This is get-all-domains-query.

How often is that executed? How many domains do you have? What form of traffic does your pdns receive?

Please give more detail to work with.

[bohdanmukovozov]

Currently, there are 22,977 domains in PowerDNS. This process is executed approximately once a day. At the moment, the application writes new zones to PowerDNS, but we do not use PowerDNS for resolving zones yet. We plan to switch to using it for resolution, but we are encountering some issues.

When the issue occurs, the server experiences a load average (LA) of around 120, which continues to increase. To mitigate this, we need to stop PowerDNS for 3-5 minutes and then restart it, after which the system functions normally.

[zeha]

I didn't see a lot of answers in your answer, but if the query is a problem, make sure all indexes exist and your mysql service is correctly scaled/tuned.