From: Dan Harris <fbsd@drivefaster.net>
To: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Simple query showing 270 hours of CPU time
Date: Fri, 20 Jul 2007 09:43:50 -0600
Message-ID: <46A0D836.5090806@drivefaster.net> (raw)
Today, I looked at 'top' on my PG server and saw a pid that reported 270 hours
of CPU time. Considering this is a very simple query, I was surprised to say
the least. I was about to just kill the pid, but I figured I'd try and see
exactly what it was stuck doing for so long.
Here's the strace summary as run for a few second sample:
% time seconds usecs/call calls errors syscall
------ ----------- ----------- --------- --------- ----------------
97.25 0.671629 92 7272 semop
1.76 0.012171 406 30 recvfrom
0.57 0.003960 66 60 gettimeofday
0.36 0.002512 28 90 sendto
0.05 0.000317 10 32 lseek
0.01 0.000049 1 48 select
------ ----------- ----------- --------- --------- ----------------
100.00 0.690638 7532 total
Here's the query:
select id from eventkeywords where word = '00003322'
If I run the query manually, it completes in about 500ms, which is very reasonable.
There are 408563 rows in this table. I just noticed there is no index on word (
there should be! ). Would this have caused the problem?
This is 8.0.12
Linux sunrise 2.6.15-26-amd64-server #1 SMP Fri Sep 8 20:33:15 UTC 2006 x86_64
GNU/Linux
Any idea what might have set it into this loop?
-Dan
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: fbsd@drivefaster.net
Subject: Re: Simple query showing 270 hours of CPU time
In-Reply-To: <46A0D836.5090806@drivefaster.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox