mariadb 5.5 in production for english wikipedia

classic Classic list List threaded Threaded
16 messages Options
Reply | Threaded
Open this post in threaded view
|

mariadb 5.5 in production for english wikipedia

Asher Feldman
Hi,

This afternoon, I migrated one of the main production English Wikipedia
slaves, db59, to MariaDB 5.5.28.  We've previously been testing 5.5.27 on
the primary research slave, and I've been testing the current build for the
last few days on a slave in eqiad.  All has looked good, and I spent the
last few days adapting our monitoring and metrics collection tools to the
new version, and building binary packages that meet our needs.

A main gotcha in major version upgrades is performance regressions due to
changes in query plans.  I've seen no sign of this, and my initial
assessment is that performance for our workload is on par with or slightly
improved over the 5.1 facebook patchset.

Taking the times of 100% of all queries over regular sample windows, the
average query time across all enwiki slave queries is about 8% faster with
MariaDB vs. our production build of 5.1-fb.  Some queries types are 10-15%
faster, some are 3% slower, and nothing looks aberrant beyond those
bounds.  Overall throughput as measured by qps has generally been improved
by 2-10%.  I wouldn't draw any conclusions from this data yet, more is
needed to filter out noise, but it's positive.

MariaDB has some nice performance improvements that our workload doesn't
really hit (better query optimization and index usage during joins, much
better sub query support) but there are also some things, such as full
utilization of the primary key embedded on the right of every secondary
index that we can take advantage of (and improve our schema around) once
prod is fully upgraded, hopefully over the next 1-2 months.

The main goal of migrating to MariaDB is not performance driven.  More so,
I think it's in WMF's and the open source communities interest to coalesce
around the MariaDB Foundation as the best route to ensuring a truly open
and well supported future for mysql derived database technology.
Performance gains along the way are icing on the cake.

-Asher
_______________________________________________
Wikitech-l mailing list
[hidden email]
https://lists.wikimedia.org/mailman/listinfo/wikitech-l
Reply | Threaded
Open this post in threaded view
|

Re: [Ops] mariadb 5.5 in production for english wikipedia

Peter Youngmeister
Asher,

This is awesome! Thank you for your hard, careful work and dedication in
taking this huge first step in moving to MariaDB!

--peter


On Tue, Dec 11, 2012 at 4:10 PM, Asher Feldman <[hidden email]>wrote:

> Hi,
>
> This afternoon, I migrated one of the main production English Wikipedia
> slaves, db59, to MariaDB 5.5.28.  We've previously been testing 5.5.27 on
> the primary research slave, and I've been testing the current build for the
> last few days on a slave in eqiad.  All has looked good, and I spent the
> last few days adapting our monitoring and metrics collection tools to the
> new version, and building binary packages that meet our needs.
>
> A main gotcha in major version upgrades is performance regressions due to
> changes in query plans.  I've seen no sign of this, and my initial
> assessment is that performance for our workload is on par with or slightly
> improved over the 5.1 facebook patchset.
>
> Taking the times of 100% of all queries over regular sample windows, the
> average query time across all enwiki slave queries is about 8% faster with
> MariaDB vs. our production build of 5.1-fb.  Some queries types are 10-15%
> faster, some are 3% slower, and nothing looks aberrant beyond those
> bounds.  Overall throughput as measured by qps has generally been improved
> by 2-10%.  I wouldn't draw any conclusions from this data yet, more is
> needed to filter out noise, but it's positive.
>
> MariaDB has some nice performance improvements that our workload doesn't
> really hit (better query optimization and index usage during joins, much
> better sub query support) but there are also some things, such as full
> utilization of the primary key embedded on the right of every secondary
> index that we can take advantage of (and improve our schema around) once
> prod is fully upgraded, hopefully over the next 1-2 months.
>
> The main goal of migrating to MariaDB is not performance driven.  More so,
> I think it's in WMF's and the open source communities interest to coalesce
> around the MariaDB Foundation as the best route to ensuring a truly open
> and well supported future for mysql derived database technology.
> Performance gains along the way are icing on the cake.
>
> -Asher
>
>
> _______________________________________________
> Ops mailing list
> [hidden email]
> https://lists.wikimedia.org/mailman/listinfo/ops
>
>
_______________________________________________
Wikitech-l mailing list
[hidden email]
https://lists.wikimedia.org/mailman/listinfo/wikitech-l
Reply | Threaded
Open this post in threaded view
|

