Thread: compact flash disks?
I see that one can now get compact flash to SATA connectors. If I were to use a filesystem with noatime etc and little non-sql traffic, does the physical update pattern tend to have hot sectors that will tend to wear out CF? I'm wondering about a RAID5 with data on CF drives and RAID1 for teh WAL on a fast SATA or SAS drive pair. I'm thhinking that this would tend to have good performance because the seek time for the data is very low, even if the actual write speed can be slower than state of the art. 2GB CF isn't so pricey any more. Just wondering. James -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.446 / Virus Database: 268.18.7/711 - Release Date: 05/03/2007 09:41
On 3/7/07, James Mansion <james@mansionfamily.plus.com> wrote: > I see that one can now get compact flash to SATA connectors. > > If I were to use a filesystem with noatime etc and little non-sql traffic, > does the physical update pattern tend to have hot sectors that will tend to > wear out CF? > > I'm wondering about a RAID5 with data on CF drives and RAID1 for teh WAL on > a fast SATA or SAS drive pair. I'm thhinking that this would tend to have > good performance because the seek time for the data is very low, even if the > actual write speed can be slower than state of the art. 2GB CF isn't so > pricey any more. > > Just wondering. me too. I think if you were going to do this I would configure as raid 0. Sequential performance might be a problem, and traditional hard drive failure is not. I think some of the better flash drives spread out the writes so that life is maximized. It's still probably cheaper buying a better motherboard and stuffing more memory in it, and a good raid controller. merlin
* James Mansion: > If I were to use a filesystem with noatime etc and little non-sql traffic, > does the physical update pattern tend to have hot sectors that will tend to > wear out CF? Thanks to the FAT file system and its update pattern, most (if not all) CF disks implement wear leveling nowadays. I wouldn't worry about the issue, unless your write rates are pretty high. -- Florian Weimer <fweimer@bfk.de> BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99
At 05:18 PM 3/6/2007, James Mansion wrote: >I see that one can now get compact flash to SATA connectors. > >If I were to use a filesystem with noatime etc and little non-sql traffic, >does the physical update pattern tend to have hot sectors that will tend to >wear out CF? Most flash RAMs have drivers that make sure the pattern of writes over time is uniform across the entire device. >I'm wondering about a RAID5 with data on CF drives and RAID1 for the WAL on >a fast SATA or SAS drive pair. I'm thinking that this would tend to have >good performance because the seek time for the data is very low, even if the >actual write speed can be slower than state of the art. WARNING: modern TOtL flash RAMs are only good for ~1.2M writes per memory cell. and that's the =good= ones. Using flash RAM for write heavy applications like OLTP, or for WAL, etc can be very dangerous Flash write speeds also stink; being ~1/2 flash's already low read speed. Much better to use flash RAM for read heavy applications. Even there you have to be careful that seek performance, not throughput, is what is gating your day to day performance with those tables. Got tables or indexes that are a= too big to fit in RAM and b= are write few, read many times and c= whose pattern of access is large enough that it does not cache well? =Those= are what should be put into flash RAMs Side Note: In the long run, we are going to have to seriously rethink pg's use of WAL as the way we implement MVCC as it becomes more and more of a performance bottleneck. We have WAL because Stonebreaker made an assumption about the future dominance of optical media that has turned out to be false. ...and it's been one of pg's big issues every since. > 2GB CF isn't so >pricey any more. Heck =16= GB Flash only costs ~$300 US and 128GB SSDs based on flash RAM are due out this year. Cheers, Ron
> > Much better to use flash RAM for read heavy applications. > Even there you have to be careful that seek performance, not > throughput, is what is gating your day to day performance with those > tables. Isn't precisely there where Flash disks would have *the* big advantage?? I mean, access time is severely held down by the *mechanical* movement of the heads to the right cylinder on the disk --- that's a brutally large amount of time compared to anything else happening on the computer (well, floppy disks aside, and things like trying-to-break-128-bit-encryption aside :-)). Or are these Flash disks so slow that they compare to the HD's latency figures? Carlos --
> Or are these Flash disks so slow that they compare to the HD's latency > figures? On sequential read speed HDs outperform flash disks... only on random access the flash disks are better. So if your application is a DW one, you're very likely better off using HDs. Cheers, Csaba.
Le mardi 6 mars 2007 23:18, James Mansion a écrit : > I see that one can now get compact flash to SATA connectors. I can suggest you to have a look at Gigabyte i-ram . We use it on a website with higth traffic with lot of succes. Unfortunely, I can not provide any benchmark... > > If I were to use a filesystem with noatime etc and little non-sql traffic, > does the physical update pattern tend to have hot sectors that will tend to > wear out CF? > > I'm wondering about a RAID5 with data on CF drives and RAID1 for teh WAL on > a fast SATA or SAS drive pair. I'm thhinking that this would tend to have > good performance because the seek time for the data is very low, even if > the actual write speed can be slower than state of the art. 2GB CF isn't > so pricey any more. > > Just wondering. > > James > > -- > No virus found in this outgoing message. > Checked by AVG Free Edition. > Version: 7.5.446 / Virus Database: 268.18.7/711 - Release Date: 05/03/2007 > 09:41 > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
>WARNING: modern TOtL flash RAMs are only good for ~1.2M writes per >memory cell. and that's the =good= ones. >Using flash RAM for write heavy applications like OLTP, or for WAL, >etc can be very dangerous Well, that's why I suggested that the WAL would stream to a hard disk array, where the large IO sequential write speed will be helpful. Whether OLTP is a problem will presumably depend on the freqency of updates and vacuum to each physical cluster of rows in a disk block. Most rows in a trading application will have quite a long lifetime, and be updated relatively few times (even where we writing fixings info into trades). >Flash write speeds also stink; being ~1/2 flash's already low read speed. Sure - but it may still be an effective tradoff where the limiting factor would otherwise be seek time. >Much better to use flash RAM for read heavy applications. Why? I can get a 'PC' server with 128GB of RAM quite easily now, and that will mean I can cache most of not all hot data for any trading app I've worked on. Settled trades that matured in prior periods can be moved to tables on real disks - they are hardly ever accessed anyway. In the long run, we are going to have to seriously rethink pg's use of WAL as the way we implement MVCC as it becomes more and more of a performance bottleneck. We have WAL because Stonebreaker made an assumption about the future dominance of optical media that has turned out to be false. ...and it's been one of pg's big issues every since. >> 2GB CF isn't so >>pricey any more. >Heck =16= GB Flash only costs ~$300 US and 128GB SSDs based on flash >RAM are due out this year. Quite. Suppose I have a RAID with double redundancy, then I get enough capacity for quite a lot of raw data, and can swap a card out every weekend and let the RAID rebuild it in rotation to keep them within conservative wear limits. So long as the wear levelling works moderately well (and without needing FAT on the disk or whatever) then I should be fine. I think. Maybe. James -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.446 / Virus Database: 268.18.7/713 - Release Date: 07/03/2007 09:24
On Thu, Mar 08, 2007 at 06:24:35AM -0000, James Mansion wrote: > > In the long run, we are going to have to seriously rethink pg's use > of WAL as the way we implement MVCC as it becomes more and more of a > performance bottleneck. > We have WAL because Stonebreaker made an assumption about the future > dominance of optical media that has turned out to be false. > ...and it's been one of pg's big issues every since. Uh. pg didn't *have* WAL back when Stonebreaker was working on it. It was added in PostgreSQL 7.1, by Vadim. And it significantly increased performance at the time, since we no longer had to sync the datafiles after every transaction commit. (We also didn't have MVCC back in the Stonebreaker days - it was added in 6.5) That said, it's certainly possible that someone can find an even better way to do it :-) //Magnus
On 3/8/07, Magnus Hagander <magnus@hagander.net> wrote: > On Thu, Mar 08, 2007 at 06:24:35AM -0000, James Mansion wrote: > > > > In the long run, we are going to have to seriously rethink pg's use > > of WAL as the way we implement MVCC as it becomes more and more of a > > performance bottleneck. > > We have WAL because Stonebreaker made an assumption about the future > > dominance of optical media that has turned out to be false. > > ...and it's been one of pg's big issues every since. > > Uh. pg didn't *have* WAL back when Stonebreaker was working on it. It > was added in PostgreSQL 7.1, by Vadim. And it significantly increased > performance at the time, since we no longer had to sync the datafiles > after every transaction commit. > (We also didn't have MVCC back in the Stonebreaker days - it was added > in 6.5) Exactly, and WAL services other purposes than minimizing the penalty from writing to high latency media. WAL underlies PITR for example. Near-zero latency media is coming, eventually...and I don't think the issue is reliability (catastrophic failure is extremely unlikely) but cost. I think the poor write performance is not an issue because you can assemble drives in a giant raid 0 (or even 00 or 000) which will blow away disk based raid 10 systems at virtually everything. Solid State Drives consume less power (a big deal in server farms) and the storage density and life-span will continue to improve. I give it five years (maybe less) before you start to see SSD penetration in a big way. It will simply become cheaper to build a box with SSD than without since you won't need to buy as much RAM, draws less power, and is much more reliable. Disk drives will displace tape as low speed archival storage but will probably live on in super high storage enterprise environments. my 0.02$, as usual, merlin
At 09:11 AM 3/8/2007, Merlin Moncure wrote: >On 3/8/07, Magnus Hagander <magnus@hagander.net> wrote: >>On Thu, Mar 08, 2007 at 06:24:35AM -0000, James Mansion wrote: >> > >> > In the long run, we are going to have to seriously rethink pg's use >> > of WAL as the way we implement MVCC as it becomes more and more of a >> > performance bottleneck. >> > We have WAL because Stonebreaker made an assumption about the future >> > dominance of optical media that has turned out to be false. >> > ...and it's been one of pg's big issues every since. >> >>Uh. pg didn't *have* WAL back when Stonebreaker was working on it. It >>was added in PostgreSQL 7.1, by Vadim. And it significantly increased >>performance at the time, since we no longer had to sync the datafiles >>after every transaction commit. >>(We also didn't have MVCC back in the Stonebreaker days - it was added >>in 6.5) Huh. I have to go re-read my references. Seems I've misremembered history. Thanks for correcting my mistake. >Exactly, and WAL services other purposes than minimizing the penalty >from writing to high latency media. WAL underlies PITR for example. Never argued with any of this. >Near-zero latency media is coming, eventually...and I don't think the >issue is reliability (catastrophic failure is extremely unlikely) but >cost. I think the poor write performance is not an issue because you >can assemble drives in a giant raid 0 (or even 00 or 000) which will >blow away disk based raid 10 systems at virtually everything. Have you considered what the $cost$ of that much flash RAM would be? >Solid State Drives consume less power (a big deal in server farms) and >the storage density and life-span will continue to improve. I give it >five years (maybe less) before you start to see SSD penetration in a >big way. It will simply become cheaper to build a box with SSD than >without since you won't need to buy as much RAM, draws less power, and >is much more reliable. Don't bet on it. HDs 2x in density faster than RAM does, even flash RAM, and have a -much- lower cost per bit. ...and it's going to stay that way for the foreseeable future. ATM, I can buy a 500GB 7200 rpm SATA II HD w/ a 5 yr warranty for ~$170 US per HD. 1TB HDs of that class cost ~$350-$400 US per. (...and bear in mind the hybrid HDs coming out later this year that offer the best of both HD and flash technologies at very close to current HD costs per bit.) The 128GB flash RAM SSDs coming out later this year are going to cost 4x - 10x those HD prices... 4+ decades of history shows that initial acquisition cost is =by far= the primary deciding factor in IT spending. QED: SSDs are going to remain a niche product unless or until Something Drastic (tm) happens to the economics and storage requirements of IT. >Disk drives will displace tape as low speed archival storage but will >probably live on in super high storage enterprise environments. Again, don't bet on it. Tape 2x in density even faster than HD does and has an even lower cost per bit. >my 0.02$, as usual, >merlin
On 3/8/07, Ron <rjpeace@earthlink.net> wrote: > >Exactly, and WAL services other purposes than minimizing the penalty > >from writing to high latency media. WAL underlies PITR for example. > > Never argued with any of this. > > > >Near-zero latency media is coming, eventually...and I don't think the > >issue is reliability (catastrophic failure is extremely unlikely) but > >cost. I think the poor write performance is not an issue because you > >can assemble drives in a giant raid 0 (or even 00 or 000) which will > >blow away disk based raid 10 systems at virtually everything. > Have you considered what the $cost$ of that much flash RAM would be? > > > >Solid State Drives consume less power (a big deal in server farms) and > >the storage density and life-span will continue to improve. I give it > >five years (maybe less) before you start to see SSD penetration in a > >big way. It will simply become cheaper to build a box with SSD than > >without since you won't need to buy as much RAM, draws less power, and > >is much more reliable. > Don't bet on it. HDs 2x in density faster than RAM does, even flash > RAM, and have a -much- lower cost per bit. > ...and it's going to stay that way for the foreseeable future. yes, but SSD drives do not have to overtake hard disks to be cost competitive. one reason for this is that a SSD based system does not need nearly as much ram as a hard drive based system due to 99% reduction of cache miss penalty. Also, flash drives are falling faster in price than hard drives. Unless there is some new breakthrough in hard drive technology the price differential is going to narrow a bit. Ultimately (and this may be quite some way down the road, but maybe not), SSD will be cheaper than spinning disk due to lower cost of materials. > ATM, I can buy a 500GB 7200 rpm SATA II HD w/ a 5 yr warranty for > ~$170 US per HD. > 1TB HDs of that class cost ~$350-$400 US per. > (...and bear in mind the hybrid HDs coming out later this year that > offer the best of both HD and flash technologies at very close to > current HD costs per bit.) it is not fair to compare cost of 10ms latency hard drive to 10us flash drive unless you add in the cost of ram to get the systems to performance parity. > The 128GB flash RAM SSDs coming out later this year are going to cost > 4x - 10x those HD prices... > 4+ decades of history shows that initial acquisition cost is =by far= > the primary deciding factor in IT spending. > QED: SSDs are going to remain a niche product unless or until > Something Drastic (tm) happens to the economics and storage requirements of IT. Historically what you are saying is true but something drastic is indeed happening. There is currently a power crisis in many colocation facilities in the u.s. For example, for at least 1/3 of the cages in qwest at sterling, va are sitting empty because there are not enough circuits to give out (qwest is hitting environmental regs so that adding any additional generator power they would have to re-classify as a power generating facility). This basically puts their customers in a bidding war for available circuits and is making power draw a very significant factor of cost of rack rental. It is not unreasonable to factor in, say, one year of savings of monthly power bill at a facility. some facilities are so strapped they will only give out dc power circuits... > >Disk drives will displace tape as low speed archival storage but will > >probably live on in super high storage enterprise environments. > > Again, don't bet on it. Tape 2x in density even faster than HD does > and has an even lower cost per bit. this is a completely different argument but disk based backup systems are exploding as are the companies that design and implement them. tape has problems that go way beyond performance and price. whizzing robot arms that stick tapes in and out of things may have looked cool and futuristic in 1960 but its time to put this technology to bed for good. merlin
Hey there :)
I'm re-writing a summarization process that used to be very 'back and
forth' intensive (i.e. very chatty between my summarization software and
the DB). I'm trying to reduce that by practically reducing all this back
and forth to a single SQL query, but in order to support certain
complexities it looks to me like I'm going to have to write some postgres
C language functions.
This is something I'm actually familiar with and have done before, but let
me describe what I'm trying to do here so I can be sure that this is the
right thing to do, and to be sure I do it correctly and don't cause memory
leaks :)
---
I have two columns, one called "procedure_code" and the other called
"wrong_procedure_code" in my summary table. These are both of type
varchar(32) I believe or possibly text -- if it matters I can double check
but because all the involved columns are the same type and size it
shouldn't matter. :)
These are actually populated by the "procedure_code" and
"corrected_procedure_code" in the source table. The logic is, basically:
IF strlen(source.corrected_procedure_code)
THEN:
summary.procedure_code=source.corrected_procedure_code
summary.wrong_procedure_code=source.procedure_code
ELSE:
summary.procedure_code=source.procedure_code
summary.wrong_procedure_code=NULL
Simple, right? Making a C function to handle this should be no sweat --
I would basically split this logic into two separate functions, one to
populate summary.procedure_code and one to populate
summary.wrong_procedure_code, and it removes the need of having any sort
of back and forth between the program and DB... I can just do like:
update summary_table
set procedure_code=pickCorrect(source.procedure_code,
source.corrected_procedure_code),
wrong_procedure_code=pickWrong(source.procedure_code,
source.corrected_procedure_code),....
from source where summary_table.source_id=source.source_id;
Make sense? So question 1, is this the good way to do all this?
Question 2: Assuming it is the good way to do all this, would this
function be correct assuming I did all the other stuff right (like
PG_FUNCTION_INFO_V1, etc.):
Datum pickCorrect(PG_FUNCTION_ARGS){
text* procedure_code=PG_GETARG_TEXT_P(0);
text* corrected_code=PG_GETARG_TEXT_P(1);
if(VARSIZE(corrected_code)-VARHDRSZ){
PG_RETURN_TEXT_P(corrected_code);
}else{
PG_RETURN_TEXT_P(procedure_code);
}
}
Would that simply work because I'm not actually modifying the data, or
would I have to pmalloc a separate chunk of memory, copy the data, and
return the newly allocated memory because the memory allocated for the
args "goes away" or gets corrupted or something otherwise?
Thanks a lot for the info!
Steve
Steve wrote:
> IF strlen(source.corrected_procedure_code)
> THEN:
> summary.procedure_code=source.corrected_procedure_code
> summary.wrong_procedure_code=source.procedure_code
> ELSE:
> summary.procedure_code=source.procedure_code
> summary.wrong_procedure_code=NULL
Um, so you test if source.corrected_procedure_code is an empty string?
And if it is, summary.procedure_code is set to an empty string? But in
wrong_procedure_code, you use NULLs?
> Simple, right? Making a C function to handle this should be no sweat --
> I would basically split this logic into two separate functions, one to
> populate summary.procedure_code and one to populate
> summary.wrong_procedure_code, and it removes the need of having any sort
> of back and forth between the program and DB... I can just do like:
>
> update summary_table
> set procedure_code=pickCorrect(source.procedure_code,
> source.corrected_procedure_code),
> wrong_procedure_code=pickWrong(source.procedure_code,
> source.corrected_procedure_code),....
> from source where summary_table.source_id=source.source_id;
ISTM you could write this easily with a little bit of SQL, with no need
for C-functions (I haven't run this, probably full of typos..) :
update summary_table
set procedure_code = (CASE WHEN source.corrected_procedure_code = ''
THEN '' ELSE source.procedure_code END;),
wrong_procedure_code = (CASE WHEN source.corrected_procedure_code
= '' THEN source.procedure_code ELSE NULL END;)
from source where summary_table.source_id=source.source_id;
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
> Steve wrote:
>> IF strlen(source.corrected_procedure_code)
>> THEN:
>> summary.procedure_code=source.corrected_procedure_code
>> summary.wrong_procedure_code=source.procedure_code
>> ELSE:
>> summary.procedure_code=source.procedure_code
>> summary.wrong_procedure_code=NULL
>
> Um, so you test if source.corrected_procedure_code is an empty string? And if
> it is, summary.procedure_code is set to an empty string? But in
> wrong_procedure_code, you use NULLs?
Yeah; we could use empty strings if that make it easier for
whatever reason, but to our front end software NULL vs. empty string
doesn't actually matter and we never query based on these columns, they're
for display purposes only.
>> Simple, right? Making a C function to handle this should be no sweat -- I
>> would basically split this logic into two separate functions, one to
>> populate summary.procedure_code and one to populate
>> summary.wrong_procedure_code, and it removes the need of having any sort of
>> back and forth between the program and DB... I can just do like:
>>
>> update summary_table
>> set procedure_code=pickCorrect(source.procedure_code,
>> source.corrected_procedure_code),
>> wrong_procedure_code=pickWrong(source.procedure_code,
>> source.corrected_procedure_code),....
>> from source where summary_table.source_id=source.source_id;
>
> ISTM you could write this easily with a little bit of SQL, with no need for
> C-functions (I haven't run this, probably full of typos..) :
>
> update summary_table
> set procedure_code = (CASE WHEN source.corrected_procedure_code = '' THEN
> '' ELSE source.procedure_code END;),
> wrong_procedure_code = (CASE WHEN source.corrected_procedure_code = ''
> THEN source.procedure_code ELSE NULL END;)
> from source where summary_table.source_id=source.source_id;
This looks interesting and I'm going to give this a shot tomorrow
and see how it goes. Speed is somewhat of an issue which is why I
initially thought of the C function -- plus I wasn't aware you could do
CASE statements like that :) Thanks for the idea!
Steve
Isn't it likely that a single stream (or perhaps one that can be partitioned across spindles) will tend to be fastest, since it has a nice localised stream that a) allows for compression of reasonable blocks and b) fits with commit aggregation? RAM capacity on servers is going up and up, but the size of a customer address or row on an invoice isn't. I'd like to see an emphasis on speed of update with an assumption that most hot data is cached, most of the time. My understanding also is that storing data columnwise is handy when its persisted because linear scans are much faster. Saw it once with a system modelled after APL, blew me away even on a sparc10 once the data was organised and could be mapped. Still, for the moment anything that helps with the existing system would be good. Would it help to define triggers to be deferrable to commit as well as end of statement (and per row)? Seems to me it should be, at least for ones that raise 'some thing changed' events. And/or allow specification that events can fold and should be very cheap (don't know if this is the case now? Its not as well documented how this works as I'd like) James -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.446 / Virus Database: 268.18.7/713 - Release Date: 07/03/2007 09:24
>On sequential read speed HDs outperform flash disks... only on random >access the flash disks are better. So if your application is a DW one, >you're very likely better off using HDs. This looks likely to be a non-issue shortly, see here: http://www.reghardware.co.uk/2007/03/27/sams_doubles_ssd_capacity/ I still think this sort of devices will become the OLTP device of choice before too long - even if we do have to watch the wear rate. >WARNING: modern TOtL flash RAMs are only good for ~1.2M writes per >memory cell. and that's the =good= ones. Well, my original question was whether the physical update pattern of the server does have hotspots that will tend to cause a problem in normal usage if the wear levelling (such as it is) doesn't entirely spread the load. The sorts of application I'm interested in will not update individual data elements very often. There's a danger that index nodes might be rewritted frequently, but one might want to allow that indexes persist lazily and should be recovered from a scan after a crash that leaves them dirty, so that they can be cached and avoid such an access pattern. Out of interest with respect to WAL - has anyone tested to see whether one could tune the group commit to pick up slightly bigger blocks and write the WAL using compression, to up the *effective* write speed of media? Once again, most data I'm interested in is far from a random pattern and tends to compress quite well. If the WAL write is committed to the disk platter, is it OK for arbitrary data blocks to have failed to commit to disk so we can recover the updates for committed transactions? Is theer any documentation on the write barrier usage? James -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.446 / Virus Database: 268.18.25/744 - Release Date: 03/04/2007 05:32
Please help me to set up optimal values in the postgresql.conf file for PostgreSQL 8.2.3
Can you please give us an advice, which of your DBs and which configuration should we take for a project that has the following parameters:
Server Hardware:
Can you please give us an advice, which of your DBs and which configuration should we take for a project that has the following parameters:
- DB size: 25-30Gb
- number of tables: 100 - 150
- maximum number of records for one table: 50 - 100 millions
- avarage number of records for one table: 3 - 5 millions
- DB's querying frequency: 150-500 per minute
- SELECT/INSERT/UPDATE correlation is equal
- DELETE operations are performed rarely
Server Hardware:
- 2x Xeon 2.8Ghz
- 6GB RAM (2 GB for OS and DB, 4GB for Application)
- RAID-10 w/ 6x72GB 15.000rpm HDDs
-- Thanks, Eugene Ogurtsov Internal Development Chief Architect SWsoft, Inc.