Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id BBA309FA0FB for ; Sun, 10 Dec 2006 21:18:56 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 02404-08 for ; Sun, 10 Dec 2006 21:18:50 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.184]) by postgresql.org (Postfix) with ESMTP id 872BC9FA0EF for ; Sun, 10 Dec 2006 21:18:50 -0400 (AST) Received: by nf-out-0910.google.com with SMTP id x4so1976037nfb for ; Sun, 10 Dec 2006 17:18:48 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:references; b=XklSnWF+AAVWHncl5oM3VUmuCxnG95QXxrA0jbDtxEWV7FNT97ReWiw50VMsiNUY8go03MQXfnmQhO+GOTxN4UE2JTWe5soPuug1x3g69h0lwQYpRWNJK7qe1tIUbm8/iph9GJF6ExAmWZG6WVReCAF/uaq9dMEGrGORbUsrejw= Received: by 10.49.43.2 with SMTP id v2mr3794830nfj.1165799928490; Sun, 10 Dec 2006 17:18:48 -0800 (PST) Received: by 10.49.78.1 with HTTP; Sun, 10 Dec 2006 17:18:48 -0800 (PST) Message-ID: <430d92a20612101718o239dbf9dg990e784c17bf175a@mail.gmail.com> Date: Sun, 10 Dec 2006 20:18:48 -0500 From: Gene Reply-To: gene@sotech.us To: "Daniel van Ham Colchete" Subject: Re: New to PostgreSQL, performance considerations Cc: pgsql-performance@postgresql.org In-Reply-To: <8a0c7af10612101702i49b5a533v4a2300d90d9dae6b@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_8621_27693586.1165799928335" References: <8a0c7af10612101141n2c7727c3r2f92345753960808@mail.gmail.com> <430d92a20612101336r3de8862coe618277fccb4006a@mail.gmail.com> <8a0c7af10612101702i49b5a533v4a2300d90d9dae6b@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.055 tagged_above=0 required=5 tests=AWL, BAYES_50, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200612/194 X-Sequence-Number: 22250 ------=_Part_8621_27693586.1165799928335 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline I'm using gentoo as well, I'm having performance issues as the number of partitions is increasing I imagine do due to overhead managing them and figuring out where to put each insert/update. I'm switching to weekly partitions instead of daily. I believe in PG8.2 constraint exclusion works with updates/deletes also so I'm eager to upgrade. I get about 1 million records per day in two tables each, each record updated about 4 times within 30 minutes. Do you think using UTF8 vs US-ASCII hurts performance signficantly, some of my smaller tables require unicode, and I don't think you can have some tables be unicode and some be ASCII. On 12/10/06, Daniel van Ham Colchete wrote: > > Hi Gene, > > at my postgresql.conf, the only non-comented lines are: > fsync = off > lc_messages = 'C' > lc_monetary = 'C' > lc_numeric = 'C' > lc_time = 'C' > max_connections = 100 > shared_buffers = 5000 > temp_buffers = 1000 > work_mem = 4096 > > The only two values I changed are shared_buffers and work_mem. > > *** BUT *** > I'm using Gentoo Linux, so all my libraries (including glibc that is > very important to PostgreSQL), and all my softwares are compiled with > good CFLAG options to my processor ("-O2 march=athlon-xp (...)"). My > Linux is not an Intel-AMD binary compatible turtle like > Fedora/RedHat/SUSE/... It's really important to have your GLIBC > compiled for your processor. It is essencial for performance. > > I can't imagine anyone buying a $1k-dollar quad-core XEON and using an > i585 compatible distro that doesn't even know what the fudge is > SSE/SSE2/vectorized instructions. > > Best regards, > Daniel Colchete > > On 12/10/06, Gene wrote: > > I have a similar type application, I'm partitioning using constraint > > exclusion so queries only have to look at a few tables. I've found that > > there is some overhead to using partitioning so you should test to see > how > > many partitions you want to create. Could I check out you > postgresql.conf > > parameters to compare? thanks > > > > > > Gene Hart > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > -- Gene Hart cell: 443-604-2679 ------=_Part_8621_27693586.1165799928335 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit Content-Disposition: inline I'm using gentoo as well, I'm having performance issues as the number of partitions is increasing I imagine do due to overhead managing them and figuring out where to put each insert/update. I'm switching to weekly partitions instead of daily. I believe in PG8.2 constraint exclusion works with updates/deletes also so I'm eager to upgrade. I get about 1 million records per day in two tables each, each record updated about 4 times within 30 minutes.

Do you think using UTF8 vs US-ASCII hurts performance signficantly, some of my smaller tables require unicode, and I don't think you can have some tables be unicode and some be ASCII.

On 12/10/06, Daniel van Ham Colchete <daniel.colchete@gmail.com> wrote:
Hi Gene,

at my postgresql.conf, the only non-comented lines are:
fsync = off
lc_messages = 'C'
lc_monetary = 'C'
lc_numeric = 'C'
lc_time = 'C'
max_connections = 100
shared_buffers = 5000
temp_buffers = 1000
work_mem = 4096

The only two values I changed are shared_buffers and work_mem.

*** BUT ***
I'm using Gentoo Linux, so all my libraries (including glibc that is
very important to PostgreSQL), and all my softwares are compiled with
good CFLAG options to my processor ("-O2 march=athlon-xp (...)"). My
Linux is not an Intel-AMD binary compatible turtle like
Fedora/RedHat/SUSE/... It's really important to have your GLIBC
compiled for your processor.  It is essencial for performance.

I can't imagine anyone buying a $1k-dollar quad-core XEON and using an
i585 compatible distro that doesn't even know what the fudge is
SSE/SSE2/vectorized instructions.

Best regards,
Daniel Colchete

On 12/10/06, Gene <genekhart@gmail.com> wrote:
> I have a similar type application, I'm partitioning using constraint
> exclusion so queries only have to look at a few tables. I've found that
> there is some overhead to using partitioning so you should test to see how
> many partitions you want to create. Could I check out you postgresql.conf
> parameters to compare? thanks
>
>
> Gene Hart

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match



--
Gene Hart
cell: 443-604-2679 ------=_Part_8621_27693586.1165799928335--