Thread: Bundling postgreSQL with my Java application
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?
Thanks,
Saurabh
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?
Thanks,
Saurabh
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
>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.>If you are going to include PostgreSQL in your application, I'd highly recommend you >understand what you are including. :-)
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.
Thanks,
Saurabh
On Mon, Jul 6, 2009 at 12:37 PM, Guillaume Cottenceau <gc@mnc.ch> wrote:
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.autovacuum is enabled by default on PG 8.3 as well.
> 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.
--
Guillaume Cottenceau
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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 />
Thanks all for your valuable comments, as I gather, 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.
I will work on that.
Thanks again,
Saurabh
I will work on that.
Thanks again,
Saurabh
On Tue, Jul 7, 2009 at 4:46 AM, justin <justin@emproshunts.com> wrote:
Saurabh Dave wrote:As others have pointed tuning is not a caned answer hence all the config options to start with. But to change the configuration to something a bench mark must be made. The only way to do that is identify the common SQL commands sent to the server then run explain analyze so you know what the server is doing. Then post the the results along with Config file and we can make suggestions>No offense intended - but have you looked at the documentation for postgresql.conf?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.
>If you are going to include PostgreSQL in your application, I'd highly recommend you >understand what you are including. :-)
Autovaccum is enabled by default in 8.3.7 , but i reduced the nap time so that it happens more frequently.
There is http://wiki.postgresql.org/wiki/Performance_Optimization
Greg Smith is working on a tuner http://notemagnet.blogspot.com/2008/11/automating-initial-postgresqlconf.html
But thats a monumental undertaking as one configuration setting for one type of work load can be ruinousness to another work load.
The one common theme is know the workload so the configuration matches.This boils down to know the work load.
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.
different kinds of work loads:
A: more writing with very few reads.
B: more reads that are simple queries and few complex quiers with very few writes. There is a ratio to look at in my case 10000 reads occur before next write So we have lots of indexes aimed at those common queries.
C: Complex queries taking minutes to hours to run on data warehouse covering millions of records.
D: equal work load between writes and reads.
There are many kinds of workloads requiring different configurations.<snip>
If there a utility that understands the system specification on which postgres is going to run and change the paramters accordingly, that would help.
Thanks,
Saurabh
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