Обсуждение: Please Help: PostgreSQL performance Optimization
<pre style="margin: 0em;">Hi, <font face="Arial, Helvetica, sans-serif"> I'm working on a project, whose implementation deals with PostgreSQL. A brief description of our application is given below.</font> I'm running version 8.0 on a dedicated server 1Gb of RAM. my database isn't complex, it contains just 2 simple tables. <tt> CREATE TABLE cookies ( domain varchar(50) NOT NULL, path varchar(50) NOT NULL, name varchar(50) NOT NULL, principalidvarchar(50) NOT NULL, host text NOT NULL, value text NOT NULL, secure bool NOT NULL, timestamp timestampwith 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) ) </tt>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. <tt> 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'</tt> <tt>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')</tt> 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, </pre>
Jamal Ghaffour a écrit : <blockquote cite="mid43C66742.9060104@elios-informatique.fr" type="cite"></blockquote><font><fontcolor="#000000"> <pre style="margin: 0em;">Hi, <font face="Arial, Helvetica, sans-serif"> I'm working on a project, whose implementation deals with PostgreSQL. A brief description of our application is given below.</font> I'm running version 8.0 on a dedicated server 1Gb of RAM. my database isn't complex, it contains just 2 simple tables. <tt> CREATE TABLE cookies ( domain varchar(50) NOT NULL, path varchar(50) NOT NULL, name varchar(50) NOT NULL, principalidvarchar(50) NOT NULL, host text NOT NULL, value text NOT NULL, secure bool NOT NULL, timestamp timestampwith 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) ) </tt>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. <tt> 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'</tt> <tt>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')</tt> 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,</pre> </font></font> Hi,<br /><br /> There are some results that can give you <span class="def-example">concrete </span>idea about my problem: <br /> when i 'm launching my test that executes in loop mannerthe SELECT and UPDATE queries described above, i'm obtaining this results:<br /><br /> UPDATE Time execution :0s:5225 us<br /> SELECT Time execution :0s: 6908 us<br /><br /> 5 minutes Later: <br /><br /> UPDATE Time execution :0s:6125 us<br /> SELECT Time execution :0s: 10928 us<br /><br /> 5 minutes Later: <br /><br /> UPDATE Time execution :0s:5825 us<br /> SELECT Time execution :0s: 14978 us<br /><br /> As you can see , the time execution of the SELECT requestis growing relatively to time and not the UPDATE time execution. <br /> I note that to stop the explosion of theSelect time execution, i m using frequently the vaccum query on the cookies table.<br /> Set the autovacuum parmaeterin the configuation file to on wasn't able to remplace the use of the vaccum command, and i don't know if this behaivouris normal?<br /><br /> Thanks,<br /> Jamal<br />
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 timestamp<CURRENT_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.
Вложения
On 1/12/06, Jamal Ghaffour <Jamal.Ghaffour@elios-informatique.fr> 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 ;)
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 >>> timestamp<CURRENT_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 timestamp>CURRENT_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 timestamp>CURRENT_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 on index_cookies_select (cost=0.00..2.01 rows=1 width=0) (actual time=0.036..0.036 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: 39.616 ms (9 rows) I notice that the time execution increases significantly. and i need the vacuum query to obtain normal time execution: 3- After vacuum execution: icap=# vacuum cookies; VACUUM icap=# EXPLAIN ANALYZE 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'); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on cookies (cost=4.02..8.04 rows=1 width=268) (actual time=0.111..0.112 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.095..0.095 rows=0 loops=1) -> Bitmap Index Scan on index_cookies_select (cost=0.00..2.01 rows=1 width=0) (actual time=0.081..0.081 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.159 ms (9 rows) Thanks, Jamal
Вложения
On Thu, 12 Jan 2006 01:32:10 +0100 Jamal Ghaffour <Jamal.Ghaffour@elios-informatique.fr> wrote: > I'm using the default configuration file, and i m asking if i have to > change some paramters to have a good performance. In general the answer is yes. The default is a pretty good best guess at what sorts of values work for your "typical system", but if you run into performance problems the config file is where you should look first, provided you've done the simple things like adding good indexes, vacumm analyze, etc. You'll want to consult the following various documentation out there to help your properly tune your configuration: http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html http://www.powerpostgresql.com/Docs http://www.powerpostgresql.com/PerfList http://www.revsys.com/writings/postgresql-performance.html Hopefully these will help you understand how to set your configuration values. --------------------------------- Frank Wiles <frank@wiles.org> http://www.wiles.org ---------------------------------