Re: [PERFORM] How to improve Postgres performance

2005-08-30 Thread Dennis Bjorklund
On Tue, 30 Aug 2005, Hemant Pandey wrote: So please tell me how can i improve database performance through configuration parameters. I had tried to change parameters in postgresql.conf file but of no avail. Now i am trying to Auto Vacuum, but don't know how to run Auto Vacuum. The most

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

[PERFORM] RAID Configuration Sugestion

2005-08-30 Thread Alvaro Nunes Melo
Hello, We are about to install a new PostgreSQL server, and despite of being a very humble configuration compared to the ones we see in the list, it's the biggest one we've got till now. The server is a Dual Xeon 3.0 with 2 GB RAM and two SCSI disks. Our main doubt is what is the best

Re: [PERFORM] RAID Configuration Sugestion

2005-08-30 Thread Bruno Wolff III
On Tue, Aug 30, 2005 at 09:37:17 -0300, Alvaro Nunes Melo [EMAIL PROTECTED] wrote: The server is a Dual Xeon 3.0 with 2 GB RAM and two SCSI disks. Our main doubt is what is the best configuration for the disks. We are thinking about use them in a RAID-0 array. Is this the best option?

[PERFORM] Observation about db response time

2005-08-30 Thread Akshay Mathur
Hello Friends, We were having a database in pgsql7.4.2 The database was responding very slowly even after full vacuum analyze (select count(*) from some_table_having_18000_records was taking 18 Sec). We took a backup of that db and restored it back. Now the same db on same PC is

Re: [PERFORM] Observation about db response time

2005-08-30 Thread Frank Wiles
On Tue, 30 Aug 2005 18:35:30 +0530 Akshay Mathur [EMAIL PROTECTED] wrote: Hello Friends, We were having a database in pgsql7.4.2 The database was responding very slowly even after full vacuum analyze (select count(*) from some_table_having_18000_records was taking 18 Sec). We took a

Re: [PERFORM] RAID Configuration Sugestion

