pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedpostgresql performance tuning
18+ messages / 10 participants
[nested] [flat]
* postgresql performance tuning
@ 2005-12-06 16:14 Ameet Kini <akini@cs.wisc.edu>
2005-12-06 20:07 ` Re: postgresql performance tuning Vivek Khera <vivek@khera.org>
0 siblings, 1 reply; 18+ messages in thread
From: Ameet Kini @ 2005-12-06 16:14 UTC (permalink / raw)
To: pgsql-performance
Hello,
I have a question on postgres's performance tuning, in particular, the
vacuum and reindex commands. Currently I do a vacuum (without full) on all
of my tables. However, its noted in the docs (e.g.
http://developer.postgresql.org/docs/postgres/routine-reindex.html)
and on the lists here that indexes may still bloat after a while and hence
reindex is necessary. How often do people reindex their tables out
there? I guess I'd have to update my cron scripts to do reindexing too
along with vacuuming but most probably at a much lower frequency than
vacuum.
But these scripts do these maintenance tasks at a fixed time (every few
hours, days, weeks, etc.) What I would like is to do these tasks on a need
basis. So for vacuuming, by "need" I mean every few updates or some such
metric that characterizes my workload. Similarly, "need" for the reindex
command might mean every few updates or degree of bloat, etc.
I came across the pg_autovacuum daemon, which seems to do exactly what I
need for vacuums. However, it'd be great if there was a similar automatic
reindex utility, like say, a pg_autoreindex daemon. Are there any plans
for this feature? If not, then would cron scripts be the next best
choice?
Thanks,
Ameet
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: postgresql performance tuning
2005-12-06 16:14 postgresql performance tuning Ameet Kini <akini@cs.wisc.edu>
@ 2005-12-06 20:07 ` Vivek Khera <vivek@khera.org>
2005-12-06 20:48 ` Re: postgresql performance tuning Alan Stange <stange@rentec.com>
2005-12-06 22:03 ` Re: postgresql performance tuning Ameet Kini <akini@cs.wisc.edu>
0 siblings, 2 replies; 18+ messages in thread
From: Vivek Khera @ 2005-12-06 20:07 UTC (permalink / raw)
To: pgsql-performance
On Dec 6, 2005, at 11:14 AM, Ameet Kini wrote:
> need for vacuums. However, it'd be great if there was a similar
> automatic
> reindex utility, like say, a pg_autoreindex daemon. Are there any
> plans
> for this feature? If not, then would cron scripts be the next best
what evidence do you have that you are suffering index bloat? or are
you just looking for solutions to problems that don't exist as an
academic exercise? :-)
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: postgresql performance tuning
2005-12-06 16:14 postgresql performance tuning Ameet Kini <akini@cs.wisc.edu>
2005-12-06 20:07 ` Re: postgresql performance tuning Vivek Khera <vivek@khera.org>
@ 2005-12-06 20:48 ` Alan Stange <stange@rentec.com>
2005-12-06 21:20 ` Re: postgresql performance tuning Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 18+ messages in thread
From: Alan Stange @ 2005-12-06 20:48 UTC (permalink / raw)
To: Vivek Khera <vivek@khera.org>; +Cc: pgsql-performance
Vivek Khera wrote:
>
> On Dec 6, 2005, at 11:14 AM, Ameet Kini wrote:
>
>> need for vacuums. However, it'd be great if there was a similar
>> automatic
>> reindex utility, like say, a pg_autoreindex daemon. Are there any plans
>> for this feature? If not, then would cron scripts be the next best
>
> what evidence do you have that you are suffering index bloat? or are
> you just looking for solutions to problems that don't exist as an
> academic exercise? :-)
The files for the two indices on a single table used 7.8GB of space
before a reindex, and 4.4GB after. The table had been reindexed over
the weekend and a vacuum was completed on the table about 2 hours ago.
The two indices are now 3.4GB smaller. I don't think this counts as
bloat, because of our use case. Even so, we reindex our whole database
every weekend.
-- Alan
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: postgresql performance tuning
2005-12-06 16:14 postgresql performance tuning Ameet Kini <akini@cs.wisc.edu>
2005-12-06 20:07 ` Re: postgresql performance tuning Vivek Khera <vivek@khera.org>
2005-12-06 20:48 ` Re: postgresql performance tuning Alan Stange <stange@rentec.com>
@ 2005-12-06 21:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-12-06 21:39 ` Re: postgresql performance tuning Alan Stange <stange@rentec.com>
0 siblings, 1 reply; 18+ messages in thread
From: Tom Lane @ 2005-12-06 21:20 UTC (permalink / raw)
To: stange@rentec.com; +Cc: Vivek Khera <vivek@khera.org>; pgsql-performance
Alan Stange <stange@rentec.com> writes:
> Vivek Khera wrote:
>> what evidence do you have that you are suffering index bloat?
> The files for the two indices on a single table used 7.8GB of space
> before a reindex, and 4.4GB after.
That's not bloat ... that's pretty nearly in line with the normal
expectation for a btree index, which is about 2/3rds fill factor.
If the compacted index were 10X smaller then I'd agree that you have
a bloat problem.
Periodic reindexing on this scale is not doing a lot for you except
thrashing your disks --- you're just giving space back to the OS that
will shortly be sucked up again by the same index.
regards, tom lane
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: postgresql performance tuning
2005-12-06 16:14 postgresql performance tuning Ameet Kini <akini@cs.wisc.edu>
2005-12-06 20:07 ` Re: postgresql performance tuning Vivek Khera <vivek@khera.org>
2005-12-06 20:48 ` Re: postgresql performance tuning Alan Stange <stange@rentec.com>
2005-12-06 21:20 ` Re: postgresql performance tuning Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-12-06 21:39 ` Alan Stange <stange@rentec.com>
0 siblings, 0 replies; 18+ messages in thread
From: Alan Stange @ 2005-12-06 21:39 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Vivek Khera <vivek@khera.org>; pgsql-performance
Tom Lane wrote:
> Alan Stange <stange@rentec.com> writes:
>
>> Vivek Khera wrote:
>>
>>> what evidence do you have that you are suffering index bloat?
>>>
>
>
>> The files for the two indices on a single table used 7.8GB of space
>> before a reindex, and 4.4GB after.
>>
>
> That's not bloat ... that's pretty nearly in line with the normal
> expectation for a btree index, which is about 2/3rds fill factor.
> If the compacted index were 10X smaller then I'd agree that you have
> a bloat problem.
>
I wrote "I don't think this counts as bloat...". I still don't.
-- Alan
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: postgresql performance tuning
2005-12-06 16:14 postgresql performance tuning Ameet Kini <akini@cs.wisc.edu>
2005-12-06 20:07 ` Re: postgresql performance tuning Vivek Khera <vivek@khera.org>
@ 2005-12-06 22:03 ` Ameet Kini <akini@cs.wisc.edu>
2005-12-07 00:23 ` Re: postgresql performance tuning Michael Stone <mstone+postgres@mathom.us>
2005-12-07 15:50 ` Re: postgresql performance tuning Vivek Khera <vivek@khera.org>
1 sibling, 2 replies; 18+ messages in thread
From: Ameet Kini @ 2005-12-06 22:03 UTC (permalink / raw)
To: pgsql-performance
> what evidence do you have that you are suffering index bloat? or are
> you just looking for solutions to problems that don't exist as an
> academic exercise? :-)
Well, firstly, its not an academic exercise - Its very much of a real
problem that needs a real solution :)
I'm running postgresql v8.0 and my problem is that running vacuum on my
indices are blazing fast (upto 10x faster) AFTER running reindex. For a
table with only 1 index, the time to do a vacuum (without full) went down
from 45 minutes to under 3 minutes. Maybe thats not bloat but thats
surely surprising. And this was after running vacuum periodically.
Ameet
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
Ameet
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: postgresql performance tuning
2005-12-06 16:14 postgresql performance tuning Ameet Kini <akini@cs.wisc.edu>
2005-12-06 20:07 ` Re: postgresql performance tuning Vivek Khera <vivek@khera.org>
2005-12-06 22:03 ` Re: postgresql performance tuning Ameet Kini <akini@cs.wisc.edu>
@ 2005-12-07 00:23 ` Michael Stone <mstone+postgres@mathom.us>
1 sibling, 0 replies; 18+ messages in thread
From: Michael Stone @ 2005-12-07 00:23 UTC (permalink / raw)
To: pgsql-performance
On Tue, Dec 06, 2005 at 04:03:22PM -0600, Ameet Kini wrote:
>I'm running postgresql v8.0 and my problem is that running vacuum on my
>indices are blazing fast (upto 10x faster) AFTER running reindex. For a
>table with only 1 index, the time to do a vacuum (without full) went down
>from 45 minutes to under 3 minutes.
I've also noticed a fairly large increase in vacuum speed after a
reindex. (To the point where the reindex + vacuum was faster than just a
vacuum.)
Mike Stone
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: postgresql performance tuning
2005-12-06 16:14 postgresql performance tuning Ameet Kini <akini@cs.wisc.edu>
2005-12-06 20:07 ` Re: postgresql performance tuning Vivek Khera <vivek@khera.org>
2005-12-06 22:03 ` Re: postgresql performance tuning Ameet Kini <akini@cs.wisc.edu>
@ 2005-12-07 15:50 ` Vivek Khera <vivek@khera.org>
1 sibling, 0 replies; 18+ messages in thread
From: Vivek Khera @ 2005-12-07 15:50 UTC (permalink / raw)
To: pgsql-performance
On Dec 6, 2005, at 5:03 PM, Ameet Kini wrote:
> table with only 1 index, the time to do a vacuum (without full)
> went down
> from 45 minutes to under 3 minutes. Maybe thats not bloat but thats
> surely surprising. And this was after running vacuum periodically.
I'll bet either your FSM settings are too low and/or you don't vacuum
often enough for your data churn rate.
Without more data, it is hard to solve the right problem.
^ permalink raw reply [nested|flat] 18+ messages in thread
* postgresql performance tuning
@ 2005-12-06 17:44 Ameet Kini <akini@cs.wisc.edu>
2005-12-06 18:03 ` Re: postgresql performance tuning Tom Lane <tgl@sss.pgh.pa.us>
2005-12-06 18:33 ` Re: postgresql performance tuning Vivek Khera <vivek@khera.org>
2005-12-06 21:02 ` Re: postgresql performance tuning Michael Riess <mlriess@gmx.de>
0 siblings, 3 replies; 18+ messages in thread
From: Ameet Kini @ 2005-12-06 17:44 UTC (permalink / raw)
To: pgsql-performance
This didn't get through the first time around, so resending it again.
Sorry for any duplicate entries.
Hello,
I have a question on postgres's performance tuning, in particular, the
vacuum and reindex commands. Currently I do a vacuum (without full) on all
of my tables. However, its noted in the docs (e.g.
http://developer.postgresql.org/docs/postgres/routine-reindex.html)
and on the lists here that indexes may still bloat after a while and hence
reindex is necessary. How often do people reindex their tables out
there? I guess I'd have to update my cron scripts to do reindexing too
along with vacuuming but most probably at a much lower frequency than
vacuum.
But these scripts do these maintenance tasks at a fixed time (every few
hours, days, weeks, etc.) What I would like is to do these tasks on a need
basis. So for vacuuming, by "need" I mean every few updates or some such
metric that characterizes my workload. Similarly, "need" for the reindex
command might mean every few updates or degree of bloat, etc.
I came across the pg_autovacuum daemon, which seems to do exactly what I
need for vacuums. However, it'd be great if there was a similar automatic
reindex utility, like say, a pg_autoreindex daemon. Are there any plans
for this feature? If not, then would cron scripts be the next best
choice?
Thanks,
Ameet
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: postgresql performance tuning
2005-12-06 17:44 postgresql performance tuning Ameet Kini <akini@cs.wisc.edu>
@ 2005-12-06 18:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 18+ messages in thread
From: Tom Lane @ 2005-12-06 18:03 UTC (permalink / raw)
To: Ameet Kini <akini@cs.wisc.edu>; +Cc: pgsql-performance
Ameet Kini <akini@cs.wisc.edu> writes:
> I have a question on postgres's performance tuning, in particular, the
> vacuum and reindex commands. Currently I do a vacuum (without full) on all
> of my tables. However, its noted in the docs (e.g.
> http://developer.postgresql.org/docs/postgres/routine-reindex.html)
> and on the lists here that indexes may still bloat after a while and hence
> reindex is necessary. How often do people reindex their tables out
> there?
Never, unless you have actual evidence that your indexes are bloating.
It's only very specific use-patterns that have problems.
regards, tom lane
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: postgresql performance tuning
2005-12-06 17:44 postgresql performance tuning Ameet Kini <akini@cs.wisc.edu>
@ 2005-12-06 18:33 ` Vivek Khera <vivek@khera.org>
2 siblings, 0 replies; 18+ messages in thread
From: Vivek Khera @ 2005-12-06 18:33 UTC (permalink / raw)
To: pgsql-performance
On Dec 6, 2005, at 12:44 PM, Ameet Kini wrote:
> I have a question on postgres's performance tuning, in particular, the
> vacuum and reindex commands. Currently I do a vacuum (without full)
> on all
> of my tables. However, its noted in the docs (e.g.
> http://developer.postgresql.org/docs/postgres/routine-reindex.html)
> and on the lists here that indexes may still bloat after a while
> and hence
> reindex is necessary. How often do people reindex their tables out
Why would you be running a version older than 7.4? Index bloat is
mostly a non-issue in recent releases of pg.
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: postgresql performance tuning
2005-12-06 17:44 postgresql performance tuning Ameet Kini <akini@cs.wisc.edu>
@ 2005-12-06 21:02 ` Michael Riess <mlriess@gmx.de>
2 siblings, 0 replies; 18+ messages in thread
From: Michael Riess @ 2005-12-06 21:02 UTC (permalink / raw)
To: pgsql-performance
Ameet Kini schrieb:
>
> This didn't get through the first time around, so resending it again.
> Sorry for any duplicate entries.
>
> Hello,
>
> I have a question on postgres's performance tuning, in particular, the
> vacuum and reindex commands. Currently I do a vacuum (without full) on all
> of my tables.
I'm curious ... why no full vacuum? I bet that the full vacuum will
compact your (index) tables as much as a reindex would.
I guess the best advice is to increase FSM and to use autovacuum.
^ permalink raw reply [nested|flat] 18+ messages in thread
* PostgreSQL Performance Tuning
@ 2007-04-26 11:19 Shohab Abdullah <Shohab.Abdullah@lntinfotech.com>
2007-04-26 19:49 ` Re: [GENERAL] PostgreSQL Performance Tuning Steve Crawford <scrawford@pinpointresearch.com>
0 siblings, 1 reply; 18+ messages in thread
From: Shohab Abdullah @ 2007-04-26 11:19 UTC (permalink / raw)
To: pgsql-general@postgresql.org; pgsql-performance; pgsql-performance-owner@postgresql.org; majordomo-owner@postgresql.org; +Cc: Vaishali Mane <Vaishali.Mane@lntinfotech.com>
Dear,
We are facing performance tuning problem while using PostgreSQL Database
over the network on a linux OS.
Our Database consists of more than 500 tables with an average of 10K
records per table with an average of 20 users accessing the database
simultaneously over the network. Each table has indexes and we are
querying the database using Hibernate.
The biggest problem is while insertion, updating and fetching of records,
ie the database performance is very slow. It take a long time to respond
in the above scenario.
Please provide me with the tuning of the database. I am attaching my
postgresql.conf file for the reference of our current configuration
Please replay me ASAP
Regards,
Shohab Abdullah
Software Engineer,
Manufacturing SBU-POWAI
Larsen and Toubro Infotech Ltd.| 4th floor, L&T Technology Centre,
Saki Vihar Road, Powai, Mumbai-400072
(: +91-22-67767366 | (: +91-9870247322
Visit us at : http://www.lntinfotech.com
”I cannot predict future, I cannot change past, I have just the present
moment, I must treat it as my last"
----------------------------------------------------------------------------------------
The information contained in this email has been classified:
[ X] L&T Infotech General Business
[ ] L&T Infotech Internal Use Only
[ ] L&T Infotech Confidential
[ ] L&T Infotech Proprietary
This e-mail and any files transmitted with it are for the sole use of the
intended recipient(s) and may contain confidential and privileged
information.
If you are not the intended recipient, please contact the sender by reply
e-mail and destroy all copies of the original message.
______________________________________________________________________
Attachments:
postgresql.conf (application/octet-stream, 13.4K) download
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [GENERAL] PostgreSQL Performance Tuning
2007-04-26 11:19 PostgreSQL Performance Tuning Shohab Abdullah <Shohab.Abdullah@lntinfotech.com>
@ 2007-04-26 19:49 ` Steve Crawford <scrawford@pinpointresearch.com>
0 siblings, 0 replies; 18+ messages in thread
From: Steve Crawford @ 2007-04-26 19:49 UTC (permalink / raw)
To: Shohab Abdullah <Shohab.Abdullah@lntinfotech.com>; +Cc: pgsql-performance@postgresql.org, majordomo-owner@postgresql.org, Vaishali Mane <Vaishali.Mane@lntinfotech.com>
Shohab Abdullah wrote:
>
> Dear,
> We are facing performance tuning problem while using PostgreSQL Database
> over the network on a linux OS.
> Our Database consists of more than 500 tables with an average of 10K
> records per table with an average of 20 users accessing the database
> simultaneously over the network. Each table has indexes and we are
> querying the database using Hibernate.
> The biggest problem is while insertion, updating and fetching of
> records, ie the database performance is very slow. It take a long time
> to respond in the above scenario.
> Please provide me with the tuning of the database. I am attaching my
> *postgresql.conf* file for the reference of our current configuration
Have you changed _anything_ from the defaults? The defaults are set so
PG will run on as many installations as practical. They are not set for
performance - that is specific to your equipment, your data, and how you
need to handle the data. Assuming the record sizes aren't huge, that's
not a very large data set nor number of users.
Look at these for starters:
http://www.varlena.com/GeneralBits/Tidbits/perf.html
http://www.varlena.com/GeneralBits/Tidbits/annotated_conf_e.html
You might try setting the logging parameters to log queries longer than
"x" (where x is in milliseconds - you will have to decide the
appropriate value for "too long") and start looking into those first.
Make sure that you are running "analyze" if it is not being run by
autovacuum.
Use "EXPLAIN <your query>" to see how the query is being planned - as a
first-pass assume that on any reasonably sized table the words
"sequential scan" means "fix this". Note that you may have to cast
variables in a query to match the variable in an index in order for the
planner to figure out that it can use the index.
Read the guidelines then take an educated stab at some settings and see
how they work - other than turning off fsync, there's not much in
postgresql.conf that will put your data at risk.
Cheers,
Steve
^ permalink raw reply [nested|flat] 18+ messages in thread
* Fw: PostgreSQL Performance Tuning
@ 2007-04-26 11:38 Shohab Abdullah <Shohab.Abdullah@lntinfotech.com>
2007-04-26 11:53 ` Re: Fw: PostgreSQL Performance Tuning Richard Huxton <dev@archonet.com>
2007-04-26 11:53 ` Re: Fw: PostgreSQL Performance Tuning Richard Huxton <dev@archonet.com>
2007-04-26 17:02 ` Re: Fw: PostgreSQL Performance Tuning Gerhard Wiesinger <lists@wiesinger.com>
0 siblings, 3 replies; 18+ messages in thread
From: Shohab Abdullah @ 2007-04-26 11:38 UTC (permalink / raw)
To: pgsql-general@postgresql.org; pgsql-performance; pgsql-performance-owner@postgresql.org; majordomo-owner@postgresql.org
Dear,
We are facing performance tuning problem while using PostgreSQL Database
over the network on a linux OS.
Our Database consists of more than 500 tables with an average of 10K
records per table with an average of 20 users accessing the database
simultaneously over the network. Each table has indexes and we are
querying the database using Hibernate.
The biggest problem is while insertion, updating and fetching of records,
ie the database performance is very slow. It take a long time to respond
in the above scenario.
Please provide me with the tuning of the database. I am attaching my
postgresql.conf file for the reference of our current configuration
Please replay me ASAP
Regards,
Shohab Abdullah
Software Engineer,
Manufacturing SBU-POWAI
Larsen and Toubro Infotech Ltd.| 4th floor, L&T Technology Centre,
Saki Vihar Road, Powai, Mumbai-400072
(: +91-22-67767366 | (: +91-9870247322
Visit us at : http://www.lntinfotech.com
”I cannot predict future, I cannot change past, I have just the present
moment, I must treat it as my last"
----------------------------------------------------------------------------------------
The information contained in this email has been classified:
[ X] L&T Infotech General Business
[ ] L&T Infotech Internal Use Only
[ ] L&T Infotech Confidential
[ ] L&T Infotech Proprietary
This e-mail and any files transmitted with it are for the sole use of the
intended recipient(s) and may contain confidential and privileged
information.
If you are not the intended recipient, please contact the sender by reply
e-mail and destroy all copies of the original message.
______________________________________________________________________
Attachments:
postgresql.conf (application/octet-stream, 13.4K) download
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Fw: PostgreSQL Performance Tuning
2007-04-26 11:38 Fw: PostgreSQL Performance Tuning Shohab Abdullah <Shohab.Abdullah@lntinfotech.com>
@ 2007-04-26 11:53 ` Richard Huxton <dev@archonet.com>
2 siblings, 0 replies; 18+ messages in thread
From: Richard Huxton @ 2007-04-26 11:53 UTC (permalink / raw)
To: Shohab Abdullah <Shohab.Abdullah@lntinfotech.com>; +Cc: pgsql-performance
Please try to keep postings to one mailing list - I've replied to the
performance list here.
Shohab Abdullah wrote:
> Dear,
> We are facing performance tuning problem while using PostgreSQL Database
> over the network on a linux OS.
> Our Database consists of more than 500 tables with an average of 10K
> records per table with an average of 20 users accessing the database
> simultaneously over the network. Each table has indexes and we are
> querying the database using Hibernate.
> The biggest problem is while insertion, updating and fetching of records,
> ie the database performance is very slow. It take a long time to respond
> in the above scenario.
> Please provide me with the tuning of the database. I am attaching my
> postgresql.conf file for the reference of our current configuration
You haven't provided any details on what version of PG you are using,
what hardware you are using, whether there is a specific bottleneck
(disk, memory, cpu) or certain queries.
Without that, no-one can suggest useful settings. You might find this
document a good place to start: http://www.powerpostgresql.com/PerfList/
HTH
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Fw: PostgreSQL Performance Tuning
2007-04-26 11:38 Fw: PostgreSQL Performance Tuning Shohab Abdullah <Shohab.Abdullah@lntinfotech.com>
@ 2007-04-26 11:53 ` Richard Huxton <dev@archonet.com>
2 siblings, 0 replies; 18+ messages in thread
From: Richard Huxton @ 2007-04-26 11:53 UTC (permalink / raw)
To: Shohab Abdullah <Shohab.Abdullah@lntinfotech.com>; +Cc: pgsql-general@postgresql.org; pgsql-performance; pgsql-performance-owner@postgresql.org; majordomo-owner@postgresql.org
Please try to post to one list at a time.
I've replied to this on the -performance list.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Fw: PostgreSQL Performance Tuning
2007-04-26 11:38 Fw: PostgreSQL Performance Tuning Shohab Abdullah <Shohab.Abdullah@lntinfotech.com>
@ 2007-04-26 17:02 ` Gerhard Wiesinger <lists@wiesinger.com>
2 siblings, 0 replies; 18+ messages in thread
From: Gerhard Wiesinger @ 2007-04-26 17:02 UTC (permalink / raw)
To: Shohab Abdullah <Shohab.Abdullah@lntinfotech.com>; +Cc: pgsql-general@postgresql.org; pgsql-performance
Hello!
I would do the following (in that order):
1.) Check for a performant application logic and application design (e.g.
degree of granularity of the Java Hibernate Mapping, are there some
object iterators with hundreds of objects, etc.)
2.) Check the hibernate generated queries and whether the query is
suitable or not. Also do a "explain query" do see the query plan.
Sometimes a manually generated is much more efficient than hibernate ones.
3.) Optimize the database e.g. postgresql.
Ciao,
Gerhard
--
http://www.wiesinger.com/
On Thu, 26 Apr 2007, Shohab Abdullah wrote:
> Dear,
> We are facing performance tuning problem while using PostgreSQL Database
> over the network on a linux OS.
> Our Database consists of more than 500 tables with an average of 10K
> records per table with an average of 20 users accessing the database
> simultaneously over the network. Each table has indexes and we are
> querying the database using Hibernate.
> The biggest problem is while insertion, updating and fetching of records,
> ie the database performance is very slow. It take a long time to respond
> in the above scenario.
> Please provide me with the tuning of the database. I am attaching my
> postgresql.conf file for the reference of our current configuration
>
>
>
> Please replay me ASAP
> Regards,
> Shohab Abdullah
> Software Engineer,
> Manufacturing SBU-POWAI
> Larsen and Toubro Infotech Ltd.| 4th floor, L&T Technology Centre,
> Saki Vihar Road, Powai, Mumbai-400072
> (: +91-22-67767366 | (: +91-9870247322
> Visit us at : http://www.lntinfotech.com
> ÿÿI cannot predict future, I cannot change past, I have just the present
> moment, I must treat it as my last"
> ----------------------------------------------------------------------------------------
> The information contained in this email has been classified:
> [ X] L&T Infotech General Business
> [ ] L&T Infotech Internal Use Only
> [ ] L&T Infotech Confidential
> [ ] L&T Infotech Proprietary
> This e-mail and any files transmitted with it are for the sole use of the
> intended recipient(s) and may contain confidential and privileged
> information.
> If you are not the intended recipient, please contact the sender by reply
> e-mail and destroy all copies of the original message.
>
> ______________________________________________________________________
>
^ permalink raw reply [nested|flat] 18+ messages in thread
end of thread, other threads:[~2007-04-26 19:49 UTC | newest]
Thread overview: 18+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-06 16:14 postgresql performance tuning Ameet Kini <akini@cs.wisc.edu>
2005-12-06 20:07 ` Vivek Khera <vivek@khera.org>
2005-12-06 20:48 ` Alan Stange <stange@rentec.com>
2005-12-06 21:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-12-06 21:39 ` Alan Stange <stange@rentec.com>
2005-12-06 22:03 ` Ameet Kini <akini@cs.wisc.edu>
2005-12-07 00:23 ` Michael Stone <mstone+postgres@mathom.us>
2005-12-07 15:50 ` Vivek Khera <vivek@khera.org>
2005-12-06 17:44 postgresql performance tuning Ameet Kini <akini@cs.wisc.edu>
2005-12-06 18:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-12-06 18:33 ` Vivek Khera <vivek@khera.org>
2005-12-06 21:02 ` Michael Riess <mlriess@gmx.de>
2007-04-26 11:19 PostgreSQL Performance Tuning Shohab Abdullah <Shohab.Abdullah@lntinfotech.com>
2007-04-26 19:49 ` Re: [GENERAL] PostgreSQL Performance Tuning Steve Crawford <scrawford@pinpointresearch.com>
2007-04-26 11:38 Fw: PostgreSQL Performance Tuning Shohab Abdullah <Shohab.Abdullah@lntinfotech.com>
2007-04-26 11:53 ` Re: Fw: PostgreSQL Performance Tuning Richard Huxton <dev@archonet.com>
2007-04-26 11:53 ` Re: Fw: PostgreSQL Performance Tuning Richard Huxton <dev@archonet.com>
2007-04-26 17:02 ` Re: Fw: PostgreSQL Performance Tuning Gerhard Wiesinger <lists@wiesinger.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox