date:20060807

Hi, Reimer,

carlosreimer wrote:

 There is some performance problems with the server and I discovered with
 vmstat tool that there is some process writing a lot of information in
 the disk subsystem.
[..]
 I could I discover who is sending so many data to the disks?

It could be something triggered by your crontab (updatedb comes in my
mind, or texpire from leafnode etc.).

Another idea would be that you have statement logging on, or something
else that produces lots of kernel or syslog messages[1], and your
syslogd is configured to sync() after every line...

HTH,
Markus

[1] We once had such a problem because an ill-compiled kernel having USB
verbose logging on...
-- 
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [PERFORM] Performance with 2 AMD/Opteron 2.6Ghz and 8gig

Hi, Charles,

Charles Sprickman wrote:

 I've also got a 1U with a 9500SX-4 and 4 drives.  I like how the 3Ware
 card scales there - started with 2 drives and got drive speed
 mirroring. Added two more and most of the bonnie numbers doubled.  This
 is not what I'm used to with the Adaptec SCSI junk.

Well, for sequential reading, you should be able to get double drive
speed on a 2-disk mirror with a good controller, as it can balance the
reads among the drives.

Markus
-- 
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [PERFORM] PostgreSQL scalability on Sun UltraSparc T1

Hi, Arjen,

Arjen van der Meijden wrote:

 It was the 8core version with 16GB memory... but actually that's just
 overkill, the active portions of the database easily fits in 8GB and a
 test on another machine with just 2GB didn't even show that much
 improvements when going to 7GB (6x1G, 2x 512M), it was mostly in the
 range of 10% improvement or less.

I'd be interested in the commit_siblings and commit_delay settings,
tuning them could give a high increase on throughput for highly
concurrent insert/update workloads, at the cost of latency (and thus
worse results for low concurrency situations).

Different fsync method settings can also make a difference (I presume
that syncing was enabled).

HTH,
Markus


-- 
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [PERFORM] sub select performance due to seq scans

Hi, Scott and Hale,

Scott Marlowe wrote:
 Make sure analyze has been run and that the statistics are fairly
 accurate.

It might also help to increase the statistics_target on the column in
question.

HTH,
Markus

-- 
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] PostgreSQL scalability on Sun UltraSparc T1

2006-08-07 Thread Arjen van der Meijden


Hi Markus,

As said, our environment really was a read-mostly one. So we didn't do 
much inserts/updates and thus spent no time tuning those values and left 
them as default settings.


Best regards,

Arjen

Markus Schaber wrote:

Hi, Arjen,

Arjen van der Meijden wrote:


It was the 8core version with 16GB memory... but actually that's just
overkill, the active portions of the database easily fits in 8GB and a
test on another machine with just 2GB didn't even show that much
improvements when going to 7GB (6x1G, 2x 512M), it was mostly in the
range of 10% improvement or less.


I'd be interested in the commit_siblings and commit_delay settings,
tuning them could give a high increase on throughput for highly
concurrent insert/update workloads, at the cost of latency (and thus
worse results for low concurrency situations).

Different fsync method settings can also make a difference (I presume
that syncing was enabled).

HTH,
Markus




---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [PERFORM] 7.3.2 pg_restore very slow

