maria-developers team mailing list archive

[Saad Khan the hacker <mrashid2000@xxxxxxxxx>]

This is Muhammad Rashid from Karachi, Pakistan. I need some help to
implement MariaDB Master/Master replication with gtid on Microsoft azure
environment.
Although I have configured the implementation across azure "East US" and
"West 2 US" region using vm size "Standard E8s v3 (8 vcpus, 64 GB memory)"
through vnet/vnet However I have few points to clear.

(1) Can we put Master and slave in scale set.
(2)  How can improve the latency between regions.
(3) Could you suggest me the MariaDB configuration for above machine and
1TB database size having 1 million/hour maximum transaction.
(4) How could we avoid single point of failure with in region.
(5) Suggest for Microsoft VM size for  1TB database size having 1
million/hour maximum transaction.
(6) Segregate the read/write operation without Maxscale.

I will be very thankful for your kind support and suggestions.

Thanks and regards

Muhammad Rashid

On Thu, Apr 12, 2018 at 10:11 AM, Alexander Barkov <bar@xxxxxxxxxxx> wrote:

> Hi,
>
> On 04/12/2018 07:59 AM, Alexander Barkov wrote:
> > Hi Igor,
> >
> > Please do "reply all" when replying.
> >
> > On 04/12/2018 01:28 AM, Igor Babaev wrote:
> >> Alexander,
> >>
> >> If you have the same behaviour you have to use
> >>
> >> SELECT * FROM t1 UNION (SELECT * FROM t2 FOR UPDATE).
> >
> > Thanks. This worked fine for me.
> >
> >>
> >> Otherwise it's considered as
> >>
> >> (SELECT * FROM t1 UNION SELECT * FROM t2 FOR UPDATE).
> >
> > What does this syntax mean 10.3-MDEV-11953?
> >
> > Which records should be locked? From t1, from t2, or from both?
> >
> > It seems that it locks nothing.
> > If so, shouldn't this be disallowed?
> >
> > This query (without parentheses) also locks nothing:
> >
> > SELECT * FROM t1 UNION SELECT * FROM t2 FOR UPDATE;
> >
> >>
> >> MySQL manual says that 'FOR UPDATE' must follow  ORDER BY,
> >>
> >> but it does not tell you how to interpret FOR  UPDATE in unions.
> >
> > The old behavior was to interpret UNION parts separately.
> > This locked only t2 in 10.3:
> >
> > SELECT * FROM t1 UNION SELECT * FROM t2 FOR UPDATE;
> >
> > In 10.3-MDEV-11953 it locks nothing. Upgrade looks dangerous.
> >
> >
> > Can we do this way:
> >
> > 1. In 10.4: disallow FOR UPDATE applied to the entire UNION.
>
> Or perhaps, is it possible to fix "..UNION..FOR UPDATE"
> to lock all union parts instead disallowing it
> and instead of doing nothing (current 10.3-MDEV-11953 behavior)?
>
>
>
> >
> > 2. In 10.3: Add a warning which tells that this feature
> >    is deprecated and suggests to use parentheses
> >    around union parts when a non-parenthesized union term
> >    is used with FOR UPDATE ?
> >
> >
> >
> >>
> >> Anyway you can't use anymore:
> >> SELECT c FROM t1 ORDER BY a
> >> UNION
> >> SELECT c FROM t2 ORDER BY b
> >> ORDER BY c.
> >>
> >> You have to put it so:
> >> (SELECT c FROM t1 ORDER BY a)
> >> UNION
> >> (SELECT c FROM t2 ORDER BY b)
> >> ORDER BY c.
> >>
> >> (see the standard).
> >
> > I like this change.
> >
> >>
> >> It means that the ORDER BY clause followed a union relates to the union.
> >> The same is true for 'FOR UPDATE' because it follows optional ORDER BY/
> >> LIMIT clauses.
> >>
> >>
> >> Regards,
> >> Igor.
> >>
> >>
> >> On 04/11/2018 09:10 AM, Alexander Barkov wrote:
> >>> Hi Sanja, and Igor,
> >>>
> >>>
> >>> (resending with Igor on CC)
> >>>
> >>>
> >>> I noticed a FOR UPDATE behavior change in 10.3-MDEV-11953
> >>>
> >>> In 10.3 I run this script in a client session:
> >>>
> >>> CREATE OR REPLACE TABLE t1 (a INT NOT NULL PRIMARY KEY);
> >>> CREATE OR REPLACE TABLE t2 (a INT NOT NULL PRIMARY KEY);
> >>> INSERT INTO t1 VALUES (1);
> >>> INSERT INTO t2 VALUES (2);
> >>> BEGIN;
> >>> SELECT * FROM t1 UNION SELECT * FROM t2 FOR UPDATE;
> >>>
> >>> Notice no COMMIT yet! It returns this result:
> >>> +---+
> >>> | a |
> >>> +---+
> >>> | 1 |
> >>> | 2 |
> >>> +---+
> >>>
> >>> Now I open a new console, start a new client session and run this
> script:
> >>>
> >>> BEGIN;
> >>> SELECT * FROM t2 FOR UPDATE;
> >>>
> >>> The second session gets locked, as expected.
> >>>
> >>> Now I return to the first console and run "COMMIT;".
> >>> The second console gets unlocked and returns
> >>> +---+
> >>> | a |
> >>> +---+
> >>> | 2 |
> >>> +---+
> >>>
> >>>
> >>> Now if I do the same in 10.3-MDEV-11953, the second session does not
> get
> >>> locked, it returns the result immediately, even before I typed
> "COMMIT;"
> >>> in the first session.
> >>>
> >>>
> >>> Note, if in 10.3-MDEV-11953 I start a transaction with a simpler query
> >>> (without UNION) in the first session:
> >>>
> >>> BEGIN;
> >>> SELECT * FROM t2 FOR UPDATE;
> >>>
> >>> and run the same script in the second session
> >>>
> >>> BEGIN;
> >>> SELECT * FROM t2 FOR UPDATE;
> >>>
> >>> then the second session gets locked as expected.
> >>>
> >>>
> >>> Is this behavior change expected?
> >>>
> >>>
> >>> Thanks!
> >>>
> >>> _______________________________________________
> >>> Mailing list: https://launchpad.net/~maria-developers
> >>> Post to     : maria-developers@xxxxxxxxxxxxxxxxxxx
> >>> Unsubscribe : https://launchpad.net/~maria-developers
> >>> More help   : https://help.launchpad.net/ListHelp
> >>>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-developers
> Post to     : maria-developers@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-developers
> More help   : https://help.launchpad.net/ListHelp
>