Opened 8 years ago

Last modified 8 years ago

#3866 new Bug/Something is broken

control panel is painfully slow when dealing any decent-sized list of DNS records

Reported by: https://id.mayfirst.org/dkg Owned by: https://id.mayfirst.org/jamie
Priority: Medium Component: Tech
Keywords: red performance mysql Cc:
Sensitive: no

Description

I've been doing DNS auditing and cleanup.

Much of the cleanup i've tried to do through the control panel.

the control panel is painfully slow (20 second pageloads) when dealing with pagefuls of only a few dozen DNS records. When doing a lot of different operations, these slow pageloads add up to a lot of time wasted.

On the idea that there might have been a single query chewing up a lot of mysql daemon resources, i enabled the slow query log in harry:/etc/mysql/my.cnf, and restarted mysql:

log_slow_queries	= /var/log/mysql/mysql-slow.log
long_query_time = 2

however, nothing showed up in that log. Looking at top, i could see that harry was spending all its cpu time in userspace, and that apache was consuming more CPU than mysql during these pageloads (but mysql was still active at around 20% CPU).

This suggested that apache might be doing a lot of little SQL requests, each of which were mildly expensive. So i enabled log-queries-not-using-indexes on mysql, and found in a pageview of several dozen DNS RRs, red executes one of these (non-indexed) mysql queries for each record it displays:

SELECT dns_server_name FROM red_item_dns JOIN red_item WHERE
dns_type = 'ns' AND dns_fqdn = 'example.net' AND item_status = 'active';

One step toward fixing this would be to index the tables properly.

Another step would be to make sure that only a single DB call gets made up front, and can then be cleanly and quickly parsed by apache/php later.

Change History (1)

comment:1 Changed 8 years ago by https://id.mayfirst.org/dkg

I just added two single-column indexes on red_item_dns to try to reduce the non-indexed lookups:

create index dns_type ON red_item_dns (dns_type);
create index dns_fqdn ON red_item_dns (dns_fqdn);

You can review the indexes on any mysql table with:

show indexes from red_item_dns;

This change did stop the queries from showing up in the query log, but the pageloads are still taking a long time.

It was not clear to me how to make a multi-column index that could bind across tables, if we wanted to try to make an even quicker index for these lookups.

However, i suspect that the performance hit is in the PHP itself, since apache continues to consume the lion's share of the CPU.

Please login to add comments to this ticket.

Note: See TracTickets for help on using tickets.