Scaling MySQL with Python

Video thumbnail (Frame 0) Video thumbnail (Frame 10990) Video thumbnail (Frame 22565) Video thumbnail (Frame 24333) Video thumbnail (Frame 25096) Video thumbnail (Frame 25967) Video thumbnail (Frame 27431) Video thumbnail (Frame 28390) Video thumbnail (Frame 28904) Video thumbnail (Frame 29669) Video thumbnail (Frame 36468)
Video in TIB AV-Portal: Scaling MySQL with Python

Formal Metadata

Scaling MySQL with Python
Title of Series
Part Number
Number of Parts
CC Attribution - NonCommercial - ShareAlike 3.0 Unported:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal and non-commercial purpose as long as the work is attributed to the author in the manner specified by the author or licensor and the work or content is shared also in adapted form only under the conditions of this license.
Release Date
Production Place
Bilbao, Euskadi, Spain

Content Metadata

Subject Area
Roberto Polli - Scaling MySQL with Python Python is the language of choice for the orchestration part of MySQL 5.6. After a brief introduction of MySQL replication architecture, the talk presents the python utilities released by MySQL: - a set of drivers in pure-python - mysql-utilites for replication, management and failover - fabric, a tool for scaling, sharding and provisioning new servers You will see how to: - create resilient configurations in minutes - use mysql-fabric to create high available infrastructures As a plus, we'll show how we: - implemented a fabric provider for provisioning new databases via docker
Keywords EuroPython Conference EP 2015 EuroPython 2015
Randomization Context awareness Concurrency (computer science) INTEGRAL View (database) Multiplication sign Function (mathematics) Shape (magazine) Client (computing) Replication (computing) Fault-tolerant system Mereology Total S.A. Medical imaging Mathematics Different (Kate Ryan album) Information security Multiplication Area Electric generator File format Linear regression Software developer Computer file Binary code Keyboard shortcut Stress (mechanics) Internet service provider Database transaction ACID Statistics Front and back ends Connected space Arithmetic mean Data management Internet service provider Physical system Point (geometry) Statistics Implementation Game controller Vapor barrier Computer file Device driver Data storage device Drop (liquid) Mass Login Bit Wave packet Product (business) Read-only memory Utility software MiniDisc Renewal theory Computing platform Compilation album Computer architecture Scaling (geometry) Server (computing) Consistency Uniqueness quantification Projective plane Ordinary differential equation Planning Computer network Basis <Mathematik> Database Device driver Limit (category theory) Signal processing Performance appraisal Single-precision floating-point format Subject indexing Computer animation Integrated development environment Query language Blog Network topology Vertex (graph theory) Utility software Point cloud Table (information) Fingerprint
NP-hard Complex (psychology) Group action Greatest element Building Logarithm Graph (mathematics) Multiplication sign Covering space Parameter (computer programming) Client (computing) Function (mathematics) Replication (computing) Fault-tolerant system Mereology Connected space Mathematics Negative number Diagram Endliche Modelltheorie Electronic mailing list Internet service provider Parameter (computer programming) Port scanner Radical (chemistry) Data management Frequency Auditory masking Internet service provider Order (biology) Configuration space Right angle Remote Access Service Reading (process) Asynchronous Transfer Mode Server (computing) Observational study Computer-generated imagery Password Maxima and minima Knot Directory service Mass Rule of inference Product (business) Revision control Natural number Subject indexing Backup Address space Metropolitan area network Information Server (computing) Weight Physical law Code High availability Database Library catalog Binary file Density of states Cartesian coordinate system Subject indexing Event horizon Computer animation Query language Function (mathematics) Network topology Family Cloning
Metropolitan area network Pairwise comparison Type theory Slide rule Connected space Asynchronous Transfer Mode Server (computing) Sample (statistics) Computer animation Server (computing) Weight Source code
Quantum state Computer animation Raster graphics Server (computing) Set (mathematics) Mass Gamma function Arithmetic logic unit Rule of inference Family Cloning
Metropolitan area network Server (computing) Interface (computing) Virtual machine Password Internet service provider Parameter (computer programming) Electronic mailing list Client (computing) Port scanner Euler angles Type theory Computer animation Raster graphics
Group action Server (computing) Table (information) Computer file Virtual machine Password Electronic mailing list Client (computing) Inclusion map Implementation Information Server (computing) Real number Interface (computing) Internet service provider Ext functor Client (computing) Database Volume (thermodynamics) Port scanner Flow separation Skeleton (computer programming) Computer animation Interface (computing) Personal area network Cloning
Server (computing) Coefficient of determination Computer animation Server (computing) Multiplication sign Port scanner Cloning
Module (mathematics) Mashup <Internet> Group action Server (computing) Implementation Information Block (periodic table) Code Multiplication sign Virtual machine Variance Database Drop (liquid) Replication (computing) Mereology Cartesian coordinate system Number Computer animation Auditory masking Figurate number
Computer file Block (periodic table) Multiplication sign Consistency 1 (number) Sheaf (mathematics) Data storage device Database transaction Propositional formula Solid geometry Replication (computing) Mereology Rule of inference Theory Product (business) Bit rate Software testing Position operator
so this is our agenda I will show you after a brief introduction on muscular what are private is and how to use them to connected to the other Bayesian awful fault-tolerant where and how do create manage scale find out of fault-tolerant infrastructure and then Austin run and i mean of some drops of clothes out to the literary master in the cloud has sold all you have to turn my company for financing these long followed from Italy to be involved product uh what's going on with integration of the get infrastructure provides business intelligence solutions and vertical projection for the financial market so you know must it's a signal processing with different architecture 1 and maybe you don't know is that it as a tool tighter infrastructure architecture 1 the providing the SQL in connection services given area provides persistency adding is implemented via an so everybody can ride his own that can in I strongly suggest you to follow the development of muscular undergoes new evaluation very very nice of all the they can that maybe you know the reason is going to be which provides SEA them molting versioning concurrency control and redo and undo last so everything you need to created consistent of a base and another important thing is the replication is visible and change a lot every transaction is recorded and to be provided to the slaves those transactions involving binary so what do you should be doing to managing and use of a base it's almost out of more than I know it is managing tables indexes binary logs reprojection inconsistencies because if the replication should uh provide a way to monitor that actually recorded that their managed and what did you do yesterday you download this freed from some blog on random queries shape strongly and then you have your solution for your database well those days are all the we have the magical my SQL provided by my security in those are within 5 includes some connectors for general and fabric they appear applied and so if you want to treat your present implementation from surviving to abide by for example they want to move the and William improve in time also known as Europe Python implementation improves so all all more c by bindings and those implementation of this with platform because up pure Python diluting driver and the fabric of this trade and then we will see after so how to use them as purely view that is it simple just 5 minus and see and you are in a new client environment a command line environment a unique entry point for all the utilities this context so the compilation and uh otherwise you can access each you separated so what can we do when you we can check the ODE log compare the the basis even the that if I want to check if my tree gigabyte then by 100 gigabyte database is corrected would be didn't but I know that the just 1 table to compare again just compare 1 table between 2 databases injected the that of the this table is right does it did his plan created the in a secure format so I can apply the resulting the to 1 of the the basis to get within same again even somebody to transistor stage reported in implement the resumed infrastructure for example I want to know on this book an e-mail database that is actually a nice throughout I right muscular this is age and now that I had on this 1 gigabytes of the money indexes and I guess the data out there barriers database those files are essentially the renewal of my database so even if I don't know anything about muscular I just write muscular this is agent of bunch of data and statistics and I get a lot for me I get the output I checked the other good with that of limitation I learned my stress this is very useful from very useful for make training and really let realize classrooms because people can land you anything down now everything I have connection this is a non shot from the court of the connected so for years general I can use the the jungle driver provided by my the image provided any checks my generation let's start then with interesting part of that is replication regression muscular you have these almost the criminals awesome that is the master creates a change of regards all the changes assign them a transaction idea and then there's this connects to the master get those transactions and use the same missing grows replication the mass away and doesn't act knowledge the transaction on the of 1 labels on knowledge that the obligations have been replicated intersection of the replicated this
simple diagram is the following so all of them produces the change in this connects to the master down laws the change stars locally and then applies on the log base you might guess why replication
revocation gives us availability with the obligation to give a this read but when we get even partly scare rights using the different indexes embargoes server for example if I haven't really hard fulltext indexes on my master on my database I just don't provision the that's and the muscles by the continue loneliness and his name so I run on the mass at full speed of almost full speed when the mask their replicates rights on this in this vein schema set up the next and right the full query the full text queries Orleans later in this way I have a very scalable infrastructure without adding complexity just the master the names rejection by using some tricks and to improve the performance I have my revision infrastructure well there is 1 common 1 you the immediate revocation show that gives me the obligation layout so even if I don't know all my obligations set that I just 1 is commonly connect to the studies covering all its laid then In 1 minute I've got the topology of the database he then again I don't know even if we don't know how to configure obligation it's just 1 common and you for I don't know either going to Europe negation and all times 1 common that takes care to contact the master server provisions I use replication users run all the replication checks find the slave to and the master so that the obligation is found logging the binary problems I guess I don't know muscular 1 learn I just have 1 common I get the output all the the steps that they need to sit abrogation more although I get even the preliminary checks so unified land must you have 5 years ago and I don't know which of the checks required for replication of that 7 just runs common and get the information I unique I can initialize his latest so we will see it's after because we are running out of time and you for a of 2 so that my fault tolerant infrastructure I just around my strength in other common I Golden go to the master get along the slaves the the master you'll take care of configure of finding the best to be elected as a master then doing strip of the master and the news as the mustard America lives blind to the new master OK it's just 1 come on the head but they're trying but before using it in production read carefully the documentation because there are many many parameters and it's quite flexible and it is very useful so it's starting from 0 to hero in meaning then the most parts of the fabric orchestrator there dialog of the marriage and just running server infrastructure is just 1 common that had it is therefore a model for managing replicated in a scanning Maestro infrastructure takes a bunch of cerebral nature of those sidebar obligation group continually replication monitor and have the infrastructure MIT Center director for the regrets grid infrastructure so I continue my clients and then go to fabric get the date of the infrastructure from him and then the connector knows where is the master where there's ladies and you can you can contact you cannot contact the master with contact is late these these building infrastructure like at the center we have the application they asked to the family you know who is the master ladies then contacted directly the server they every group with a bunch of several any just contact the server for ask and so there is no latency the primary is not approximate the just get contains the data of who is the mass was this lady wonders ladies and of the many groups US the to set up we have many comments the we everything started fabric weekend managers rule uh continued to sell a radical few provider with seed after an interesting parts of the talk so we have many features that we want to sort fabric OK so that the listing bottom further see G so that the credentials to contact the sovereign and just go you're everything managed to adopt a man so OK fabric is really nearest server catalog of providing you are at fault tolerant infrastructure OK you said that the cerebrum and you want to create many groups let's start with 1 group 1 high-availability groups it just created the rule and I for example just truly several then knows those servers are in the same group when you promote a rule 1 of the cerebrum is chosen by the master you can even provide a the once you do it this is the master of the orders of a fabric provides in initializing the slaves replicating everything and then we look up you can just look at the address if there is 1 the primary server and to the reserve set the weights on the server so if you want to do most of the queries goal to the
first one you said an idea weights or if you want to test the found that 71 1 several would find that 7 you give it a lower weight so you don't disrupt your infrastructure you you activates the starts monitoring for for for fault then we stop whatsoever with most relevant mean shut down and fabric magically it not the master from 1 of those place now we have a full to a primary server and thus a comparison of the each down ultimately just type the common you see on the slides on the you can see in the doctor major to connect to fabric yes this divided from a cost specify the the Bayesian this correct credentials you just said with variety from Greek goes to the Master Peter's said
read only fabric goes this if there is a fault fabric blind source you read write to the new master everything from
his to provision and you of to provision and use later you can just go the rules on annual target so you have 1 set and then you close the rule all on this side of the family who knows who is the
mass that's scary
provision new machine of a stock at the I didn't want to provision new machine a wanted to provision you container we don't have so I implemented these interfaces using a blocker clients no if I on reduced and you provided just reduced so I provide a kind of kind you see provide a
type was blocking then when I create a new container from mind OK provide I just telling manually major instead of our of a stock
and plates and then a provisional new machine and you continue database then I can just obvious that the baby FIL I got the information of December from several East and again just found these 1 to my to group and work might be solution is this skeleton so we have only names but I am that we can see if you're interested you can contact me I will see the ground not on of the missing is light there 1 thing after that
is that almost like interface subpart shoes that showed the another client Dr. volumes doesn't support the snapshot but something is more in uh I started to 20 commodity if you're interested in these uh let me know again find try to find out a better solution that is good for me and for you so reprojection is 0 with Frederick and muscular you can include server file although is just 1 common the hands dollar
just followed must master if you have a master server that is broad and just take it and dump you don't use it please try fabric we don't care and play with dog abundance OK have been in a hurry by the time is a tyrant that's so thank
you very much for your attention Thank
you Questions say which is what might many we show that we can see that we
used to do that and we we we we we really OK the guy ask how can you make I have a million of 107 where you improvement master-slave replication of the fabric that babies and you can have cluster to implement far to create resilient infrastructure 1 further anyway you need your 407 dies the most about server using from a connector OS all the information to connect the server because when they 1st connect to fabric Frederick gives them all the information on the infrastructure so they know is the master and what is late yeah the master server variance you should have a cluster that checks the mask is fabric is not and if it's not should start another fabric is stance on another machine it is very easy because fabric is a simple by then application so it once you have replicated the base you just have to Ron in 1 2nd another fabric is done using the uh replicated database other questions the of a simple question will do you know it's so the replication is supported in more you have to be replication I think is supported many in the about actually this kind of about this and about uh not only replication about to scaling because you can navigation with many that embraces the advantage of those tools is that using a simple so that gives you the ability to wear many drops of databases through many times then 20 100 server easily because once you have created the group you have that you can't even provision new machine if there a number of stock implementation you you you will have to know that 1 of the group as to several also you can just provision to machine the 1 group to those 2 machine and other so that is not only about replication it's about the orchestration OK and you get a Python called sure you can figure code it's GPS you can send the garden you can measure out you know when you dealing uh you can mention you DVDs so you need something using 2 stages and uh auditory longer passing you told just to work and so the functioning part module and mashup and create your your own uh application for uh I think the real advantage of using python of choosing Python is that uh it's really easy to to do whatever you want but we just code so don't think only about replication think big think I at many labeled bottle you can take all these little blocks and this is what I want this which of the the are completely
screwed if you're stuck on my sequel soluble solid or how we cause muscular and that introduces the global transaction ID this global production the uh gives the ability to easily find the most of the the theory because they can just check which I display with matter transaction applied the problem with the fact that 5 is that the obligation rather than the position the log files in the binary logs while in having a hybrid section and you don't need to disperse proposition you just have the master I and got those blocks of transaction me the ones that I don't have whatever and the positioning so I can get some parts uh before and some parts in other uh side and I strongly suggest to me rates as soon as possible in near it nicely so make your test not replication is the may play nice with friend that seeks but look at the future look at from that city's rule get fired that sentence Friday even if it's not stable or general been from the sentence starts start to try because there are many improvement on performance and the storage engine those are really the changing of the friend that 7 it is the important stuff for people think caring of production of a consistent consistency and so on OK and in and out of time we can talk about the cafeteria thank you