Re: [Ops] mariadb 5.5 in production for english wikipedia

Patrick Reilly-2
Hello Asher,

Thanks so much for your hard work on supporting our MariaDB migration.
I think that it's a big step for the MariaDB Foundation.

— Patrick

On Tue, Dec 11, 2012 at 4:17 PM, Peter Youngmeister <[hidden email]> wrote:

> Asher,
>
> This is awesome! Thank you for your hard, careful work and dedication in
> taking this huge first step in moving to MariaDB!
>
> --peter
>
>
> On Tue, Dec 11, 2012 at 4:10 PM, Asher Feldman <[hidden email]>
> wrote:
>>
>> Hi,
>>
>> This afternoon, I migrated one of the main production English Wikipedia
>> slaves, db59, to MariaDB 5.5.28.  We've previously been testing 5.5.27 on
>> the primary research slave, and I've been testing the current build for the
>> last few days on a slave in eqiad.  All has looked good, and I spent the
>> last few days adapting our monitoring and metrics collection tools to the
>> new version, and building binary packages that meet our needs.
>>
>> A main gotcha in major version upgrades is performance regressions due to
>> changes in query plans.  I've seen no sign of this, and my initial
>> assessment is that performance for our workload is on par with or slightly
>> improved over the 5.1 facebook patchset.
>>
>> Taking the times of 100% of all queries over regular sample windows, the
>> average query time across all enwiki slave queries is about 8% faster with
>> MariaDB vs. our production build of 5.1-fb.  Some queries types are 10-15%
>> faster, some are 3% slower, and nothing looks aberrant beyond those bounds.
>> Overall throughput as measured by qps has generally been improved by 2-10%.
>> I wouldn't draw any conclusions from this data yet, more is needed to filter
>> out noise, but it's positive.
>>
>> MariaDB has some nice performance improvements that our workload doesn't
>> really hit (better query optimization and index usage during joins, much
>> better sub query support) but there are also some things, such as full
>> utilization of the primary key embedded on the right of every secondary
>> index that we can take advantage of (and improve our schema around) once
>> prod is fully upgraded, hopefully over the next 1-2 months.
>>
>> The main goal of migrating to MariaDB is not performance driven.  More so,
>> I think it's in WMF's and the open source communities interest to coalesce
>> around the MariaDB Foundation as the best route to ensuring a truly open and
>> well supported future for mysql derived database technology.  Performance
>> gains along the way are icing on the cake.
>>
>> -Asher
>>
>>
>> _______________________________________________
>> Ops mailing list
>> [hidden email]
>> https://lists.wikimedia.org/mailman/listinfo/ops
>>
>
>
> _______________________________________________
> Ops mailing list
> [hidden email]
> https://lists.wikimedia.org/mailman/listinfo/ops
>

_______________________________________________
Wikitech-l mailing list
[hidden email]
https://lists.wikimedia.org/mailman/listinfo/wikitech-l
Reply | Threaded
Open this post in threaded view
|

Re: [Ops] mariadb 5.5 in production for english wikipedia

Terry Chay
In reply to this post by Asher Feldman
Nice!


> The main goal of migrating to MariaDB is not performance driven.  More so, I think it's in WMF's and the open source communities interest to coalesce around the MariaDB Foundation as the best route to ensuring a truly open and well supported future for mysql derived database technology.  Performance gains along the way are icing on the cake.
>


If it works out, then at some point we should probably tell the MariaDB peeos that they can mention that the WMF uses it. :-)
_______________________________________________
Wikitech-l mailing list
[hidden email]
https://lists.wikimedia.org/mailman/listinfo/wikitech-l
Reply | Threaded
Open this post in threaded view
|