Hi Richard,Thank you very muchfor the suggestions. As I said, we are stuck with 7.3.2 version for now. We have a Upgrade Project in place, but this backup is something we have to do immediately (we do not have enough time to test our application with 7.3.15 :( )The checkpoint segments occur every 1.15 minutes with the default setting. I tried tuning some parameters in the conf file, which took 4.5 hours for the restore.sort_mem = 40960 shared_buffers = 3000 #checkpoint_segments = 3 (default) #fsync = true --I will disable this and tryWe can afford to have a downtime of only 1 to 1.5 hours. I am going to increase the shared_buffers, sort_mem and disable fysnc as suggested by you, andtry the restore process again. I
would appreciate any other suggestions/advice in this regard.Thanks, SaranyaRichard Huxton dev@archonet.com wrote: Saranya Sivakumar wrote: Hi All, I am trying to back up a full copy of one of our databases (14G) and restore it on another server. Both databases run 7.3.2 version. Though the restore completed successfully, it took 9 hours for the process to complete. The destination server runs Fedora Core 3 with 512 MB RAM and has 1 processor. I have also deferred referential intergrity checks during the restore. I tried to tune some parameters in the config file, but it still takes 9 hours.Firstly, you should upgrade to the most recent version of 7.3.x (7.3.15) - that's a *lot*
of bug-fixes you are missingThen, I would temporarily disable fsync and increase sort_mem and checkpoint_segments. What you're trying to do is make a single process run as fast as possible, so allow it to grab more resources than you normally would.-- Richard HuxtonArchonet Ltd

See the all-new, redesigned Yahoo.com. Check it out.

Re: [PERFORM] High Context-Switches on Linux 8.1.4 Server

2006-08-07 Thread Donald C. Sumbry ][


Tom Lane wrote:
We're seeing an average of 30,000 context-switches a sec.  This problem 
was much worse w/8.0 and got bearable with 8.1 but slowly resurfaced.  


Is this from LWLock or spinlock contention?  strace'ing a few backends
could tell the difference: look to see how many select(0,...) you see
compared to semop()s.  Also, how many of these compared to real work
(such as read/write calls)?


Over a 20 second interval, I've got about 85 select()s and 6,230 
semop()s. 2604 read()s vs 16 write()s.



Do you have any long-running transactions, and if so does shutting
them down help?  There's been some discussion about thrashing of the
pg_subtrans buffers being a problem, and that's mainly a function of
the age of the oldest open transaction.


Not long-running.  We do have a badly behaving legacy app that is 
leaving some backends idle in transaction  They're gone pretty quickly 
so I can't kill them fast enough, but running a pg_stat_activity  will 
always show at least a handful.  Could this be contributing?


Based on the number of semop's we're getting it does look like 
shared_memory may be getting thrased - any suggestions?  We did try 
lowering shared_memory usage in half the previous day, but that did 
little to help (it didn't make performance any worse and we still saw 
the high context-switches, but it didn't make it any better either).


--
Sumbry][

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [PERFORM] 7.3.2 pg_restore very slow

Hi All,I tried to set shared_buffers= 1, turned off fsyncand reload the config file. But I got the following error:IpcMemoryCreate: shmget(key=5432001, size=85450752, 03600) failed: Invalid argument This error usually means that PostgreSQL's request for a shared memorysegment exceeded your kernel's SHMMAX parameter. You can eitherreduce the request size or reconfigure the kernel with larger SHMMAX.To reduce the request size (currently 85450752 bytes), reducePostgreSQL's shared_buffers parameter (currently 1) and/orits max_connections parameter (currently 128). If the request size is already small, it's possible that it is less thanyour kernel's SHMMIN parameter, in which case raising the request size orreconfiguring SHMMIN is called for. The total RAM available on this machine is 512MB.
I am not sure how to set these parameters SHMMAX and SHMMIN. Any help/advice would be greatly appreciated.Thanks, Saranya Richard Huxton dev@archonet.com wrote: Saranya Sivakumar wrote: Hi All, I am trying to back up a full copy of one of our databases (14G) and restore it on another server. Both databases run 7.3.2 version. Though the restore completed successfully, it took 9 hours for the process to complete. The destination server runs Fedora Core 3 with 512 MB RAM and has 1 processor. I have also deferred referential intergrity checks during the restore. I tried to tune some parameters in the config file, but it still takes 9 hours.Firstly, you should upgrade to the
most recent version of 7.3.x (7.3.15) - that's a *lot* of bug-fixes you are missingThen, I would temporarily disable fsync and increase sort_mem and checkpoint_segments. What you're trying to do is make a single process run as fast as possible, so allow it to grab more resources than you normally would.-- Richard HuxtonArchonet Ltd
Do you Yahoo!? Everyone is raving about the all-new Yahoo! Mail Beta.

Re: [NOVICE] [PERFORM] 7.3.2 pg_restore very slow

2006-08-07 Thread Richard Broersma Jr

   IpcMemoryCreate: shmget(key=5432001, size=85450752, 03600) failed: Invalid 
 argument
   This error usually means that PostgreSQL's request for a shared memory
 segment exceeded your kernel's SHMMAX parameter.  You can either
 reduce the request size or reconfigure the kernel with larger SHMMAX.
 To reduce the request size (currently 85450752 bytes), reduce
 PostgreSQL's shared_buffers parameter (currently 1) and/or
 its max_connections parameter (currently 128).
   If the request size is already small, it's possible that it is less than
 your kernel's SHMMIN parameter, in which case raising the request size or
 reconfiguring SHMMIN is called for.

if you cat /proc/sys/kernel/shmmax
it will tell you what it is set to. It needs to be at least 85450752. The 
size that Postgresql
is trying to grab.

also shmall may need to be adjusted also.

   The total RAM available on this machine is 512MB. 

   I am not sure how to set these parameters SHMMAX and SHMMIN. 
   Any help/advice would be greatly appreciated.

http://www.postgresql.org/docs/8.1/interactive/kernel-resources.html
This will help you to set the kernel parameters.

Regards,

Richard Broersma Jr.


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] XFS filessystem for Datawarehousing

2006-08-07 Thread Alvaro Herrera

Jim C. Nasby wrote:
 On Tue, Aug 01, 2006 at 08:42:23PM -0400, Alvaro Herrera wrote:

  Most likely ext3 was used on the default configuration, which logs data
  operations as well as metadata, which is what XFS logs.  I don't think
  I've seen any credible comparison between XFS and ext3 with the
  metadata-only journal option.
  
  On the other hand I don't think it makes sense to journal data on a
  PostgreSQL environment.  Metadata is enough, given that we log data on
  WAL anyway.
 
 Actually, according to http://en.wikipedia.org/wiki/Ext3 the default
 journalling option for ext3 isn't to journal the data (which is actually
 data=journal), but to wait until the data is written before considering
 the metadata to be committed (data=ordered).

