Re: HA & Scalability w MySQL + SAN + VMWare: Architecture Suggestion Wanted
On 4/3/2012 3:04 AM, Ian wrote: > On 03/04/2012 00:47, Wes Modes wrote: >> Thanks again for sharing your knowledge. I do believe the answers I've >> receiving, but since I have requirements that I cannot easily alter, I'm >> also gently pushing my expert advisers here to look beyond their own >> preferences and direct experience. >> >> RE: Shared storage. I can easily let go of the preference to take >> advantage of shared storage. I understand duplicated databases are the >> essence of database redundancy. You make good points. >> >> In terms of the acceptability of a small fraction of users being >> temporarily unable to access services: rather than sharding, which >> again requires more control over the application than we have, I was >> more envisioning that would be the fraction of users who hit the one >> peer MySQL server that is temporarily unavailable due to h/w or s/w >> failure or DB corruption while its fail over is powered up. >> >> Does MySQL cluster seem like it will address my requirements to allow us >> to horizontally scale a number of MySQL nodes as peers without >> separating reads and writes, or slaves and masters. >> >> Wes > Hi Wes, > > If you can't alter the application to split reads and writes, why not > let MySQL Proxy to do it for you? > > http://forge.mysql.com/wiki/MySQL_Proxy > > Combine this with haproxy and you could build a multi-master environment > with each master having any number of slaves. Set MySQL Proxy to send > writes to the masters and reads to the slaves. > > Regards > > Ian Ian, what is the best place to ask specific questions about mysql-proxy? In general, here are my questions: 1. Does the proxy sit on a separate server and route all MySQL requests, or is it installed on each of the MySQL nodes and re-shuffle MySQL requests to the appropriate place? 2. Can multiple proxies be run in concert to provide redundancy and scalability as well as eliminate SPoF and bottlenecks? 3. In 2007 when RW Splitting was new, there were a few problems and limitations. What is the current status of development of this important feature? Thanks! And again I appreciate the brainstorming that many have done here to find a solution that fits my requirements. Wes -- Wes Modes Systems Designer, Developer, and Administrator University Library ITS University of California, Santa Cruz
Re: HA & Scalability w MySQL + SAN + VMWare: Architecture Suggestion Wanted
Am I right in seeing that if you can split reads and writes without the application having to be replication-aware, one does not need multiple masters? One can simply have standard MySQL replication, yes? For us, we only were interested in multiple masters so that we could read or write from any of the available MySQL nodes. W. On 4/3/2012 3:04 AM, Ian wrote: > On 03/04/2012 00:47, Wes Modes wrote: >> Thanks again for sharing your knowledge. I do believe the answers I've >> receiving, but since I have requirements that I cannot easily alter, I'm >> also gently pushing my expert advisers here to look beyond their own >> preferences and direct experience. >> >> RE: Shared storage. I can easily let go of the preference to take >> advantage of shared storage. I understand duplicated databases are the >> essence of database redundancy. You make good points. >> >> In terms of the acceptability of a small fraction of users being >> temporarily unable to access services: rather than sharding, which >> again requires more control over the application than we have, I was >> more envisioning that would be the fraction of users who hit the one >> peer MySQL server that is temporarily unavailable due to h/w or s/w >> failure or DB corruption while its fail over is powered up. >> >> Does MySQL cluster seem like it will address my requirements to allow us >> to horizontally scale a number of MySQL nodes as peers without >> separating reads and writes, or slaves and masters. >> >> Wes > Hi Wes, > > If you can't alter the application to split reads and writes, why not > let MySQL Proxy to do it for you? > > http://forge.mysql.com/wiki/MySQL_Proxy > > Combine this with haproxy and you could build a multi-master environment > with each master having any number of slaves. Set MySQL Proxy to send > writes to the masters and reads to the slaves. > > Regards > > Ian -- Wes Modes Systems Designer, Developer, and Administrator University Library ITS University of California, Santa Cruz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
JOIN giving duplicate records
Hi Before sending the table definition, and queries etc, can anyone advise why my query with four INNER JOIN might be give me back duplicate results e.g 100,UK,12121 100,UK,12121 Basically the query the statement AND (hotel_facilities.hotelfacilitytype_id = 47 OR hotel_facilities.hotelfacilitytype_id = 7) and if I add additional IDs like AND (hotel_facilities.hotelfacilitytype_id = 47 OR hotel_facilities.hotelfacilitytype_id = 7 OR hotel_facilities.hotelfacilitytype_id = 8), it would return three records like below when I'm expecting one record. 100,UK,12121 100,UK,12121 100,UK,12121 However in my SELECT statement if I use SELECT DISTINCT (field_name), I only get back one record. Thanks, Neil
Re: New Fast MySQL Compatible Server
On Tue, Apr 3, 2012 at 04:10, Johan De Meersman wrote: > - Original Message - >> From: "Singer X.J. Wang" >> >> Perhaps something called a VM can be use? > > Perhaps something called documentation can be provided? > > I also notice that the original poster seems to have vanished again, which > makes the whole thing even more shady. I see this morning that the "site" says, "Pre-release field evaluation will be conducted when licensing is arranged." The link to download the file is no longer there. -- Network Infrastructure Manager http://www.php.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: HA & Scalability w MySQL + SAN + VMWare: Architecture Suggestion Wanted
- Original Message - > From: "Ian" > > with each master having any number of slaves. Set MySQL Proxy to > send writes to the masters and reads to the slaves. Yes, except when you have replication delays (asynchronous, remember?) like someone else recently posted, your application writes a value, and consequently can't find it again. Boom goes the software :-) I'm sure proxy has it's uses, but I remain extremely sceptical about both performance and trustworthyness of the read/write splitting scheme as long as the application isn't aware of it. Now, if you could somehow cough up a *context*, say, by having proxy pass all requests from a connection that has done a write to the master, that would help, but... * it would yield less scaling - master must again serve some selects * with replication delays, you still risk updating the master based off old data from a not-up-to-date slave * in a web context, connections are not permanent; to keep track of a single stream you'd need the proxy to be aware of something like a browser session or whatnot No, if you *really* want to do horizontal scaling, have a look at SchoonerSQL, Clustrix and the like. They're third-party, but they (claim to) have proper multimaster replication. I've not actually looked at any of them in depth myself - we're in an environment where we have a lot of smaller clusters. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Percona: Contact Details - a word on poaching
- Original Message - > From: "Martin Gainty" That... made no sense whatsoever. Good thing I'm not Dutch, I suppose. Anyway, I invoke Godwin's Law. Thread ended. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: HA & Scalability w MySQL + SAN + VMWare: Architecture Suggestion Wanted
On 03/04/2012 00:47, Wes Modes wrote: > Thanks again for sharing your knowledge. I do believe the answers I've > receiving, but since I have requirements that I cannot easily alter, I'm > also gently pushing my expert advisers here to look beyond their own > preferences and direct experience. > > RE: Shared storage. I can easily let go of the preference to take > advantage of shared storage. I understand duplicated databases are the > essence of database redundancy. You make good points. > > In terms of the acceptability of a small fraction of users being > temporarily unable to access services: rather than sharding, which > again requires more control over the application than we have, I was > more envisioning that would be the fraction of users who hit the one > peer MySQL server that is temporarily unavailable due to h/w or s/w > failure or DB corruption while its fail over is powered up. > > Does MySQL cluster seem like it will address my requirements to allow us > to horizontally scale a number of MySQL nodes as peers without > separating reads and writes, or slaves and masters. > > Wes Hi Wes, If you can't alter the application to split reads and writes, why not let MySQL Proxy to do it for you? http://forge.mysql.com/wiki/MySQL_Proxy Combine this with haproxy and you could build a multi-master environment with each master having any number of slaves. Set MySQL Proxy to send writes to the masters and reads to the slaves. Regards Ian -- > On 4/2/2012 2:25 PM, shawn green wrote: >> Hello Wes, >> >> On 4/2/2012 4:05 PM, Wes Modes wrote: >>> Thanks Shawn and Karen, for the suggestions, even given my vague >>> requirements. >>> >>> To clarify some of my requirements. >>> >>> *Application: *We are using an open-source application called Omeka, >>> which is a "free, flexible, and open source web-publishing platform for >>> the display of library, museum, archives, and scholarly collections and >>> exhibitions." Without getting into how free (or scalable) free software >>> really is, we can view it as one aspect we cannot change, having been >>> written into the grant requirements we received for the project. >>> Experienced Omeka developers and our own developer have suggested >>> that/it is not feasible to separate database writes from reads in the >>> application/ (given time and resources). >>> >> >> That's a shame. Sounds like you are back to one big server or several >> smaller servers with in-program sharding. >> >>> *SAN: *The SAN is a Dell EqualLogic 6100 which has redundant everything, >>> including multiple NICs, controllers, and power. So we are less >>> concerned about the SAN being a SPoF. On the other hand, if we have a >>> single big MySQL server that fails, we could bring up another copy of it >>> via VMWare, but until the server came up, the application would be dead >>> in the water. If the database is corrupted, service will be interrupted >>> for a considerable time. >>> >> >> Again, each MySQL instance needs it's own copy of the data. Having >> only one big powerful disk system means that each instance you fire up >> must both share spindles and networking to access its data. Just like >> a freeway at rush hour, you may find the traffic into and out of this >> one device crawling to a halt exactly when you don't want it to. >> >>> *High Availability:* It sounds like there is some debate over how to >>> provide HA best, but do people really disagree on the desired results? >>> Without getting into the many meanings of this buzz word, here's what we >>> mean: /We desire to maintain high availability of service, allowing a >>> small fraction of users to experience outage for only seconds at a >>> time. We desire to provide this through horizontal scaling, redundancy, >>> failover planning, and external monitoring. / >>> >> >> "Small fraction of users" - this implies data sharding. Multiple MySQL >> instances each with enough data to operate independently for one slice >> of your most important data and an application smart enough to know >> which shard to go to for each slice of data. >> >> "For a few seconds at a time" - you do not want a shared disk. Should >> the active MySQL die, it's data will be in an inconsistent state. Once >> you fire up the passive daemon it will need to perform a recovery >> restart. This down time is more than likely not going to take only a >> few seconds. The more data you have, the longer the checks will take. >> An independent copy maintained by a slave instance, provides a >> logically consistent copy of the master's data as it will only >> replicate complete transactions. >> >> "horizontal scaling" - one master, multiple slaves. This requires the >> separation of writes and reads. >> >> >>> *Scalability: *Again, seems like there are lots of applications and >>> implementation, but people agree on the general concept. Here's what we >>> mean for this project: /We desire to scale our services so that a >>> usage surge does
RE: Percona: Contact Details - a word on poaching
Johann I would like to *think* the dutch would be agreeable to advice from German and or French supporters of the EU Occasionally I am contacted by someone in the Hague for Database Administrator Positions If you've been good about NOT doing strip searches on political enemies I can get you in the door On the other hand if you've been a full blown nazi with illegal wiretaps, strip searches and harassment you might wind up in the Hague for different circumstances. I am reminded of the platitude: United we stand..Divided we fall Bedankt, Martin Gainty __ Jogi és Bizalmassági kinyilatkoztatás/Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Ez az üzenet bizalmas. Ha nem ön az akinek szánva volt, akkor kérjük, hogy jelentse azt nekünk vissza. Semmiféle továbbítása vagy másolatának készítése nem megengedett. Ez az üzenet csak ismeret cserét szolgál és semmiféle jogi alkalmazhatósága sincs. Mivel az electronikus üzenetek könnyen megváltoztathatóak, ezért minket semmi felelöség nem terhelhet ezen üzenet tartalma miatt. Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. > Date: Tue, 3 Apr 2012 10:41:39 +0200 > From: vegiv...@tuxera.be > To: wmo...@ucsc.edu > CC: mysql@lists.mysql.com > Subject: Re: Percona: Contact Details - a word on poaching > > - Original Message - > > From: "Wes Modes" > > > > Hi, I received a suggestion from Baron Schwartz that I consider your > > company for consulting advice as a solution to an enquiry I made to > > the MySQL list. I did not respond to Baron Schwartz and now I receive > > this email from an account executive. I don not think I am alone is > > believing poaching the list is unethical. > > > > Please feel free to have your experts offer suggestions and advice, > > but having those experts directly passing contacts on to sales execs does > > not feel okay. Using the MySQL lists as a honey pot for sales > > opportunities is not why the list exist. > > > I'm slightly in two minds, here. On the one hand, yes, the list isn't made > for being poached. > > On the other hand, it is my experience that Percona is very liberal in > dispensing free - and good - advice here. Your recent enquiry is not an > obvious thing, however. Percona is still a business, and your problem is > unlikely to be solved just like that - it seems appropriate for them to offer > their services. The sales guy might've waited a bit longer to see if you > responded, but it still doesn't feel all that pushy, to me. > > You say you haven't answered, yet; so you also haven't quite told them that > you're /not/ in the market, either :-) I'm sure a simple "no, not right now" > is sufficient, if you're not interested. > > > -- > Bier met grenadyn > Is als mosterd by den wyn > Sy die't drinkt, is eene kwezel > Hy die't drinkt, is ras een ezel > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql >
Re: Percona: Contact Details - a word on poaching
- Original Message - > From: "Wes Modes" > > Hi, I received a suggestion from Baron Schwartz that I consider your > company for consulting advice as a solution to an enquiry I made to > the MySQL list. I did not respond to Baron Schwartz and now I receive > this email from an account executive. I don not think I am alone is > believing poaching the list is unethical. > > Please feel free to have your experts offer suggestions and advice, > but having those experts directly passing contacts on to sales execs does > not feel okay. Using the MySQL lists as a honey pot for sales > opportunities is not why the list exist. I'm slightly in two minds, here. On the one hand, yes, the list isn't made for being poached. On the other hand, it is my experience that Percona is very liberal in dispensing free - and good - advice here. Your recent enquiry is not an obvious thing, however. Percona is still a business, and your problem is unlikely to be solved just like that - it seems appropriate for them to offer their services. The sales guy might've waited a bit longer to see if you responded, but it still doesn't feel all that pushy, to me. You say you haven't answered, yet; so you also haven't quite told them that you're /not/ in the market, either :-) I'm sure a simple "no, not right now" is sufficient, if you're not interested. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: New Fast MySQL Compatible Server
- Original Message - > From: "Singer X.J. Wang" > > Perhaps something called a VM can be use? Perhaps something called documentation can be provided? I also notice that the original poster seems to have vanished again, which makes the whole thing even more shady. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
