You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@activemq.apache.org by Ul...@t-systems.com on 2016/10/24 09:14:56 UTC
Persistent messages and postgres backend
Hi all,
we need persistent messages with a postgres backend.
We did some performance tests... and with about 70.000 Rows in ACTIVEMQ_MSGS we saw postgres log entries with:
duration: 15439.610 ms execute <unnamed>: SELECT ID, MSG FROM ACTIVEMQ_MSGS WHERE CONTAINER=$1 ORDER BY ID
DETAIL: parameters: $1 = 'topic://Test.Foo2'
That is: Postgres sees queries with 15 (and more) seconds responsetime.
Ok, we see - our ActiveMQ is slow.
Any ideas how to tune ActiveMQ and/or Postgres database here ?
Uli
Re: Persistent messages and postgres backend
Posted by artnaseef <ar...@amlinv.com>.
Couple of thoughts on tracking this down.
First off, how large are the messages? 70,000 messages doesn't sound too
high in general, but if the messages are very large, it could be an issue.
If all 70,000 are waiting to go to a single consumer, that raises the
concern of a possible "slow consumer" problem.
Second, how slow is that query when run directly against PostgreSQL?
If the query run directly against PostgreSQL is slow, then most likely the
only way to improve, without re-architecting the use of AMQ, would be
tuning of PostgreSQL itself.
On the other hand, if the query runs quickly, then the consumption of the
query within AMQ may be slow. Which could indicate slow consumption by
consumers, among other possibilities.
Another thing that's odd here - I would need to review the code to see why
this would happen. Topics don't persist messages - queues and durable
topic subscriptions store messages. In fact, there isn't even any
in-memory store of messages for topics - they immediately go into
subscriptions. So, seeing the container name "topic://Test.Foo2" in the
query to the DB is surprising. That may be how durable Topic subscriptions
are handled by the JDBC persistence adapter.
Art
On Tue, Oct 25, 2016 at 7:56 AM, Lachezar Dobrev [via ActiveMQ] <
ml-node+s2283324n4718474h42@n4.nabble.com> wrote:
> Hm. That actually shows, that there is no lag caused by PostgreSQL: it
> actually completed all tasks and is idle. That commonly is a result from
> some client-side processing (in ActiveMQ) while the connection to the DB
> is
> still open (common with pools). I suspect that the problem is not with
> PostgreSQL. It's also weird, that
> Do you notice severe CPU load?
> Running 'top' might show if the load is within PostgreSQL or the Web
> Server.
> Are the two on the same machine?
> It might be network latency problems?
> There is a slim chance there is an issue with the JDBC PostgreSQL
> driver,
> do you have the latest version? It might be beneficial to try a few older,
> stable ones too.
>
> I've had some similar issues, with the PostgreSQL being idle, but
> queries
> running slow, but all of those were my fault: near-infinite
> loops/recursion
> while processing a result set…
>
>
> 2016-10-25 14:30 GMT+03:00 <[hidden email]
> <http:///user/SendEmail.jtp?type=node&node=4718474&i=0>>:
>
> > Hi all,
> >
> > VACUUM ANALYSE VERBOSE ACTIVEMQ_MSGS;
> > => No difference. Still slow.
> >
> >
> > I would expect the index to need to be on ID, CONTAINER for it to be
> used
> > in this query.
> > => No difference, still slow. Index is not used (as shown with explain
> and
> > explain analyze)
> >
> > select pid, waiting, state, query from pg_stat_activity order by
> waiting
> > desc, pid asc;
> >
> > amq2=# select pid, waiting, state, query from pg_stat_activity order by
> > waiting
> > desc, pid asc;
> > pid | waiting | state | query
> > -------+---------+--------+---------------------------------
> > -------------------------------------------------
> > 7643 | f | active | select pid, waiting, state, query from
> > pg_stat_activity order by waiting +
> > | | | desc, pid asc;
> > 7992 | f | idle | SET extra_float_digits = 3
> > 7993 | f | idle | UPDATE ACTIVEMQ_LOCK SET BROKER_NAME=$1,
> > TIME=$2 WHERE BROKER_NAME=$3 AND ID = 1
> > 7994 | f | idle | UPDATE ACTIVEMQ_LOCK SET BROKER_NAME=$1,
> > TIME=$2 WHERE BROKER_NAME=$3 AND ID = 1
> > 7995 | f | idle | UPDATE ACTIVEMQ_LOCK SET BROKER_NAME=$1,
> > TIME=$2 WHERE BROKER_NAME=$3 AND ID = 1
> > 7996 | f | idle | UPDATE ACTIVEMQ_LOCK SET BROKER_NAME=$1,
> > TIME=$2 WHERE BROKER_NAME=$3 AND ID = 1
> > 11048 | f | idle |
> >
> > And a loop with this query shows same thing... no waiting visible
> >
> > Other ideas ?
> >
> > Uli
> >
> >
> >
> >
> > T-Systems International GmbH
> > IT Division | Global IT Operations | GCU Automotive & Finance
> > Ulrich Herbst
> > Bannwaldallee 42, D-76185 Karlsruhe
> > +49 721 351-2342 (Tel.)
> > +49 151 16 20 73 13 (Mobil)
> > E-Mail: [hidden email]
> <http:///user/SendEmail.jtp?type=node&node=4718474&i=1>
> > Internet: http://www.t-systems.de
> >
> > Die gesetzlichen Pflichtangaben finden Sie unter:
> > http://www.t-systems.de/pflichtangaben
> >
> >
> > -----Ursprüngliche Nachricht-----
> > Von: [hidden email]
> <http:///user/SendEmail.jtp?type=node&node=4718474&i=2> [mailto:[hidden
> email] <http:///user/SendEmail.jtp?type=node&node=4718474&i=3>] Im
> Auftrag von Tim Bain
> > Gesendet: Montag, 24. Oktober 2016 14:36
> > An: ActiveMQ Users
> > Betreff: Re: Persistent messages and postgres backend
> >
> > I would expect the index to need to be on ID, CONTAINER for it to be
> used
> > in this query.
> >
> > Tim
> >
> > On Oct 24, 2016 5:10 AM, "Lachezar Dobrev" <[hidden email]
> <http:///user/SendEmail.jtp?type=node&node=4718474&i=4>> wrote:
> >
> > Hm… That's weird. Did you do a
> > VACUUM ANALYSE VERBOSE ACTIVEMQ_MSGS;
> > Do you have autovacuum enabled in PostgreSQL?
> >
> > You can try and execute the following:
> > select pid, waiting, state, query from pg_stat_activity order by
> waiting
> > desc, pid asc;
> > this will show all running tasks, check if there are lots of processes
> > that have waiting = true, that might mean that there are locking issues.
> >
> > 2016-10-24 12:48 GMT+03:00 <[hidden email]
> <http:///user/SendEmail.jtp?type=node&node=4718474&i=5>>:
> >
> > > Thank you for your answer - we have tried this before with no success.
> > >
> > > Uli
> > >
> > > -----Ursprüngliche Nachricht-----
> > > Von: Lachezar Dobrev [mailto:[hidden email]
> <http:///user/SendEmail.jtp?type=node&node=4718474&i=6>]
> > > Gesendet: Montag, 24. Oktober 2016 11:42
> > > An: [hidden email]
> <http:///user/SendEmail.jtp?type=node&node=4718474&i=7>
> > > Betreff: Re: Persistent messages and postgres backend
> > >
> > > You might want to add an INDEX on CONTAINER column in ACTIVEMQ_MSGS
> > > table:
> > > CREATE INDEX ACTIVEMQ_MSGS_CONTAINER_INDEX ON
> ACTIVEMQ_MSGS(CONTAINER);
> > >
> > > 2016-10-24 12:14 GMT+03:00 <[hidden email]
> <http:///user/SendEmail.jtp?type=node&node=4718474&i=8>>:
> > >
> > > > Hi all,
> > > >
> > > > we need persistent messages with a postgres backend.
> > > >
> > > > We did some performance tests... and with about 70.000 Rows in
> > > > ACTIVEMQ_MSGS we saw postgres log entries with:
> > > >
> > > > duration: 15439.610 ms execute <unnamed>: SELECT ID, MSG FROM
> > > > ACTIVEMQ_MSGS WHERE CONTAINER=$1 ORDER BY ID
> > > > DETAIL: parameters: $1 = 'topic://Test.Foo2'
> > > >
> > > > That is: Postgres sees queries with 15 (and more) seconds
> responsetime.
> > > >
> > > > Ok, we see - our ActiveMQ is slow.
> > > >
> > > > Any ideas how to tune ActiveMQ and/or Postgres database here ?
> > > >
> > > > Uli
> > > >
> > > >
> > >
> >
>
>
> ------------------------------
> If you reply to this email, your message will be added to the discussion
> below:
> http://activemq.2283324.n4.nabble.com/Persistent-
> messages-and-postgres-backend-tp4718297p4718474.html
> To start a new topic under ActiveMQ - User, email
> ml-node+s2283324n2341805h3@n4.nabble.com
> To unsubscribe from ActiveMQ - User, click here
> <http://activemq.2283324.n4.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=2341805&code=YXJ0QGFtbGludi5jb218MjM0MTgwNXwyMDc3NjQwODU5>
> .
> NAML
> <http://activemq.2283324.n4.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
>
--
View this message in context: http://activemq.2283324.n4.nabble.com/Persistent-messages-and-postgres-backend-tp4718297p4718497.html
Sent from the ActiveMQ - User mailing list archive at Nabble.com.
Re: Persistent messages and postgres backend
Posted by Lachezar Dobrev <l....@gmail.com>.
Hm. That actually shows, that there is no lag caused by PostgreSQL: it
actually completed all tasks and is idle. That commonly is a result from
some client-side processing (in ActiveMQ) while the connection to the DB is
still open (common with pools). I suspect that the problem is not with
PostgreSQL. It's also weird, that
Do you notice severe CPU load?
Running 'top' might show if the load is within PostgreSQL or the Web
Server.
Are the two on the same machine?
It might be network latency problems?
There is a slim chance there is an issue with the JDBC PostgreSQL driver,
do you have the latest version? It might be beneficial to try a few older,
stable ones too.
I've had some similar issues, with the PostgreSQL being idle, but queries
running slow, but all of those were my fault: near-infinite loops/recursion
while processing a result set…
2016-10-25 14:30 GMT+03:00 <Ul...@t-systems.com>:
> Hi all,
>
> VACUUM ANALYSE VERBOSE ACTIVEMQ_MSGS;
> => No difference. Still slow.
>
>
> I would expect the index to need to be on ID, CONTAINER for it to be used
> in this query.
> => No difference, still slow. Index is not used (as shown with explain and
> explain analyze)
>
> select pid, waiting, state, query from pg_stat_activity order by waiting
> desc, pid asc;
>
> amq2=# select pid, waiting, state, query from pg_stat_activity order by
> waiting
> desc, pid asc;
> pid | waiting | state | query
> -------+---------+--------+---------------------------------
> -------------------------------------------------
> 7643 | f | active | select pid, waiting, state, query from
> pg_stat_activity order by waiting +
> | | | desc, pid asc;
> 7992 | f | idle | SET extra_float_digits = 3
> 7993 | f | idle | UPDATE ACTIVEMQ_LOCK SET BROKER_NAME=$1,
> TIME=$2 WHERE BROKER_NAME=$3 AND ID = 1
> 7994 | f | idle | UPDATE ACTIVEMQ_LOCK SET BROKER_NAME=$1,
> TIME=$2 WHERE BROKER_NAME=$3 AND ID = 1
> 7995 | f | idle | UPDATE ACTIVEMQ_LOCK SET BROKER_NAME=$1,
> TIME=$2 WHERE BROKER_NAME=$3 AND ID = 1
> 7996 | f | idle | UPDATE ACTIVEMQ_LOCK SET BROKER_NAME=$1,
> TIME=$2 WHERE BROKER_NAME=$3 AND ID = 1
> 11048 | f | idle |
>
> And a loop with this query shows same thing... no waiting visible
>
> Other ideas ?
>
> Uli
>
>
>
>
> T-Systems International GmbH
> IT Division | Global IT Operations | GCU Automotive & Finance
> Ulrich Herbst
> Bannwaldallee 42, D-76185 Karlsruhe
> +49 721 351-2342 (Tel.)
> +49 151 16 20 73 13 (Mobil)
> E-Mail: Ulrich.Herbst@t-systems.com
> Internet: http://www.t-systems.de
>
> Die gesetzlichen Pflichtangaben finden Sie unter:
> http://www.t-systems.de/pflichtangaben
>
>
> -----Ursprüngliche Nachricht-----
> Von: tbain98@gmail.com [mailto:tbain98@gmail.com] Im Auftrag von Tim Bain
> Gesendet: Montag, 24. Oktober 2016 14:36
> An: ActiveMQ Users
> Betreff: Re: Persistent messages and postgres backend
>
> I would expect the index to need to be on ID, CONTAINER for it to be used
> in this query.
>
> Tim
>
> On Oct 24, 2016 5:10 AM, "Lachezar Dobrev" <l....@gmail.com> wrote:
>
> Hm… That's weird. Did you do a
> VACUUM ANALYSE VERBOSE ACTIVEMQ_MSGS;
> Do you have autovacuum enabled in PostgreSQL?
>
> You can try and execute the following:
> select pid, waiting, state, query from pg_stat_activity order by waiting
> desc, pid asc;
> this will show all running tasks, check if there are lots of processes
> that have waiting = true, that might mean that there are locking issues.
>
> 2016-10-24 12:48 GMT+03:00 <Ul...@t-systems.com>:
>
> > Thank you for your answer - we have tried this before with no success.
> >
> > Uli
> >
> > -----Ursprüngliche Nachricht-----
> > Von: Lachezar Dobrev [mailto:l.dobrev@gmail.com]
> > Gesendet: Montag, 24. Oktober 2016 11:42
> > An: users@activemq.apache.org
> > Betreff: Re: Persistent messages and postgres backend
> >
> > You might want to add an INDEX on CONTAINER column in ACTIVEMQ_MSGS
> > table:
> > CREATE INDEX ACTIVEMQ_MSGS_CONTAINER_INDEX ON ACTIVEMQ_MSGS(CONTAINER);
> >
> > 2016-10-24 12:14 GMT+03:00 <Ul...@t-systems.com>:
> >
> > > Hi all,
> > >
> > > we need persistent messages with a postgres backend.
> > >
> > > We did some performance tests... and with about 70.000 Rows in
> > > ACTIVEMQ_MSGS we saw postgres log entries with:
> > >
> > > duration: 15439.610 ms execute <unnamed>: SELECT ID, MSG FROM
> > > ACTIVEMQ_MSGS WHERE CONTAINER=$1 ORDER BY ID
> > > DETAIL: parameters: $1 = 'topic://Test.Foo2'
> > >
> > > That is: Postgres sees queries with 15 (and more) seconds responsetime.
> > >
> > > Ok, we see - our ActiveMQ is slow.
> > >
> > > Any ideas how to tune ActiveMQ and/or Postgres database here ?
> > >
> > > Uli
> > >
> > >
> >
>
Re: AW: Persistent messages and postgres backend
Posted by Tim Bain <tb...@gmail.com>.
I second Art's suggestion to test whether the query is slow when run
directly against the database. If so, is some other index used, or is a
full table scan being done? If it's a full table scan even though the
index was created, you need to figure out why not (which is a question for
Postgres support groups, not for the ActiveMQ support group). The index I
suggested is the one I'd expect to work based on my experience with Oracle,
but there may be some subtlety about Postgres that causes my Oracle
experience not to translate.
On Oct 25, 2016 5:30 AM, <Ul...@t-systems.com> wrote:
Hi all,
VACUUM ANALYSE VERBOSE ACTIVEMQ_MSGS;
=> No difference. Still slow.
I would expect the index to need to be on ID, CONTAINER for it to be used
in this query.
=> No difference, still slow. Index is not used (as shown with explain and
explain analyze)
select pid, waiting, state, query from pg_stat_activity order by waiting
desc, pid asc;
amq2=# select pid, waiting, state, query from pg_stat_activity order by
waiting
desc, pid asc;
pid | waiting | state | query
-------+---------+--------+---------------------------------
-------------------------------------------------
7643 | f | active | select pid, waiting, state, query from
pg_stat_activity order by waiting +
| | | desc, pid asc;
7992 | f | idle | SET extra_float_digits = 3
7993 | f | idle | UPDATE ACTIVEMQ_LOCK SET BROKER_NAME=$1,
TIME=$2 WHERE BROKER_NAME=$3 AND ID = 1
7994 | f | idle | UPDATE ACTIVEMQ_LOCK SET BROKER_NAME=$1,
TIME=$2 WHERE BROKER_NAME=$3 AND ID = 1
7995 | f | idle | UPDATE ACTIVEMQ_LOCK SET BROKER_NAME=$1,
TIME=$2 WHERE BROKER_NAME=$3 AND ID = 1
7996 | f | idle | UPDATE ACTIVEMQ_LOCK SET BROKER_NAME=$1,
TIME=$2 WHERE BROKER_NAME=$3 AND ID = 1
11048 | f | idle |
And a loop with this query shows same thing... no waiting visible
Other ideas ?
Uli
T-Systems International GmbH
IT Division | Global IT Operations | GCU Automotive & Finance
Ulrich Herbst
Bannwaldallee 42, D-76185 Karlsruhe
+49 721 351-2342 (Tel.)
+49 151 16 20 73 13 (Mobil)
E-Mail: Ulrich.Herbst@t-systems.com
Internet: http://www.t-systems.de
Die gesetzlichen Pflichtangaben finden Sie unter:
http://www.t-systems.de/pflichtangaben
-----Ursprüngliche Nachricht-----
Von: tbain98@gmail.com [mailto:tbain98@gmail.com] Im Auftrag von Tim Bain
Gesendet: Montag, 24. Oktober 2016 14:36
An: ActiveMQ Users
Betreff: Re: Persistent messages and postgres backend
I would expect the index to need to be on ID, CONTAINER for it to be used
in this query.
Tim
On Oct 24, 2016 5:10 AM, "Lachezar Dobrev" <l....@gmail.com> wrote:
Hm… That's weird. Did you do a
VACUUM ANALYSE VERBOSE ACTIVEMQ_MSGS;
Do you have autovacuum enabled in PostgreSQL?
You can try and execute the following:
select pid, waiting, state, query from pg_stat_activity order by waiting
desc, pid asc;
this will show all running tasks, check if there are lots of processes
that have waiting = true, that might mean that there are locking issues.
2016-10-24 12:48 GMT+03:00 <Ul...@t-systems.com>:
> Thank you for your answer - we have tried this before with no success.
>
> Uli
>
> -----Ursprüngliche Nachricht-----
> Von: Lachezar Dobrev [mailto:l.dobrev@gmail.com]
> Gesendet: Montag, 24. Oktober 2016 11:42
> An: users@activemq.apache.org
> Betreff: Re: Persistent messages and postgres backend
>
> You might want to add an INDEX on CONTAINER column in ACTIVEMQ_MSGS
> table:
> CREATE INDEX ACTIVEMQ_MSGS_CONTAINER_INDEX ON ACTIVEMQ_MSGS(CONTAINER);
>
> 2016-10-24 12:14 GMT+03:00 <Ul...@t-systems.com>:
>
> > Hi all,
> >
> > we need persistent messages with a postgres backend.
> >
> > We did some performance tests... and with about 70.000 Rows in
> > ACTIVEMQ_MSGS we saw postgres log entries with:
> >
> > duration: 15439.610 ms execute <unnamed>: SELECT ID, MSG FROM
> > ACTIVEMQ_MSGS WHERE CONTAINER=$1 ORDER BY ID
> > DETAIL: parameters: $1 = 'topic://Test.Foo2'
> >
> > That is: Postgres sees queries with 15 (and more) seconds responsetime.
> >
> > Ok, we see - our ActiveMQ is slow.
> >
> > Any ideas how to tune ActiveMQ and/or Postgres database here ?
> >
> > Uli
> >
> >
>
AW: Persistent messages and postgres backend
Posted by Ul...@t-systems.com.
Hi all,
VACUUM ANALYSE VERBOSE ACTIVEMQ_MSGS;
=> No difference. Still slow.
I would expect the index to need to be on ID, CONTAINER for it to be used
in this query.
=> No difference, still slow. Index is not used (as shown with explain and explain analyze)
select pid, waiting, state, query from pg_stat_activity order by waiting
desc, pid asc;
amq2=# select pid, waiting, state, query from pg_stat_activity order by waiting
desc, pid asc;
pid | waiting | state | query
-------+---------+--------+----------------------------------------------------------------------------------
7643 | f | active | select pid, waiting, state, query from pg_stat_activity order by waiting +
| | | desc, pid asc;
7992 | f | idle | SET extra_float_digits = 3
7993 | f | idle | UPDATE ACTIVEMQ_LOCK SET BROKER_NAME=$1, TIME=$2 WHERE BROKER_NAME=$3 AND ID = 1
7994 | f | idle | UPDATE ACTIVEMQ_LOCK SET BROKER_NAME=$1, TIME=$2 WHERE BROKER_NAME=$3 AND ID = 1
7995 | f | idle | UPDATE ACTIVEMQ_LOCK SET BROKER_NAME=$1, TIME=$2 WHERE BROKER_NAME=$3 AND ID = 1
7996 | f | idle | UPDATE ACTIVEMQ_LOCK SET BROKER_NAME=$1, TIME=$2 WHERE BROKER_NAME=$3 AND ID = 1
11048 | f | idle |
And a loop with this query shows same thing... no waiting visible
Other ideas ?
Uli
T-Systems International GmbH
IT Division | Global IT Operations | GCU Automotive & Finance
Ulrich Herbst
Bannwaldallee 42, D-76185 Karlsruhe
+49 721 351-2342 (Tel.)
+49 151 16 20 73 13 (Mobil)
E-Mail: Ulrich.Herbst@t-systems.com
Internet: http://www.t-systems.de
Die gesetzlichen Pflichtangaben finden Sie unter:
http://www.t-systems.de/pflichtangaben
-----Ursprüngliche Nachricht-----
Von: tbain98@gmail.com [mailto:tbain98@gmail.com] Im Auftrag von Tim Bain
Gesendet: Montag, 24. Oktober 2016 14:36
An: ActiveMQ Users
Betreff: Re: Persistent messages and postgres backend
I would expect the index to need to be on ID, CONTAINER for it to be used
in this query.
Tim
On Oct 24, 2016 5:10 AM, "Lachezar Dobrev" <l....@gmail.com> wrote:
Hm… That's weird. Did you do a
VACUUM ANALYSE VERBOSE ACTIVEMQ_MSGS;
Do you have autovacuum enabled in PostgreSQL?
You can try and execute the following:
select pid, waiting, state, query from pg_stat_activity order by waiting
desc, pid asc;
this will show all running tasks, check if there are lots of processes
that have waiting = true, that might mean that there are locking issues.
2016-10-24 12:48 GMT+03:00 <Ul...@t-systems.com>:
> Thank you for your answer - we have tried this before with no success.
>
> Uli
>
> -----Ursprüngliche Nachricht-----
> Von: Lachezar Dobrev [mailto:l.dobrev@gmail.com]
> Gesendet: Montag, 24. Oktober 2016 11:42
> An: users@activemq.apache.org
> Betreff: Re: Persistent messages and postgres backend
>
> You might want to add an INDEX on CONTAINER column in ACTIVEMQ_MSGS
> table:
> CREATE INDEX ACTIVEMQ_MSGS_CONTAINER_INDEX ON ACTIVEMQ_MSGS(CONTAINER);
>
> 2016-10-24 12:14 GMT+03:00 <Ul...@t-systems.com>:
>
> > Hi all,
> >
> > we need persistent messages with a postgres backend.
> >
> > We did some performance tests... and with about 70.000 Rows in
> > ACTIVEMQ_MSGS we saw postgres log entries with:
> >
> > duration: 15439.610 ms execute <unnamed>: SELECT ID, MSG FROM
> > ACTIVEMQ_MSGS WHERE CONTAINER=$1 ORDER BY ID
> > DETAIL: parameters: $1 = 'topic://Test.Foo2'
> >
> > That is: Postgres sees queries with 15 (and more) seconds responsetime.
> >
> > Ok, we see - our ActiveMQ is slow.
> >
> > Any ideas how to tune ActiveMQ and/or Postgres database here ?
> >
> > Uli
> >
> >
>
Re: Persistent messages and postgres backend
Posted by Tim Bain <tb...@alumni.duke.edu>.
I would expect the index to need to be on ID, CONTAINER for it to be used
in this query.
Tim
On Oct 24, 2016 5:10 AM, "Lachezar Dobrev" <l....@gmail.com> wrote:
Hm… That's weird. Did you do a
VACUUM ANALYSE VERBOSE ACTIVEMQ_MSGS;
Do you have autovacuum enabled in PostgreSQL?
You can try and execute the following:
select pid, waiting, state, query from pg_stat_activity order by waiting
desc, pid asc;
this will show all running tasks, check if there are lots of processes
that have waiting = true, that might mean that there are locking issues.
2016-10-24 12:48 GMT+03:00 <Ul...@t-systems.com>:
> Thank you for your answer - we have tried this before with no success.
>
> Uli
>
> -----Ursprüngliche Nachricht-----
> Von: Lachezar Dobrev [mailto:l.dobrev@gmail.com]
> Gesendet: Montag, 24. Oktober 2016 11:42
> An: users@activemq.apache.org
> Betreff: Re: Persistent messages and postgres backend
>
> You might want to add an INDEX on CONTAINER column in ACTIVEMQ_MSGS
> table:
> CREATE INDEX ACTIVEMQ_MSGS_CONTAINER_INDEX ON ACTIVEMQ_MSGS(CONTAINER);
>
> 2016-10-24 12:14 GMT+03:00 <Ul...@t-systems.com>:
>
> > Hi all,
> >
> > we need persistent messages with a postgres backend.
> >
> > We did some performance tests... and with about 70.000 Rows in
> > ACTIVEMQ_MSGS we saw postgres log entries with:
> >
> > duration: 15439.610 ms execute <unnamed>: SELECT ID, MSG FROM
> > ACTIVEMQ_MSGS WHERE CONTAINER=$1 ORDER BY ID
> > DETAIL: parameters: $1 = 'topic://Test.Foo2'
> >
> > That is: Postgres sees queries with 15 (and more) seconds responsetime.
> >
> > Ok, we see - our ActiveMQ is slow.
> >
> > Any ideas how to tune ActiveMQ and/or Postgres database here ?
> >
> > Uli
> >
> >
>
Re: Persistent messages and postgres backend
Posted by Tim Bain <tb...@alumni.duke.edu>.
I would expect the index to need to be on ID, CONTAINER for it to be used
in this query.
Tim
On Oct 24, 2016 5:10 AM, "Lachezar Dobrev" <l....@gmail.com> wrote:
> Hm… That's weird. Did you do a
> VACUUM ANALYSE VERBOSE ACTIVEMQ_MSGS;
> Do you have autovacuum enabled in PostgreSQL?
>
> You can try and execute the following:
> select pid, waiting, state, query from pg_stat_activity order by waiting
> desc, pid asc;
> this will show all running tasks, check if there are lots of processes
> that have waiting = true, that might mean that there are locking issues.
>
> 2016-10-24 12:48 GMT+03:00 <Ul...@t-systems.com>:
>
> > Thank you for your answer - we have tried this before with no success.
> >
> > Uli
> >
> > -----Ursprüngliche Nachricht-----
> > Von: Lachezar Dobrev [mailto:l.dobrev@gmail.com]
> > Gesendet: Montag, 24. Oktober 2016 11:42
> > An: users@activemq.apache.org
> > Betreff: Re: Persistent messages and postgres backend
> >
> > You might want to add an INDEX on CONTAINER column in ACTIVEMQ_MSGS
> > table:
> > CREATE INDEX ACTIVEMQ_MSGS_CONTAINER_INDEX ON ACTIVEMQ_MSGS(CONTAINER);
> >
> > 2016-10-24 12:14 GMT+03:00 <Ul...@t-systems.com>:
> >
> > > Hi all,
> > >
> > > we need persistent messages with a postgres backend.
> > >
> > > We did some performance tests... and with about 70.000 Rows in
> > > ACTIVEMQ_MSGS we saw postgres log entries with:
> > >
> > > duration: 15439.610 ms execute <unnamed>: SELECT ID, MSG FROM
> > > ACTIVEMQ_MSGS WHERE CONTAINER=$1 ORDER BY ID
> > > DETAIL: parameters: $1 = 'topic://Test.Foo2'
> > >
> > > That is: Postgres sees queries with 15 (and more) seconds responsetime.
> > >
> > > Ok, we see - our ActiveMQ is slow.
> > >
> > > Any ideas how to tune ActiveMQ and/or Postgres database here ?
> > >
> > > Uli
> > >
> > >
> >
>
Re: Persistent messages and postgres backend
Posted by Lachezar Dobrev <l....@gmail.com>.
Hm… That's weird. Did you do a
VACUUM ANALYSE VERBOSE ACTIVEMQ_MSGS;
Do you have autovacuum enabled in PostgreSQL?
You can try and execute the following:
select pid, waiting, state, query from pg_stat_activity order by waiting
desc, pid asc;
this will show all running tasks, check if there are lots of processes
that have waiting = true, that might mean that there are locking issues.
2016-10-24 12:48 GMT+03:00 <Ul...@t-systems.com>:
> Thank you for your answer - we have tried this before with no success.
>
> Uli
>
> -----Ursprüngliche Nachricht-----
> Von: Lachezar Dobrev [mailto:l.dobrev@gmail.com]
> Gesendet: Montag, 24. Oktober 2016 11:42
> An: users@activemq.apache.org
> Betreff: Re: Persistent messages and postgres backend
>
> You might want to add an INDEX on CONTAINER column in ACTIVEMQ_MSGS
> table:
> CREATE INDEX ACTIVEMQ_MSGS_CONTAINER_INDEX ON ACTIVEMQ_MSGS(CONTAINER);
>
> 2016-10-24 12:14 GMT+03:00 <Ul...@t-systems.com>:
>
> > Hi all,
> >
> > we need persistent messages with a postgres backend.
> >
> > We did some performance tests... and with about 70.000 Rows in
> > ACTIVEMQ_MSGS we saw postgres log entries with:
> >
> > duration: 15439.610 ms execute <unnamed>: SELECT ID, MSG FROM
> > ACTIVEMQ_MSGS WHERE CONTAINER=$1 ORDER BY ID
> > DETAIL: parameters: $1 = 'topic://Test.Foo2'
> >
> > That is: Postgres sees queries with 15 (and more) seconds responsetime.
> >
> > Ok, we see - our ActiveMQ is slow.
> >
> > Any ideas how to tune ActiveMQ and/or Postgres database here ?
> >
> > Uli
> >
> >
>
AW: Persistent messages and postgres backend
Posted by Ul...@t-systems.com.
Thank you for your answer - we have tried this before with no success.
Uli
-----Ursprüngliche Nachricht-----
Von: Lachezar Dobrev [mailto:l.dobrev@gmail.com]
Gesendet: Montag, 24. Oktober 2016 11:42
An: users@activemq.apache.org
Betreff: Re: Persistent messages and postgres backend
You might want to add an INDEX on CONTAINER column in ACTIVEMQ_MSGS table:
CREATE INDEX ACTIVEMQ_MSGS_CONTAINER_INDEX ON ACTIVEMQ_MSGS(CONTAINER);
2016-10-24 12:14 GMT+03:00 <Ul...@t-systems.com>:
> Hi all,
>
> we need persistent messages with a postgres backend.
>
> We did some performance tests... and with about 70.000 Rows in
> ACTIVEMQ_MSGS we saw postgres log entries with:
>
> duration: 15439.610 ms execute <unnamed>: SELECT ID, MSG FROM
> ACTIVEMQ_MSGS WHERE CONTAINER=$1 ORDER BY ID
> DETAIL: parameters: $1 = 'topic://Test.Foo2'
>
> That is: Postgres sees queries with 15 (and more) seconds responsetime.
>
> Ok, we see - our ActiveMQ is slow.
>
> Any ideas how to tune ActiveMQ and/or Postgres database here ?
>
> Uli
>
>
Re: Persistent messages and postgres backend
Posted by Lachezar Dobrev <l....@gmail.com>.
You might want to add an INDEX on CONTAINER column in ACTIVEMQ_MSGS table:
CREATE INDEX ACTIVEMQ_MSGS_CONTAINER_INDEX ON ACTIVEMQ_MSGS(CONTAINER);
2016-10-24 12:14 GMT+03:00 <Ul...@t-systems.com>:
> Hi all,
>
> we need persistent messages with a postgres backend.
>
> We did some performance tests... and with about 70.000 Rows in
> ACTIVEMQ_MSGS we saw postgres log entries with:
>
> duration: 15439.610 ms execute <unnamed>: SELECT ID, MSG FROM
> ACTIVEMQ_MSGS WHERE CONTAINER=$1 ORDER BY ID
> DETAIL: parameters: $1 = 'topic://Test.Foo2'
>
> That is: Postgres sees queries with 15 (and more) seconds responsetime.
>
> Ok, we see - our ActiveMQ is slow.
>
> Any ideas how to tune ActiveMQ and/or Postgres database here ?
>
> Uli
>
>