Re: Help request: how to tune performance? - Mailing list pgsql-admin

On Thu, Sep 18, 2008 at 12:00 PM, Mauri Sahlberg
<mauri.sahlberg@claymountain.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.