Anyone using MariaDB Galera Cluster with XF?

[trizz]

Looking for any pro-tips, gotchas, etc you may have experienced during deployment.

Any performance issues?

 

[Digital Doctor]

@Floren @Deebs @trizz
Trizz: if anyone has tried it ... it might be one of these two (They are both server experts, IMO).

 

[Deebs]

I have created many large clusters running Percona Galera Cluster performing thousands of queries per second (both read and writes) without any issues. Using Galera does mean changing the way you manage the cluster (especially after a failure as you could have splitbrain etc). What are you attempting to achieve? HA?

One last thing to note, each node in the cluster will hold the complete dataset unlike Oracle's cluster offering.

 

[digitalpoint]

I tried Galera with XenForo, and it worked fine. It's definitely much simpler to set up and manage than MySQL Cluster (ndbcluster).

That being said, we ended up going with Oracle's cluster offering (ndbcluster) because it scaled much better. Galera is better if you are not going to scale beyond 4 or so DB servers, but once you start going beyond that, it just doesn't scale really well because you are still using InnoDB underneath it all, and that will be your bottleneck since you become disk i/o bound, and your Galera cluster is always as slow as the slowest individual node.

ndbcluster tends to have slightly slower individual queries (it has to go out to multiple nodes, get data, reconstruct results, etc., but it has much more stable query times especially under load).

This is a pretty good article on it... http://www.palominodb.com/blog/2012/12/10/benchmarking-ndb-vs-galera

Bottom line, MariaDB Galera works just fine with XenForo... just be wary if you plan on scaling beyond a few servers.

 

[TPerry]

@Deebs, I was researching this (and I know VERY little about clustering mySQL but am planning on learning) and one thing worried me. The Percona Galera Cluster denotes a "limitation"

Currently replication works only with InnoDB storage engine. Any writes to tables of other types, including system (mysql.*) tables, are not replicated. However, DDL statements are replicated in statement level, and changes to mysql.* tables will get replicated that way. So, you can safely issue: CREATE USER..., but issuing: INSERT INTO mysql.user..., will not be replicated.

Would that not cause problems with xenForo since it does have some myISAM tables?

 

[digitalpoint]

A large site that needs database clustering isn't going to be using the 3 MyISAM tables that XenForo uses.

xf_search_index isn't used if you are using the XF Enhanced Search.

xf_session and xf_session_admin aren't going to be used if you are using the session cache option (for example we use memcached for session).

 

[Deebs]

@Deebs, I was researching this (and I know VERY little about clustering mySQL but am planning on learning) and one thing worried me. The Percona Galera Cluster denotes a "limitation"
Would that not cause problems with xenForo since it does have some myISAM tables?

Hi,

There is not a MySQL clustering solution that does not have some form of limitation, Galera allows you to use InnoDB unchanged (nearly), Oracle uses the NDB storage engine which has other constraints. To be honest if you are looking to invest time and effort into MySQL clustering you will not typically be using MyISAM, and if you need to scale MyISAM the general train of thought is to replicate and split the reads to the slaves and keep the master just for writes. So you could have a cluster and a couple of slaves hanging off the cluster for the MyISAM tables.

As @digitalpoint mentions the tables used by XF are infact redundant for sites which are pushing for performance and availability.

 

[TPerry]

A large site that needs database clustering isn't going to be using the 3 MyISAM tables that XenForo uses.

xf_search_index isn't used if you are using the XF Enhanced Search.

xf_session and xf_session_admin aren't going to be used if you are using the session cache option (for example we use memcached for session).

Already using memcached on both servers (just to learn it - I can probably eliminate it on one and already have basically set up stuff so I can implement it). Since my site isn't that large, Enhanced Search isn't really needed but may end up buying it anyway.

 

[Deebs]

Already using memcached on both servers (just to learn it - I can probably eliminate it on one and already have basically set up stuff so I can implement it). Since my site isn't that large, Enhanced Search isn't really needed but may end up buying it anyway.

ElasticSearch offers much more than just performance over MySQL full-text indexing.

 

[Abracadaniel]

@Deebs, @digitalpoint

Hey everybody, any expert opinion would be appreciated:

1. Any issues with AUTO_INCREMENT while using Galera? Or collisions being successfully avoided as described here? https://blog.mariadb.org/auto-increments-in-galera/
2. What's going to happen with MEMORY-tables, like xf_thread_view, xf_session_activity, xf_attachment_view?

 

[Xon]

Any issues with AUTO_INCREMENT while using Galera? Or collisions being successfully avoided as described here? [URL='https://blog.mariadb.org/auto-incre...//blog.mariadb.org/auto-increments-in-galera/[/url]

Post order is dependant on a computed position (driven by insert date), not the post id. Threads are displayed by date.

Make sure all your nodes are synced to the same clock!

What's going to happen with MEMORY-tables, like xf_thread_view, xf_session_activity, xf_attachment_view?

xf_session_activity can also be converted to being stored into your front-end cache. Otherwise convert to xf_session_activity, and get slightly lower inserts/updates but avoids full-table locks of MyISAM

For xf_thread_view & xf_attachment_view, you need to convert them to InnoDB to have them work properly.

The alternative would be to have an addon written which overrides the following methods:

XenForo_Model_Thread::logThreadView
XenForo_Model_Attachment::logAttachmentView
XenForo_Model_Thread::updateThreadViews
XenForo_Model_Attachment::updateAttachmentViews

Ideally you can use Memcached::append directly, but supporting memcache would be quite annoying.