Fragmentation of database stored on SAN
Hi guys, IDS 7.31 AIX 4.3 2 RS6000 CPUs HP SAN Manager We have a database having some very large tables (over 30 million rows). We are considering using Informix table fragmentation to improve the performance. However, we are not sure whether this will be effective because the database resides on a SAN. Has anyone attempted anything similar to this and/or can give me some information regarding whether the performance gain can be achieved through this technique? Thanks, Garth Davis ----------------------- Manager Application Engineering FSL
If you are looking at round robin fragmentation, you will only gain the ability of having more rows in your table before your table's dbspace exceeds the maximum size of a dbspace. That remains the same regardless of SAN or no SAN. If you are looking to implement a fragmentation scheme that will eliminate rows (fragments) from a query, you can decrease the amount of time it takes for queries to complete. That remains the same regardless of SAN or no SAN. Look at the various manuals for more on Fragmentation. Take care. Clifton PS. You can eliminate the issue of maximum dbspace size by upgrading to 9.40. CB Garth Davis <gdavis@fsl.org.jm> wrote: Hi guys, IDS 7.31 AIX 4.3 2 RS6000 CPUs HP SAN Manager We have a database having some very large tables (over 30 million rows). We are considering using Informix table fragmentation to improve the performance. However, we are not sure whether this will be effective because the database resides on a SAN. Has anyone attempted anything similar to this and/or can give me some information regarding whether the performance gain can be achieved through this technique? Thanks, Garth Davis ----------------------- Manager Application Engineering FSL
Yes, please use "fragment by expression" strategy. And have your queries coded such that they will only hit the data in the fragments needed. Hope this help. Ravi. "Clifton M. Bean" <cmbean@sbcglobal.net> wrote: If you are looking at round robin fragmentation, you will only gain the ability of having more rows in your table before your table's dbspace exceeds the maximum size of a dbspace. That remains the same regardless of SAN or no SAN. If you are looking to implement a fragmentation scheme that will eliminate rows (fragments) from a query, you can decrease the amount of time it takes for queries to complete. That remains the same regardless of SAN or no SAN. Look at the various manuals for more on Fragmentation. Take care. Clifton PS. You can eliminate the issue of maximum dbspace size by upgrading to 9.40. CB Garth Davis wrote: Hi guys, IDS 7.31 AIX 4.3 2 RS6000 CPUs HP SAN Manager We have a database having some very large tables (over 30 million rows). We are considering using Informix table fragmentation to improve the performance. However, we are not sure whether this will be effective because the database resides on a SAN. Has anyone attempted anything similar to this and/or can give me some information regarding whether the performance gain can be achieved through this technique? Thanks, Garth Davis ----------------------- Manager Application Engineering FSL --------------------------------- Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses.
Thanks guys. You have being a tremendous help to me. Based on the evidence you have provided, I should be able to convince my colleagues go ahead with fragmentation of our tables. Garth. -----Original Message----- From: Demeis, Tony [mailto:Tony.Demeis@moh.gov.on.ca] Sent: Wednesday, December 01, 2004 2:12 PM To: 'Garth Davis ' Subject: RE: Fragmentation of database stored on SAN [3822] I use fragmentation for various tables (including 1 table over 800 million rows) in an environment similar to yours and there is definitely an improvement in performance. You should index on the key fragment expression if possible as well as any other highly used columns. Also, if space permits, keep your indexes attached in the same fragment but detached for unique indexes. -----Original Message----- From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On Behalf Of Garth Davis Sent: Wednesday, December 01, 2004 12:00 PM To: ids@iiug.org Subject: Fragmentation of database stored on SAN [3822] Hi guys, IDS 7.31 AIX 4.3 2 RS6000 CPUs HP SAN Manager We have a database having some very large tables (over 30 million rows). We are considering using Informix table fragmentation to improve the performance. However, we are not sure whether this will be effective because the database resides on a SAN. Has anyone attempted anything similar to this and/or can give me some information regarding whether the performance gain can be achieved through this technique? Thanks, Garth Davis ----------------------- Manager Application Engineering FSL
Hi Garth We use frags very successfully - just one 'gotcha' is to remember that if you use a REMAINDER IN... clause that the engine will include that path in all searches even though the fragmentation strategy steers a query absolutely to a single fragment. Caused much headscratching here! Malc