Re: Disk Layout recommendation

Neil Truby wrote: > "Jonathan Leffler" <jleffler@earthlink.net> wrote: >>You don't mention which version of IDS you are using (nor which >>platform), but I assume it is not 9.40. If it were, then I'd >>recommend using large chunks for dbs0 (the main dbspace). > > > Why? Just for ease of administration? No, performance. If you have multiple chunks on a single device, IDS will treat them as independent devices when they clearly are not, and will therefore send the disk heads tracking all over the place as it moves between chunks. For example, IDS might read sequentially from both Chunk1 and Chunk2 (which happen to be the only chunks on the disk drive), but at the disk level, it might be reading blocks (tracks?) alternately from the inner part of the drive and the outer part of the drive. With a single big chunk, it won't do that; it will schedule the sequential read from the inner part of the disk, then the outer part of the disk. Of course, the extent to which that is a problem depends on the cleverness of the disk controller, the size of its caches, whether you're really dealing with a single disk drive or whether you've got a logical volume manager in the way, or a RAID unit, or a NAS/SAN system, and so on. But when you have simple disks, having single chunks on them as big as the device should give the best performance. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

Jonathan Leffler wrote: > > Neil Truby wrote: > > "Jonathan Leffler" <jleffler@earthlink.net> wrote: > >>You don't mention which version of IDS you are using (nor which > >>platform), but I assume it is not 9.40. If it were, then I'd > >>recommend using large chunks for dbs0 (the main dbspace). > > > > > > Why? Just for ease of administration? > > No, performance. If you have multiple chunks on a single device, IDS > will treat them as independent devices when they clearly are not, and > will therefore send the disk heads tracking all over the place as it > moves between chunks. For example, IDS might read sequentially from > both Chunk1 and Chunk2 (which happen to be the only chunks on the disk > drive), but at the disk level, it might be reading blocks (tracks?) > alternately from the inner part of the drive and the outer part of the > drive. With a single big chunk, it won't do that; it will schedule > the sequential read from the inner part of the disk, then the outer > part of the disk. Of course, the extent to which that is a problem > depends on the cleverness of the disk controller, the size of its > caches, whether you're really dealing with a single disk drive or > whether you've got a logical volume manager in the way, or a RAID > unit, or a NAS/SAN system, and so on. But when you have simple disks, > having single chunks on them as big as the device should give the best > performance. > > -- > Jonathan Leffler #include <disclaimer.h> > Email: jleffler@earthlink.net, jleffler@us.ibm.com > Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/ Here I must jump in. As Andrew showed us some 14-18 months ago, all modern (i.e. 10K or higher rpm) disk do track read serialization and are able to handle 5 to 6 parallel I/O streams to maximize throughput per disk. Now the biggest advantage of having NOT 1 chunk per disk is that on can easily see from monitoring the I/O per chunk, which of the chunks must be placed on the fastest area of the disks. I really am disappointed from V9.4 docu, which hints you in the wrong direction when it comes to this rather trivial fact. Of course, I do know, that monitoring your baby to know as much as possible, is not a job for the lazy or noncaring or dummy. But that we were able to get information about almost everything is still the biggest advantage of IFX over competing products, and it is this fact, which still creates the not so small performance difference ifx has compared to other SQL DB engines (like 'O' or db2) dic_k

