Re: Large Database Problems (LONG)

Disclaimer: these are my opinions only; I alone am responsible for my comments below. In article <9176@emory.mathcs.emory.edu> anasaz!qip.naomi@enuucp.eas.asu.edu (Naomi Walker) writes: >>>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. This "emergency patch release" was to fix a *performance* (excess disk space consumption) problem, not an integrity problem (according to the casenotes -- I didn't work on your case firsthand, obviously. The only stated bug was #11581: ONLINE DOES NOT USE REMAINDER PAGES FOR MORE THAN ONE ROW.) As for "tables and keys were damaged", I don't know what to say. There's no other such reports that I can find. >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. If your data is "munged", how on Earth is exporting it going to help? >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)? I'm saying you won't have LOCK problems. Actually, ALTERing the table exclusively locks it automatically anyway. Of course, you can still run out of LOG space, as the inserts are logged. >What *is* the best way to stuff one column in this table? I know isql >would barf because of locks. Again, completely untrue. > 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. Either delete some smaller number per transaction over several transactions, or build a new table with just the rows you want to keep -- it depends on how many rows you are keeping. (With SORTINDEX, the latter option would probably be preferable in most cases.) >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, ^^^^^^^^^^^^^^^^^^^^^^^^^^^ Slow? Your opinion. Here's mine. First contact on that problem was 11/25, it took some effort to diagnose/reproduce over the next couple of weeks, it was finally isolated on 12/9, and a prerelease version with the 11581 fix on your platform shipped 12/13. And this was a performance problem, not a corruption or crash or inability to run. Jeez. >and we have to live with what we have today. >... >>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. It's wrong. If the lock table overflowed, it wasn't due to an ALTER. OnLine has NEVER worked that way. >We then tried exclusively locking the table, which resulted in overflowing >our logs. That obviously wasnt going to work. Locking your table overflowed your logs? No. Locking the table had no effect, as ALTER locks it anyway. You just had too little log space for what you wanted to do. Locks had nothing to do with it. >>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 What irked me about your post is that is was more disinformation than anything else, and it came *after* I (and perhaps others) had already informed you of the errors. >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. Are they on a platform with SORTINDEX capability? Many 4.10 versions have it, and all 5.0 versions do. >Can you reassure me otherwise? Do you qa or benchmark large databases, >or is Informix *really* suited for smaller applications? Oh, man. What I wouldn't like to say here. I'll just say this. First of all, I think your attempt here to make it look like OnLine can't handle large systems because you had problems using it is really lacking in class. Two, yes I have benchmarked large databases -- for example, I worked on a benchmarking project with YOUR OWN COMPANY when I was in the consulting organization. Nowadays, there are a dozen or more Informix consultants that know more about such performance issues than you and I combined. >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. Just check your facts BEFORE posting such severe indictments, and I won't be bothered a bit. Keep in mind that your company is not only a customer of ours, but (it seems to me) a competitor on the services side. Therefore, any repeated, inaccurate criticisms could be perceived as more than just simple misstatements. Just a thought. > Naomi Walker (aka N7FSA) naomi%anasaz.UUCP@asuvax.eas.asu.edu > > Enthusiasm is caught, What if, there were no hypothetical > not taught. situations....... -- Alan Denney aland@informix.com {pyramid|uunet}!infmx!aland Disclaimer: These opinions are mine alone. If I am caught or killed, the secretary will disavow any knowledge of my actions. "In the cafeteria just after lunch, (well, not *just* after, more like *during* lunch, about 12:28; say 12:30, give or take a few minutes), I leaned back in my chair (it was one of those aluminum chairs, good strength-to