Other errors

[sid]

League 7 is doing an expansion draft but only the last 2 picks are showing on the draft page, not sure why.

If you want to check it out, one of the CIDs is 712.

[sid]

Another Issue, I don't know if you and I are "stepping" on each other but yesterday and today, the server went to never-never land. I just want to make sure that when you are "restarting" the production server you're issuing ENDEFSPRD waiting for it to stop then issuing the STREFSPRD.

If you are doing this I need to open a problem record with IBM. There is something locking up that server.

[sid]

Rock did you add code to your draft driver to post dollars to picks, if so it is busted. Let me know cuz it could be my code. I am looking now.

One more note regarding Jason's post, not only is it wrong, it is eating our lunch in the WAS server, CPU is up in the high 90% range.

Thanks,
sid

[paladisiac]

Regarding "posting dollars to picks" when drafting -- that code has been around a while unchanged through vet and rookie and expansion drafts. I can take a look if you want me to -- but please tell me -exactly- what i'm looking for, as well as a CID/LID/PID example.

Regarding "90% CPU usage" -- i've seen this. This is why i told you that something was amiss with the server, the app server or in the code. I scoured the code yesterday and found very minor possible improvements. I also did research on web app server settings and increased the connection pool size last nite. (That should be in affect now.) Hopefully that helps.

The only other idea i have involves a large redesign of all pages' functionality.

i think it's all tied to RFA. In League 666 there were about 180 RFA players to begin with. At about 5-to-10 queries per player, that's possible 1000+ queries per page load, plus the logical overhead to accumulate the data that is displayed on the rfa bid list (find player) page.

If you've been thinking of calling IBM to look at the iseries and the web app server settings, that might not be a bad idea. This problem's only going to rear its ugly head at the beginning of ufa, the beginning of rfa and the beginning of ps bidding, so if we want it to solve it so that it's not a problem next year, we have to do it soon.

[sid]

Ship me the sql code so I can take a look, maybe I can help speed it up.

[paladisiac]

Ship me the sql code so I can take a look, maybe I can help speed it up.

It's just find player code that you've already worked on. Here's an example:

SELECT owpl2.PID, owpl2.PFIRST, owpl2.PLAST,
owpl2.PPOS, owpl2.PSPOS, owpl2.TOW, owpl2.TTEAM,
owpl2.FINJURED, owpl2.PSQUAD, owpl2.PSQUADE,
owpl2.CONTRACT, owpl2.SALARY, owpl2.ORC,
owpl2.OID, owpl2.TID, owpl2.FRANPLAYER,
owpl2.INJURED, owpl2.GCONTRACT, owpl2.PRANK,
owpl2.RFAPK, owpl2.AVGPTSIN, owpl2.AVGPTSOUT,
owpl2.ROOKYEAR, owpl2.LCRDOID
FROM TEFSFDTA.FFSOWPL2 owpl2
WHERE owpl2.LID = '000666' AND owpl2.RYEAR = 2006
AND owpl2.TID != '9999' AND owpl2.OID != ' '
AND owpl2.OID != ' ' AND owpl2.CONTRACT <= 0
AND owpl2.SYEAR = 2006 AND owpl2.SCRID = '9992'
ORDER BY owpl2.PPOS, owpl2.PSPOS, owpl2.AVGPTSIN DESC

[sid]

You said you were doing 10+ this sql is fast. This cannot be the only issues.

If that is the only query, then something is bad busted. Makes no sense.

send me all your sql code. that cannot be all of it.

[paladisiac]

That is not all of it. There are around 7 other queries in the same loop. I didn't have time to send you all the queries. All the other queries are actually quicker than that one. In fact, the summation of all the queries for each player queries is LESS THAN a second! However, when there are 180 RFA players that comes to less than 3 minutes, which is the problem. I think it's the web app server or java code that is adding processing overhead. I'm trying to isolate the reason.

[paladisiac]

and today even cgi pages are loading slower than usual. system seems like it's struggling.

i'm having trouble just logging in to the admin console. itsss soooo slooooww.

i think my conn pool changes might've made this worse. whenever i can access that page, i'm going to reset it back down to 30 max 5 min.

[sid]

Best I can tell, DB capacity is the issue, somewhere in these SQLs there is a bust, we seldom run above 15% on DB issues, today I saw 98% cpu and 86% DB. It has to be the SQL sorry. This would explain the fact that both CGI and Java are busted. We gota find the indexes that are missing from the DB.

Plus your note may be correct, lowering the connections may be better than raising them.

Also I am downloading the latest WAS and i5/OS patches, I will load them on Saturday or Sunday. I do not expect this to help but it should not hurt.

[paladisiac]

i've been reading a lot of ibm websphere documentation lately and will continue to install db-related "fixes" based on what i read. They're not fixes as much as better ways to do things. Little stuff here-and-there that i hope helps because i don't see anything that jumps out as a problem.

[sid]

