Message-Id: <4BE98A2A.2E1C.0042.0@homedecorators.com>
Date: Tue, 11 May 2010 16:47:38 -0500
From: "Jorge Montero" <jorge_montero@homedecorators.com>
To: "venu madhav" <venutaurus539@gmail.com>,
 <pgsql-performance@postgresql.org>
Subject: Re: Performance issues when the number of records
	are around 10 Million
References: <AANLkTin1f-9M0iNmIEtU7YlvEsVAt6TmHE1vDk3iUmQq@mail.gmail.com>
In-Reply-To: <AANLkTin1f-9M0iNmIEtU7YlvEsVAt6TmHE1vDk3iUmQq@mail.gmail.com>
Mime-Version: 1.0
Content-Type: multipart/alternative; boundary="=__Part705ADF6A.1__="

This is a MIME message. If you are reading this text, you may want to 
consider changing to a mail reader or gateway that understands how to 
properly handle MIME multipart messages.

--=__Part705ADF6A.1__=
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable

First, are you sure you are getting autovacuum to run hourly? Autovacuum =
will only vacuum when certain configuration thresholds are reached. You =
can set it to only check for those thresholds every so often, but no =
vacuuming or analyzing will be done unless they are hit, regardless of how =
often autovacuum checks the tables. Whenever you are dealing with time =
series, the default thresholds are often insufficient, especially when you =
are especially interested in the last few records on a large table.=20
=20
What are your autovacuum configuration parameters?
When were the two tables last autovacuum and analyzed, according to =
pg_stat_user_tables?
Could you post the output of explain analyze of your query?
Which default statistic collection parameters do you use? Have you changed =
them specifically for the tables you are using?
Which version of Postgres are you running? Which OS?=20
=20
=20

>>> venu madhav <venutaurus539@gmail.com> 05/11/10 3:47 AM >>>
Hi all,
In my database application, I've a table whose records can reach 10M and =
insertions can happen at a faster rate like 100 insertions per second in =
the peak times. I configured postgres to do auto vacuum on hourly basis. I =
have frontend GUI application in CGI which displays the data from the =
database. When I try to get the last twenty records from the database, it =
takes around 10-15 mins to complete the operation.This is the query which =
is used:

select e.cid, timestamp, s.sig_class, s.sig_priority, s.sig_name, =
e.sniff_ip, e.sniff_channel, s.sig_config, e.wifi_addr_1,
e.wifi_addr_2, e.view_status, bssid FROM event e, signature s WHERE =
s.sig_id =3D e.signature AND e.timestamp >=3D '1270449180' AND e.timestamp =
< '1273473180' ORDER BY e.cid DESC, e.cid DESC limit 21 offset 10539780;

Can any one suggest me a better solution to improve the performance.

Please let me know if you've any further queries.


Thank you,
Venu=20

--=__Part705ADF6A.1__=
Content-Type: text/html; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable
Content-Description: HTML

<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Dutf-8">
<META content=3D"MSHTML 6.00.2900.3243" name=3DGENERATOR></HEAD>
<BODY style=3D"MARGIN: 4px 4px 1px; FONT: 10pt Tahoma">
<DIV>First, are you sure you are getting autovacuum to run hourly? =
Autovacuum will only vacuum when certain configuration thresholds are =
reached. You can set it to only check for those thresholds every so often, =
but no vacuuming or analyzing will be done unless they are hit, regardless =
of how often autovacuum checks the tables. Whenever you are dealing with =
time series, the default&nbsp;thresholds are often&nbsp;insufficient,&nbsp;=
especially when you are especially interested&nbsp;in the&nbsp;last few =
records on a large table.&nbsp;</DIV>
<DIV>&nbsp;</DIV>
<DIV>What are your autovacuum configuration parameters?</DIV>
<DIV>When were the two tables last autovacuum and analyzed, according to =
pg_stat_user_tables?</DIV>
<DIV>Could you&nbsp;post the output of explain analyze of your query?<BR>Wh=
ich default statistic collection parameters do you use? Have you changed =
them specifically for the tables you are using?</DIV>
<DIV>Which version of Postgres are you running? Which OS? </DIV>
<DIV>&nbsp;</DIV>
<DIV>&nbsp;</DIV>
<DIV><BR>&gt;&gt;&gt; venu madhav &lt;venutaurus539@gmail.com&gt; 05/11/10 =
3:47 AM &gt;&gt;&gt;<BR>Hi all,<BR>In my database application, I've a =
table whose records can reach 10M and insertions can happen at a faster =
rate like 100 insertions per second in the peak times. I configured =
postgres to do auto vacuum on hourly basis. I have frontend GUI application=
 in CGI which displays the data from the database. When I try to get the =
last twenty records from the database, it takes around 10-15 mins to =
complete the operation.This is the query which is used:<BR><BR><B>select =
e.cid, timestamp, s.sig_class, s.sig_priority, s.sig_name, e.sniff_ip, =
e.sniff_channel, s.sig_config, e.wifi_addr_1,<BR>e.wifi_addr_2, e.view_stat=
us, bssid FROM event e, signature s WHERE s.sig_id =3D e.signature AND =
e.timestamp &gt;=3D '1270449180' AND e.timestamp &lt; '1273473180' ORDER =
BY e.cid DESC, e.cid DESC limit 21 offset 10539780;<BR></B><BR>Can any one =
suggest me a better solution to improve the performance.<BR><BR>Please let =
me know if you've any further queries.<BR><BR><BR>Thank you,<BR><FONT =
color=3D#888888><FONT color=3D#888888>Venu</FONT> </FONT></DIV></BODY></HTM=
L>

--=__Part705ADF6A.1__=--