Can anyone tell me what the scoop is on this? I read that CentOS was releasing the enterprise/cluster capable MySQL in the CentOS-Plus repo's but I don't see it there. I don't want to use the mysql.com packages (if they are even available -- didn't they stop supplying the binaries for this to the public)?
I'm trying to setup failover, load balancing clustering of MySQL.I'm a bit confused on what the current story is for doing this.
Thanks, James
jchase wrote:
Can anyone tell me what the scoop is on this? I read that CentOS was releasing the enterprise/cluster capable MySQL in the CentOS-Plus repo's but I don't see it there. I don't want to use the mysql.com packages (if they are even available -- didn't they stop supplying the binaries for this to the public)?
I'm trying to setup failover, load balancing clustering of MySQL.I'm a bit confused on what the current story is for doing this.
Last I recall MySQL max wasn't really mysql but based on some other DB(SAP DB?).. Mysql by itself has built in "clustering" though there can be significant limitations in it depending on your requirements.
http://www.mysql.com/downloads/cluster/
nate
Mysql by itself has built in "clustering" though there can be significant limitations in it depending on your requirements.
I agree. The built in cluster has too many limitations to be useful, but MySQL master-master replication gives a very good alternative to a true cluster. We use it to deploy geographically redundant systems and it has worked very well for us.
Neil
-- Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net/mysql MySQL pre-installed on a virtual private server for $25/mo Unmetered bandwidth = no overage charges, 7 day free trial
Neil Aggarwal wrote:
I agree. The built in cluster has too many limitations to be useful, but MySQL master-master replication gives a very good alternative to a true cluster. We use it to deploy geographically redundant systems and it has worked very well for us.
master-master replication has all kinds of inherent issues if you're concerned with data and transactional integrity. its pretty hard to uncommitted a transaction some time after its been committed.
if its done fully safely, its incredibly slow, as you need global locks. otherwise, you're playing russian roulette and relying on your data access patterns to inherently avoid conflicts. if your problem space can be partitioned geographically, then its doable.
John:
master-master replication has all kinds of inherent issues if you're concerned with data and transactional integrity.
We have evaluated the problems and think we have found adequate workarounds for them. The sites we deploy are e-commerce sites so: 1. There are a lot more reads than writes. 2. When we need to write to the database, it is saving info for an order. All orders are independent of each other so there is no conflict for those. 3. Product updates, fulfillments, etc. happen once daily at night when the traffic is almost nonexistant. The updates are sent to only one server so there is no conflict there.
The only potential place a conflict may occur is in the qty available for a specific product. The inventory system updates the inventory regularly so even if the number is wrong, it gets refreshed shortly thereafter.
We even built an application layer on top of master-master replication to handle cases where a transaction fails.
We are using this system for several large clients and it is working well. Being able to have geographical redundancy at a reasonable cost (A true cluster would be very high cost) outweighs the limitations.
Thanks, Neil
-- Neil Aggarwal, (281)846-8957 MySQL pre-installed on a virtual private server for $25/mo Unmetered bandwidth = no overage charges, 7 day free trial
2010/3/17 Neil Aggarwal neil@jammconsulting.com:
The only potential place a conflict may occur is in the qty available for a specific product. The inventory system updates the inventory regularly so even if the number is wrong, it gets refreshed shortly thereafter.
Do you mean that a separate job, iterates the orders, accumulates the real ordered quantity and subtracts it from some "initial quantity" in order to produce available quantity?
What do you do in cases where you have oversold a product. I mean when the "ordered quantity" got bigger than the "available quantity" due to a conflict in available quantity field? I assume that the system sends an email to the warehouse to increase additionally the quantity of that product?
We even built an application layer on top of master-master replication to handle cases where a transaction fails.
Could you describe a case where a transaction has failed , and how you deal with it?
Thank you!
Alex
On Wed, 2010-03-17 at 13:29 +0200, Alexander Georgiev wrote:
2010/3/17 Neil Aggarwal neil@jammconsulting.com:
The only potential place a conflict may occur is in the qty available for a specific product. The inventory system updates the inventory regularly so even if the number is wrong, it gets refreshed shortly thereafter.
Do you mean that a separate job, iterates the orders, accumulates the real ordered quantity and subtracts it from some "initial quantity" in order to produce available quantity?
What do you do in cases where you have oversold a product. I mean when the "ordered quantity" got bigger than the "available quantity" due to a conflict in available quantity field? I assume that the system sends an email to the warehouse to increase additionally the quantity of that product?
We even built an application layer on top of master-master replication to handle cases where a transaction fails.
Could you describe a case where a transaction has failed , and how you deal with it?
Yes im interested to hear that also because im not aware that MySQL has any type of feature like: database.rollback() or table and collumn rollback on a bad transaction. I have always heard the replication of MySQL could not keep up with lots of writes.
Have you thought of separating the databases? One for the reads and one for the write on different raids? Despite what some may believe this can be done.
John
JohnS wrote:
I have always heard the replication of MySQL could not keep up with lots of writes.
I don't think MySQL replication has an issue with number of writes, at least with regular replication(can't speak to multi master stuff), all replication is is the DB sending the raw query to the other system to execute, so provided the other system(s) your replicating to are at least as fast as the system doing the real work the others should have no trouble keeping up.
nate
Nate:
I don't think MySQL replication has an issue with number of writes,
That has been our experience as well.
There are a couple of things to ensure: 1. The databases have to be sized such that they can handle all transactions occurring on the entire system, not just one side. 2. The network between them has to be high quality. We use premium bandwidth and keep our replicated servers in data centers. No back of the office connected via DSL/cable stuff.
As long as that is the case, replication has no problem keeping up and the transactions are sent across the network very quickly. It is not real time but close enough for our needs.
Neil
-- Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net/centos Virtual private server with CentOS 5.4 preinstalled for $25/month! Unmetered bandwidth = no overage charges, 7 day free trial
John:
Have you thought of separating the databases? One for the reads and one for the write on different raids? Despite what some may believe this can be done.
Our goal is to create redundancy. We want either system to be able to work if the other is not available. Designating one database as a write db and the other as a read defeats that.
Thanks, Neil
-- Neil Aggarwal, (281)846-8957 FREE trial: cPanel VPS with unmetered bandwidth http://UnmeteredVPS.net/cpanel
Neil Aggarwal wrote:
Our goal is to create redundancy. We want either system to be able to work if the other is not available. Designating one database as a write db and the other as a read defeats that.
Depending on the requirements splitting out can greatly improve scalability though, potentially using something like mysql proxy and perhaps even a load balancer.
The write systems can still be clustered/multi-master replication but if the bulk of your work is reads then load balancing many independent databases for reads can improve performance and even improve availability.
But it really depends on the workload.
nate
On Wed, 2010-03-17 at 11:10 -0500, Neil Aggarwal wrote:
John:
Have you thought of separating the databases? One for the reads and one for the write on different raids? Despite what some may believe this can be done.
Our goal is to create redundancy. We want either system to be able to work if the other is not available. Designating one database as a write db and the other as a read defeats that.
No it does not. You can have what ever you want: and it will work with brilliance. Master/Writer > Replicate/GEO/Writer Master/Reader > Replicate/GEO/Reader
The Master writer can be on server1 the master reader server 2 then do your replication. You just have to take account for this in the design of you code connection and query wise. Ohh a heads up most python database modules can support this way of connecting. IE They are not connect aware to more than one connection. Meaning you can mix inserts and selects from the write db and then from the read db at once.
Nates comment in the latter mail is correct also in doing it. I do this all the time. Just not with your truly (mysql)
John
Alex:
Do you mean that a separate job, iterates the orders, accumulates the real ordered quantity and subtracts it from some "initial quantity" in order to produce available quantity?
There are a few things we do: 1. When we place an order, we commit the order to the database in a transaction. 2. In a separate transaction, we reduce the qty available of the product by the amounts ordered. This transaction may be a conflict with other transactions. That is OK. 3. The inventory management system keeps the master inventory of the products. We have a continuous job that pulls the latest updates to the inventory and feeds those updates back to the web database.
So, even if an inventory number is wrong for a short time, it will be updated to an accurate number soon.
What do you do in cases where you have oversold a product. I mean when the "ordered quantity" got bigger than the "available quantity" due to a conflict in available quantity field? I assume that the system sends an email to the warehouse to increase additionally the quantity of that product?
If we oversell a product, we are going to either have to get more pronto or tell the customer we can't deliver it. The inventory numbers are updated often enough that we do not encounter it often.
Could you describe a case where a transaction has failed , and how you deal with it?
If a transaction fails on one endpoint, we try it against another endpoint. If it still fails, it depends on what the transaction was for. If it is not urgent (Like updating an inventory number) we send ourselves and alert and drop the transaction. If it is urgent, we notify the customer there was an error and send ourselves an alert. This happens very rarely.
We also have monitoring on the state of the replication endpoints to make sure they are still active and are not having a problem communicating to the master.
I hope this helps, Neil
-- Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net/centos Virtual private server with CentOS 5.4 preinstalled for $25/month! Unmetered bandwidth = no overage charges, 7 day free trial
On Wed, 2010-03-17 at 12:07 -0500, Neil Aggarwal wrote:
There are a few things we do:
- When we place an order, we commit the order to the database in a transaction.
- In a separate transaction, we reduce the qty available of the product by the amounts ordered. This transaction may be a conflict with other transactions. That is OK.
- The inventory management system keeps the master inventory of the products. We have a continuous job that pulls the latest updates to the inventory and feeds those updates back to the web database.
So, even if an inventory number is wrong for a short time, it will be updated to an accurate number soon.
-- I am just curious is this a hack around some specific problem? It just does not merge in my head.
John
John:
So, even if an inventory number is wrong for a short time, it will be updated to an accurate number soon.
-- I am just curious is this a hack around some specific problem? It just does not merge in my head.
Our customers sell products on marketplaces (Like Amazon) as well as their direct web site so the web database needs to get inventory updates from the inventory system in case a product sold somewhere else.
Neil
-- Neil Aggarwal, (281)846-8957 FREE trial: cPanel VPS with unmetered bandwidth http://UnmeteredVPS.net/cpanel
Neil Aggarwal wrote:
There are a few things we do:
- When we place an order, we commit the order to the database in a transaction.
- In a separate transaction, we reduce the qty available of the product by the amounts ordered. This transaction may be a conflict with other transactions. That is OK.
- The inventory management system keeps the master inventory of the products. We have a continuous job that pulls the latest updates to the inventory and feeds those updates back to the web database.
So, even if an inventory number is wrong for a short time, it will be updated to an accurate number soon.
that may be OK for an order processing system, but it could be a serious problem for something like a banking system where you are dispersing cash.
Neil Aggarwal wrote:
There are a few things we do:
- When we place an order, we commit the order to the database in a transaction.
- In a separate transaction, we reduce the qty available of the product by the amounts ordered. This transaction may be a conflict with other transactions. That is OK.
- The inventory management system keeps the master inventory of the products. We have a continuous job that pulls the latest updates to the inventory and feeds those updates back to the web database.
So, even if an inventory number is wrong for a short time, it will be updated to an accurate number soon.
that may be OK for an order processing system, but it could be a serious problem for something like a banking system where you are dispersing cash.
True... but such a system would, by default, be RT.
mark
that may be OK for an order processing system, but it could be a serious problem for something like a banking system where you are dispersing cash.
I agree. I did preface my comments with that. Neil
-- Neil Aggarwal, (281)846-8957 FREE trial: cPanel VPS with unmetered bandwidth http://UnmeteredVPS.net/cpanel
On Tue, 2010-03-16 at 23:19 -0500, Neil Aggarwal wrote:
Mysql by itself has built in "clustering" though there can be significant limitations in it depending on your requirements.
I agree. The built in cluster has too many limitations to be useful, but MySQL master-master replication gives a very good alternative to a true cluster. We use it to deploy geographically redundant systems and it has worked very well for us.
Neil
Well what are your plans when it gets the AXE??
John
JohnS wrote:
On Tue, 2010-03-16 at 23:19 -0500, Neil Aggarwal wrote:
Mysql by itself has built in "clustering" though there can be significant limitations in it depending on your requirements.
I agree. The built in cluster has too many limitations to be useful, but MySQL master-master replication gives a very good alternative to a true cluster. We use it to deploy geographically redundant systems and it has worked very well for us.
Neil
Well what are your plans when it gets the AXE??
firebirdsql of course.
On Wed, 2010-03-17 at 21:10 +0800, Chan Chung Hang Christopher wrote:
JohnS wrote:
On Tue, 2010-03-16 at 23:19 -0500, Neil Aggarwal wrote:
Mysql by itself has built in "clustering" though there can be significant limitations in it depending on your requirements.
I agree. The built in cluster has too many limitations to be useful, but MySQL master-master replication gives a very good alternative to a true cluster. We use it to deploy geographically redundant systems and it has worked very well for us.
Neil
Well what are your plans when it gets the AXE??
firebirdsql of course.
You have a loving for Pontiacs?
On Wednesday, March 17, 2010 09:41 PM, JohnS wrote:
On Wed, 2010-03-17 at 21:10 +0800, Chan Chung Hang Christopher wrote:
JohnS wrote:
On Tue, 2010-03-16 at 23:19 -0500, Neil Aggarwal wrote:
Mysql by itself has built in "clustering" though there can be significant limitations in it depending on your requirements.
I agree. The built in cluster has too many limitations to be useful, but MySQL master-master replication gives a very good alternative to a true cluster. We use it to deploy geographically redundant systems and it has worked very well for us.
Neil
Well what are your plans when it gets the AXE??
firebirdsql of course.
You have a loving for Pontiacs?
Trans AM.
But don't let that get in the way of replacing MySQL with firebirdsql. ;-)
After they have suffered enough, then they eat crow and finally go postgresql. :-D
Greetings,
On Thu, Mar 18, 2010 at 5:23 AM, Christopher Chan christopher.chan@bradbury.edu.hk wrote:
GT.M perhaps for speed ;)
Regards,
Rajagopal
On Thursday, March 18, 2010 12:38 PM, Rajagopal Swaminathan wrote:
Greetings,
On Thu, Mar 18, 2010 at 5:23 AM, Christopher Chan christopher.chan@bradbury.edu.hk wrote:
GT.M perhaps for speed ;)
Actually, I'd rather get a Tesla. When, oh when, will Tesla come to HK. This is getting way off topic. /me zipping up.
On Thu, 2010-03-18 at 12:42 +0800, Christopher Chan wrote:
On Thursday, March 18, 2010 12:38 PM, Rajagopal Swaminathan wrote:
Greetings,
On Thu, Mar 18, 2010 at 5:23 AM, Christopher Chan christopher.chan@bradbury.edu.hk wrote:
GT.M perhaps for speed ;)
Actually, I'd rather get a Tesla. When, oh when, will Tesla come to HK. This is getting way off topic. /me zipping up.
--- Well actually since he mentioned GT.M IE (MUMPS DB Database). Ive seen nothing yet to outperform it from nanoseconds to milliseconds.
Actually that particular DB employs the Worlds Largest Database Cluster in History of real time replication encrypted. Of all things people do not even realize our US taxes pay for it!!
Ok that's enough...
john
Greetings,
On Thu, Mar 18, 2010 at 12:52 PM, JohnS jses27@gmail.com wrote:
On Thu, 2010-03-18 at 12:42 +0800, Christopher Chan wrote:
On Thursday, March 18, 2010 12:38 PM, Rajagopal Swaminathan wrote:
GT.M perhaps for speed ;)
Well actually since he mentioned GT.M IE (MUMPS DB Database). Ive seen nothing yet to outperform it from nanoseconds to milliseconds.
Actually that particular DB employs the Worlds Largest Database Cluster in History of real time replication encrypted. Of all things people do not even realize our US taxes pay for it!!
Thanks John for that. I had exactly meant that. I was toying around with OpenVista sometime back for some Non-profit blookbank when I hit upon this.
I was amazed when I read about its stability and speed by glancing at its mission critical deployments in Production env for last few *decades*
Apologies for the noise...
Now back to our regular programming...
Regards,
Rajagopal
On Thu, 2010-03-18 at 13:31 +0530, Rajagopal Swaminathan wrote:
Greetings,
On Thu, Mar 18, 2010 at 12:52 PM, JohnS jses27@gmail.com wrote:
On Thu, 2010-03-18 at 12:42 +0800, Christopher Chan wrote:
On Thursday, March 18, 2010 12:38 PM, Rajagopal Swaminathan wrote:
GT.M perhaps for speed ;)
Well actually since he mentioned GT.M IE (MUMPS DB Database). Ive seen nothing yet to outperform it from nanoseconds to milliseconds.
Actually that particular DB employs the Worlds Largest Database Cluster in History of real time replication encrypted. Of all things people do not even realize our US taxes pay for it!!
Thanks John for that. I had exactly meant that. I was toying around with OpenVista sometime back for some Non-profit blookbank when I hit upon this.
I was amazed when I read about its stability and speed by glancing at its mission critical deployments in Production env for last few *decades*
Apologies for the noise...
Yea but Our US Tax Dollars Pay for VistA which in turn is actually OpenVistA. Well, reading about it is nothing compared to seeing it actually replicate throughout every state in the US. Although keep in mind MUMPS DB is not really RDMS in mind but can be. The glorious DB Cache' is mumps db :-) Facts: http://en.wikipedia.org/wiki/MUMPS
John
Well what are your plans when it gets the AXE??
We will probably consider Maria DB. Hopefully, it will be mature enough by then.
Neil
-- Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net/centos Virtual private server with CentOS 5.4 preinstalled for $25/month! Unmetered bandwidth = no overage charges, 7 day free trial
-
Alexander Georgiev -
Chan Chung Hang Christopher -
Christopher Chan
-
jchase -
John R Pierce -
JohnS -
m.roth＠5-cent.us -
nate -
Neil Aggarwal -
Rajagopal Swaminathan