IDS 12.10- BEST PRATICES Database Design

Hi, We are plan to do database reorganize to follow best practices database design from IBM to get best performance for our database. Our design as below: (all dbspaces size 2K) rootdbs - 1 logdbs - 1, allocated log file phydbs - 1, allocated physical log datadbs - 7, allocated data indexdbs - 4, allocated index table tempdsb - 4, allocated tempdbs Refer our onstat -d ouput as below: IBM Informix Dynamic Server Version 12.10.FC6AEE -- online -- Up 00:00:01 -- 12389868 Kbytes Dbspaces number flags fchunk nchunks flags owner name 1 40001 1 1 N B informix rootdb 2 40001 2 1 N B informix phydbs 3 40001 3 1 N B informix logdbs 4 42001 4 1 N TB informix tempdbs1 5 42001 5 1 N TB informix tempdbs2 6 42001 6 1 N TB informix tempdbs3 7 42001 7 1 N TB informix tempdbs4 8 40001 8 2 N B informix datadb1 9 40001 9 1 N B informix datadb2 10 40001 10 1 N B informix datadb3 11 40001 11 1 N B informix datadb4 12 40001 12 1 N B informix datadb5 13 40001 13 3 N B informix datadb6 14 40001 14 2 N B informix datadb7 15 40001 15 1 N B informix datadb8 16 40001 16 1 N B informix idxdbs1 17 40001 17 3 N B informix idxdbs2 18 40001 18 2 N B informix idxdbs3 19 40001 19 1 N B informix idxdbs4 Chunks chk/dbs offset size free bpages flags pathname 1 1 0 1000000 984577 PO-B /dev/vg00/elixir/rootdb 2 2 0 500000 249947 PO-B /dev/vg00/elixir/phydbs 3 3 0 10000000 9249947 PO-B /dev/vg00/elixir/logdbs 4 4 0 2500000 2499897 PO-B /dev/vg00/elixir/tempdbs1 5 5 0 2500000 2499897 PO-B /dev/vg00/elixir/tempdbs2 6 6 0 2500000 2499897 PO-B /dev/vg00/elixir/tempdbs3 7 7 0 2500000 2499897 PO-B /dev/vg00/elixir/tempdbs4 8 8 0 10000000 0 PO-B /dev/vg00/elixir/datadb1 9 9 0 10000000 3830913 PO-B /dev/vg00/elixir/datadb2 10 10 0 10000000 2633307 PO-B /dev/vg00/elixir/datadb3 11 11 0 10000000 2709573 PO-B /dev/vg00/elixir/datadb4 12 12 0 10000000 3830913 PO-B /dev/vg00/elixir/datadb5 13 13 0 10000000 19 PO-B /dev/vg00/elixir/datadb6 14 14 0 10000000 3125 PO-B /dev/vg00/elixir/datadb7 15 15 0 10000000 4374729 PO-B /dev/vg00/elixir/datadb8 16 16 0 10000000 6433758 PO-B /dev/vg00/elixir/idxdbs1 17 17 0 10000000 261 PO-B /dev/vg00/elixir/idxdbs2 18 18 0 10000000 562711 PO-B /dev/vg00/elixir/idxdbs3 19 19 0 10000000 2878822 PO-B /dev/vg00/elixir/idxdbs4 20 17 0 2500000 288 PO-B /dev/vg00/elixir/idxdbs21 21 8 0 10000000 3425631 PO-B /dev/vg00/elixir/datadb12 22 17 0 10000000 6085694 PO-B /dev/vg00/elixir/idxdbs22 23 13 0 2500000 0 PO-B /dev/vg00/elixir/datadb61 24 18 0 2500000 1600906 PO-B /dev/vg00/elixir/idxdbs31 25 13 0 10000000 9146000 PO-B /dev/vg00/elixir/datadb62 26 14 0 10000000 9160827 PO-B /dev/vg00/elixir/datadb71 Based on our design, we follow best practices or not? We plan to move all index table into 1 dbspaces from 4 dbspaces, can improve performance or not? Any document from IBM as references for us how to design our database follow best practices. Please help. Thank You

