|
Hi, We're running PostgreSQL 8.1.4 on CentOS 4 (Linux version 2.6.9-34.0.1.ELsmp). Hardware specs: 2x AMD Dual-Core Opteron 270 Italy 1Ghz HT 2 x 1MB L2 Cache Socket 940 4 GB Registered ECC PC3200 DDR RAM SuperMicro Server-Class 1U AS1020S series system Dual-channel Ultra320 SCSI controller 1 x 73 GB 10,000rpm Ultra320 SCSI drive with 8MB cacheI use it to drive a web application. Everything was working fine when all of a sudden today, things went belly up. Load on the server started increasing and query speeds decreased rapidly. After dropping all the clients I did some quick tests and found the following: I have a log table looking like this: Table "public.log" Column | Type | Modifiers ---------+-----------------------------+--------------------------------- site | bigint | not null stamp | timestamp without time zone | default now() type | character(8) | not null default 'log'::bpchar user | text | not null default 'public'::text message | text | Indexes: "fki_log_sites" btree (site) "ix_log_stamp" btree (stamp) "ix_log_type" btree ("type") "ix_log_user" btree ("user") Foreign-key constraints: "log_sites" FOREIGN KEY (site) REFERENCES sites(id) ON UPDATE CASCADE ON DELETE CASCADE and it has 743321 rows and a explain analyze select count(*) from property_values; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=55121.95..55121.96 rows=1 width=0) (actual time=4557.797..4557.798 rows=1 loops=1) -> Seq Scan on property_values (cost=0.00..51848.56 rows=1309356 width=0) (actual time=0.026..2581.418 rows=1309498 loops=1) Total runtime: 4557.978 ms (3 rows) 4 1/2 seconds for a count(*) ? This seems a bit rough - is there anything else I can try to optimize my Database? You can imagine that slightly more complex queries goes out the roof. Any help appreciated Regards Willo van der Merwe |
