Re: [PERFORM] [GENERAL] Arguments Pro/Contra Software Raid

2b- LARGE UPS because HDs are the components that have the higher power consomption (a 700VA UPS gives me about 10-12 minutes on a machine with a XP2200+, 1GB RAM and a 40GB HD, however this fall to.. less than 25 secondes with seven HDs ! all ATA), I got my hands on a (free)

Re: [PERFORM] UNSUBSCRIBE

On May 10, 2006, at 14:42 , Tom Lane wrote: Chris <[EMAIL PROTECTED]> writes: Maybe :) The php-general list has To unsubscribe, visit: http://www.php.net/unsub.php at the bottom of every email, and there are still random unsubscribe requests.. That will *always* happen. Just human nat

Re: [PERFORM] UNSUBSCRIBE

Chris <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Maybe the real problem is at the other end of the process, ie we should >> require some evidence of a greater-than-room-temp IQ to subscribe in the >> first place? > Maybe :) The php-general list has > To unsubscribe, visit: http://www.php.ne

Re: [PERFORM] UNSUBSCRIBE

Tom Lane wrote: Chris <[EMAIL PROTECTED]> writes: Email admins - Could we add this above or below the random tips that get appended to every email ? You mean like these headers that already get added to every list message (these copied-and-pasted from your own message): The headers aren't

Re: [PERFORM] UNSUBSCRIBE

Chris <[EMAIL PROTECTED]> writes: > Email admins - Could we add this above or below the random tips that get > appended to every email ? You mean like these headers that already get added to every list message (these copied-and-pasted from your own message): List-help:

Re: [PERFORM] [GENERAL] Arguments Pro/Contra Software Raid

Steve Atkins <[EMAIL PROTECTED]> writes: > On May 9, 2006, at 2:16 AM, Hannes Dorbath wrote: > > > Hi, > > > > I've just had some discussion with colleagues regarding the usage of > > hardware or software raid 1/10 for our linux based database servers. > > > > I myself can't see much reason to

Re: [PERFORM] [GENERAL] Arguments Pro/Contra Software Raid

Douglas McNaught <[EMAIL PROTECTED]> writes: > Vivek Khera <[EMAIL PROTECTED]> writes: > > > On May 9, 2006, at 11:51 AM, Joshua D. Drake wrote: > > > >> And dollar for dollar, SCSI will NOT be faster nor have the hard > >> drive capacity that you will get with SATA. > > > > Does this hold true s

Re: [PERFORM] VACUUM killing my CPU

On May 9, 2006 02:45 am, [EMAIL PROTECTED] wrote: > What I am worry about is "93.5% wa" ... > > Could someone explain me what is the VACUUM process waiting for ? > Disk I/O. -- In a truly free society, "Alcohol, Tobacco and Firearms" would be a convenience store chain. ---

Re: [PERFORM] [GENERAL] Arguments Pro/Contra Software Raid

On May 9, 2006, at 11:26 AM, Joshua D. Drake wrote: Of course not, but which drives lie about sync that are SATA? Or more specifically SATA-II? I don't know the answer to this question, but have you seen this tool? http://brad.livejournal.com/2116715.html It attempts to experimentally de

Re: [PERFORM] Slow C Function

"Adam Palmblad" <[EMAIL PROTECTED]> writes: > We've got a C function that we use here and we find that for every > connection, the first run of the function is much slower than any > subsequent runs. ( 50ms compared to 8ms) Perhaps that represents the time needed to load the dynamic library into t

Re: [PERFORM] PostgreSQL VACCUM killing CPU

I have got such problem. Im running Postgresql 7.3.2 on Linux 2.6.13. Also, you should seriously consider upgrading. 8.1.3 is the current PostgreSQL release. If you must remain on 7.3, at least upgrade to 7.3.14, which contains many bugfixes. Michael Glaesemann grzm seespotcode net -

Re: [PERFORM] PostgreSQL VACCUM killing CPU

I have got such problem. Im running Postgresql 7.3.2 on Linux 2.6.13. Also, you should seriously consider upgrading. 8.1.3 is the current PostgreSQL release. If you must remain on 7.3, at least upgrade to 7.3.14, which contains *many* bugfixes. Michael Glaesemann grzm seespotcode net

Re: [PERFORM] UNSUBSCRIBE

