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: [email protected]
Internet: http://www.t-systems.de
Die gesetzlichen Pflichtangaben finden Sie unter:
http://www.t-systems.de/pflichtangaben
-----Ursprüngliche Nachricht-----
Von: [email protected] [mailto:[email protected]] 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" <[email protected]> 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 <[email protected]>:
> Thank you for your answer - we have tried this before with no success.
>
> Uli
>
> -----Ursprüngliche Nachricht-----
> Von: Lachezar Dobrev [mailto:[email protected]]
> Gesendet: Montag, 24. Oktober 2016 11:42
> An: [email protected]
> 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 <[email protected]>:
>
> > 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
> >
> >
>