Richard Kofler wrote: > Jonathan Leffler wrote: > >>Neil Truby wrote: >>>"Jonathan Leffler" <jleffler@earthlink.net> wrote: >>>>You don't mention which version of IDS you are using (nor which >>>>platform), but I assume it is not 9.40. If it were, then I'd >>>>recommend using large chunks for dbs0 (the main dbspace). >>> >>>Why? Just for ease of administration? >> >>No, performance. If you have multiple chunks on a single device, IDS >>will treat them as independent devices when they clearly are not, and >>will therefore send the disk heads tracking all over the place as it >>moves between chunks. For example, IDS might read sequentially from >>both Chunk1 and Chunk2 (which happen to be the only chunks on the disk >>drive), but at the disk level, it might be reading blocks (tracks?) >>alternately from the inner part of the drive and the outer part of the >>drive. With a single big chunk, it won't do that; it will schedule >>the sequential read from the inner part of the disk, then the outer >>part of the disk. Of course, the extent to which that is a problem >>depends on the cleverness of the disk controller, the size of its >>caches, whether you're really dealing with a single disk drive or >>whether you've got a logical volume manager in the way, or a RAID >>unit, or a NAS/SAN system, and so on. But when you have simple disks, >>having single chunks on them as big as the device should give the best >>performance. >> >>-- >>Jonathan Leffler #include <disclaimer.h> >>Email: jleffler@earthlink.net, jleffler@us.ibm.com >>Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/ > > > Here I must jump in. > > As Andrew showed us some 14-18 months ago, all modern (i.e. > 10K or higher rpm) disk do track read serialization and are > able to handle 5 to 6 parallel I/O streams to maximize throughput > per disk. So you'd recommend putting multiple chunks on a single disk if the disk is fast enough? Assuming you aren't using an LVM or RAID or something similar to provide a level of indirection? And you'd recommend this regardless of the fact that if you have 4 chunks on a disk, then accessing the different chunks requires head movement across 3/4 of the disk even when each chunk is only say 20% full, whereas in a single chunk, it might only involve head movement over 20% of the disk? > Now the biggest advantage of having NOT 1 chunk per disk is that on > can easily see from monitoring the I/O per chunk, which of the > chunks must be placed on the fastest area of the disks. I can see that you lose some precision in the monitoring. But are you really sure that multiple chunks on a single disk would really, really improve throughput? > I really am disappointed from V9.4 docu, which hints you in the > wrong direction when it comes to this rather trivial fact. So, you've reported this to Tech Pubs? Since my fastest machine runs at 400 MHz, and the disk speed is nowhere near 10K rpm, there's no way for me to know from direct experience what you say - plus I don't get the time to run benchmarking etc, or read up on disk specs. So, if you know stuff that isn't in the manual, maybe you can help out by telling us. I know it isn't exactly open source, but... > Of course, I do know, that monitoring your baby to know as > much as possible, is not a job for the lazy or noncaring or > dummy. > > But that we were able to get information about almost > everything is still the biggest advantage of IFX over > competing products, and it is this fact, which still creates > the not so small performance difference ifx has compared to > other SQL DB engines (like 'O' or db2) > > dic_k -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

