You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@activemq.apache.org by sreekumara <sr...@gmail.com> on 2015/08/06 12:29:08 UTC
Too many updates in MySQL
I am using jdbcPersistenceAdapter and MySQL database. In my test scenario
some of the durable subscribers are offline for long hours (<24hrs). The
tests shows continuous update queries are fired to database to update
"LAST_ACKED_ID".
Why so many updates are repeatedly fired for every few seconds? Is there any
parameter to control the frequency for these updates. How
"expireMessagesPeriod" work , does it has any impact on these updates?
repeated queries from database general log are given below.
Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25026\, XID = NULL WHERE
CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA27' AND SUB_NAME='test3'
Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25026\, XID = NULL WHERE
CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA26' AND SUB_NAME='test2'
Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25026\, XID = NULL WHERE
CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA29' AND SUB_NAME='test5'
Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25026\, XID = NULL WHERE
CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA28' AND SUB_NAME='test4'
Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25026\, XID = NULL WHERE
CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA23' AND SUB_NAME='test1'
Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25027\, XID = NULL WHERE
CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA27' AND SUB_NAME='test3'
Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25027\, XID = NULL WHERE
CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA26' AND SUB_NAME='test2'
Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25027\, XID = NULL WHERE
CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA29' AND SUB_NAME='test5'
Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25027\, XID = NULL WHERE
CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA28' AND SUB_NAME='test4'
Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25027\, XID = NULL WHERE
CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA23' AND SUB_NAME='test1'
Query,UPDATE ACTIVEMQ_LOCK SET TIME = 1438705485091 WHERE ID = 1
Query,SELECT ID\, MSG FROM ACTIVEMQ_MSGS WHERE CONTAINER='topic://testTopic'
ORDER BY ID
Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25026\, XID = NULL WHERE
CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA27' AND SUB_NAME='test3'
Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25026\, XID = NULL WHERE
CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA26' AND SUB_NAME='test2'
Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25026\, XID = NULL WHERE
CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA29' AND SUB_NAME='test5'
Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25026\, XID = NULL WHERE
CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA28' AND SUB_NAME='test4'
Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25026\, XID = NULL WHERE
CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA23' AND SUB_NAME='test1'
Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25027\, XID = NULL WHERE
CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA27' AND SUB_NAME='test3'
Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25027\, XID = NULL WHERE
CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA26' AND SUB_NAME='test2'
Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25027\, XID = NULL WHERE
CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA29' AND SUB_NAME='test5'
Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25027\, XID = NULL WHERE
CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA28' AND SUB_NAME='test4'
Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25027\, XID = NULL WHERE
CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA23' AND SUB_NAME='test1'
Thanks in advance
sreekumar
--
View this message in context: http://activemq.2283324.n4.nabble.com/Too-many-updates-in-MySQL-tp4700485.html
Sent from the ActiveMQ - Dev mailing list archive at Nabble.com.
Re: Too many updates in MySQL
Posted by artnaseef <ar...@artnaseef.com>.
OK, extracting one SUB_NAME and CLIENT_ID combo, something else looks wrong
here:
Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25026\, XID = NULL WHERE
CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA27' AND SUB_NAME='test3'
Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25027\, XID = NULL WHERE
CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA27' AND SUB_NAME='test3'
Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25026\, XID = NULL WHERE
CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA27' AND SUB_NAME='test3'
Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25027\, XID = NULL WHERE
CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA27' AND SUB_NAME='test3'
Notice that the ACK ID 25026 is updated, then 25027, and then both again.
Perhaps I'm misreading the Ops pasted content.
--
View this message in context: http://activemq.2283324.n4.nabble.com/Too-many-updates-in-MySQL-tp4700512p4700740.html
Sent from the ActiveMQ - Dev mailing list archive at Nabble.com.
Re: Too many updates in MySQL
Posted by artnaseef <ar...@artnaseef.com>.
Actually, it looks like the updates are not storing new information, but
simply repeatedly storing the acknowledgement table.
I need to review the code to be sure how this works. I believe the ack
table is re-written periodically. However, I *may* be thinking of KahaDB
logic that does not apply to MySql.
--
View this message in context: http://activemq.2283324.n4.nabble.com/Too-many-updates-in-MySQL-tp4700512p4700739.html
Sent from the ActiveMQ - Dev mailing list archive at Nabble.com.
Re: Too many updates in MySQL
Posted by Clebert Suconic <cl...@gmail.com>.
> Also, I forgot to mention that no you can't control the frequency of this.
> Durable subscriptions need to update the store on acknowledgement because
> they are persistent. Message expiration is different and this query
> wouldn't be affected by this.
>
> If you are having performance issues I would recommend looking at something
> like KahaDB as a message store which should perform much better.
+1000... Databases are not meant for messaging. a DB is meant to store
data in a transactional manner. Message storages will use the DB as a
transient storage, for that reason I have always reject any user
request towards a JDBC storage on my old HornetQ days...
Although there is an opened JIRA now for a JDBC/Database storage on
Artemis. I had opened that because it's one of the gaps between
ActiveMQ5 and ActiveMQ-Artemis.. (and because I have had seen users
requesting it on a frequent base on my day job).
So, answering sreekumar, in your shoes I would either use KahaDB orif
you must use a JDBC because of environment restrictions, you will need
to either research DB tuning accordingly to your transient load or
hire/contact a Database Specialist (aka DBA)
Re: Too many updates in MySQL
Posted by Christopher Shannon <ch...@gmail.com>.
Also, I forgot to mention that no you can't control the frequency of this.
Durable subscriptions need to update the store on acknowledgement because
they are persistent. Message expiration is different and this query
wouldn't be affected by this.
If you are having performance issues I would recommend looking at something
like KahaDB as a message store which should perform much better.
On Thu, Aug 6, 2015 at 2:57 PM, Christopher Shannon <
christopher.l.shannon@gmail.com> wrote:
> These updates typically happen when a client acknowledges a message. On
> acknowledgement the TopicStore needs to be updated to keep track of which
> subscriptions have acked each message. This is how the broker knows when a
> message can be deleted (when all durable subscriptions have acknowledged
> the message)
>
> On Thu, Aug 6, 2015 at 6:29 AM, sreekumara <sr...@gmail.com> wrote:
>
>> I am using jdbcPersistenceAdapter and MySQL database. In my test scenario
>> some of the durable subscribers are offline for long hours (<24hrs). The
>> tests shows continuous update queries are fired to database to update
>> "LAST_ACKED_ID".
>>
>> Why so many updates are repeatedly fired for every few seconds? Is there
>> any
>> parameter to control the frequency for these updates. How
>> "expireMessagesPeriod" work , does it has any impact on these updates?
>>
>> repeated queries from database general log are given below.
>>
>> Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25026\, XID = NULL WHERE
>> CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA27' AND SUB_NAME='test3'
>> Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25026\, XID = NULL WHERE
>> CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA26' AND SUB_NAME='test2'
>> Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25026\, XID = NULL WHERE
>> CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA29' AND SUB_NAME='test5'
>> Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25026\, XID = NULL WHERE
>> CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA28' AND SUB_NAME='test4'
>> Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25026\, XID = NULL WHERE
>> CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA23' AND SUB_NAME='test1'
>> Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25027\, XID = NULL WHERE
>> CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA27' AND SUB_NAME='test3'
>> Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25027\, XID = NULL WHERE
>> CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA26' AND SUB_NAME='test2'
>> Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25027\, XID = NULL WHERE
>> CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA29' AND SUB_NAME='test5'
>> Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25027\, XID = NULL WHERE
>> CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA28' AND SUB_NAME='test4'
>> Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25027\, XID = NULL WHERE
>> CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA23' AND SUB_NAME='test1'
>> Query,UPDATE ACTIVEMQ_LOCK SET TIME = 1438705485091 WHERE ID = 1
>> Query,SELECT ID\, MSG FROM ACTIVEMQ_MSGS WHERE
>> CONTAINER='topic://testTopic'
>> ORDER BY ID
>> Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25026\, XID = NULL WHERE
>> CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA27' AND SUB_NAME='test3'
>> Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25026\, XID = NULL WHERE
>> CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA26' AND SUB_NAME='test2'
>> Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25026\, XID = NULL WHERE
>> CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA29' AND SUB_NAME='test5'
>> Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25026\, XID = NULL WHERE
>> CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA28' AND SUB_NAME='test4'
>> Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25026\, XID = NULL WHERE
>> CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA23' AND SUB_NAME='test1'
>> Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25027\, XID = NULL WHERE
>> CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA27' AND SUB_NAME='test3'
>> Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25027\, XID = NULL WHERE
>> CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA26' AND SUB_NAME='test2'
>> Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25027\, XID = NULL WHERE
>> CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA29' AND SUB_NAME='test5'
>> Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25027\, XID = NULL WHERE
>> CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA28' AND SUB_NAME='test4'
>> Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25027\, XID = NULL WHERE
>> CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA23' AND SUB_NAME='test1'
>>
>> Thanks in advance
>> sreekumar
>>
>>
>>
>> --
>> View this message in context:
>> http://activemq.2283324.n4.nabble.com/Too-many-updates-in-MySQL-tp4700485.html
>> Sent from the ActiveMQ - Dev mailing list archive at Nabble.com.
>>
>
>
Re: Too many updates in MySQL
Posted by Christopher Shannon <ch...@gmail.com>.
These updates typically happen when a client acknowledges a message. On
acknowledgement the TopicStore needs to be updated to keep track of which
subscriptions have acked each message. This is how the broker knows when a
message can be deleted (when all durable subscriptions have acknowledged
the message)
On Thu, Aug 6, 2015 at 6:29 AM, sreekumara <sr...@gmail.com> wrote:
> I am using jdbcPersistenceAdapter and MySQL database. In my test scenario
> some of the durable subscribers are offline for long hours (<24hrs). The
> tests shows continuous update queries are fired to database to update
> "LAST_ACKED_ID".
>
> Why so many updates are repeatedly fired for every few seconds? Is there
> any
> parameter to control the frequency for these updates. How
> "expireMessagesPeriod" work , does it has any impact on these updates?
>
> repeated queries from database general log are given below.
>
> Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25026\, XID = NULL WHERE
> CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA27' AND SUB_NAME='test3'
> Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25026\, XID = NULL WHERE
> CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA26' AND SUB_NAME='test2'
> Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25026\, XID = NULL WHERE
> CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA29' AND SUB_NAME='test5'
> Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25026\, XID = NULL WHERE
> CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA28' AND SUB_NAME='test4'
> Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25026\, XID = NULL WHERE
> CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA23' AND SUB_NAME='test1'
> Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25027\, XID = NULL WHERE
> CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA27' AND SUB_NAME='test3'
> Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25027\, XID = NULL WHERE
> CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA26' AND SUB_NAME='test2'
> Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25027\, XID = NULL WHERE
> CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA29' AND SUB_NAME='test5'
> Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25027\, XID = NULL WHERE
> CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA28' AND SUB_NAME='test4'
> Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25027\, XID = NULL WHERE
> CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA23' AND SUB_NAME='test1'
> Query,UPDATE ACTIVEMQ_LOCK SET TIME = 1438705485091 WHERE ID = 1
> Query,SELECT ID\, MSG FROM ACTIVEMQ_MSGS WHERE
> CONTAINER='topic://testTopic'
> ORDER BY ID
> Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25026\, XID = NULL WHERE
> CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA27' AND SUB_NAME='test3'
> Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25026\, XID = NULL WHERE
> CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA26' AND SUB_NAME='test2'
> Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25026\, XID = NULL WHERE
> CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA29' AND SUB_NAME='test5'
> Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25026\, XID = NULL WHERE
> CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA28' AND SUB_NAME='test4'
> Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25026\, XID = NULL WHERE
> CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA23' AND SUB_NAME='test1'
> Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25027\, XID = NULL WHERE
> CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA27' AND SUB_NAME='test3'
> Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25027\, XID = NULL WHERE
> CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA26' AND SUB_NAME='test2'
> Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25027\, XID = NULL WHERE
> CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA29' AND SUB_NAME='test5'
> Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25027\, XID = NULL WHERE
> CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA28' AND SUB_NAME='test4'
> Query,UPDATE ACTIVEMQ_ACKS SET LAST_ACKED_ID=25027\, XID = NULL WHERE
> CONTAINER='topic://testTopic' AND CLIENT_ID='AP8DA23' AND SUB_NAME='test1'
>
> Thanks in advance
> sreekumar
>
>
>
> --
> View this message in context:
> http://activemq.2283324.n4.nabble.com/Too-many-updates-in-MySQL-tp4700485.html
> Sent from the ActiveMQ - Dev mailing list archive at Nabble.com.
>
Re: Too many updates in MySQL
Posted by will1 <wi...@me.com>.
We are currently experiencing a very similar behaviour with duplicate updates
being issued to the activeMQ table for each offline subscriber every time a
message expires.
Obviously this is not ideal as this doubles the update transactions on the
DB.
Can anyone provide an explanation regarding this please..?
--
View this message in context: http://activemq.2283324.n4.nabble.com/Too-many-updates-in-MySQL-tp4700512p4703221.html
Sent from the ActiveMQ - Dev mailing list archive at Nabble.com.