Database Setup

Hello all, Here are a few more questions to all Informix gurus from an Informix newbie. My company has purchased a new enterprise wide software package. It is using an Informix database (IDS 7.3) and the software is written in Informix 4GL. For a little background: We are a medium to smaill size company with a small IT department. Basically myself and one other person, along with a little help from others, are responsible for all things related to computers whether it be UNIX admin, NT admin, etc. and now Informix DBA. We are coming from an environment in which the software is written in Business BASIC so we are new to RDBMS in general and Informix specifically. As we try to get up to speed on Informix we are relying on the knowledge of the people from the software company. To this point my knowledge of Informix consists of reading a few books, doing some CBT, and taking one class, Managing and Optimizing IDS databases. In our situation the software company came in, installed Informix, initialized the instance, and then ran an install script which setup the database and all the tables. After taking the Informix class we came back to work, and using tools like dbaccess and dbschema we examined our database to see how, and if, the priciples we had learned in class were applied to our database. Much to our surprise we found the following areas lacking: 1. No constraints defined (primary key, foreign key, check, etc) There are some indexes defined. 2. Indexes not defined in their own index space. 3. All tables created with the default extent and next extent size of 16k. We have pointed out these areas that we find lacking to the powers to be within our company but as of yet we haven't gotten very far. We want to pursue it further but we don't want to go in just saying "We should setup the database this way because that's what they told us in class" We are looking for real world evidence to go into a meeting and say things like: "These are the problems we are going to run into unless we change the structure of the database", and then run down a list of potential problems. I guess my questions are a) Do we have a reason to be concerned?, b) If so, can anyone help us provide evidence or better reasoning as to why we should be concerned? Thanks, John Welch Systems Analyst Brockway-Smith Co.

John Welch wrote: > Hello all, > Here are a few more questions to all Informix gurus from an Informix > newbie. > > My company has purchased a new enterprise wide software package. It > is using an Informix database (IDS 7.3) and the software is written in > Informix 4GL. > For a little background: We are a medium to smaill size company with a > small IT department. Basically myself and one other person, along > with a little help from others, are responsible for all things related > to computers whether it be UNIX admin, NT admin, etc. and now Informix > DBA. We are coming from an environment in which the software is > written in Business BASIC so we are new to RDBMS in general and > Informix specifically. As we try to get up to speed on Informix we > are relying on the knowledge of the people from the software company. > To this point my knowledge of Informix consists of reading a few > books, doing some CBT, and taking one class, Managing and Optimizing > IDS databases. > In our situation the software company came in, installed Informix, > initialized the instance, and then ran an install script which setup > the database and all the tables. After taking the Informix class we > came back to work, and using tools like dbaccess and dbschema we > examined our database to see how, and if, the priciples we had learned > in class were applied to our database. Much to our surprise we found > the following areas lacking: > 1. No constraints defined (primary key, foreign key, check, etc) > There are some indexes defined. > 2. Indexes not defined in their own index space. > 3. All tables created with the default extent and next extent size of > 16k. That's faily normal. Purchased packages always seem to suck. My company bought a package for god knows how much money, and it doesn't even come CLOSE to _FIRST_ normal form. Makes me write some of the funkiest SQL you've ever seen... > > We have pointed out these areas that we find lacking to the powers to > be within our company but as of yet we haven't gotten very far. We > want to pursue it further but we don't want to go in just saying "We > should setup the database this way because that's what they told us in > class" We are looking for real world evidence to go into a meeting > and say things like: "These are the problems we are going to run into > unless we change the structure of the database", and then run down a > list of potential problems. > I guess my questions are a) Do we have a reason to be concerned?, b) > If so, can anyone help us provide evidence or better reasoning as to > why we should be concerned? > To the first question, maybe. It depends on what kind of usage the database has. However, most likely, yes, you will have a problem. Performance will quickly begin to degrade with large influxes of data. Adding constraints to a prepackaged piece of software might be a bad idea. Without extensive research into what the code is REALLY doing, those constraints could screw stuff up. 'Some indexes defined'? There should be at least one on every table... (in most cases, anyway.) Do you have access to the SQL involved in the code? If not, it could be difficult to figure out how to index the tables... Adding indexes can't hurt the application (meaning 'make it stop working all together), but it could hurt performance on inserts, updates, and deletes. Try to figure out what fields are used in equality filters in the application. This can be done using 'onstat -g ses', but it's a pain in the ass. You could also use the database management software that came with your engine. The engine decides on what index to use, not the app, unless the app tells the engine to use specific indexes (only available on version 7.3+), but if there aren't many indexes there, the app can't very well be doing that... The extent size problem is a little more problematic. Without letting the app run for a while and watch to see which tables are growing by how much, there isn't a whole lot you can do. Give it a month, then check back. Hope this is some help... > > Thanks, > John Welch > Systems Analyst > Brockway-Smith Co.

