PostgreSQL performance issues
От | Willo van der Merwe |
---|---|
Тема | PostgreSQL performance issues |
Дата | |
Msg-id | 44F446B2.2090900@studentvillage.co.za обсуждение исходный текст |
Ответы |
Re: PostgreSQL performance issues
Re: PostgreSQL performance issues Re: PostgreSQL performance issues Re: PostgreSQL performance issues Re: PostgreSQL performance issues Re: PostgreSQL performance issues |
Список | pgsql-performance |
Hi,
We're running PostgreSQL 8.1.4 on CentOS 4 (Linux version 2.6.9-34.0.1.ELsmp). Hardware specs:
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
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
В списке pgsql-performance по дате отправления: