Chunk Size

Hi we are running 11.70.FC7 on Solaris 10 and we just got new storage for our database. For the first time our storage admin asked what size chunks we want for our raw DB storage. HIstorically we have always had 2 GB chunks. Any suggestions on a good chunk size for an OLTP system with 10 Data DBspaces? Thank You, --David --001a1136dfc66bcfc804f238e4d2

It's a balancing act. The more chunks you spread your data across the more IO threads will be used to flush dirty pages at checkpoint time (one thread per chunk up to CLEANERS). On the other hand, more chunks means more management overhead for your SAs, for you, and even for the engine. Rule of thumb: quiet dbspaces on fewer bigger chunks, busy ones on more smaller chunks. What do "fewer" and "more" mean? That's the balancing act. Art Art S. Kagel, Principal Consultant ASK Database Management 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 Wed, Feb 12, 2014 at 12:27 PM, Informix DBA <in4mixdba@gmail.com> wrote: > Hi we are running 11.70.FC7 on Solaris 10 and we just got new storage for > our database. For the first time our storage admin asked what size chunks > we want for our raw DB storage. HIstorically we have always had 2 GB > chunks. Any suggestions on a good chunk size for an OLTP system with 10 > Data DBspaces? > > Thank You, > > --David > > --001a1136dfc66bcfc804f238e4d2 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1136c0ba4654ca04f238f78e

Art, Thank you. What sizes have you typically seen on OLTP systems? --Dave On Wed, Feb 12, 2014 at 12:33 PM, Art Kagel <art.kagel@gmail.com> wrote: > It's a balancing act. The more chunks you spread your data across the more > IO threads will be used to flush dirty pages at checkpoint time (one thread > per chunk up to CLEANERS). On the other hand, more chunks means more > management overhead for your SAs, for you, and even for the engine. Rule > of thumb: quiet dbspaces on fewer bigger chunks, busy ones on more smaller > chunks. What do "fewer" and "more" mean? That's the balancing act. > > Art > > Art S. Kagel, Principal Consultant > ASK Database Management > > 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 Wed, Feb 12, 2014 at 12:27 PM, Informix DBA <in4mixdba@gmail.com> > wrote: > > > Hi we are running 11.70.FC7 on Solaris 10 and we just got new storage for > > our database. For the first time our storage admin asked what size chunks > > we want for our raw DB storage. HIstorically we have always had 2 GB > > chunks. Any suggestions on a good chunk size for an OLTP system with 10 > > Data DBspaces? > > > > Thank You, > > > > --David > > > > --001a1136dfc66bcfc804f238e4d2 > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001a1136c0ba4654ca04f238f78e > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11348c0488117a04f2391d0f

I know your question was directed at Art and I'm interested to hear what he has to say on the subject. My thoughts (and what I've done with my OLTP environments) is to go with the largest chunk size I need to hold all my data so I end up with 1 chunk per each dbspace (initially, but if we need more space we will obviously add an additional chunk to the dbspace). I do this just for ease of administration and not for any performance related reason. It is nice to see just a few chunks in the onstat -d output vs. the 100's of chunks we would see when the 2GB chunk size limitation was in place. What do I sacrifice in the way of performance by doing this? Like Art said, you get more I/O threads during checkpoint time with more chunks. So maybe my checkpoint time will increase (will it really? only testing will tell), but what do I care? We now have non blocking checkpoints (in most cases) so take twice as long to checkpoint for all I care. Maybe a slower and longer drawn out non blocking checkpoint is better for OLTP anyway. Instead of stealing I/O cycles from OLTP sessions to perform a checkpoint quickly, if I draw out the checkpoint over a longer period of time and use less resources then that means my OLTP is going to be more responsive during a checkpoint. When I consolidated all of my 2 GB chunks into 1 large chunk I did not notice any problems and I haven't regretted the decision. Andrew -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Informix DBA Sent: Wednesday, February 12, 2014 11:44 AM To: ids@iiug.org Subject: Re: Chunk Size [32462] Art, Thank you. What sizes have you typically seen on OLTP systems? --Dave On Wed, Feb 12, 2014 at 12:33 PM, Art Kagel <art.kagel@gmail.com> wrote: > It's a balancing act. The more chunks you spread your data across the > more IO threads will be used to flush dirty pages at checkpoint time > (one thread per chunk up to CLEANERS). On the other hand, more chunks > means more management overhead for your SAs, for you, and even for the > engine. Rule of thumb: quiet dbspaces on fewer bigger chunks, busy > ones on more smaller chunks. What do "fewer" and "more" mean? That's the balancing act. > > Art > > Art S. Kagel, Principal Consultant > ASK Database Management > > 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 Wed, Feb 12, 2014 at 12:27 PM, Informix DBA <in4mixdba@gmail.com> > wrote: > > > Hi we are running 11.70.FC7 on Solaris 10 and we just got new > > storage for our database. For the first time our storage admin asked > > what size chunks we want for our raw DB storage. HIstorically we > > have always had 2 GB chunks. Any suggestions on a good chunk size > > for an OLTP system with 10 Data DBspaces? > > > > Thank You, > > > > --David > > > > --001a1136dfc66bcfc804f238e4d2 > > > > > > > > > > **************************************************************************** *** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001a1136c0ba4654ca04f238f78e > > > > **************************************************************************** *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11348c0488117a04f2391d0f **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