On Fri, 18 Aug 2000 03:11:28 GMT, Daniel Marshall <dtmarshall@worldnet.att.net> wrote: > >The extent size problem is a little more problematic. Without letting the >app run for a while and watch to see which tables are growing by how much, >there isn't a whole lot you can do. Give it a month, then check back. > I don't agree with that. That can (and will) become problem. After one month user must unload data, drop tables, recreate tables with real extent size and load data back - do reorganization. Some tables will be static - thay will not grow sigificantly in the future (customer table for example) but some others (invoice table for ex.) will grow every month. Do you know which tables are 'static' and which are not? You can change NEXT EXTENT SIZE (ALTER TABLE ...) of growing tables but you still don't know which are growing tables. If you will not do reorg. or change next extent size, 'not static' tables will have a lot of extents after some time. It will degrade perfomace. You can expect, when number of extents become really large, that INSERTs in those tables can not be done (error -136). How large is that number depends on table row size. Everything about that you can read in Performace Guide Chapter 4. And that is not just theory.

> If you will not do reorg. or change next extent size, 'not static' tables will > have a lot of extents after some time. It will degrade perfomace. What evidence do you have for this? It's true that older versions of OnLine did suffer when the number of extents was greater than 8, but I didn't think it was such a problem now, save for the poor page filling implicit in a multiple-extent table. I'd go with Daniel's "wait and see" suggestion. To addreess the original question, such a set-up is depressingly common with bought-in pakages. The vendor, who often knows very little about Informix, provides straight-out-of-the-manual advice to the client. We have a bought-in warehousing package at my client's, running on v9.20, and I've lost count of the number of times I'm given long quotes straight from the OnLine 5.2 manual about disk placement, extents etc. etc. Perhaps I'm biased 'cos I are one, but I think a little money spent on a month of a contractor, perhaps over 2-3 months, would be money well spent if the application is important.

On Fri, 18 Aug 2000 09:07:42 +0100, "Neil Truby" <ntruby@netcomuk.co.uk> wrote: >> If you will not do reorg. or change next extent size, 'not static' tables >will >> have a lot of extents after some time. It will degrade perfomace. > >What evidence do you have for this? It's true that older versions of OnLine >did suffer when the number of extents was greater than 8, but I didn't think >it was such a problem now, save for the poor page filling implicit in a >multiple-extent table. I'd go with Daniel's "wait and see" suggestion. > >To addreess the original question, such a set-up is depressingly common with >bought-in pakages. The vendor, who often knows very little about Informix, >provides straight-out-of-the-manual advice to the client. We have a >bought-in warehousing package at my client's, running on v9.20, and I've >lost count of the number of times I'm given long quotes straight from the >OnLine 5.2 manual about disk placement, extents etc. etc. > >Perhaps I'm biased 'cos I are one, but I think a little money spent on a >month of a contractor, perhaps over 2-3 months, would be money well spent if >the application is important. > From Performance Guide for Informix Dynamic Server Version 7.3 February 1998 Part No. 000-4357 Capter 4 Table and Index Performance Considerations ---------------------------------------------------------------------------------------------- Managing Extents As you add rows to a table, Dynamic Server allocates disk space to it in units called extents. Each extent is a block of physically contiguous pages from the dbspace. Even when the dbspace includes more than one chunk, each extent is allocated entirely within a single chunk so that it remains contiguous. Contiguity is important to performance. When the pages of data are contiguous, disk-arm motion is minimized when the database server reads the rows sequentially. .... --------------------------------------------------------------------------------------------- Upper Limit on Extents Do not allow a table to acquire a large number of extents because an upper limit exists on the number of extents allowed. Trying to add an extent after the limit is reached causes error -136 (No more extents) to follow an INSERT request. The upper limit on extents depends on the page size and the table definition.... ---------------------------------------------------------------------------------------------- So, about performace degradation no evidence. About Upper Limit on Extents one evidence - own experience.

