Re: [PERFORM] 8.4 Performance improvements: was Re: Proposal of tunable fix for scalability of 8.4

Gregory Stark wrote: A minute ago I said: AFAIK Opensolaris doesn't implement posix_fadvise() so there's no benefit. It would be great to hear if you could catch the ear of the right people to get an implementation committed. Depending on how the i/o scheduler system is written it might not

Re: [PERFORM] Huge Data sets, simple queries

Jeffrey W. Baker wrote: On Tue, 2006-01-31 at 09:00 -0800, Luke Lonergan wrote: Jim, On 1/30/06 12:25 PM, "Jim C. Nasby" <[EMAIL PROTECTED]> wrote: Why divide by 2? A good raid controller should be able to send read requests to both drives out of the mirrored set to fully utilize the

Re: [PERFORM] PostgreSQL and Ultrasparc T1

David Lang wrote: On Tue, 20 Dec 2005, Alan Stange wrote: Jignesh K. Shah wrote: I guess it depends on what you term as your metric for measurement. If it is just one query execution time .. It may not be the best on UltraSPARC T1. But if you have more than 8 complex queries running

Re: [PERFORM] PostgreSQL and Ultrasparc T1

Jignesh K. Shah wrote: I guess it depends on what you term as your metric for measurement. If it is just one query execution time .. It may not be the best on UltraSPARC T1. But if you have more than 8 complex queries running simultaneously, UltraSPARC T1 can do well compared comparatively prov

Re: [PERFORM] postgresql performance tuning

Tom Lane wrote: Alan Stange <[EMAIL PROTECTED]> writes: Vivek Khera wrote: what evidence do you have that you are suffering index bloat? The files for the two indices on a single table used 7.8GB of space before a reindex, and 4.4GB after. That'

Re: [PERFORM] postgresql performance tuning

Vivek Khera wrote: On Dec 6, 2005, at 11:14 AM, Ameet Kini wrote: need for vacuums. However, it'd be great if there was a similar automatic reindex utility, like say, a pg_autoreindex daemon. Are there any plans for this feature? If not, then would cron scripts be the next best what eviden

Re: [PERFORM] Hardware/OS recommendations for large databases (

Luke Lonergan wrote: Why not contribute something - put up proof of your stated 8KB versus 32KB page size improvement. I did observe that 32KB block sizes were a significant win "for our usage patterns". It might be a win for any of the following reasons: 0) The preliminaries: ~300GB dat