Re: [Ops] mariadb 5.5 in production for english wikipedia

Asher Feldman
On Tue, Dec 11, 2012 at 5:49 PM, Terry Chay <[hidden email]> wrote:

> Nice!
>
>
> > The main goal of migrating to MariaDB is not performance driven.  More
> so, I think it's in WMF's and the open source communities interest to
> coalesce around the MariaDB Foundation as the best route to ensuring a
> truly open and well supported future for mysql derived database technology.
>  Performance gains along the way are icing on the cake.
> >
>
>
> If it works out, then at some point we should probably tell the MariaDB
> peeos that they can mention that the WMF uses it. :-)


We've been talking to Monty Widenius who visited the WMF office prior to
the Foundation announcement, and are fostering mutual support between the
Wikimedia and MariaDB Foundations.  Win-win for the open source community
at large!

-A
_______________________________________________
Wikitech-l mailing list
[hidden email]
https://lists.wikimedia.org/mailman/listinfo/wikitech-l
Reply | Threaded
Open this post in threaded view
|

Re: [Ops] mariadb 5.5 in production for english wikipedia

Alolita Sharma
Asher,

This is great news! Thanks for your perseverance and setting up
MariaDB 5.5 for en.wp :-) Monty will be thrilled!

Best,
Alolita


On Wed, Dec 12, 2012 at 7:53 AM, Asher Feldman <[hidden email]> wrote:

> On Tue, Dec 11, 2012 at 5:49 PM, Terry Chay <[hidden email]> wrote:
>
>> Nice!
>>
>>
>> > The main goal of migrating to MariaDB is not performance driven.  More
>> so, I think it's in WMF's and the open source communities interest to
>> coalesce around the MariaDB Foundation as the best route to ensuring a
>> truly open and well supported future for mysql derived database technology.
>>  Performance gains along the way are icing on the cake.
>> >
>>
>>
>> If it works out, then at some point we should probably tell the MariaDB
>> peeos that they can mention that the WMF uses it. :-)
>
>
> We've been talking to Monty Widenius who visited the WMF office prior to
> the Foundation announcement, and are fostering mutual support between the
> Wikimedia and MariaDB Foundations.  Win-win for the open source community
> at large!
>
> -A
> _______________________________________________
> Wikitech-l mailing list
> [hidden email]
> https://lists.wikimedia.org/mailman/listinfo/wikitech-l

_______________________________________________
Wikitech-l mailing list
[hidden email]
https://lists.wikimedia.org/mailman/listinfo/wikitech-l
Reply | Threaded
Open this post in threaded view
|

Re: [Ops] mariadb 5.5 in production for english wikipedia

Brad Jorsch (Anomie)
In reply to this post by Terry Chay
Awesome!

On Tue, Dec 11, 2012 at 8:49 PM, Terry Chay <[hidden email]> wrote:
>
> If it works out, then at some point we should probably tell the MariaDB peeos that they can mention that the WMF uses it. :-)

The enwiki article on MariaDB has claimed MediaWiki "officially"
supports it since October 2012.[1] Perhaps that's a {{citation
needed}}.

 [1]: https://en.wikipedia.org/w/index.php?diff=prev&oldid=519286745

_______________________________________________
Wikitech-l mailing list
[hidden email]
https://lists.wikimedia.org/mailman/listinfo/wikitech-l
Reply | Threaded
Open this post in threaded view
|

Re: [Ops] mariadb 5.5 in production for english wikipedia

David Gerard-2
On 12 December 2012 14:28, Brad Jorsch <[hidden email]> wrote:

> The enwiki article on MariaDB has claimed MediaWiki "officially"
> supports it since October 2012.[1] Perhaps that's a {{citation
> needed}}.
>  [1]: https://en.wikipedia.org/w/index.php?diff=prev&oldid=519286745


I would have thought it would have Just Worked in MediaWiki (the
software) for a long time.


- d.