Shoaib Burq wrote: UNSUBSCRIBE To unsubscribe: List-Unsubscribe: Email admins - Could we add this above or below the random tips that get appended to every email ? -- Postgresql & php tutorials http://www.designmagick.com/ ---(end of b

Re: [PERFORM] PostgreSQL VACCUM killing CPU

The "wa" means waiting on IO. Vacuum is a very IO intensive process. You can use tools like vmstat and iostat to see how much disk IO is occurring. Also, sar is very helpful for trending these values over time. -- Will Reese http://blog.rezra.com On May 9, 2006, at 5:19 AM, [EMAIL PROT

[PERFORM] UNSUBSCRIBE

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

[PERFORM] PostgreSQL VACCUM killing CPU

Hi all ! I have got such problem. Im running Postgresql 7.3.2 on Linux 2.6.13. What is see when VACCUM is running and killing my CPU is: Cpu(s): 3.2% us, 0.0% sy, 0.0% ni, 0.0% id, 96.8% wa, 0.0% hi, 0.0% si what i am worry about is "96.8% wa" why is it like that? what is the process waiti

Re: [HACKERS] [PERFORM] Big IN() clauses etc : feature proposal

On Tue, 2006-05-09 at 13:29 +0200, PFC wrote: > 0.101 ms BEGIN > 1.451 ms CREATE TEMPORARY TABLE tmp ( a INTEGER NOT NULL, b INTEGER NOT > NULL, c TIMESTAMP NOT NULL, d INTEGER NOT NULL ) ON COMMIT DROP > 0.450 ms INSERT INTO tmp SELECT * FROM bookmarks ORDER BY annonce_id DESC > LIMIT 20 > 0.4

Re: [PERFORM] [GENERAL] Arguments Pro/Contra Software Raid

Hi Hannes, Hannes Dorbath a écrit : > Hi, > > I've just had some discussion with colleagues regarding the usage of > hardware or software raid 1/10 for our linux based database servers. > > I myself can't see much reason to spend $500 on high end controller > cards for a simple Raid 1. Naa, you

