IDS Forum

Re: What are best onconfig parameters for a DSS ap

Posted By: Art Kagel
Date: Monday, 7 May 2012, at 9:02 p.m.

In Response To: Re: What are best onconfig parameters for a DSS ap (FRANK J. COMPUTER)

See below:

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/

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 Mon, May 7, 2012 at 7:56 PM, FRANK J. COMPUTER
<frank_in_pr@hotmail.com>wrote:

> OK, so I've made your suggested changes to onconfig. Now, since my app is
> only
> does queries, is it possible for me to disable transaction logging and
> checkpoints?
>

Logging yes. If your database was created with logging (see the
sysmaster:sysdatabases record to find out if you are not sure) you can only
turn logging off as part of a level 0 archive:

ontape -s -L 0 -N <mydatabase> -t STDIO >/some/file/path

Checkpoints can't be disabled, but if your database has no logging and/or
you are not updating data, checkpoints will have nothing much to do
(checkpoints don't block most work in 11.xx anyway).

>
> Also:
>
> 1. What about enabling DIRECT_IO, or is that feature only available for UX
> raw
> disk partitions?
>

Actually, directo_io only works for filesystem chunks on UNIX platforms
(direct_io is an operating system feature and I don't think that windows
supports it).

>
> 2. Do I need to keep locks at 2000, or can I do with less?
>

Locks only take up about 48 bytes each. The lock table is dynamic, so you
could set it smaller and it will expand if needed, but as I say, there's
little cost to lock. OLTP systems often configure up to a million of them.

>
> 3. Will I need PDQ's if I'm the only user submitting queries?.. Remember,
> this
> is a single user app.
>

Yes. Extra memory is allocated to complex queries only if PDQPRIORITY is
>0 and parallel processing is only performed if it is set >1. Values over
1 are a percent of total DS_ resources, so set to 10 you would get up to
10% of the configured DS_TOTAL_MEMORY and 10% of the CPU VP's available (OK
you only have one, but if the machine is fast enough you could configure
more).

>
> 4. Should I change the way the query optimizer determines the best path?..
> e.g. OPT_GOAL, OPTCOMIND, etc.?
>

For DSS & DW style queries it is recommended to set OPTCOMPIND to 2,
OPT_GOAL defaults to ALL_ROWS which is the appropriate setting. The
FIRST_ROWS setting is for interactive applications that have to display the
first <N> rows of a large result set as quickly as possible, even if the
entire result set will take longer to produce completely.

>
> 5. I didn't see the AUTO_RA_PAGES parameter you mentioned in your reply.
>

'cause it is called AUTO_READAHEAD.

>
> 6. Since I am the only single-user (ADMIN) who will run these queries, can
> I
> disable permissions, replication or any other non-relevant multi-user
> parameters?.. Would that make performance faster?
>

MACH-11 replication is only active if you have a secondary server and
Enterprise Replication is only active if the server is defined to be a node
in an ER cluster. Privileges are always checked, nothing you can do about
it.

>
> 7. I would like to get the best possible diagnostics (with no tracing) to
> assist me in fine-tuning this app. Should I change any SQLEXPLAIN or QSTATS
> parms?
>

QSTATS and WSTATS config parameters are for diagnostics purposes only.
They add lots of overhead. You can run your queries initially with SET
EXPLAIN ON; to see the query plan the engine has selected and possibly use
the details in the sqexplain.out file to restructure the SQL, add indexes,
partition the tables, etc. Note that any query that has more than 5 or 6
tables joined should probably be run with SET OPTIMIZATION LOW; since the
costs of testing and costing every possible query plan (the default
behavior) can be more than the difference between the runtime of the best
query plan and one that is 3rd or 4th best. The number of query plans that
have to be examined under the default HIGH optimization is <#tables>
factorial while under low it is just SUM(1...<#tables-1>).

>
> 8. I'm not using any Java, ESQL or other API's. I'm just loading the data
> into
> this server and doing single-user DSS queries. Can I disable any of these
> API's?
>
> 9. There's also no networking with this server. Can I disable LISTENERS,
> TCP/IP, etc.?
>

Not sure if Windows supports shared memory connections but it might support
named pipe connection types. You would have to modify the NETTYPE onconfig
parameters and the sqlhosts file.

>
> 10. Any other parms you can think of to optimize this app?.. I'm pretty
> sure
> that 200MB's is not a performance issue for 1170, but in the future, my
> table
> sizes could grow to 1GB+, and I also want to learn how to fine-tune DSS and
> OLTP apps as well!.. I've been stuck in the SE world of 20 years ago for
> too
> long and need to catch up on all of this.. thanks for your help!
>

Go to the IIUG web site on the members' pages and download some of the
conference presentations on optimizing and tuning the server. There are a
couple of mine on the subject and several from other users and from IBMers.

>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--14dae9340d59cc353604bf7bee23

Messages In This Thread

• What are best onconfig parameters for a DSS app
  FRANK J. COMPUTER -- Monday, 7 May 2012, at 5:12 p.m.
  • RE: What are best onconfig parameters for a DSS ap
    Alexandre Marini -- Monday, 7 May 2012, at 5:28 p.m.
    • Re: RE: What are best onconfig parameters for a DS
      FRANK J. COMPUTER -- Monday, 7 May 2012, at 5:47 p.m.
  • Re: What are best onconfig parameters for a DSS ap
    Art Kagel -- Monday, 7 May 2012, at 6:25 p.m.
    • Re: What are best onconfig parameters for a DSS ap
      FRANK J. COMPUTER -- Monday, 7 May 2012, at 6:45 p.m.
      • Re: What are best onconfig parameters for a DSS ap
        Art Kagel -- Monday, 7 May 2012, at 6:48 p.m.
    • Re: What are best onconfig parameters for a DSS ap
      FRANK J. COMPUTER -- Monday, 7 May 2012, at 7:56 p.m.
      • Re: What are best onconfig parameters for a DSS ap
        Jonathan Leffler -- Monday, 7 May 2012, at 8:06 p.m.
        • Re: What are best onconfig parameters for a DSS ap
          FRANK J. COMPUTER -- Monday, 7 May 2012, at 8:20 p.m.
          • Re: What are best onconfig parameters for a DSS ap
            Art Kagel -- Monday, 7 May 2012, at 9:06 p.m.
        • Re: What are best onconfig parameters for a DSS ap
          FRANK J. COMPUTER -- Monday, 7 May 2012, at 8:33 p.m.
          • Re: What are best onconfig parameters for a DSS ap
            Art Kagel -- Monday, 7 May 2012, at 9:16 p.m.
            • Re: What are best onconfig parameters for a DSS ap
              FRANK J. COMPUTER -- Monday, 7 May 2012, at 10:59 p.m.
      • Re: What are best onconfig parameters for a DSS ap
        Art Kagel -- Monday, 7 May 2012, at 9:02 p.m.
        • Re: What are best onconfig parameters for a DSS ap
          FRANK J. COMPUTER -- Monday, 7 May 2012, at 9:32 p.m.
        • Re: What are best onconfig parameters for a DSS ap
          FRANK J. COMPUTER -- Monday, 7 May 2012, at 9:44 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.