0

I have my setup like this:

Two separate instances of the database on a clustered environment. Both instances are on dedicated servers each carries up to the standard configurations.

Instance one (R1) is used by an application which do day-to-day general purpose tasks for a certain client (this instance has read/write permissions).

Instance two (R2) used by a BI (Business intelligent) tool that generate large reports, data mining, and future referencing data (this instance has readonly permissions). This required heavy server load and CPU/Memory consumption (based on regular monitoring)

The server that running BI tool is also has pretty decent specifications and has allocated 12GB of memory as we thought the bottleneck was there.

Even though we allocate 12GB of memory to BI server, R2 seems lacking the performance.

Our database has more than 100 tables and primary keys are based on UUID. So my question is what are the best ways to optimize the database? Does indexing will give a performance enhancement?

Or what are the best tuning tips or tools out there..

Postgresql database version 9.1

Switch
  • 159
  • 10
  • What's the total database size - or the size of the actively part of it, anyway? What PostgreSQL version are you using? What disk subsystem do these servers have and what is its performance like? Please show `EXPLAIN (BUFFERS, ANALYZE)` of key slow queries. – Craig Ringer Mar 26 '13 at 05:54
  • At the time I don't have much details. I will later update the question with all the information. By the way thanks for the quick response. – Switch Mar 26 '13 at 06:04
  • Check out http://wiki.postgresql.org/wiki/Slow_Query_Questions and its links for guidance on what to include. – Craig Ringer Mar 26 '13 at 07:02

2 Answers2

1

Use the pgtune tool from pgfoundry. https://github.com/gregs1104/pgtune

pgtune will take the default postgresql.conf file, check the hardware and suggest configuration values.

Ankit
  • 215
  • 2
  • 9
1

Does indexing will give a performance enhancement?

Don't you have any indexes now (other than primary keys)? If not, then that's one place to start, though you need to understand your queries to determine where the indexes will give a benefit.

Apart from that you should look at postgresql.conf as there is plenty that can be done there - the defaults are very conservative.

There is also the maintenance regime to look at. If it's an older version of PostgreSQL then it may not be autovacuuming.

hmallett
  • 2,425
  • 14
  • 26