Do you have a web-server set up on the data base machine? Install a web-server if you can, and I'd even recommend the Informix Server Administrator tool, it takes 5 minutes to install. Even the most inexperienced person can install it. I normally don't recommend software that installs its own Perl and Apache software, but I'd be willing to bet your system doesn't have either. Once you get ISA installed you can run reports and monitor the engine from your web browser. Once you get familiar with ISA you might even try a little tool for engine reporting I wrote, WDBA, available over at my web site, which produces nice little HTML reports about your engine. It has an extents report that was written based on an article I read in Informix Tech Notes. In fact a lot of the stuff I build is written like that. :-) Good luck to you, Tim -- . .- .-- .--- .---- Tim Schaefer .----- tschaefe@bellsouth.net .---- http://www.inxutil.com .--- InxUtil: 214 Subscribers & growing .-- .- . John Welch wrote: > > Hello all, > Here are a few more questions to all Informix gurus from an Informix > newbie. > > My company has purchased a new enterprise wide software package. It > is using an Informix database (IDS 7.3) and the software is written in > Informix 4GL. > For a little background: We are a medium to smaill size company with a > small IT department. Basically myself and one other person, along > with a little help from others, are responsible for all things related > to computers whether it be UNIX admin, NT admin, etc. and now Informix > DBA. We are coming from an environment in which the software is > written in Business BASIC so we are new to RDBMS in general and > Informix specifically. As we try to get up to speed on Informix we > are relying on the knowledge of the people from the software company. > To this point my knowledge of Informix consists of reading a few > books, doing some CBT, and taking one class, Managing and Optimizing > IDS databases. > In our situation the software company came in, installed Informix, > initialized the instance, and then ran an install script which setup > the database and all the tables. After taking the Informix class we > came back to work, and using tools like dbaccess and dbschema we > examined our database to see how, and if, the priciples we had learned > in class were applied to our database. Much to our surprise we found > the following areas lacking: > 1. No constraints defined (primary key, foreign key, check, etc) > There are some indexes defined. > 2. Indexes not defined in their own index space. > 3. All tables created with the default extent and next extent size of > 16k. > We have pointed out these areas that we find lacking to the powers to > be within our company but as of yet we haven't gotten very far. We > want to pursue it further but we don't want to go in just saying "We > should setup the database this way because that's what they told us in > class" We are looking for real world evidence to go into a meeting > and say things like: "These are the problems we are going to run into > unless we change the structure of the database", and then run down a > list of potential problems. > I guess my questions are a) Do we have a reason to be concerned?, b) > If so, can anyone help us provide evidence or better reasoning as to > why we should be concerned? > > Thanks, > John Welch > Systems Analyst > Brockway-Smith Co.

John, ask yourself this, "why do you think you should be concerned?" Certainly, these may be valid questions to ask the application vendor, but first consider why these topics were convered in the class you took and try to determine if the same situations will apply to you. For example, constraints may not be neccessary - the application may handle all that either with field edits or processing logic. A "tight" primary and foreign key relationship can be a real pain and once again, the application can be handling this on it's own. Index location and extents sizes become issues where record volume is a factor. Before you cry "wolf", make sure!

At the same time, though, remember that there are usually some "power" users that know how to log in to a unix box and use dbaccess to modify data directly. Or maybe they know how to set up ODBC and use Microsoft Access or excel, or any number of other products. Fred Prose wrote: > John, ask yourself this, "why do you think you should be concerned?" > Certainly, these may be valid questions to ask the application vendor, but > first consider why these topics were convered in the class you took and try > to determine if the same situations will apply to you. > > For example, constraints may not be neccessary - the application may handle > all that either with field edits or processing logic. A "tight" primary and > foreign key relationship can be a real pain and once again, the application > can be handling this on it's own. Index location and extents sizes become > issues where record volume is a factor. > > Before you cry "wolf", make sure!

