IDS BUFFER POLL CONFIG *NM*
A newcomer asked whether there's a formula for sizing BUFFERS/bufferpool and SHMVIRTSIZE based on a machine's memory and CPU (his setup: IDS 10 FC4 on AIX 5.3, single instance). No single formula was given. Advice: the Performance Guide suggests roughly 1/3 of memory for IDS; better to size the buffer cache to the daily working set, aim for a 98.5-99%+ read cache rate (onstat -p), isolate hot tables in separate pagesize dbspaces/bufferpools, set SHMVIRTSIZE large enough to need only one virtual segment (onstat -g seg) and to hold hash joins/sorts and PDQ memory. Others pointed to the Information Center and the AIX machine notes.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
\
Good afternoon My Name is Helder Jesus. I was wondering if there was a formula to configure BUFFERPOOL and SHMVIRTSIZE correctly regarding the machine specifications of memory and cpu capabilities Thanks in advance seeing that this is my first post!
There's a recommendation in the Performance Guide manual to allocate about
1/3 of memory to IDS. Honesly the best thing to do is to calculate your
normal daily working set data size and allocate the buffer cache according
to that. Then assign as much memory as you can afford to fence off from
other applications to SHMVIRTSIZE and allocate a large portion of that to
the DS_ memory parameters.
Welcome to the forums!
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 Fri, Jan 8, 2010 at 8:26 AM, HELDER JESUS
<helder.e.jesus@bancomais.pt>wrote:
> Good afternoon
>
> My Name is Helder Jesus.
> I was wondering if there was a formula to configure BUFFERPOOL and
> SHMVIRTSIZE> correctly regarding the machine specifications of memory and cpu
> capabilities
>
> Thanks in advance seeing that this is my first post!
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--000e0ce0091efa3e35047ca78093
Mr Helder,
have you read the Information Center ?
Are you using the 11.50.X version of the database engine?
If so, you could find everything you need to tune your installation
correctly at the link below:
http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp
The fine-tunning you might do afterwards, and then you could search
doubts here on forums, or even post new ones, ok?
Hope it helps.
Regards!
Alexandre Marini
Tecnologia da Informação - DBA
SEFAZ-MS / SGI-UIMP / Sistemas IBM-Informix
IIUG Member
<http://www.iiug.org>;
HELDER JESUS escreveu:
> Good afternoon
>
> My Name is Helder Jesus.
> I was wondering if there was a formula to configure BUFFERPOOL and
SHMVIRTSIZE> correctly regarding the machine specifications of memory and cpu capabilities
>
> Thanks in advance seeing that this is my first post!
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
HELDER JESUS schrieb:
> Good afternoon
>
> My Name is Helder Jesus.
> I was wondering if there was a formula to configure BUFFERPOOL and
SHMVIRTSIZE> correctly regarding the machine specifications of memory and cpu capabilities
>
> Thanks in advance seeing that this is my first post!
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
Hi,
a general formula is not an easy thing.
It depends on so many influencing factors; to name a few:
- dedicated DB server or other apps on the same machine
- one IDS instance or more than one
- how big is the working set of your databases
- OLTP workload or DSS workload or *shudders* a mix
In short:
Bufferpool(s)
it is a good thing to have high read cache rate (onstat -p)
high == better than 98.5%, best 99+%, IFF your workload is
read bound, which is mostly the case.
It is a good thing to have isolation, i.e. to have the most active
and read bound table(s) / index(es) in a separate BUFFERPOOL by
creating dbspaces with a different pagesize and putting (only!) those
DB objects there.
shmvirtsize, shmadd
It is a good thing to have only one V-segment. Make SHMVIRTSIZE
big enough to cover all, if you see more than one V-segment in
onstat -g seg output.For hash joins is it a good thing to have the hash table incore
to avoid I/O.
It is a good thing to have enough V-seg size to use PDQ memory
(or Memory Grant Manager - MGM - managed memory)
It is nice to have enough PDQ mem to do index(re-)build sorts
incore, as with each and every sort. So you reduce I/O
on dbspaces in the DBSPACETEMP list.
But all depends very much on IDS version
[ please ALWAYS - in EVERY new post - give IDS version
seen in 'onstat -' output and OS type and version and
in every answer please quote the relevant parts of the posting
or thread. So those, who use a news- or mailreader ( == we :) )
have the context ]
and it depends on HW type (amount of memory, DAS, SAN or NAS disks
SSDs or no SSDs), workload and performance expectations.
dic_k
--
Richard Kofler
SOLID STATE EDV
Dienstleistungen GmbH
Vienna/Austria/Europe
Thanks for the replys! Im a bit new to Informix also and I dont have a lot of knowledge as well as working hours on this mater! But I forgot to mention that my Informix version is IDS 10 FC4 over Aix 5.3. The machine only as one IDS instance! Thanks to all!
Please note that most of the forum denizens do not use the web interface but rather the email gateway. Consequently we cannot see the threads to which you reply so you must quote at least part of the posting to which you are responding for our benefit and for the benefit of those who come after you and may search the forum history to find your solution to their problem 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 Fri, Jan 8, 2010 at 9:25 AM, HELDER JESUS <helder.e.jesus@bancomais.pt>wrote: > Thanks for the replys! > > Im a bit new to Informix also and I dont have a lot of knowledge as well > as > working hours on this mater! > But I forgot to mention that my Informix version is IDS 10 FC4 over Aix > 5.3. > The machine only as one IDS instance! > > Thanks to all! > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0015174c3c1ccd14cd047ca80627
Ok so you can begin: 1) read your machine notes file, it has always a lot of recommended adjustments you must follow 2) use the 10 Information Center to begin with the formulas of the onconfig parameters suggested: http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp Regards! Alexandre Marini Tecnologia da Informação - DBA SEFAZ-MS / SGI-UIMP / Sistemas IBM-Informix IIUG Member <http://www.iiug.org>; HELDER JESUS escreveu: > Thanks for the reply's! > > I'm a bit new to Informix also and I don't have a lot of knowledge as well as > working hours on this mater! > But I forgot to mention that my Informix version is IDS 10 FC4 over Aix 5.3. > The machine only as one IDS instance! > > Thanks to all! > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > >
In case you didn´t find, here´s your specific machine notes link: http://publib.boulder.ibm.com/epubs/html/29919430.html Regards. Alexandre Marini Tecnologia da Informação - DBA SEFAZ-MS / SGI-UIMP / Sistemas IBM-Informix IIUG Member <http://www.iiug.org>; Alexandre Marini escreveu: > Ok so you can begin: > 1) read your machine notes file, it has always a lot of recommended > adjustments you must follow > 2) use the 10 Information Center to begin with the formulas of the > onconfig parameters suggested: > http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp > > Regards! > > Alexandre Marini > > Tecnologia da Informação - DBA > > SEFAZ-MS / SGI-UIMP / Sistemas IBM-Informix > > IIUG Member > > <http://www.iiug.org>; > > HELDER JESUS escreveu: > >> Thanks for the reply's! >> >> I'm a bit new to Informix also and I don't have a lot of knowledge as well >> > as > >> working hours on this mater! >> But I forgot to mention that my Informix version is IDS 10 FC4 over Aix 5.3. >> The machine only as one IDS instance! >> >> Thanks to all! >> >> >> >> > ******************************************************************************* > >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> >> >> >> > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > >