#metabrainz

/

      • ephemer0l_ has quit
      • Nyanko-sensei has quit
      • ephemer0l joined the channel
      • UmkaDK_ has quit
      • ruaok
        nice! load on bowie is now 8.5!
      • ok, lesson learned: autovacuum does not work. we all need to remember that for next time this exact same problems happens.
      • I wonder why that is -- because the server is too overloaded for the daemon to run?
      • SothoTalKer
        is it fast again?
      • Nyanko-sensei joined the channel
      • ruaok
        looks better
      • SothoTalKer
        yay
      • dragonzeron has quit
      • Slurpee joined the channel
      • Slurpee has quit
      • Slurpee joined the channel
      • CatQuest
        hilariously, it seems everythig *but* mb is slow now (wikipedia, discogs)
      • Gore|woerk joined the channel
      • G0re has quit
      • Major_Lurker
        woah, metabrainz TShirt arrived....
      • SothoTalKer
        does it fit?
      • Major_Lurker
        looks like it will
      • i got the fat size
      • SothoTalKer
        3XL
      • Major_Lurker has quit
      • Major_Lurker joined the channel
      • Major_Lurker
        ha no just xl
      • SothoTalKer
        aww
      • sentriz has quit
      • sentriz joined the channel
      • Slurpee has quit
      • CatQuest
        he's australian SothoTalKer, not american :3
      • drsaund joined the channel
      • UmkaDK joined the channel
      • UmkaDK has quit
      • UmkaDK joined the channel
      • reosarevok
        zas: what's off with the https://tickets.metabrainz.org/browse/STYLE-398 certificates?
      • drsaund is back from tax hell
      • Major_Lurker
        yay
      • drsaund
        worked 16hrs today, been home for 2and a half and i'm wired
      • Major_Lurker
        tax should be banned
      • drsaund
        well then i wouldn't have a job
      • but aholes that wait until the last day should be flogged at least
      • Major_Lurker
        mmm oh well.... hermit is not bad
      • drsaund
        now i've got lots of time to pester reosarevok into making CAA-84 happen
      • Major_Lurker has quit
      • Major_Lurker joined the channel
      • Freso
        reosarevok (zas): Nothing's off with it, it's just expired. It expired on May 1st 1:59 CEST (ie., almost 8 hours ago).
      • There might be something off with the script that should automatically make new certificates before the current one expired though. :)
      • d4rkie joined the channel
      • Nyanko-sensei has quit
      • drsaund
        sweet..and i just found a J in the bowels of my chair
      • Freso
        Congrats!
      • drsaund
      • UmkaDK_ joined the channel
      • UmkaDK has quit
      • Slurpee joined the channel
      • UmkaDK_ has quit
      • UmkaDK joined the channel
      • zas
        moin
      • reosarevok: cert expired on tickets but also on stats.metabrainz.org, i don't know what is going on, but that's very weird
      • Nyanko-sensei joined the channel
      • d4rkie has quit
      • ok, my fault, forgot to deploy new certs to those machines, fix in progress
      • fixed, i also added checks for those issues
      • ruaok: autovacuum is actually running, according to logs, but it may not trigger an ANALYZE, because the config has no specific options (it uses default thresholds). The command that was run yesterday is VACUUM ANALYZE.
      • I checked that the improvement is due to that, and it seems to be the case: 2018-04-30 23:29:16.936 GMT postgres@musicbrainz_db 22301 172.17.0.1(16512) LOG: duration: 2486396.275 ms statement: VACUUM ANALYZE;
      • time matches load decreases
      • so, to my understanding, autovacuum works, but needs to be tuned
      • running vacuum manually shouldn't be needed with current pg versions
      • bitmap: i'd start by logging autovacuum actions (log_autovacuum_min_duration), it should be also noted we can set those options per table
      • UmkaDK
        Hi guys, I just wanted to the check on your replication server. How is it doing? Is it feeling better?
      • We've disabled some of the alarms while replication was going up and down, so I just need to know if it's safe to re-enable them.
      • zas
        UmkaDK: nope, this issue isn't fixed yet. https://metabrainz.org/api/musicbrainz/replicat...
      • UmkaDK
        Thanks zas, I'll keep the alarms off for now. Good luck with the fix!!
      • zas
        UmkaDK: we had serious db issues those last days, the replication issue is prolly related to this, i think it'll be fixed soon
      • UmkaDK
        Yee, I've seen the chatter. Thanks for the update zas!
      • ruaok
        moin!
      • > I checked that the improvement is due to that, and it seems to be the case: 2018-04-30 23:29:16.936 GMT postgres@musicbrainz_db 22301 172.17.0.1(16512) LOG: duration: 2486396.275 ms statement: VACUUM ANALYZE;
      • that is the manual one yvanzo started.
      • that is not actually the reason why replication is behind -- that check is pointing to another ancient problem.
      • zas
        ah
      • ruaok
        yvanzo: when you get up, things improved after the vacuum analyze, now we should be able to generate packets again.
      • can you kick that process, please.
      • do you know which machines stores replication packets, zas? not inside a container.. just on FS.
      • yvanzo
        ruaok: yup, seen that :)
      • kicked
      • ruaok
        great, thanks.
      • do you know where the replication packets are stored?
      • one of our machines generates replication packets and stores them on the FS. where is that?
      • yvanzo
        still the same place, in docker, on hip
      • ruaok
        not ever stored on the FS?
      • yvanzo
        oh right, there is a backup dir as well, let me search
      • ruaok: still not on the FS, the backup dir is in the container musicbrainz-production-cron at ~musicbrainz/backup
      • ruaok
        yvanzo: ok, thanks.
      • zas: sudden degradation in performance happen because of the optimizer.
      • ... errr planner.
      • zas
        morning yvanzo
      • ruaok
        the planner needs to use statistics from the DB to plan a query for optimium efficiency.
      • yvanzo
        hi zas!
      • ruaok
        at some point in time, black magic, really, something changes.
      • zas
        let's start with facts: autovacuum runs, but doesn't help with this problem
      • ruaok
        the planner needs to do something different because the previous strategy didn't work anymore.
      • zas
        VACUUM ANALYZE did help a lot
      • ruaok
        hang on. I'm trying to answer your previous questions.
      • zas
        k
      • ruaok
        so, what used to be an efficient in-memory query now becomes an more expensive on disk query... for instance.
      • and that has a greater impact on system performance.
      • zas
        hmmm, wait, i need to verify this
      • ruaok
        and if that query was used a lot, then bam your server is overloaded.
      • but, this may be because it is using old statistics.
      • zas
        you assume it causes disk activity, and therefore a slowdown, but nope: https://stats.metabrainz.org/d/000000048/hetzne...
      • there was no increase in disk IO during 3 events, i can only see CPU usage increase in fact
      • ruaok
        if the statistic don't get updated, or old shit thrown from the DB, then it has to assume that things have changed. it can't made good judgements anymore.
      • zas
        yup, but then why it isn't more progressive ? i mean i'd expect slow performance degradation
      • ruaok
        so, running vacuum analyze throws out old shit and updates stats.
      • because the query planner now needs to make a different decision. it reaches a threshold.
      • once it passes the threshold, bam everything backs up.
      • now, you can spend a pile of time looking for exactly what happend.
      • but in the end the outcome is the same: optimize your DB or add more capacity.
      • zas
        i tend to disagree here, as it doesn't explain all
      • first one and second one went through without any action (i think), and lasted for 24-36hours
      • i see no trace of VACUUM in logs for those events
      • ruaok
        ok, I'm speaking from experience of 15 years of running postgres.
      • I'm trying to save you more frustration.
      • zas
        yes, i understand, but please stick to facts
      • ruaok
        I see you approaching this with tools that simply are not effective in combatting this.
      • I don't have facts. that's the whole problem about this. facts are hard to come by.
      • what I do have is observed patterns and experience with this problem.
      • this is classic "nothing changed, but PG is freaking out, why?" I've been here several times before.
      • zas
        i can't find solutions until the problem is well defined... and for now, your attempt to define the problem doesn't match any measurement
      • you said "what used to be an efficient in-memory query now becomes an more expensive on disk query..." -> where's the disk activity ?
      • ruaok
        I am trying to describe one of many different scenarios. I really don't know what the query planner is or is not doing in this case.
      • it simply may not be disk related.
      • zas
        ok, but keep your scenarii realistic: here we had only increase in cpu usage; network, memory and disk activity remained constant
      • but i agree with you it is a decrease of efficiency, likely coming from bad predictions (and lack of ANALYZE)
      • so why does this happen suddenly (starting on 17th) after months of stable activity ?
      • size of table triggered something ? number of insert/delete/update ?
      • ruaok
        I don't know, but I can speculate....
      • the query planner decides to use X tables for a query...
      • then stats go out of date and things get more fuzzy.
      • now it can't keep the whole table in ram anymore, or it thinks it can't.
      • the it may need to go get bits from disk and do more loading of data.
      • but that data is fresh so it actually resides in cache, (RAM, not L2)
      • so, now more fetching across RAM.
      • and it might really only be a slight change, but that slight change is what trip the tipping point.
      • and now everything backs up and can't ever recover and suddenly the server is totally overloaded.
      • by running the stats and throwing out old cruft, we turn back to where the planner can do things better.