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, <ulrich.her...@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.her...@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: tbai...@gmail.com [mailto:tbai...@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.dob...@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 <ulrich.her...@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.dob...@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 <ulrich.her...@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
> >
> >
>
