← Back to team overview

maria-discuss team mailing list archive

Re: MariaDB server horribly slow on start

 

Perhaps you should increase its side then, anyway PMM is great to see what’s a good setting! :)


> Le 27 juil. 2022 à 15:31, Cédric Counotte <cedric.counotte@xxxxxxxxxx> a écrit :
> 
> Considering the binlog files are 1GB and I get 144 of them per day, doesn't it mean that my 1GB log file holds about 10 minutes only of logs?
>  
> I suppose I should make it 6GB then to follow that article 😉
>  
> Still using galera, I assume crashes are compensated by another node and 1GB of log should be enough for now.
>  
> FWIW Servers are using NVMe from OVH, 4 of them are on ovh gen 2 NVMe (whatever it means).
>  
>  
> -----Message d'origine-----
> De : jocelyn fournier <jocelyn.fournier@xxxxxxxxx> 
> Envoyé : mercredi 27 juillet 2022 15:26
> À : Gordan Bobic <gordan.bobic@xxxxxxxxx>
> Cc : Cédric Counotte <cedric.counotte@xxxxxxxxxx>; Marko Mäkelä <marko.makela@xxxxxxxxxxx>; Mailing-List mariadb <maria-discuss@xxxxxxxxxxxxxxxxxxx>; Pierre LAFON <pierre.lafon@xxxxxxxxxx>
> Objet : Re: [Maria-discuss] MariaDB server horribly slow on start
>  
> It’s not that tiny, this article is a bit old, but still valid:
>  
> https://www.percona.com/blog/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/
>  
> If you don’t need a big redo log, reduce its side to avoid slow crash recovery
>  
> > Le 27 juil. 2022 à 15:23, Gordan Bobic <gordan.bobic@xxxxxxxxx> a écrit :
> > 
> > 10.5+ only uses a single log file, so that is 1x1GB.
> > And 1GB is tiny, IMO it should be a default these days.
> > I would only even consider something smaller if I was running on an
> > older Raspberry Pi or something similarly constrained.
> > 
> > 
> > On Wed, Jul 27, 2022 at 4:11 PM jocelyn fournier 
> > <jocelyn.fournier@xxxxxxxxx> wrote:
> >> 
> >> Hi Cédric!
> >> 
> >> Just to be sure, do you really need the 2x 1G log_file_size ?
> >> 
> >> BR,
> >>  Jocelyn Fournier
> >> 
> >>> Le 27 juil. 2022 à 14:36, Cédric Counotte <cedric.counotte@xxxxxxxxxx> a écrit :
> >>> 
> >>> Reading this: https://jira.mariadb.org/browse/MDEV-27295
> >>> 
> >>> It's quite unclear when it is fixed or reverted.
> >>> 
> >>> That said I read that the following setting might fix it:
> >>>      SET GLOBAL innodb_max_dirty_pages_pct_lwm=0.001;
> >>> 
> >>> Is that correct and should I try that and see if that helps?
> >>> 
> >>> 
> >>> -----Message d'origine-----
> >>> De : Gordan Bobic <gordan.bobic@xxxxxxxxx> Envoyé : mercredi 27
> >>> juillet 2022 14:29 À : Marko Mäkelä <marko.makela@xxxxxxxxxxx> Cc :
> >>> Cédric Counotte <cedric.counotte@xxxxxxxxxx>; Mailing-List mariadb
> >>> <maria-discuss@xxxxxxxxxxxxxxxxxxx>
> >>> Objet : Re: [Maria-discuss] MariaDB server horribly slow on start
> >>> 
> >>> On Wed, Jul 27, 2022 at 3:08 PM Marko Mäkelä <marko.makela@xxxxxxxxxxx> wrote:
> >>>> 
> >>>> On Wed, Jul 27, 2022 at 2:48 PM Gordan Bobic <gordan.bobic@xxxxxxxxx> wrote:
> >>>>> 
> >>>>> There is no supported downgrade path other than logical dump+restore.
> >>>>> There are also no packages built for distros where the major version is older than what ships with the distro.
> >>>>> 
> >>>>> Since your queries seem to end up stuck in commit stage, it could be related to redo log flushing, which behaves very erratically on 10.5+. If it leaves the log to fill up to 90% and the state transfer hits, it could be that with the checkpoint age already high, there just isn't enough headroom to avoid a massive stall. Purely guessing here without any telemetry.
> >>>> 
> >>>> I think that you may refer to InnoDB page flushing. There was some
> >>>> misunderstanding around that, and indeed some partly unintended or
> >>>> uninformed changes in behaviour (in 10.5.7 and 10.5.8) that were
> >>>> reverted later. It could be useful to read 
> >>>> https://jira.mariadb.org/browse/MDEV-27295.
> >>> 
> >>> What version was it reverted in?
> >>> I am still seeing the errant redo log flushing behaviour in 10.5.15.
> >>> It looks like no flushing happens until the hwm is reached at about
> >>> 85% full. It then tries to commit everything down to the lwm. And
> >>> inbetween it doesn't do anything, even while everything is idle and
> >>> it should be running down the 
> >>> _______________________________________________
> >>> Mailing list: https://launchpad.net/~maria-discuss
> >>> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> >>> Unsubscribe : https://launchpad.net/~maria-discuss
> >>> More help   : https://help.launchpad.net/ListHelp
> >> 



References