2005-08-30 Thread Bruno Wolff III
Please keep replies copied to the list so that others may contribute to and learn from the discussion. On Tue, Aug 30, 2005 at 10:15:13 -0300, Alvaro Nunes Melo [EMAIL PROTECTED] wrote: Hello Bruno, Bruno Wolff III wrote: On Tue, Aug 30, 2005 at 09:37:17 -0300, Alvaro Nunes Melo [EMAIL

Re: [PERFORM] OSX Performance

2005-08-30 Thread Jeff Trout
On Aug 29, 2005, at 1:57 PM, Tom Lane wrote: You must have CFLAGS set to empty in your build environment, because configure will certainly default to -O2 if not overridden. It works fine for me on OS X. Maybe you want to trace through the configure script and see why it's doing something

Re: [PERFORM] RAID Configuration Sugestion

2005-08-30 Thread Ron
At 08:37 AM 8/30/2005, Alvaro Nunes Melo wrote: Hello, We are about to install a new PostgreSQL server, and despite of being a very humble configuration compared to the ones we see in the list, it's the biggest one we've got till now. The server is a Dual Xeon 3.0 with 2 GB RAM and two SCSI

Re: [PERFORM] Observation about db response time

2005-08-30 Thread Vivek Khera
On Aug 30, 2005, at 9:05 AM, Akshay Mathur wrote:We were having a database in pgsql7.4.2 The database was responding very slowly even after full vacuum analyze (select count(*) from some_table_having_18000_records was taking 18 Sec).On a 7.4.2 db, there should probably be no index bloat, but there

Re: [PERFORM] Need indexes on empty tables for good performance ?

2005-08-30 Thread Chris Travers
Lenard, Rohan (Rohan) wrote: I've read that indexes aren't used for COUNT(*) and I've noticed (7.3.x) with EXPLAIN that indexes never seem to be used on empty tables - is there any reason to have indexes on empty tables, or will postgresql never use them. You could add a row, vacuum

[PERFORM] Observation about db response time

2005-08-30 Thread akshay
Hello Friends, We were having a database in pgsql7.4. The database was responding very slowly even after full vacuum (select count(*) from some_table_having_18000_records was taking 18 Sec). We took a backup of that db and restored it back. Now the same db on same PC is responding fast (same

[PERFORM] High load and iowait but no disk access

2005-08-30 Thread Rémy Beaumont
We have been trying to pinpoint what originally seem to be a I/O bottleneck but which now seems to be an issue with either Postgresql or RHES 3. We have the following test environment on which we can reproduce the problem: 1) Test System A Dell 6650 Quad Xeon Pentium 4 8 Gig of RAM OS: RHES 3

Re: [PERFORM] RAID Configuration Sugestion

2005-08-30 Thread Matthew Nuzum
On Tue, Aug 30, 2005 at 09:37:17 -0300, Alvaro Nunes Melo [EMAIL PROTECTED] wrote: The server is a Dual Xeon 3.0 with 2 GB RAM and two SCSI disks. Our main doubt is what is the best configuration for the disks. We are thinking about use them in a RAID-0 array. Is this the best option?

Re: [PERFORM] Need indexes on empty tables for good performance ?

2005-08-30 Thread Lenard, Rohan (Rohan)
Actually the indexes on the child table do seem to get used - I just wanted to make sure there was no penalty not havingindexes on the empty parent tables. You are right - the parentis the best way to get at the unknown children ... From: Thomas F. O'Connell [mailto:[EMAIL

Re: [PERFORM] High load and iowait but no disk access

2005-08-30 Thread Rémy Beaumont
On 30-Aug-05, at 12:15, Tom Lane wrote: =?ISO-8859-1?Q?R=E9my_Beaumont?= [EMAIL PROTECTED] writes: The stats of the NetApp do confirm that it is sitting idle. Really? CPU NFS CIFS HTTP TotalNet kB/s Disk kB/s Tape kB/s Cache Cache CP CP Disk DAFS FCP iSCSI

Re: [PERFORM] High load and iowait but no disk access

2005-08-30 Thread Michael Stone
On Mon, Aug 29, 2005 at 09:42:46AM -0400, Rémy Beaumont wrote: We have been trying to pinpoint what originally seem to be a I/O bottleneck but which now seems to be an issue with either Postgresql or RHES 3. Nope, it's an IO bottleneck. The behavior we see is that when running queries that

Re: [PERFORM] High load and iowait but no disk access

2005-08-30 Thread Tom Lane
=?ISO-8859-1?Q?R=E9my_Beaumont?= [EMAIL PROTECTED] writes: The stats of the NetApp do confirm that it is sitting idle. Really? CPU NFS CIFS HTTP TotalNet kB/s Disk kB/s Tape kB/s Cache Cache CP CP Disk DAFS FCP iSCSI FCP kB/s in

Re: [PERFORM] High load and iowait but no disk access

2005-08-30 Thread Tom Lane
=?ISO-8859-1?Q?R=E9my_Beaumont?= [EMAIL PROTECTED] writes: On 30-Aug-05, at 12:15, Tom Lane wrote: I know zip about NetApps, but doesn't the 8th column indicate pretty steady disk reads? Yes, but they are very low. Sure, but that's more or less what you'd expect if the thing is randomly

Re: [PERFORM] High load and iowait but no disk access

2005-08-30 Thread Rémy Beaumont
On 30-Aug-05, at 12:29, Tom Lane wrote: =?ISO-8859-1?Q?R=E9my_Beaumont?= [EMAIL PROTECTED] writes: On 30-Aug-05, at 12:15, Tom Lane wrote: I know zip about NetApps, but doesn't the 8th column indicate pretty steady disk reads? Yes, but they are very low. Sure, but that's more or less

Re: [PERFORM] RAID Configuration Sugestion

2005-08-30 Thread Joshua D. Drake
Ron wrote: At 08:37 AM 8/30/2005, Alvaro Nunes Melo wrote: Hello, We are about to install a new PostgreSQL server, and despite of being a very humble configuration compared to the ones we see in the list, it's the biggest one we've got till now. The server is a Dual Xeon 3.0 with 2 GB

Re: [PERFORM] Need indexes on empty tables for good performance ?

2005-08-30 Thread Chris Travers
Lenard, Rohan (Rohan) wrote: Actually the indexes on the child table do seem to get used - I just wanted to make sure there was no penalty not having indexes on the empty parent tables. You are right - the parent is the best way to get at the unknown children ... Indexes are created in

Re: [PERFORM] RAID Configuration Sugestion

2005-08-30 Thread Ron
At 12:56 PM 8/30/2005, Joshua D. Drake wrote: Ron wrote: At 08:37 AM 8/30/2005, Alvaro Nunes Melo wrote: Hello, We are about to install a new PostgreSQL server, and despite of being a very humble configuration compared to the ones we see in the list, it's the biggest one we've got till

Re: [PERFORM] High load and iowait but no disk access

2005-08-30 Thread Josh Berkus
Remy, The behavior we see is that when running queries that do random reads on disk, IOWAIT goes over 80% and actual disk IO falls to a crawl at a throughput bellow 3000kB/s (We usually average 4 kB/s to 8 kB/s on sequential read operations on the netapps) This seems pretty low for a

Re: [PERFORM] High load and iowait but no disk access

2005-08-30 Thread Woody Woodring
Have you tried a different kernel? We run with a netapp over NFS without any issues, but we have seen high IO-wait on other Dell boxes (running and not running postgres) and RHES 3. We have replaced a Dell PowerEdge 350 running RH 7.3 with a PE750 with more memory running RHES3 and it be

Re: [PERFORM] High load and iowait but no disk access

2005-08-30 Thread Rémy Beaumont
On 30-Aug-05, at 14:32, Josh Berkus wrote: Remy, The behavior we see is that when running queries that do random reads on disk, IOWAIT goes over 80% and actual disk IO falls to a crawl at a throughput bellow 3000kB/s (We usually average 4 kB/s to 8 kB/s on sequential read

Re: [PERFORM] Observation about db response time

2005-08-30 Thread george young
On Sat, 27 Aug 2005 21:28:57 +0530 (IST) [EMAIL PROTECTED] threw this fish to the penguins: Hello Friends, We were having a database in pgsql7.4. The database was responding very slowly even after full vacuum (select count(*) from some_table_having_18000_records was taking 18 Sec). One

Re: [PERFORM] High load and iowait but no disk access

2005-08-30 Thread Rémy Beaumont
On 30-Aug-05, at 14:46, Anjan Dave wrote: I have seen references of changing the kernel io scheduler at boot time…not sure if it applies to RHEL3.0, or will help, but try setting ‘elevator=deadline’ during boot time or via grub.conf. That's only for RHEL 4.0. Have you tried running a

Re: [PERFORM] RAID Configuration Sugestion

2005-08-30 Thread Matthew Nuzum
On 8/30/05, Ron [EMAIL PROTECTED] wrote: If you still have the budget, I would suggest considering either what Ron suggested or possibly using a 4 drive RAID 10 instead. IME, with only 4 HDs, it's usually better to split them them into two RAID 1's (one for the db, one for everything else

Re: [PERFORM] High load and iowait but no disk access

2005-08-30 Thread Anjan Dave
I have seen references of changing the kernel io scheduler at boot timenot sure if it applies to RHEL3.0, or will help, but try setting elevator=deadline during boot time or via grub.conf. Have you tried running a simple dd on the LUN? The drives are in RAID10 configuration, right?

Re: [PERFORM] RAID Configuration Sugestion

2005-08-30 Thread Joshua D. Drake
If you still have the budget, I would suggest considering either what Ron suggested or possibly using a 4 drive RAID 10 instead. IME, with only 4 HDs, it's usually better to split them them into two RAID 1's (one for the db, one for everything else including the logs) than it is to put

Re: [PERFORM] High load and iowait but no disk access

2005-08-30 Thread mudfoot
This might be optimal behavior from the hardware. Random reads are hard to optimize for--except if you have enough physical memory to hold the entire dataset. Cached reads (either in array controller or OS buffer cache) should return nearly immediately. But random reads probably aren't cached.

[PERFORM] When to do a vacuum for highly active table

2005-08-30 Thread Markus Benne
We have a highly active table that has virtually all entries updated every 5 minutes. Typical size of the table is 50,000 entries, and entries have grown fat. We are currently vaccuming hourly, and towards the end of the hour we are seeing degradation, when compared to the top of the hour.

Re: [PERFORM] When to do a vacuum for highly active table

2005-08-30 Thread Tom Lane
Markus Benne [EMAIL PROTECTED] writes: We have a highly active table that has virtually all entries updated every 5 minutes. Typical size of the table is 50,000 entries, and entries have grown fat. We are currently vaccuming hourly, and towards the end of the hour we are seeing degradation,

Re: [PERFORM] When to do a vacuum for highly active table

2005-08-30 Thread mark
On Tue, Aug 30, 2005 at 05:29:17PM -0400, Tom Lane wrote: Markus Benne [EMAIL PROTECTED] writes: We have a highly active table that has virtually all entries updated every 5 minutes. Typical size of the table is 50,000 entries, and entries have grown fat. ... We are thinking of

[PERFORM] 'Real' auto vacuum?

2005-08-30 Thread Ralph Mason
This is a wild and crazy thought which I am sure is invalid for some good reason. But why can't postgres just vacuum itself as it goes along? When a row is orphaned it's added to a list of possibly available rows. When a new row is needed the list of possible rows is examined and the first

Re: [PERFORM] 'Real' auto vacuum?

2005-08-30 Thread mark
On Wed, Aug 31, 2005 at 10:21:20AM +1200, Ralph Mason wrote: This is a wild and crazy thought which I am sure is invalid for some good reason. But why can't postgres just vacuum itself as it goes along? When a row is orphaned it's added to a list of possibly available rows. When a new

Re: [PERFORM] 'Real' auto vacuum?

2005-08-30 Thread Josh Berkus
Ralph, When a row is orphaned it's added to a list of possibly available rows. When a new row is needed the list of possible rows is examined and the first one with a transaction id less then the lowest running transaction id is chosen to be the new row? These rows can be in a heap so it's

Re: [PERFORM] RAID Configuration Sugestion

2005-08-30 Thread Ron
At 03:27 PM 8/30/2005, Joshua D. Drake wrote: If you still have the budget, I would suggest considering either what Ron suggested or possibly using a 4 drive RAID 10 instead. IME, with only 4 HDs, it's usually better to split them them into two RAID 1's (one for the db, one for everything

Re: [PERFORM] When to do a vacuum for highly active table

2005-08-30 Thread Chris Browne
[EMAIL PROTECTED] (Markus Benne) writes: We have a highly active table that has virtually all entries updated every 5 minutes. Typical size of the table is 50,000 entries, and entries have grown fat. We are currently vaccuming hourly, and towards the end of the hour we are seeing

Re: [PERFORM] When to do a vacuum for highly active table

2005-08-30 Thread Tom Lane
[EMAIL PROTECTED] writes: I think he means splitting it vertically, instead of horizontally, and it sounds like an excellent idea, if a large enough portion of each record is in fact mostly fixed. Otherwise, PostgreSQL is copying data multiple times, only to have the data expire as part of a

Re: [PERFORM] 'Real' auto vacuum?

2005-08-30 Thread Ralph Mason
[EMAIL PROTECTED] wrote: But, yeah. It's probably not that easy, especially with really big databases. Where is this free list stored? How efficient is it to keep track of the lowest running transaction at all times? How does one synchronize access to this free list, to ensure that processes

Re: [PERFORM] RAID Configuration Sugestion

2005-08-30 Thread Michael Stone
On Tue, Aug 30, 2005 at 07:02:28PM -0400, Ron wrote: purpose(s). That's why the TPC bench marked systems tend to have literally 100's of HD's and they tend to be split into very focused purposes. Of course, TPC benchmark systems are constructed such that cost and storage capacity are

Re: [PERFORM] RAID Configuration Sugestion

2005-08-30 Thread Ron
At 08:04 PM 8/30/2005, Michael Stone wrote: On Tue, Aug 30, 2005 at 07:02:28PM -0400, Ron wrote: purpose(s). That's why the TPC bench marked systems tend to have literally 100's of HD's and they tend to be split into very focused purposes. Of course, TPC benchmark systems are constructed

Re: [PERFORM] RAID Configuration Sugestion

2005-08-30 Thread Michael Stone
On Tue, Aug 30, 2005 at 08:41:40PM -0400, Ron wrote: The scary thing is that I've worked on RW production systems that bore a striking resemblance to a TPC benchmark system. As you can imagine, they uniformly belonged to BIG organizations (read: lot's 'o $$$) who were using the systems for

Re: [PERFORM] RAID Configuration Sugestion

2005-08-30 Thread Ron
At 08:43 PM 8/30/2005, Michael Stone wrote: On Tue, Aug 30, 2005 at 08:41:40PM -0400, Ron wrote: The scary thing is that I've worked on RW production systems that bore a striking resemblance to a TPC benchmark system. As you can imagine, they uniformly belonged to BIG organizations (read:

Re: [PERFORM] 'Real' auto vacuum?

2005-08-30 Thread Mindaugas Riauba
When a row is orphaned it's added to a list of possibly available rows. When a new row is needed the list of possible rows is examined and the first one with a transaction id less then the lowest running transaction id is chosen to be the new row? These rows can be in a heap so it's