Re: [PERFORM] performance question (something to do w/

On Mon, May 08, 2006 at 12:50:13PM -0500, Jim C. Nasby wrote: > On Mon, May 08, 2006 at 10:42:21AM -0700, Mark Lewis wrote: > > Doing a SELECT with a large list of variables inside an IN runs slowly > > on every database we've tested. We've tested mostly in Oracle and > > PostgreSQL, and both get

[PERFORM] VACUUM killing my CPU

Hi all ! I am running PostgreSQL 7.3.2 on Linux 2.6.13... What I see when VACUUM process is running is: Cpu(s): 0.0% us, 3.2% sy, 0.0% ni, 0.0% id, 93.5% wa, 3.2% hi, 0.0% si What I am worry about is "93.5% wa" ... Could someone explain me what is the VACUUM process waiting for ? Best re

[PERFORM]

Hi, We've got a C function that we use here and we find that for every connection, the first run of the function is much slower than any subsequent runs. ( 50ms compared to 8ms) Besides using connection pooling, are there any options to improve performance? By the way, we are using pg version 8.

[PERFORM] Dynamically loaded C function performance

Hi, We've got a C function that we use here and we find that for every connection, the first run of the function is much slower than any subsequent runs. ( 50ms compared to 8ms) Besides using connection pooling, are there any options to improve performance? -Adam ---(end

Re: [PERFORM] Performance Issues on Opteron Dual Core

I installed Ubuntu 5.10 on the production server (64-Bit version), and sure enough the peformance is like I expected. Opening up that table (320,000 records) takes 6 seconds, with CPU usage of one of the cores going up to 90% - 100% for the 6 seconds. I assume only one core is being used per user /

[PERFORM] slow variable against int??

I have a question about my function. I must get user rating by game result. This isn't probably a perfect solution but I have one question about select into inGameRating count(game_result)+1 from users where game_result > inRow.game_result; This query in function results in abo

Re: [PERFORM] Lot'sa joins - performance tip-up, please?

On Wed, 2006-05-03 at 13:58 -0400, Tom Lane wrote: > Mario Splivalo <[EMAIL PROTECTED]> writes: > > I have a quite large query that takes over a minute to run on my laptop. > > The EXPLAIN output you provided doesn't seem to agree with the stated > query. Where'd the "service_id = 1102" condition

Re: [PERFORM] Lot'sa joins - performance tip-up, please?

On Wed, 2006-05-03 at 10:20 -0500, Dave Dutcher wrote: > > -> Nested Loop (cost=0.00..176144.30 rows=57925 width=26) > > (actual time=68.322..529472.026 rows=57925 loops=1) > >-> Seq Scan on ticketing_codes_played > > (cost=0.00..863.25 rows=57925 width=8) (actual time=0

Re: [PERFORM] Arguments Pro/Contra Software Raid

William Yu wrote: We upgraded our disk system for our main data processing server earlier this year. After pricing out all the components, basically we had the choice of: LSI MegaRaid 320-2 w/ 1GB RAM+BBU + 8 15K 150GB SCSI or Areca 1124 w/ 1GB RAM+BBU + 24 7200RPM 250GB SATA My mistake

Re: [PERFORM] [GENERAL] Arguments Pro/Contra Software Raid

Scott Marlowe wrote: > Actually, in the case of the Escalades at least, the answer is yes. > Last year (maybe a bit more) someone was testing an IDE escalade > controller with drives that were known to lie, and it passed the power > plug pull test repeatedly. Apparently, the escalades tell the dr

Re: [GENERAL] [PERFORM] Arguments Pro/Contra Software Raid

Joshua D. Drake wrote: > Vivek Khera wrote: > > > > On May 9, 2006, at 11:51 AM, Joshua D. Drake wrote: > > > >> Sorry that is an extremely misleading statement. SATA RAID is > >> perfectly acceptable if you have a hardware raid controller with a > >> battery backup controller. > >> > >> And do

Re: [PERFORM] Postgres gets stuck

This is a deadly bug, because our web site goes dead when this happens, and it requires an administrator to log in and kill the stuck postgres process then restart Postgres. We've installed failover system so that the web site is diverted to a backup server, but since this has happened twice

[PERFORM] Postgres gets stuck

I'm having a rare but deadly problem. On our web servers, a process occasionally gets stuck, and can't be unstuck. Once it's stuck, all Postgres activities cease. "kill -9" is required to kill it -- signals 2 and 15 don't work, and "/etc/init.d/postgresql stop" fails. Here's what the process

Re: [PERFORM] Slow C Function

Adam Palmblad wrote: Hi, We've got a C function that we use here and we find that for every connection, the first run of the function is much slower than any subsequent runs. ( 50ms compared to 8ms) That is fairly standard because the data will be cached. Besides using connection pooling,

[PERFORM] Slow C Function

Hi, We've got a C function that we use here and we find that for every connection, the first run of the function is much slower than any subsequent runs. ( 50ms compared to 8ms) Besides using connection pooling, are there any options to improve performance? By the way, we are using pg version

Re: [HACKERS] [PERFORM] Big IN() clauses etc : feature proposal

PFC <[EMAIL PROTECTED]> writes: > Fun thing is, the rowcount from a temp table (which is the problem > here) > should be available without ANALYZE ; as the temp table is not concurrent, > it would be simple to inc/decrement a counter on INSERT/DELETE... No, because MVCC rules still appl

Re: [PERFORM] [GENERAL] Arguments Pro/Contra Software Raid

On Tue, 2006-05-09 at 12:52, Steve Atkins wrote: > On May 9, 2006, at 8:51 AM, Joshua D. Drake wrote: > > ("Using SATA drives is always a bit of risk, as some drives are lying > about whether they are caching or not.") > > >> Don't buy those drives. That's unrelated to whether you use hardware

Re: [PERFORM] [GENERAL] Arguments Pro/Contra Software Raid

Douglas McNaught wrote: Vivek Khera <[EMAIL PROTECTED]> writes: On May 9, 2006, at 11:51 AM, Joshua D. Drake wrote: And dollar for dollar, SCSI will NOT be faster nor have the hard drive capacity that you will get with SATA. Does this hold true still under heavy concurrent-write loads? I'm

Re: [PERFORM] [GENERAL] Arguments Pro/Contra Software Raid

On May 9, 2006, at 11:26 AM, Joshua D. Drake wrote: You're not suggesting that a hardware RAID controller will protect you against drives that lie about sync, are you? Of course not, but which drives lie about sync that are SATA? Or more specifically SATA-II? SATA-II, none that I'm awar

Re: [PERFORM] [GENERAL] Arguments Pro/Contra Software Raid

You're not suggesting that a hardware RAID controller will protect you against drives that lie about sync, are you? Of course not, but which drives lie about sync that are SATA? Or more specifically SATA-II? Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command P

Re: [PERFORM] Arguments Pro/Contra Software Raid

Vivek Khera wrote: On May 9, 2006, at 11:51 AM, Joshua D. Drake wrote: Sorry that is an extremely misleading statement. SATA RAID is perfectly acceptable if you have a hardware raid controller with a battery backup controller. And dollar for dollar, SCSI will NOT be faster nor have the hard

Re: [PERFORM] [GENERAL] Arguments Pro/Contra Software Raid

Vivek Khera <[EMAIL PROTECTED]> writes: > On May 9, 2006, at 11:51 AM, Joshua D. Drake wrote: > >> And dollar for dollar, SCSI will NOT be faster nor have the hard >> drive capacity that you will get with SATA. > > Does this hold true still under heavy concurrent-write loads? I'm > preparing yet

Re: [PERFORM] Arguments Pro/Contra Software Raid

On May 9, 2006, at 11:51 AM, Joshua D. Drake wrote: Sorry that is an extremely misleading statement. SATA RAID is perfectly acceptable if you have a hardware raid controller with a battery backup controller. And dollar for dollar, SCSI will NOT be faster nor have the hard drive capacity

Re: [PERFORM] [GENERAL] Arguments Pro/Contra Software Raid

On May 9, 2006, at 8:51 AM, Joshua D. Drake wrote: ("Using SATA drives is always a bit of risk, as some drives are lying about whether they are caching or not.") Don't buy those drives. That's unrelated to whether you use hardware or software RAID. Sorry that is an extremely misleading st

Re: [HACKERS] [PERFORM] Big IN() clauses etc : feature proposal

> SELECT * FROM somewhere WHERE id IN (SELECT id FROM result) Well, you can either SELECT * FROM somewhere JOIN (SELECT id FROM result GROUP BY id) AS a USING (id); It's the same thing (and postgres knows it) You might want to use PL to store values, say PLperl, or even C

Re: [HACKERS] [PERFORM] Big IN() clauses etc : feature proposal

On 5/9/06, PFC <[EMAIL PROTECTED]> wrote: > You might consider just selecting your primary key or a set of > primary keys to involved relations in your search query. If you > currently use "select *" this can make your result set very large. > > Copying all the result set to the temp. costs you

Re: [HACKERS] [PERFORM] Big IN() clauses etc : feature proposal

Does the time for commit change much if you leave out the analyze? Yes, when I don't ANALYZE the temp table, commit time changes from 30 ms to about 15 ms ; but the queries get horrible plans (see below) : Fun thing is, the rowcount from a temp table (which is the problem here) should

Re: [PERFORM] [HACKERS] Big IN() clauses etc : feature proposal

Creating cursors for a simple plan like a single sequential scan is fast because it's using the original data from the table. I used the following query : SELECT * FROM bookmarks ORDER BY annonce_id DESC LIMIT 20 It's a backward index scan + limit... not a seq scan. And it's

Re: [PERFORM] [GENERAL] Arguments Pro/Contra Software Raid

Don't buy those drives. That's unrelated to whether you use hardware or software RAID. Sorry that is an extremely misleading statement. SATA RAID is perfectly acceptable if you have a hardware raid controller with a battery backup controller. And dollar for dollar, SCSI will NOT be faster n

Re: [PERFORM] Arguments Pro/Contra Software Raid

On Tue, May 09, 2006 at 12:10:32 +0200, "Jean-Yves F. Barbier" <[EMAIL PROTECTED]> wrote: > Naa, you can find ATA &| SATA ctrlrs for about EUR30 ! But those are the ones that you would generally be better off not using. > Definitely NOT, however if your server doen't have a heavy load, the > so

Re: [PERFORM] [HACKERS] Big IN() clauses etc : feature proposal

PFC <[EMAIL PROTECTED]> writes: > > I really like this. It's clean, efficient, and easy to use. > > This would be a lot faster than using temp tables. > Creating cursors is very fast so we can create two, and avoid doing > twice the same work (ie. hashing the ids from the res

Re: [PERFORM] [GENERAL] Arguments Pro/Contra Software Raid

On May 9, 2006, at 2:16 AM, Hannes Dorbath wrote: Hi, I've just had some discussion with colleagues regarding the usage of hardware or software raid 1/10 for our linux based database servers. I myself can't see much reason to spend $500 on high end controller cards for a simple Raid 1.

Re: [PERFORM] Big IN() clauses etc : feature proposal

PFC <[EMAIL PROTECTED]> writes: > Feature proposal : > A way to store query results in a named buffer and reuse them in the > next > queries. Why not just fix the speed issues you're complaining about with temp tables? I see no reason to invent a new concept. (Now, "just fix" mig

Re: [PERFORM] Memory and/or cache issues?

Title: RE: [PERFORM] Memory and/or cache issues? Ok, thank you all again for your help in this matter.  Yes, Michael I (the original poster) did say or imply I guess is a better word for it that a combo of training and hands-on is the best way for one to learn PostgreSQL or just about anything

Re: [PERFORM] Arguments Pro/Contra Software Raid

-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Hannes Dorbath wrote: > Hi, > > I've just had some discussion with colleagues regarding the usage of > hardware or software raid 1/10 for our linux based database servers. > > I myself can't see much reason to spend $500 on high end controller >

Re: [HACKERS] [PERFORM] Big IN() clauses etc : feature proposal

It would be interesting to know what the bottleneck is for temp tables for you. They do not go via the buffer-cache, they are stored in private memory in the backend, they are not xlogged. Nor flushed to disk on backend exit. They're about as close to in-memory tables as you're going to get...

Re: [HACKERS] [PERFORM] Big IN() clauses etc : feature proposal

[snip] > It would be interesting to know what the bottleneck is for temp tables > for you. They do not go via the buffer-cache, they are stored in [snip] Is it possible that the temp table creation is the bottleneck ? Would that write into system catalogs ? If yes, maybe the system catalogs are no

Re: [HACKERS] [PERFORM] Big IN() clauses etc : feature proposal

On Tue, May 09, 2006 at 12:10:37PM +0200, PFC wrote: > Yes, but in this case temp tables add too much overhead. I wish > there were RAM based temp tables like in mysql. However I guess the > current temp table slowness comes from the need to mark their existence in > the system ca

Re: [HACKERS] [PERFORM] Big IN() clauses etc : feature proposal

Additionally to your query you are already transferring the whole result set multiple times. First you copy it to the result table. Then you read it again. Your subsequent queries will also have to read over all the unneeded tuples just to get your primary key. Considering that the resul

Re: [PERFORM] Big IN() clauses etc : feature proposal

Hi, On Tue, 9 May 2006, PFC wrote: You might consider just selecting your primary key or a set of primary keys to involved relations in your search query. If you currently use "select *" this can make your result set very large. Copying all the result set to the temp. costs you additional I

Re: [PERFORM] Big IN() clauses etc : feature proposal

Hi, On Tue, 9 May 2006, PFC wrote: Back to the point : I can't use the temp table method, because temp tables are too slow. Creating a temp table, filling it, analyzing it and then dropping it takes about 100 ms. The search query, on average, takes 10 ms. just some thoughts: You might con

Re: [PERFORM] Big IN() clauses etc : feature proposal

You might consider just selecting your primary key or a set of primary keys to involved relations in your search query. If you currently use "select *" this can make your result set very large. Copying all the result set to the temp. costs you additional IO that you propably dont need. It i

[PERFORM] Arguments Pro/Contra Software Raid

Hi, I've just had some discussion with colleagues regarding the usage of hardware or software raid 1/10 for our linux based database servers. I myself can't see much reason to spend $500 on high end controller cards for a simple Raid 1. Any arguments pro or contra would be desirable. From

[PERFORM] Big IN() clauses etc : feature proposal

The moral of the story is that you're probably better off running a bunch of small selects than in trying to optimize things with one gargantuan select. Ever experiment with loading the parameters into a temp table and joining to that? Also, it might be worth re-testing that conclusion with

Re: [PERFORM] Takes too long to fetch the data from database

On Tue, May 09, 2006 at 09:24:15 +0530, soni de <[EMAIL PROTECTED]> wrote: > > EXPLAIN > pdb=# EXPLAIN ANALYZE select * from wan where kname = 'pluto' order by stime > limit 50; > NOTICE: QUERY PLAN: > > Limit (cost=3515.32..3515.32 rows=50 width=95) (actual time= > 230492.69..230493.07 rows=