Skip site navigation (1) Skip section navigation (2)

Re: Help request: how to tune performance?

From: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
To: "Mauri Sahlberg" <mauri(dot)sahlberg(at)claymountain(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Help request: how to tune performance?
Date: 2008-09-18 20:01:02
Message-ID: dcc563d10809181301q724b835v75947147856616e2@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
On Thu, Sep 18, 2008 at 12:00 PM, Mauri Sahlberg
<mauri(dot)sahlberg(at)claymountain(dot)com> wrote:
>> So, you built it its own machine, but you didn't upgrade to at least 8.2?
>>
>>
>
> Now it is: 8.4devel_15092008

I don't think I'd be running production data on a dev version of the
db.  Not that it's likely to crash and eat all your data, which is a
distinct possibility, but that you might have to dump and reload a
couple of times before you get to 8.4 production.  Plus if there's a
weird performance corner case you might get to be the lucky one to
report it.  8.3.3 is quite stable and quite a bit faster than 8.1.  I
haven't had a chance to even test 8.4 yet, but I'm sure it's got its
own performance enhancements as well.

> The machine was installed by the production team from the standard CentOS
> template. I tried to adhere to the standard and installed the standard
> CentOS binary for Postgresql. I am not  part of production team so I try to
> be extra careful with the "rule book".

Understood...  I prefer to install the PGDG rpms on centos / redhat,
as it lets me choose the version I want instead of using the old
version that rh/centos supports for that version.

They're easy to install and uninstall.

So, how's the performance of 8.4 now compared to 8.1?

> When I upgraded to 8.4 I also checked newer Postgresql manual for the memory
> consumption and found comment by Steven Citron-Pousty and increased
> accordingly:
> - shared_buffers to 320MB
> - wal_buffers to 8MB
> - effective_cache_size to 2048MB
> - maintenance_work_mem to 384MB

Seems reasonable.  What's work_mem set to?  I'd suggest something in
the 4 to 8 meg range for starters, unless you're trying to handle
hundreds and hundreds of connections.

> Sorry, I do not understand what you mean by bloating.

Every time pgsql updates or deletes a row it leaves a dead row in its
place.  Enough of these without vacuuming up the dead tuples and you
wind up with a table with 90% dead space etc...  Bad for performance.

> The db size is:
> rt=# select pg_size_pretty(pg_database_size('rt'));
> pg_size_pretty
> ----------------
> 350 MB
> (1 row)

Cool, between OS kernel cache and pgsql's shared_buffers it should all
be in memory after a bit.

>> Are you running on a single SATA hard drive?    How big's the database
>> directory?  I'm guessing from your top output that the db is about 500
>> meg or so.  it should all fit in memory.
>>
>>
>
> -bash-3.2$ du --si -s data
> 524M    data
>
> I don't know what kind of drives there actually are. The machine is vmware
> virtual with two virtual CPU's clocking 2,33GHz, 4 GB ram, 1 GB swap. The
> disk is probably given from either MSA or from EVA. The disk shows up as one
> virtual drive and everything is on it. Filesystem is ext3 on lvm. Database
> data is on /var which is it's own volume.

The one thing that should NEVER run on a VM or on an LVM vol is a
database.  This is because most VMs and LVM for sure, do NOT provide
proper write barriers, which means a crash could cost you your
database being corrupted beyond repair.  also, VMs tend to slow down
heavily switched apps like databases and LVM has a maximum throughput
in the 300Meg/sec range.  Not a big deal for a couple of mirrored
disks, but a big deal if you're running a 32 disk RAID-10 array under
it.

> For me the results look promising. Opening search builder went from 42
> seconds to 4 seconds and opening one particular long chain takes now only 27
> seconds. But again I am not from the support team either so I do not get to
> define what is fast enough. The verdict is now in for the jury to decide.

hehe.  I know how that works.  best of luck.  I'd push or a dedicated
db server.  They can't give you a pick up truck and be upset it's not
a dragster later on.

In response to

pgsql-admin by date

Next:From: Randall WilsonDate: 2008-09-18 20:10:24
Subject: Re: Idle Error invalid byte sequence
Previous:From: Tom LaneDate: 2008-09-18 18:17:15
Subject: Re: Idle Error invalid byte sequence

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group