Re: [PERFORM] VACUUM FULL does not works.......

We have a view in our database. CREATE view public.hogs AS SELECT pg_stat_activity.procpid, pg_stat_activity.usename, pg_stat_activity.current_query FROM ONLY pg_stat_activity; Select current_query from public.hogs helps us to spot errant queries at times. regds mallah. On 12/7/06, asif

Re: [PERFORM] File Systems Compared

On Wed, 6 Dec 2006, Alexander Staubo wrote: Care to post these numbers *without* word wrapping? Brian's message was sent with format=flowed and therefore it's easy to re-assemble into original form if your software understands that. I just checked with two e-mail clients (Thunderbird and Pi

Re: [PERFORM] Configuration settings for 32GB RAM server

On Mon, Dec 04, 2006 at 09:42:57AM -0800, Jeff Davis wrote: > > fsm_pages = 200,000 ??? Based this on some statistics about the number > > of pages freed from a vacuum on older server. Not sure if its fair > > to calculate this based on vacuum stats of 7.3.4 server? > > > > Might as well make i

Re: [PERFORM] 8.2rc1 (much) slower than 8.2dev?

On Mon, Dec 04, 2006 at 05:41:14PM +0100, Arjen van der Meijden wrote: > Since I'd rather not send the entire list of queries to the entire > world, is it OK to send both explain analyze-files to you off list? Can you post them on the web somewhere so everyone can look at them? Also, are you loo

Re: [PERFORM] Areca 1260 Performance

At 06:40 PM 12/6/2006, Brian Wipf wrote: I appreciate your suggestions, Ron. And that helps answer my question on processor selection for our next box; I wasn't sure if the lower MHz speed of the Kentsfield compared to the Woodcrest but with double the cores would be better for us overall or not.

Re: [PERFORM] VACUUM FULL does not works.......

Thanks Scott, It worked!!! We killed an old idle running transaction, now everything is fine.. Thanks Again asif ali icrossing inc Scott Marlowe <[EMAIL PROTECTED]> wrote: On Wed, 2006-12-06 at 15:53, asif ali wrote: > Thanks Everybody for helping me out. > I checked "pg_stat_activity"/pg_

Re: [PERFORM] Areca 1260 Performance

I appreciate your suggestions, Ron. And that helps answer my question on processor selection for our next box; I wasn't sure if the lower MHz speed of the Kentsfield compared to the Woodcrest but with double the cores would be better for us overall or not. On 6-Dec-06, at 4:25 PM, Ron wrote

Re: [PERFORM] Areca 1260 Performance (was: File Systems

The 1100 series is PCI-X based. The 1200 series is PCI-E x8 based. Apples and oranges. I still think Luke Lonergan or Josh Berkus may have some interesting ideas regarding possible OS and SW optimizations. WD1500ADFDs are each good for ~90MBps read and ~60MBps write ASTR. That means your 16

Re: [PERFORM] Areca 1260 Performance (was: File Systems Compared)

On 6-Dec-06, at 2:47 PM, Brian Wipf wrote: Hmmm. Something is not right. With a 16 HD RAID 10 based on 10K rpm HDs, you should be seeing higher absolute performance numbers. Find out what HW the Areca guys and Tweakers guys used to test the 1280s. At LW2006, Areca was demonstrating all-i

Re: [PERFORM] VACUUM FULL does not works.......

On Wed, 2006-12-06 at 15:53, asif ali wrote: > Thanks Everybody for helping me out. > I checked "pg_stat_activity"/pg_locks, but do not see any activity on > the table. > How to find a old running transaction... > I saw this link, but it did not help.. > http://archives.postgresql.org/pgsql-hacker

Re: [PERFORM] VACUUM FULL does not works.......

Thanks Everybody for helping me out. I checked "pg_stat_activity"/pg_locks, but do not see any activity on the table. How to find a old running transaction... I saw this link, but it did not help.. http://archives.postgresql.org/pgsql-hackers/2005-02/msg00760.php Thanks asif ali icross

Re: [PERFORM] File Systems Compared

Hmmm. Something is not right. With a 16 HD RAID 10 based on 10K rpm HDs, you should be seeing higher absolute performance numbers. Find out what HW the Areca guys and Tweakers guys used to test the 1280s. At LW2006, Areca was demonstrating all-in-cache reads and writes of ~1600MBps and ~

Re: [PERFORM] File Systems Compared

At 10:40 AM 12/6/2006, Brian Wipf wrote: All tests are with bonnie++ 1.03a Main components of system: 16 WD Raptor 150GB 1 RPM drives all in a RAID 10 ARECA 1280 PCI-Express RAID adapter with 1GB BB Cache (Thanks for the recommendation, Ron!) 32 GB RAM Dual Intel 5160 Xeon Woodcrest 3.0 GH

[PERFORM] Disk storage and san questions (was File Systems Compared)

Joshua D. Drake wrote: > I agree. I have many people that want to purchase a SAN because someone > told them that is what they need... Yet they can spend 20% of the cost > on two external arrays and get incredible performance... > > We are seeing great numbers from the following config: > > (2) HP

Re: [PERFORM] Problems with an update-from statement and pg-8.1.4

Rafael Martinez wrote: On Wed, 2006-12-06 at 14:19 -0600, Erik Jones wrote: Rafael Martinez wrote: On Wed, 2006-12-06 at 14:55 -0500, Ted Allen wrote: Stephan Szabo wrote: On Wed, 6 Dec 2006, Rafael Martinez wrote: mailstats=# EXPLAIN update

Re: [PERFORM] File Systems Compared

On Wed, Dec 06, 2006 at 18:45:56 +0100, Markus Schiltknecht <[EMAIL PROTECTED]> wrote: > > Cool, thank you for the example :-) I thought the MTA or at least the the > mailing list would wrap mails at some limit. I've now set word-wrap to > characters (it seems not possible to turn it off

Re: [PERFORM] Problems with an update-from statement and pg-8.1.4

On Wed, 2006-12-06 at 14:19 -0600, Erik Jones wrote: > Rafael Martinez wrote: > > On Wed, 2006-12-06 at 14:55 -0500, Ted Allen wrote: > > > >> Stephan Szabo wrote: > >> > >>> On Wed, 6 Dec 2006, Rafael Martinez wrote: > >>> > mailstats=# EXPLAIN update mail SET spamscore = '-5.0

Re: [PERFORM] File Systems Compared

Luke Lonergan wrote: Brian, On 12/6/06 8:40 AM, "Brian Hurt" <[EMAIL PROTECTED]> wrote: But actually looking things up, I see that PCI-Express has a theoretical 8 Gbit/sec, or about 800Mbyte/sec. It's PCI-X that's 533 MByte/sec. So there's still some headroom available there. See h

Re: [PERFORM] Problems with an update-from statement and pg-8.1.4

Rafael Martinez wrote: On Wed, 2006-12-06 at 14:55 -0500, Ted Allen wrote: Stephan Szabo wrote: On Wed, 6 Dec 2006, Rafael Martinez wrote: mailstats=# EXPLAIN update mail SET spamscore = '-5.026' FROM mail m, mail_received mr where mr.mail_id = m.mail_id AND mr.queue_id = '1Grx

Re: [PERFORM] File Systems Compared

On 12/6/06, Luke Lonergan <[EMAIL PROTECTED]> wrote: People buy SANs for interesting reasons, some of them having to do with the manageability features of high end SANs. I've heard it said in those cases that "performance doesn't matter much". There is movement in the industry right now away f

Re: [PERFORM] Problems with an update-from statement and pg-8.1.4

On Wed, 2006-12-06 at 14:55 -0500, Ted Allen wrote: > Stephan Szabo wrote: > > On Wed, 6 Dec 2006, Rafael Martinez wrote: > >> > >> mailstats=# EXPLAIN update mail SET spamscore = '-5.026' FROM mail m, > >> mail_received mr where mr.mail_id = m.mail_id AND mr.queue_id = > >> '1GrxLs-0004N9-I1' and

Re: [PERFORM] VACUUM FULL does not works.......

asif ali <[EMAIL PROTECTED]> writes: > INFO: vacuuming "public.product_table" > INFO: "product_table": found 0 removable, 139178 nonremovable row versions > in 4305 pages > DETAIL: 138859 dead row versions cannot be removed yet. So Scott's guess was correct: you've got a whole lot of dead r

Re: [PERFORM] Problems with an update-from statement and pg-8.1.4

Stephan Szabo wrote: On Wed, 6 Dec 2006, Rafael Martinez wrote: We are having some problems with an UPDATE ... FROM sql-statement and pg-8.1.4. It takes ages to finish. The problem is the Seq Scan of the table 'mail', this table is over 6GB without indexes, and when we send thousands of this

Re: [PERFORM] Problems with an update-from statement and pg-8.1.4

On Wed, 6 Dec 2006, Rafael Martinez wrote: > We are having some problems with an UPDATE ... FROM sql-statement and > pg-8.1.4. It takes ages to finish. The problem is the Seq Scan of the > table 'mail', this table is over 6GB without indexes, and when we send > thousands of this type of statement,

[PERFORM] Problems with an update-from statement and pg-8.1.4

Hello We are having some problems with an UPDATE ... FROM sql-statement and pg-8.1.4. It takes ages to finish. The problem is the Seq Scan of the table 'mail', this table is over 6GB without indexes, and when we send thousands of this type of statement, the server has a very high iowait percent.

Re: [PERFORM] [offtopic] File Systems Compared

On 6-Dec-06, at 9:05 AM, Alexander Staubo wrote: All tests are with bonnie++ 1.03a [snip] Care to post these numbers *without* word wrapping? Thanks. That's what Bonnie++'s output looks like. If you have Bonnie++ installed, you can run the following: bon_csv2html << EOF hulk4,64368M, 7862

Re: [PERFORM] VACUUM FULL does not works.......

Thanks for the prompt reply... Here is the output of "VACUUM FULL VERBOSE" The postgres version is "8.0.3". Thanks asif ali icrossing inc INFO: vacuuming "public.product_table" INFO: "product_table": found 0 removable, 139178 nonremovable row versions in 4305 pages DETAIL: 138859 de

Re: [PERFORM] File Systems Compared

On Wed, Dec 06, 2006 at 06:59:12PM +0100, Arnaud Lesauvage wrote: Markus Schiltknecht a écrit : What's common practice? What's it on the pgsql mailing lists? The netiquette usually advise mailers to wrap after 72 characters on mailing lists. This does not apply for format=flowed I guess (that

Re: [PERFORM] Locking in PostgreSQL?

Casey Duncan wrote: On Dec 5, 2006, at 11:04 PM, Joost Kraaijeveld wrote: Does PostgreSQL lock the entire row in a table if I update only 1 column? Know that updating 1 column is actually updating the whole row. So if one transaction updates column A of a row, it will block another concurre

[PERFORM] [offtopic] Word wrapping

On Wed, Dec 06, 2006 at 06:45:56PM +0100, Markus Schiltknecht wrote: > Cool, thank you for the example :-) I thought the MTA or at least the the > mailing list would wrap mails at some limit. I've now set word-wrap to > characters (it seems not possible to turn it off completely in > thund

Re: [PERFORM] File Systems Compared

Markus Schiltknecht a écrit : What's common practice? What's it on the pgsql mailing lists? The netiquette usually advise mailers to wrap after 72 characters on mailing lists. This does not apply for format=flowed I guess (that's the format used in Steinar's message). --

Re: [PERFORM] Locking in PostgreSQL?

On Dec 5, 2006, at 11:04 PM, Joost Kraaijeveld wrote: Does PostgreSQL lock the entire row in a table if I update only 1 column? Know that updating 1 column is actually updating the whole row. So if one transaction updates column A of a row, it will block another concurrent transaction that

Re: [PERFORM] VACUUM FULL does not works.......

On 12/6/06, asif ali <[EMAIL PROTECTED]> wrote: Hi, I have a "product" table having 350 records. It takes approx 1.8 seconds to get all records from this table. I copies this table to a "product_temp" table and run the same query to select all records; and it took 10ms(much faster). I did "VACU

Re: [PERFORM] File Systems Compared

Hi, Steinar H. Gunderson wrote: This is a rather long sentence without any kind of word wrapping except what would be imposed on your own side -- how to set that up properly depends on the sending e-mail client, but in mine it's just a matter of turning off the word wrapping in your editor :-

Re: [PERFORM] File Systems Compared

Brian, On 12/6/06 8:40 AM, "Brian Hurt" <[EMAIL PROTECTED]> wrote: > But actually looking things up, I see that PCI-Express has a theoretical 8 > Gbit/sec, or about 800Mbyte/sec. It's PCI-X that's 533 MByte/sec. So there's > still some headroom available there. See here for the official specifi

Re: [PERFORM] VACUUM FULL does not works.......

On Wed, 2006-12-06 at 11:07, asif ali wrote: > Hi, > I have a "product" table having 350 records. It takes approx 1.8 > seconds to get all records from this table. I copies this table to a > "product_temp" table and run the same query to select all records; and > it took 10ms(much faster). > I did

Re: [PERFORM] VACUUM FULL does not works.......

2006/12/6, asif ali <[EMAIL PROTECTED]>: Hi, I have a "product" table having 350 records. It takes approx 1.8 seconds to get all records from this table. I copies this table to a "product_temp" table and run the same query to select all records; and it took 10ms(much faster). I did "VACUUM FULL

[PERFORM] VACUUM FULL does not works.......

Hi, I have a "product" table having 350 records. It takes approx 1.8 seconds to get all records from this table. I copies this table to a "product_temp" table and run the same query to select all records; and it took 10ms(much faster). I did "VACUUM FULL" on "product" table but It did not wor

Re: [PERFORM] File Systems Compared

> Anyone run their RAIDs with disk caches enabled, or is this akin to > having fsync off? Disk write caches are basically always akin to having fsync off. The only time a write-cache is (more or less) safe to enable is when it is backed by a battery or in some other way made non-volatile. So a R

Re: [PERFORM] File Systems Compared

* Brian Wipf: > Anyone run their RAIDs with disk caches enabled, or is this akin to > having fsync off? If your cache is backed by a battery, enabling write cache shouldn't be a problem. You can check if the whole thing is working well by running this test script:

Re: [PERFORM] File Systems Compared

On Wed, Dec 06, 2006 at 05:31:01PM +0100, Markus Schiltknecht wrote: >> Care to post these numbers *without* word wrapping? Thanks. > How is one supposed to do that? Care giving an example? This is a rather long sentence without any kind of word wrapping except what would be imposed on your own s

Re: [PERFORM] File Systems Compared

Luke Lonergan wrote: Brian, On 12/6/06 8:02 AM, "Brian Hurt" <[EMAIL PROTECTED]> wrote: These numbers are close enough to bus-saturation rates PCIX is 1GB/s + and the memory architecture is 20GB/s+, though each CPU is likely to obtain only 2-3GB/s. We routinely achieve 1GB/s I/O ra

Re: [PERFORM] File Systems Compared

> As you suggest, database replication provides one of those features, and > Solaris ZFS has many of the data management features found in high end SANs. > Perhaps we can get the best of both? > > In the end, I think SAN vs. server storage is a religious battle. I agree. I have many people that

Re: [PERFORM] File Systems Compared

Hi, Alexander Staubo wrote: Care to post these numbers *without* word wrapping? Thanks. How is one supposed to do that? Care giving an example? Markus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] File Systems Compared

