Re: [PERFORM] Strange problems with more memory.
If your nightly process is heavily read-only, then raid5 is probably fine. If however, there is a significant write component then it would perhaps be worth getting another disk and converting to raid10 (alternatively - see previous postings about raid cards with on-board cache). Are you seeing a lot of write activity? Note that it is possible for a SELECT only workload to generate significant write activity - if the resulting datasets are too large for memory sorting or hashing. I'm *guessing* that with an 11G database and 1G (or was that 2G?) of ram that it is possible to overflow whatever your sort_mem is set to. cheers Mark Stef wrote: Got 3 1 rpm SCSI raid5 on here. I doubt I will get much better than that without losing both arms and legs... ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] General performance problem!
Hi,
Make
multi-column indexes, using the columns from your most typical queries, putting
the most selective columns first (ie; you don't need to make indexes with
columns in the same order as they are used in the query).
For
instance, an index on cp, effectif could likely benefit both queries; same for
an index on cp, effectif, naf. (You'd need only one of these indexes I think,
not both. Experiment to find out which one gives you most benefit in your
queries, vs. the slowdown in inserts).
Perhaps some of the single-column keys can be
dropped.
-Original Message-From:
[EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]On Behalf Of olivier
HAROSent: dinsdag 17 augustus 2004 15:30To:
[EMAIL PROTECTED]Subject: [PERFORM] General
performance problem!
Hello,
I have a dedicated server for my
posgresql database :
P4 2.4 GHZ
HDD IDE 7200 rpm
512 DDR 2700
I have a problem whith one table
of my database :
CREATE SEQUENCE "base_aveugle_seq" START
1;CREATE TABLE "base_aveugle" ("record_id" integer DEFAULT
nextval('"base_aveugle_seq"'::text) NOT NULL,"dunsnumber" integer
NOT NULL,"cp" text NOT NULL,"tel" text NOT
NULL,"fax" text NOT NULL,"naf" text NOT
NULL,"siege/ets" text NOT NULL,"effectif" integer NOT
NULL,"ca" integer NOT NULL,Constraint "base_aveugle_pkey"
Primary Key ("record_id"));CREATE INDEX base_aveugle_dunsnumber_key ON
base_aveugle USING btree (dunsnumber);CREATE INDEX base_aveugle_cp_key ON
base_aveugle USING btree (cp);CREATE INDEX base_aveugle_naf_key ON
base_aveugle USING btree (naf);CREATE INDEX base_aveugle_effectif_key ON
base_aveugle USING btree (effectif);
This table contains 5 000 000
records
I have a PHP application which
often makes queries on this table (especially on the "cp","naf","effectif"
fields)
Querries are like :
select (distint cp) from base_aveugle where cp='201A' and effectif between 1
and 150
select (*) from base_aveugle where naf in ('721A','213F','421K') and cp in
('54210','21459','201A') and effectif 150
I think it is possible to
optimize the performance of this queries before changing thehardware (I
now I will...) but I don't know how, even after having read lot of things
about postgresql ...
Thanks ;)
---Outgoing mail is
certified Virus Free.Checked by AVG anti-virus system (http://www.grisoft.com).Version: 6.0.737
/ Virus Database: 491 - Release Date:
11/08/2004
[PERFORM] Postgres does not utilize indexes. Why?
Hi everybody!
I cant make use of indexes even I tried the same test by changing different settings
in
postgres.conf like geqo to off/on geqo related parameters, enable_seqscan off/on
so on. Result
is the same.
Here is test itself:
Ive created simplest table test and executed the same statement explain analyze
select id from
test where id = 5; Few times I added 100,000 records, applied vacuum full; and
issued above
explain command.
Postgres uses sequential scan instead of index one.
Of cause Time to execute the same statement constantly grows. In my mind index should
not allow
time to grow so much.
Why Postgres does not utilizes primary unique index?
What Im missing? It continue growing even there are 1,200,000 records. It should at
least start
using index at some point.
Details are below:
100,000 records:
QUERY PLAN
Seq Scan on test (cost=0.00..2427.00 rows=2 width=8) (actual time=99.626..199.835
rows=1
loops=1)
Filter: (id = 5)
Total runtime: 199.990 ms
200,000 records:
QUERY PLAN
-
Seq Scan on test (cost=0.00..4853.00 rows=2 width=8) (actual time=100.389..402.770
rows=1
loops=1)
Filter: (id = 5)
Total runtime: 402.926 ms
300,000 records:
QUERY PLAN
-
Seq Scan on test (cost=0.00..7280.00 rows=1 width=8) (actual time=100.563..616.064
rows=1
loops=1)
Filter: (id = 5)
Total runtime: 616.224 ms
(3 rows)
I've created test table by script:
CREATE TABLE test
(
id int8 NOT NULL DEFAULT nextval('next_id_seq'::text) INIQUE,
description char(50),
CONSTRAINT users_pkey PRIMARY KEY (id)
);
CREATE SEQUENCE next_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 100
START 1
CACHE 5
CYCLE;
I use postgres 7.4.2
=
Thanks a lot
Igor Artimenko
I specialize in
Java, J2EE, Unix, Linux, HP, AIX, Solaris, Progress, Oracle, DB2, Postgres, Data
Modeling
__
Do you Yahoo!?
New and Improved Yahoo! Mail - Send 10MB messages!
http://promotions.yahoo.com/new_mail
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Postgres does not utilize indexes. Why?
Artimenko Igor wrote:
id int8 NOT NULL DEFAULT nextval('next_id_seq'::text) INIQUE,
ID column is bigint, but '5' is int, therefore the index does not
match. You need to cast your clause like this:
select id from test where id = 5::int8
Also, issue VACUUM ANALYZE, so Postgres knows about the structure of the
data.
--
Michal Taborsky
http://www.taborsky.cz
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] General performance problem!
An index
on cp and effectif would help your first query. An index on naf, cp and effectif would help your second
query.
Something
like this:
CREATE INDEX base_aveugle_cp_key2 ON
base_aveugle USING btree (cp, effectif);
CREATE INDEX base_aveugle_naf_key2 ON base_aveugle USING btree (naf, cp,
effectif);
Another thing, why include distinct cp
when you are only selecting cp=201A?
You will only retrieve one record regardless of how many may contain cp=201A.
If you could make these UNIQUE indexes that
would help also but its not a requirement.
Good luck,
Duane
-Original
Message-
From: olivier HARO
[mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 17, 2004
6:30 AM
To:
[EMAIL PROTECTED]
Subject: [PERFORM] General
performance problem!
Hello,
I
have a dedicated server for my posgresql database :
P4
2.4 GHZ
HDD
IDE 7200 rpm
512
DDR 2700
I
have a problem whith one table of my database :
CREATE
SEQUENCE base_aveugle_seq START 1;
CREATE TABLE base_aveugle (
record_id integer DEFAULT
nextval('base_aveugle_seq'::text) NOT NULL,
dunsnumber integer NOT NULL,
cp text NOT NULL,
tel text NOT NULL,
fax text NOT NULL,
naf text NOT NULL,
siege/ets text NOT NULL,
effectif integer NOT NULL,
ca integer NOT NULL,
Constraint base_aveugle_pkey Primary Key
(record_id)
);
CREATE INDEX base_aveugle_dunsnumber_key ON base_aveugle USING btree
(dunsnumber);
CREATE INDEX base_aveugle_cp_key ON base_aveugle USING btree (cp);
CREATE INDEX base_aveugle_naf_key ON base_aveugle USING btree (naf);
CREATE INDEX base_aveugle_effectif_key ON base_aveugle USING btree (effectif);
This
table contains 5 000 000 records
I
have a PHP application which often makes queries on this table (especially on
the cp,naf,effectif fields)
Querries
are like :
select (distint cp) from base_aveugle where cp='201A' and effectif between 1
and 150
select (*) from base_aveugle where naf in ('721A','213F','421K') and cp in
('54210','21459','201A') and effectif 150
I
think it is possible to optimize the performance of this queries before
changing thehardware (I now I will...) but I don't know how, even after
having read lot of things about postgresql ...
Thanks
;)
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.737 / Virus Database: 491 - Release Date: 11/08/2004
Re: [PERFORM] Postgres does not utilize indexes. Why?
test where id = 5; Few times I added 100,000 records, applied cast the 5 to int8 and it will use the index ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[PERFORM] Why is the number of dead tuples causing the performance of deferred triggers to degrading so rapidly (exponentionally)?
Hi, I'm seeing the following behaviour with the table and functions given below: db=# insert into f select * from full_sequence(1, 1000); INSERT 0 1000 Time: 197,507 ms db=# insert into f select * from full_sequence(1, 1000); INSERT 0 1000 Time: 341,880 ms db=# insert into f select * from full_sequence(1, 1000); INSERT 0 1000 Time: 692,603 ms db=# insert into f select * from full_sequence(1, 1000); INSERT 0 1000 Time: 985,253 ms db=# insert into f select * from full_sequence(1, 1000); INSERT 0 1000 Time: 1241,334 ms Or even worse (fresh drop/create of the table and functions): db=# insert into f select id from full_sequence(1, 1); INSERT 0 1 Time: 22255,767 ms db=# insert into f select id from full_sequence(1, 1); INSERT 0 1 Time: 45398,433 ms db=# insert into f select id from full_sequence(1, 1); INSERT 0 1 Time: 67993,476 ms Wrapping the commands in a transaction only accumulates the penalty at commit. It seems in this case the time needed for a single deferred trigger somehow depends on the number of dead tuples in the table, because a vacuum of the table will 'reset' the query-times. However, even if I wanted to, vacuum is not allowed from within a function. What is happening here? And more importantly, what can I do to prevent this? NB. My real-world application 'collects' id's in need for deferred work, but this work is both costly and only needed once per base record. So I use an 'update' table whose content I join with the actual tables in order to do the work for _all_ the base records involved upon the first execution of the deferred trigger. At the end of the trigger, this 'update' table is emptied so any additional deferred triggers on the same table will hardly lose any time. Or at least, that was the intention *** demo script *** drop table f cascade; drop function tr_f_def() cascade; drop function full_sequence(integer, integer); drop type full_sequence_type; create table f (id int); create function tr_f_def() RETURNS trigger LANGUAGE 'plpgsql' STABLE STRICT SECURITY INVOKER AS ' DECLARE BEGIN -- do stuff with all the ids in the table -- delete the contents -- delete from f; IF EXISTS (SELECT 1 FROM f) THEN DELETE FROM F; VACUUM F; END IF; RETURN NULL; END;'; create type full_sequence_type as (id int); create function full_sequence(integer, integer) RETURNS SETOF full_sequence_type LANGUAGE 'plpgsql' IMMUTABLE STRICT SECURITY INVOKER AS 'DECLARE my_from ALIAS FOR $1; my_to ALIAS FOR $2; result full_sequence_type%ROWTYPE; BEGIN -- just loop FOR i IN my_from..my_to LOOP result.id = i; RETURN NEXT result; END LOOP; -- finish RETURN; END;'; CREATE CONSTRAINT TRIGGER f_def AFTER INSERT ON f DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE tr_f_def(); *** demo script *** db=# select version(); version - PostgreSQL 7.4.3 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66 -- Best, Frank. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[PERFORM] Why is the number of dead tuples causing the performance of deferred triggers to degrading so rapidly (exponentionally)?
Obviously, this part of tr_f_def(): ** -- delete the contents -- delete from f; IF EXISTS (SELECT 1 FROM f) THEN DELETE FROM F; VACUUM F; END IF; ** should simply read: ** -- delete the contents delete from f; ** -- Best, Frank. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Why is the number of dead tuples causing the performance of deferred triggers to degrading so rapidly (exponentionally)?
Frank, It seems in this case the time needed for a single deferred trigger somehow depends on the number of dead tuples in the table, because a vacuum of the table will 'reset' the query-times. However, even if I wanted to, vacuum is not allowed from within a function. What is happening here? And more importantly, what can I do to prevent this? I'm not clear on all of the work you're doing in the trigger. However, it seems obvious that you're deleting and/or updating a large number of rows. The escalating execution times would be consistent with that. NB. My real-world application 'collects' id's in need for deferred work, but this work is both costly and only needed once per base record. So I use an 'update' table whose content I join with the actual tables in order to do the work for _all_ the base records involved upon the first execution of the deferred trigger. At the end of the trigger, this 'update' table is emptied so any additional deferred triggers on the same table will hardly lose any time. Or at least, that was the intention I think you're doing a lot more than is wise to do in triggers. Deferrable triggers aren't really intended for running long procedures with the creation of types and temporary tables (your post got a bit garbled, so pardon me if I'm misreading it). I'd suggest reconsidering your approach to this application problem. At the very least, increase max_fsm_relations to some high value, which may help (or not). -Josh -- __Aglio Database Solutions___ Josh BerkusConsultant [EMAIL PROTECTED]www.agliodbs.com Ph: 415-752-2500Fax: 415-752-2387 2166 Hayes Suite 200San Francisco, CA ---(end of broadcast)--- TIP 8: explain analyze is your friend
[PERFORM] postgresql performance with multimedia
Hi, I am working on a project which explore postgresql to store multimedia data. In details, i am trying to work with the buffer management part of postgres source code. And try to improve the performance. I had search on the web but could not find much usefull information. It would be great if anyone knows any developer groups that working on similar things ? or where can i find more information on this issue? Thank you very much for your help regards, MT Ho __ Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