Thank you Andrew for your input. --Dave On Wed, Feb 12, 2014 at 1:06 PM, Andrew Ford <andrew@informix-dba.com>wrote: > I know your question was directed at Art and I'm interested to hear what he > has to say on the subject. > > My thoughts (and what I've done with my OLTP environments) is to go with > the > largest chunk size I need to hold all my data so I end up with 1 chunk per > each dbspace (initially, but if we need more space we will obviously add an > additional chunk to the dbspace). > > I do this just for ease of administration and not for any performance > related reason. It is nice to see just a few chunks in the onstat -d output > vs. the 100's of chunks we would see when the 2GB chunk size limitation was > in place. > > What do I sacrifice in the way of performance by doing this? Like Art said, > you get more I/O threads during checkpoint time with more chunks. So maybe > my checkpoint time will increase (will it really? only testing will tell), > but what do I care? We now have non blocking checkpoints (in most cases) so > take twice as long to checkpoint for all I care. > > Maybe a slower and longer drawn out non blocking checkpoint is better for > OLTP anyway. Instead of stealing I/O cycles from OLTP sessions to perform a > checkpoint quickly, if I draw out the checkpoint over a longer period of > time and use less resources then that means my OLTP is going to be more > responsive during a checkpoint. > > When I consolidated all of my 2 GB chunks into 1 large chunk I did not > notice any problems and I haven't regretted the decision. > > Andrew > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Informix DBA > Sent: Wednesday, February 12, 2014 11:44 AM > To: ids@iiug.org > Subject: Re: Chunk Size [32462] > > Art, > > Thank you. What sizes have you typically seen on OLTP systems? > > --Dave > > On Wed, Feb 12, 2014 at 12:33 PM, Art Kagel <art.kagel@gmail.com> wrote: > > > It's a balancing act. The more chunks you spread your data across the > > more IO threads will be used to flush dirty pages at checkpoint time > > (one thread per chunk up to CLEANERS). On the other hand, more chunks > > means more management overhead for your SAs, for you, and even for the > > engine. Rule of thumb: quiet dbspaces on fewer bigger chunks, busy > > ones on more smaller chunks. What do "fewer" and "more" mean? That's the > balancing act. > > > > Art > > > > Art S. Kagel, Principal Consultant > > ASK Database Management > > > > 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 Wed, Feb 12, 2014 at 12:27 PM, Informix DBA <in4mixdba@gmail.com> > > wrote: > > > > > Hi we are running 11.70.FC7 on Solaris 10 and we just got new > > > storage for our database. For the first time our storage admin asked > > > what size chunks we want for our raw DB storage. HIstorically we > > > have always had 2 GB chunks. Any suggestions on a good chunk size > > > for an OLTP system with 10 Data DBspaces? > > > > > > Thank You, > > > > > > --David > > > > > > --001a1136dfc66bcfc804f238e4d2 > > > > > > > > > > > > > > > > > > **************************************************************************** > *** > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --001a1136c0ba4654ca04f238f78e > > > > > > > > > > **************************************************************************** > *** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001a11348c0488117a04f2391d0f > > > **************************************************************************** > *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c32274d1ac2004f23b2c0d

David: There's no "typical" unfortunately. 2GB is probably most common just because of the old Informix legacy of 2GB chunks, but that's just habit. I have clients using chunk sizes from less than 2GB to 100GB. Here's what you do: - Evaluate the estimated peak throughput requirements for writing and reading by dbspace. - Make sure that the underlying SAN structures on which your chunks will reside are using sufficient channels and number of spindles to support that level of throughput. (If not, fewer chunks will not hurt performance but restructuring your SAN may improve it). - Look at your longest checkpoints and estimate the time to flush that data volume - Try to figure out how activating more or fewer cleaner threads by using more or fewer chunks would affect your checkpoint block times, data safety ('cause log flushes may be affected as well), and recovery time. - Decide. Art Art S. Kagel, Principal Consultant ASK Database Management 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 Wed, Feb 12, 2014 at 12:43 PM, Informix DBA <in4mixdba@gmail.com> wrote: > Art, > > Thank you. What sizes have you typically seen on OLTP systems? > > --Dave > > On Wed, Feb 12, 2014 at 12:33 PM, Art Kagel <art.kagel@gmail.com> wrote: > > > It's a balancing act. The more chunks you spread your data across the > more > > IO threads will be used to flush dirty pages at checkpoint time (one > thread > > per chunk up to CLEANERS). On the other hand, more chunks means more > > management overhead for your SAs, for you, and even for the engine. Rule > > of thumb: quiet dbspaces on fewer bigger chunks, busy ones on more > smaller > > chunks. What do "fewer" and "more" mean? That's the balancing act. > > > > Art > > > > Art S. Kagel, Principal Consultant > > ASK Database Management > > > > 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 Wed, Feb 12, 2014 at 12:27 PM, Informix DBA <in4mixdba@gmail.com> > > wrote: > > > > > Hi we are running 11.70.FC7 on Solaris 10 and we just got new storage > for > > > our database. For the first time our storage admin asked what size > chunks > > > we want for our raw DB storage. HIstorically we have always had 2 GB > > > chunks. Any suggestions on a good chunk size for an OLTP system with 10 > > > Data DBspaces? > > > > > > Thank You, > > > > > > --David > > > > > > --001a1136dfc66bcfc804f238e4d2 > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --001a1136c0ba4654ca04f238f78e > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001a11348c0488117a04f2391d0f > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0158c7c8457b9404f23b7de9

You had two diverse opinions from two Lions of Informix, Art and Andrew, which just goes to confirm that there's no "right" answer. My own inclination is to side with Andrew, as I am lazy: I too have never noticed an overhead from large chunks, but that's not to say there isn't one. On a point of pragmatism though, keeping exactly the same chunk layout as you already have will allow you to migrate easily to the new storage with virtually no downtime (via Informix mirroring for example), but any re-organisation to larger chunk sizes is going to involve an outage to move your data around, presumably.