Well, we don't need the data to be written before considering metadata
committed.  data=writeback is enough for partitions to be dedicated to
PGDATA.  Not sure what other FSs do on this front but the ext3 default
leans towards safe rather than speedy.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [NOVICE] [PERFORM] 7.3.2 pg_restore very slow

Hi Richard,Thank you very much for the information. The SHMMAX was set to 33554432, and that's why it failed to start the postmaster. Thanks for the link to the kernel resources article. I guess changing these parameters would require recompiling the kernel. Is there any work around without changing these parameters to make maximum use of RAM?We got a new server now with 2GB RAM, but it also has the same value forSHMMAX.And I am trying the restore with the following conf sort_mem = 40960 (changed from 1024) shared_buffers = 3000 (changed from 64)
max_connections = 128 (changed from 32)Thanks, SaranyaRichard Broersma Jr [EMAIL PROTECTED] wrote: IpcMemoryCreate: shmget(key=5432001, size=85450752, 03600) failed: Invalid argument This error usually means that PostgreSQL's request for a shared memory segment exceeded your kernel's SHMMAX parameter. You can either reduce the request size or reconfigure the kernel with larger SHMMAX. To reduce the request size (currently 85450752 bytes), reduce PostgreSQL's shared_buffers parameter (currently 1) and/or its max_connections parameter (currently 128). If the request size is already small, it's possible that it is less than your kernel's SHMMIN
parameter, in which case raising the request size or reconfiguring SHMMIN is called for.if you cat /proc/sys/kernel/shmmaxit will tell you what it is set to. It needs to be at least "85450752". The size that Postgresqlis trying to grab.also shmall may need to be adjusted also. The total RAM available on this machine is 512MB. I am not sure how to set these parameters SHMMAX and SHMMIN. Any help/advice would be greatly appreciated.http://www.postgresql.org/docs/8.1/interactive/kernel-resources.htmlThis will help you to set the kernel parameters.Regards,Richard Broersma Jr.
Groups are talking. Were listening. Check out the handy changes to Yahoo! Groups.

Re: [PERFORM] Slow transfer speeds

2006-08-07 Thread Scott Marlowe