Mohd: Notes for you: - Test whether in your environment applications that make extensive use of temp tables and/or sorting might perform better if your temp dbspaces were allocated in a file system as files rather than as RAW devices. Using COOKED files for temp dbspaces sometimes performs better than RAW because most temp tables do not live long enough to cause the underlying file system to flush to disk. Informix does not open temp dbspaces that are COOKED as OSYNC or ODIRECT so they are not auto-flushed (ordinary chunks in COOKED files are auto-flushed). - Indexes perform best when placed in dbspaces with wide pages. Except for trivially small indexes (on tables with few rows) all indexes perform best on 16K pages. So you may want to make the index dbspace(s) 16K instead of 2K. - One index dbspace or four? That depends. If the index chunks are all allocated from a single disk structure on your SAN then there may be no difference in performance between one dbspace and four initially at least. Over time, however, interleaving and fragmentation of different indexes on the same dbspace may adversely impact performance necessitating early rebuilding of indexes. If you have multiple index dbspaces you can intelligently place indexes so that those that will be growing concurrently (so multiple indexes on a highly active table or indexes in related tables like a header/detail table pair) are placed on different dbspaces to prevent them from fragmenting each other more than necessary. Proper extent sizing can help here somewhat, but best practice is to avoid the problem. - As much as possible isolate busy tables/indexes from each other and data from indexes. This may mean dividing your SAN into more than one physical array. Difficult to convince your storage types to do this, but you asked about best practice and this is part of 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 Mon, Apr 11, 2016 at 9:01 AM, MOHD FADZIL JUSOH <fadzil@isianpadu.com> wrote: > Hi, > > We are plan to do database reorganize to follow best practices database > design > from IBM to get best performance for our database. > > Our design as below: > (all dbspaces size 2K) > rootdbs - 1 > logdbs - 1, allocated log file > phydbs - 1, allocated physical log > datadbs - 7, allocated data > indexdbs - 4, allocated index table > tempdsb - 4, allocated tempdbs > > Refer our onstat -d ouput as below: > > IBM Informix Dynamic Server Version 12.10.FC6AEE -- online -- Up 00:00:01 > -- > 12389868 Kbytes > > Dbspaces > number flags fchunk nchunks flags owner name > 1 40001 1 1 N B informix rootdb > 2 40001 2 1 N B informix phydbs > 3 40001 3 1 N B informix logdbs > 4 42001 4 1 N TB informix tempdbs1 > 5 42001 5 1 N TB informix tempdbs2 > 6 42001 6 1 N TB informix tempdbs3 > 7 42001 7 1 N TB informix tempdbs4 > 8 40001 8 2 N B informix datadb1 > 9 40001 9 1 N B informix datadb2 > 10 40001 10 1 N B informix datadb3 > 11 40001 11 1 N B informix datadb4 > 12 40001 12 1 N B informix datadb5 > 13 40001 13 3 N B informix datadb6 > 14 40001 14 2 N B informix datadb7 > 15 40001 15 1 N B informix datadb8 > 16 40001 16 1 N B informix idxdbs1 > 17 40001 17 3 N B informix idxdbs2 > 18 40001 18 2 N B informix idxdbs3 > 19 40001 19 1 N B informix idxdbs4 > > Chunks > chk/dbs offset size free bpages flags pathname > 1 1 0 1000000 984577 PO-B /dev/vg00/elixir/rootdb > 2 2 0 500000 249947 PO-B /dev/vg00/elixir/phydbs > 3 3 0 10000000 9249947 PO-B /dev/vg00/elixir/logdbs > 4 4 0 2500000 2499897 PO-B /dev/vg00/elixir/tempdbs1 > 5 5 0 2500000 2499897 PO-B /dev/vg00/elixir/tempdbs2 > 6 6 0 2500000 2499897 PO-B /dev/vg00/elixir/tempdbs3 > 7 7 0 2500000 2499897 PO-B /dev/vg00/elixir/tempdbs4 > 8 8 0 10000000 0 PO-B /dev/vg00/elixir/datadb1 > 9 9 0 10000000 3830913 PO-B /dev/vg00/elixir/datadb2 > 10 10 0 10000000 2633307 PO-B /dev/vg00/elixir/datadb3 > 11 11 0 10000000 2709573 PO-B /dev/vg00/elixir/datadb4 > 12 12 0 10000000 3830913 PO-B /dev/vg00/elixir/datadb5 > 13 13 0 10000000 19 PO-B /dev/vg00/elixir/datadb6 > 14 14 0 10000000 3125 PO-B /dev/vg00/elixir/datadb7 > 15 15 0 10000000 4374729 PO-B /dev/vg00/elixir/datadb8 > 16 16 0 10000000 6433758 PO-B /dev/vg00/elixir/idxdbs1 > 17 17 0 10000000 261 PO-B /dev/vg00/elixir/idxdbs2 > 18 18 0 10000000 562711 PO-B /dev/vg00/elixir/idxdbs3 > 19 19 0 10000000 2878822 PO-B /dev/vg00/elixir/idxdbs4 > 20 17 0 2500000 288 PO-B /dev/vg00/elixir/idxdbs21 > 21 8 0 10000000 3425631 PO-B /dev/vg00/elixir/datadb12 > 22 17 0 10000000 6085694 PO-B /dev/vg00/elixir/idxdbs22 > 23 13 0 2500000 0 PO-B /dev/vg00/elixir/datadb61 > 24 18 0 2500000 1600906 PO-B /dev/vg00/elixir/idxdbs31 > 25 13 0 10000000 9146000 PO-B /dev/vg00/elixir/datadb62 > 26 14 0 10000000 9160827 PO-B /dev/vg00/elixir/datadb71 > > Based on our design, we follow best practices or not? We plan to move all > index table into 1 dbspaces from 4 dbspaces, can improve performance or > not? > Any document from IBM as references for us how to design our database > follow > best practices. Please help. > > Thank You > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1135fa6894f218053035a660

