Performace issues
A new DBA running Informix 7.31.FC6 on HP-UX 11.11 with a 1TB+ database asked, in very general terms, what to check and tune when users report slow queries, especially right after startup. Replies noted the question was far too broad: one joked the engine simply needs time to cache data; Art Kagel listed hardware/configuration details and a long set of onstat outputs (-p, -d, -D, -g iof/iov, -c, -C, -g glo, -l, -R, -F, -m, -g rea) to post, plus IIUG conference papers and training. Others pointed to an IBM Redbook and suggested UPDATE STATISTICS, more buffers, and upgrading from the unsupported 7.3. No specific problem or fix was identified.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi to all, I am handling informix 7.31 FC6 (more than 1 TB size)on HP-UX11.11. I am new to informix database administration. I have a few questions: 1) If a user complains that his queries are going slow than normal then what are the areas/utilities/commands/parameters i shd look thru and what are the actions/changes to be done? 2) I have just started the database and the users complain that the response is poor then what are the areas i shd look for and the ways to tune them?
SUSHANT KODE wrote: > Hi to all, > I am handling informix 7.31 FC6 (more than 1 TB size)on HP-UX11.11. I am new > to informix database administration. I have a few questions: > 1) If a user complains that his queries are going slow than normal then what > are the areas/utilities/commands/parameters i shd look thru and what are the > actions/changes to be done? I have a piece of string here. Can you tell me how long it is? > 2) I have just started the database and the users complain that the response > is poor then what are the areas i shd look for and the ways to tune them? Tell them to wait. The database needs time to cache up the data. -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.
There's much to much about performance tuning to be able to answer you
properly here. Go to the IIUG web site members pages and download all of
the presentation materials from previous IIUG Conferences that pertain to
server tuning, including several of my own. Second plan on attending this
year's IIUG Conference in Overland Park Kansas from April 25-28 (click on
the IIUG home page where it says "*Registration for the IIUG Informix
Conference 2010 is now open!*<http://www.iiug.org/conf/2010/iiug/register.php>";
to register. Third, sign up for some Informix training - Lester Knutsen
and I at Advanced DataTools are giving a Performance Tuning class on May
24-27 in our offices in Annandale VA near Washington DC. I don't know if
the class if fully subscribed yet, but contact our office if you are
interested (www.advancedatatools.com).
That all said, if you post some basic information and onstat output, the
denizens of this list will try to help as much as we can via remote control:
Number of CPU cores and core speed.
Type of disk used for server chunks (ie RAW device, COOKED device,
filesystem files)
Disk array details: (Type of array, RAID levels, number of separate arrays,
# spindles in each structure)
Amount of memory on the system.
What connection types do most user/client connections use (shared memory or
TCP/IP?)
onstat -p
onstat -d
onstat -D
onstat -g iof
onstat -g iov
onstat -c
onstat -C
onstat -g glo
onstat -P (Might not be supported in 7.3FC6 I don't remember anymore)
onstat -l
onstat -R
onstat -F
onstat -m
onstat -g rea
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Sun, Apr 4, 2010 at 2:31 PM, SUSHANT KODE <ksushants@gmail.com> wrote:
> Hi to all,
> I am handling informix 7.31 FC6 (more than 1 TB size)on HP-UX11.11. I am
> new
> to informix database administration. I have a few questions:
> 1) If a user complains that his queries are going slow than normal then
> what
> are the areas/utilities/commands/parameters i shd look thru and what are
> the
> actions/changes to be done?
> 2) I have just started the database and the users complain that the
> response
> is poor then what are the areas i shd look for and the ways to tune them?
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0016368e21654ce91704836e1e4d
@Obnoxio: LOL, I sure could use your sense of humor in stackoverflow.com informix forum! I posed the "Begin viewing query results before query completes" question and instead of others being objective about the question, they're more focused on what their acceptance rate/votes are. It's more of a popularity contest drama rather than helping/help figure things out!
http://www.redbooks.ibm.com/abstracts/sg245511.html?Open -----Mensaje original----- De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] En nombre de SUSHANT KODE Enviado el: Domingo, 04 de Abril de 2010 01:31 p.m. Para: ids@iiug.org Asunto: Performace issues [19502] Hi to all, I am handling informix 7.31 FC6 (more than 1 TB size)on HP-UX11.11. I am new to informix database administration. I have a few questions: 1) If a user complains that his queries are going slow than normal then what are the areas/utilities/commands/parameters i shd look thru and what are the actions/changes to be done? 2) I have just started the database and the users complain that the response is poor then what are the areas i shd look for and the ways to tune them? ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
I am with Art on 'there is too much to performance tuning...'. For starter, try making these simple changes ( maybe on your development box first) such as 'update statistics on tables and indexes', increase the buffers in the 'onconfig' file and cycle the engine. (BTW, isn't 7.3x out of support? Consider moving to a supported engine like 11.x)