On Mon, 2006-08-07 at 12:26, hansell baran wrote:
 Hi. I'm new at using PostgreSQL. I have found posts related to this
 one but there is not a definite answer or solution. Here it goes.
 Where I work, all databases were built with MS Access. The Access
 files are hosted by computers with Windows 2000 and Windows XP. A new
 server is on its way and only Open Source Software is going to be
 installed. The OS is going to be SUSE Linux 10.1 and we are making
 comparisons between MySQL, PostgreSQL and MS Access. We installed
 MySQL and PostgreSQL on both SUSE and Windows XP (MySQL  PostgreSQL
 DO NOT run at the same time)(There is one HDD for Windows and one for
 Linux)
 The Test Server in which we install the DBMS has the following
 characteristics:
 
 CPU speed = 1.3 GHz
 RAM = 512 MB
 HDD = 40 GB

Just FYI, that's not only not much in terms of server, it's not even
much in terms of a workstation.  My laptop is about on par with that.

Just sayin.

OK, just so you know, you're comparing apples and oranges.  A client
side application like access has little or none of the overhead that a
real database server has.

The advantage PostgreSQL has is that many people can read AND write to
the same data store simultaneously and the database server will make
sure that the underlying data in the files never gets corrupted. 
Further, with proper constraints in place, it can make sure that the
data stays coherent (i.e. that data dependencies are honored.)

As you can imagine, there's gonna be some overhead there.  And it's
wholly unfair to compare a databases ability to stream out data in a
single read to access.  It is the worst case scenario.

Try having 30 employees connect to the SAME access database and start
updating lots and lots of records.  Have someone read out the data while
that's going on.  Repeat on PostgreSQL.

If you're mostly going to be reading data, then maybe some intermediate
system is needed, something to harvest the data into some flat files.

But if your users need to read out 500,000 rows, change a few, and write
the whole thing back, your business process is likely not currently
suited to a database and needs to be rethought.

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [PERFORM] High Context-Switches on Linux 8.1.4 Server

2006-08-07 Thread Tom Lane

 Is this from LWLock or spinlock contention?

 Over a 20 second interval, I've got about 85 select()s and 6,230 
 semop()s. 2604 read()s vs 16 write()s.

OK, so mostly LWLocks then.

 Do you have any long-running transactions,

 Not long-running.  We do have a badly behaving legacy app that is 
 leaving some backends idle in transaction  They're gone pretty quickly 
 so I can't kill them fast enough, but running a pg_stat_activity  will 
 always show at least a handful.  Could this be contributing?

Sorry, I was unclear: it's the age of your oldest transaction that
counts (measured by how many xacts started since it), not how many
cycles it's consumed or not.

With the 8.1 code it's possible for performance to degrade pretty badly
once the age of your oldest transaction exceeds 16K transactions.  You
were not specific enough about the behavior of this legacy app to let
me guess where you are on that scale ...

 Based on the number of semop's we're getting it does look like 
 shared_memory may be getting thrased - any suggestions?  We did try 
 lowering shared_memory usage in half the previous day,

Unlikely to help --- if it is the pg_subtrans problem, the number of
buffers involved is set by a compile-time constant.

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [NOVICE] [PERFORM] 7.3.2 pg_restore very slow

2006-08-07 Thread Richard Broersma Jr

   Thank you very much for the information. The SHMMAX was set to 33554432, 
 and that's why it
 failed to start the postmaster. Thanks for the link to the kernel resources 
 article. I guess
 changing these parameters would require recompiling the kernel. 

   Is there any work around without changing these parameters to make maximum 
 use of RAM?

   We got a new server now with 2GB RAM, but it also has the same value for 
 SHMMAX.

   And I am trying the restore with the following conf
   sort_mem = 40960  (changed from 1024)
   shared_buffers = 3000 (changed from 64)
 max_connections = 128 (changed from 32)

This is one of the best links that I can give you in addition to the Postgresql 
Kernel resource
link.
http://www.powerpostgresql.com/PerfList

I am pretty much a beginner at resource tuning also.  In fact, after googling 
for sources that
describe how to tune kernel parameters, the postgresql documents remains the 
best documents I've
found so far.

