Page MenuHomeMiraheze

High load on dbbackup servers
Open, NormalPublic

Description

This should be resolved as currently with how many times this gets sent, it could hide other checks because of the spam.

Event Timeline

Paladox triaged this task as High priority.Mar 16 2021, 15:25
Paladox created this task.
This comment was removed by R4356th.

I am not sure if load alerts are useful for these servers. These servers are not production facing; as long as the replication processes are running and the replication lag is not greater than X seconds, I don't mind what the load average is.

However, for dbbackup1, there's definitely a performance issue: https://grafana.miraheze.org/d/_9zrwMHmk/mariadb-replication?viewPanel=16&orgId=1&from=1615442601928&to=1615939976899&var-interval=$__auto_interval_interval&var-host=dbbackup1.miraheze.org:9105... what's causing this? Perhaps two clusters on one VPS, running on HDDs (since SSDs are too expensive), is not possible?

Downtimed dbbackup[12] Current Load.

Investigating load on dbbackup1.

Analysing the queries from a binlog (c4):
mysqlbinlog mysql-bin.001818 | grep '::' > ~/analyse-queries-T6984.txt
(any query that has '::' in it usually includes the PHP caller as SQL comments)

Output:

$ awk '{print $4}' analyse-queries-T6984.txt | sort | uniq -c | sort -nr | head -15
   5437 UpdateExternalLinks::execute
   5164 SqlBagOStuff::deleteServerObjectsExpiringBefore
   3736 WikiPage::archiveRevisions
   2859 SET
   2346 LinksUpdate::incrTableUpdate
   1873 SiteStatsUpdate::doUpdate
   1814 CheckUserHooks::updateCheckUserData
   1807 RecentChange::save
   1799 LinksDeletionUpdate::doIncrementalUpdate
   1728 LinksUpdate::updateLinksTimestamp
   1609 ManualLogEntry::insert
   1527 SqlBagOStuff::updateTable
   1142 MediaWiki\User\UserOptionsManager::saveOptions
    934 WikiPage::doDeleteArticleBatched
    934 SearchMySQL::delete

@Paladox I have disabled c4 replication on dbbackup1, but the lag is not decreasing. It looks like dbbackup1 still doesn't have enough room to replicate a full database cluster. Do you see room for improvements?

@Paladox I have disabled c4 replication on dbbackup1, but the lag is not decreasing. It looks like dbbackup1 still doesn't have enough room to replicate a full database cluster. Do you see room for improvements?

Hi, I don't see any room for improvements without spending. I don't think hdds are suitable for DBs as they are just too slow.

I much prefer your other suggestion of using mysql backup which means we at least have a stable backup even if its not a replica.

Southparkfan lowered the priority of this task from High to Normal.Wed, Mar 24, 12:20
Southparkfan changed the task status from Open to Stalled.Sun, Mar 28, 22:44

The future of these servers depends on the outcome of testing regarding T5877#139273.

John changed the task status from Stalled to Open.Sun, Mar 28, 23:06
John added a subscriber: John.

Not blocked on external entity