On Fri, 18 Aug 2000 00:57:15 GMT, jrw3319@gis.net (John Welch) wrote: >Hello all, > Here are a few more questions to all Informix gurus from an Informix >newbie. >... First, I want to thank everyone who responded to my question. Now for a couple of follow up questions/comments: Of the three areas of concern that I highlighted, the lack of constraints is the least of my worries. I know for a fact that validation is being done within the 4GL code. I guess in moving to a Relational Database system I thought that one of the primary things that made it "relational" was the definition of primary and foreign keys. As far as the extent size, one of the reasons for my concern is in the conversion of data from our existing system to the new system. Some of the files that we will be converting have 10's of thousands, and in some cases 100's of thousands (i.e. history files) records. So my concern is that right of the gate, after converting data, we will already have several extents for some tables. For example, our item file has approixmately 20,000 records. The row size for one of the item tables in the new system is 359. Using a formula I learned in class, if I did the math right, I come up with an extent size of around 6MB for this table. If we leave the extent size as is (16k) I figure that right after the conversion we will have over 300 extents for this one table, if my logic is correct. This same type situation exists for several tables in the new database. Considering this information, should I still go with the wait and see approach or is it worth going through the exercise of calculating and changing extent sizes prior to conversion? Finally, most of the responses focused on extent sizes. How about the issue of defining a separate space for indexes? Is this even worth pursuing, or is this one of those "in theory" only areas. Thanks again! John Welch Systems Analyst Brockway-Smith Co.

I agree with your take on "Relational". Seems a shame to pay for a database and not use it. Your estimation for initial table size is pretty good. I would also try and allow for expected future growth of tables. If you have any idea about how many records are added, I would add some to allow for this growth. However, out of the gate, you should be ok. If when an extent is added it is physically contiguous with the previous extent, informix considers that 1 extent. It is called extent concatenation. During the load of a table, if it is done serially with dbimport or dbload, all the data for one table is loaded at once, so extent concatenation will occur. At worst you'll have two exents per table (one because an extent is allocated (maybe at 16k) when you create the table, and then the second when table data starts getting loaded. As for extent spaces, generally the answer is yes. However, the performance improvement is scaled to the size of the table. The size of the tables you described I would consider a rather small database. If the engine is configured correctly, and the system is relatively decent (not a 486 with 32mb of memory), you probably won't see a drastic improvement. With a terabyte system, it could mean the difference of minutes or hours in improvement. Hope this helps. John Welch wrote: > On Fri, 18 Aug 2000 00:57:15 GMT, jrw3319@gis.net (John Welch) wrote: > > >Hello all, > > Here are a few more questions to all Informix gurus from an Informix > >newbie. > >... > > First, I want to thank everyone who responded to my question. > Now for a couple of follow up questions/comments: > > Of the three areas of concern that I highlighted, the lack of > constraints is the least of my worries. I know for a fact that > validation is being done within the 4GL code. I guess in moving to a > Relational Database system I thought that one of the primary things > that made it "relational" was the definition of primary and foreign > keys. > As far as the extent size, one of the reasons for my concern is in > the conversion of data from our existing system to the new system. > Some of the files that we will be converting have 10's of thousands, > and in some cases 100's of thousands (i.e. history files) records. So > my concern is that right of the gate, after converting data, we will > already have several extents for some tables. For example, our item > file has approixmately 20,000 records. The row size for one of the > item tables in the new system is 359. Using a formula I learned in > class, if I did the math right, I come up with an extent size of > around 6MB for this table. If we leave the extent size as is (16k) I > figure that right after the conversion we will have over 300 extents > for this one table, if my logic is correct. This same type situation > exists for several tables in the new database. Considering this > information, should I still go with the wait and see approach or is it > worth going through the exercise of calculating and changing extent > sizes prior to conversion? > Finally, most of the responses focused on extent sizes. How about > the issue of defining a separate space for indexes? Is this even > worth pursuing, or is this one of those "in theory" only areas. > > Thanks again! > > John Welch > Systems Analyst > Brockway-Smith Co.

