Thanks for the release of XtraDB Cluster, it really hits the sweetspot for a lot of aplications like mine, where High Availability and data consistency are very important, and the amount of data is not huge.
I am planning on migrating my companies infrastructure to an XtraDB cluster architecture, but have a question on how best to deal with heavy and long running Business Intelligence queries. If I have a 3 node cluster, three options come to mind.
1) Create a 3 node XtraDB cluster. With HA proxy, distribute all queries to all nodes. However, a BI query might tie up a node, and then that node would exhibit increased latency when answering more importante OLTP queries.
2) Create a 3 node XtraDB cluster. With HA proxy, distribute OLTP queries to 2 nodes and have the third only take BI queries. However, as the replication is synchronous, won't this slow down the masters if tables are blocked and the cluster commit takes time?
3) The other idea is to have a 2 node cluster, and a third node with normal, asynchronous MySQL replication and a cluster arbiter to simulate a third node. In this case, if the third node has only BI queries and it is blocked, it will not affect the galera cluster, and BI queries and data can lag behind the production servers without a real issue. However, it does seem to have some extra management and headaches.
Are my assumptions correct? Any recommendations on what strategy to use?
As long as you are using InnoDB tables, then readers won't block
writers, that is, writes on node A won't be blocked by reads on node
A, B or C.
I'd suggest that you use a three node cluster, use HA proxy to direct
OLTP read/writes to one or two nodes and BI requests to the third
node. The BI workload likely has a different working set from the
OLTP workload, which is why I suggest you send BI queries to a
dedicated node.
--Justin
On Tue, Sep 11, 2012 at 8:01 AM, Alejandro Recarey
<alexreca...@gmail.com> wrote:
> Thanks for the release of XtraDB Cluster, it really hits the sweetspot for a
> lot of aplications like mine, where High Availability and data consistency
> are very important, and the amount of data is not huge.
> I am planning on migrating my companies infrastructure to an XtraDB cluster
> architecture, but have a question on how best to deal with heavy and long
> running Business Intelligence queries. If I have a 3 node cluster, three
> options come to mind.
> 1) Create a 3 node XtraDB cluster. With HA proxy, distribute all queries to
> all nodes. However, a BI query might tie up a node, and then that node would
> exhibit increased latency when answering more importante OLTP queries.
> 2) Create a 3 node XtraDB cluster. With HA proxy, distribute OLTP queries to
> 2 nodes and have the third only take BI queries. However, as the replication
> is synchronous, won't this slow down the masters if tables are blocked and
> the cluster commit takes time?
> 3) The other idea is to have a 2 node cluster, and a third node with normal,
> asynchronous MySQL replication and a cluster arbiter to simulate a third
> node. In this case, if the third node has only BI queries and it is blocked,
> it will not affect the galera cluster, and BI queries and data can lag
> behind the production servers without a real issue. However, it does seem to
> have some extra management and headaches.
> Are my assumptions correct? Any recommendations on what strategy to use?
> --
> You received this message because you are subscribed to the Google Groups
> "Percona Discussion" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/percona-discussion/-/fInV4T3hoTAJ.
> To post to this group, send email to percona-discussion@googlegroups.com.
> To unsubscribe from this group, send email to
> percona-discussion+unsubscribe@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/percona-discussion?hl=en.
On Sep 11, 2012, at 1:56 PM, Justin Swanhart <greenl...@gmail.com> wrote:
> As long as you are using InnoDB tables, then readers won't block
> writers, that is, writes on node A won't be blocked by reads on node
> A, B or C.
> I'd suggest that you use a three node cluster, use HA proxy to direct
> OLTP read/writes to one or two nodes and BI requests to the third
> node. The BI workload likely has a different working set from the
> OLTP workload, which is why I suggest you send BI queries to a
> dedicated node.
> On Sep 11, 2012, at 1:56 PM, Justin Swanhart <greenl...@gmail.com> wrote:
> As long as you are using InnoDB tables, then readers won't block
> writers, that is, writes on node A won't be blocked by reads on node
> A, B or C.
> I'd suggest that you use a three node cluster, use HA proxy to direct
> OLTP read/writes to one or two nodes and BI requests to the third
> node. The BI workload likely has a different working set from the
> OLTP workload, which is why I suggest you send BI queries to a
> dedicated node.
> --
> You received this message because you are subscribed to the Google Groups
> "Percona Discussion" group.
> To post to this group, send email to percona-discussion@googlegroups.com.
> To unsubscribe from this group, send email to
> percona-discussion+unsubscribe@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/percona-discussion?hl=en.
Jay, thanks for pointing that blog post, definitely good reading, I think I can get around those issues.
Danilo, while I am familiar with and use some NoSQL solutions, neither my query load or my stored data is large enough lo make the extra investment in maintenence worth it. MySQL should meet my needs.
> Jay, thanks for pointing that blog post, definitely good reading, I think I
> can get around those issues.
> Danilo, while I am familiar with and use some NoSQL solutions, neither my
> query load or my stored data is large enough lo make the extra investment in
> maintenence worth it. MySQL should meet my needs.
> To post to this group, send email to percona-discussion@googlegroups.com.
> To unsubscribe from this group, send email to
> percona-discussion+unsubscribe@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/percona-discussion?hl=en.
