Re: On-Line Perform. on RS6000

LMARKS@HANYS.ORG writes: > Good morning, from the finally warming up Northeast! > > We have been running INFORMIX On-Line (now vers. 5.01) on an IBM > RS6000 Model 930 for almost 4 years. We have been disappointed with > the performance from the beginning and have sought the advice of both > IBM and INFORMIX supplied consultants. None have been able to identify > and resolve our problems. > > I have noticed that immediately after we reboot the system, and with only > 1 individual logged in, anything run in INFORMIX completes very quickly. > As an example, we have a 2.5 million row, 850MB database. If we do a > select (*) from the db, and dump the results to /dev/null, we can read > through the file in about 40 minutes. But, after the system has been up > and running, and with other users logged in, performance degrades to, > at best, 2-3 hours for the same select, and at worst, can take 6-8 hours. > I realize that with a single processor machine, load is not handled well, > and some degradation occurs, but am wondering if there are "hidden or > invisible" things, like daemons, memory eaters, cpu hogs, processes > hung and still running, or other things that might be causing our extreme > degradation. > > We use both IBM bundled utilities and tbcheck and can't "see" anything. > > Any ideas, suggestions? > > Thanks. First I would analysis the 40 minute run in isolation. After a reboot and starting up Informix do the following: 1. iostat 30 1000 > iostat_1 # Record disk load during run. 2. vmstat 30 1000 > vmstat_1 # Record system info 3. tbstat -up -r 30 > tbstat_1.up # Record informix stats 4. tbstat -z # Zero informix's statistics 5. Run the query. 6. Stop the tbstat and iostat if still running. Then run the same test during a normal work day changing the "_1"s above into "_2"s and stopping tbstat and iostat when the query completes. Also during both runs use ps to get the pid of the sqlturbo process associated with the running query so I can match it up with tbstat -u. Send these files to me along with the following: 1. How much memory does the system have. 2. How many disk drives do you have and what are the sizes of each. 3. Output from a tbstat -a Optionally 4. How is the database layed out PHYSICALLY on the disks. Yes, I know this is a difficult question because of AIX's logical partitions but there are ways to determine this. Don't let this hold you up from getting the other info to me. There are 3 reasons for slow performance on a system: 1. Inefficient code(Or an SQL query in this case). I am not investigating this possiblity here because you have asked why it goes from :40 to several hours not why it take :40 minutes. 2. An overloaded system. If analysis proves that this is the case that the only solution is getting a bigger, better and more powerful system. BUT BE CAREFUL, I have seen many vendors sell bigger is better as the solution, when the problem was really item #3 below. 3. Resource bottleneck. This is where the art of performance analysis lies. The log files I have asked for should tell the story. -- Daniel Wood daniel@starsight.com (510/657-9900 x2121) The opinions expressed herein are those of myself, my employers, the carriers of this news message or any other messages for that matter, you the reader and any of your relations(whether by birth or chromosome count), all life forms and all inanimate objects.