HI, "If you have multiple index dbspaces you can intelligently place indexes so that those that will be growing concurrently (so multiple indexes on a highly active table or indexes in related tables like a header/detail table pair) are placed on different dbspaces to prevent them from fragmenting each other more than necessary. " Means better we have multiple index dbspaces right? and can we identify/clarification table based on number of record. sample: table that have record more than 10 million in indexdbs1 and table that have record less thank 10 million in indexdbs2. Thank You

Yes, multiple index dbspaces is better than a single one. I would rather distribute the indexes such that indexes for tables which are accessed together (ie in the same query) are in different dbspaces so they do not contend with each other for IO bandwidth and head movement. So distribute intelligently not by size but by access patterns. 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 Tue, Apr 12, 2016 at 1:48 AM, MOHD FADZIL JUSOH <fadzil@isianpadu.com> wrote: > HI, > > "If you have multiple index dbspaces you can > > intelligently place indexes so that those that will be growing concurrently > > (so multiple indexes on a highly active table or indexes in related tables > > like a header/detail table pair) are placed on different dbspaces to > > prevent them from fragmenting each other more than necessary. " > > Means better we have multiple index dbspaces right? and can we > identify/clarification table based on number of record. > > sample: > > table that have record more than 10 million in indexdbs1 and table that > have > record less thank 10 million in indexdbs2. > > Thank You > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a113f91ae245b030530473b5a

And one more take, assuming the "object" was a full table - much what=20 Madion already had suggested (I guess): # Target database's systabels partnum: st=5Fpartnum=3D$(oncheck -pt target=5Fdatabase:systables | awk '/partnum/{p= rint=20 $NF}') # all deletes in systables, i.e. "DROP TABLE" commands for user tables onlog -t $st=5Fpartnum -n <oldest=5Flog=5Funiq>-<newest=5Flog=5Funiq> | egr= ep=20"HDELETE|log uniqid:" With the results of this you can go looking in 'onlog -l' output to=20 determine the content of each of these deleted systables rows, for finding = the name of your lost table. Once the right delete has been found, you'd go back to its transaction's=20 BEGIN record which would have the logged user name. Good luck! Andreas From: "Art Kagel" <art.kagel@gmail.com> To: ids@iiug.org Date: 12.04.2016 12:37 Subject: Re: IDS 12.10- BEST PRATICES Database Design [36962] Sent by: ids-bounces@iiug.org Yes, multiple index dbspaces is better than a single one. I would rather=20 distribute the indexes such that indexes for tables which are accessed=20 together (ie in the same query) are in different dbspaces so they do not=20 contend with each other for IO bandwidth and head movement. So distribute=20 intelligently not by size but by access patterns.=20 Art=20 Art S. Kagel, President and Principal Consultant=20 ASK Database Management=20 www.askdbmgt.com=20 Blog: http://informix-myview.blogspot.com/=20 Disclaimer: Please keep in mind that my own opinions are my own opinions=20 and do not reflect on the IIUG, nor any other organization with which I am = associated either explicitly, implicitly, or by inference. Neither do=20 those opinions reflect those of other individuals affiliated with any=20 entity with which I am affiliated nor those of the entities themselves.=20 On Tue, Apr 12, 2016 at 1:48 AM, MOHD FADZIL JUSOH <fadzil@isianpadu.com>=20 wrote:=20 > HI,=20 >=20 > "If you have multiple index dbspaces you can=20 >=20 > intelligently place indexes so that those that will be growing=20 concurrently=20 >=20 > (so multiple indexes on a highly active table or indexes in related=20 tables=20 >=20 > like a header/detail table pair) are placed on different dbspaces to=20 >=20 > prevent them from fragmenting each other more than necessary. "=20 >=20 > Means better we have multiple index dbspaces right? and can we=20 > identify/clarification table based on number of record.=20 >=20 > sample:=20 >=20 > table that have record more than 10 million in indexdbs1 and table that=20 > have=20 > record less thank 10 million in indexdbs2.=20 >=20 > Thank You=20 >=20 >=20 >=20 >=20 ***************************************************************************= ****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20 >=20 >=20 --001a113f91ae245b030530473b5a=20 ***************************************************************************= ****=20 Forum Note: Use "Reply" to post a response in the discussion forum.=20