Brian, On 12/6/06 8:02 AM, "Brian Hurt" <[EMAIL PROTECTED]> wrote: > These numbers are close enough to bus-saturation rates PCIX is 1GB/s + and the memory architecture is 20GB/s+, though each CPU is likely to obtain only 2-3GB/s. We routinely achieve 1GB/s I/O rate on two 3Ware adapters and 2GB

Re: [PERFORM] Bad iostat numbers

On Dec 5, 2006, at 8:54 PM, Greg Smith wrote: On Tue, 5 Dec 2006, Craig A. James wrote: I'm not familiar with the inner details of software RAID, but the only circumstance I can see where things would get corrupted is if the RAID driver writes a LOT of blocks to one disk of the array bef

Re: [PERFORM] File Systems Compared

On Dec 6, 2006, at 16:40 , Brian Wipf wrote: All tests are with bonnie++ 1.03a [snip] Care to post these numbers *without* word wrapping? Thanks. Alexander. ---(end of broadcast)--- TIP 4: Have you searched our list archives? htt

Re: [PERFORM] File Systems Compared

Brian Wipf wrote: All tests are with bonnie++ 1.03a Thanks for posting these tests. Now I have actual numbers to beat our storage server provider about the head and shoulders with. Also, I found them interesting in and of themselves. These numbers are close enough to bus-saturation rates

