Dear archiveopteryx users,
currently we see a problem at our Archiveopteryx 3.2.0 installation:
more and more postgres queries from Aox queue up and sooner or later
cause resources to be too scarce for other services.
The queries are all alike, here one example with a bit context from the
log file:
debug: 2/5801/3/172/6: 2015-05-08 10:53:01.226 Sent parse/execute for
"select mm.uid, mm.modseq, mm.message
from mailbox_messages mm
where mm.mailbox=$1 and mm.uid=any($2) and not mm.deleted"
($1='441',$2='{16480,16481'... (2.9Mb)) on backend 32
The queries continue to run and use a whole CPU core each even after the
aox process is gone. Postgres seems to get a SIGPIPE but continues anyway.
Not all of these queries stay forever, though. One lasted for about 10
minutes. See below for more details on this example.
At the moment we only have the desperate workaround to do
select pg_cancel_backend(procpid)
with the leftover queries from time to time.
Any other advice?
Details:
Our biggest mailbox has 441407 messages.
db-address = "/var/run/postgresql/.s.PGSQL.5432"
PostgreSQL 9.1.13-0wheezy1
Linux 3.2 x86_64 amd64
More context from the logfile for one such query:
debug: 2/5801: 2015-05-08 10:52:57.793 Sent parse/execute for "insert
into connections
(username,address,port,mechanism,authfailures,syntaxerrors,started_at,ended_at,userid)
values ($1,$2,$3,$4,$5,$6,$7
::interval + 'epoch'::timestamptz,$8::interval +
'epoch'::timestamptz,$9)"
($1='ANONYMIZED',$2='10.122.3.108',$3='55169',$4='IMAP
login',$5='0',$6='0',$7='1431074540',$8='1431075177',$9='3') on backend 32
significant: 2/5801/3/172: 2015-05-08 10:52:57.992 Authenticated as
ANONYMIZED using IMAP login
debug: 2/5801/3/172/2: 2015-05-08 10:52:58.021 First line: 2 ID ("name"
"iPad Mail" "version" "12F69" "os" "iOS" "os-version" "8.3 (12F69)")
info: 2/5801/3/172/6: 2015-05-08 10:53:00.632 IMAP Command: 5 uid SEARCH
debug: 2/5801/3/172/6: 2015-05-08 10:53:00.632 First line: 5 UID SEARCH
RETURN (COUNT) 1:* NOT DELETED
info: 2/5801/3/172/6: 2015-05-08 10:53:00.635 Search for (16480:457904
and not set of flags contains \deleted)
debug: 2/5801/3/172/6: 2015-05-08 10:53:00.635 Executing
debug: 2/5801/3/172/6: 2015-05-08 10:53:00.635 Search considered 0 of
441425 messages using cache
debug: 2/5801/3/172/6: 2015-05-08 10:53:01.226 Sent parse/execute for
"select mm.uid, mm.modseq, mm.message from mailbox_messages mm where
mm.mailbox=$1 and mm.uid=any($2) and not mm.deleted"
($1='441',$2='{16480,16481'... (2.9Mb)) on backend 32
info: 2/5801/3/172/6: 2015-05-08 10:55:00.652 Execution time 120017ms
debug: 2/5801/3/172/6: 2015-05-08 10:55:00.652 Finished
debug: 2/5801/3/172/6: 2015-05-08 10:55:00.652 Retired
info: 2/5801/3/172/6: 2015-05-08 10:55:00.652 5 NO Unexpected close by
client
error: 2/5801/3/172/6: 2015-05-08 11:03:34.216 Database connection shutdown
thanks in advance,
--
Freundliche Grüße,
Klaus Thorn
------------------------------------------------------------------------
easydb - manage your data
Systemadministrator
Programmfabrik GmbH | Schwedter Straße 9B | 10119 Berlin | Germany
fon: +49-(0)30-40 50 579-0 | fax: +49-(0)30-40 50 579-19
www.programmfabrik.de | www.easydb.de
HINWEIS: Dies ist eine vertrauliche Nachricht und nur für den Adressaten
bestimmt. Es ist nicht erlaubt, diese Nachricht zu kopieren oder Dritten
zugänglich zu machen. Sollten Sie irrtümlich diese Nachricht erhalten
haben, bitte ich um Ihre Mitteilung per E-Mail oder unter der oben
angegebenen Telefonnummer.
