Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 7185963259A
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 19 Jul 2010 12:39:06 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 34968-02
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 19 Jul 2010 15:38:58 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from bill.kwcorp.com (mailx.kwcorp.com [65.118.99.196])
	by mail.postgresql.org (Postfix) with ESMTP id 98AC66321A0
	for <pgsql-performance@postgresql.org>;
	Mon, 19 Jul 2010 12:38:58 -0300 (ADT)
X-TM-IMSS-Message-ID: <b02dc605000ea5c7@kwcorp.com>
Received: from gw.homedecorators.com ([172.23.217.142]) by kwcorp.com
	([192.168.85.25]) with ESMTP (TREND IMSS SMTP Service 7.0) id
	b02dc605000ea5c7 ; Mon, 19 Jul 2010 10:38:55 -0500
Received: from HDC_MTA-MTA by gw.homedecorators.com
	with Novell_GroupWise; Mon, 19 Jul 2010 10:38:55 -0500
Message-Id: <4C442B03.2E1C.0042.0@homedecorators.com>
X-Mailer: Novell GroupWise Internet Agent 7.0.2 HP
Date: Mon, 19 Jul 2010 10:37:55 -0500
From: "Jorge Montero" <jorge_montero@homedecorators.com>
To: "kuopo" <spkuo@cs.nctu.edu.tw>,<pgsql-performance@postgresql.org>
Subject: Re: how to handle a big table for data log
References: <AANLkTilCP3sGTbHIvrM-ixG-P1Dz6ToqrhbgijY8m8V8@mail.gmail.com>
In-Reply-To: <AANLkTilCP3sGTbHIvrM-ixG-P1Dz6ToqrhbgijY8m8V8@mail.gmail.com>
Mime-Version: 1.0
Content-Type: multipart/alternative; boundary="=__Part527F5743.1__="
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 201007/287
X-Sequence-Number: 39676

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.

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

Large tables, by themselves, are not necessarily a problem. The problem is =
what you might be trying to do with them. Depending on the operations you =
are trying to do, partitioning the table might help performance or make it =
worse.
=20
What kind of queries are you running? How many days of history are you =
keeping? Could you post an explain analyze output of a query that is being =
problematic?
Given the amount of data you hint about, your server configuration, and =
custom statistic targets for the big tables in question would be useful.

>>> kuopo <spkuo@cs.nctu.edu.tw> 7/19/2010 1:27 AM >>>
Hi,

I have a situation to handle a log table which would accumulate a
large amount of logs. This table only involves insert and query
operations. To limit the table size, I tried to split this table by
date. However, the number of the logs is still large (46 million
records per day). To further limit its size, I tried to split this log
table by log type. However, this action does not improve the
performance. It is much slower than the big table solution. I guess
this is because I need to pay more cost on the auto-vacuum/analyze for
all split tables.

Can anyone comment on this situation? Thanks in advance.


kuopo.

--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

--=__Part527F5743.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>Large tables, by themselves, are not necessarily a problem. The =
problem is what you might be trying to do with them. Depending on the =
operations you are trying to do, partitioning the table might help =
performance or make it worse.</DIV>
<DIV>&nbsp;</DIV>
<DIV>What kind of queries are you running? How many&nbsp;days of history =
are you keeping? Could you post an explain analyze output of a query that =
is being problematic?</DIV>
<DIV>Given the amount of data you hint about, your server configuration, =
and custom statistic targets for the big tables in question would be =
useful.<BR><BR>&gt;&gt;&gt; kuopo &lt;spkuo@cs.nctu.edu.tw&gt; 7/19/2010 =
1:27 AM &gt;&gt;&gt;<BR>Hi,<BR><BR>I have a situation to handle a log =
table which would accumulate a<BR>large amount of logs. This table only =
involves insert and query<BR>operations. To limit the table size, I tried =
to split this table by<BR>date. However, the number of the logs is still =
large (46 million<BR>records per day). To further limit its size, I tried =
to split this log<BR>table by log type. However, this action does not =
improve the<BR>performance. It is much slower than the big table solution. =
I guess<BR>this is because I need to pay more cost on the auto-vacuum/analy=
ze for<BR>all split tables.<BR><BR>Can anyone comment on this situation? =
Thanks in advance.<BR><BR><BR>kuopo.<BR><BR>-- <BR>Sent via pgsql-performan=
ce mailing list (pgsql-performance@postgresql.org)<BR>To make changes to =
your subscription:<BR><A href=3D"http://www.postgresql.org/mailpref/pgsql">=
http://www.postgresql.org/mailpref/pgsql</A>-performance<BR></DIV></BODY></=
HTML>

--=__Part527F5743.1__=--