maria-discuss team mailing list archive

[Gordan Bobic <gordan.bobic@xxxxxxxxx>]

Replaying a gigabyte of a redo log takes seconds on hardware from this century.
When it comes to redo log sizing, there are two bounds:

Upper bound: big enough to soak your peak hour of writes.

Lower bound: big enough that your checkpoint age never hits the
effective redo log size. To provide a margin for error, I normally try
to make it never exceed 80% of redo log size.

There is such a thing as overtuning. If you are seeing crashes
regularly and have no redundant infrastructure for failover, tuning
redo log size to reduce crash recovery is wasting time fixing the
wrong problem.




On Wed, Jul 27, 2022 at 4:26 PM jocelyn fournier
<jocelyn.fournier@xxxxxxxxx> wrote:
>
> 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
> >>
>