I would be interested if anyone else on the list knows of any resources or 
books that have an in
depth discussion on methods/strategies to tune kernel parameters to maximized 
usage of system
resources and at the same time allow for harmonious sharing between various 
programs/services.

Regards,

Richard Broersma Jr.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [PERFORM] High Context-Switches on Linux 8.1.4 Server

2006-08-07 Thread Donald C. Sumbry ][


Tom Lane wrote:

Sorry, I was unclear: it's the age of your oldest transaction that
counts (measured by how many xacts started since it), not how many
cycles it's consumed or not.



With the 8.1 code it's possible for performance to degrade pretty badly
once the age of your oldest transaction exceeds 16K transactions.  You
were not specific enough about the behavior of this legacy app to let
me guess where you are on that scale ...


Understood.  This legacy apps wraps every single transaction (even read 
only ones) inside of BEGIN; END; blocks.  We do about 90+ percent reads 
to our database, and at 300+ queries a second that could quickly add up.


Does this sound like we should investigate this area more?

Based on the number of semop's we're getting it does look like 
shared_memory may be getting thrased - any suggestions?  We did try 
lowering shared_memory usage in half the previous day,


Unlikely to help --- if it is the pg_subtrans problem, the number of
buffers involved is set by a compile-time constant.


Interesting.  One other thing to note, this application in particular 
accounts for only 4 percent of total queries and if we disable the 
application the database runs like a champ.  The only other huge 
variable I can think of is this app's gratuitous use of cursors.


I haven't read too much about Postgres performance especially when 
dealing with cursors, but could this be a variable?  We are considering 
modifying the app and removing all use of cursors and wonder if we're 
wasting our time or not.


Thanks for the help.

--
Sumbry][

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [PERFORM] Performance with 2 AMD/Opteron 2.6Ghz and 8gig

2006-08-07 Thread Alex Turner

Although I for one have yet to see a controller that actualy does this (I believe software RAID on linux doesn't either).Alex.On 8/7/06, Markus Schaber
 [EMAIL PROTECTED] wrote:Hi, Charles,
Charles Sprickman wrote: I've also got a 1U with a 9500SX-4 and 4 drives.I like how the 3Ware card scales there - started with 2 drives and got drive speed mirroring. Added two more and most of the bonnie numbers doubled.This
 is not what I'm used to with the Adaptec SCSI junk.Well, for sequential reading, you should be able to get double drivespeed on a 2-disk mirror with a good controller, as it can balance thereads among the drives.
Markus--Markus Schaber | Logical TrackingTracing International AGDipl. Inf. | Software Development GISFight against software patents in EU! www.ffii.org
 www.nosoftwarepatents.org---(end of broadcast)---TIP 9: In versions below 8.0, the planner will ignore your desire to
 choose an index scan if your joining column's datatypes do not match

Re: [NOVICE] [PERFORM] 7.3.2 pg_restore very slow

Hi All,Thanks Richard for the additional link. The information is very useful.The restore completed successfully in 2.5 hours in the new 2GB box, with the same configuration parameters. I think if I can tweak the parameters a little more, I should be able to get it down to the 1 hr down time that we can afford.Thanks again for allthe help.Sincerely, Saranya Richard Broersma Jr [EMAIL PROTECTED] wrote: Thank you very much for the information. The SHMMAX was set to 33554432, and that's why it failed to start the postmaster. Thanks for the link to the kernel resources article. I guess changing these parameters would require recompiling the kernel.
Is there any work around without changing these parameters to make maximum use of RAM? We got a new server now with 2GB RAM, but it also has the same value for SHMMAX. And I am trying the restore with the following conf sort_mem = 40960 (changed from 1024) shared_buffers = 3000 (changed from 64) max_connections = 128 (changed from 32)This is one of the best links that I can give you in addition to the Postgresql Kernel resourcelink.http://www.powerpostgresql.com/PerfListI am pretty much a beginner at resource tuning also. In fact, after googling for sources thatdescribe how to tune kernel parameters, the postgresql documents remains the best documents I'vefound so far.I would be interested if anyone else on the list knows of any resources or books that have an indepth discussion on methods/strategies to tune kernel parameters to maximized usage of
systemresources and at the same time allow for harmonious sharing between various programs/services.Regards,Richard Broersma Jr.

Want to be your own boss? Learn how on Yahoo! Small Business.

Re: [PERFORM] Performance with 2 AMD/Opteron 2.6Ghz and 8gig

2006-08-07 Thread Steinar H. Gunderson

On Mon, Aug 07, 2006 at 04:02:52PM -0400, Alex Turner wrote:
 Although I for one have yet to see a controller that actualy does this (I
 believe software RAID on linux doesn't either).

Linux' software RAID does. See earlier threads for demonstrations.

/* Steinar */
-- 
Homepage: http://www.sesse.net/

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[PERFORM] Hardware upgraded but performance still ain't good enough

2006-08-07 Thread Alvaro Nunes Melo


Hi,

First of all I must tell that my reality in a southern brazilian city is 
way different than what we read in the list. I was lookig for ways to 
find the HW bottleneck and saw a configuration like:


we recently upgraded our dual Xeon Dell to a brand new Sun v40z with 4 
opterons, 16GB of memory and MegaRAID with enough disks. OS is Debian 
Sarge amd64, PostgreSQL is 8.0.3. on 
(http://archives.postgresql.org/pgsql-performance/2005-07/msg00431.php)


Our old server was a very modest Dell Xeon 2.8 (512 Kb Cache), with 1 GB 
RAM and one SCSI disc. This server runs PostgreSQL (8.1.4), Apache (PHP) 
and other minor services. We managed to get a test machine, a HP Xeon 
3.2 (2 MB cache), also with 1 GB RAM but 4 SCSI discs (in one sigle 
array controller). They're organized in the following way:


disk 0: Linux Root
disk 1: Database Cluster
disk 2: pg_xlog
disk 3: a dir the suffers constant read/write operations

The database size stands around 10 GB. The new server has a better 
performance than the old one, but sometimes it still stucks. We tried to 
use a HP proprietary tool to monitor the server, and find out what is 
the bottleneck, but it's been difficult to install it on Debian. The 
tool is only certified for SuSe and RedHat. So we tried to use some 
Linux tools to see what's going on, like vmstat and iostat. Are this 
tools (vm and iostat) enough? Should we use something else? Is there any 
specifical material about finding bottlenecks in Linux/PostgreSQL 
machines? Is our disks design proper?


I really apologize for my lack of knowledge in this area, and for the 
excessive number of questions in a single e-mail.


Best regards,
Alvaro

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

[PERFORM] Postgresql Performance on an HP DL385 and SmartArray 642

I am do some consulting for an animal hospital in the Boston, MA area.
They wanted a new server to run their database on. The client wants
everything from one vendor, they wanted Dell initially, I'd advised
against it. I recommended a dual Opteron system from either Sun or HP.
They settled on a DL385 8GB of RAM with two disc U320 SCSI and a 6-disc
U320 SCSI array. I recommended they add a RAID adapter with at 128MB and
battery backup, they added a HP SmartArray 642 to connect to the drive
array in addition to the SmartArray 6i which came with the server.

Has anyone worked with server before. I've read the SmartArray 6i is a
poor performer, I wonder if the SmartArray 642 adapter would have the
same fate? 

The database data is on the drive array(RAID10) and the pg_xlog is on
the internal RAID1 on the 6i controller. The results have been poor.

My guess is the controllers are garbage.

Thanks for any advice.

Steve Poe











---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [PERFORM] Postgresql Performance on an HP DL385 and

2006-08-07 Thread Luke Lonergan

Steve,

On 8/5/06 4:10 PM, Steve Poe [EMAIL PROTECTED] wrote:

 I am do some consulting for an animal hospital in the Boston, MA area.
 They wanted a new server to run their database on. The client wants
 everything from one vendor, they wanted Dell initially, I'd advised
 against it. I recommended a dual Opteron system from either Sun or HP.
 They settled on a DL385 8GB of RAM with two disc U320 SCSI and a 6-disc
 U320 SCSI array. I recommended they add a RAID adapter with at 128MB and
 battery backup, they added a HP SmartArray 642 to connect to the drive
 array in addition to the SmartArray 6i which came with the server.
 
 Has anyone worked with server before. I've read the SmartArray 6i is a
 poor performer, I wonder if the SmartArray 642 adapter would have the
 same fate? 
 
 The database data is on the drive array(RAID10) and the pg_xlog is on
 the internal RAID1 on the 6i controller. The results have been poor.
 
 My guess is the controllers are garbage.

Can you run bonnie++ version 1.03a on the machine and report the results
here?

It could be OK if you have the latest Linux driver for cciss, someone has
reported good results to this list with the latest, bleeding edge version of
Linux (2.6.17).

- Luke



---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [PERFORM] Postgresql Performance on an HP DL385 and

Luke,

I'll do that then post the results. I ran zcav on it (default
settlings) on the disc array formatted XFS and its peak MB/s was around
85-90. I am using kernel 2.6.17.7. mounting the disc array with
noatime, nodiratime.
Thanks for your feedback.

Steve
On 8/7/06, Luke Lonergan [EMAIL PROTECTED] wrote:
Steve,On 8/5/06 4:10 PM, Steve Poe [EMAIL PROTECTED] wrote: I am do some consulting for an animal hospital in the Boston, MA area. They wanted a new server to run their database on. The client wants
everything from one vendor, they wanted Dell initially, I'd advised against it. I recommended a dual Opteron system from either Sun or HP. They settled on a DL385 8GB of RAM with two disc U320 SCSI and a 6-disc
U320 SCSI array. I recommended they add a RAID adapter with at 128MB and battery backup, they added a HP SmartArray 642 to connect to the drive array in addition to the SmartArray 6i which came with the server.
Has anyone worked with server before. I've read the SmartArray 6i is a poor performer, I wonder if the SmartArray 642 adapter would have the same fate? The database data is on the drive array(RAID10) and the pg_xlog is on
the internal RAID1 on the 6i controller. The results have been poor. My guess is the controllers are garbage.Can you run bonnie++ version 1.03a on the machine and report the resultshere?
It could be OK if you have the latest Linux driver for cciss, someone hasreported good results to this list with the latest, bleeding edge version ofLinux (2.6.17).- Luke---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Re: [PERFORM] Postgresql Performance on an HP DL385 and

2006-08-07 Thread Joshua D. Drake




The database data is on the drive array(RAID10) and the pg_xlog is on
the internal RAID1 on the 6i controller. The results have been poor.


I have heard that the 6i was actually decent but to avoid the 5i.

Joshua D. Drake




My guess is the controllers are garbage.


Can you run bonnie++ version 1.03a on the machine and report the results
here?

It could be OK if you have the latest Linux driver for cciss, someone has
reported good results to this list with the latest, bleeding edge version of
Linux (2.6.17).

- Luke



---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match




--

   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
   Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/



---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [PERFORM] Postgresql Performance on an HP DL385 and

There is 64MB on the 6i and 192MB on the 642 controller. I wish the
controllers had a wrieback enable option like the LSI MegaRAID
adapters have. I have tried splitting the cache accelerator 25/75 75/25
0/100 100/0 but the results really did not improve.

SteveOn 8/7/06, Joshua D. Drake [EMAIL PROTECTED] wrote:
 The database data is on the drive array(RAID10) and the pg_xlog is on the internal RAID1 on the 6i controller. The results have been poor.I have heard that the 6i was actually decent but to avoid the 5i.
Joshua D. Drake My guess is the controllers are garbage. Can you run bonnie++ version 1.03a on the machine and report the results here? It could be OK if you have the latest Linux driver for cciss, someone has
 reported good results to this list with the latest, bleeding edge version of Linux (2.6.17). - Luke ---(end of broadcast)---
 TIP 9: In versions below 8.0, the planner will ignore your desire tochoose an index scan if your joining column's datatypes do notmatch--=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240Providing the most comprehensivePostgreSQL solutions since 1997http://www.commandprompt.com/

Re: [PERFORM] Postgresql Performance on an HP DL385 and