pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSlow SELECT on small table
2+ messages / 2 participants
[nested] [flat]
* Slow SELECT on small table
@ 2010-11-22 08:59 Martin Boese <boesemar@gmx.de>
0 siblings, 1 reply; 2+ messages in thread
From: Martin Boese @ 2010-11-22 08:59 UTC (permalink / raw)
To: pgsql-performance
Hi,
I am using Postgresql: 9.01, PostGIS 1.5 on FreeBSD 7.0. I have at
least one table on which SELECT's turn terribly slow from time to time.
This happened at least three times, also on version 8.4.
The table has only ~1400 rows. A count(*) takes more than 70 seconds.
Other tables are fast as usual.
When this happens I can also see my system's disks are suffering.
'systat -vm' shows 100% disk load at ~4MB/sec data rates.
A simple VACUUM does *not* fix it, a VACUUM FULL however does. See the
textfile attached.
My postgresql.conf is untouched as per distribution.
Can someone hint me how I can troubleshoot this problem?
Thanks!
Martin
Attachments:
postgresql-log.txt (text/plain, 5.9K) download
mbms=# select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 9.0.1 on amd64-portbld-freebsd7.0, compiled by GCC cc (GCC) 4.2.1 20070719 [FreeBSD], 64-bit
(1 row)
mbms=# EXPLAIN ANALYZE select count(*) from circuit;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=22213.20..22213.21 rows=1 width=0) (actual time=72847.131..72847.133 rows=1 loops=1)
-> Seq Scan on circuit (cost=0.00..22209.56 rows=1456 width=0) (actual time=15527.799..72844.633 rows=1456 loops=1)
Total runtime: 72922.484 ms
(3 rows)
mbms=# VACUUM FULL VERBOSE ANALYZE ;
(..snip..)
INFO: vacuuming "public.circuit"
INFO: analyzing "public.circuit"
INFO: "circuit": scanned 41 of 41 pages, containing 1456 live rows and 0 dead rows; 1456 rows in sample, 1456 estimated total rows
VACUUM
mbms=# EXPLAIN ANALYZE select count(*) from circuit;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Aggregate (cost=59.20..59.21 rows=1 width=0) (actual time=4.416..4.418 rows=1 loops=1)
-> Seq Scan on circuit (cost=0.00..55.56 rows=1456 width=0) (actual time=0.013..2.302 rows=1456 loops=1)
Total runtime: 4.470 ms
(3 rows)
mbms=# \d circuit
Table "public.circuit"
Column | Type | Modifiers
-----------------------------+-----------------------------+------------------------------------------------------
id | integer | not null default nextval('circuit_id_seq'::regclass)
router_id | integer |
customer_id | integer |
site | character varying(256) |
service | character varying(48) |
last_ifdescr | character varying(256) |
last_ifalias | character varying(256) |
bandwidth_ul | integer | default 0
bandwidth_dl | integer | default 0
last_seen | timestamp without time zone |
last_ifindex | integer | default 0
termination_point_id | integer |
last_adminstatus | smallint | default 1
status | character(1) | default 'A'::bpchar
location | geometry |
circuit_type | character(1) | default 'R'::bpchar
p_local | character varying(256) |
p_remote | character varying(256) |
p_technology | character varying(64) |
x_provider | character varying(256) |
x_provider_reference | character varying(128) |
termination_point_reference | character varying(64) |
is_monitored | boolean | default false
temp_config | boolean | default false
activation_date | timestamp without time zone |
s_last_did | character varying(64) |
td_cache_class | character varying(128) |
td_cache_id | integer |
td_cache_lastupdate | timestamp without time zone |
commercial_service_id | integer |
Indexes:
"circuit_id_key" UNIQUE, btree (id)
"id_circuit_status" btree (status)
"idx_circuit_customer_id" btree (customer_id)
"idx_circuit_site" btree (site)
Check constraints:
"enforce_dims_location" CHECK (ndims(location) = 2)
"enforce_geotype_location" CHECK (geometrytype(location) = 'POINT'::text OR location IS NULL)
"enforce_srid_location" CHECK (srid(location) = 4326)
Foreign-key constraints:
"circuit_commercial_service_id_fkey" FOREIGN KEY (commercial_service_id) REFERENCES commercial_services(id) ON UPDATE CASCADE ON DELETE CASCADE
"circuit_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(id) ON UPDATE CASCADE ON DELETE CASCADE
"circuit_router_id_fkey" FOREIGN KEY (router_id) REFERENCES router(id) ON UPDATE CASCADE ON DELETE CASCADE
"circuit_termination_point_id_fkey" FOREIGN KEY (termination_point_id) REFERENCES termination_point(id) ON UPDATE CASCADE ON DELETE SET NULL
Referenced by:
TABLE "call_log" CONSTRAINT "call_log_circuit_id_fkey" FOREIGN KEY (circuit_id) REFERENCES circuit(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "circuit_states" CONSTRAINT "circuit_states_circuit_id_fkey" FOREIGN KEY (circuit_id) REFERENCES circuit(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "circuit_traffic" CONSTRAINT "circuit_traffic_circuit_id_fkey" FOREIGN KEY (circuit_id) REFERENCES circuit(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "cpe" CONSTRAINT "cpe_circuit_id_fkey" FOREIGN KEY (circuit_id) REFERENCES circuit(id) ON UPDATE CASCADE ON DELETE SET NULL
TABLE "ip_networks" CONSTRAINT "ip_networks_circuit_id_fkey" FOREIGN KEY (circuit_id) REFERENCES circuit(id) ON UPDATE CASCADE ON DELETE SET NULL
TABLE "monitoring_settings" CONSTRAINT "monitoring_settings_circuit_id_fkey" FOREIGN KEY (circuit_id) REFERENCES circuit(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "stock" CONSTRAINT "stock_circuit_id_fkey" FOREIGN KEY (circuit_id) REFERENCES circuit(id) ON UPDATE CASCADE ON DELETE RESTRICT
* Re: Slow SELECT on small table
@ 2010-11-22 15:11 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Martin Boese <boesemar@gmx.de>
0 siblings, 0 replies; 2+ messages in thread
From: Kevin Grittner @ 2010-11-22 15:11 UTC (permalink / raw)
To: Martin Boese <boesemar@gmx.de>; pgsql-performance
Martin Boese <boesemar@gmx.de> wrote:
> The table has only ~1400 rows. A count(*) takes more than 70
> seconds. Other tables are fast as usual.
>
> When this happens I can also see my system's disks are suffering.
> 'systat -vm' shows 100% disk load at ~4MB/sec data rates.
>
> A simple VACUUM does *not* fix it, a VACUUM FULL however does. See
> the textfile attached.
This is almost certainly a result of bloat on this table.
Autovacuum should normally protect you from that, but there are a
few things which can prevent it from doing so, like long-running
transactions or repeated updates against the entire table in a short
time. There has also been a bug found recently which, as I
understand it, can cause autovacuum to become less aggressive over
time, which might possibly contribute to this sort of problem.
You appear to have snipped the portion of the vacuum output which
might have confirmed and quantified the problem. If you get into
this state again, the entire output of this would be informative:
VACUUM VERBOSE public.circuit;
The goal would be to try to prevent the bloat in the first place so
that you don't need to use aggressive maintenance like VACUUM FULL
to recover. Manual vacuums or tweaking the autovacuum parameters
may help. Also, keep an eye out for maintenance releases for 9.0;
there's likely to be a fix coming which will help you with this.
-Kevin
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2010-11-22 15:11 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-11-22 08:59 Slow SELECT on small table Martin Boese <boesemar@gmx.de>
2010-11-22 15:11 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
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