activemq-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From <Ulrich.Her...@t-systems.com>
Subject AW: Persistent messages and postgres backend
Date Tue, 25 Oct 2016 11:30:33 GMT
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.dobrev@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.Herbst@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 <Ulrich.Herbst@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
> >
> >
>
Mime
View raw message