use of dbspaces?
Question: if the whole database stays cached in the bufferpool (raw dbspaces on mirrored SSDs, 99.99% read / 98% write cache hit), is it worth revisiting the 10-year-old physical layout of tables and indexes across dbspaces? Consensus: time is probably better spent elsewhere, but layout still matters for reasons beyond I/O — extent/page-per-fragment limits in older versions, fragment elimination and PDQ, data lifecycle, page size choices, risk isolation, backup/checkpoint parallelism, and because a single huge chunk limits Informix's I/O parallelism. Art Kagel noted cache hit ratios don't show how much data is resident (check BTR/BTR3); the poster's ratios were ideal, so he decided only to add some dbspaces and fragment key tables when expanding storage.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Gurus: If the entire database is cached in the bufferpool, does it matter how the database tables and indices might be physically deployed in dbspaces? I typically observe read cache hit rate to be 99.99% and write rate to be 98%. The database layout hasn't been modified for over 10 years, and I wonder if I should even care about taking a fresh look at allocation of tables, indices, etc. But, if the whole disk space management thing is about IO performance, and all my data objects are cached all the time, and there are no checkpoint issues or foreground writes, then maybe I can use my time better with other concerns. (BTW, all dbspaces and blobspaces are raw, and on mirrored SSDs.) Regards, DG
Version? Period of those cache statistics? Those numbers are good obviously, but I've seen instances where even with those numbers there is still a lot of disk activity. Specially if your application or queries requisre some full scans etc. And you should consider that the structures on disk are not only related to performance.... things like: 1- Depending on your version, the number of extents may be critical 2- If you have tables reaching the limit of pages per fragment you may be in trouble 3- Fragmentation may help you on your data lifecycle policy 4- It is good to have data and indexes in separate dbspaces for example etc. But in principle I agree with you... You probably can find better use for your time than messing with physical layout if you're not suffering any performance issue. Regards On Thu, Aug 18, 2016 at 6:15 PM, DAVID GROVE <david.grove@alaska.gov> wrote: > Gurus: > > If the entire database is cached in the bufferpool, does it matter how the > database tables and indices might be physically deployed in dbspaces? > > I typically observe read cache hit rate to be 99.99% and write rate to be > 98%. > > The database layout hasn't been modified for over 10 years, and I wonder > if I > should even care about taking a fresh look at allocation of tables, > indices, > etc. But, if the whole disk space management thing is about IO performance, > and all my data objects are cached all the time, and there are no > checkpoint > issues or foreground writes, then maybe I can use my time better with other > concerns. (BTW, all dbspaces and blobspaces are raw, and on mirrored SSDs.) > > Regards, > > DG > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a1143e38608b7ae053a5becb7
Your time is probably better spent elsewhere. More chunks might help checkpoints, but you don't have checkpoint issues. Andrew -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of DAVID GROVE Sent: Thursday, August 18, 2016 12:16 PM To: ids@iiug.org Subject: use of dbspaces? [37622] Gurus: If the entire database is cached in the bufferpool, does it matter how the database tables and indices might be physically deployed in dbspaces? I typically observe read cache hit rate to be 99.99% and write rate to be 98%. The database layout hasn't been modified for over 10 years, and I wonder if I should even care about taking a fresh look at allocation of tables, indices, etc. But, if the whole disk space management thing is about IO performance, and all my data objects are cached all the time, and there are no checkpoint issues or foreground writes, then maybe I can use my time better with other concerns. (BTW, all dbspaces and blobspaces are raw, and on mirrored SSDs.) Regards, DG **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.
If its on SSD its effectively cached anyway. The only thing Id be worried about is management - does the database grow, do you need to allocate disk, that sort of thing. > On 18 Aug 2016, at 18:15, DAVID GROVE <david.grove@alaska.gov> wrote: > > Gurus: > > If the entire database is cached in the bufferpool, does it matter how the > database tables and indices might be physically deployed in dbspaces? > > I typically observe read cache hit rate to be 99.99% and write rate to be 98%. > > The database layout hasn't been modified for over 10 years, and I wonder if I > should even care about taking a fresh look at allocation of tables, indices, > etc. But, if the whole disk space management thing is about IO performance, > and all my data objects are cached all the time, and there are no checkpoint > issues or foreground writes, then maybe I can use my time better with other > concerns. (BTW, all dbspaces and blobspaces are raw, and on mirrored SSDs.) > > Regards, > > DG > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
David: A couple of points: 1. Cache hit percentages don't tell you the percentage of your data in memory, but how efficient that data is once it gets into memory. If you read 1000 pages into memory and reread one of those pages 200,000 times during which time you have read an additional 999 pages overwriting all by that one busy page, your read cache percentage is still 99%! similarly for writes. Look to your BTR and/or BTR3 to tell you how well you are keeping data in memory. 2. Disk layout can even matter with SSDs. Even SSDs have a maximum number of IOs they can handle even if that is much higher than for spindles. (BTW - I hope those SSD drives are not 10 years old ;-( - the reliable life of SSDs sold today is only about 5 years.) 3. Don't discount the channels between the server and the disk array and its capacity to funnel all that data. 4. If you make things REALLY simple, ie create a single huge dbspace from a single huge chunk, then Informix is limited as to the parallelism it can use to read data from and write data to storage. So you may not be getting the full IO capacity of the drives if you did that. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Aug 18, 2016 at 1:15 PM, DAVID GROVE <david.grove@alaska.gov> wrote: > Gurus: > > If the entire database is cached in the bufferpool, does it matter how the > database tables and indices might be physically deployed in dbspaces? > > I typically observe read cache hit rate to be 99.99% and write rate to be > 98%. > > The database layout hasn't been modified for over 10 years, and I wonder > if I > should even care about taking a fresh look at allocation of tables, > indices, > etc. But, if the whole disk space management thing is about IO performance, > and all my data objects are cached all the time, and there are no > checkpoint > issues or foreground writes, then maybe I can use my time better with other > concerns. (BTW, all dbspaces and blobspaces are raw, and on mirrored SSDs.) > > Regards, > > DG > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11443c58f97122053a5c3b98
Thank you all for your amazingly fast and helpful responses. Fernando: Version 12.10FC3. Regarding your comment #4 about having data and indexes in separate dbspaces... IF they are all cached, does that still matter? Andrew: Thank you. Spokey: Yes, it's growing slowly. In fact, I am about to add some drive space, and that's what lead me to ask myself whether it was time for a major reorganization. Art: (From your newratios.ksh script) BTR=.36/hr; BTR2=.10/hr; BTR3=.13/hr. Thank you for the good reminder about SSD life. Currently, all SSDs are <= 3 years old. I am about to replace one side of each mirror with new SSDs, in attempt to proactively head off lifespan issues. Our system is so small that all our SSDs (and OS drives, too [which are traditional hard drives]) are internal to our Sun [I can hardly bring myself to say "Oracle" :) ] boxes. Regarding your comment #4: In fact, that is what we do. We traded off total simplicity of administration for some performance. Since fragmentation is the one (strong) advantage I could think of for re-organizing disk storage, even with fully cached database, I think I will use this time when acquiring additional storage to create a small number of separate dbspaces to fragment some of the important tables. Still, probably not critical since no one is complaining. But, maybe I can bring them some delight, in the form of (unexpected) faster response for some queries or reports. Thank you all, again. DG
Probably should also have mentioned Bufwaits Ratio = .01% DG
David: On Fernando's comment about index and table separation - see my #4 again. B^) Sounds like you are solid for now. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Aug 18, 2016 at 2:33 PM, DAVID GROVE <david.grove@alaska.gov> wrote: > Thank you all for your amazingly fast and helpful responses. > > Fernando: > Version 12.10FC3. Regarding your comment #4 about having data and indexes > in > separate dbspaces... IF they are all cached, does that still matter? > > Andrew: > Thank you. > > Spokey: > Yes, it's growing slowly. In fact, I am about to add some drive space, and > that's what lead me to ask myself whether it was time for a major > reorganization. > > Art: > (From your newratios.ksh script) BTR=.36/hr; BTR2=.10/hr; BTR3=.13/hr. > Thank > you for the good reminder about SSD life. Currently, all SSDs are <= 3 > years > old. I am about to replace one side of each mirror with new SSDs, in > attempt > to proactively head off lifespan issues. Our system is so small that all > our > SSDs (and OS drives, too [which are traditional hard drives]) are internal > to > our Sun [I can hardly bring myself to say "Oracle" :) ] boxes. Regarding > your > comment #4: In fact, that is what we do. We traded off total simplicity of > administration for some performance. Since fragmentation is the one > (strong) > advantage I could think of for re-organizing disk storage, even with fully > cached database, I think I will use this time when acquiring additional > storage to create a small number of separate dbspaces to fragment some of > the > important tables. Still, probably not critical since no one is complaining. > But, maybe I can bring them some delight, in the form of (unexpected) > faster > response for some queries or reports. > > Thank you all, again. > > DG > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a114452b8717190053a5d185e
All ratios are ideal! Very cool! Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Aug 18, 2016 at 2:37 PM, DAVID GROVE <david.grove@alaska.gov> wrote: > Probably should also have mentioned Bufwaits Ratio = .01% > > DG > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a113f8aaed21ff7053a5d196f
On Thu, Aug 18, 2016 at 7:33 PM, DAVID GROVE <david.grove@alaska.gov> wrote: > Thank you all for your amazingly fast and helpful responses. > > Fernando: > Version 12.10FC3. Regarding your comment #4 about having data and indexes > in > separate dbspaces... IF they are all cached, does that still matter? > > Andrew: > Thank you. > > Version 12 doesn't limit the number of extents, so from that particular point of view you're ok. As for the separating of indexes.... well... in general we still consider it a good practice... A couple of reasons you may consider in favor of that: 1- It can be a good reason to split the "data", which reduces the risk if a dbspace becomes down 2- You may consider different page size for indexes and data 3- Makes it easier or more "graphic" to understand the space consumption But honestly... I do recommend that at a planning phase, but I don't recall recommending it in any "healthcheck" sort of engagement. Regards > Spokey: > Yes, it's growing slowly. In fact, I am about to add some drive space, and > that's what lead me to ask myself whether it was time for a major > reorganization. > > Art: > (From your newratios.ksh script) BTR=.36/hr; BTR2=.10/hr; BTR3=.13/hr. > Thank > you for the good reminder about SSD life. Currently, all SSDs are <= 3 > years > old. I am about to replace one side of each mirror with new SSDs, in > attempt > to proactively head off lifespan issues. Our system is so small that all > our > SSDs (and OS drives, too [which are traditional hard drives]) are internal > to > our Sun [I can hardly bring myself to say "Oracle" :) ] boxes. Regarding > your > comment #4: In fact, that is what we do. We traded off total simplicity of > administration for some performance. Since fragmentation is the one > (strong) > advantage I could think of for re-organizing disk storage, even with fully > cached database, I think I will use this time when acquiring additional > storage to create a small number of separate dbspaces to fragment some of > the > important tables. Still, probably not critical since no one is complaining. > But, maybe I can bring them some delight, in the form of (unexpected) > faster > response for some queries or reports. > > Thank you all, again. > > DG > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a113f37cc19ee12053a69845d
Yes it can matter in certain situations. Just off the top of my head: 1. Inserts into tables partitioned by round robin are generally faster than other partitioning methods and can handle greater concurrency. I have benchmarked this. 2. If you partition by expression the expression will still eliminate rows from processing regardless of whether stuff is in memory. 3. Certain PDQ operations can only work effectively with partitioned tables. 4. Table page limits and index limits in 11.50 and earlier. 5. Table row sizes against dbspace page size, i.e. using space efficiently and avoiding long rows. 6. Size/number of dbspaces: parallelism in backup/recovery, chunk cleaning during checkpoints. What I remain to be convinced about is whether it's worth re-organising tables into single extents. In-memory or not I don't think this makes much if any difference in any engine version still in support. Ben.