As the title says really. 11.5. I want to add another table and associated indexes to -B2, but don't want to increase the buffers unless necessary.
Thread created by James PalmerAs the title says really. 11.5. I want to add another table and associated indexes to -B2, but don't want to increase the buffers unless necessary.
Stop receiving emails on this subject.Flag this post as spam/abuse.
-- Tom Bascom 603 547 9043 (office) 603 396 4886 (cell) tom@wss.com[/collapse]
Empty Buffers: 600. Same as -B2. That's concerning.
Depending on the DB size relative to -B, the primary buffer pool may not be full.
You can also look at the Alternate Buffer Pool O/S reads in promon R&D 2 3. The value of -B2 minus that value should be the number of free blocks in the ABP, assuming no evictions have taken place. To use this information you would have to know the size of the table you want to assign to the ABP, in blocks. The index sizes you can get from dbanalys.
1. Is the following set as disabled or enabled?
a. LRU2 replacement policy disabled.
2. Does the value of O/S reads in the Alternate buffer pool exceed the –B2?
Empty Buffers: 600. Same as -B2. That's concerning.
Flag this post as spam/abuse.
For some reason no tables were set to be in the alternate buffer pool. Not sure when that went missing. Is that information maintained in a backup/restore scenario?
If you assign an object to ABP at the object level then that survives a backup/restore. Assigning at the area level does not (tested in 11.3.2).
At the object level it's stored in _StorageObject._Object-attrib (on bit level)
At the area level it's stored in _Area._Area-attrib.
Prodb/procopy/prorest will use the specified target .st file if it exists or otherwise they will create new target .st file based on source db and only then they will use that .st file to create the _Area records in /new/ database. The _Area-attrib will be lost because its value is not stored in the .st file.
Thanks guys. Not sure how we lost the objects from this then... Oh well. I've put them back in and will schedule a DB restart in.
Another good use for -B2 is the schema area. Make sure that it has no application stuff in it and then load the whole thing into -B2.
I know that some pushy little perisher ;-) but nevertheless I would interesting to know if somebody has the statistics of system table's activity at runtime? It can be gathered using _TableStat/_IndexStat provided _StatBase was changed below the -tablerangesize/-indexrangesize (in other words, the whole range of table/index numbers should be below zero). I did not have the chances to gather such statistics on the real databases. On other hand I did not see the big gaps between table/index reads per database and the sums of _TableStat/_IndexStat for the application's objects.
Regards,
George
Most of the production installs I have worked on have very minimal _File,_Field,etc reads. Most seem to come at DB connection time.
I suspect dynamic queries would cause more reads but I have no real hard evidence... just comparing code bases and activity between a few sites.
Development boxes usually show a ton of reads for the schema tables (relatively speaking).
> Development boxes usually show a ton of reads for the schema tables (relatively speaking).
The compiler hits _file and _field to resolve all the schema stuff
during the compile process, hence the read activity.
On Tue, Jun 16, 2015 at 1:22 PM, TheMadDBA
wrote:
> RE: Determine how much free space in Alternate Buffer Pool
> Reply by TheMadDBA
>
> Most of the production installs I have worked on have very minimal
> _File,_Field,etc reads. Most seem to come at DB connection time.
>
> I suspect dynamic queries would cause more reads but I have no real hard
> evidence... just comparing code bases and activity between a few sites.
>
> Development boxes usually show a ton of reads for the schema tables
> (relatively speaking).
>
> Stop receiving emails on this subject.
>
> Flag this post as spam/abuse.
--
Tim Kuehn: Senior Consultant - TDK Consulting Services
President - Ontario PUG
Program Committee Chair - PUG Challenge Americas,
Course Instructor: Intro to OO Concepts for Procedural Programmers
Skype: timothy.kuehn
Ph: 519-576-8100
Cell: 519-781-0081
I suspected as much but the reads never get to a point where it mattered enough for me to investigate further.
More time spent trying to find the bad queries they were compiling :)
In some of my dev and QA DBs I set -basetable/-baseindex low (-306/-1226 respectively) so I can see the CRUD stats for audit tables and indexes. A text file with CRUD stats is attached.
Disclaimer: some of this read I/O was caused by me in the process of collecting this data (and occasionally testing my code...) so it doesn't necessarily indicate production-like usage.
Needless to say, if you do session compiles instead of running r-code (in dev for instance), you'll read the meta-schema objects a lot more.
> A text file with CRUD stats is attached.
Very interesting! I wish the _ActRecord/_ActIndex would be there (as CRUD per whole db).
The meta schema gets read a lot more than you might guess. It can be fun to keep an eye on.
By the way _ActRecord/_ActIndex and _TableStat/_IndexStat do not report the full statistics - for example, they only partially see the activity of Progress utilities like idxfix or dbanalys.
> The meta schema gets read a lot more than you might guess.
_Client-Session seems to be a leader when audting is enabled. Who are the leaders in other standard cases?
> Who are the leaders in other standard cases?
For me it's typically _File, _Field, _Index, _Index-field, and maybe _User, depending on the authentication method in place.
But that's not too definitive. Unfortunately the DB's file/index numbering scheme isn't consistent. So using the default basetable/baseindex, as I do in production, I get stats for only a handful of system indexes and no system tables. I also haven't studied the relative contributions of application use (with r-code) versus client-site application compiles. That could be interesting.
What are the things you usually see that cause this? Dynamic queries?
Most of my remote connections are using a local schema cache and running 90+ percent static code.
Our clients don't run with -cache, so that may be part of it. There are some dynamic queries but I'd say that like you, the large majority of our main application's queries are static. I haven't dug into _UserTableStat/_UserIndexStat to see exactly who is responsible for what. I have had bigger fish to fry.
Depending on how frequent code deliveries are, full compiles (thousands of compile units) could be a big part of it too.
I have not very often seen meta-schema activity at a worrisome level. But it is interesting that it seems to occur at a noticeable level even when I cannot think of many good reasons for it to be happening at all.
It is also interesting that many of the obvious lookups and the relationships between some of the tables are either very poorly indexed or not indexed at all. And some of them require fields as leading components that, at first blush, you would not necessarily guess should be required.
I guess there are the dependencies between the reads of metaschema tables that are constant over time. For example, getting a schema cache during client's connection will always reads one _Db record and these _Db reads will be exclusive (just an assumption) for this particular operation.
QUERY-PREPARE() also should create a predictable activity.
From Rob's sys_crud.txt:
Table# Table name Reads ------ ------------- --------------- -2 _Field 481,809 -1 _File 481,469 -3 _Index 80,426 -4 _Index-Field 55,493
The _File to _Field read ratio is 1:1 while the _File to _Index read ratio is 6:1. Most likely these _File reads are not from QUERY-PREPARE.
Parsing the "spectrum" of metaschema activity, IMHO, might say a lot about what an application is doing. It's an interesting subject for a research.