ISAM reads of system catalog tables
A user on Informix 7.31 (HP-UX) saw huge ISAM read counts on systables and sysdistrib (via onstat -g ppf) and asked why catalog access wasn't satisfied from buffers. Replies said catalog reads that frequent point to an undersized data dictionary/distribution cache rather than BUFFERS, and suggested checking onstat -g dic for thrashing and tuning DD_HASHSIZE/DD_HASHMAX and DS_HASHSIZE/DS_POOLSIZE (e.g. 503/4/503/2000), raising them gradually since oversized caches add overhead; the parameters exist in 7.31 even if undocumented. Update statistics strategy and read-cache rate were also queried. No confirmation of a fix is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Environment:
7.31.FD3W1
S.O. HP-UX 11i 64 bits
I see the following data about systables and sysdistrib with onstat -g
ppf
isrd
systables (0x800002) 200287525
sysdistrib(0x800019) 107825782
The instance is 4 days up. Average 800 users, 1000 threads. Only one
database with about 2100 tables.
My question is why systables and sysdistrib have the ISAM reads high.
I think that system catalog must be only in buffer reads.
Is there any way to increase the buffer reads for system catalog
tables ?
On 5 jun, 10:28, Peternt <roger_vi...@yahoo.es> wrote:
> Environment:
>
> 7.31.FD3W1
> S.O. HP-UX 11i 64 bits
>
> I see the following data about systables and sysdistrib with onstat -g
> ppf
>
> isrd
>
> systables (0x800002) 200287525
> sysdistrib(0x800019) 107825782
>
> The instance is 4 days up. Average 800 users, 1000 threads. Only one
> database with about 2100 tables.
>
> My question is why systables and sysdistrib have the ISAM reads high.
> I think that system catalog must be only in buffer reads.
> Is there any way to increase the buffer reads for system catalog
> tables ?
One aditional data with onstat -p
%cached for reads = 94.47
%cached for writes = 97.78
This suggest me that The parameter BUFFERS (224000) is enough.
System catalog tables should not be read frequently at all, either from disk
or from the cache. It looks like your data dictionary cache is mis-sized.
Look at the DD_ parameters in the ONCONFIG file. They must provide enough
buckets and slots to hold all of the active tables' catalog information at
the same time. (See onstat -g dic to see how full the cache is now and to
watch over time for thrashing.)
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. 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, Jun 5, 2009 at 11:28 AM, Peternt <roger_vilca@yahoo.es> wrote:
> Environment:
>
> 7.31.FD3W1
> S.O. HP-UX 11i 64 bits
>
> I see the following data about systables and sysdistrib with onstat -g
> ppf
>
> isrd
>
> systables (0x800002) 200287525
> sysdistrib(0x800019) 107825782
>
> The instance is 4 days up. Average 800 users, 1000 threads. Only one
> database with about 2100 tables.
>
> My question is why systables and sysdistrib have the ISAM reads high.
> I think that system catalog must be only in buffer reads.
> Is there any way to increase the buffer reads for system catalog
> tables ?
>
>
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
On Jun 5, 4:46 pm, Art Kagel <art.ka...@gmail.com> wrote:
> System catalog tables should not be read frequently at all, either from disk
> or from the cache. It looks like your data dictionary cache is mis-sized.
> Look at the DD_ parameters in the ONCONFIG file. They must provide enough
> buckets and slots to hold all of the active tables' catalog information at
> the same time. (See onstat -g dic to see how full the cache is now and to
> watch over time for thrashing.)
>
> Art
>
> Art S. Kagel
> Oninit (www.oninit.com)
> IIUG Board of Directors (a...@iiug.org)
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions and
> do not reflect on my employer, Oninit, the IIUG, nor any other organization
> with which I am associated either explicitly or implicitly. 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, Jun 5, 2009 at 11:28 AM, Peternt <roger_vi...@yahoo.es> wrote:
> > Environment:
>
> > 7.31.FD3W1
> > S.O. HP-UX 11i 64 bits
>
> > I see the following data about systables and sysdistrib with onstat -g
> > ppf
>
> > isrd
>
> > systables (0x800002) 200287525
> > sysdistrib(0x800019) 107825782
>
> > The instance is 4 days up. Average 800 users, 1000 threads. Only one
> > database with about 2100 tables.
>
> > My question is why systables and sysdistrib have the ISAM reads high.
> > I think that system catalog must be only in buffer reads.
> > Is there any way to increase the buffer reads for system catalog
> > tables ?
>
> > _______________________________________________
> > Informix-list mailing list
> > Informix-l...@iiug.org
> >http://www.iiug.org/mailman/listinfo/informix-list
It is the sysdistrib reads which are interesting ...
1. Check out the following :
http://publib.boulder.ibm.com/infocenter/idshelp/v10/topic/com.ibm.perf.doc/perf113.htm
Initial suggest (just from that document) :
For medium to large systems, you can start with the following values:
* DD_HASHSIZE 503
* DD_HASHMAX 4
* DS_HASHSIZE 503
* DS_POOLSIZE 2000
2. What is your update statistics strategy?
On 5 jun, 14:08, TheBigPota...@Nothere.Co.Uk wrote:
> On Jun 5, 4:46 pm, Art Kagel <art.ka...@gmail.com> wrote:
>
>
>
> > System catalog tables should not be read frequently at all, either from disk
> > or from the cache. It looks like your data dictionary cache is mis-sized.
> > Look at the DD_ parameters in the ONCONFIG file. They must provide enough
> > buckets and slots to hold all of the active tables' catalog information at
> > the same time. (See onstat -g dic to see how full the cache is now and to
> > watch over time for thrashing.)
>
> > Art
>
> > Art S. Kagel
> > Oninit (www.oninit.com)
> > IIUG Board of Directors (a...@iiug.org)
>
> > Disclaimer: Please keep in mind that my own opinions are my own opinions and
> > do not reflect on my employer, Oninit, the IIUG, nor any other organization
> > with which I am associated either explicitly or implicitly. 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, Jun 5, 2009 at 11:28 AM, Peternt <roger_vi...@yahoo.es> wrote:
> > > Environment:
>
> > > 7.31.FD3W1
> > > S.O. HP-UX 11i 64 bits
>
> > > I see the following data about systables and sysdistrib with onstat -g
> > > ppf
>
> > > isrd
>
> > > systables (0x800002) 200287525
> > > sysdistrib(0x800019) 107825782
>
> > > The instance is 4 days up. Average 800 users, 1000 threads. Only one
> > > database with about 2100 tables.
>
> > > My question is why systables and sysdistrib have the ISAM reads high.
> > > I think that system catalog must be only in buffer reads.
> > > Is there any way to increase the buffer reads for system catalog
> > > tables ?
>
> > > _______________________________________________
> > > Informix-list mailing list
> > > Informix-l...@iiug.org
> > >http://www.iiug.org/mailman/listinfo/informix-list
>
> It is the sysdistrib reads which are interesting ...
>
> 1. Check out the following :
>
> http://publib.boulder.ibm.com/infocenter/idshelp/v10/topic/com.ibm.pe...
>
> Initial suggest (just from that document) :
>
> For medium to large systems, you can start with the following values:
>
> * DD_HASHSIZE 503
> * DD_HASHMAX 4
> * DS_HASHSIZE 503
> * DS_POOLSIZE 2000
>
> 2. What is your update statistics strategy?
Lamentably I think that my version (7.31.FD3W1) don't support the
parameters DD_ from tables and DS_ from distributions. Am I right?
Respect to onstat -g dic, this is the output:
Dictionary Cache:
Number of lists: 31, Maximum list size: 10
...
Total number of dictionary entries: 322
My distribution is according to recomendations:
1. medium for the table distributions only
2. high for the first field from each index distributions only
3. low for the table
On 5 June, 21:22, Peternt <roger_vi...@yahoo.es> wrote:
> On 5 jun, 14:08, TheBigPota...@Nothere.Co.Uk wrote:
>
>
>
> > On Jun 5, 4:46 pm, Art Kagel <art.ka...@gmail.com> wrote:
>
> > > System catalog tables should not be read frequently at all, either from disk
> > > or from the cache. It looks like your data dictionary cache is mis-sized.
> > > Look at the DD_ parameters in the ONCONFIG file. They must provide enough
> > > buckets and slots to hold all of the active tables' catalog information at
> > > the same time. (See onstat -g dic to see how full the cache is now and to
> > > watch over time for thrashing.)
>
> > > Art
>
> > > Art S. Kagel
> > > Oninit (www.oninit.com)
> > > IIUG Board of Directors (a...@iiug.org)
>
> > > Disclaimer: Please keep in mind that my own opinions are my own opinions and
> > > do not reflect on my employer, Oninit, the IIUG, nor any other organization
> > > with which I am associated either explicitly or implicitly. 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, Jun 5, 2009 at 11:28 AM, Peternt <roger_vi...@yahoo.es> wrote:
> > > > Environment:
>
> > > > 7.31.FD3W1
> > > > S.O. HP-UX 11i 64 bits
>
> > > > I see the following data about systables and sysdistrib with onstat -g
> > > > ppf
>
> > > > isrd
>
> > > > systables (0x800002) 200287525
> > > > sysdistrib(0x800019) 107825782
>
> > > > The instance is 4 days up. Average 800 users, 1000 threads. Only one
> > > > database with about 2100 tables.
>
> > > > My question is why systables and sysdistrib have the ISAM reads high.
> > > > I think that system catalog must be only in buffer reads.
> > > > Is there any way to increase the buffer reads for system catalog
> > > > tables ?
>
> > > > _______________________________________________
> > > > Informix-list mailing list
> > > > Informix-l...@iiug.org
> > > >http://www.iiug.org/mailman/listinfo/informix-list
>
> > It is the sysdistrib reads which are interesting ...
>
> > 1. Check out the following :
>
> >http://publib.boulder.ibm.com/infocenter/idshelp/v10/topic/com.ibm.pe...
>
> > Initial suggest (just from that document) :
>
> > For medium to large systems, you can start with the following values:
>
> > * DD_HASHSIZE 503
> > * DD_HASHMAX 4
> > * DS_HASHSIZE 503
> > * DS_POOLSIZE 2000
>
> > 2. What is your update statistics strategy?
>
> Lamentably I think that my version (7.31.FD3W1) don't support the
> parameters DD_ from tables and DS_ from distributions. Am I right?
> Respect to onstat -g dic, this is the output:
> Dictionary Cache:
> Number of lists: 31, Maximum list size: 10
> ...
> Total number of dictionary entries: 322
>
> My distribution is according to recomendations:
>
> 1. medium for the table distributions only
> 2. high for the first field from each index distributions only
> 3. low for the table
7.31.UC4 certainly does support these parameters (I am not sure for
7.31.FD3W1), they are just not documented for that version.
Use the documentation for 11.50 as that is the best documentation.
Be aware that overly large values for these caches can be slower than
smaller values due to the increased overhead of managing the cache.
i would increase the caches by say 50% and test/monitor, repeat until
performance no longer improves with a larger cache size.
Peternt wrote:
> On 5 jun, 10:28, Peternt <roger_vi...@yahoo.es> wrote:
>> Environment:
>>
>> 7.31.FD3W1
>> S.O. HP-UX 11i 64 bits
>>
>> I see the following data about systables and sysdistrib with onstat -g
>> ppf
>>
>> isrd
>>
>> systables (0x800002) 200287525
>> sysdistrib(0x800019) 107825782
>>
>> The instance is 4 days up. Average 800 users, 1000 threads. Only one
>> database with about 2100 tables.
>>
>> My question is why systables and sysdistrib have the ISAM reads high.
>> I think that system catalog must be only in buffer reads.
>> Is there any way to increase the buffer reads for system catalog
>> tables ?
>
> One aditional data with onstat -p
>
> %cached for reads = 94.47
> %cached for writes = 97.78
>
> This suggest me that The parameter BUFFERS (224000) is enough.
Your read cache is lower than the write cache... It can be normal, by I don't
find it usual... It really depends on the workload of your system.
In most systems (OLTP) it's very easy to get higher read cache values... If you
have enough memory you can consider increasing your buffers... Also check if
you're not doing unnecessary sequential scans or bad index choices.
Regards.
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
Related threads
- Posting from the Informix-list
- Migrating from IDS 9.40.UC6 to 11.50.UC3
- Ip for a network session
- questions onstat -g