Re: [PERFORM] Bitmap indexes etc.
On Mon, 26 Dec 2005, Tom Lane wrote: ...snip... Thanks, it's a very good explanation! -- Preserve wildlife -- pickle a squirrel today! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] What's the best hardver for PostgreSQL 8.1?
On Mon, Dec 26, 2005 at 12:32:19PM -0500, Alex Turner wrote: It's irrelavent what controller, you still have to actualy write the parity blocks, which slows down your write speed because you have to write n+n/2 blocks. instead of just n blocks making the system write 50% more data. RAID 5 must write 50% more data to disk therefore it will always be slower. At this point you've drifted into complete nonsense mode. On Mon, Dec 26, 2005 at 10:11:00AM -0800, David Lang wrote: what slows down raid 5 is that to modify a block you have to read blocks from all your drives to re-calculate the parity. this interleaving of reads and writes when all you are logicly doing is writes can really hurt. (this is why I asked the question that got us off on this tangent, when doing new writes to an array you don't have to read the blocks as they are blank, assuming your cacheing is enough so that you can write blocksize*n before the system starts actually writing the data) Correct; there's no reason for the controller to read anything back if your write will fill a complete stripe. That's why I said that there isn't a RAID 5 penalty assuming you've got a reasonably fast controller and you're doing large sequential writes (or have enough cache that random writes can be batched as large sequential writes). On Mon, Dec 26, 2005 at 06:04:40PM -0500, Alex Turner wrote: Yes, but those blocks in RAID 10 are largely irrelevant as they are to independant disks. In RAID 5 you have to write parity to an 'active' drive that is part of the stripe. Once again, this doesn't make any sense. Can you explain which parts of a RAID 10 array are inactive? I agree totally that the read+parity-calc+write in the worst case is totaly bad, which is why I alway recommend people should _never ever_ use RAID 5. In this day and age of large capacity chassis, and large capacity SATA drives, RAID 5 is totally inapropriate IMHO for _any_ application least of all databases. So I've got a 14 drive chassis full of 300G SATA disks and need at least 3.5TB of data storage. In your mind the only possible solution is to buy another 14 drive chassis? Must be nice to never have a budget. Must be a hard sell if you've bought decent enough hardware that your benchmarks can't demonstrate a difference between a RAID 5 and a RAID 10 configuration on that chassis except in degraded mode (and the customer doesn't want to pay double for degraded mode performance). In reality I have yet to benchmark a system where RAID 5 on the same number of drives with 8 drives or less in a single array beat a RAID 10 with the same number of drives. Well, those are frankly little arrays, probably on lousy controllers... Mike Stone ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PERFORM] Performance problems with 8.1.1 compared to 7.4.7
Hello, we have a PostgreSQL for datawarehousing. As we heard of so many enhancements for 8.0 and 8.1 versions we dicided to upgrade from 7.4 to 8.1. I must say that the COPY FROM processes are much faster now from 27 to 17 minutes. Some queries where slower, but the performance problems were solved by increasing work_mem to 8192. However, now we have a query that is much slower with 8.1 compared to 7.4. The query lasts 7minutes (all the times we try) with 8.1, keeping CPU usage at 93~97% while it lasts 25 seconds in 7.4 the first time going down to 4 seconds the following tries. We're not experts at all but we can't see anything strange with the differences of EXPLAIN in the queries. Below I paste the query and the EXPLAIN output. Does somebody have a clue of what could be the cause of this big difference in performance? Many thanks in advance. SELECT lpad(c.codigo,6,'0'), MIN(c.nombre), SUM( CASE WHEN ( res.hora_inicio = time '00:00' AND res.hora_inicio time '16:00' ) THEN (CASE WHEN res.importe_neto IS NOT NULL THEN res.importe_neto ELSE 0 END) ELSE 0 END ) AS p1, SUM( CASE WHEN ( res.hora_inicio = time '00:00' AND res.hora_inicio time '16:00' ) THEN (CASE WHEN res.cantidad_servida IS NOT NULL THEN res.cantidad_servida ELSE 0 END) ELSE 0 END ) AS p2, SUM( CASE WHEN ( res.hora_inicio = time '16:00' AND res.hora_inicio time '23:59' ) THEN (CASE WHEN res.importe_neto IS NOT NULL THEN res.importe_neto ELSE 0 END) ELSE 0 END ) AS p3 SUM( CASE WHEN ( res.hora_inicio = time '16:00' AND res.hora_inicio time '23:59' ) THEN (CASE WHEN res.cantidad_servida IS NOT NULL THEN res.cantidad_servida ELSE 0 END) ELSE 0 END ) AS p4 SUM(CASE WHEN res.importe_neto IS NOT NULL THEN res.importe_neto ELSE 0 END) AS total, SUM(CASE WHEN res.cantidad_servida IS NOT NULL THEN res.cantidad_servida ELSE 0 END) AS total_lineas FROM clientes c LEFT JOIN ( SELECT la.cliente as cliente, es.hora_inicio as hora_inicio, la.albaran as albaran, la.cantidad_servida as cantidad_servida, la.importe_neto as importe_neto FROM lineas_albaranes la LEFT JOIN escaner es ON la.albaran = es.albaran WHERE la.fecha_albaran = '20-12-2005' AND la.empresa = 1 AND la.indicador_factura = 'F' ) AS res ON c.codigo = res.cliente, provincias p WHERE p.codigo = c.provincia AND p.nombre='NAME' AND EXISTS(SELECT 1 FROM lineas_albaranes la WHERE la.cliente=c.codigo AND la.fecha_albaran (date '20-12-2005' - interval '2 month') AND la.fecha_albaran = '20-12-2005' AND la.empresa=1 AND la.indicador_factura='F') GROUP BY c.codigo ORDER BY nom; PostgreSQL 8.1.1: QUERY PLAN -- Sort (cost=333769.99..333769.99 rows=2 width=61) Sort Key: min((c.nombre)::text) - GroupAggregate (cost=37317.41..333769.98 rows=2 width=61) - Nested Loop (cost=37317.41..333769.83 rows=2 width=61) Join Filter: (inner.codigo = outer.provincia) - Merge Left Join (cost=37315.27..333758.58 rows=405 width=65) Merge Cond: (outer.codigo = inner.cliente) - Index Scan using clientes_pkey on clientes c (cost=0.00..296442.28 rows=405 width=40) Filter: (subplan) SubPlan - Bitmap Heap Scan on lineas_albaranes la (cost=138.99..365.53 rows=1 width=0) Recheck Cond: ((cliente = $0) AND ((indicador_factura)::text = 'F'::text)) Filter: ((fecha_albaran '2005-10-20 00:00:00'::timestamp without time zone) AND (fecha_albaran = '2005-12-20'::date)AND (empresa = 1)) - BitmapAnd (cost=138.99..138.99 rows=57 width=0) - Bitmap Index Scan on lineas_albaranes_cliente_idx (cost=0.00..65.87 rows=11392 width=0) Index Cond: (cliente = $0) - Bitmap Index Scan on lineas_albaranes_indicador_factura_idx (cost=0.00..72.87 rows=11392 width=0) Index Cond: ((indicador_factura)::text = 'F'::text) -
Re: [PERFORM] What's the best hardver for PostgreSQL 8.1?
At 08:35 AM 12/27/2005, Michael Stone wrote: On Mon, Dec 26, 2005 at 10:11:00AM -0800, David Lang wrote: what slows down raid 5 is that to modify a block you have to read blocks from all your drives to re-calculate the parity. this interleaving of reads and writes when all you are logicly doing is writes can really hurt. (this is why I asked the question that got us off on this tangent, when doing new writes to an array you don't have to read the blocks as they are blank, assuming your cacheing is enough so that you can write blocksize*n before the system starts actually writing the data) Correct; there's no reason for the controller to read anything back if your write will fill a complete stripe. That's why I said that there isn't a RAID 5 penalty assuming you've got a reasonably fast controller and you're doing large sequential writes (or have enough cache that random writes can be batched as large sequential writes). Sorry. A decade+ RWE in production with RAID 5 using controllers as bad as Adaptec and as good as Mylex, Chaparral, LSI Logic (including their Engino stuff), and Xyratex under 5 different OS's (Sun, Linux, M$, DEC, HP) on each of Oracle, SQL Server, DB2, mySQL, and pg shows that RAID 5 writes are slower than RAID 5 reads With the one notable exception of the Mylex controller that was so good IBM bought Mylex to put them out of business. Enough IO load, random or sequential, will cause the effect no matter how much cache you have or how fast the controller is. The even bigger problem that everyone is ignoring here is that large RAID 5's spend increasingly larger percentages of their time with 1 failed HD in them. The math of having that many HDs operating simultaneously 24x7 makes it inevitable. This means you are operating in degraded mode an increasingly larger percentage of the time under exactly the circumstance you least want to be. In addition, you are =one= HD failure from data loss on that array an increasingly larger percentage of the time under exactly the least circumstances you want to be. RAID 5 is not a silver bullet. On Mon, Dec 26, 2005 at 06:04:40PM -0500, Alex Turner wrote: Yes, but those blocks in RAID 10 are largely irrelevant as they are to independant disks. In RAID 5 you have to write parity to an 'active' drive that is part of the stripe. Once again, this doesn't make any sense. Can you explain which parts of a RAID 10 array are inactive? I agree totally that the read+parity-calc+write in the worst case is totaly bad, which is why I alway recommend people should _never ever_ use RAID 5. In this day and age of large capacity chassis, and large capacity SATA drives, RAID 5 is totally inapropriate IMHO for _any_ application least of all databases. I vote with Michael here. This is an extreme position to take that can't be followed under many circumstances ITRW. So I've got a 14 drive chassis full of 300G SATA disks and need at least 3.5TB of data storage. In your mind the only possible solution is to buy another 14 drive chassis? Must be nice to never have a budget. I think you mean an infinite budget. That's even assuming it's possible to get the HD's you need. I've had arrays that used all the space I could give them in 160 HD cabinets. Two 160 HD cabinets was neither within the budget nor going to perform well. I =had= to use RAID 5. RAID 10 was just not usage efficient enough. Must be a hard sell if you've bought decent enough hardware that your benchmarks can't demonstrate a difference between a RAID 5 and a RAID 10 configuration on that chassis except in degraded mode (and the customer doesn't want to pay double for degraded mode performance) I have =never= had this situation. RAID 10 latency is better than RAID 5 latency. RAID 10 write speed under heavy enough load, of any type, is faster than RAID 5 write speed under the same circumstances. RAID 10 robustness is better as well. Problem is that sometimes budget limits or number of HDs needed limits mean you can't use RAID 10. In reality I have yet to benchmark a system where RAID 5 on the same number of drives with 8 drives or less in a single array beat a RAID 10 with the same number of drives. Well, those are frankly little arrays, probably on lousy controllers... Nah. Regardless of controller I can take any RAID 5 and any RAID 10 built on the same HW under the same OS running the same DBMS and =guarantee= there is an IO load above which it can be shown that the RAID 10 will do writes faster than the RAID 5. The only exception in my career thus far has been the aforementioned Mylex controller. OTOH, sometimes you have no choice but to take the hit and use RAID 5. cheers, Ron ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Performance problems with 8.1.1 compared to 7.4.7
On Tue, Dec 27, 2005 at 05:09:28PM +0100, Albert Cervera Areny wrote: However, now we have a query that is much slower with 8.1 compared to 7.4. The query lasts 7minutes (all the times we try) with 8.1, keeping CPU usage at 93~97% while it lasts 25 seconds in 7.4 the first time going down to 4 seconds the following tries. We're not experts at all but we can't see anything strange with the differences of EXPLAIN in the queries. Below I paste the query and the EXPLAIN output. Could you post the EXPLAIN ANALYZE output of the query on both systems? That'll show how accurate the planner's estimates are. Have you run ANALYZE (or VACUUM ANALYZE) on the tables in both versions? The row count estimates in the 8.1.1 query differ from those in the 7.4.7 query. Are the two versions using the same data set? Are your configuration settings the same in both versions? You mentioned increasing work_mem, but what about others like effective_cache_size, random_page_cost, and shared_buffers? -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] What's the best hardver for PostgreSQL 8.1?
Historically, I have heard that RAID5 is only faster than RAID10 if there are six or more drives. --- Ron wrote: At 08:35 AM 12/27/2005, Michael Stone wrote: On Mon, Dec 26, 2005 at 10:11:00AM -0800, David Lang wrote: what slows down raid 5 is that to modify a block you have to read blocks from all your drives to re-calculate the parity. this interleaving of reads and writes when all you are logicly doing is writes can really hurt. (this is why I asked the question that got us off on this tangent, when doing new writes to an array you don't have to read the blocks as they are blank, assuming your cacheing is enough so that you can write blocksize*n before the system starts actually writing the data) Correct; there's no reason for the controller to read anything back if your write will fill a complete stripe. That's why I said that there isn't a RAID 5 penalty assuming you've got a reasonably fast controller and you're doing large sequential writes (or have enough cache that random writes can be batched as large sequential writes). Sorry. A decade+ RWE in production with RAID 5 using controllers as bad as Adaptec and as good as Mylex, Chaparral, LSI Logic (including their Engino stuff), and Xyratex under 5 different OS's (Sun, Linux, M$, DEC, HP) on each of Oracle, SQL Server, DB2, mySQL, and pg shows that RAID 5 writes are slower than RAID 5 reads With the one notable exception of the Mylex controller that was so good IBM bought Mylex to put them out of business. Enough IO load, random or sequential, will cause the effect no matter how much cache you have or how fast the controller is. The even bigger problem that everyone is ignoring here is that large RAID 5's spend increasingly larger percentages of their time with 1 failed HD in them. The math of having that many HDs operating simultaneously 24x7 makes it inevitable. This means you are operating in degraded mode an increasingly larger percentage of the time under exactly the circumstance you least want to be. In addition, you are =one= HD failure from data loss on that array an increasingly larger percentage of the time under exactly the least circumstances you want to be. RAID 5 is not a silver bullet. On Mon, Dec 26, 2005 at 06:04:40PM -0500, Alex Turner wrote: Yes, but those blocks in RAID 10 are largely irrelevant as they are to independant disks. In RAID 5 you have to write parity to an 'active' drive that is part of the stripe. Once again, this doesn't make any sense. Can you explain which parts of a RAID 10 array are inactive? I agree totally that the read+parity-calc+write in the worst case is totaly bad, which is why I alway recommend people should _never ever_ use RAID 5. In this day and age of large capacity chassis, and large capacity SATA drives, RAID 5 is totally inapropriate IMHO for _any_ application least of all databases. I vote with Michael here. This is an extreme position to take that can't be followed under many circumstances ITRW. So I've got a 14 drive chassis full of 300G SATA disks and need at least 3.5TB of data storage. In your mind the only possible solution is to buy another 14 drive chassis? Must be nice to never have a budget. I think you mean an infinite budget. That's even assuming it's possible to get the HD's you need. I've had arrays that used all the space I could give them in 160 HD cabinets. Two 160 HD cabinets was neither within the budget nor going to perform well. I =had= to use RAID 5. RAID 10 was just not usage efficient enough. Must be a hard sell if you've bought decent enough hardware that your benchmarks can't demonstrate a difference between a RAID 5 and a RAID 10 configuration on that chassis except in degraded mode (and the customer doesn't want to pay double for degraded mode performance) I have =never= had this situation. RAID 10 latency is better than RAID 5 latency. RAID 10 write speed under heavy enough load, of any type, is faster than RAID 5 write speed under the same circumstances. RAID 10 robustness is better as well. Problem is that sometimes budget limits or number of HDs needed limits mean you can't use RAID 10. In reality I have yet to benchmark a system where RAID 5 on the same number of drives with 8 drives or less in a single array beat a RAID 10 with the same number of drives. Well, those are frankly little arrays, probably on lousy controllers... Nah. Regardless of controller I can take any RAID 5 and any RAID 10 built on the same HW under the same OS running the same DBMS and =guarantee= there is an IO load above which it can be shown that the RAID 10 will do writes faster than the RAID 5. The only exception in my career thus far has been the aforementioned Mylex controller. OTOH, sometimes you have no choice but to take
Re: [PERFORM] Performance problems with 8.1.1 compared to 7.4.7
A Dimarts 27 Desembre 2005 18:13, Michael Fuhr va escriure: On Tue, Dec 27, 2005 at 05:09:28PM +0100, Albert Cervera Areny wrote: However, now we have a query that is much slower with 8.1 compared to 7.4. The query lasts 7minutes (all the times we try) with 8.1, keeping CPU usage at 93~97% while it lasts 25 seconds in 7.4 the first time going down to 4 seconds the following tries. We're not experts at all but we can't see anything strange with the differences of EXPLAIN in the queries. Below I paste the query and the EXPLAIN output. Could you post the EXPLAIN ANALYZE output of the query on both systems? That'll show how accurate the planner's estimates are. Have you run ANALYZE (or VACUUM ANALYZE) on the tables in both versions? The row count estimates in the 8.1.1 query differ from those in the 7.4.7 query. Are the two versions using the same data set? Are your configuration settings the same in both versions? You mentioned increasing work_mem, but what about others like effective_cache_size, random_page_cost, and shared_buffers? Hey, thank you for your fast response, I found what the problem was. I thought the settings were the same but work_mem was still higher in 7.4, 30Mb, so I increased 8.1 to 30Mb and it worked faster, down to 17 seconds the first time, 2.5 seconds for the others. Are there any rules of thumb to let a begginer give reasonable values to these parameters? Not only work_mem, but also random_page_cost, and so on. Are there any tests one can run to determine good values? Thanks a lot! -- Albert Cervera Areny Dept. Informàtica Sedifa, S.L. Av. Can Bordoll, 149 08202 - Sabadell (Barcelona) Tel. 93 715 51 11 Fax. 93 715 51 12 AVISO LEGAL La presente comunicación y sus anexos tiene como destinatario la persona a la que va dirigida, por lo que si usted lo recibe por error debe notificarlo al remitente y eliminarlo de su sistema, no pudiendo utilizarlo, total o parcialmente, para ningún fin. Su contenido puede tener información confidencial o protegida legalmente y únicamente expresa la opinión del remitente. El uso del correo electrónico vía Internet no permite asegurarni la confidencialidad de los mensajes nisucorrecta recepción. Enel caso de que el destinatario no consintiera la utilización del correo electrónico, deberá ponerlo en nuestro conocimiento inmediatamente. ... DISCLAIMER . This message and its attachments are intended exclusively for the named addressee. If you receive this message in error, please immediately delete it from your system and notify the sender. You may not use this message or any part of it for any purpose. The message may contain information that is confidential or protected by law, and any opinions expressed are those of the individualsender. Internet e-mail guarantees neither the confidentiality nor the proper receipt of the message sent. If the addressee of this message does not consent to the use of internete-mail,pleaseinform usinmmediately. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Performance problems with 8.1.1 compared to 7.4.7
On Tue, 27 Dec 2005 19:02:17 +0100 Albert Cervera Areny [EMAIL PROTECTED] wrote: Are there any rules of thumb to let a begginer give reasonable values to these parameters? Not only work_mem, but also random_page_cost, and so on. Are there any tests one can run to determine good values? Hi Albert, There are several online sites that have information related to tuning parameters. Here is a list of a few of them: http://revsys.com/writings/postgresql-performance.html http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html http://www.powerpostgresql.com/Docs http://www.powerpostgresql.com/PerfList Hope these help! - Frank Wiles [EMAIL PROTECTED] http://www.wiles.org - ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] What's the best hardver for PostgreSQL 8.1?
On Tue, Dec 27, 2005 at 11:50:16AM -0500, Ron wrote: Sorry. A decade+ RWE in production with RAID 5 using controllers as bad as Adaptec and as good as Mylex, Chaparral, LSI Logic (including their Engino stuff), and Xyratex under 5 different OS's (Sun, Linux, M$, DEC, HP) on each of Oracle, SQL Server, DB2, mySQL, and pg shows that RAID 5 writes are slower than RAID 5 reads What does that have to do with anything? That wasn't the question... RAID 5 is not a silver bullet. Who said it was? Nothing is, not even RAID 10. The appropriate thing to do is to make decisions based on requirements, not to make sweeping statements that eliminate entire categories of solutions based on hand waving. Mike Stone ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] What's the best hardver for PostgreSQL 8.1?
Bruce, On 12/27/05 9:51 AM, Bruce Momjian pgman@candle.pha.pa.us wrote: Historically, I have heard that RAID5 is only faster than RAID10 if there are six or more drives. I think the real question here is faster for what? Also, just like the optimizer tunables for cpu/disk/memory speed relationships, the standing guidance for RAID has become outdated. Couple that with the predominance of really bad hardware RAID controllers and people not testing them or reporting their performance (HP, Adaptec, LSI, Dell) and we've got a mess. All we can really do is report success with various point solutions. RAID5 and RAID50 work fine for our customers who do OLAP type applications which are read-mostly. However, it only works well on good hardware and software, which at this time include the HW RAID controllers from 3Ware and reputedly Areca and SW using Linux SW RAID. I've heard that the external storage RAID controllers from EMC work well, and I'd suspect there are others, but none of the host-based SCSI HW RAID controllers I've tested work well on Linux. I say Linux, because I'm pretty sure that the HP smartarray controllers work well on Windows, but the Linux driver is so bad I'd say it doesn't work at all. WRT RAID10, it seems like throwing double the number of disks at the problems is something to be avoided if possible, though the random write performance may be important for OLTP. I think this assertion should be retested however in light of the increased speed of checksumming hardware and / or CPUs and faster, more effective drive electronics (write combining, write cache, etc). - Luke ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] What's the best hardver for PostgreSQL 8.1?
Bruce, On 12/27/05 9:51 AM, Bruce Momjian pgman@candle.pha.pa.us wrote: Historically, I have heard that RAID5 is only faster than RAID10 if there are six or more drives. Speaking of testing / proof, check this site out: http://www.wlug.org.nz/HarddiskBenchmarks I really like the idea - post your bonnie++ results so people can learn from your configurations. We've built a performance reporting site, but we can't seem to get it into shape for release. I'd really like to light a performance leaderboard / experiences site up somewhere... - Luke ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] What's the best hardver for PostgreSQL 8.1?
At 02:05 PM 12/27/2005, Michael Stone wrote: On Tue, Dec 27, 2005 at 11:50:16AM -0500, Ron wrote: Sorry. A decade+ RWE in production with RAID 5 using controllers as bad as Adaptec and as good as Mylex, Chaparral, LSI Logic (including their Engino stuff), and Xyratex under 5 different OS's (Sun, Linux, M$, DEC, HP) on each of Oracle, SQL Server, DB2, mySQL, and pg shows that RAID 5 writes are slower than RAID 5 reads What does that have to do with anything? That wasn't the question... Your quoted position is there isn't a 'RAID 5 penalty' assuming you've got a reasonably fast controller and you're doing large sequential writes (or have enough cache that random writes can be batched as large sequential writes). My experience across a wide range of HW, OSs, DBMS, and applications says you are wrong. Given enough IO, RAID 5 takes a bigger performance hit for writes than RAID 10 does. Enough IO, sequential or otherwise, will result in a situation where a RAID 10 array using the same number of HDs (and therefore of ~1/2 the usable capacity) will have better write performance than the equivalent RAID 5 built using the same number of HDs. There is a 'RAID 5 write penalty'. Said RAID 10 array will also be more robust than a RAID 5 built using the same number of HDs. OTOH, that does not make RAID 5 bad. Nor are statements like Never use RAID 5! realistic or reasonable. Also, performance is not the only or even most important reason for choosing RAID 10 or RAID 50 over RAID 5. Robustness considerations can be more important than performance ones. cheers, Ron ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] What's the best hardver for PostgreSQL 8.1?
On Tue, Dec 27, 2005 at 02:57:13PM -0500, Ron wrote: Your quoted position is there isn't a 'RAID 5 penalty' assuming you've got a reasonably fast controller and you're doing large sequential writes (or have enough cache that random writes can be batched as large sequential writes). And you said that RAID 5 writes are slower than reads. That's a completely different statement. The traditional meaning of RAID 5 penalty is the cost of reading a stripe to calculate parity if only a small part of the stripe changes. It has a special name because it can result in a condition that the performance is catastrophically worse than an optimal workload, or even the single-disk non-RAID case. It's still an issue, but might not be relevant for a particular workload. (Hence the recommendation to benchmark.) My experience across a wide range of HW, OSs, DBMS, and applications says you are wrong. Given enough IO, RAID 5 takes a bigger performance hit for writes than RAID 10 does. I don't understand why you keep using the pejorative term performance hit. Try describing the performance characteristics instead. Also, claims about performance claims based on experience are fairly useless. Either you have data to provide (in which case claiming vast experience is unnecessary) or you don't. Said RAID 10 array will also be more robust than a RAID 5 built using the same number of HDs. And a RAID 6 will be more robust than either. Basing reliability on hopefully you wont have both disks in a mirror fail is just silly. Either you need double disk failure protection or you don't. Mike Stone ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] What's the best hardver for PostgreSQL 8.1?
At 04:15 PM 12/27/2005, Michael Stone wrote: I don't understand why you keep using the pejorative term performance hit. Try describing the performance characteristics instead. pe·jor·a·tive( P ) Pronunciation Key (p-jôr-tv, -jr-, pj-rtv, pj-) adj. Tending to make or become worse. Disparaging; belittling. RAID 5 write performance is significantly enough less than RAID 5 read performance as to be a matter of professional note and concern. That's not disparaging or belittling nor is it tending to make or become worse. It's measurable fact that has an adverse impact on capacity planning, budgeting, HW deployment, etc. If you consider calling a provable decrease in performance while doing a certain task that has such effects a hit or bad pejorative, you are using a definition for the word that is different than the standard one. Also, claims about performance claims based on experience are fairly useless. Either you have data to provide (in which case claiming vast experience is unnecessary) or you don't. My experience _is_ the data provided. Isn't it convenient for you that I don't have the records for every job I've done in 20 years, nor do I necessarily have the right to release some specifics for some of what I do have. I've said what I can as a service to the community. Including to you. Your reaction implies that I and others with perhaps equally or more valuable experience to share shouldn't bother. One of the major differences between Man and Beast is that Man learns from others experience. It's also impressive that you evidently seem to be implying that you do such records for your own job experience _and_ that you have the legal right to publish them. In which case, please feel free to impress me further by doing so. Said RAID 10 array will also be more robust than a RAID 5 built using the same number of HDs. And a RAID 6 will be more robust than either. Basing reliability on hopefully you wont have both disks in a mirror fail is just silly. Either you need double disk failure protection or you don't. That statement is incorrect and ignores both probability and real world statistical failure patterns. The odds of a RAID 10 array of n HDs suffering a failure that loses data are less than the odds of it happening in a RAID 6 array of n HDs. You are correct that RAID 6 is more robust than RAID 5. cheers, Ron ---(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
