Re: [ADMIN] Help request: how to tune performance?

Hi,

Thanks for the reply and advice.

Scott Marlowe kirjoitti:
>> Version : 8.1.11 Vendor: CentOS
>>
>
> So, you built it its own machine, but you didn't upgrade to at least 8.2?
>
>
Now it is: 8.4devel_15092008

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".
>
> Please post the output of explain analyze as an attachment. explain
> is only half the answer.
>
>
I did what Kenneth Marshall suggested and edited DBIx::Searchbuilder's
Handle/Pg.pm. I will post the explain analyze for the new query it now
generates if it becomes necessary.
> Possibly. explain analyze will help you identify where stats are
> wrong. sometimes just cranking the stats target on a few columns and
> re-analyzing gets you a noticeable performance boost. It's cheap and
> easy.
>
> When the estimated and actual number of rows are fairly close, then
> look for the slowest thing and see if an index can help.
>
> What have to already done to tune the install? shared_buffers,
> work_mem, random_page_cost, effective_cache_size. Is your db bloating
> during the day?
>
>
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

Sorry, I do not understand what you mean by bloating. The db size is:
rt=# select pg_size_pretty(pg_database_size('rt'));
pg_size_pretty
----------------
350 MB
(1 row)

> 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.

I have also added 5 more mason processes to the web frontend machine.

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.

Thank you.

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