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 <ulrich.her...@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.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 > > > > > > > > >
