fragment performance
Aix 6.1, ids 11.50.fc7 Just looking for opinions on the following, when fragmenting a table ... what is the performance difference between fragmenting the table into separate raw devices .. or a single raw device with named partitions...assuming that the devices are all part of an IBM Shark subsystem...and I have no control over the disk level ... cause it's all magic and I don't need to worry about it ... Art, you can calm down ... trust me I've tried and lost that battle .... Thanks in advance for any assistance. Peter Peter Logan Senior Database Administrator Phone: 616/878-8309
I took a 6-disk RAID10 apart a couple of years back and fragmented over the subsequent disks to see how performance was. I couldn't beat the RAID10. Of course I was on Windows and Windows is not doing a good job at parallelism - which is the key advantage to spreading over multiple disks. cheers j. Sep 29, 2010 12:23:29 PM, ids@iiug.org wrote: =========================================== Aix 6.1, ids 11.50.fc7 > >Just looking for opinions on the following, when fragmenting a table ... >what is the performance difference between fragmenting the table into >separate raw devices .. or a single raw device with named >partitions...assuming that the devices are all part of an IBM Shark >subsystem...and I have no control over the disk level ... cause it's all > >magic and I don't need to worry about it ... Art, you can calm down ... > >trust me I've tried and lost that battle .... > >Thanks in advance for any assistance. > >Peter > >Peter Logan >Senior Database Administrator >Phone: 616/878-8309 > > >******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > >
The two big reasons to fragment are: 1. Parallel scans 2. Fragment elimination. Now if your fragment elimination always takes you to one single partition/fragment then you will not be using parallel scans and thus multiple scans in a single dbspace is not an issue. Please let us know which part of table fragmentation are you trying to take advantage of. Parallel scans, fragment elimination or both? John F. Miller III STSM, Embedability Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 09/29/2010 05:23:11 AM: > [image removed] > > fragment performance [21484] > > Peter_Logan@spartanstores.com > > to: > > ids > > 09/29/2010 05:23 AM > > Sent by: > > ids-bounces@iiug.org > > Please respond to ids > > Aix 6.1, ids 11.50.fc7 > > Just looking for opinions on the following, when fragmenting a table ... > what is the performance difference between fragmenting the table into > separate raw devices .. or a single raw device with named > partitions...assuming that the devices are all part of an IBM Shark > subsystem...and I have no control over the disk level ... cause it's all > magic and I don't need to worry about it ... Art, you can calm down ... > trust me I've tried and lost that battle .... > > Thanks in advance for any assistance. > > Peter > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Both ... Peter Logan Senior Database Administrator Phone: 616/878-8309 From: "John Miller iii" <miller3@us.ibm.com> To: ids@iiug.org Date: 09/29/2010 02:21 PM Subject: Re: fragment performance [21486] Sent by: ids-bounces@iiug.org The two big reasons to fragment are: 1. Parallel scans 2. Fragment elimination. Now if your fragment elimination always takes you to one single partition/fragment then you will not be using parallel scans and thus multiple scans in a single dbspace is not an issue. Please let us know which part of table fragmentation are you trying to take advantage of. Parallel scans, fragment elimination or both? John F. Miller III STSM, Embedability Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 09/29/2010 05:23:11 AM: > [image removed] > > fragment performance [21484] > > Peter_Logan@spartanstores.com > > to: > > ids > > 09/29/2010 05:23 AM > > Sent by: > > ids-bounces@iiug.org > > Please respond to ids > > Aix 6.1, ids 11.50.fc7 > > Just looking for opinions on the following, when fragmenting a table ... > what is the performance difference between fragmenting the table into > separate raw devices .. or a single raw device with named > partitions...assuming that the devices are all part of an IBM Shark > subsystem...and I have no control over the disk level ... cause it's all > magic and I don't need to worry about it ... Art, you can calm down ... > trust me I've tried and lost that battle .... > > Thanks in advance for any assistance. > > Peter > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
If the partitions are all on the same physical/logical structure, then it doesn't matter for read preformance whether they are on separate dbspaces or the same one. However, for heavy write performance, keep in mind that at checkpoint time the engine assigns one cleaner to each chunk, so you will get better overall write performance from the extra write time parallelism if IDS <thinks> that the data is on different structures. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, 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, Sep 29, 2010 at 8:23 AM, Peter_Logan@spartanstores.com < Peter_Logan@spartanstores.com> wrote: > Aix 6.1, ids 11.50.fc7 > > Just looking for opinions on the following, when fragmenting a table ... > what is the performance difference between fragmenting the table into > separate raw devices .. or a single raw device with named > partitions...assuming that the devices are all part of an IBM Shark > subsystem...and I have no control over the disk level ... cause it's all > magic and I don't need to worry about it ... Art, you can calm down ... > trust me I've tried and lost that battle .... > > Thanks in advance for any assistance. > > Peter > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636164ad1f3aab20491abba89