_______________________________________________
Wikitech-l mailing list
[hidden email]
https://lists.wikimedia.org/mailman/listinfo/wikitech-l
Reply | Threaded
Open this post in threaded view
|

Re: [Ops] mariadb 5.5 in production for english wikipedia

Brad Jorsch (Anomie)
On Wed, Dec 12, 2012 at 9:34 AM, David Gerard <[hidden email]> wrote:

> On 12 December 2012 14:28, Brad Jorsch <[hidden email]> wrote:
>
>> The enwiki article on MariaDB has claimed MediaWiki "officially"
>> supports it since October 2012.[1] Perhaps that's a {{citation
>> needed}}.
>>  [1]: https://en.wikipedia.org/w/index.php?diff=prev&oldid=519286745
>
>
> I would have thought it would have Just Worked in MediaWiki (the
> software) for a long time.

It probably did. But [[mw:Manual:Installation guide]] still says it's
not "officially" supported.

_______________________________________________
Wikitech-l mailing list
[hidden email]
https://lists.wikimedia.org/mailman/listinfo/wikitech-l
Reply | Threaded
Open this post in threaded view
|

Re: mariadb 5.5 in production for english wikipedia

Antoine Musso-3
In reply to this post by Asher Feldman
Le 12/12/12 01:10, Asher Feldman a écrit :
> This afternoon, I migrated one of the main production English Wikipedia
> slaves, db59, to MariaDB 5.5.28.

Congratulations :-)

Out of curiosity, have you looked at Drizzle too?

--
Antoine "hashar" Musso


_______________________________________________
Wikitech-l mailing list
[hidden email]
https://lists.wikimedia.org/mailman/listinfo/wikitech-l
Reply | Threaded
Open this post in threaded view
|

Re: [Ops] mariadb 5.5 in production for english wikipedia

David Gerard-2
In reply to this post by Brad Jorsch (Anomie)
On 12 December 2012 14:38, Brad Jorsch <[hidden email]> wrote:
> On Wed, Dec 12, 2012 at 9:34 AM, David Gerard <[hidden email]> wrote:
>> On 12 December 2012 14:28, Brad Jorsch <[hidden email]> wrote:

>>> The enwiki article on MariaDB has claimed MediaWiki "officially"
>>> supports it since October 2012.[1] Perhaps that's a {{citation
>>> needed}}.
>>>  [1]: https://en.wikipedia.org/w/index.php?diff=prev&oldid=519286745

>> I would have thought it would have Just Worked in MediaWiki (the
>> software) for a long time.

> It probably did. But [[mw:Manual:Installation guide]] still says it's
> not "officially" supported.


I expect when it's on more of the cluster someone can announce its
enhanced status here :-)


- d.

_______________________________________________
Wikitech-l mailing list
[hidden email]
https://lists.wikimedia.org/mailman/listinfo/wikitech-l
Reply | Threaded
Open this post in threaded view
|

Re: [Ops] mariadb 5.5 in production for english wikipedia

Thomas Fellows
This is awesome!  Is there any write-up of the migration process floating
around?

-Tom

On Wed, Dec 12, 2012 at 10:06 AM, David Gerard <[hidden email]> wrote:

> On 12 December 2012 14:38, Brad Jorsch <[hidden email]> wrote:
> > On Wed, Dec 12, 2012 at 9:34 AM, David Gerard <[hidden email]> wrote:
> >> On 12 December 2012 14:28, Brad Jorsch <[hidden email]> wrote:
>
> >>> The enwiki article on MariaDB has claimed MediaWiki "officially"
> >>> supports it since October 2012.[1] Perhaps that's a {{citation
> >>> needed}}.
> >>>  [1]: https://en.wikipedia.org/w/index.php?diff=prev&oldid=519286745
>
> >> I would have thought it would have Just Worked in MediaWiki (the
> >> software) for a long time.
>
> > It probably did. But [[mw:Manual:Installation guide]] still says it's
> > not "officially" supported.
>
>
> I expect when it's on more of the cluster someone can announce its
> enhanced status here :-)
>
>
> - d.
>
> _______________________________________________
> Wikitech-l mailing list
> [hidden email]
> https://lists.wikimedia.org/mailman/listinfo/wikitech-l
>
_______________________________________________
Wikitech-l mailing list
[hidden email]
https://lists.wikimedia.org/mailman/listinfo/wikitech-l
Reply | Threaded
Open this post in threaded view
|