John Welch wrote in message <399e9bb8.52863266@news.gis.net>... >On Fri, 18 Aug 2000 00:57:15 GMT, jrw3319@gis.net (John Welch) wrote: > >>Hello all, >> Here are a few more questions to all Informix gurus from an Informix >>newbie. >>... > >First, I want to thank everyone who responded to my question. >Now for a couple of follow up questions/comments: > > Of the three areas of concern that I highlighted, the lack of >constraints is the least of my worries. I know for a fact that >validation is being done within the 4GL code. I guess in moving to a >Relational Database system I thought that one of the primary things >that made it "relational" was the definition of primary and foreign >keys. > As far as the extent size, one of the reasons for my concern is in >the conversion of data from our existing system to the new system. >Some of the files that we will be converting have 10's of thousands, >and in some cases 100's of thousands (i.e. history files) records. So >my concern is that right of the gate, after converting data, we will >already have several extents for some tables. For example, our item >file has approixmately 20,000 records. The row size for one of the >item tables in the new system is 359. Using a formula I learned in >class, if I did the math right, I come up with an extent size of >around 6MB for this table. If we leave the extent size as is (16k) I >figure that right after the conversion we will have over 300 extents >for this one table, if my logic is correct. This same type situation Nope, because of - extent doubling every 16 extents - merging of adjacent extents - index pages are not included in the row size - What happens if the vendor adds columns/indexes before you go live / shortly after you go live? Usally after we go live a customer starts searching in an unexpected way and new indexe have to be added. Wait and see. Also the reorg is NOT that hard. Take a level 0 archive Turn off transaction logging alter table bob next size <x> alter fragment on table bob init in <dbspace> No unloading/loading/indexing required. >exists for several tables in the new database. Considering this >information, should I still go with the wait and see approach or is it >worth going through the exercise of calculating and changing extent >sizes prior to conversion? > Finally, most of the responses focused on extent sizes. How about >the issue of defining a separate space for indexes? Is this even >worth pursuing, or is this one of those "in theory" only areas. > >Thanks again! > >John Welch >Systems Analyst >Brockway-Smith Co. >

I think you need to consider the size of the db and the number of users before you go off and spend lots of time tuning a db/server. The answers to tuning questions differ greatly on a practical level for 5gig/5user db's as opposed to 500gig/5000user db's. If you have a relatively small db, then I wouldn't spend a lot of time trying to implement everything you learned in class. If performance is acceptable (I know, I know, but you need to define what ACCEPTABLE performance levels are for you in this situation), then follow the KISS rule. John Welch wrote: > On Fri, 18 Aug 2000 00:57:15 GMT, jrw3319@gis.net (John Welch) wrote: > > >Hello all, > > Here are a few more questions to all Informix gurus from an Informix > >newbie. > >... > > First, I want to thank everyone who responded to my question. > Now for a couple of follow up questions/comments: > > Of the three areas of concern that I highlighted, the lack of > constraints is the least of my worries. I know for a fact that > validation is being done within the 4GL code. I guess in moving to a > Relational Database system I thought that one of the primary things > that made it "relational" was the definition of primary and foreign > keys. > As far as the extent size, one of the reasons for my concern is in > the conversion of data from our existing system to the new system. > Some of the files that we will be converting have 10's of thousands, > and in some cases 100's of thousands (i.e. history files) records. So > my concern is that right of the gate, after converting data, we will > already have several extents for some tables. For example, our item > file has approixmately 20,000 records. The row size for one of the > item tables in the new system is 359. Using a formula I learned in > class, if I did the math right, I come up with an extent size of > around 6MB for this table. If we leave the extent size as is (16k) I > figure that right after the conversion we will have over 300 extents > for this one table, if my logic is correct. This same type situation > exists for several tables in the new database. Considering this > information, should I still go with the wait and see approach or is it > worth going through the exercise of calculating and changing extent > sizes prior to conversion? > Finally, most of the responses focused on extent sizes. How about > the issue of defining a separate space for indexes? Is this even > worth pursuing, or is this one of those "in theory" only areas. > > Thanks again! > > John Welch > Systems Analyst > Brockway-Smith Co.