Re: [PERFORM] random_page_costs - are defaults of 4.0 realistic for SCSI RAID 1
"Luke Lonergan" <[EMAIL PROTECTED]> writes: > Right now the pattern for index scan goes like this: > > - Find qualifying TID in index > - Seek to TID location in relfile > - Acquire tuple from relfile, return >... > If we implement AIO and allow for multiple pending I/Os used to prefetch > groups of qualifying tuples, basically a form of random readahead Ah, I see what you mean now. It makes a lot more sense if you think of it for bitmap index scans. So, for example, the bitmap index scan could stream tids to the executor and the executor would strip out the block numbers and pass them to the i/o layer saying "i need this block now but following that I'll need these blocks so get them moving now". I think this seems pretty impractical for regular (non-bitmap) index probes though. You might be able to do it sometimes but not very effectively and you won't know when it would be useful. I think what this means is that there are actually *three* kinds of i/o: 1) Sequential which means you get the full bandwidth of your drives * the number of spindles; 2) Random which gets you 1 block per seek latency regardless of how many spindles you have; and 3) Random but with prefetch which gets you the random bandwidth above times the number of spindles. The extra spindles speed up sequential i/o too so the ratio between sequential and random with prefetch would still be about 4.0. But the ratio between sequential and random without prefetch would be even higher. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] [Again] Postgres performance problem
> Last time I had this problem i solved it stopping website, restarting > database, vacuumm it, run again website. But I guess this is going to > happen again. > > I would like to detect and solve the problem. Any ideas to detect it? Do you have very long transactions? Maybe some client that is connected all the time that is idle in transaction? /Dennis ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] random_page_costs - are defaults of 4.0 realistic for SCSI RAID 1
Greg, > I think this seems pretty impractical for regular > (non-bitmap) index probes though. You might be able to do it > sometimes but not very effectively and you won't know when it > would be useful. Maybe so, though I think it's reasonable to get multiple actuators going even if the seeks are truly random. It's a dynamic / tricky business to determine how many pending seeks to post, but it should be roughly close to the number of disks in the pool IMO. > I think what this means is that there are actually *three* > kinds of i/o: 1) Sequential which means you get the full > bandwidth of your drives * the number of spindles; 2) Random > which gets you 1 block per seek latency regardless of how > many spindles you have; and 3) Random but with prefetch which > gets you the random bandwidth above times the number of spindles. Perhaps so, though I'm more optimistic that prefetch would help most random seek situations. For reasonable amounts of concurrent usage this point becomes moot - we get the benefit of multiple backends doing seeking anyway, but I think if we do dynamic prefetch right it would degenerate gracefully in those circumstances. > The extra spindles speed up sequential i/o too so the ratio > between sequential and random with prefetch would still be > about 4.0. But the ratio between sequential and random > without prefetch would be even higher. Right :-) - Luke ---(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] [Again] Postgres performance problem
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 [EMAIL PROTECTED] escribió: >> Last time I had this problem i solved it stopping website, restarting >> database, vacuumm it, run again website. But I guess this is going to >> happen again. >> >> I would like to detect and solve the problem. Any ideas to detect it? > > Do you have very long transactions? Maybe some client that is connected > all the time that is idle in transaction? There should not be long transactions. I ll keep an eye on Idle transactions I m detecting it using: echo 'SELECT current_query FROM pg_stat_activity;' | /usr/local/pgsql/bin/psql vacadb | grep IDLE | wc -l > > /Dennis > > > -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFG5kiRIo1XmbAXRboRAj3sAKCH21zIhvdvPcmVQG71owiCye96xwCcDPe0 o/aArJF0JjUnTIFd1sMYD+Y= =6zyY -END PGP SIGNATURE- begin:vcard fn:Ruben Rubio n:Rubio;Ruben org:Rentalia Holidays S.L adr;quoted-printable:;;Gran v=C3=ADa 31, 9=C2=BA-1=C2=BA;Madrid;;;Spain email;internet:[EMAIL PROTECTED] tel;work:+34915233104 url:http://www.rentalia.com version:2.1 end:vcard ---(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
[PERFORM] Barcelona vs Tigerton
Hello, Now that both 4x4 out it's time for us to decide which one should be better for our PostgreSQL and Oracle. And especially for Oracle we really need such server to squeeze everything from Oracle licenses. Both of the databases handle OLTP type of the load. Since we plan to buy 4U HP DL580 or 585 and only very few of them so power ratings are not very critical in this our case. First benchmarks (http://www.anandtech.com/IT/showdoc.aspx?i=3091) show that Intel still has more raw CPU power but Barcelona scales much better and also has better memory bandwidth which I believe is quite critical with 16 cores and DB usage pattern. On the other hand Intel's X7350 (2.93GHz) has almost 50% advantage in CPU frequency against 2GHz Barcelona. Regards, Mindaugas P.S. tweakers.net does not have both of those yet? Test results far away? :) ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Barcelona vs Tigerton
Mindaugas, The Anandtech results appear to me to support a 2.5 GHz Barcelona performing better than the available Intel CPUs overall. If you can wait for the 2.5 GHz AMD parts to come out, they'd be a better bet IMO especially considering 4 sockets. In fact, have you seen quad QC Intel benchmarks? BTW - Can someone please get Anand a decent PG benchmark kit? :-) At least we can count on excellent PG bench results from the folks at Tweakers. - Luke > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Mindaugas > Sent: Tuesday, September 11, 2007 12:58 AM > To: pgsql-performance@postgresql.org > Subject: [PERFORM] Barcelona vs Tigerton > > Hello, > > Now that both 4x4 out it's time for us to decide which one > should be better for our PostgreSQL and Oracle. And > especially for Oracle we really need such server to squeeze > everything from Oracle licenses. Both of the databases handle > OLTP type of the load. > Since we plan to buy 4U HP DL580 or 585 and only very few > of them so power ratings are not very critical in this our case. > > First benchmarks > (http://www.anandtech.com/IT/showdoc.aspx?i=3091) show that > Intel still has more raw CPU power but Barcelona scales much > better and also has better memory bandwidth which I believe > is quite critical with 16 cores and DB usage pattern. > On the other hand Intel's X7350 (2.93GHz) has almost 50% > advantage in CPU frequency against 2GHz Barcelona. > > Regards, > > Mindaugas > > P.S. tweakers.net does not have both of those yet? Test > results far away? :) > > ---(end of > broadcast)--- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate > ---(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] random_page_costs - are defaults of 4.0 realistic for SCSI RAID 1
Luke Lonergan wrote: > For plans that qualify with the above conditions, the executor will issue > blocking calls to lseek(), which will translate to a single disk actuator > moving to the needed location in seek_time, approximately 8ms. I doubt it's actually the lseeks, but the reads/writes after the lseeks that block. > If we implement AIO and allow for multiple pending I/Os used to prefetch > groups of qualifying tuples, basically a form of random readahead, we can > improve the throughput for any given query by taking advantage of multiple > disk actuators. Rather than jumping to AIO, which is a huge change, I think we could get much of the benefit by using posix_fadvise(WILLNEED) in strategic places to tell the OS what pages we're going to need in the near future. If the OS has implemented that properly, it should schedule I/Os for the requested pages ahead of time. That would require very little change to PostgreSQL code, and could simply be #ifdef'd away on platforms that don't support posix_fadvise. > Note that > the same approach would also work to speed sequential access by overlapping > compute and I/O. Yes, though the OS should already doing read ahead for us. How efficient it is is another question. posix_fadvise(SEQUENTIAL) could be used to give a hint on that as well. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] random_page_costs - are defaults of 4.0 realistic for SCSI RAID 1
Gregory Stark wrote (in part): > The extra spindles speed up sequential i/o too so the ratio between sequential > and random with prefetch would still be about 4.0. But the ratio between > sequential and random without prefetch would be even higher. > I never figured out how extra spindles help sequential I-O because consecutive logical blocks are not necessarily written consecutively in a Linux or UNIX file system. They try to group a bunch (8 512-bit?) of blocks together, but that is about it. So even if you are reading sequentially, the head actuator may be seeking around anyway. I suppose you could fix this, if the database were reasonably static, by backing up the entire database, doing a mkfs on the file system, and restoring it. This might make the database more contiguous, at least for a while. When I was working on a home-brew UNIX dbms, I used raw IO on a separate disk drive so that the files could be contiguous, and this would work. Similarly, IBM's DB2 does that (optionally). But it is my understanding that postgreSQL does not. OTOH, the large (in my case) cache in the kernel can be helpful if I seek around back and forth to nearby records since they may be in the cache. On my 8 GByte RAM, I have the shared buffers set to 200,000 which should keep any busy stuff in memory, and there are about 6 GBytes of ram presently available for the system I-O cache. I have not optimized anything yet because I am still laundering the major input data to initialize the database so I do not have any real transactions going through it yet. I have 6 SCSI hard drives on two Ultra/320 SCSI controllers. Of the database partitions, sda8 has the write-ahead-log, sdb7 has a few tiny seldom-used tables and pg_log, and sdc1, sdd1, sde1, and sdf1 are just for the other tables. For the data on sd[c-f]1 (there is nothing else on these drives), I keep the index for a table on a different drive from the data. When populating the database initially, this seems to help since I tend to fill one table, or a very few tables, at a time, so the table itself and its index do not contend for the head actuator. Presumably, the SCSI controllers can do simultaneous seeks on the various drives and one transfer on each controller. When loading the database (using INSERTs mainly -- because the input data are gawdawful unnormalized spreadsheets obtained from elsewhere, growing once a week), the system is IO limited with seeks (and rotational latency time). IO transfers average about 1.7 Megabytes/second, although there are peaks that exceed 10 Megabytes/second. If I run pg_restore from a backup tape, I can see 90 Megabyte/second transfer rates for bursts of several seconds at a time, but that is pretty much of a record. -- .~. Jean-David Beyer Registered Linux User 85642. /V\ PGP-Key: 9A2FC99A Registered Machine 241939. /( )\ Shrewsbury, New Jerseyhttp://counter.li.org ^^-^^ 06:35:01 up 33 days, 9:57, 0 users, load average: 4.06, 4.07, 4.02 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] DRBD and Postgres: how to improve the perfomance?
Thank you very much for your ideas. I've tried to change the protocol from C to B and I got an increase in the number of TPS: 64.555763. Now I would like to follow the advice of Mr. Bernd Helmle and change the value of snd-bufsize. The servers are cross connected with a common 100 Mbit/sec Ethernet so I think they have a bandwidth around 80 Mbit/sec (even if I haven't yet done any test on it). A rate of 70Mb seems reasonable to me. The two servers are in two different racks (next to each other) and they have two power supplies connected to two different sets of UPS. Unfortunately we cannot accept a loss of recently committed transactions so we cannot put the synchronous_commit to off. Regards, Maila Fatticcioni Simon Riggs wrote: > On Fri, 2007-09-07 at 11:37 +0200, Maila Fatticcioni wrote: > >> protocol C; > > Try protocol B instead. > -- __ Maila Fatticcioni __ Mediterranean Broadband Infrastructure s.r.l. ITALY __ signature.asc Description: OpenPGP digital signature
Re: [PERFORM] DRBD and Postgres: how to improve the perfomance?
On Tue, Sep 11, 2007 at 04:47:40PM +0200, Maila Fatticcioni wrote: > The servers are cross connected with a common 100 Mbit/sec Ethernet so I > think they have a bandwidth around 80 Mbit/sec (even if I haven't yet > done any test on it). A rate of 70Mb seems reasonable to me. Umm, seriously? Unless that was a typo, you should consider very seriously to go to gigabit; it's cheap these days, and should provide you with a very decent speed boost if the network bandwidth is the bottleneck. /* Steinar */ -- Homepage: http://www.sesse.net/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Barcelona vs Tigerton
On Tue, 11 Sep 2007, Mindaugas wrote: Now that both 4x4 out it's time for us to decide which one should be better for our PostgreSQL and Oracle. You're going to have to wait a bit for that. No one has had both to compare for long enough yet to reach a strong conclusion, and you're probably going to need a database-specific benchmark before there's useful data for your case. Yesterday's meta-coverage at Ars was a nice summary of the current state of things: http://arstechnica.com/news.ars/post/20070910-barcelonas-out-and-the-reviews-are-out.html -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(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
[PERFORM] More Vacuum questions...
Hi List; I've recently started cleaning up some postgres db's which previous to my recent arrival had no DBA to care for them. I quickly figured out that there were several tables which were grossly full of dead space. One table in particular had 75G worth of dead pages (or the equivelant in overall dead rows). So I rebuilt these several tables via this process: 1) BEGIN; 2) LOCK TABLE table_old (the current table) 2) CREATE TABLE table_new (...) (copy of table_old above without the indexes) 3) insert into table_new select * from table_old; 4) DROP TABLE table_old; 5) ALTER TABLE table_new rename to table_old; 6) CREATE INDEX (create all original table indexes) 7) COMMIT; The biggest table mentioned above did in fact reduce the able's overall size by about 69G. After the table rebuild, as an interum measure since I'm still tuning and I need to go through a full test/qa/prod lifecycle to get anything rolled onto the production servers I added this table to pg_autovacuum with enabled = 'f' and setup a daily cron job to vacuum the table during off hours. This was due primarily to the fact that the vacuum of this table was severely impacting day2day processing. I've since upped the maintenance_work_mem to 300,000 and in general the vacuums no longer impact day2day processing - with the exception of this big table. I let the cron vacuum run for 14 days. in that 14 days the time it takes to vacuum the table grew from 1.2hours directly after the rebuild to > 8hours last nite. It's difficult to try and vacuum this table during the day as it seems to begin blocking all the other queries against the database after some time. I plan to rebuild the table again and see if I can get away with vacuuming more often - it during the day. Also I'm considering a weekly cron job each Sunday (minimal processing happens on the weekends) to rebuild the table. Just curious if anyone has any thoughts on an automated rebuild scenario? or better yet managing the vac of this table more efficiently? Maybe it's worth upping maintenance_work_mem sky-high for this table (via a session specific SET of maintenance_work_mem) and running a vacuum every 3 hours or so. Also, does Postgres allocate maintenence_work_memory from the overall shared_buffers space available (I think not) ? Is there some method / guideline I could use to determine the memory needs on a table by table basis for the vacuum process ? If so, I suspect I could use this as a guide for setting a session specific maintenance_work_mem via cron to vacuum these problem tables on a specified schedule. Thanks in advance... /Kevin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] More Vacuum questions...
Kevin Kempter wrote: > It's difficult to try and vacuum this table during the day as it seems to > begin blocking all the other queries against the database after some time. Vacuum can generate so much I/O that it overwhelms all other transactions, but it shouldn't block other queries otherwise. You can use the vacuum cost delay options to throttle vacuum so that it doesn't runs slower, but doesn't disrupt other operations so much. > I plan to rebuild the table again and see if I can get away with vacuuming > more > often - it during the day. Also I'm considering a weekly cron job each Sunday > (minimal processing happens on the weekends) to rebuild the table. > > Just curious if anyone has any thoughts on an automated rebuild scenario? or > better yet managing the vac of this table more efficiently? CLUSTER is a handy way to do rebuild tables. > Maybe it's worth upping maintenance_work_mem sky-high for this table (via a > session specific SET of maintenance_work_mem) and running a vacuum every 3 > hours or so. You only need enough maintenance_work_mem to hold pointers to all dead tuples in the table. Using more than that won't help. > Also, does Postgres allocate maintenence_work_memory from the > overall shared_buffers space available (I think not) ? No. > Is there some method / guideline I could use to determine the memory needs on > a table by table basis for the vacuum process ? If so, I suspect I could use > this as a guide for setting a session specific maintenance_work_mem via cron > to vacuum these problem tables on a specified schedule. You need 6 bytes per dead tuple in the table to avoid scanning the indexes more than once. If you vacuum regularly, you shouldn't need more than a few hundred MB. One way is to run VACUUM VERBOSE, which will tell how many passes it used. If it used more than one, increase maintenance_work_mem. I would suggest using autovacuum after all. If it seems to be disrupting other activity too much, increase autovacuum_cost_delay. Or decrease it if it can't keep up with the updates. BTW, you didn't mention which version of PostgreSQL you're using. There's been some performance enhancements to VACUUM in 8.2, as well as autovacuum changes. You might consider upgrading if you're not on 8.2 already. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] random_page_costs - are defaults of 4.0 realistic for SCSI RAID 1
"Jean-David Beyer" <[EMAIL PROTECTED]> writes: > Gregory Stark wrote (in part): > >> The extra spindles speed up sequential i/o too so the ratio between >> sequential >> and random with prefetch would still be about 4.0. But the ratio between >> sequential and random without prefetch would be even higher. >> > I never figured out how extra spindles help sequential I-O because > consecutive logical blocks are not necessarily written consecutively in a > Linux or UNIX file system. They try to group a bunch (8 512-bit?) of blocks > together, but that is about it. So even if you are reading sequentially, the > head actuator may be seeking around anyway. That's somewhat true but good filesystems group a whole lot more than 8 blocks together. You can do benchmarks with dd and compare the speed of reading from a file with the speed of reading from the raw device. On typical consumer drives these days you'll get 50-60MB/s raw and I would expect not a whole lot less than that with a large ext2 file, at least if it's created all in one chunk on a not overly-full filesystem. (Those assumptions is not necessarily valid for Postgres which is another topic, but one that requires some empirical numbers before diving into.) -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Re: 500rows = 1min/2.5k rows=20min/6K rows 2 hours and still running
El-Lotso <[EMAIL PROTECTED]> writes: > sorry.. I sent this as I was about to go to bed and the explain analyse > of the query w/ 4 tables joined per subquery came out. It's those factor-of-1000 misestimates of the join sizes that are killing you, eg this one: > -> Hash Join (cost=249.61..512.56 rows=1 width=87) (actual > time=15.139..32.858 rows=969 loops=1) > Hash Cond: (((test_db.ts.id)::text = > (test_db.d.id)::text) AND (test_db.ts.start_timestamp = > test_db.trd.start_timestamp) AND (test_db.ts.ttype = test_db.trd.ttype)) > -> Seq Scan on ts (cost=0.00..226.44 rows=3244 > width=40) (actual time=0.135..6.916 rows=3244 loops=1) > -> Hash (cost=235.00..235.00 rows=835 width=47) (actual > time=14.933..14.933 rows=1016 loops=1) The single-row-result estimate persuades it to use a nestloop at the next level up, and then when the output is actually 969 rows, that means 969 executions of the other side of the upper join. The two input size estimates are reasonably close to reality, so the problem seems to be in the estimate of selectivity of the join condition. First off, do you have up-to-date statistics for all the columns being joined here? It might be that increasing the statistics targets for those columns would help. But what I'm a bit worried about is the idea that the join conditions are correlated or even outright redundant; the planner will not know that, and will make an unrealistic estimate of their combined selectivity. If that's the case, you might need to redesign the table schema to eliminate the redundancy before you'll get good plans. regards, tom lane ---(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] DRBD and Postgres: how to improve the perfomance?
Simon Riggs schrieb: On Fri, 2007-09-07 at 11:37 +0200, Maila Fatticcioni wrote: protocol C; Try protocol B instead. Sure? I've always heard that there has yet to be a case found, where B is better than C. We use DRBD with protocol C, and are quite happy with it. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PERFORM] efficient pattern queries (using LIKE, ~)
Hi, I have a table containing some ~13 million rows. Queries on indexed fields run fast, but unanchored pattern queries on a text column are slow. Indexing the column doesn't help (this is already mentioned in the manual). http://www.postgresql.org/docs/8.2/interactive/indexes-types.html However, no alternative solution is mentioned for indexing and/or optimizing queries based on unanchored patterns: i.e. description LIKE '%kinase%'. I've already searched the archives, read the manual, googled around and the only alternative I've found is: full text indexing (tsearch2 in postgresql-contrib; OpenFTS; others?) But do note that i) I'm not interested in finding results 'similar to' the query term (and ranked based on similarity) but just results 'containing an exact substring' of the query term ... i.e. not the original goal of a full text search And, ii) from what I've read it seems that for both tsearch2 and OpenFTS the queries have to be rewritten to explicitly evaluate the pattern on the special indices, i.e. they're not transparently available (i.e. via the query planner), I'm hoping for something like: CREATE INDEX newindex ON table USING fti (column); and then having the new index automagically used by the planner in cases like: SELECT * FROM table WHERE column LIKE '%magic%'; If there's anything like this, I've failed at finding it ... Thanks for any pointer, Fernan PS: additional information This is on PostgreSQL-8.2.4, FreeBSD-6.2 (amd64). EXPLAIN ANALYZE SELECT COUNT(*) FROM dots.transcript WHERE product LIKE '%kinase%'; QUERY PLAN QUERY PLAN - Aggregate (cost=651878.85..651878.86 rows=1 width=0) (actual time=45587.244..45587.246 rows=1 loops=1) -> Seq Scan on nafeatureimp (cost=0.00..651878.85 rows=1 width=0) (actual time=33.049..45582.628 rows=2255 loops=1) Filter: (((subclass_view)::text = 'Transcript'::text) AND ((string13)::text ~~ '%kinase%'::text)) Total runtime: 45589.892 ms (4 rows) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] efficient pattern queries (using LIKE, ~)
Fernan Aguero schrieb: Hi, I have a table containing some ~13 million rows. Queries on indexed fields run fast, but unanchored pattern queries on a text column are slow. Indexing the column doesn't help (this is already mentioned in the manual). http://www.postgresql.org/docs/8.2/interactive/indexes-types.html However, no alternative solution is mentioned for indexing and/or optimizing queries based on unanchored patterns: i.e. description LIKE '%kinase%'. Maybe trigram search might help you? Never tried it myself, but it seems to be able to handle substring searches. ---(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] random_page_costs - are defaults of 4.0 realistic for SCSI RAID 1
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Gregory Stark wrote: > "Jean-David Beyer" <[EMAIL PROTECTED]> writes: > >> Gregory Stark wrote (in part): >> >>> The extra spindles speed up sequential i/o too so the ratio between >>> sequential >>> and random with prefetch would still be about 4.0. But the ratio between >>> sequential and random without prefetch would be even higher. >>> >> I never figured out how extra spindles help sequential I-O because >> consecutive logical blocks are not necessarily written consecutively in a >> Linux or UNIX file system. They try to group a bunch (8 512-bit?) of blocks >> together, but that is about it. So even if you are reading sequentially, the >> head actuator may be seeking around anyway. > > That's somewhat true but good filesystems group a whole lot more than 8 blocks > together. You can do benchmarks with dd and compare the speed of reading from > a file with the speed of reading from the raw device. On typical consumer > drives these days you'll get 50-60MB/s raw and I would expect not a whole lot > less than that with a large ext2 file, at least if it's created all in one > chunk on a not overly-full filesystem. # date; dd if=/dev/sda8 of=/dev/null;date Tue Sep 11 14:27:36 EDT 2007 8385867+0 records in 8385867+0 records out 4293563904 bytes (4.3 GB) copied, 71.7648 seconds, 59.8 MB/s Tue Sep 11 14:28:48 EDT 2007 # date; dd bs=8192 if=/dev/sda8 of=/dev/null;date Tue Sep 11 14:29:15 EDT 2007 524116+1 records in 524116+1 records out 4293563904 bytes (4.3 GB) copied, 68.2595 seconds, 62.9 MB/s Tue Sep 11 14:30:23 EDT 2007 # date; dd bs=8192 if=/srv/dbms/dataA/pgsql/data/pg_xlog/0001002B002F of=/dev/null;date Tue Sep 11 14:34:25 EDT 2007 2048+0 records in 2048+0 records out 16777216 bytes (17 MB) copied, 0.272343 seconds, 61.6 MB/s Tue Sep 11 14:34:26 EDT 2007 The first two are the partition where the W.A.L. is in (and a bit more: [/srv/dbms/dataA/pgsql/data]# ls -l total 104 - -rw--- 1 postgres postgres 4 Aug 11 13:32 PG_VERSION drwx-- 5 postgres postgres 4096 Aug 11 13:32 base drwx-- 2 postgres postgres 4096 Sep 11 14:35 global drwx-- 2 postgres postgres 4096 Sep 10 18:58 pg_clog - -rw--- 1 postgres postgres 3396 Aug 11 13:32 pg_hba.conf - -rw--- 1 root root 3396 Aug 16 14:32 pg_hba.conf.dist - -rw--- 1 postgres postgres 1460 Aug 11 13:32 pg_ident.conf drwx-- 4 postgres postgres 4096 Aug 11 13:32 pg_multixact drwx-- 2 postgres postgres 4096 Sep 10 19:48 pg_subtrans drwx-- 2 postgres postgres 4096 Aug 12 16:14 pg_tblspc drwx-- 2 postgres postgres 4096 Aug 11 13:32 pg_twophase drwx-- 3 postgres postgres 4096 Sep 10 19:53 pg_xlog - -rw--- 1 postgres postgres 15527 Sep 8 00:35 postgresql.conf - -rw--- 1 postgres postgres 13659 Aug 11 13:32 postgresql.conf.dist - -rw--- 1 root root 15527 Sep 4 10:37 postgresql.conf~ - -rw--- 1 postgres postgres56 Sep 8 08:12 postmaster.opts - -rw--- 1 postgres postgres53 Sep 8 08:12 postmaster.pid It is tricky for me to find a big enough file to test. I tried one of the pg_xlog files, but I cannot easily copy from there because it acts a bit interactive and the time is mostly my time. If I copy it elsewhere and give it to non-root, then it is all in the cache, so it does not really read it. > > (Those assumptions is not necessarily valid for Postgres which is another > topic, but one that requires some empirical numbers before diving into.) > - -- .~. Jean-David Beyer Registered Linux User 85642. /V\ PGP-Key: 9A2FC99A Registered Machine 241939. /( )\ Shrewsbury, New Jerseyhttp://counter.li.org ^^-^^ 14:30:04 up 33 days, 17:52, 1 user, load average: 5.50, 4.67, 4.29 -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) Comment: Using GnuPG with CentOS - http://enigmail.mozdev.org iD8DBQFG5uM4Ptu2XpovyZoRAhtlAKDFs5eP/CGIqB/z207j2dpwDSHOlwCfevp4 lBWn3b2GW6gesaq+l3Rbooc= =F4H6 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Barcelona vs Tigerton
Mindaugas wrote: Hello, Now that both 4x4 out it's time for us to decide which one should be better for our PostgreSQL and Oracle. And especially for Oracle we really need such server to squeeze everything from Oracle licenses. Both of the databases handle OLTP type of the load. Since we plan to buy 4U HP DL580 or 585 and only very few of them so power ratings are not very critical in this our case. First benchmarks (http://www.anandtech.com/IT/showdoc.aspx?i=3091) show that Intel still has more raw CPU power but Barcelona scales much better and also has better memory bandwidth which I believe is quite critical with 16 cores and DB usage pattern. On the other hand Intel's X7350 (2.93GHz) has almost 50% advantage in CPU frequency against 2GHz Barcelona. Barcelona was just announced yesterday. I wouldn't want to be betting my business on it just yet. Plus, AMD usually is able to up the clock on their chips pretty well after they've got a few production runs under their belts. If you've absolutely got to have something today, I'd say Intel would be a safer bet. If you can afford to wait 3-4 months, then you'll benefit from some industry experience as well as production maturity with Barcelona, and can judge then which better fits your needs. -- Guy Rouillier ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Barcelona vs Tigerton
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Guy Rouillier wrote: > Mindaugas wrote: >> Hello, > Barcelona was just announced yesterday. I wouldn't want to be betting > my business on it just yet. Plus, AMD usually is able to up the clock on > their chips pretty well after they've got a few production runs under > their belts. If you've absolutely got to have something today, I'd say > Intel would be a safer bet. If you can afford to wait 3-4 months, then > you'll benefit from some industry experience as well as production > maturity with Barcelona, and can judge then which better fits your needs. > AMD has already stated they will be ramping up the MHZ toward the end of the year. Patience is a virtue. Joshua D. Drake - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFG5vO3ATb/zqfZUUQRAkv5AJ9vEF/OnM23X30YdWVIiY2xGtDrHACfZ678 fYfZxD7XdIH+VYYzhGSz9w4= =eBTJ -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Performance on 8CPU's and 32GB of RAM
On Wed, Sep 05, 2007 at 11:06:03AM -0400, Carlo Stonebanks wrote: > Unfortunately, LINUX is not an option at this time. We looked into it; there > is no *NIX expertise in the enterprise. However, I have raised this issue in > various forums before, and when pressed no one was willing to say that "*NIX > *DEFINITELY* outperforms Windows" for what my client is doing (or if it did > outperform Windows, that it would outperform so significantly that it > merited the move). > > Was this incorrect? Can my client DEFINITELY expect a significant > improvement in performance for what he is doing? Since we don't know your actual workload, there's no way to predict this. That's what benchmarking is for. If you haven't already bought the hardware, I'd strongly recommend benchmarking this before buying anything, so that you have a better idea of what your workload looks like. Is it I/O-bound? CPU-bound? Memory? One of the fastest ways to non-performance in PostgreSQL is not vacuuming frequently enough. Vacuum more, not less, and control IO impact via vacuum_cost_delay. Make sure the FSM is big enough, too. Unless your database is small enough to fit in-memory, your IO subsystem is almost certainly going to kill you. Even if it does fit in memory, if you're doing much writing at all you're going to be in big trouble. -- Decibel!, aka Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgpvMfqORWRv7.pgp Description: PGP signature
Re: [PERFORM] Hardware spec
On Thu, Sep 06, 2007 at 11:26:46AM +0200, Willo van der Merwe wrote: > Richard Huxton wrote: > >Willo van der Merwe wrote: > >>Hi guys, > >> > >>I'm have the rare opportunity to spec the hardware for a new database > >>server. It's going to replace an older one, driving a social networking > >>web application. The current server (a quad opteron with 4Gb of RAM and > >>80Gb fast SCSI RAID10) is coping with an average load of ranging between > >>1.5 and 3.5. > >> > >>The new machine spec I have so far: > >What's the limiting factor on your current machine - disk, memory, cpup? > I'm a bit embarrassed to admit that I'm not sure. The reason we're > changing machines is that we might be changing ISPs and we're renting / > leasing the machines from the ISP. Get yourself the ability to benchmark your application. This is invaluable^W a requirement for any kind of performance tuning. -- Decibel!, aka Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgpnCHpHesYZG.pgp Description: PGP signature
Re: [PERFORM] SAN vs Internal Disks
On Fri, Sep 07, 2007 at 02:10:32PM -0700, [EMAIL PROTECTED] wrote: > >3. Easy to set up "warm standby" functionality. (Then again, if the > >postgres server fails miserably, it's likely to be due to a disk > >crash). > > and if postgres dies for some other reason the image on disk needs repair, > unless you script stopping postgres when the SAN does it's snapshots, > those snapshots are not going to be that good. the problems are useually > repairable, but that makes starting your warm spare harder. Uh, the "image" you get from a PITR backup "needs repair" too. There's absolutely nothing wrong with using a SAN or filesystem snapshot as a backup mechanism, as long as it's a true snapshot, and it includes *all* PostgreSQL data (everything under $PGDATA as well as all tablespaces). Also, to reply to someone else's email... there is one big reason to use a SAN over direct storage: you can do HA that results in 0 data loss. Good SANs are engineered to be highly redundant, with multiple controllers, PSUs, etc, so that the odds of losing the SAN itself are very, very low. The same isn't true with DAS. But unless you need that kind of HA recovery, I'd tend to stay away from SANs. BTW, if you need *serious* bandwidth, look at things like Sun's "thumper" (I know there's at least one other company that makes something similar). 40-48 drives in a single 4U chassis == lots of throughput. Finally, if you do get a SAN, make sure and benchmark it. I've seen more than one case of a SAN that wasn't getting anywhere near the performance it should be, even with a simple dd test. -- Decibel!, aka Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgprqUCHZieqB.pgp Description: PGP signature
Re: [PERFORM] DRBD and Postgres: how to improve the perfomance?
On Mon, Sep 10, 2007 at 12:06:40AM -0400, Greg Smith wrote: > On Sat, 8 Sep 2007, Joshua D. Drake wrote: > > >You would have to have lightning handed by God to your server to have a > >total power failure without proper shutdown in the above scenario. > > Do you live somewhere without thunderstorms? This is a regular event in Actually, he does. :) Or at least I don't think Portland gets a lot of t-storms, just rain by the bucketful. -- Decibel!, aka Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgpkpeZ765p7x.pgp Description: PGP signature
Re: [PERFORM] SAN vs Internal Disks
On Tue, Sep 11, 2007 at 03:55:51PM -0500, Decibel! wrote: Also, to reply to someone else's email... there is one big reason to use a SAN over direct storage: you can do HA that results in 0 data loss. Good SANs are engineered to be highly redundant, with multiple controllers, PSUs, etc, so that the odds of losing the SAN itself are very, very low. The same isn't true with DAS. You can get DAS arrays with multiple controllers, PSUs, etc. DAS != single disk. Mike Stone ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] DRBD and Postgres: how to improve the perfomance?
On Mon, Sep 10, 2007 at 12:54:37AM -0400, Tom Lane wrote: > Greg Smith <[EMAIL PROTECTED]> writes: > > On Sat, 8 Sep 2007, Joshua D. Drake wrote: > >> You would have to have lightning handed by God to your server to have a > >> total power failure without proper shutdown in the above scenario. > > > Do you live somewhere without thunderstorms? This is a regular event in > > this part of the world during the summer. It happened to me once this > > year and once last; lost count for previous ones. In both of the recent > > cases it's believed the servers were burned from the Ethernet side because > > somewhere in the network was a poor switch that wasn't isolated well > > enough from the grid when the building was hit. Lightning is tricky that > > way; cable TV and satellite wiring are also weak links that way. > > Yeah. I've lost half a dozen modems of varying generations, a server > motherboard, a TiVo, a couple of VCRs, and miscellaneous other equipment > from strikes near my house --- none closer than a couple blocks away. > I don't really care to think about what would still work after a direct > hit, despite the whole-house surge suppressor at the meter and the local > suppressor on each circuit and the allegedly surge-proof UPSes powering > all the valuable stuff. I've also moved heavily into wireless local Pretty much every surge supressor out there is a POS... 99.9% of them just wire a varistor across the line; like a $0.02 part is going to stop a 10,00+ amp discharge. The only use I have for those things is if they come with an equipment guarantee, though I have to wonder how much those are still honored, since as you mention it's very easy for equipment to be fried via other means (ethernet, monitor, etc). > net to eliminate any direct electrical connections between machines that > are not on the same power circuit (the aforesaid burned motherboard > taught me that particular lesson). And yet I still fear every time a > thunderstorm passes over. Wired is safe as long as everything's on the same circuit. My house is wired for ethernet with a single switch running what's going to every room, but in each room I have a second switch on the same power as whatever's in that room; so if there is a strike it's far more likely that I'll lose switches and not hardware. > Then of course there are the *other* risks, such as the place burning to > the ground, or getting drowned by a break in the city reservoir that's > a couple hundred yards up the hill (but at least I needn't worry about Invest in sponges. Lots of them. :) -- Decibel!, aka Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgp1myEeooOQW.pgp Description: PGP signature
Re: [PERFORM] DRBD and Postgres: how to improve the perfomance?
On Tue, Sep 11, 2007 at 04:57:24PM +0200, Steinar H. Gunderson wrote: > On Tue, Sep 11, 2007 at 04:47:40PM +0200, Maila Fatticcioni wrote: > > The servers are cross connected with a common 100 Mbit/sec Ethernet so I > > think they have a bandwidth around 80 Mbit/sec (even if I haven't yet > > done any test on it). A rate of 70Mb seems reasonable to me. > > Umm, seriously? Unless that was a typo, you should consider very seriously to > go to gigabit; it's cheap these days, and should provide you with a very > decent speed boost if the network bandwidth is the bottleneck. Actually, in this case, I suspect that latency will be far more critical than overall bandwidth. I don't know if it's inherent to Gig-E, but my limited experience has been that Gig-E has higher latency than 100mb. -- Decibel!, aka Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgpHMx4JicFgQ.pgp Description: PGP signature
Re: [PERFORM] random_page_costs - are defaults of 4.0 realistic for SCSI RAID 1
On Mon, Sep 10, 2007 at 06:22:06PM -0400, Mark Mielke wrote: > In my case, I set effective_cache_size to 25% of the RAM available to > the system (256 Mbytes), for a database that was about 100 Mbytes or > less. I found performance to increase when reducing random_page_cost > from 4.0 to 3.0. Just for the record, effective_cache_size of 25% is *way* too low in most cases, though if you only have 1GB setting it to 500MB probably isn't too far off. Generally, I'll set this to however much memory is in the server, minus 1G for the OS, unless there's less than 4G of total memory in which case I subtract less. -- Decibel!, aka Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgpbj9aYHBI3X.pgp Description: PGP signature
Re: [PERFORM] random_page_costs - are defaults of 4.0 realistic for SCSI RAID 1
On Tue, Sep 11, 2007 at 02:49:28PM -0400, Jean-David Beyer wrote: > It is tricky for me to find a big enough file to test. I tried one of the dd if=/dev/zero of=bigfile bs=8192 count=100 -- Decibel!, aka Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgpFbvUNb2CWU.pgp Description: PGP signature
Re: [PERFORM] random_page_costs - are defaults of 4.0 realistic for SCSI RAID 1
Decibel! wrote: On Mon, Sep 10, 2007 at 06:22:06PM -0400, Mark Mielke wrote: In my case, I set effective_cache_size to 25% of the RAM available to the system (256 Mbytes), for a database that was about 100 Mbytes or less. I found performance to increase when reducing random_page_cost from 4.0 to 3.0. Just for the record, effective_cache_size of 25% is *way* too low in most cases, though if you only have 1GB setting it to 500MB probably isn't too far off. Generally, I'll set this to however much memory is in the server, minus 1G for the OS, unless there's less than 4G of total memory in which case I subtract less. Agree. My point was only that there are conflicting database requirements, and that one setting may not be valid for both. The default should be whatever is the most useful for the most number of people. People who fall into one of the two extremes should know enough to set the value based on actual performance measurements. Cheers, mark -- Mark Mielke <[EMAIL PROTECTED]>
Re: [PERFORM] [Again] Postgres performance problem
On Tue, Sep 11, 2007 at 09:49:37AM +0200, Ruben Rubio wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > [EMAIL PROTECTED] escribi?: > >> Last time I had this problem i solved it stopping website, restarting > >> database, vacuumm it, run again website. But I guess this is going to > >> happen again. > >> > >> I would like to detect and solve the problem. Any ideas to detect it? > > > > Do you have very long transactions? Maybe some client that is connected > > all the time that is idle in transaction? > > There should not be long transactions. I ll keep an eye on Idle transactions > > I m detecting it using: > > echo 'SELECT current_query FROM pg_stat_activity;' | > /usr/local/pgsql/bin/psql vacadb | grep IDLE | wc -l If you're using VACUUM FULL, you're doing something wrong. :) Run lazy vacuum frequently enough (better yet, autovacuum, but cut all of 8.1's autovac parameters in half), and make sure your FSM is big enough (periodic vacuumdb -av | tail is an easy way to check that). Try a REINDEX. VACUUM FULL is especially hard on the indexes, and it's easy for them to seriously bloat. -- Decibel!, aka Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgpL7e2h1Br2t.pgp Description: PGP signature
Re: [PERFORM] Barcelona vs Tigerton
On Tue, Sep 11, 2007 at 10:57:43AM +0300, Mindaugas wrote: > Hello, > > Now that both 4x4 out it's time for us to decide which one should be better > for our PostgreSQL and Oracle. And especially for Oracle we really need such > server to squeeze everything from Oracle licenses. Both of the databases > handle OLTP type of the load. You might take a look at replacing Oracle with EnterpriseDB... but I'm a bit biased. ;) > Since we plan to buy 4U HP DL580 or 585 and only very few of them so power > ratings are not very critical in this our case. > > First benchmarks (http://www.anandtech.com/IT/showdoc.aspx?i=3091) show > that Intel still has more raw CPU power but Barcelona scales much better and > also has better memory bandwidth which I believe is quite critical with 16 > cores and DB usage pattern. > On the other hand Intel's X7350 (2.93GHz) has almost 50% advantage in CPU > frequency against 2GHz Barcelona. Databases are all about bandwidth and latency. Compute horsepower almost never matters. The only reason I'd look at the clock rate is if it substantially affects memory IO capability; but from what I've seen, memory seems to be fairly independent of CPU frequency now-a-days, so I don't think there's a huge difference there. -- Decibel!, aka Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgp0JJsI29xNu.pgp Description: PGP signature
Re: [PERFORM] random_page_costs - are defaults of 4.0 realistic for SCSI RAID 1
"Decibel!" <[EMAIL PROTECTED]> writes: > On Tue, Sep 11, 2007 at 02:49:28PM -0400, Jean-David Beyer wrote: >> It is tricky for me to find a big enough file to test. I tried one of the > > dd if=/dev/zero of=bigfile bs=8192 count=100 On linux another useful trick is: echo 1 > /proc/sys/vm/drop_caches Also, it helps to run a "vmstat 1" in another window and watch the bi and bo columns. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] SAN vs Internal Disks
On Tue, Sep 11, 2007 at 05:09:00PM -0400, Michael Stone wrote: > On Tue, Sep 11, 2007 at 03:55:51PM -0500, Decibel! wrote: > >Also, to reply to someone else's email... there is one big reason to use > >a SAN over direct storage: you can do HA that results in 0 data loss. > >Good SANs are engineered to be highly redundant, with multiple > >controllers, PSUs, etc, so that the odds of losing the SAN itself are > >very, very low. The same isn't true with DAS. > > You can get DAS arrays with multiple controllers, PSUs, etc. DAS != > single disk. It's still in the same chassis, though, which means if you lose memory or mobo you're still screwed. In a SAN setup for redundancy, there's very little in the way of a single point of failure; generally only the backplane, and because there's very little that's on there it's extremely rare for one to fail. -- Decibel!, aka Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgpol3S1SLA0f.pgp Description: PGP signature
Re: [PERFORM] More Vacuum questions...
On Tue, Sep 11, 2007 at 10:24:58AM -0600, Kevin Kempter wrote: > I let the cron vacuum run for 14 days. in that 14 days the time it takes to > vacuum the table grew from 1.2hours directly after the rebuild to > 8hours > last nite. Sounds to me like daily isn't enough, and that your FSM is too small. -- Decibel!, aka Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgpCY6kMFsQLb.pgp Description: PGP signature
Re: [PERFORM] random_page_costs - are defaults of 4.0 realistic for SCSI RAID 1
On Wed, Sep 12, 2007 at 12:02:46AM +0100, Gregory Stark wrote:
> "Decibel!" <[EMAIL PROTECTED]> writes:
>
> > On Tue, Sep 11, 2007 at 02:49:28PM -0400, Jean-David Beyer wrote:
> >> It is tricky for me to find a big enough file to test. I tried one of the
> >
> > dd if=/dev/zero of=bigfile bs=8192 count=100
>
> On linux another useful trick is:
>
> echo 1 > /proc/sys/vm/drop_caches
The following C code should have similar effect...
/*
* $Id: clearmem.c,v 1.1 2003/06/29 20:41:33 decibel Exp $
*
* Utility to clear out a chunk of memory and zero it. Useful for flushing disk
buffers
*/
int main(int argc, char *argv[]) {
if (!calloc(atoi(argv[1]), 1024*1024)) { printf("Error allocating
memory.\n"); }
}
--
Decibel!, aka Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
pgpWoNVFef8hh.pgp
Description: PGP signature
Re: [PERFORM] SAN vs Internal Disks
On Tue, 11 Sep 2007, Decibel! wrote: On Tue, Sep 11, 2007 at 05:09:00PM -0400, Michael Stone wrote: On Tue, Sep 11, 2007 at 03:55:51PM -0500, Decibel! wrote: Also, to reply to someone else's email... there is one big reason to use a SAN over direct storage: you can do HA that results in 0 data loss. Good SANs are engineered to be highly redundant, with multiple controllers, PSUs, etc, so that the odds of losing the SAN itself are very, very low. The same isn't true with DAS. You can get DAS arrays with multiple controllers, PSUs, etc. DAS != single disk. It's still in the same chassis, though, which means if you lose memory or mobo you're still screwed. In a SAN setup for redundancy, there's very little in the way of a single point of failure; generally only the backplane, and because there's very little that's on there it's extremely rare for one to fail. not nessasarily. direct attached doesn't mean in the same chassis, external drive shelves attached via SCSI are still DAS you can even have DAS attached to a pair of machines, with the second box configured to mount the drives only if the first one dies. David Lang ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] random_page_costs - are defaults of 4.0 realistic for SCSI RAID 1
On Wed, 12 Sep 2007, Gregory Stark wrote: Also, it helps to run a "vmstat 1" in another window and watch the bi and bo columns. Recently on Linux systems I've been using dstat ( http://dag.wieers.com/home-made/dstat/ ) for live monitoring in this sort of situation. Once you get the command line parameters right, you can get data for each of the major disks on your system that keep the columns human readable (like switching from KB/s to MB/s as appropriate) as activity goes up and down combined with the standard vmstat data. I still use vmstat/iostat if I want to archive or parse the data, but if I'm watching it I always use dstat now. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] random_page_costs - are defaults of 4.0 realistic for SCSI RAID 1
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Greg Smith wrote: > On Wed, 12 Sep 2007, Gregory Stark wrote: > >> Also, it helps to run a "vmstat 1" in another window and watch the bi >> and bo >> columns. > > Recently on Linux systems I've been using dstat ( > http://dag.wieers.com/home-made/dstat/ ) for live monitoring in this > sort of situation. Once you get the command line parameters right, you > can get data for each of the major disks on your system that keep the > columns human readable (like switching from KB/s to MB/s as appropriate) > as activity goes up and down combined with the standard vmstat data. > > I still use vmstat/iostat if I want to archive or parse the data, but if > I'm watching it I always use dstat now. Thanks for the tip Greg... I hadn't heard of dstat. Sincerely, Joshua D. Drake > > -- > * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend > - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFG5zbRATb/zqfZUUQRAnz4AJwM1bGsVPdUZWy6ldqEq9l8SqRpJACcCfUc Joc8dLj12hISB5mQO6Tn+a8= =E5D2 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] DRBD and Postgres: how to improve the perfomance?
Decibel! wrote: > > Pretty much every surge supressor out there is a POS... 99.9% of them > just wire a varistor across the line; like a $0.02 part is going to stop > a 10,00+ amp discharge. > > The only use I have for those things is if they come with an equipment > guarantee, though I have to wonder how much those are still honored, > since as you mention it's very easy for equipment to be fried via other > means (ethernet, monitor, etc). My UPSs, from American Power Conversion, have one of those impressive guarantees. It specifies that all connections to my computer must be protected: power, modem, ethernet, etc. It further specifies that everything must be UL or CSA approved, and so on and so forth. Well, that is what I have. > >> net to eliminate any direct electrical connections between machines that >> are not on the same power circuit (the aforesaid burned motherboard >> taught me that particular lesson). And yet I still fear every time a >> thunderstorm passes over. > > Wired is safe as long as everything's on the same circuit. My house is > wired for ethernet with a single switch running what's going to every > room, but in each room I have a second switch on the same power as > whatever's in that room; so if there is a strike it's far more likely > that I'll lose switches and not hardware. My systems are all in the same room. The UPS for the main system has a single outlet on a circuit all its own all the way back to the power panel at the building entrance. The UPS for my other system also has a outlet on a circuit all its own all the way back to the power panel at the building entrance -- on the other side of my 240 volt service so they sorta-kinda balance out. The only other UPS is a little 620 VA one for the power to the Verizon FiOS leading into my house. That is fibre-optic all the way to the pole. I will probably get less lightning coming in that way than when I used to be on copper dial-up. ;-) > >> Then of course there are the *other* risks, such as the place burning to >> the ground, or getting drowned by a break in the city reservoir that's >> a couple hundred yards up the hill (but at least I needn't worry about > > Invest in sponges. Lots of them. :) -- .~. Jean-David Beyer Registered Linux User 85642. /V\ PGP-Key: 9A2FC99A Registered Machine 241939. /( )\ Shrewsbury, New Jerseyhttp://counter.li.org ^^-^^ 22:00:01 up 34 days, 1:22, 5 users, load average: 4.05, 4.22, 4.25 ---(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] SAN vs Internal Disks
Yeah, the DAS we are considering is Dell MD3000, it has redundant hot swappable raid controllers in active-active mode. Provision for hot spare hard-disk. And it can take upto 15 disks in 3U, you can attach two more MD1000 to it, giving a total of 45 disks in total. -- Harsh On 9/12/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > On Tue, 11 Sep 2007, Decibel! wrote: > > > On Tue, Sep 11, 2007 at 05:09:00PM -0400, Michael Stone wrote: > >> On Tue, Sep 11, 2007 at 03:55:51PM -0500, Decibel! wrote: > >>> Also, to reply to someone else's email... there is one big reason to > use > >>> a SAN over direct storage: you can do HA that results in 0 data loss. > >>> Good SANs are engineered to be highly redundant, with multiple > >>> controllers, PSUs, etc, so that the odds of losing the SAN itself are > >>> very, very low. The same isn't true with DAS. > >> > >> You can get DAS arrays with multiple controllers, PSUs, etc. DAS != > >> single disk. > > > > It's still in the same chassis, though, which means if you lose memory > > or mobo you're still screwed. In a SAN setup for redundancy, there's > > very little in the way of a single point of failure; generally only the > > backplane, and because there's very little that's on there it's > > extremely rare for one to fail. > > not nessasarily. direct attached doesn't mean in the same chassis, > external drive shelves attached via SCSI are still DAS > > you can even have DAS attached to a pair of machines, with the second box > configured to mount the drives only if the first one dies. > > David Lang > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster > -- Harsh Azad === [EMAIL PROTECTED]
Re: [PERFORM] SAN vs Internal Disks
[Decibel! - Tue at 06:07:44PM -0500] > It's still in the same chassis, though, which means if you lose memory > or mobo you're still screwed. In a SAN setup for redundancy, there's > very little in the way of a single point of failure; generally only the > backplane, and because there's very little that's on there it's > extremely rare for one to fail. Funny, the only time we lost a database server was due to a backplane failure ... ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
