You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@activemq.apache.org by will1 <wi...@me.com> on 2015/10/21 15:16:26 UTC

Slow MySQL datastore performance.

Hi 

We are currently experiencing database performance issues with an activeMQ
JDBC datastore. 
The key components involved are as follows:
	- ActiveMQ 5.10
	- MySQL 5.6.20 

In terms of activeMQ, for resilience we have a simple master/slave setup
with a load balancer directing traffic at the current master which in turn
has the current lock on the shared non high performance journal database.

DB performance issues happen in 2 key areas:
	1. Number of DB transactions issued by activeMQ appear to be relatively
high. For example in our test: 

		a. A backlog of messages contained roughly 1000 messages. We had 10
offline subscribers. When the subscribers were brought back online and the
messages were delivered we had an update for each message and subscription
combination ie 10,000 updates being issued. Obviously scaling this to 100
users results in 100,000 updates etc. 
This behaviour, combined with the multitude of other transactions such as
when a message is written to the broker all offline subscriptions are
updated in the activeMQ_acks table eg for 100 offline subscriptions, 100
update statements are issued every time a message is received, results is a
very high number of transactions being issued to the DB. 

	2. In turn the DB especially the replication aspect of this is struggling
to keep up.
	

So initially 2 questions:

	1. Is there any configuration to reduce or tune the number of transactions
issued to the DB.
	2. Would a different datastore strategy provide better performance in terms
of persistence eg
		a. High performance journaled JDBC (MySQL) (would just need a solution to
replicate the journal)
		b. File based datastore eg replicated level DB store with zookper.


Inputs would be greatly appreciated.





--
View this message in context: http://activemq.2283324.n4.nabble.com/Slow-MySQL-datastore-performance-tp4703223.html
Sent from the ActiveMQ - User mailing list archive at Nabble.com.

Re: Slow MySQL datastore performance.

Posted by Tim Bain <tb...@alumni.duke.edu>.
In your example of 100 consumers, I would expect that when a message is
received 100 ack rows would be inserted (ideally in a single transaction),
and when each consumer receives its copy of the message one row would be
updated (so when they all have finally received it, you'll have done 100
updates).  I'm assuming this all makes sense and your question is "why is
each update a separate transaction?"

Batching updates made within a very short time window into a single
transaction is possible, but 1) it's more complicated code because you need
to coordinate the requested updates across threads, and 2) you increase
latency for some threads because the early ones in the window have to wait
till the window closes and the database update is issued before taking
their next action (which may depend on the knowledge that the previous ack
has been durably recorded), which might be worse than the overhead of
starting a transaction.