Don't put your indexes in separate dbspaces, they will become a bottleneck Sent from my iPad > On 11 Apr 2016, at 14:01, MOHD FADZIL JUSOH <fadzil@isianpadu.com> wrote: > > Hi, > > We are plan to do database reorganize to follow best practices database design > from IBM to get best performance for our database. > > Our design as below: > (all dbspaces size 2K) > rootdbs - 1 > logdbs - 1, allocated log file > phydbs - 1, allocated physical log > datadbs - 7, allocated data > indexdbs - 4, allocated index table > tempdsb - 4, allocated tempdbs > > Refer our onstat -d ouput as below: > > IBM Informix Dynamic Server Version 12.10.FC6AEE -- online -- Up 00:00:01 -- > 12389868 Kbytes > > Dbspaces > number flags fchunk nchunks flags owner name > 1 40001 1 1 N B informix rootdb > 2 40001 2 1 N B informix phydbs > 3 40001 3 1 N B informix logdbs > 4 42001 4 1 N TB informix tempdbs1 > 5 42001 5 1 N TB informix tempdbs2 > 6 42001 6 1 N TB informix tempdbs3 > 7 42001 7 1 N TB informix tempdbs4 > 8 40001 8 2 N B informix datadb1 > 9 40001 9 1 N B informix datadb2 > 10 40001 10 1 N B informix datadb3 > 11 40001 11 1 N B informix datadb4 > 12 40001 12 1 N B informix datadb5 > 13 40001 13 3 N B informix datadb6 > 14 40001 14 2 N B informix datadb7 > 15 40001 15 1 N B informix datadb8 > 16 40001 16 1 N B informix idxdbs1 > 17 40001 17 3 N B informix idxdbs2 > 18 40001 18 2 N B informix idxdbs3 > 19 40001 19 1 N B informix idxdbs4 > > Chunks > chk/dbs offset size free bpages flags pathname > 1 1 0 1000000 984577 PO-B /dev/vg00/elixir/rootdb > 2 2 0 500000 249947 PO-B /dev/vg00/elixir/phydbs > 3 3 0 10000000 9249947 PO-B /dev/vg00/elixir/logdbs > 4 4 0 2500000 2499897 PO-B /dev/vg00/elixir/tempdbs1 > 5 5 0 2500000 2499897 PO-B /dev/vg00/elixir/tempdbs2 > 6 6 0 2500000 2499897 PO-B /dev/vg00/elixir/tempdbs3 > 7 7 0 2500000 2499897 PO-B /dev/vg00/elixir/tempdbs4 > 8 8 0 10000000 0 PO-B /dev/vg00/elixir/datadb1 > 9 9 0 10000000 3830913 PO-B /dev/vg00/elixir/datadb2 > 10 10 0 10000000 2633307 PO-B /dev/vg00/elixir/datadb3 > 11 11 0 10000000 2709573 PO-B /dev/vg00/elixir/datadb4 > 12 12 0 10000000 3830913 PO-B /dev/vg00/elixir/datadb5 > 13 13 0 10000000 19 PO-B /dev/vg00/elixir/datadb6 > 14 14 0 10000000 3125 PO-B /dev/vg00/elixir/datadb7 > 15 15 0 10000000 4374729 PO-B /dev/vg00/elixir/datadb8 > 16 16 0 10000000 6433758 PO-B /dev/vg00/elixir/idxdbs1 > 17 17 0 10000000 261 PO-B /dev/vg00/elixir/idxdbs2 > 18 18 0 10000000 562711 PO-B /dev/vg00/elixir/idxdbs3 > 19 19 0 10000000 2878822 PO-B /dev/vg00/elixir/idxdbs4 > 20 17 0 2500000 288 PO-B /dev/vg00/elixir/idxdbs21 > 21 8 0 10000000 3425631 PO-B /dev/vg00/elixir/datadb12 > 22 17 0 10000000 6085694 PO-B /dev/vg00/elixir/idxdbs22 > 23 13 0 2500000 0 PO-B /dev/vg00/elixir/datadb61 > 24 18 0 2500000 1600906 PO-B /dev/vg00/elixir/idxdbs31 > 25 13 0 10000000 9146000 PO-B /dev/vg00/elixir/datadb62 > 26 14 0 10000000 9160827 PO-B /dev/vg00/elixir/datadb71 > > Based on our design, we follow best practices or not? We plan to move all > index table into 1 dbspaces from 4 dbspaces, can improve performance or not? > Any document from IBM as references for us how to design our database follow > best practices. Please help. > > Thank You > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >