XtraDB Cluster and long running Business Intelligence queries

[Alejandro Recarey]

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?

[Justin Swanhart]

Hi,

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

> --
> 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-d...@googlegroups.com.
> To unsubscribe from this group, send email to
> percona-discuss...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/percona-discussion?hl=en.

[Jay Janssen]

My only concern would be long running queries on the third node gathering enough row locks to trigger the situation described here:  http://www.mysqlperformanceblog.com/2012/08/17/percona-xtradb-cluster-multi-node-writing-and-unexpected-deadlocks/

On Sep 11, 2012, at 1:56 PM, Justin Swanhart <gree...@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.

--Justin

Jay Janssen, Senior MySQL Consultant, Percona Inc.

http://about.me/jay.janssen

Percona Live in NYC Oct 1-2nd: http://www.percona.com/live/nyc-2012/

[Danillo Souza]

Alejandro,


have you tried to use columnar database for BI propose?

I think you gonna get a better performance with it.


Danillo Souza

2012/9/11 Jay Janssen <jay.j...@percona.com>:

> --
> You received this message because you are subscribed to the Google Groups
> "Percona Discussion" group.

[Danillo Souza]

Hi Alejandro,

I meant InfiniDB, for example, It's free as far as you application is
small, It's MySQL with MySQL's syntax =]

I'm saying that because I had the same problem as yours, and the
easiest and faster way to solve it, was InfiniDB, you should try It.

Danillo Souza



2012/9/13 Alejandro Recarey <a...@cyberfonica.com>:
> Thanks for the replies guys!
>
> Justin, thanks for the advice.
>
> 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.

>
> --
> 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/-/Rq-T4oIv2JcJ.