Jonathan Leffler wrote: > > Richard Kofler wrote: > > Jonathan Leffler wrote: > > > >>Neil Truby wrote: > >>>"Jonathan Leffler" <jleffler@earthlink.net> wrote: > >>>>You don't mention which version of IDS you are using (nor which > >>>>platform), but I assume it is not 9.40. If it were, then I'd > >>>>recommend using large chunks for dbs0 (the main dbspace). > >>> > >>>Why? Just for ease of administration? > >> > >>No, performance. If you have multiple chunks on a single device, IDS > >>will treat them as independent devices when they clearly are not, and > >>will therefore send the disk heads tracking all over the place as it > >>moves between chunks. For example, IDS might read sequentially from > >>both Chunk1 and Chunk2 (which happen to be the only chunks on the disk > >>drive), but at the disk level, it might be reading blocks (tracks?) > >>alternately from the inner part of the drive and the outer part of the > >>drive. With a single big chunk, it won't do that; it will schedule > >>the sequential read from the inner part of the disk, then the outer > >>part of the disk. Of course, the extent to which that is a problem > >>depends on the cleverness of the disk controller, the size of its > >>caches, whether you're really dealing with a single disk drive or > >>whether you've got a logical volume manager in the way, or a RAID > >>unit, or a NAS/SAN system, and so on. But when you have simple disks, > >>having single chunks on them as big as the device should give the best > >>performance. > >> > >>-- > >>Jonathan Leffler #include <disclaimer.h> > >>Email: jleffler@earthlink.net, jleffler@us.ibm.com > >>Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/ > > > > > > Here I must jump in. > > > > As Andrew showed us some 14-18 months ago, all modern (i.e. > > 10K or higher rpm) disk do track read serialization and are > > able to handle 5 to 6 parallel I/O streams to maximize throughput > > per disk. > > So you'd recommend putting multiple chunks on a single disk if the > disk is fast enough? Assuming you aren't using an LVM or RAID or In many - but not all - circumstances - yes. Explanation below. > something similar to provide a level of indirection? And you'd > recommend this regardless of the fact that if you have 4 chunks on a > disk, then accessing the different chunks requires head movement > across 3/4 of the disk even when each chunk is only say 20% full, > whereas in a single chunk, it might only involve head movement over > 20% of the disk? > > > Now the biggest advantage of having NOT 1 chunk per disk is that on > > can easily see from monitoring the I/O per chunk, which of the > > chunks must be placed on the fastest area of the disks. > > I can see that you lose some precision in the monitoring. But are you > really sure that multiple chunks on a single disk would really, really > improve throughput? > > > I really am disappointed from V9.4 docu, which hints you in the > > wrong direction when it comes to this rather trivial fact. > > So, you've reported this to Tech Pubs? Since my fastest machine runs I collect my remarks and will report them alltogether, when we through with first round of tests, approx 3rd week in June. Same is true for the not so important thingies, when new 'features' are found. Only the real hammers deserve an immediate support case number. > at 400 MHz, and the disk speed is nowhere near 10K rpm, there's no way > for me to know from direct experience what you say - plus I don't get > the time to run benchmarking etc, or read up on disk specs. So, if > you know stuff that isn't in the manual, maybe you can help out by > telling us. I know it isn't exactly open source, but... > > > Of course, I do know, that monitoring your baby to know as > > much as possible, is not a job for the lazy or noncaring or > > dummy. > > > > But that we were able to get information about almost > > everything is still the biggest advantage of IFX over > > competing products, and it is this fact, which still creates > > the not so small performance difference ifx has compared to > > other SQL DB engines (like 'O' or db2) > > > > dic_k > > -- > Jonathan Leffler #include <disclaimer.h> > Email: jleffler@earthlink.net, jleffler@us.ibm.com > Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/ Here is my explanation, why multichunk disk layout can pay off, if -- there is no more other (trivial) way to reduce I/O and the penny counter ppl do not allocate the money to buy Solid Sate disks a) disk rotaion is fast b) disk platter to ondisk cache speed is high (does not always correlate to disk rotation speed) c) ondisk cache is huge (I like those 8MB caches. But I am an old man and thus I know, that a 10GB disk with 512KB cache has way more cache than a 200GB disk with 8MB cache, seen like (disk size)/cache size d) one has a lot of disks That is the reason why I go for IDE (ATA133 or SATA) instead of SCSI. Price relation here is like IDE:SCSI=1:2.5+ I therefore am able to motivate the pennycounting pointy haired ones to buy 18 disks and an additional controller when maths show that 10 disks can hold the data AND they still save gold :) Now this makes it possible, that we have no need to fill more than 50-60% of the disks. e) DB is read bound like 20+:1 at least f) you have a detailled monitoring profile on disk I/O read and disk I/O write g) you understand read ahead and you are able to tune it a bit h) you know what table is read only and has heavy I/O at the same time i) the ifx feature of attached index is not taken away by IBM (That would make real speed gained from sideeffekts of read ahead in OLTP systems much harder - Yes, I did some tuning using FILLFACTOR with very good results, more than a dozen times that is, which is way easier to do than the fragmentation game) j) you have enough disks to be able to pack 5 or fewer hot chunks onto 1 device and then 'fill up' that disk with chunks adding low I/O volume I use the 1 - 2 - 3 - 4 - 5 10 - 9 - 8 - 7 - 6 strategy to balance over disks. The numbers are from the I/O-per-chunk ranking list. 1 column = 1 disk k) placing chunks on disk avoids unnessary disk arm movements by avoiding to place such chunks on the same disk where the respective tables are not small and have a master-detail relation or are used to join in those queries of the Top10 frequency list. But I consider to avoid this as a trivial exercise for a DBA To make it short: Most important is that the business is in need for speed. If you know your baby I/O wise, and if you have some way to test you can really do what a SAN *should* do but in real life only has on the marketing brochure. To prove that I am wrong: Since 1997 I looking for the first person to show up and to tell me that migrating a database to a SAN was a good move, I/O-performance wise .... [ if it was boring to read that far - don't bother to read any further