Any solution that wanted to batch several updates from the same thread
(i.e. allow the thread to continue working asynchronously) would have to
ensure that the actions taken thereafter would not be invalid if the broker
crashed without writing them to the database (which I think would be
difficult) or would need to relax the guarantees that ActiveMQ makes about
how messages are handled (probably not going to happen just to improve
performance of a store technology that's infrequently used).
On Oct 21, 2015 8:47 AM, "will1" <wi...@me.com> wrote:

> Thanks Tim.. If anyone has any relevant benchmarks they could provide, that
> would be really useful.. any more thoughts on the original question would
> be
> appreciated also..
>
>
>
>
> --
> View this message in context:
> http://activemq.2283324.n4.nabble.com/Slow-MySQL-datastore-performance-tp4703223p4703233.html
> Sent from the ActiveMQ - User mailing list archive at Nabble.com.
>

Re: Slow MySQL datastore performance.

Posted by will1 <wi...@me.com>.
Thanks Tim.. If anyone has any relevant benchmarks they could provide, that
would be really useful.. any more thoughts on the original question would be
appreciated also..




--
View this message in context: http://activemq.2283324.n4.nabble.com/Slow-MySQL-datastore-performance-tp4703223p4703233.html
Sent from the ActiveMQ - User mailing list archive at Nabble.com.

Re: Slow MySQL datastore performance.

Posted by Tim Bain <tb...@alumni.duke.edu>.
I personally don't have any, though someone else on here might.

My statements about the relative performance of the three store types is
based on reading threads on this mailing list as well as posts on
blogs/StackOverflow/etc., not direct firsthand observation, and I've never
seen anyone post any apples-to-apples metrics for comparison.  But if
anyone else has done that, please post your observations.

Tim

On Wed, Oct 21, 2015 at 7:59 AM, will1 <wi...@me.com> wrote:

> Hi Tim.. thanks for the swift response..
>
> Do you have any benchmarks for the KahaDB or replicated LevelDB approach..?
>
>
>
>
>
> --
> View this message in context:
> http://activemq.2283324.n4.nabble.com/Slow-MySQL-datastore-performance-tp4703223p4703230.html
> Sent from the ActiveMQ - User mailing list archive at Nabble.com.
>

Re: Slow MySQL datastore performance.

Posted by will1 <wi...@me.com>.
Hi Tim.. thanks for the swift response..

Do you have any benchmarks for the KahaDB or replicated LevelDB approach..?





--
View this message in context: http://activemq.2283324.n4.nabble.com/Slow-MySQL-datastore-performance-tp4703223p4703230.html
Sent from the ActiveMQ - User mailing list archive at Nabble.com.

Re: Slow MySQL datastore performance.

Posted by Tim Bain <tb...@alumni.duke.edu>.
For 2b, the answer is "yes".  Both KahaDB and LevelDB are generally held to
be considerably faster than JDBC.

Previous questions to this mailing list about JDBC store performance have
netted responses along the lines of "not much effort has been made to tune
it, and there's very little interest in it (other than from people new to
ActiveMQ before they figure out they should be using something else
instead), so no one's likely to put much effort into tuning it."  With that
being said, you're welcome to fork the git repo, figure out how to make it
faster, and submit a pull request; we wouldn't mind having it be faster (as
long as you don't break correctness, so anything that doesn't issue a SQL
statement per message-subscription pair will need to prove it's not causing
potential errors if an unplanned shutdown occurs at just the wrong time),
if you want to spend the time to make it that way.

Tim

On Wed, Oct 21, 2015 at 7:16 AM, will1 <wi...@me.com> wrote:

> Hi
>
> We are currently experiencing database performance issues with an activeMQ
> JDBC datastore.
> The key components involved are as follows:
>         - ActiveMQ 5.10
>         - MySQL 5.6.20
>
> In terms of activeMQ, for resilience we have a simple master/slave setup
> with a load balancer directing traffic at the current master which in turn
> has the current lock on the shared non high performance journal database.
>
> DB performance issues happen in 2 key areas:
>         1. Number of DB transactions issued by activeMQ appear to be
> relatively
> high. For example in our test:
>
>                 a. A backlog of messages contained roughly 1000 messages.
> We had 10
> offline subscribers. When the subscribers were brought back online and the
> messages were delivered we had an update for each message and subscription
> combination ie 10,000 updates being issued. Obviously scaling this to 100
> users results in 100,000 updates etc.
> This behaviour, combined with the multitude of other transactions such as
> when a message is written to the broker all offline subscriptions are
> updated in the activeMQ_acks table eg for 100 offline subscriptions, 100
> update statements are issued every time a message is received, results is a
> very high number of transactions being issued to the DB.
>
>         2. In turn the DB especially the replication aspect of this is
> struggling
> to keep up.
>
>
> So initially 2 questions:
>
>         1. Is there any configuration to reduce or tune the number of
> transactions
> issued to the DB.
>         2. Would a different datastore strategy provide better performance
> in terms
> of persistence eg
>                 a. High performance journaled JDBC (MySQL) (would just
> need a solution to
> replicate the journal)
>                 b. File based datastore eg replicated level DB store with
> zookper.
>
>
> Inputs would be greatly appreciated.
>
>
>
>
>
> --
> View this message in context:
> http://activemq.2283324.n4.nabble.com/Slow-MySQL-datastore-performance-tp4703223.html
> Sent from the ActiveMQ - User mailing list archive at Nabble.com.
>