Re: [Ops] mariadb 5.5 in production for english wikipedia

David Gerard-2
On 12 December 2012 15:32, Thomas Fellows <[hidden email]> wrote:

> This is awesome!  Is there any write-up of the migration process floating
> around?


+1

In fact, this would be a nice thing to put on the WMF blog. It'll
certainly get a lot of linkage and reporting around the geekosphere.


- d.

_______________________________________________
Wikitech-l mailing list
[hidden email]
https://lists.wikimedia.org/mailman/listinfo/wikitech-l
Reply | Threaded
Open this post in threaded view
|

Re: mariadb 5.5 in production for english wikipedia

Asher Feldman
On Wednesday, December 12, 2012, David Gerard wrote:

> On 12 December 2012 15:32, Thomas Fellows <[hidden email]<javascript:;>>
> wrote:
>
> > This is awesome!  Is there any write-up of the migration process floating
> > around?
>
>
> +1
>
> In fact, this would be a nice thing to put on the WMF blog. It'll
> certainly get a lot of linkage and reporting around the geekosphere.


A detailed blog post is definitely my intent, I'm just waiting until
at least one major project is 100% on mariadb and I have more data and
hence confidence in drawn conclusions. I don't think that's far off at all,
potentially later this month.  If that occurs and goes well, the eqiad data
center migration in late January may also be a migration to all mariadb.
_______________________________________________
Wikitech-l mailing list
[hidden email]
https://lists.wikimedia.org/mailman/listinfo/wikitech-l
Reply | Threaded
Open this post in threaded view
|

Re: mariadb 5.5 in production for english wikipedia

Daniel Friesen-2
In reply to this post by Antoine Musso-3
On Wed, 12 Dec 2012 06:45:24 -0800, Antoine Musso <[hidden email]>  
wrote:

> Le 12/12/12 01:10, Asher Feldman a écrit :
>> This afternoon, I migrated one of the main production English Wikipedia
>> slaves, db59, to MariaDB 5.5.28.
>
> Congratulations :-)
>
> Out of curiosity, have you looked at Drizzle too?

IIRC Drizzle uses a completely different client and we'd need to write a  
new database class for it.

...something I've wished I could do for a long time.

--
~Daniel Friesen (Dantman, Nadir-Seen-Fire) [http://danielfriesen.name/]


_______________________________________________
Wikitech-l mailing list
[hidden email]
https://lists.wikimedia.org/mailman/listinfo/wikitech-l
Reply | Threaded
Open this post in threaded view
|

Re: mariadb 5.5 in production for english wikipedia

Asher Feldman
In reply to this post by Antoine Musso-3
On Wed, Dec 12, 2012 at 6:45 AM, Antoine Musso <[hidden email]> wrote:

> Le 12/12/12 01:10, Asher Feldman a écrit :
> > This afternoon, I migrated one of the main production English Wikipedia
> > slaves, db59, to MariaDB 5.5.28.
>
> Congratulations :-)
>
> Out of curiosity, have you looked at Drizzle too?
>

I've spoken with Drizzle developers at OSCON in the past.  I haven't seen
anyone advocate it as a production quality database though, and it doesn't
currently seem to have a lot of development momentum behind it, with Brian
Aker no longer putting in a lot of time.  Lots of interesting ideas and
features, especially around replication, but they make it incompatible with
MySQL in enough ways where a gradual migration wouldn't be practical even
if it was otherwise desirable.

-A
_______________________________________________
Wikitech-l mailing list
[hidden email]
https://lists.wikimedia.org/mailman/listinfo/wikitech-l