From: Gene <genekhart@gmail.com>
To: Daniel van Ham Colchete <daniel.colchete@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: New to PostgreSQL, performance considerations
Date: Sun, 10 Dec 2006 20:18:48 -0500
Message-ID: <430d92a20612101718o239dbf9dg990e784c17bf175a@mail.gmail.com> (raw)
In-Reply-To: <8a0c7af10612101702i49b5a533v4a2300d90d9dae6b@mail.gmail.com>
References: <8a0c7af10612101141n2c7727c3r2f92345753960808@mail.gmail.com>
<430d92a20612101336r3de8862coe618277fccb4006a@mail.gmail.com>
<8a0c7af10612101702i49b5a533v4a2300d90d9dae6b@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: genekhart@gmail.com, daniel.colchete@gmail.com
Subject: Re: New to PostgreSQL, performance considerations
In-Reply-To: <430d92a20612101718o239dbf9dg990e784c17bf175a@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox