Обсуждение: Bundling postgreSQL with my Java application

On 07/06/2009 01:48 AM, Saurabh Dave wrote:
> We are bundling PostgreSQL 8.3.7 with our Java based application.
> We observe that in some systems the Database access becomes very slow
> after running it for couple of days.
>
> We understand that postgresql.conf needs to be adjusted as per the
> system specification where postgreSQL is running.
>
> Is there a utility that we can use that can check the system
> specification and change the required parameters in postgresql.conf
> accordingly?

Hi Saurabh:

No offense intended - but have you looked at the documentation for
postgresql.conf?

If you are going to include PostgreSQL in your application, I'd highly
recommend you understand what you are including. :-)

PostgreSQL 8.4 comes with significantly improved "out of the box"
configuration. I think that is what you are looking for. Specifically,
you are probably looking for "autovacuum" to be enabled.

Cheers,
mark

--
Mark Mielke<mark@mielke.cc>

Mark Mielke <mark 'at' mark.mielke.cc> writes:

> On 07/06/2009 01:48 AM, Saurabh Dave wrote:
>> We are bundling PostgreSQL 8.3.7 with our Java based application.

[...]

> PostgreSQL 8.4 comes with significantly improved "out of the box"
> configuration. I think that is what you are looking for. Specifically,
> you are probably looking for "autovacuum" to be enabled.

autovacuum is enabled by default on PG 8.3 as well.

--
Guillaume Cottenceau

On 07/06/2009 03:17 AM, Saurabh Dave wrote:
> >No offense intended - but have you looked at the documentation for
> postgresql.conf?
>
> >If you are going to include PostgreSQL in your application, I'd
> highly recommend you >understand what you are including. :-)
>
> I had a look into the documentation of postgres.conf, and tried a lot
> with changing paramters I thought would improve the performance, but
> in vain.
> Autovaccum is enabled by default in 8.3.7 , but i reduced the nap time
> so that it happens more frequently.
>
> My personal opinion is that certain parameters in postgres.conf are
> simply too technical in nature for a application developer like me, it
> becomes more of a trial and error kind of frustrating process.
>
> If there a utility that understands the system specification on which
> postgres is going to run and change the paramters accordingly, that
> would help.

If autovacuum is on - then I suspect your problem would not be addressed
by tweaking postgresql.conf. You'll have to analyze the queries that are
taking longer than you expect. Run them with "explain analyze" in front
and post the results. Provide table structure information.

It's possible tweaking postgresql.conf would give you a performance
boost - but it would probably be temporary. That is, getting 1 x 5%
speedup here and 1 x 10% there will be useless if the actual query is
becoming slower by 100% every few days.

The problem needs to be understood.

For what it's worth, we have some fairly busy systems that have used
PostgreSQL 8.0 / 8.1 out of the box, the administrators forgot to run
vacuum / analyze, and the system *still* performed well months later.
PostgreSQL is pretty good even without non-optimal configuration and
even without database maintenance. If autovacuum is really running for
you - I would look as to whether you have the right indexes defined
and/or whether you are actually using them?

Cheers,
mark

--
Mark Mielke<mark@mielke.cc>

Saurabh Dave wrote: <blockquote cite="mid:fdd0c0f90907060017y334df602k4fff0acf07776d91@mail.gmail.com"
type="cite"><divclass="im">>No offense intended - but have you looked at the documentation for postgresql.conf?<br
/><br/> >If you are going to include PostgreSQL in your application, I'd highly recommend you >understand what
youare including. :-)<br /><br /></div> I had a look into the documentation of postgres.conf, and tried a lot with
changingparamters I thought would improve the performance, but in vain.<br /> Autovaccum is enabled by default in 8.3.7
,but i reduced the nap time so that it happens more frequently.<br /></blockquote> As others have pointed tuning is not
acaned answer  hence all the config options to start with.  But to change the configuration to something a bench mark
mustbe made.  The only way to do that is identify the common SQL commands sent to the server then run explain analyze 
soyou know what the server is doing.  Then post the the results along with Config file and we can make suggestions <br
/><br/> There is  <a class="moz-txt-link-freetext"
href="http://wiki.postgresql.org/wiki/Performance_Optimization">http://wiki.postgresql.org/wiki/Performance_Optimization</a><br
/><br/> Greg Smith is working on a tuner  <a class="moz-txt-link-freetext"
href="http://notemagnet.blogspot.com/2008/11/automating-initial-postgresqlconf.html">http://notemagnet.blogspot.com/2008/11/automating-initial-postgresqlconf.html</a><br
/><br/> But thats a monumental undertaking as one configuration setting for one type of work load can be ruinousness to
anotherwork load.<br /><br /> The one common theme is know the workload so the configuration matches.  <br
/><blockquotecite="mid:fdd0c0f90907060017y334df602k4fff0acf07776d91@mail.gmail.com" type="cite"><br /> My personal
opinionis that certain parameters in postgres.conf are simply too technical in nature for a application developer like
me,it becomes more of a trial and error kind of frustrating process.<br /></blockquote> This boils down to know the 
workload. <br /> different kinds of work loads:  <br />     A: more writing with very few  reads.<br />     B: more
readsthat are simple queries and few complex quiers with very few writes.  There is a ratio to look at in my case 10000
readsoccur before next write So we have lots of indexes aimed at those common queries.  <br />     C: Complex queries
takingminutes to hours to run on data warehouse covering  millions of records.<br />     D: equal work load between
writesand reads.  <br /><br /> There are many kinds of workloads requiring different configurations.  <br /><blockquote
cite="mid:fdd0c0f90907060017y334df602k4fff0acf07776d91@mail.gmail.com"type="cite"><br /> If there a utility that
understandsthe system specification on which postgres is going to run and change the paramters accordingly, that would
help.<br/><br /> Thanks,<br /><font color="#888888">Saurabh</font></blockquote> <snip><br /><br /> 

On Sun, Jul 5, 2009 at 11:48 PM, Saurabh Dave<saurabhdave@gmail.com> wrote:
> Hi,
>
> We are bundling PostgreSQL 8.3.7 with our Java based application.
> We observe that in some systems the Database access becomes very slow after
> running it for couple of days.
>
> We understand that postgresql.conf needs to be adjusted as per the system
> specification where postgreSQL is running.
>
> Is there a utility that we can use that can check the system specification
> and change the required parameters in postgresql.conf accordingly?

Assuming autovacuum is enabled still (it is by default) it is likely
that your updates are big enough that you're blowing out the free
space map. Easy to check, take a db that's slowed down and run vacuum
verbose as a super user on any of the dbs in it (postgres is a good
choice) and see what the last 20 or so lines have to say about how
many slots you have and how many you're using.  If you need more
slots, then adjust the free space map settings (max slots and max
relations) so they're large enough to keep the db from bloating in the
future.  On larger datasets 1M to 10M slots is not uncommon, and since
it only uses 6 bytes per slot, even 10M is only 60M of shared memory.

8.4 has a LOT of improvements in this area, as I understand the whole
FSM stuff has been automated on that version.  Note that I haven't
tested 8.4 yet, so I'm just going by what I read.

Saurabh Dave <saurabhdave@gmail.com> wrote:

> what I need to do is to check the queries that are slow and do a
> vacuum analyze and share the results along with postgresql.conf
> being used.

Hopefully just a typo there.  While you need to ensure that adequate
VACUUM and ANALYZE maintenance is being performed, and a suggestion
was made to use VACUUM VERBOSE for diagnostic purposes, what you need
to do with the slow queries you identify is to run them with EXPLAIN
ANALYZE in front (not VACUUM ANALYZE as your post stated).

-Kevin