Re: PostgreSQL Performance issue

Поиск
Список
Период
Сортировка
От
Тема Re: PostgreSQL Performance issue
Дата
Msg-id 4AD2336877609F41A2B0D53BAD09FC570107D371@VOO-EXCHANGE07.internal.sungard.corp
обсуждение исходный текст
Ответ на Re: PostgreSQL Performance issue  (Magnus Hagander <magnus@hagander.net>)
Ответы Re: PostgreSQL Performance issue
Список pgsql-general

Thanks a lot for your help. However I am new to Postgres database therefore it would be nice if you can let me know how to set autocommit off.

I know from psql client issuing “\set Autocommit Off” would set it off but unfortunately it doesn’t set it off.

 

Many thanks for your help.

 

 

 

 

From: Magnus Hagander [mailto:magnus@hagander.net]
Sent: Tuesday, April 27, 2010 2:19 PM
To: Bhattacharya, A
Cc: pgsql-general
Subject: Re: [GENERAL] PostgreSQL Performance issue

 

On Tue, Apr 27, 2010 at 10:11, <A.Bhattacharya@sungard.com> wrote:

Dear All Experts,

 

I am using in PostgreSQL 8.3.5 database on windows 64 bit OS.


You really need to upgrade. At least to 8.3.10. It has many important bugfixes.
 

However, I have a batch program written in Java which processes the data and populates them into tables in Postgres database.

 

I have 622,000 number of records but it is taking almost 4 and half hours to load these data into the tables.

I have a simple function in db which is being called from Java batch program to populate the records into tables from flat files.

 

I have the below system configuration for my database server.

Database Server

PostgreSQL v8.3.5

Operating System

Windows 2003 Server 64 bit, Service Pack 2

CPU

2 * Quad Core AMD Opteron Processor 2376 @ 2.29 GHz

Memory

16 GB RAM

Disk Space

total 2.5 TB [C drive – 454 GB & D drive 1.99 TB]


The interesting point is not how much disk you have, but what configuration you have it in. Eitehr way, 622k records in 4 1/2 hours is obviously crappy even for a single disk though.
 

 

 and I have set my postgresql.conf parameters as below.

 

shared_buffers = 1GB                                                                                                     


You might want to try to lower that one drastically, say 128Mb. In some cases, this has been known to give better performance on Windows. not in all case though, so you have to try it out.

 

temp_buffers = 256MB                                   

max_prepared_transactions = 100              


Are you really using 2-phase commit on the system? If not, turn this off. This is prepared transactions, not prepared statements.
 

========================================================================

 

Please advise me the best or optimum way setting these parameters to achieve better performance.

Also note that, when I am setting my shared_buffer = 2GB or high , Postgres is throwing an error “shared_buffer size cannot be more than size_t


That is normal since your binary is 32-bit. In fact, having shared_buffers at 1Gb may give you some trouble with your fairly high work_mem as well, as the *total* amount of memory in the process is limited. That's another reason to try a lower shared_buffers.
 

(other than that, read the comments from Thom)

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

В списке pgsql-general по дате отправления:

Предыдущее
От: Rod
Дата:
Сообщение: Re: Storing many big files in database- should I do it?
Следующее
От: Allan Kamau
Дата:
Сообщение: Re: Efficient intersect operation (Full Text Search)