Re: how to design mysql clusters with 30,000 clients?
On Fri, May 24, 2002 at 08:01:35PM +0200, Nicolas Bougues wrote: > On Fri, May 24, 2002 at 01:42:12PM -0400, Jeff S Wheeler wrote: > > > > While he may still need a large amount of DB muscle for other things, > > using PHP/MySQL sessions for a site that really expects to have 30,000 > > different HTTP clients at peak instants is not very bright. We have > > cookies for this. Server-side sessions are a great fallback for > > paranoid end-users who disable cookies in their browser, but it is my > > understanding that PHP relies on a cookie-based session ID anyway? > > What's not very bright is rather using MySQL in a somewhat audacious > configuration, for which support is quite recent (and thus, probably > not bugfree). In a high load / high availability environnement. What's the recent stuff you speak of? Replication has been in MySQL for about 1.5 years now. > An Oracle would probably be better here. At least, it has proven > replication mechanisms. I wonder what the license costs would be in that situation? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
Re: how to design mysql clusters with 30,000 clients?
On Fri, May 24, 2002 at 08:01:35PM +0200, Nicolas Bougues wrote: > On Fri, May 24, 2002 at 01:42:12PM -0400, Jeff S Wheeler wrote: > > > > While he may still need a large amount of DB muscle for other things, > > using PHP/MySQL sessions for a site that really expects to have 30,000 > > different HTTP clients at peak instants is not very bright. We have > > cookies for this. Server-side sessions are a great fallback for > > paranoid end-users who disable cookies in their browser, but it is my > > understanding that PHP relies on a cookie-based session ID anyway? > > What's not very bright is rather using MySQL in a somewhat audacious > configuration, for which support is quite recent (and thus, probably > not bugfree). In a high load / high availability environnement. What's the recent stuff you speak of? Replication has been in MySQL for about 1.5 years now. > An Oracle would probably be better here. At least, it has proven > replication mechanisms. I wonder what the license costs would be in that situation? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
Re: how to design mysql clusters with 30,000 clients?
Everything I've heard about experiences with mysql on NFS has been negative. If you do want to try it, though, keep in mind that 100Mbit/sec ethernet is going to give you 12.5MByte/sec, less actually, of I/O performance. GIGE cards are cheap these days, as are switches with a few GIGE ports. 1000baseT works, take advantage of it. I hope you'll think about a solution other than mysql for this problem, though. It's not the right tool for session management on such a scale. -- Jeff S Wheeler [EMAIL PROTECTED] Software DevelopmentFive Elements, Inc http://www.five-elements.com/~jsw/ On Mon, 2002-05-27 at 07:54, Patrick Hsieh wrote: > Hello Nicolas Bougues <[EMAIL PROTECTED]>, > > I'd like to discuss the NFS server in this network scenario. > Say, if I put a linux-based NFS server as the central storage device and > make all web servers as well as the single mysql write server attached > over the 100Base ethernet. When encountering 30,000 concurrent clients, > will the NFS server be the bottleneck? > > I am thinking about to put a NetApp filer as the NFS server or build a > linux-based one myself. Can anyone give me some advice? > > If I put the raw data of MySQL write server in the NetApp filer, if the > database crashes, I can hopefully recover the latest snapshot backup > from the NetApp filer in a very short time. However, if I put on the > local disk array(raid 5) or linux-based NFS server with raid 5 disk > array attached, I wonder whether it will be my bottleneck or not. > > How does mysql support the NFS server? Is it wise to put mysql raw data > in the NFS? > > > -- > Patrick Hsieh <[EMAIL PROTECTED]> > GPG public key http://pahud.net/pubkeys/pahudatpahud.gpg > > > -- > To UNSUBSCRIBE, email to [EMAIL PROTECTED] > with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED] > -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
Re: how to design mysql clusters with 30,000 clients?
Everything I've heard about experiences with mysql on NFS has been negative. If you do want to try it, though, keep in mind that 100Mbit/sec ethernet is going to give you 12.5MByte/sec, less actually, of I/O performance. GIGE cards are cheap these days, as are switches with a few GIGE ports. 1000baseT works, take advantage of it. I hope you'll think about a solution other than mysql for this problem, though. It's not the right tool for session management on such a scale. -- Jeff S Wheeler [EMAIL PROTECTED] Software DevelopmentFive Elements, Inc http://www.five-elements.com/~jsw/ On Mon, 2002-05-27 at 07:54, Patrick Hsieh wrote: > Hello Nicolas Bougues <[EMAIL PROTECTED]>, > > I'd like to discuss the NFS server in this network scenario. > Say, if I put a linux-based NFS server as the central storage device and > make all web servers as well as the single mysql write server attached > over the 100Base ethernet. When encountering 30,000 concurrent clients, > will the NFS server be the bottleneck? > > I am thinking about to put a NetApp filer as the NFS server or build a > linux-based one myself. Can anyone give me some advice? > > If I put the raw data of MySQL write server in the NetApp filer, if the > database crashes, I can hopefully recover the latest snapshot backup > from the NetApp filer in a very short time. However, if I put on the > local disk array(raid 5) or linux-based NFS server with raid 5 disk > array attached, I wonder whether it will be my bottleneck or not. > > How does mysql support the NFS server? Is it wise to put mysql raw data > in the NFS? > > > -- > Patrick Hsieh <[EMAIL PROTECTED]> > GPG public key http://pahud.net/pubkeys/pahudatpahud.gpg > > > -- > To UNSUBSCRIBE, email to [EMAIL PROTECTED] > with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED] > -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
Re: how to design mysql clusters with 30,000 clients?
Hello I have similar scenario of mysql, but i can't put mysql data on NFS, because there have some locks and cache problems. My NFS is NetworkAppliance too, and my mysql have very hard traffic. I'm studing the posibility to put on SAN with fiberchannel cards. We will buy specefic software (in develop now) for clustering mysql. > I'd like to discuss the NFS server in this network scenario. > Say, if I put a linux-based NFS server as the central storage device and > make all web servers as well as the single mysql write server attached > over the 100Base ethernet. When encountering 30,000 concurrent clients, > will the NFS server be the bottleneck? -- A. Ramos: [EMAIL PROTECTED] Admin Sistemas, PrisaCOM Telf: 91.353.7930 Edificio Apot, 5a planta 28042 Madrid. -- -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
Re: how to design mysql clusters with 30,000 clients?
Hello Nicolas Bougues <[EMAIL PROTECTED]>, I'd like to discuss the NFS server in this network scenario. Say, if I put a linux-based NFS server as the central storage device and make all web servers as well as the single mysql write server attached over the 100Base ethernet. When encountering 30,000 concurrent clients, will the NFS server be the bottleneck? I am thinking about to put a NetApp filer as the NFS server or build a linux-based one myself. Can anyone give me some advice? If I put the raw data of MySQL write server in the NetApp filer, if the database crashes, I can hopefully recover the latest snapshot backup from the NetApp filer in a very short time. However, if I put on the local disk array(raid 5) or linux-based NFS server with raid 5 disk array attached, I wonder whether it will be my bottleneck or not. How does mysql support the NFS server? Is it wise to put mysql raw data in the NFS? -- Patrick Hsieh <[EMAIL PROTECTED]> GPG public key http://pahud.net/pubkeys/pahudatpahud.gpg -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
Re: how to design mysql clusters with 30,000 clients?
Hello I have similar scenario of mysql, but i can't put mysql data on NFS, because there have some locks and cache problems. My NFS is NetworkAppliance too, and my mysql have very hard traffic. I'm studing the posibility to put on SAN with fiberchannel cards. We will buy specefic software (in develop now) for clustering mysql. > I'd like to discuss the NFS server in this network scenario. > Say, if I put a linux-based NFS server as the central storage device and > make all web servers as well as the single mysql write server attached > over the 100Base ethernet. When encountering 30,000 concurrent clients, > will the NFS server be the bottleneck? -- A. Ramos: [EMAIL PROTECTED] Admin Sistemas, PrisaCOM Telf: 91.353.7930 Edificio Apot, 5a planta 28042 Madrid. -- -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
Re: how to design mysql clusters with 30,000 clients?
Hello Nicolas Bougues <[EMAIL PROTECTED]>, I'd like to discuss the NFS server in this network scenario. Say, if I put a linux-based NFS server as the central storage device and make all web servers as well as the single mysql write server attached over the 100Base ethernet. When encountering 30,000 concurrent clients, will the NFS server be the bottleneck? I am thinking about to put a NetApp filer as the NFS server or build a linux-based one myself. Can anyone give me some advice? If I put the raw data of MySQL write server in the NetApp filer, if the database crashes, I can hopefully recover the latest snapshot backup from the NetApp filer in a very short time. However, if I put on the local disk array(raid 5) or linux-based NFS server with raid 5 disk array attached, I wonder whether it will be my bottleneck or not. How does mysql support the NFS server? Is it wise to put mysql raw data in the NFS? -- Patrick Hsieh <[EMAIL PROTECTED]> GPG public key http://pahud.net/pubkeys/pahudatpahud.gpg -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
Re: how to design mysql clusters with 30,000 clients?
On Fri, May 24, 2002 at 01:42:12PM -0400, Jeff S Wheeler wrote: > > While he may still need a large amount of DB muscle for other things, > using PHP/MySQL sessions for a site that really expects to have 30,000 > different HTTP clients at peak instants is not very bright. We have > cookies for this. Server-side sessions are a great fallback for > paranoid end-users who disable cookies in their browser, but it is my > understanding that PHP relies on a cookie-based session ID anyway? > What's not very bright is rather using MySQL in a somewhat audacious configuration, for which support is quite recent (and thus, probably not bugfree). In a high load / high availability environnement. An Oracle would probably be better here. At least, it has proven replication mechanisms. Cookie based *whatever* is generally not a good idea. PHP sessions can be handled using cookies or URL signatures. And BTW, they do not necessarily require database backend. They can be handled on the filesystem, though I'm not sure whether it works well in a shared NFS environnement. Note: I never implemented something like this. These are juste ideas. -- Nicolas Bougues Axialys Interactive -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
Re: how to design mysql clusters with 30,000 clients?
I don't know if anyone else who followed-up on this thread has ever implemented a high traffic web site of this calibre, but the original poster is really just trying to band-aid a poor session management mechanism into working for traffic levels it wasn't really intended for. While he may still need a large amount of DB muscle for other things, using PHP/MySQL sessions for a site that really expects to have 30,000 different HTTP clients at peak instants is not very bright. We have cookies for this. Server-side sessions are a great fallback for paranoid end-users who disable cookies in their browser, but it is my understanding that PHP relies on a cookie-based session ID anyway? I tried to follow up with the original poster directly but I can't deliver mail to his MX for some reason. *shrug* Look into signed cookies for authen/authz/session, using a shared secret known by all your web servers. This is not a new concept, nor a difficult one. It can even be implemented using PHP, though a C apache module is smarter. -- Jeff S Wheeler [EMAIL PROTECTED] Software DevelopmentFive Elements, Inc http://www.five-elements.com/~jsw/ -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
Re: how to design mysql clusters with 30,000 clients?
On Fri, May 24, 2002 at 01:42:12PM -0400, Jeff S Wheeler wrote: > > While he may still need a large amount of DB muscle for other things, > using PHP/MySQL sessions for a site that really expects to have 30,000 > different HTTP clients at peak instants is not very bright. We have > cookies for this. Server-side sessions are a great fallback for > paranoid end-users who disable cookies in their browser, but it is my > understanding that PHP relies on a cookie-based session ID anyway? > What's not very bright is rather using MySQL in a somewhat audacious configuration, for which support is quite recent (and thus, probably not bugfree). In a high load / high availability environnement. An Oracle would probably be better here. At least, it has proven replication mechanisms. Cookie based *whatever* is generally not a good idea. PHP sessions can be handled using cookies or URL signatures. And BTW, they do not necessarily require database backend. They can be handled on the filesystem, though I'm not sure whether it works well in a shared NFS environnement. Note: I never implemented something like this. These are juste ideas. -- Nicolas Bougues Axialys Interactive -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
Re: how to design mysql clusters with 30,000 clients?
I don't know if anyone else who followed-up on this thread has ever implemented a high traffic web site of this calibre, but the original poster is really just trying to band-aid a poor session management mechanism into working for traffic levels it wasn't really intended for. While he may still need a large amount of DB muscle for other things, using PHP/MySQL sessions for a site that really expects to have 30,000 different HTTP clients at peak instants is not very bright. We have cookies for this. Server-side sessions are a great fallback for paranoid end-users who disable cookies in their browser, but it is my understanding that PHP relies on a cookie-based session ID anyway? I tried to follow up with the original poster directly but I can't deliver mail to his MX for some reason. *shrug* Look into signed cookies for authen/authz/session, using a shared secret known by all your web servers. This is not a new concept, nor a difficult one. It can even be implemented using PHP, though a C apache module is smarter. -- Jeff S Wheeler [EMAIL PROTECTED] Software DevelopmentFive Elements, Inc http://www.five-elements.com/~jsw/ -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
Re: how to design mysql clusters with 30,000 clients?
> On Wednesday 22 May 2002 18:44, Dave Watkins wrote: > > At 16:02 22/05/2002 +0800, Patrick Hsieh wrote: > > >Hello list, > > > > > >I am expecting to have 30,000 http clients visting my website at the > > >same time. To meet the HA requirement, we use dual firewall, dual > > >Layer-4 switch and multiple web servers in the backend. My problem is, > > >if we use the user-tracking system with apache, php and mysql, it will > > >surely brings a huge amount of database traffic. How can I balance mysql > > >load among multiple mysql server yet assure the data consistency among > > >them? My idea is: > > > > I suggest that you do use clustering. However, you are better off running the Database in a fail over manner as opposed to load balancing multiple db machines. However, a bit of load balancing can be achieved if all reads are done from replicated machines and all writes are done to the master. Just as the manual suggests. For the rest of it (not including the firewalls and switch) you could use a product like TurboCluster 6. It will load balance and monitor the health of every machine in the cluster, including it's software, and in the event a node goes down, the action taken can be customized. The machine that manages the cluster can also run with a backup of it's own that in the event of failure would take over witout admin intervention. It can also track returning users and route them to a server they previously visited. You can understand why this is good. It doesn't care about what OS is running on any of the machines in the backend and the load balancing can even run "Weighted round robin" if you using a heterogenous topography. When designing our setup, I pushed hard to make sure that we used the same hardware and configuration in each machine as much as was possilbe. That certainly eases tuning issues. Anyway, I would check this out. The price is 2k$ for a ten node cluster and two traffic mangers. Nothing else is going to even close. Later on, TRC -- ___ Download the free Opera browser at http://www.opera.com/ Powered by Outblaze -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
Re: how to design mysql clusters with 30,000 clients?
> On Wednesday 22 May 2002 18:44, Dave Watkins wrote: > > At 16:02 22/05/2002 +0800, Patrick Hsieh wrote: > > >Hello list, > > > > > >I am expecting to have 30,000 http clients visting my website at the > > >same time. To meet the HA requirement, we use dual firewall, dual > > >Layer-4 switch and multiple web servers in the backend. My problem is, > > >if we use the user-tracking system with apache, php and mysql, it will > > >surely brings a huge amount of database traffic. How can I balance mysql > > >load among multiple mysql server yet assure the data consistency among > > >them? My idea is: > > > > I suggest that you do use clustering. However, you are better off running the Database in a fail over manner as opposed to load balancing multiple db machines. However, a bit of load balancing can be achieved if all reads are done from replicated machines and all writes are done to the master. Just as the manual suggests. For the rest of it (not including the firewalls and switch) you could use a product like TurboCluster 6. It will load balance and monitor the health of every machine in the cluster, including it's software, and in the event a node goes down, the action taken can be customized. The machine that manages the cluster can also run with a backup of it's own that in the event of failure would take over witout admin intervention. It can also track returning users and route them to a server they previously visited. You can understand why this is good. It doesn't care about what OS is running on any of the machines in the backend and the load balancing can even run "Weighted round robin" if you using a heterogenous topography. When designing our setup, I pushed hard to make sure that we used the same hardware and configuration in each machine as much as was possilbe. That certainly eases tuning issues. Anyway, I would check this out. The price is 2k$ for a ten node cluster and two traffic mangers. Nothing else is going to even close. Later on, TRC -- ___ Download the free Opera browser at http://www.opera.com/ Powered by Outblaze -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
Re: how to design mysql clusters with 30,000 clients?
Ideas? Noone mentioned it before, but if you may choose different database, ie postreSQL than there is a debian packaged program called dbbalancer (at least in woody). But as the authors states it works for now only with postgres. http://dbbalancer.sourceforge.net JA -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
Re: how to design mysql clusters with 30,000 clients?
> Ideas? Noone mentioned it before, but if you may choose different database, ie postreSQL than there is a debian packaged program called dbbalancer (at least in woody). But as the authors states it works for now only with postgres. http://dbbalancer.sourceforge.net JA -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
Re: how to design mysql clusters with 30,000 clients?
On Wednesday 22 May 2002 18:44, Dave Watkins wrote: > At 16:02 22/05/2002 +0800, Patrick Hsieh wrote: > >Hello list, > > > >I am expecting to have 30,000 http clients visting my website at the > >same time. To meet the HA requirement, we use dual firewall, dual > >Layer-4 switch and multiple web servers in the backend. My problem is, > >if we use the user-tracking system with apache, php and mysql, it will > >surely brings a huge amount of database traffic. How can I balance mysql > >load among multiple mysql server yet assure the data consistency among > >them? My idea is: > > Actually you don't need to do clustering at all, at least not for the systems doing the actual id tracking. Use Apache's mod_unique (which will generate unique ids even across a cluster of web servers with no need for them the communicate) and then each web server can have its own user tracking database, there will be no danger of id collisions. If you need to do actual session management where you would have any of the pool of servers needing to recover per-session data, then you're stuck back where you were before, though at least you have globally unique ids now. One way to deal with that issue is to provide some form of session affinity, so once a given user session hits one particular apache, it always gets directed there. Layer 4 switching may allow you to do that. Another idea to throw into the mix is reverse proxying. Most likely you have a mix of dynamic and static content, and the static stuff really could care less where it gets served from, so set up your cluster so that each system has a stripped Apache running on port 80 which can serve static content and reverse proxy dynamic content handling off to a 2nd instance. Then you can write your proxy rewrite rules such that they take into account the user's session state, and you end up with effectively session affinity at the dynamic server, which is really the only place it would matter. Then you have no need for clustering at all, you can just replicate for reliability purposes and munge your data together from each server when you need to do reports. -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
Re: how to design mysql clusters with 30,000 clients?
Hi. On Thu, May 23, 2002 at 11:16:33PM +0800, [EMAIL PROTECTED] wrote: > Hello Benjamin Pflugmann <[EMAIL PROTECTED]>, > > This scenario is fine. But in real life, the circular master-slave > replication will probably cause inconsistency of data among them. That is why I wrote you have to take care of the special properties (e.g. unique keys will not assure uniqueness among all servers). If you take care of such things, there should be no problem with data consistency. > I wish to keep 1 copy of the shared raw data in a storage device and > forget circular master-slave replication. If there is no locking > problem in this scenario, then I can balance the > insert/delete/update load onto every mysql server attached on the > shared storage device. Idea? No comment, because I have no experience with that. Bye, Benjamin. > On Thu, 23 May 2002 16:19:53 +0200 > Benjamin Pflugmann <[EMAIL PROTECTED]> wrote: [...] > > I beg to differ. This kind of setting is doable since 3.23.26 and even > > mentioned in the manual as circular master-slave relationship: > > > > http://www.mysql.com/doc/R/e/Replication_Features.html > > > > Of course you have to take care of the special properties of this > > configuration. -- [EMAIL PROTECTED] -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
Re: how to design mysql clusters with 30,000 clients?
On Wednesday 22 May 2002 18:44, Dave Watkins wrote: > At 16:02 22/05/2002 +0800, Patrick Hsieh wrote: > >Hello list, > > > >I am expecting to have 30,000 http clients visting my website at the > >same time. To meet the HA requirement, we use dual firewall, dual > >Layer-4 switch and multiple web servers in the backend. My problem is, > >if we use the user-tracking system with apache, php and mysql, it will > >surely brings a huge amount of database traffic. How can I balance mysql > >load among multiple mysql server yet assure the data consistency among > >them? My idea is: > > Actually you don't need to do clustering at all, at least not for the systems doing the actual id tracking. Use Apache's mod_unique (which will generate unique ids even across a cluster of web servers with no need for them the communicate) and then each web server can have its own user tracking database, there will be no danger of id collisions. If you need to do actual session management where you would have any of the pool of servers needing to recover per-session data, then you're stuck back where you were before, though at least you have globally unique ids now. One way to deal with that issue is to provide some form of session affinity, so once a given user session hits one particular apache, it always gets directed there. Layer 4 switching may allow you to do that. Another idea to throw into the mix is reverse proxying. Most likely you have a mix of dynamic and static content, and the static stuff really could care less where it gets served from, so set up your cluster so that each system has a stripped Apache running on port 80 which can serve static content and reverse proxy dynamic content handling off to a 2nd instance. Then you can write your proxy rewrite rules such that they take into account the user's session state, and you end up with effectively session affinity at the dynamic server, which is really the only place it would matter. Then you have no need for clustering at all, you can just replicate for reliability purposes and munge your data together from each server when you need to do reports. -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
Re: how to design mysql clusters with 30,000 clients?
Hello Benjamin Pflugmann <[EMAIL PROTECTED]>, This scenario is fine. But in real life, the circular master-slave replication will probably cause inconsistency of data among them. I wish to keep 1 copy of the shared raw data in a storage device and forget circular master-slave replication. If there is no locking problem in this scenario, then I can balance the insert/delete/update load onto every mysql server attached on the shared storage device. Idea? On Thu, 23 May 2002 16:19:53 +0200 Benjamin Pflugmann <[EMAIL PROTECTED]> wrote: > Hi. > > On Thu, May 23, 2002 at 10:44:15AM +1200, [EMAIL PROTECTED] wrote: > > At 16:02 22/05/2002 +0800, Patrick Hsieh wrote: > [...] > > >1. use 3 or more mysql servers for write/update and more than 5 mysql > > >servers for read-only. Native mysql replication is applied among them. > > >In the mysql write servers, use 1 way replication like A->B->C->A to > > >keep the data consistency. But I am afraid the loss of data, since we > > >can't take the risk on it, especially when we are relying our billing > > >system on it. > > > > This will not work. MySQL replication does not work like that. With MySQL > > replication you have one master and all others replicate from it. > [...] > > I beg to differ. This kind of setting is doable since 3.23.26 and even > mentioned in the manual as circular master-slave relationship: > > http://www.mysql.com/doc/R/e/Replication_Features.html > > Of course you have to take care of the special properties of this > configuration. > > Regards, > > Benjamin. > > -- > [EMAIL PROTECTED] > > > -- > To UNSUBSCRIBE, email to [EMAIL PROTECTED] > with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED] -- Patrick Hsieh <[EMAIL PROTECTED]> GPG public key http://pahud.net/pubkeys/pahudatpahud.gpg -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
Re: how to design mysql clusters with 30,000 clients?
Hi. On Thu, May 23, 2002 at 11:16:33PM +0800, [EMAIL PROTECTED] wrote: > Hello Benjamin Pflugmann <[EMAIL PROTECTED]>, > > This scenario is fine. But in real life, the circular master-slave > replication will probably cause inconsistency of data among them. That is why I wrote you have to take care of the special properties (e.g. unique keys will not assure uniqueness among all servers). If you take care of such things, there should be no problem with data consistency. > I wish to keep 1 copy of the shared raw data in a storage device and > forget circular master-slave replication. If there is no locking > problem in this scenario, then I can balance the > insert/delete/update load onto every mysql server attached on the > shared storage device. Idea? No comment, because I have no experience with that. Bye, Benjamin. > On Thu, 23 May 2002 16:19:53 +0200 > Benjamin Pflugmann <[EMAIL PROTECTED]> wrote: [...] > > I beg to differ. This kind of setting is doable since 3.23.26 and even > > mentioned in the manual as circular master-slave relationship: > > > > http://www.mysql.com/doc/R/e/Replication_Features.html > > > > Of course you have to take care of the special properties of this > > configuration. -- [EMAIL PROTECTED] -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
Re: how to design mysql clusters with 30,000 clients?
Hi. On Thu, May 23, 2002 at 10:44:15AM +1200, [EMAIL PROTECTED] wrote: > At 16:02 22/05/2002 +0800, Patrick Hsieh wrote: [...] > >1. use 3 or more mysql servers for write/update and more than 5 mysql > >servers for read-only. Native mysql replication is applied among them. > >In the mysql write servers, use 1 way replication like A->B->C->A to > >keep the data consistency. But I am afraid the loss of data, since we > >can't take the risk on it, especially when we are relying our billing > >system on it. > > This will not work. MySQL replication does not work like that. With MySQL > replication you have one master and all others replicate from it. [...] I beg to differ. This kind of setting is doable since 3.23.26 and even mentioned in the manual as circular master-slave relationship: http://www.mysql.com/doc/R/e/Replication_Features.html Of course you have to take care of the special properties of this configuration. Regards, Benjamin. -- [EMAIL PROTECTED] -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
Re: how to design mysql clusters with 30,000 clients?
Hello Benjamin Pflugmann <[EMAIL PROTECTED]>, This scenario is fine. But in real life, the circular master-slave replication will probably cause inconsistency of data among them. I wish to keep 1 copy of the shared raw data in a storage device and forget circular master-slave replication. If there is no locking problem in this scenario, then I can balance the insert/delete/update load onto every mysql server attached on the shared storage device. Idea? On Thu, 23 May 2002 16:19:53 +0200 Benjamin Pflugmann <[EMAIL PROTECTED]> wrote: > Hi. > > On Thu, May 23, 2002 at 10:44:15AM +1200, [EMAIL PROTECTED] wrote: > > At 16:02 22/05/2002 +0800, Patrick Hsieh wrote: > [...] > > >1. use 3 or more mysql servers for write/update and more than 5 mysql > > >servers for read-only. Native mysql replication is applied among them. > > >In the mysql write servers, use 1 way replication like A->B->C->A to > > >keep the data consistency. But I am afraid the loss of data, since we > > >can't take the risk on it, especially when we are relying our billing > > >system on it. > > > > This will not work. MySQL replication does not work like that. With MySQL > > replication you have one master and all others replicate from it. > [...] > > I beg to differ. This kind of setting is doable since 3.23.26 and even > mentioned in the manual as circular master-slave relationship: > > http://www.mysql.com/doc/R/e/Replication_Features.html > > Of course you have to take care of the special properties of this > configuration. > > Regards, > > Benjamin. > > -- > [EMAIL PROTECTED] > > > -- > To UNSUBSCRIBE, email to [EMAIL PROTECTED] > with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED] -- Patrick Hsieh <[EMAIL PROTECTED]> GPG public key http://pahud.net/pubkeys/pahudatpahud.gpg -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
Re: how to design mysql clusters with 30,000 clients?
Hi. On Thu, May 23, 2002 at 10:44:15AM +1200, [EMAIL PROTECTED] wrote: > At 16:02 22/05/2002 +0800, Patrick Hsieh wrote: [...] > >1. use 3 or more mysql servers for write/update and more than 5 mysql > >servers for read-only. Native mysql replication is applied among them. > >In the mysql write servers, use 1 way replication like A->B->C->A to > >keep the data consistency. But I am afraid the loss of data, since we > >can't take the risk on it, especially when we are relying our billing > >system on it. > > This will not work. MySQL replication does not work like that. With MySQL > replication you have one master and all others replicate from it. [...] I beg to differ. This kind of setting is doable since 3.23.26 and even mentioned in the manual as circular master-slave relationship: http://www.mysql.com/doc/R/e/Replication_Features.html Of course you have to take care of the special properties of this configuration. Regards, Benjamin. -- [EMAIL PROTECTED] -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
Re: how to design mysql clusters with 30,000 clients?
At 16:02 22/05/2002 +0800, Patrick Hsieh wrote: Hello list, I am expecting to have 30,000 http clients visting my website at the same time. To meet the HA requirement, we use dual firewall, dual Layer-4 switch and multiple web servers in the backend. My problem is, if we use the user-tracking system with apache, php and mysql, it will surely brings a huge amount of database traffic. How can I balance mysql load among multiple mysql server yet assure the data consistency among them? My idea is: 1. use 3 or more mysql servers for write/update and more than 5 mysql servers for read-only. Native mysql replication is applied among them. In the mysql write servers, use 1 way replication like A->B->C->A to keep the data consistency. But I am afraid the loss of data, since we can't take the risk on it, especially when we are relying our billing system on it. This will not work. MySQL replication does not work like that. With MySQL replication you have one master and all others replicate from it. It is also the only server that can write to the DB. Your options for following this route would be to either use the experimantal 2 way replication support in the beta of MySQL4. Or use a different DB Dave -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
Re: how to design mysql clusters with 30,000 clients?
At 16:02 22/05/2002 +0800, Patrick Hsieh wrote: >Hello list, > >I am expecting to have 30,000 http clients visting my website at the >same time. To meet the HA requirement, we use dual firewall, dual >Layer-4 switch and multiple web servers in the backend. My problem is, >if we use the user-tracking system with apache, php and mysql, it will >surely brings a huge amount of database traffic. How can I balance mysql >load among multiple mysql server yet assure the data consistency among >them? My idea is: > >1. use 3 or more mysql servers for write/update and more than 5 mysql >servers for read-only. Native mysql replication is applied among them. >In the mysql write servers, use 1 way replication like A->B->C->A to >keep the data consistency. But I am afraid the loss of data, since we >can't take the risk on it, especially when we are relying our billing >system on it. This will not work. MySQL replication does not work like that. With MySQL replication you have one master and all others replicate from it. It is also the only server that can write to the DB. Your options for following this route would be to either use the experimantal 2 way replication support in the beta of MySQL4. Or use a different DB Dave -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
