Merken

1 M+ QPS on MySQL Galera cluster

Zitierlink des Filmsegments
Embed Code

Automatisierte Medienanalyse

Beta
Erkannte Entitäten
Sprachtranskript
all right so it is quite a past 3 so I'm not going to start welcome my and my name is walter back and I'm not here to have presented a session I was originally titled 600 thousand queries per 2nd on a musical their cluster but as I was preparing slides this week I realize that we are past and that is degrees per 2nd so I have my own presentation titled that so it makes our own only telling you mention the stuff about it in a case study and a ours that we we're building their cluster for I will also we will look into how all that is is that of 1st of all I will
my and my name is walter and suffered really was 1 of the programmer about obviously that a language died so it was time to move on to something else and then my next challenge was my sequel and and then systematization and then started running all the data and so on and less technical stuff now and a little bit of my sickle cell so this is founder of and data we are in an open source training consulting and company we our public labs partner for the whole of Asia and that part of Europe and do a bunch of other training on has some OpenStack and some icicle consulting so I'm standing here and all bunch of other stuff what will be talking about today this is more or less the overview and so did the bicycle clusters in their clusters around uh compared with the with puppet so we'll we'll talk a little bit about what we do with but that it's not so much the topic of focus here so will I will just take a quick look at a and Miller cluster for those of you who are not familiar with it doesn't many who here is running in their cluster that's getting more over the years so know some terminology and look at the elements summer statistics of what we know what we're running in what kind of hardware area where have and all that the age proxy set up sitting in front of you go there are well look at the stuff like monitoring and how we do operations some backups and and always will end with what the next challenges are because any rowing infrastructure in challenges never really and so a million was funded but now needs to scale to the next step in the next couple of years and so we have some more challenges coming our way the so 1st off and a little bit about the puppet who here is familiar with puppet so but is a configuration management software open source configuration management software it's actually commercially supported open source so you can choose to start with my cyclic and open source version can choosy and a prize a supported version of this configuration management software so what you do is know that's a all explained to you as I explained to my 73 year-old mother and that should get everybody on the same line in the old days you have done that that's the woodlot and every laptop insult microsoft office on each 1 of them and that would be horrible and with a suffer like but but there are many others like it sold shaft and you know so with that kind of stuff where you take 1 of those laptops using k if 1 of the other 9 laptops comes in and asks what it means to be in the running time to go and solve microsoft office now I only have to work on 1 laptop and all the other 9 are managing themselves but but it's the same as the same story only we're talking about servers and larger than 10 nodes that but happens to scale quite nicely and run it on to server is the very 1st time you are using it just to to play with it but certainly in in Switzerland that are running on somewhere between 200 and 400 thousand nodes so it scales quite well always see 200 thousand is not going to be out of the box you need to Wilson stance due to get that working but yeah so if researchers of 10 15 thousand they're not really an exception for for infrastructures 1 of the nice things is that the type of is very multiplatform so I heard the founder of other labs ones colored the Switzerland of IT they want to try and not be specific to operating systems runs an unknown windows many Unix flavors of Mac OS B is the all these things some of the neural network devices also run poverty agents which makes it nice and very usable for and the structures that are running every operating system design at with puppet you right infrastructures code and so you get all the benefits of having your whole infrastructure written down encode and which means that you get a whole bunch of uh of benefits and I want to go too deep into that right now this is a typical of public architecture and you do if you assign 1 or more machines in your network to be a puppet master that is a machine that runs a demon called the Puppet Master there is a get repository that that that 1 that has all your public coordinate and it doesn't have to be given by the way it doesn't have to be in version control theory I don't want to I couldn't imagine why but most people are running and running the did at the moment so we have to get a repository contains only puppet code that is checked out onto the puppet master and and all the servers on your network here see the red dot in the 1 solar servicing network I just created 3 ones and untried act and they don't actually exist I think the whole some 3 rounds 3 so all servers in a network are running at a small little a program called the Public Agency which you can also run as a as a demon or many people write it as a crime jobs that wakes up every 30 minutes and the agent connects to the public master and says about master hey web 0 1 not little have gone to tell me what I want my configuration it's still like the puppet master looks into the public called to see OK this is a configuration is to be running my sequel service convict violently so these users and these directories created and compiles all that in an adjacent file called them the manifest and send that right back to the public
Agency that still waiting here on the on that machine that of an agent receives that configuration in comparison to the to the server itself and any differences are changed or at least attempted to be changed so that the server after the public agent run looks exactly like the puppet Master Stolojan that's in a nutshell how an output that works at go there clusters and so this created by a company called recordership that has renamed itself to deliver cluster of mistaking and because nobody knew co-authorship and everybody knew their clusters so then they decided to rename themselves which I think is probably a good move but the labs was also not called public lands before but then everything you public and nobody knew reductive lab so they decided to change animals so so although the classes is basically a library compiled on top of my sequel it's also commercials on open source you can use the open-source version or you can pay them for uh some excellent support if you if you want but it's also multiplatform that distributions available so both the procurement variety be compiled offer a version of their my sequel and distribution that has galleria are compiled into it so you have to get into that yourself is this follows from my from their baggage repositories which makes life a lot easier but it provides a high availability in a way that is quite strange for people who are used to traditional uh my skull replication so traditional myself replication is asynchronous and where did you send the query to the to the masses and and the master executes it writes it to a lot of their slave downloads it and executed as well whereas with the regulator an when a when you send the query to to 1 of the nodes it won't return until all the nodes have at least committed that they can execute the query don't all actually execute execute the query but they at least as a I can I can do this and if they all do that then it comes back it means that your failover uh situation will be very different with the traditional model of replication if it a slave failed OK not the end of the world the world that your master failed and you had to make another slave master and then have all the other slaves and connects to the to the new masseur figure out which rather a position to be at was not really a fun exercise to be honest with in this case we have a 5 nodes in a cluster of 6 nodes actually at the moment but it should be 5 and if 1 drops out there and the other for just keep working and no big problem usually I'll tell you some horror stories of things I've watched that were not really a lot of fun and over time the brain is the and a situation in which uh and there is a networks splits and but the both sides cannot really figure out if they are the uh the biggest part of the so the literary uses something called a quorum and which means that if a has 1 or more nodes fall off of the cluster if the and the remaining nodes are in uh piece of the cluster that is more than half the class I don't keep working in otherwise I'll shut themselves down because they might be this was a smaller piece of so split-brain happens when you have for instance for nodes in the cluster and get split middle and then both of them have no idea if there majority so the recommendation is always have an uneven number of nodes in a cluster we are failing to we and will commit to that idea with our cluster we have 6 nodes in the cluster but realistically with 6 and the chances that such a thing happens is so small that we haven't seen it so far at all the nodes are on the same physical network in the same data centers and so that there's less of a chance then when there was going to be a replication over over whether I is the SST one so the winner the latter works is that if a new node joins the Cluster whether that node was previously down or not and as Haiti and Union a new node do you already have data yes the what was the last transaction that you committed into your note if that falls within a certain and cached than I can send you an incremental state transfer only the differences that you missed out of great in reality it doesn't happen so much and you can increase that buffer and to a larger amount and but it's not really uh a in in such a high performance clusters it rarely happens in ICT that happens in incremental state transfer and the other option is a SST which is called state snapshot chance for that correct the Nokia and I think that's what it is as a C and you'll become very familiar with the word SST and you will probably start hating it after a while as as well and this is the is uh 8 wider all data from the server and copy it all from another node in the cluster which means that the other node and as the as the cluster like a new copy of the data and the classes as OK this node will be the donor itself that to state that is that the the the donor states and and that node will start copying the data over 2 over the network to the other to node that is received and with a 380 database that becomes annoying at best not so that was not the
caffeine 1 of the best tools and lecture and so had a becomes annoying invest in the very beginning we were on 100 network that was really ridiculous that you would spend half a day waiting for a state transfer and then we were on gigabit networks in that makes sense 40 minutes 2 hours something like that depending on the the day of the week and now there's a new cluster and I will be on the content of the network and that should make it a lot nicer accepted than other hardware becomes the bottle neck and so herself and works of figuring that out so you all who have it yeah but more often than you would like to I would say a couple of times a month depends a bit too sometimes it's it's like any operation sometimes it's stable for forever and then polymer-solvent size happening all over the place so what happens is generally is that when something happens to this cluster it doesn't just happen to 1 node it happens to 1 node and then another falls over with this kind of high performance clusters it's really difficult to have the proper of failover I don't think that the class has gone completely down more than a couple of times but it that but it has happened and indeed simply because this is on the Galleria is very trigger-happy get to that in a minute but you'll see some of the downsides of the so so all yeah so so that the problem is that 1 of the problems is that if you have for instance 6 nodes and 1 node goes down then another node has to come down to to donate to that node that is recovering which means you have to know is going down if you're if that happens happen in a high peak traffic situation all of a sudden you will only have 60 per cent of your capacity left and then it's very easy for more misery to happen and there is a so I mean 2 categories immediate caracoled features the neural chemical features so and then on the plus side we have the features and the regularizer true multimaster so you can read and write to any known at any time and in in reality and it's not entirely true and if you have hot spots in your data and also certain Rosa given to really really frequently and then writing to multiple nodes for those same same grows at the same time is not going to be a pleasant experience because you'll get a complex and that it is translated into a a deadlock and sold to cover that most people either right everything to a single or rights a specific parts of the application to a single so you could write everything it has to do with users to 101 and everything has to do with logs to or whatever to another node and everything as it with messages to get and all that kind of stuff makes that a little bit easier so synchronous application which means that there is no slave life uh which uh for those of you who have run bicycle traditional replication earlier this year I don't miss that at all actually lost data and when a node crashes also doesn't happen with the traditional replication you have these a whole situations where slave went down on a master and down in the application started writing to another master but it didn't really figured out that it was not masters so all kinds of mass would happen regularly that's all gone tell all the nodes are always the same mostly there are ways that you have to mess that up as well which I'll get to in a minute multiphase slave so there has of multithreaded that applied thing and where you can have we have we run 16 transits and I will also show you in a bit but so we before this we were running a master slave with an amount of very old in Montreal was here in 2009 preaching memorize stories on curious to see what that will be preaching in 5 years from now but the and man you had this holes label a master-slave failover and had a virtual IP that you're writing to in a virtual peace should be moved to the new master but it didn't always work that way a cache that we get stock all these things was very pretty that you can have a hot standby is because there is no real the downtime during failover because there isn't a lower in the 1st place just an stops working period automatic node provisioning this is something that I would also put on the beaches list because it's also super 9 but at the same time and with traditional replication of slavery messed up you to copy the data from another slave and do the same thing manually that you should be able to do automatically Gilera does this for you so you don't have to worry about copying the over from slide sports in B I put that also on the features list because it only supports innovative and there is some option to also sort of replicate my son but there is no guarantees about any kind of uh stability or like and stuff so just don't do that but 1 of the fun thing is is that it's a transparent applications so before you had to figure out which known to be to initial to right to and that really is gone you can write to any node and similar rewrite splitting is taking the needed and we came from a situation where we had that because we were on this whole a master-slave replication alright so then we decided to sort of stick that and delivered speeches at some sort of trigger happy and we've had situations where of foreign always went down the we have a situation so originally the uh Clusterers running this an operated from always from my circle stop my circle 5 0 0 Moravia 5 2 1 5 3 5 5 and then 2 and proponents server 5 6 I believe amendable turns out there's somebody with the storage of data times and that changed from 5 5 2 5 6 or something and the way and we have to nodes that were never and old my simple version they were always there they just came into cluster as big see uh nodes and the other 4 came from this old uh move move move and datasets so they had actually this problem which was inherited kind of hidden node problem under 1 day we had about all rules for all nodes went down like nothing and the fish and 1 of the 2 that was left over did a went to donate to 1 the other 4 and then that would render the other note went down because all the traffic of the whole application was rammed onto a single server which was really uh anything suitable so 5 of the 6 nodes were vanished like I was a bit more than a minute but watching that happen as as such but that 1 is also and that's what it was not good so what delivered as is when a node comes back up and and decides it needs to do a full state transfer of necessary it will wipe everything from the data directory completely without asking you how should I just wipe everything which is freaking scary and I would like there to be an option that says before he wiped out all my data from the server just I know stop and ask me or just stop and let me set a specific flag that says OK school in you white everything from this server because it's scary and you lose all data that's on there it doesn't actually doing on the shaft but it's essentially what and pretty hard to to set up there lots and lots of options and they need to match exactly and are lots of moving parts to do an SST
it uses the there's many methods but the most useful ones and in most cases is xtrabackup images of proponent of backup tool that doesn't smart stuff but in order to have the state transfer happen correctly you to both have all the and the the disk of the directory permissions have to be exactly accurate both on the donating known as well as on the receiving node and but you never know which node is going to be a donor because it can be any node in the cluster that's up to the cluster to decide and you have to have the permission set correctly for the xtrabackup tool to run in the 1st place all this seems pretty trivial but before you have the whole set of options figure out nicely usually takes a while so we we did this with the with puppet model and which helped a lot you can find on and get out that conflation all data and as a the Galera but module is there that's the 1 that we use in some operations are not supported I don't know them from the top of my head and I neglected to look them up and but there are a few things that you shouldn't do on angular cluster and that you can do some roles and was the last feature that I wrote down is the only technically in is the only supported star search engine and we had last week or the week before that there are some developer creates a table that was my the sum and it happened he happened to created on the on 1 of the nodes that was also the only 1 reading it back but uh if you do that and you don't have the support enables and it'll just create a eyes table on that 1 server and not on any of the other servers and just sit there and nothing nobody will notice and nothing logger by the way you have a table that is nowhere else and I have technically inconsistent cluster so after God knows how many days and 1 of our guys was comparing just a routine check after we had some kind of an incidence is we have this 1 table exists on only this node and not on any other node and then it turned out to be a minus some table so be careful with that the all that right yeah so we just turned off the whole mass the support don't think you should be using it but unfortunately that my circle it's only in the create table you can anyone can set the storage engine and it's not going to failed it is not going to replicate so I would like to if somebody executed a statement that says create a table with the mice and the legend uh a storage engine failed that's not allowed because I don't have any support for that would be much less I think 1 of the things that are missing here and is also enhance your logs are super hard to read the Galleria logs a whole bunch of stuff that is all completely irrelevant to and when it does a lot of errors or problems and logs it in such a way that you have to be able to read the Egyptian upside down and in in order to be able to figure out what it does or what it means and especially when things go wrong with the user a the IOC's and along spotted on the 1 node that is donating imparted on the node that is receiving iteration and but then that if something goes wrong on the xtrabackup cited section the extra lack of log file that is secretly hidden away in your data directory so it's a fun adventure to figure out what to 1 gets along well so that was the introduction to of the Museum of RE not halfway through that's perfect and so now a little bit about this specific case we have a client that runs a holiday property booking application where people who owns a cottage can or multiple cottages can manage the bookings that come in for those colleges and their integration with things like booking uncommon whole bunch of other websites so there is a special and then working of gone synchronization happens the whole the availability of these colleges needs to be updated then calculated and sent to looking at common they're pretty trigger as well with the with the API so that's pretty heavy and complicated queries with this uh the checking the availability of a property so somebody says I want to grant this property from on august 20th did 20 7th and then you have to figure out if it's available in what the prices for that we can all like himself so pretty complicated cruiser there is a single my sequel database actions 300 and by now 1 of 500 + still living its 600 by now gives growing they developed quite fast and the observers are not under our control and so the the client that develops application themselves and there is the classic 1 of which is fun because it doesn't support things like memcached so they use a database for catch me that the news is that this is the classic is still supported until 2020 3 or something like that so ridiculous yes here I'd like to being surprised there they're looking at the dialog with the correct heritages dream that it gets a support that long on the we standing and it especially a lot of sort functions to calculate things on the database server instead of on the web servers and specifically those availability agrees they some nice tricks to make it all work a bit faster was looked like the the database side of the whole story and it's very simple we have a dealer and on and that takes mostly rights and summaries and then the other 5 Aguilera knows as I said before you can write to any node with clear about what we and I came from was this traditional or my sequel replication set up where we already had a split between read and write traffic and uh just turn out to be the easiest to keep it that way and so what we did is we have a energy proxy an instance in front of the genera nodes it happens to be running on the would be designated as the primary writer and proxy listens on 2 different ports and application sounds all those right traffic to 1 3 3 0 6 is traffic 2 1 3 3 0 7 and the age proxy instance wipes all the traffic from that comes in on this board straight to the primary writer unless that 1 is down and all the real traffic goes straight to the other 5 nodes but we haven't passivated proxy sitting here as well in case this 1 introduced this whole machine fails because these 2 things run on the same physical machine it's all physical machines like and the reason they're on on the same physical machine is still eliminate the network hop between the age of proxy and the primary writer just say performance so so there's a passage approxi we have never needed but it's there in case this machine goes down for whatever reason we have had in the literature that we haven't had the machine them so they should proxy has also never gone down its super-stable it's really really impressed by what is the what it can do and very high throughput no problems we have the whole thing is managed by a open-source puppetmasters 3 . 7 point 5 and the Martin Kay which the final deployment tool for public and NGO and means and collective which is a orchestration tool that comes with puppet won't keep those 2 decides not really the focus for today but so this is mostly the kind the simple set and what we have for hardware all 6 servers are exactly the same 1 runs deviant 728 at the moment I have we have some fairly averages CPU is nothing and nothing to an intense 64 gigs of memory this was taken at a lower traffic time the summer last nite actually so the load is not too high
and that's running quite nicely done the Galleria related configurations and so when you have a bit of a delay or my sequel my sequel Gilera built and you get a bunch of new system variables so we have the SST method said to xtrabackup feature which is kind of the most used uh as as the method and you can do things that are a sink in my sequel dump as well but the mice equal down uh style SST is blocking so it means that your donor cannot do anything until it's done bicycle dumping them in and then has to catch up and the slave threads we have set to 16 and 16 threads and applying it degrees from the other nodes at all times and the cache is the angular cash I mentioned that it a node comes back up after it has been down it will do an incremental state transfer if the amount of rights that it's had uh so if the amount of rights that the rest of the clusters had is less than its gas reset that attached to 1 game at the moment that much of the my sequel of options that we said set up them formats uh is as the role for angular Greek caches not supported so that is turned off as well as the provider options this is where he's at the duke cash but so we set that to 1 day we have slave threads was that the 16 and already mentioned at all to increment control so and if you set this to 1 and then and then the other to increment incrementally increment offset uh settings will be managed by Aguilera itself so you don't have to worry about it this is that this has to do with making sure that the primary keys are far enough apart so you don't have to worry about and some collisions and and priority assignments if you have a lot of 2 primary keys are set up and you have a delta and notifies scripts not super happy about this so and American whenever something happens to cluster of change happens to cluster in and a run this script and automatically you can notified whatever you want to notify but there is about 6 or 7 potential options that it can pass and most of the cases so passes only 1 of those options so you don't really have much information so but mildly useful showing that what we're what we're using it for this is the method is said to actually complete 2 only mentioned that the uh as the authentication uh so depending on what the assisting method you using this requires some other values but you know this is not our actual password guessing case here but anyway that that's how you mention the username and the password of that user and this is the user name and password are used by the and when the xtrabackup needs to make its back out of the donating node so they can send it off to the to the receiving then some as as the specific options and we set the stream format to xt stream you can read of over here why we should use that 1 and not the other ones and 1 and then some find ones recent progress to 1 especially with a large database is good to be able to see how long how far you along you are in your SST and lots of it merely so we set in just 1 so it the right semantical standard errors have detailing the mice equal error log detail batch after uh the bicycle along because otherwise logs in very strange way but if you tell that Scheffer you can nicely see the associ coming along and so that's quite useful and time equals 1 I don't know what it does if you don't have time equals 1 set but anyway we have I don't that sound like I should probably look up what it does exactly what I managed to not do that I finished my slides 10 minutes before the year presentation so bear with me and broke my book by finger and last week so that can be destroyed a whole bunch of their plans ahead as simple as a backstage as from the from 1 of the nodes that so as you can see I had to figure out what this says 2 trillion if I'm not mistaken it appears correct me if I'm wrong with it I think this says to try and so on to train queries that 80 thousand of them were slow which normally 80 thousand is a reasonably high number about 1 to 2 and unprepared to let that slide and queries per 2nd on average so that this is not the uh the uh the throughput at this moment this is this number divided by 140 days 19 hours 30 minutes and 22 seconds so this is more like sustained throughput or the Abbas throughput and 185 thousand so if you multiply that by 6 to over a million I notice that yesterday when I was making use of the specific slides actually quite happy that we needed prove million because I was wondering when that would happen in but I yeah that's that's what we have there we using the upper corner actually the cluster we were using right in the in the beginning and then and there was at some point a bug that was not sold in writing and was solved in Percona Cluster had to do with the sword functions that they're using so we had to switch to the upper corner and really we have really notice much difference to be honest but the size of the data 240 thousand megabytes on on this we are still have some some energy stuff there so on this the whole data directory is about 300 gigs and but the data the database is 240 thousand change in in the database here so also that is not really values so much for the hardware and my signal on the age proxy side I'm really impressed by stability and throughput which is about 10 terrabytes of my mystical traffic through and its interfaces every month I'll show in a minute we have well 1 writer front end and 1 reader front end and the writer it sends all of its node to the sink to the same back with the other 5 nodes at the back ups so if the primary goes on and sends it to the next 1 maximum next etc. etc. interior front end just round robin school all machines and doesn't really care but it what not just as simple as that and get consolidated proxy and it's fairly straightforward and then we run age approxi so in order to run as it approaches to most TCP IP mode and it should be be known and in order to run this easily and we rented in HTTP mode which sounds a bit strange considering what I'm about bicycle traffic and but what we're doing is that the health of each node is checked by a simple exited the script that you just calculate checks a bunch of different conditions and if they're all good then reports a fake HTTP status code and if it's not OK then in reports of 500 or something like that of arbitrary and then at the end you brought it takes that node out of the air replication it's about hockey and you don't really have much control over status reporting inside the age of proxy front end up but it works and it works really really well so and will talk about backups later as well but 1 of the nodes backups every 4 hours backs up for every 4 hours so every time it does that it reports on healthy to the uh to which proxy instance and diskette sticking out of the load balancing easily and we have to worry about failover by age approxi instance on 1 of the other machines so that if 1 goes out and get alone will take over as I said we've never needed
it's strange 1 of the slides corner like London I copied from
another presentation and guilty so this is the description of the Echopraxia status the interference you can see here that they have they all have the status code 200 you can see that the little the 1 that has been so the whole thing has been up for 141 days I even see here that this 1 has been out for 133 days all the other ones have been down at some point or the other and but that hasn't affected you can update of the cluster as a whole and call waiting
you can see furthermore that we have here about 32 there by its outgoing and the incoming so that's about 40 terrorists uh of communion in line outgoing and incoming on the reader front end and this is the right front and you can see here that it only sends a traffic to the uh to the green node and the other 5 are blue and lose color for back up and so these are actually the same 6 nodes and the and the writer is a back up In the reader interface of doesn't really send traffic there another really really really has to happen because there that single-mode cannot really deal with the reader traffic and writer traffic at the same time strictly those are the most interesting parts about the the the front end here and the health
check and that's the related the Galleria health check and comes from the top of model and put the you you're all here at the top so you can go check it out so we check a number of things at this state of the local state not because not 2 or 4 and I believe that 2 4 is online or donor and so I 1 1 as a donor it's still in the cluster and then returns an arrow and whenever we'd say return fail that just returns an HDP cord of firewood 3 and leaves so far or on 4 and various web connected also is connected to the rest of the classes the clusters status this is primarily it is healthy and donors donating reducing if it's not healthy so only if its primary and or actually it's not primarily return failure various red ready must be displayed on and we checked lastly if the clusters state you utility is equal to the local state you idiot this has to do with the configuration for not mistaken not actually 100 per cent sure so we don't write things it's a kind of redundancy and but we decided to check more than what was needed because it's relatively fast anyway so better safe than sorry in this case we also track the added disk space by where found that that wasn't a we have that happen once that this grant full and in and checking my circle just went down created all kinds of chaos so that wasn't pretty but that's not in this in this slide monitoring use a a self-hosted version of peer to you not necessarily my favorite tool that's client users also and supports windows and supports all kinds of other stuff so it works and here you can see that everything's healthy just to serve as a running a little on this 1 what we do with the regular notify script is that uh we send messages to our have chat rooms so within all data we have rooms for each client and uh the notifies script sends automatically as a message to the uh hit chat rooms if something is wrong with the class or if you change happens in the cluster doesn't necessarily mean that something is wrong and so we send a message to the internet chat room we do the same thing for out just to make sure that there is a message that says hated that the back of his completed but a chat is quite convenient it's basically a fancy version of our see with the president when you log in to have chat you can see what happened before you were there instead of not being able to see that as it is in C so that makes it a very useful for this kind of things you see here that node went down and affects the incident of the SST was initiated here at 6 15 and then it takes about an hour for the to to complete for backups we used to have so with the cluster it's quite nice because it works on a completely different hence part the traditional applications so you can set the whole Galera cluster as a slave of a of another my single and my sequel server or you can set my a single my sequel server as a slave of a cluster all that is possible we used to have that for backups but it didn't really work out very nice was an offside slave either something went wrong with the the traffic between the legislative and and the cluster on it classes executing some great the slave couldn't handle is this was pre-eminent broken and had to be fixed and it's not something you want to be worried about so what we do now is really uh make backups on 1 of the nodes that we do have full in a backup X daily and then 5 incremental backups we do a data my sickled on and and they just get sink mother of sites to the backup server but with the added advantage that really the last backup on the knowledge that is making a back up so that when something really goes wrong we already have a local back up most of the time I must say that when backups are needed as some developer that accidentally did something that you should have done to the production database and is just a taking you get me back up of Table XYZ and which is why we make my single bonds and arsenic bicycle bonds of every table individually because restoring the free energy right there the data by database to retrieve 10 rows from a table that has 20 rows is really quite annoying sometime this week somebody accidentally overall data and update without a where clause so we have all the records in the table overwritten with the same value not pretty but those moments you're very happy that you have made back ups per table and you don't have to go and sit there and wait until 300 You bytes of data is restored to and see if the back has the actual data that you're looking for that you need to go like in time property so bicycle a yes it to make a back up said the sink and so
on which kind of that takes that it makes the node unhealthy in R & it's proxy a instance so it doesn't receive any direct traffic anymore it does receive the traffic from there from the rest of the cluster but at least it quieted down enough for that known to make backups it makes the back of a nondescript sets the sink to offer so that works quite well actually much nicer than this old situation 8 minutes that's perfect because is my last slide the current issues and what's next and so because the application keeps growing more you skip signing up which is all great but it means more headache for us and we have known for quite a while that this database needs to be split but it happens to have built as a monolithic application that isn't so easy to split so that has been postponed and postponed and possible little more until now we're actually so yeah pretty much so now we are reaching the max capacity of 6 nodular cluster and the data is getting quite big there is a bunch of reasons so many reasons to make an actual effort to split off often to shards that the data that are actually in the process of doing that you're actually there in the process of doing that for the operations side of databases of the mystical the there is not really that much we just need to set up a 2nd pillar cluster and then the 3rd and 4th and whatever and so we have decided to go and do that on unattended network but new issues that's that's really this week kind of things we're going to figure out what the problem is exactly and 1 of the problems is backup Stiglitz space even if you take a back up the day you you want to keep the last 7 days of back ups and you take the incremental backups have my sequel don't want to keep the the backups from in the last 7 days from 2 weeks ago from a month ago from 2 months ago and from 3 months ago you end up with a time of need space and so on for now it's still all fits of this it's a giant storage server but that's not as exactly and because also when the 1 node that sits and because it's making data to back up has to back up the whole the thing from both in America and in my sequel don so that takes a bunch of a bunch of space for now I think we are OK but if that keeps growing then we will have some new challenges on our on our hands I think that was most of what I want to talk about when considering how 5 minutes left perfect obviously I would be qualified number signs as we're hiring so we're hiring an issue like open the sources after I love traveling and and small team of skilled people and this I think scaled and then send e-mail to jobs at all medical more to myself I'm on twitter Walter height can find its way out that's all we're ready for questions on very fancy question might have 11 states yet as sources for use set WS traps slave connects to yes why said this is a seems like to me well we had access to lower and this provided better and better throughput so that's what we did the yeah we played around with in the very beginning and then never touch the and so must honestly say that it's been there for quite a while Back then this provided the best performance that's why I said that was but thank you my name's it will hold like to ask you whether you plan your customer requirements multi-datacentre His centrepin the future and the future yes but it current time-space continuum we have managed to convince them that the multi-datacentre failover is more pain in his work and to think about and this is my who believe that there is a chance the off chance of the whole data center goes down that has happened once or twice it's a holiday cottage booking applications in which they have received the actual death threats during downtime yes on the only some of the data on the site the players in seems all get distance that's you know as I'm yes downtime is a big problems and yes it should not happen but the amount of problems we had from this under downtime is so limited that it simply that currently not worth going through the trouble of adding the complexity of multi dissenters thank you and has anyone else I saw a hand up there someone who was this the the number all right then and if there is any questions thank you very much if you have any questions earlier on all come and find me here or online I
Rechenschieber
Beobachtungsstudie
Offene Menge
Freeware
Software
Minimalgrad
Abfrage
Kombinatorische Gruppentheorie
Hecke-Operator
Computeranimation
Distributionstheorie
Programmiergerät
Bit
Momentenproblem
Formale Sprache
Versionsverwaltung
Wärmeübergang
Fortsetzung <Mathematik>
Element <Mathematik>
Eins
Rechenzentrum
Prozess <Informatik>
Datenreplikation
Bildschirmfenster
Figurierte Zahl
Regulator <Mathematik>
Gerade
Funktion <Mathematik>
Bildauflösung
Zentrische Streckung
Nichtlinearer Operator
Statistik
Hardware
Dokumentenserver
Datennetz
Datenhaltung
Hochverfügbarkeit
Abfrage
Ausnahmebehandlung
Konfiguration <Informatik>
Transaktionsverwaltung
Konfigurationsverwaltung
Dienst <Informatik>
Rechter Winkel
Server
Decodierung
Verzeichnisdienst
Koordinaten
Varietät <Mathematik>
Instantiierung
Aggregatzustand
Proxy Server
Subtraktion
Wellenpaket
Ortsoperator
Quader
Klasse <Mathematik>
Zahlenbereich
Zellularer Automat
EDV-Beratung
Unrundheit
Code
Physikalische Theorie
Puffer <Netzplantechnik>
Virtuelle Maschine
Informationsmodellierung
Knotenmenge
Datensatz
Benutzerbeteiligung
Software
Netzbetriebssystem
Notebook-Computer
Datentyp
Programmbibliothek
Cluster <Rechnernetz>
Datenstruktur
Optimierung
Konfigurationsraum
Videospiel
Open Source
Paarvergleich
Physikalisches System
Elektronische Publikation
Fokalpunkt
Office-Paket
Flächeninhalt
Mereologie
Wort <Informatik>
Computerarchitektur
Dämon <Informatik>
Neuronales Netz
Bit
Multiplikation
Klasse <Mathematik>
Gruppenoperation
Versionsverwaltung
Systemzusammenbruch
Sprachsynthese
Kartesische Koordinaten
Wärmeübergang
Login
Knotenmenge
Regulärer Graph
Fahne <Mathematik>
Datenreplikation
Inhalt <Mathematik>
Speicher <Informatik>
Cluster <Rechnernetz>
Metropolitan area network
Nichtlinearer Operator
Videospiel
Verklemmung
Kreisfläche
Hardware
Datennetz
Kategorie <Mathematik>
Kanalkapazität
Ruhmasse
Schlussregel
Mailing-Liste
Frequenz
Quick-Sort
Konfiguration <Informatik>
Rechenschieber
Rechter Winkel
Caching
Heegaard-Zerlegung
Mereologie
Server
Verzeichnisdienst
Message-Passing
Instantiierung
Aggregatzustand
Gewichtete Summe
Momentenproblem
Extrempunkt
Schreiben <Datenverarbeitung>
Wärmeübergang
Service provider
Gerichteter Graph
Streaming <Kommunikationstechnik>
Client
Perfekte Gruppe
Suchmaschine
Datenreplikation
Skript <Programm>
Vorlesung/Konferenz
Schnittstelle
Befehl <Informatik>
Hardware
Datennetz
Datenlogger
Kategorie <Mathematik>
Ruhmasse
Hoax
Menge
Rechter Winkel
Konditionszahl
Festspeicher
Grundsätze ordnungsmäßiger Datenverarbeitung
Heegaard-Zerlegung
Server
Ordnung <Mathematik>
Fehlermeldung
Tabelle <Informatik>
Lesen <Datenverarbeitung>
Instantiierung
Stabilitätstheorie <Logik>
Subtraktion
Mathematisierung
Automatische Handlungsplanung
Unrundheit
Whiteboard
Stichprobenfehler
Virtuelle Maschine
Informationsmodellierung
Knotenmenge
Variable
Arithmetische Folge
Spieltheorie
Thread
Cluster <Rechnernetz>
Konfigurationsraum
Varianz
Diskette
Modul
Chipkarte
Programmfehler
Caching
Debugging
Gamecontroller
Authentifikation
Bit
Punkt
Fortsetzung <Mathematik>
Kartesische Koordinaten
Inzidenzalgebra
Login
Datensicherung
Synchronisierung
Eins
Metropolitan area network
Figurierte Zahl
Nichtlinearer Operator
Lineares Funktional
ATM
Datenhaltung
Klassische Physik
Abfrage
Konfiguration <Informatik>
Rechenschieber
Dateiformat
Garbentheorie
Information
Schlüsselverwaltung
Verzeichnisdienst
Aggregatzustand
Proxy Server
Web Site
Stoß
Gruppenoperation
Zahlenbereich
Kombinatorische Gruppentheorie
Zentraleinheit
Abenteuerspiel
Code
Benutzerbeteiligung
Mittelwert
Mini-Disc
Luenberger-Beobachter
Passwort
Speicher <Informatik>
Softwareentwickler
Bildgebendes Verfahren
Schreib-Lese-Kopf
Assoziativgesetz
Kreisfläche
Relativitätstheorie
Zwei
Physikalisches System
Fokalpunkt
Quick-Sort
Integral
Energiedichte
Minimalgrad
Differenzkern
Last
Speicherabzug
Stapelverarbeitung
Verkehrsinformation
Rechenschieber
Deskriptive Statistik
Punkt
Vorlesung/Konferenz
Code
Eins
Web Site
Momentenproblem
Relationentheorie
Klasse <Mathematik>
Versionsverwaltung
Zahlenbereich
Kartesische Koordinaten
Fortsetzung <Mathematik>
Inzidenzalgebra
Datensicherung
Raum-Zeit
Gerichteter Graph
Informationsmodellierung
Benutzerbeteiligung
Datensatz
Client
Knotenmenge
Lesezeichen <Internet>
Mini-Disc
Bildschirmfenster
Unordnung
Zeitrichtung
Skript <Programm>
Vorlesung/Konferenz
Cluster <Rechnernetz>
Softwareentwickler
Konfigurationsraum
Gerade
Schnittstelle
Kreisfläche
Kategorie <Mathematik>
Datenhaltung
Softwarewerkzeug
Einfache Genauigkeit
Biprodukt
Rechenschieber
Chatten <Kommunikation>
Debugging
Mereologie
Server
Ultraviolett-Photoelektronenspektroskopie
Kantenfärbung
Message-Passing
Tabelle <Informatik>
Perpetuum mobile
Aggregatzustand
Offene Menge
Proxy Server
Web Site
Prozess <Physik>
Extrempunkt
Zahlenbereich
Kartesische Koordinaten
Fortsetzung <Mathematik>
Komplex <Algebra>
Datensicherung
Gerichteter Graph
Raum-Zeit
Computeranimation
Rechenzentrum
Freeware
Multiplikation
Knotenmenge
Prozess <Informatik>
Vorzeichen <Mathematik>
Abstand
E-Mail
Datei-Server
Minkowski-Metrik
Nichtlinearer Operator
Datennetz
Datenhaltung
Open Source
Kanalkapazität
Strömungsrichtung
Rechenschieber
Software
Twitter <Softwareplattform>
Menge
Ultraviolett-Photoelektronenspektroskopie
Aggregatzustand
Instantiierung
Fitnessfunktion

Metadaten

Formale Metadaten

Titel 1 M+ QPS on MySQL Galera cluster
Untertitel the table of Breaching 1 M+ queries per second
Alternativer Titel 600k QPS on MySQL Galera cluster
Serientitel FrOSCon 2015
Teil 29
Anzahl der Teile 80
Autor Heck, Walter
Lizenz CC-Namensnennung 3.0 Unported:
Sie dürfen das Werk bzw. den Inhalt zu jedem legalen Zweck nutzen, verändern und in unveränderter oder veränderter Form vervielfältigen, verbreiten und öffentlich zugänglich machen, sofern Sie den Namen des Autors/Rechteinhabers in der von ihm festgelegten Weise nennen.
DOI 10.5446/19623
Herausgeber Free and Open Source software Conference (FrOSCon) e.V.
Erscheinungsjahr 2015
Sprache Englisch

Inhaltliche Metadaten

Fachgebiet Informatik
Abstract The world of high availability mysql has seen several solutions over the last decade or so. All of them were flawed in some way, none were perfect. One of the new kids on the block is Galera cluster for MySQL. It's fully open source and scales surprisingly well. In this talk we'll look at a case study of how we deployed a 600k QPS cluster sucessfully

Ähnliche Filme

Loading...