[PERFORM] The trigger can be specified to fire on time condition?
Hi All, I ' m using the postgresql datbase to stores cookies. Theses cookies become invalid after 30 mn and have to be deleted. i have defined a procedure that will delete all invalid cookies, but i don't know how to call it in loop way (for example each hour). I think that it possible because this behaivor is the same of the autovaccum procedure that handle the vaccum process every time (60s in default way). After reading the documentation, it seems that triggers can't handle this stuff . how can i resolve the problem ? Thanks begin:vcard fn:Jamal Ghaffour n:Ghaffour;Jamal org:ELIOS Informatique adr;quoted-printable:;;1, sq de ch=C3=AAne Germain,;CESSON SEVIGNE;;35510;FRANCE email;internet:[EMAIL PROTECTED] tel;work:(+33) 2.99.63.85.30 tel;fax:(+33) 2.99.63.85.93 tel;home:(+33) 2 99 36 73 96 tel;cell:(+33) 6.21.85.15.91 url:http://www.elios-informatique.fr version:2.1 end:vcard ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Please Help: PostgreSQL performance Optimization
Andrew Lazarus a écrit : Jamal Ghaffour wrote: CREATE TABLE cookies ( domain varchar(50) NOT NULL, path varchar(50) NOT NULL, name varchar(50) NOT NULL, principalid varchar(50) NOT NULL, host text NOT NULL, value text NOT NULL, secure bool NOT NULL, timestamp timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP+TIME '04:00:00', PRIMARY KEY (domain,path,name,principalid) ) [snip] SELECT path, upper(name) AS name, value FROM cookies WHERE timestampCURRENT_TIMESTAMP AND principalid='192.168.8.219' AND secure=FALSE AND (domain='ping.icap-elios.com' OR domain='.icap-elios.com') I think the problem here is that the column order in the index doesn't match the columns used in the WHERE clause criteria. Try adding an index on (domain,principalid) or (domain,principalid,timestamp). If these are your only queries, you can get the same effect by re-ordering the columns in the table so that this is the column order used by the primary key and its implicit index. You should check up on EXPLAIN and EXPLAIN ANALYZE to help you debug slow queries. Hi, I created an index into the cookies table CREATE INDEX index_cookies_select ON cookies (domain, principalid, timestamp); and execute my UPDATE and select queries: 1 - The first select quey give the following results: icap=# EXPLAIN ANALYZE SELECT path, upper(name) AS name, value FROM cookies WHERE timestampCURRENT_TIMESTAMP AND principalid='192.168.8.219' AND secure=FALSE AND (domain='ping.icap-elios.com' OR domain='.icap-elios.com'); QUERY PLAN Bitmap Heap Scan on cookies (cost=4.02..8.04 rows=1 width=268) (actual time=0.107..0.108 rows=1 loops=1) Recheck Cond: domain)::text = 'ping.icap-elios.com'::text) AND ((principalid)::text = '192.168.8.219'::text) AND (timestamp now())) OR (((domain)::text = '.icap-elios.com'::text) AND ((principalid)::text = '192.168.8.219'::text) AND (timestamp now( Filter: ((timestamp now()) AND (NOT secure)) - BitmapOr (cost=4.02..4.02 rows=1 width=0) (actual time=0.091..0.091 rows=0 loops=1) - Bitmap Index Scan on index_cookies_select (cost=0.00..2.01 rows=1 width=0) (actual time=0.077..0.077 rows=1 loops=1) Index Cond: (((domain)::text = 'ping.icap-elios.com'::text) AND ((principalid)::text = '192.168.8.219'::text) AND (timestamp now())) - Bitmap Index Scan on index_cookies_select (cost=0.00..2.01 rows=1 width=0) (actual time=0.012..0.012 rows=0 loops=1) Index Cond: (((domain)::text = '.icap-elios.com'::text) AND ((principalid)::text = '192.168.8.219'::text) AND (timestamp now())) Total runtime: 0.155 ms (9 rows) 2- After that, i launch my test code that execute continuely the UPDATE and select queries (in loop manner), after 1 minute of continuous execution, i obtain the following result: icap=# EXPLAIN ANALYZE SELECT path, upper(name) AS name, value FROM cookies WHERE timestampCURRENT_TIMESTAMP AND principalid='192.168.8.219' AND secure=FALSE AND (domain='ping.icap-elios.com' OR domain='.icap-elios.com'); QUERY PLAN Bitmap Heap Scan on cookies (cost=4.02..8.04 rows=1 width=268) (actual time=39.545..39.549 rows=1 loops=1) Recheck Cond: domain)::text = 'ping.icap-elios.com'::text) AND ((principalid)::text = '192.168.8.219'::text) AND (timestamp now())) OR (((domain)::text = '.icap-elios.com'::text) AND ((principalid)::text = '192.168.8.219'::text) AND (timestamp now( Filter: ((timestamp now()) AND (NOT secure)) - BitmapOr (cost=4.02..4.02 rows=1 width=0) (actual time=39.512..39.512 rows=0 loops=1) - Bitmap Index Scan on index_cookies_select (cost=0.00..2.01 rows=1 width=0) (actual time=39.471..39.471 rows=2 loops=1) Index Cond: (((domain)::text = 'ping.icap-elios.com'::text) AND ((principalid)::text = '192.168.8.219'::text) AND (timestamp now())) - Bitmap Index Scan
Re: [PERFORM] Please Help: PostgreSQL performance Optimization
Jamal Ghaffour a crit: Hi, I'm working on a project, whose implementation deals with PostgreSQL. A brief description of our application is given below. I'm running version 8.0 on a dedicated server 1Gb of RAM. my database isn't complex, it contains just 2 simple tables. CREATE TABLE cookies ( domain varchar(50) NOT NULL, path varchar(50) NOT NULL, name varchar(50) NOT NULL, principalid varchar(50) NOT NULL, host text NOT NULL, value text NOT NULL, secure bool NOT NULL, timestamp timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP+TIME '04:00:00', PRIMARY KEY (domain,path,name,principalid) ) CREATE TABLE liberty ( principalid varchar(50) NOT NULL, requestid varchar(50) NOT NULL, spassertionurl text NOT NULL, libertyversion varchar(50) NOT NULL, relaystate varchar(50) NOT NULL, PRIMARY KEY (principalid) ) I'm developping an application that uses the libpqxx to execute psql queries on the database and have to execute 500 requests at the same time. UPDATE cookies SET host='ping.icap-elios.com', value= '54E5B5491F27C0177083795F2E09162D', secure=FALSE, timestamp=CURRENT_TIMESTAMP+INTERVAL '14400 SECOND' WHERE domain='ping.icap-elios.com' AND path='/tfs' AND principalid='192.168.8.219' AND name='jsessionid' SELECT path, upper(name) AS name, value FROM cookies WHERE timestampCURRENT_TIMESTAMP AND principalid='192.168.8.219' AND secure=FALSE AND (domain='ping.icap-elios.com' OR domain='.icap-elios.com') I have to notify that the performance of is extremely variable and irregular. I can also see that the server process uses almost 100% of a CPU. I'm using the default configuration file, and i m asking if i have to change some paramters to have a good performance. Any help would be greatly appreciated. Thanks, Hi, There are some results that can give you concrete idea about my problem: when i 'm launching my test that executes in loop manner the SELECT and UPDATE queries described above, i'm obtaining this results: UPDATE Time execution :0s: 5225 us SELECT Time execution :0s: 6908 us 5 minutes Later: UPDATE Time execution :0s: 6125 us SELECT Time execution :0s: 10928 us 5 minutes Later: UPDATE Time execution :0s: 5825 us SELECT Time execution :0s: 14978 us As you can see , the time execution of the SELECT request is growing relatively to time and not the UPDATE time execution. I note that to stop the explosion of the Select time execution, i m using frequently the vaccum query on the cookies table. Set the autovacuum parmaeter in the configuation file to on wasn't able to remplace the use of the vaccum command, and i don't know if this behaivour is normal? Thanks, Jamal begin:vcard fn:Jamal Ghaffour n:Ghaffour;Jamal org:ELIOS Informatique adr;quoted-printable:;;1, sq de ch=C3=AAne Germain,;CESSON SEVIGNE;;35510;FRANCE email;internet:[EMAIL PROTECTED] tel;work:(+33) 2.99.63.85.30 tel;fax:(+33) 2.99.63.85.93 tel;home:(+33) 2 99 36 73 96 tel;cell:(+33) 6.21.85.15.91 url:http://www.elios-informatique.fr version:2.1 end:vcard ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PERFORM] Please Help: PostgreSQL performance Optimization
Hi, I'm working on a project, whose implementation deals with PostgreSQL. A brief description of our application is given below. I'm running version 8.0 on a dedicated server 1Gb of RAM. my database isn't complex, it contains just 2 simple tables. CREATE TABLE cookies ( domain varchar(50) NOT NULL, path varchar(50) NOT NULL, name varchar(50) NOT NULL, principalid varchar(50) NOT NULL, host text NOT NULL, value text NOT NULL, secure bool NOT NULL, timestamp timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP+TIME '04:00:00', PRIMARY KEY (domain,path,name,principalid) ) CREATE TABLE liberty ( principalid varchar(50) NOT NULL, requestid varchar(50) NOT NULL, spassertionurl text NOT NULL, libertyversion varchar(50) NOT NULL, relaystate varchar(50) NOT NULL, PRIMARY KEY (principalid) ) I'm developping an application that uses the libpqxx to execute psql queries on the database and have to execute 500 requests at the same time. UPDATE cookies SET host='ping.icap-elios.com', value= '54E5B5491F27C0177083795F2E09162D', secure=FALSE, timestamp=CURRENT_TIMESTAMP+INTERVAL '14400 SECOND' WHERE domain='ping.icap-elios.com' AND path='/tfs' AND principalid='192.168.8.219' AND name='jsessionid' SELECT path, upper(name) AS name, value FROM cookies WHERE timestampCURRENT_TIMESTAMP AND principalid='192.168.8.219' AND secure=FALSE AND (domain='ping.icap-elios.com' OR domain='.icap-elios.com') I have to notify that the performance of is extremely variable and irregular. I can also see that the server process uses almost 100% of a CPU. I'm using the default configuration file, and i m asking if i have to change some paramters to have a good performance. Any help would be greatly appreciated. Thanks,