Re: [PERFORM] Hardware/OS recommendations for large databases (

Bruce Momjian wrote: Greg Stark wrote: Alan Stange <[EMAIL PROTECTED]> writes: The point your making doesn't match my experience with *any* storage or program I've ever used, including postgresql. Your point suggests that the storage system is idle and that post

Re: [PERFORM] Hardware/OS recommendations for large databases (

Luke, - XFS will probably generate better data rates with larger files. You really need to use the same file size as does postgresql. Why compare the speed to reading a 16G file and the speed to reading a 1G file. They won't be the same. If need be, write some code that does the test or

Re: [PERFORM] Hardware/OS recommendations for large databases (

ant to use 1GB files for dd as well. Luke Lonergan wrote: Alan, On 11/21/05 6:57 AM, "Alan Stange" <[EMAIL PROTECTED]> wrote: $ time dd if=/dev/zero of=/fidb1/bigfile bs=8k count=80 80+0 records in 80+0 records out real0m13.780s user0m0.134s sys 0m1

Re: [PERFORM] Hardware/OS recommendations for large databases (

Luke Lonergan wrote: OK - slower this time: We've seen between 110MB/s and 120MB/s on a wide variety of fast CPU machines with fast I/O subsystems that can sustain 250MB/s+ using dd, but which all are capped at 120MB/s when doing sequential scans with different versions of Postgres. Postgresql

Re: [PERFORM] Hardware/OS recommendations for large databases (

Greg Stark wrote: Alan Stange <[EMAIL PROTECTED]> writes: Iowait is time spent waiting on blocking io calls. As another poster pointed out, you have a two CPU system, and during your scan, as predicted, one CPU went 100% busy on the seq scan. During iowait periods, the CPU can be c

Re: [PERFORM] Hardware/OS recommendations for large databases (

William Yu wrote: Alan Stange wrote: Luke Lonergan wrote: The "aka iowait" is the problem here - iowait is not idle (otherwise it would be in the "idle" column). Iowait is time spent waiting on blocking io calls. As another poster pointed out, you have a two CPU system,

Re: [PERFORM] Hardware/OS recommendations for large databases (

Another data point. We had some down time on our system today to complete some maintenance work. It took the opportunity to rebuild the 700GB file system using XFS instead of Reiser. One iostat output for 30 seconds is avg-cpu: %user %nice%sys %iowait %idle 1.580.00

Re: [PERFORM] Hardware/OS recommendations for large databases (

Luke Lonergan wrote: Alan, On 11/18/05 11:39 AM, "Alan Stange" <[EMAIL PROTECTED]> wrote: Yes and no. The one cpu is clearly idle. The second cpu is 40% busy and 60% idle (aka iowait in the above numbers). The "aka iowait" is the problem here - iowait

Re: [PERFORM] Hardware/OS recommendations for large databases (

Greg Stark wrote: Alan Stange <[EMAIL PROTECTED]> writes: Luke Lonergan wrote: Alan, On 11/18/05 9:31 AM, "Alan Stange" <[EMAIL PROTECTED]> wrote: Here's the output from one iteration of iostat -k 60 while the box is doing a select count(1)

Re: [PERFORM] Hardware/OS recommendations for large databases (

Luke Lonergan wrote: opterons from Sun that we got some time ago. I think the 130MB/s is slow given the hardware, but it's acceptable. I'm not too price sensitive; I care much more about reliability, uptime, etc. I don't know what the system cost. It was part of block of dual Then I kno

Re: [PERFORM] Hardware/OS recommendations for large databases (

Luke Lonergan wrote: Alan, On 11/18/05 9:31 AM, "Alan Stange" <[EMAIL PROTECTED]> wrote: Here's the output from one iteration of iostat -k 60 while the box is doing a select count(1) on a 238GB table. avg-cpu: %user %nice%sys %iowait %idle 0.99

Re: [PERFORM] Hardware/OS recommendations for large databases (

Luke Lonergan wrote: Alan, On 11/18/05 8:13 AM, "Alan Stange" <[EMAIL PROTECTED]> wrote: I told you in my initial post that I was observing numbers in excess of what you claiming, but you seemed to think I didn't know how to measure an IO rate. Prov

Re: [PERFORM] Hardware/OS recommendations for large databases (

Richard Huxton wrote: Dave Cramer wrote: On 18-Nov-05, at 1:07 AM, Luke Lonergan wrote: Postgres + Any x86 CPU from 2.4GHz up to Opteron 280 is CPU bound after 110MB/s of I/O. This is true of Postgres 7.4, 8.0 and 8.1. A $1,000 system with one CPU and two SATA disks in a software RAID0

Re: [PERFORM] Hardware/OS recommendations for large databases (

Luke Lonergan wrote: Alan, On 11/18/05 5:41 AM, "Alan Stange" <[EMAIL PROTECTED]> wrote: That's interesting, as I occasionally see more than 110MB/s of postgresql IO on our system. I'm using a 32KB block size, which has been a huge win in performance for o

Re: [PERFORM] Hardware/OS recommendations for large databases (

David Boreham wrote: Alex Turner wrote: Just pick up a SCSI drive and a consumer ATA drive. Feel their weight. Not sure I get your point. We would want the lighter one, all things being equal, right ? (lower shipping costs, less likely to break when dropped on the floor) Why would the

Re: [PERFORM] Is There Any Way ....

Alex Turner wrote: This is possible with Oracle utilizing the keep pool alter table t_name storage ( buffer_pool keep); If Postgres were to implement it's own caching system, this seems like it would be easily to implement (beyond the initial caching effort). Alex On 10/24/05, Craig A. James

Re: [PERFORM] Performance on SUSE w/ reiserfs

Alex Turner wrote: Perhaps this is true for 1.5 on x86-32 (I've only used it on x86-64) but I was more thinking 1.4 which many folks are still using. The 1.4.x JVM's will also work just fine with much more than 1GB of memory. Perhaps you'd like to try again? -- Alan On

Re: [PERFORM] Performance on SUSE w/ reiserfs

Alex Turner wrote: Realise also that unless you are running the 1.5 x86-64 build, java will not use more than 1Gig, and if the app server requests more than 1gig, Java will die (I've been there) with an out of memory error, even though there is plenty of free mem available. This can easily be

Re: [PERFORM] Performance considerations for very heavy INSERT traffic

Brandon Black wrote: On 9/12/05, *PFC* <[EMAIL PROTECTED] > wrote: - benchmarking something else than ext3 (xfs ? reiser3 ?) We've had bad experiences under extreme and/or strange workloads with XFS here in general, although thi

Re: [PERFORM] difference in plan between 8.0 and 8.1?

Tom Lane wrote: Alan Stange <[EMAIL PROTECTED]> writes: Unique (cost=2717137.08..2771407.21 rows=10854026 width=8) -> Sort (cost=2717137.08..2744272.14 rows=10854026 width=8) Sort Key: timeseriesid -> Bitmap Heap Scan on tbltimeseries (cost=48714.09

[PERFORM] difference in plan between 8.0 and 8.1?

Hello all, I was hoping someone could explain the plan for a statement. We have a table with a column of longs being used as an index. The query plan in 8.0 was like this: # explain select distinct timeseriesid from tbltimeseries where timeseriesid > 0 order by timeseriesid; SET

Re: [PERFORM] Caching by Postgres

[EMAIL PROTECTED] wrote: On Wed, Aug 24, 2005 at 02:47:09PM -0400, Alan Stange wrote: At least on Sparc processors, v8 and newer, any double precision math (including longs) is performed with a single instruction, just like for a 32 bit datum. Loads and stores of 8 byte datums are also

Re: [PERFORM] Caching by Postgres

[EMAIL PROTECTED] wrote: So then we move on to what 64-bit is really useful for. Obviously, there is the arithmetic. If you were previously doing 64-bit arithmetic through software, you will notice an immediate speed improvement when doing it through hardware instead. If you have a program that i

Re: [PERFORM] Read/Write block sizes

Josh Berkus wrote: Steve, I would assume that dbt2 with STP helps minimize the amount of hours someone has to invest to determine performance gains with configurable options? Actually, these I/O operation issues show up mainly with DW workloads, so the STP isn't much use there. If

[PERFORM] unused item pointers?

Hello all, what are unused item pointers and how do I get rid of them? We have a fairly large table which is vacuumed daily and reindexed every weekend. NFO: vacuuming "public.tbltimeseries" INFO: index "idx_timeseries" now contains 26165807 row versions in 151713 pages DETAIL: 8610108 inde

[PERFORM] limit number of concurrent callers to a stored proc?

Hello all, is there a simple way to limit the number of concurrent callers to a stored proc? The problem we have is about 50 clients come and perform the same operation at nearly the same time. Typically, this query takes a few seconds to run, but in the case of this thundering herd the que

[PERFORM] BG writer question?

Hello all, I just was running strace in the writer process and I noticed this pattern: select(0, NULL, NULL, NULL, {0, 20}) = 0 (Timeout) getppid() = 4240 time(NULL) = 1123773324 mmap2(NULL, 528384, PROT_READ|PROT_WRITE, MAP_PRIVATE|

Re: [PERFORM] How to improve db performance with $7K?

Alex Turner wrote: [snip] Adding drives will not let you get lower response times than the average seek time on your drives*. But it will let you reach that response time more often. [snip] I believe your assertion is fundamentaly flawed. Adding more drives will not let you reach that resp

Re: [PERFORM] How to improve db performance with $7K?

PFC wrote: My argument is that a sufficiently smart kernel scheduler *should* yield performance results that are reasonably close to what you can get with that feature. Perhaps not quite as good, but reasonably close. It shouldn't be an orders-of-magnitude type difference. And a controller

Re: [PERFORM] PostgreSQL on Solaris 8 and ufs

Brandon Metcalf wrote: We've recently moved our pgsql installation and DBs to a Solaris 8 machine with striped and mirrored ufs filesystem that houses the DB data. We are now seeing terrible performance and the bottleneck is no doubt disk I/O. We've tried modifying a tunables related to ufs, but i

Re: [PERFORM] multi billion row tables: possible or insane?

Ramon Bastiaans wrote: I am doing research for a project of mine where I need to store several billion values for a monitoring and historical tracking system for a big computer system. My currect estimate is that I have to store (somehow) around 1 billion values each month (possibly more). I wa

Re: [PERFORM] multi billion row tables: possible or insane?

Isn't that 385 rows/second. Presumably one can insert more than one row in a transaction? -- Alan Vig, Sandor (G/FI-2) wrote: 385 transaction/sec? fsync = false risky but fast. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of John Arbash Meinel Sent: Tues

Re: [PERFORM] Swapping on Solaris

Kevin Schroeder wrote: I may be asking the question the wrong way, but when I start up PostgreSQL swap is what gets used the most of. I've got 1282MB free RAM right now and and 515MB swap in use. Granted, swap file usage probably wouldn't be zero, but I would guess that it should be a lot low

Re: [PERFORM] Swapping on Solaris

Kevin Schroeder wrote: I take that back. There actually is some paging going on. I ran sar -g 5 10 and when a request was made (totally about 10 DB queries) my pgout/s jumped to 5.8 and my ppgout/s jumped to 121.8. pgfree/s also jumped to 121.80. I'm fairly sure that the pi and po numbers inc

Re: [PERFORM] Swapping on Solaris

Kevin Schroeder wrote: I suspect that the memory is being used to cache files as well since the email boxes are using unix mailboxes, for the time being. With people checking their email sometimes once per minute I can see why Solaris would want to cache those files. Perhaps my question would

Re: [PERFORM] Swapping on Solaris

Mark Kirkwood wrote: Kevin Schroeder wrote: Ignoring the fact that the sort and vacuum numbers are really high, this is what Solaris shows me when running top: Memory: 2048M real, 1376M free, 491M swap in use, 2955M swap free Maybe check the swap usage with 'swap -l' which reports reliably if an

Re: [PERFORM] First set of OSDL Shared Mem scalability results, some

Tom Lane wrote: Kevin Brown <[EMAIL PROTECTED]> writes: Hmm...something just occurred to me about this. Would a hybrid approach be possible? That is, use mmap() to handle reads, and use write() to handle writes? Nope. Have you read the specs regarding mmap-vs-stdio synchronization? B

Re: [PERFORM] Excessive context switching on SMP Xeons

Bill Montgomery wrote: Alan Stange wrote: Here's a few numbers from the Opteron 250. If I get some time I'll post a more comprehensive comparison including some other systems. The system is a Sun v20z. Dual Opteron 250, 2.4Ghz, Linux 2.6, 8 GB memory. I did a compile and install

Re: [PERFORM] Excessive context switching on SMP Xeons

Here's a few numbers from the Opteron 250. If I get some time I'll post a more comprehensive comparison including some other systems. The system is a Sun v20z. Dual Opteron 250, 2.4Ghz, Linux 2.6, 8 GB memory. I did a compile and install of pg 8.0 beta 3. I created a data base on a tmpfs f

Re: [PERFORM] Excessive context switching on SMP Xeons

Greg Stark wrote: Alan Stange <[EMAIL PROTECTED]> writes: A few quick random observations on the Xeon v. Opteron comparison: - running a dual Xeon with hyperthreading turned on really isn't the same as having a quad cpu system. I haven't seen postgresql specific benchmarks, but

Re: [PERFORM] Excessive context switching on SMP Xeons

A few quick random observations on the Xeon v. Opteron comparison: - running a dual Xeon with hyperthreading turned on really isn't the same as having a quad cpu system. I haven't seen postgresql specific benchmarks, but the general case has been that HT is a benefit in a few particular work

Re: [PERFORM] Anyone familiar with Apple Xserve RAID

Doug McNaught wrote: Kevin Barnard <[EMAIL PROTECTED]> writes: Actually you are both are right and wrong. The XRaid uses FibreChannel to communicate to the host machine(s). The Raid controller is a FibreChannel controller. After that there is a FibreChannel to UltraATA conversion for

Re: [PERFORM] linux distro for better pg performance

Joseph Shraibman wrote: J. Andrew Rogers wrote: Do these features make a difference? Far more than you would imagine. On one postgres server I just upgraded, we went from a 3Ware 8x7200-RPM RAID-10 configuration to an LSI 320-2 SCSI 3x10k RAID-5, with 256M Is raid 5 much faster than raid 10? On

[PERFORM] vacuum performance

Hello all, I have a question/observation about vacuum performance. I'm running Solaris 9, pg 7.4.1. The process in questions is doing a vacuum: bash-2.05$ /usr/ucb/ps auxww | grep 4885 fiasco4885 19.1 3.7605896592920 ?O 19:29:44 91:38 postgres: fiasco fiasco [local] VACUUM I do a

Re: [PERFORM] Insert Times

PC Drew wrote: I tested this out and saw no improvement: I'd still suspect some class loading issues and HotSpot compilation issues are polluting your numbers.Try using a PreparedStatement to another table first in order to make sure that classes bytecode has been loaded. There are som