1) the connection pools setting is too high, a lot of jobs are running but little is getting done. count the # of QZDASOINIT jobs way high.

2) I noticed that the quries are locking a huge amount of records then issuing a huge rollback operatioin. I dont know why that is happening, but it is killing the run time probably.

3) if we cannot get this fixed we got put it back and convert to jni.

The server is locking up 2 or 3 times a day and killing the site.

[paladisiac]

01) i put the connection pool setting back DOWN where YOU had it. So don't mess with it.

02) I might've found the query that was hosing us. i want to install a fix for it now.

03) The stuff i'm doing now can't be converted to jni without a lot of more work on my part.

[sid]

Well what ever is going on just locked up the entire system, not just java or http the whole fing system. I headed to the office to fix it. IF we dont have a clear answer by tomorrow night we MUST put it back or I will be forced to close efs during the day, I cannot have our partner that pays half of the bill shut down during tax season.

Sorry

[paladisiac]

the RFA code was in LAST YEAR. Something happened between then and now to cause this problem. i THINK i found it and will install it as soon as i can, but i'm running out of the kid's naptime window so it probably won't be for around 3 hours from now.

(And i wonder what you expect i'd put back. There really isn't anything specific but maybe the player page.)

[sid]

the old code...

Plus, I can open a problem with IBM, but it could take days to figure out. As I said if it's not fixed by Monday morning, I'm killing efs during the day. It's their busiest part of the year all the email/dns/http servers run there. So them's the facts.

I have the system back up, put your code in after nap time.

[paladisiac]

i found two queries using left outer joins that weren't fully-keyed, one with a typo that basically put the entire conditional in the outer join and not the main select. It was probably querying the universe. Sorry. Hopefully (fingers welded crossed) we're ok now.

[sid]

Rock, I am trying to be helpful, but it seems to me that sometimes you cut me out of the loop. I am ready willing and able to work on the sql stuff, I am certified on both DB2/i5 and DB2 Enterprise (read Linux/S390/AIX/UNIX). Keep in mind that cert does not mean good, but I have a piece of paper and 25 years on DB2.

Let me help!

Also, I did open a PMR (Problem Management Record) with IBM. After discussing the problem, they recommend in cases where the connection pool gets "buried", meaning too many Queries, always lower the number first. So my advice to raise it was dead wrong.

:>)

[paladisiac]

i wasn't trying to cut you out of the loop. i was trying to save you time. there are a ton of queries i looked through in order to find these 2 (where 1 was pretty bad, the other so-so) that had problems. If i sent you every query to look at that'd waste both of our time, me to copy them one-at-a-time & send them to you, and you to go through each one of them. That probably would've taken days longer than this has.

Now if you were coding Java with me, we could've doubled the effort. ;) Remember that for baseball...

It's looking good so i think that was the problem. rfa bid list loads in about 10 sec now. In the process of looking for the real problem, i corrected a bunch of little things in the process. So some added benefits to this whole mess.

[sid]

I would still like to see the one that was #1 on the hit list, Maybe I can add some keys to make it or others faster.....

My personal opinion, on a DB this size with our Iron, in most cases we should run sub 3 seconds.

Thanks, for your effort.

In May make time for me to watch how you load up your java code make changes etc, I did make one class, but they did not teach it the way you do it. Some Saturday and Sunday for say 3 hours a day, I want to go thru what you do with eclips.

[paladisiac]

Don't know what you mean by the "#1 on the hit list". i already emailed you the main query of find player.

Here's the fixed sql for the one that was causing problems:

SELECT rfa.PID, rfa.BDOLLARS, rfa.BCONT, rfa.EXTAMOUNT,
rfa.RFABTS, rfa.RFABLOCK, rfa.BOID, own.OTNAME, draft.PID
FROM PEFSFDTA.FFPOWNR own, PEFSFDTA.FFPRFRE rfa
LEFT OUTER JOIN PEFSFDTA.FFPPICK AS draft
ON rfa.PID = draft.PID AND rfa.LID = draft.LID
WHERE rfa.LID = '000666' AND rfa.LID = own.LID
AND rfa.BOID = own.OID AND rfa.PID = '1937'
ORDER BY rfa.EXTAMOUNT DESC

[sid]

What was the original busted one?

[paladisiac]

What was the original busted one?

Don't run it!!:

SELECT rfa.PID, rfa.BDOLLARS, rfa.BCONT, rfa.EXTAMOUNT,
rfa.RFABTS, rfa.RFABLOCK, rfa.BOID, own.OTNAME, draft.PID
FROM PEFSFDTA.FFPOWNR own, PEFSFDTA.FFPRFRE rfa
LEFT OUTER JOIN PEFSFDTA.FFPPICK AS draft
ON rfa.PID = draft.PID
AND rfa.LID = '000666' AND rfa.LID = own.LID
AND rfa.BOID = own.OID AND rfa.PID = '1937'
ORDER BY rfa.EXTAMOUNT DESC