RE: Split Up PSApBTAB dbspace [1336]

Posted in 2003

Rajasekaran, Rajesh — Mon, 16 Jun 2003 09:01:46 -0500 — source: Usenet: comp.databases.informix

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C3340F.D2B8B520 Content-Type: text/plain; charset="iso-8859-1" Hello all, I have some clarification queries on the above topic. It will be great if someone can clarify my query. In our setup, We use the LVM say VERITAS volume manager for database volumes configured with RAID 10 and striping . We created the volumes using LVM in such an order that the data is distributed across all disks in the form of striping. For ex, first volume on the first stripeset,second volume on the second stripeset......and so on and again back to first stripe set etc. From Informix using SAPDBA , I will assign the available volumes when the database requires in the form of chunks. The data distribution on the disk is taken care by the LVM and is transparent to the Informix. Having said that we have dbspaces not evenly grown in size. For example, we have psapbtab for 150GB, psapstab for 70GB more than 60% of the whole database size in these 2 dbspaces itself. Though i have removed some of the larger tables into its own dbspace and index detached , i cant keep creating new dbspaces for all the growing tables as it will increase the dbspace maintenance headaches.I still have lot of growing tables but i frequently maintain those tables with fewer extents possible inside psapbtab/psapstab. Since there could be frequently used tables inside psapbtab due to the bigger dbspace size but all spread out from the Volume layout., i dont think any I/O bottleneck in this scenario. Ofcourse, I/O issue will always be there for Poor SQL's on big tables with concurrent access and not exactly matching the available index under any circumstances. Question ------------ 1) Under this scenario, Does the split up of PSapBtab dbspace improve I/O performance ? 2) If so, please pass on your brief explanation. Any comments are greatly appreciated. Thanks in advance. Rajesh Rajasekaran Informix Database Administrator Forest Pharmaceuticals Inc. 314-493-7073 (Work) -----Original Message----- From: ESTEBAN RIEZNIK [mailto:erieznikr@repsolypf.com] Sent: Wednesday, June 11, 2003 4:21 PM To: ids@iiug.org Subject: Re: Split Up PSApBTAB dbspace [1336] hi bro, the easiest (but kinda clumsy...) - dbexport your instance - drop database - drop dbspace PSAPBTAB - oninit -i - create new dbspaces PSAPBTAB1, PSAPBTAB2, and so on (also create new dbspaces for physical and logical logs!) - move logs to new dbspaces - edit the database query script file and add the "in PSAPBTABx" after CREATE TABLE clauses (check the create table syntax form details)- dbimport pretty nasty indeed, of course might be better methods, but this might help greetings ------_=_NextPart_001_01C3340F.D2B8B520 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = charset=3Diso-8859-1"> <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2653.12"> <TITLE>RE: Split Up PSApBTAB dbspace [1336] </TITLE> </HEAD> <BODY> Hello all,    I have some clarification queries on the = above topic. It will be great if someone can clarify my query.         In our = setup,  We use the LVM say VERITAS volume manager for database = volumes configured with RAID 10 and striping . We created the volumes = using LVM in such an order that the data is distributed across all = disks in the form of striping. For ex, first volume on the first = stripeset,second volume on the second stripeset......and so on and = again back to first stripe set etc. From Informix using SAPDBA , I will = assign the available volumes when the database requires in the form of = chunks. The data distribution on the disk is taken care by the LVM and = is transparent to the Informix.   Having said that we have dbspaces not evenly = grown in size. For example, we have psapbtab for 150GB, psapstab for = 70GB more than 60% of the whole database size in these 2 dbspaces = itself. Though i have removed some of the larger tables into its own = dbspace and index detached ,  i cant keep creating new dbspaces = for all the growing tables as it will increase the dbspace maintenance = headaches.I still have lot of growing tables but i frequently maintain = those tables with fewer extents possible inside psapbtab/psapstab. = Since there could be frequently used tables inside = psapbtab due to the bigger dbspace size but all spread out from the = Volume layout., i dont think any I/O bottleneck in this scenario. = Ofcourse, I/O issue will always be there for Poor SQL's on big tables = with concurrent access and not exactly matching the available index = under any circumstances. Question ------------ 1) Under this scenario, Does the split up of = PSapBtab dbspace improve I/O performance  ? 2) If so, please pass on your brief = explanation. Any comments are greatly appreciated. Thanks in advance. Rajesh Rajasekaran Informix Database Administrator Forest Pharmaceuticals Inc. 314-493-7073 (Work) -----Original Message----- From: ESTEBAN RIEZNIK [<A = HREF=3D"mailto:erieznikr@repsolypf.com">mailto:erieznikr@repsolypf.com</= A>] Sent: Wednesday, June 11, 2003 4:21 PM To: ids@iiug.org Subject: Re: Split Up PSApBTAB dbspace [1336] = hi bro, the easiest (but kinda clumsy...) - dbexport your instance - drop database - drop dbspace PSAPBTAB - oninit -i - create new dbspaces PSAPBTAB1, PSAPBTAB2, and so = on (also create new dbspaces for physical and logical = logs!) - move logs to new dbspaces - edit the database query script file and add the = "in PSAPBTABx" after CREATE TABLE clauses (check the create = table syntax form details) - dbimport pretty nasty indeed, of course might be better = methods, but this might help @@N

RE: Split Up PSApBTAB dbspace [1336]

Related threads

Your privacy choices