Re: [PERFORM] shared-buffers set to 24GB but the RAM only use 4-5 GB average

2015-10-07 Thread Merlin Moncure
On Wed, Oct 7, 2015 at 5:29 AM, FattahRozzaq wrote: > Response from you all are very precious. > > @Merlin, > I'm misunderstood the question. > Yes, I didn't measure it. I only monitor RAM and CPU using htop (I also use Can you be a little more specific. What values did you look at and how did y

Re: [PERFORM] shared-buffers set to 24GB but the RAM only use 4-5 GB average

2015-10-07 Thread Scott Marlowe
On Wed, Oct 7, 2015 at 4:29 AM, FattahRozzaq wrote: > Response from you all are very precious. > > @Merlin, > I'm misunderstood the question. > Yes, I didn't measure it. I only monitor RAM and CPU using htop (I also use > nmon for disk IO, iftop for the network utilization). > Did 1 connection nee

Re: [PERFORM] shared-buffers set to 24GB but the RAM only use 4-5 GB average

2015-10-07 Thread FattahRozzaq
Response from you all are very precious. @Merlin, I'm misunderstood the question. Yes, I didn't measure it. I only monitor RAM and CPU using htop (I also use nmon for disk IO, iftop for the network utilization). Did 1 connection need 1 core dedicatedly? (I was having 40-80 connections in stable co

Re: [PERFORM] shared-buffers set to 24GB but the RAM only use 4-5 GB average

2015-10-06 Thread Josh Berkus
On 10/06/2015 02:33 AM, FattahRozzaq wrote: > @Merlin Moncure, I got the calculation using pg_tune. And I modified > the shared_buffers=24GB and the effective_cache_size=64GB I really need to get Greg to take down pg_tune. It's way out of date. Probably, I should replace it. -- Josh Berkus Post

Re: [PERFORM] shared-buffers set to 24GB but the RAM only use 4-5 GB average

2015-10-06 Thread Merlin Moncure
On Tue, Oct 6, 2015 at 10:10 AM, Scott Marlowe wrote: > On Tue, Oct 6, 2015 at 3:33 AM, FattahRozzaq wrote: >> @Merlin Moncure, I got the calculation using pg_tune. And I modified >> the shared_buffers=24GB and the effective_cache_size=64GB >> >> @Igor Neyman, >> Yes, I had performance problem wh

Re: [PERFORM] shared-buffers set to 24GB but the RAM only use 4-5 GB average

2015-10-06 Thread Scott Marlowe
On Tue, Oct 6, 2015 at 3:33 AM, FattahRozzaq wrote: > @Merlin Moncure, I got the calculation using pg_tune. And I modified > the shared_buffers=24GB and the effective_cache_size=64GB > > @Igor Neyman, > Yes, I had performance problem which sometimes the response time took > 11ms, with the exactly

Re: [PERFORM] shared-buffers set to 24GB but the RAM only use 4-5 GB average

2015-10-06 Thread FattahRozzaq
: > > > -Original Message- > From: pgsql-performance-ow...@postgresql.org > [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Igor Neyman > Sent: Monday, October 05, 2015 2:25 PM > To: FattahRozzaq ; pgsql-performance@postgresql.org > Subject: Re: [PERFORM] shared-buf

Re: [PERFORM] shared-buffers set to 24GB but the RAM only use 4-5 GB average

2015-10-05 Thread Igor Neyman
-Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Igor Neyman Sent: Monday, October 05, 2015 2:25 PM To: FattahRozzaq ; pgsql-performance@postgresql.org Subject: Re: [PERFORM] shared-buffers set to 24GB but the

Re: [PERFORM] shared-buffers set to 24GB but the RAM only use 4-5 GB average

2015-10-05 Thread Igor Neyman
-Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of FattahRozzaq Sent: Monday, October 05, 2015 10:51 AM To: pgsql-performance@postgresql.org Subject: [PERFORM] shared-buffers set to 24GB but the RAM only use 4-5

Re: [PERFORM] shared-buffers set to 24GB but the RAM only use 4-5 GB average

2015-10-05 Thread Merlin Moncure
On Mon, Oct 5, 2015 at 9:51 AM, FattahRozzaq wrote: > I have configured postgresql.conf with parameters as below: > > log_destination = 'stderr' > logging_collector = on > log_directory = 'pg_log' > listen_addresses = '*' > log_destination = 'stderr' > logging_collector = on > log_directory = 'pg_

[PERFORM] shared-buffers set to 24GB but the RAM only use 4-5 GB average

2015-10-05 Thread FattahRozzaq
I have configured postgresql.conf with parameters as below: log_destination = 'stderr' logging_collector = on log_directory = 'pg_log' listen_addresses = '*' log_destination = 'stderr' logging_collector = on log_directory = 'pg_log' log_rotation_age = 1d log_rotation_size = 1024MB listen_addresses

Re: [PERFORM] Shared buffers, db transactions commited, and write IO on Solaris

2007-04-18 Thread Jim C. Nasby
On Fri, Mar 30, 2007 at 11:19:09AM -0500, Erik Jones wrote: > >On Fri, Mar 30, 2007 at 04:25:16PM +0200, Dimitri wrote: > >>The problem is while your goal is to commit as fast as possible - > >>it's > >>pity to vast I/O operation speed just keeping common block size... > >>Let's say if your trans

Re: [PERFORM] Shared buffers, db transactions commited, and write IO on Solaris

2007-04-03 Thread Erik Jones
On Apr 3, 2007, at 11:51 AM, Dimitri wrote: Well, to check if there is a real potential gain all we need is a small comparing test using PgSQL compiled with LOG block size equal to say 1K and direct IO enabled. Rgds, -Dimitri On 3/30/07, Kenneth Marshall <[EMAIL PROTECTED]> wrote: > On Fr

Re: [PERFORM] Shared buffers, db transactions commited, and write IO on Solaris

2007-04-03 Thread Dimitri
Folks, to close topic with "LOG block size=1K" idea - I took a time to test it (yes) and in best cases there is only 15% gain comparing to 8K - storage protocol is quite heavy itself, so less or more data sent within it doesn't reduce service time too much... As well even this gain is quickly dec

Re: [PERFORM] Shared buffers, db transactions commited, and write IO on Solaris

2007-04-03 Thread Josh Berkus
Erik, > Well, our application code itself doesn't retry queries if the db is > taking a long time to respond. However, we do have a number of our > servers making db connections via pgpool so you may be on to > something here. While I will be taking these questions to the pgpool > lists, I'll po

Re: [PERFORM] Shared buffers, db transactions commited, and write IO on Solaris

2007-03-31 Thread Erik Jones
On Mar 30, 2007, at 4:46 PM, Josh Berkus wrote: Erik, You'er welcome! However, I believe our situation is very different from what you're testing if I understand you correctly. Are you saying that you're entire database will fit in memory? If so, then these are very different situations as

Re: [PERFORM] Shared buffers, db transactions commited, and write IO on Solaris

2007-03-30 Thread Josh Berkus
Erik, > You'er welcome! However, I believe our situation is very different > from what you're testing if I understand you correctly. Are you > saying that you're entire database will fit in memory? If so, then > these are very different situations as there is no way ours could > ever do that.

Re: [PERFORM] Shared buffers, db transactions commited, and write IO on Solaris

2007-03-30 Thread Erik Jones
On Mar 30, 2007, at 10:05 AM, Kenneth Marshall wrote: On Fri, Mar 30, 2007 at 04:25:16PM +0200, Dimitri wrote: The problem is while your goal is to commit as fast as possible - it's pity to vast I/O operation speed just keeping common block size... Let's say if your transaction modification

Re: [PERFORM] Shared buffers, db transactions commited, and write IO on Solaris

2007-03-30 Thread Dimitri
The problem is while your goal is to commit as fast as possible - it's pity to vast I/O operation speed just keeping common block size... Let's say if your transaction modification entering into 512K - you'll be able to write much more 512K blocks per second rather 8K per second (for the same amou

Re: [PERFORM] Shared buffers, db transactions commited, and write IO on Solaris

2007-03-30 Thread Erik Jones
On Mar 30, 2007, at 8:14 AM, Dimitri wrote: You are right in that the page size constraint is lifted in that directio cuts out the VM filesystem cache. However, the Solaris kernel still issues io ops in terms of its logical block size (which we have at the default 8K). It can issue io ops fo

Re: [PERFORM] Shared buffers, db transactions commited, and write IO on Solaris

2007-03-30 Thread Dimitri
You are right in that the page size constraint is lifted in that directio cuts out the VM filesystem cache. However, the Solaris kernel still issues io ops in terms of its logical block size (which we have at the default 8K). It can issue io ops for fragments as small as 1/8th of the block size

Re: [PERFORM] Shared buffers, db transactions commited, and write IO on Solaris

2007-03-29 Thread Erik Jones
On Mar 29, 2007, at 5:15 PM, Dimitri wrote: >> > Erik, > > using 'forcedirectio' simply brings your write operations to the > *real* volume - means while you need to write 10 bytes you'll write 10 > bytes (instead of UFS block size (8K)). So it explains me why your > write volume became slowe

Re: [PERFORM] Shared buffers, db transactions commited, and write IO on Solaris

2007-03-29 Thread Greg Smith
On Thu, 29 Mar 2007, Erik Jones wrote: As far as the procs responsible for the writes go, we were unable to see that from the OS level as the guy we had as a systems admin last year totally screwed us with the way he set up the SunCluster on the boxes and we have been unable to run Dtrace whic

Re: [PERFORM] Shared buffers, db transactions commited, and write IO on Solaris

2007-03-29 Thread Erik Jones
On Mar 29, 2007, at 7:23 PM, Josh Berkus wrote: Erik, Wow, thanks for the post. We've just started testing the option of sizing shared_buffers bigger than the database, and using forcedirectio in benchmarks at Sun. So far, our experience has been *equal* performance in that configuratio

Re: [PERFORM] Shared buffers, db transactions commited, and write IO on Solaris

2007-03-29 Thread Josh Berkus
Erik, Wow, thanks for the post. We've just started testing the option of sizing shared_buffers bigger than the database, and using forcedirectio in benchmarks at Sun. So far, our experience has been *equal* performance in that configuration, so it's *very* interesting to see you're getting a

Re: [PERFORM] Shared buffers, db transactions commited, and write IO on Solaris

2007-03-29 Thread Dimitri
>> > Erik, > > using 'forcedirectio' simply brings your write operations to the > *real* volume - means while you need to write 10 bytes you'll write 10 > bytes (instead of UFS block size (8K)). So it explains me why your > write volume became slower. I men 'lower' (not slower) Sorry, that's

Re: [PERFORM] Shared buffers, db transactions commited, and write IO on Solaris

2007-03-29 Thread Erik Jones
On Mar 29, 2007, at 2:19 PM, Tom Lane wrote: Erik Jones <[EMAIL PROTECTED]> writes: One very important thing to note here is that the number, or rather rate, of disk writes has not changed. It's the volume of data in those writes that has dropped, along with those transaction mysterious counts

Re: [PERFORM] Shared buffers, db transactions commited, and write IO on Solaris

2007-03-29 Thread Tom Lane
Erik Jones <[EMAIL PROTECTED]> writes: > One very important thing to note here is that the number, or rather > rate, of disk writes has not changed. It's the volume of data in > those writes that has dropped, along with those transaction > mysterious counts. Hmm. I'm suddenly thinking abou

Re: [PERFORM] Shared buffers, db transactions commited, and write IO on Solaris

2007-03-29 Thread Erik Jones
On Mar 29, 2007, at 12:41 PM, dimitri k wrote: On 3/29/07, Erik Jones <[EMAIL PROTECTED]> wrote: On Mar 29, 2007, at 11:16 AM, Tom Lane wrote: > Erik Jones <[EMAIL PROTECTED]> writes: >> We've recently made a couple changes to our system that have resulted >> in a drastic increase in perform

Re: [PERFORM] Shared buffers, db transactions commited, and write IO on Solaris

2007-03-29 Thread dimitri k
Erik, using 'forcedirectio' simply brings your write operations to the *real* volume - means while you need to write 10 bytes you'll write 10 bytes (instead of UFS block size (8K)). So it explains me why your write volume became slower. Now, why TX number is reduced - is a small mystery :) Opti

Re: [PERFORM] Shared buffers, db transactions commited, and write IO on Solaris

2007-03-29 Thread Erik Jones
On Mar 29, 2007, at 11:16 AM, Tom Lane wrote: Erik Jones <[EMAIL PROTECTED]> writes: We've recently made a couple changes to our system that have resulted in a drastic increase in performance as well as some very confusing changes to the database statistics, specifically pg_stat_database.xact_c

Re: [PERFORM] Shared buffers, db transactions commited, and write IO on Solaris

2007-03-29 Thread Tom Lane
Erik Jones <[EMAIL PROTECTED]> writes: > We've recently made a couple changes to our system that have resulted > in a drastic increase in performance as well as some very confusing > changes to the database statistics, specifically > pg_stat_database.xact_commit. Here's the details: I'm kin

[PERFORM] Shared buffers, db transactions commited, and write IO on Solaris

2007-03-28 Thread Erik Jones
Greetings, We've recently made a couple changes to our system that have resulted in a drastic increase in performance as well as some very confusing changes to the database statistics, specifically pg_stat_database.xact_commit. Here's the details: OS: Solaris10 x86 Server: Sunfire X4100,

Re: [PERFORM] shared buffers

2005-09-11 Thread John A Meinel
Martin Nickel wrote: > Chris, > Would you say that 3 pages is a good maximum for a Postgres install? > We're running 8.0.3 on 64-bit SUSE on a dual Opteron box with 4G and have > shared_buffers set at 12. I've moved it up and down (it was 16 > when I got here) without any measurable pe

Re: [PERFORM] shared buffers

2005-09-05 Thread Martin Nickel
Chris, Would you say that 3 pages is a good maximum for a Postgres install? We're running 8.0.3 on 64-bit SUSE on a dual Opteron box with 4G and have shared_buffers set at 12. I've moved it up and down (it was 16 when I got here) without any measurable performance difference. The reas

Re: [PERFORM] shared buffers

2005-08-30 Thread William Yu
Carlos Henrique Reimer wrote: I forgot to say that it´s a 12GB database... Ok, I´ll set shared buffers to 30.000 pages but even so "meminfo" and "top" shouldn´t show some shared pages? I heard something about that Redhat 9 can´t handle very well RAM higher than 2GB. Is it right? Thanks in

Re: [PERFORM] shared buffers

2005-08-29 Thread Tom Lane
Carlos Henrique Reimer <[EMAIL PROTECTED]> writes: > I heard something about that Redhat 9 can´t handle very well RAM higher than > 2GB. Is it right? RHL 9 is certainly pretty long in the tooth. Why aren't you using a more recent distro? regards, tom lane --

Re: [PERFORM] shared buffers

2005-08-29 Thread Christopher Kings-Lynne
I forgot to say that it´s a 12GB database... That's actually not that large. Ok, I´ll set shared buffers to 30.000 pages but even so "meminfo" and "top" shouldn´t show some shared pages? Yeah. The reason for not setting buffers so high is because PostgreSQL cannot efficiently manage huge sh

Re: [PERFORM] shared buffers

2005-08-29 Thread Carlos Henrique Reimer
I forgot to say that it´s a 12GB database...   Ok, I´ll set shared buffers to 30.000 pages but even so "meminfo" and "top" shouldn´t show some shared pages?   I heard something about that Redhat 9 can´t handle very well RAM higher than 2GB. Is it right? Thanks in advance!   Reimer Christopher King

Re: [PERFORM] shared buffers

2005-08-29 Thread Christopher Kings-Lynne
I´ve configured postgresql to use 1GB of shared buffers but meminfo and "top" are indicanting 0 shared buffers page. Why? 1GB shared buffers is far too much. Set it back to like 3 buffers max... Chris ---(end of broadcast)--- TIP 4: Have

[PERFORM] shared buffers

2005-08-29 Thread Carlos Henrique Reimer
Hi,   I´ve configured postgresql to use 1GB of shared buffers but meminfo and "top" are indicanting 0 shared buffers page. Why?   It´s a Linux Redhat 9 box with 4GB RAM and postgresql 7.3.   Thanks in advance!   Reimer Yahoo! Acesso Grátis: Internet rápida e grátis. Instale o discador agora!

Re: [PERFORM] Shared buffers, Sort memory, Effective Cache Size

2004-04-27 Thread Manfred Koizar
On Wed, 21 Apr 2004 10:01:30 -0700, Qing Zhao <[EMAIL PROTECTED]> wrote: >I have recently configured my PG7.3 on a G5 (8GB RAM) with >shmmax set to 512MB and shared_buffer=5, sort_mem=4096 >and effective cache size = 1. It seems working great so far but >I am wondering if I should make eff

Re: [PERFORM] Shared buffers, Sort memory, Effective Cache Size

2004-04-26 Thread Qing Zhao
Hello, I have recently configured my PG7.3 on a G5 (8GB RAM) with shmmax set to 512MB and shared_buffer=5, sort_mem=4096 and effective cache size = 1. It seems working great so far but I am wondering if I should make effctive cache size larger myself. Tnaks! Qing On Apr 21, 2004, at 9:29 A

[PERFORM] Shared buffers, Sort memory, Effective Cache Size

2004-04-21 Thread Frédéric Robinet
Hello, I have a bi-PIII server with 2Gb of RAM with Debian and a PostgreSQL 7.4 running on. What are the bests settings for shared buffers, sort memory and effective cache size? My main database have a small/mid range size: some tables may have 1 or 2 millions of records. Thanks Frédéric Robin