#metabrainz

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?

is it fast again?

looks better

yay

hilariously, it seems everythig *but* mb is slow now (wikipedia, discogs)

woah, metabrainz TShirt arrived....

does it fit?

looks like it will

i got the fat size

3XL

ha no just xl

aww

he's australian SothoTalKer, not american :3

zas: what's off with the https://tickets.metabrainz.org/browse/STYLE-398 certificates?

yay

worked 16hrs today, been home for 2and a half and i'm wired

tax should be banned

well then i wouldn't have a job

but aholes that wait until the last day should be flogged at least

mmm oh well.... hermit is not bad

now i've got lots of time to pester reosarevok into making CAA-84 happen

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. :)

sweet..and i just found a J in the bowels of my chair

Congrats!

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

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

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.

UmkaDK: nope, this issue isn't fixed yet. https://metabrainz.org/api/musicbrainz/replicat...

Thanks zas, I'll keep the alarms off for now. Good luck with the fix!!

UmkaDK: we had serious db issues those last days, the replication issue is prolly related to this, i think it'll be fixed soon

Yee, I've seen the chatter. Thanks for the update zas!

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.

zas: https://metabrainz.org/api/musicbrainz/replicat...

that is not actually the reason why replication is behind -- that check is pointing to another ancient problem.

ah

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.

ruaok: yup, seen that :)

kicked

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?

still the same place, in docker, on hip

not ever stored on the FS?

ruaok: still not on the FS, the backup dir is in the container musicbrainz-production-cron at ~musicbrainz/backup

yvanzo: ok, thanks.

zas: sudden degradation in performance happen because of the optimizer.

... errr planner.

morning yvanzo

the planner needs to use statistics from the DB to plan a query for optimium efficiency.

hi zas!

at some point in time, black magic, really, something changes.

let's start with facts: autovacuum runs, but doesn't help with this problem

the planner needs to do something different because the previous strategy didn't work anymore.

VACUUM ANALYZE did help a lot

hang on. I'm trying to answer your previous questions.

k

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.

hmmm, wait, i need to verify this

and if that query was used a lot, then bam your server is overloaded.

but, this may be because it is using old statistics.

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

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.

yup, but then why it isn't more progressive ? i mean i'd expect slow performance degradation

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.

i tend to disagree here, as it doesn't explain all

https://stats.metabrainz.org/d/eStswhGmk/postgr... shows there was 3 "major events"

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

ok, I'm speaking from experience of 15 years of running postgres.

I'm trying to save you more frustration.

yes, i understand, but please stick to facts

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.

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 ?

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.

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 ?

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.