[PERFORM] File Systems Compared

All tests are with bonnie++ 1.03a Main components of system: 16 WD Raptor 150GB 1 RPM drives all in a RAID 10 ARECA 1280 PCI-Express RAID adapter with 1GB BB Cache (Thanks for the recommendation, Ron!) 32 GB RAM Dual Intel 5160 Xeon Woodcrest 3.0 GHz processors OS: SUSE Linux 10.1 All run

Re: [GENERAL] [PERFORM] Locking in PostgreSQL?

On Wed, Dec 06, 2006 at 08:26:45AM -0500, Dave Cramer wrote: > >>Unless you specifically ask for it postgresql doesn't lock any > >>rows when you update data. > >> > >Thats not right. UPDATE will force a RowExclusiveLock to rows > >matching the WHERE clause, or all if no one is specified. > >

Re: [GENERAL] [PERFORM] Locking in PostgreSQL?

Hi, Dave Cramer wrote: Apparently I've completely misunderstood MVCC then Probably not. You are both somewhat right. Jens Schipkowski wrote: >> Thats not right. UPDATE will force a RowExclusiveLock to rows >> matching the WHERE clause, or all if no one is specified. That almost right, Ro

Re: [PERFORM] Locking in PostgreSQL?

On Wed, Dec 06, 2006 at 08:26:45AM -0500, Dave Cramer wrote: > Apparently I've completely misunderstood MVCC then My > understanding is that unless you do a select ... for update then > update the rows will not be locked . The discussion was about updates, not selects. Selects do not in ge

