On 1/12/06, Jamal Ghaffour <Jamal.Ghaffour@xxxxxxxxxxxxxxxxxxxxx> wrote: > 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 timestamp<CURRENT_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 > > please execute EXPLAIN ANALYZE <your query> and show the results, is the only way to know what's happening -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;)
