Large Database Problems

>>First, there is the lock table to worry about. The maximum number of >>locks that may be configured is 256k. When an ALTER TABLE is executed, >>a lock is created for *each* row, for *EACH* key. That is, if you have >>100k rows, with 3 keys, you get 300k keys (which would overflow your >>lock table, and cause disastrous results). > >This is completely untrue. ALTER TABLE does NOT require locks for every >row, let alone every row/key combination, in either the origin table or the >target table. Ok, let me explain further what has happened here, in the trenches. Under 4.0, if you tried to ALTER TABLE on a table with a row larger that a page, the table and keys were damaged. Believe me. I saw it happen with my very own little eyes. In fact, we found many disastrous errors when working with rows larger than a page (refer to case #151789), which ended in an emergency patch release. I may go overboard on being safe, but more than once, large production databases of ours have been munged, and tech supports' only answer is to dbexport the whole thing. So now, I am so freaked by running out of space or logs or locks (because I know firsthand of the ramifications) that I go the safest route. Flat out, I have a production table, with over a million rows. Are you absolutely saying, that under 4.0 or 4.1, I can put an exclusize lock on the table, alter it, and have no problems (logging tunred on)? What *is* the best way to stuff one column in this table? I know isql would barf because of locks. Is there some safe way short of writing a program that counts transactions? What about if I want to delete, say half a million of the rows at one time? What is the best procedure for that? I keep ending up with the same flavor of problems. In all fairness, each release is getting a little better. On the other hand, we have Informix running on many platforms, are releases are slooooowww, and we have to live with what we have today. >>You could put an exclusive lock on the table, causing only one lock >>entry. HOWEVER, then your transaction would be so long it would blow >>out your log files. The problem exists even if logging is set to /dev/null, >>since the one huge transaction *has* to fit in the log file. ^^^^ I missed one 's' > >Wrong, wrong, wrong. ALTER TABLE will automatically lock the origin >table; if anybody else is already using the table in a transaction, the >ALTER attempt fails with a -242/-113. It exclusively locks the target >table (the table it is building to eventually replace the table being >ALTERed). > >Also, a transaction *can* span log files. You just can't span your *entire >set* of log files with one transaction, as it would disallow a rollback. I didnt say it couldnt. I am talking about a system, with 200k rows in a table, and each row is a just under a page. We had 24, 1000 kbyte logs configured. If we tried to stuff a column in each row, we overflowed the lock table. We were told by Informix the scheme about needing one lock for each row, for each key. If thats wrong please tell me. I've seen it happen. We did not have room in the partition to created more or larger logs (at the time). We then tried exclusively locking the table, which resulted in overflowing our logs. That obviously wasnt going to work. All this was under 4.0, which did not behave well when logs were full, and lock tables overflowed. Sometimes, even though I hate it, I take down the entire application, turn off logging, and proceed carefully (after an archive). If I could get one table off an archive, I would be much happier. I hear we will soon be able to retreive a single dbspace. That will be a little better. >Not really -- this "sad saga" is fiction. Ok, Alan, give me a break here. You have a good reputation for knowing whats going on. We have seen disastrous results using Informix with large databases. Most of the the issues are performance related, like creating indicies on tables with over a million rows, and the like. I cringe when I think about routine maintenance on our new customer, that will have a 60 gigabyte database. Can you reassure me otherwise? Do you qa or benchmark large databases, or is Informix *really* suited for smaller applications? I dont claim to know everything about Informix. I *do* know quite a bit from supporting and developing for customers using your product, and have have more than my share of headaches, so be easy on us out here. -- Naomi Walker (aka N7FSA) naomi%anasaz.UUCP@asuvax.eas.asu.edu Enthusiasm is caught, What if, there were no hypothetical not taught. situations.......