Re: [PERFORM] Locking in PostgreSQL?

On 6-Dec-06, at 8:20 AM, Jens Schipkowski wrote: On Wed, 06 Dec 2006 13:29:37 +0100, Dave Cramer <[EMAIL PROTECTED]> wrote: Unless you specifically ask for it postgresql doesn't lock any rows when you update data. Thats not right. UPDATE will force a RowExclusiveLock to rows matching t

Re: [PERFORM] Locking in PostgreSQL?

On Wed, 06 Dec 2006 13:29:37 +0100, Dave Cramer <[EMAIL PROTECTED]> wrote: Unless you specifically ask for it postgresql doesn't lock any rows when you update data. Thats not right. UPDATE will force a RowExclusiveLock to rows matching the WHERE clause, or all if no one is specified. @Joost

Re: [PERFORM] Locking in PostgreSQL?

Unless you specifically ask for it postgresql doesn't lock any rows when you update data. Dave On 6-Dec-06, at 2:04 AM, Joost Kraaijeveld wrote: Does PostgreSQL lock the entire row in a table if I update only 1 column? -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nij

Re: [PERFORM] Hardware advice

If your data is valuable I'd recommend against RAID5 ... see performance aside, I'd advise against RAID5 in almost all circumstances. Why take chances ? Greg Williamson DBA GlobeXplorer LLC -Original Message- From: [EMAIL PROTE

Re: [PERFORM] Hardware advice

Hi Alex, Please check out before you use RAID 5 for PostgreSQL. Anyhow, In a larger scale you end up in the response time of the I/O system for an read or write. The read is in modern RAID and SAN environments the part where you have to focus when you wan

Re: [PERFORM] Restart time

Rajesh Kumar Mallah a écrit : On 12/5/06, *Tom Lane* <[EMAIL PROTECTED] > wrote: Jean Arnaud <[EMAIL PROTECTED] > writes: > Is there a relation between database size and PostGreSQL restart duration ? No. > Does any