• Article Type: General
• Product: Aleph
• Product Version: 20

Description:
CPU load on the server appears very low however our staff are complaining of incredibly slow response from their gui clients and the OPAC. Performance appears terrible. It appears that many disks are very busy (iostat).

What can be done to fix?

Resolution:
I found that, while Browses were giving results in a few seconds, my keyword searches took almost a minute and then timed out, giving no results.

Since slowness for a particular activity can be associated with missing Oracle indexes on the tables which that activity uses, I did the following SQL (from KB 8192-6423) to check for missing sys01 Oracle indexes:

sys01@ALEPH20> select index_name from all_indexes where owner = 'USM01' and index_name like 'Z%' minus select index_name from all_indexes where owner = 'SYS01' and index_name like 'Z%' and status = 'VALID';
**** Hit return to continue ****

INDEX_NAME
------------------------------
Z0101_ID3
Z01_ID3
Z120_ID
Z120_ID1
Z120_ID2
Z120_ID3
Z35_ID2
Z35_ID3

The z120 is an optional table (for Union Catalog/Union View) which your library doesn't have; its absence and the absence of its Oracle indexes are *not* a problem.

The z01 and z0101 *are* present in sys01 and you should use util a/17/2 to build these missing indexes. But, since these tables are not involved in keyword searching, I did not do anything with them myself. You should do that.

I felt that the z35 table *could* be involved. The z35 is the "events table". There were 4.8 million z35's in sys01:

sys01@ALEPH20> select count(*) from z35;
**** Hit return to continue ****

COUNT(*)
----------
4873552


KB 8192-2055 describes the z35 in relation to the Main OPAC events table, the z69. Basically, it concludes that the z35 is not providing any important statistics; the z69 would be used for those.

There are *no* Aleph reports which use the bib library z35.

I did util a/17/2 for the z35_id2 (taking about 1/2 hour); I tried the same for the z35_id3 but (after 40 minutes) got a "duplicates" error message. I then tried util a/17/18 but when it was still running after an hour did the following:

> dlib sys01
> cp -p tab_events.eng tab_events.eng.jssave
"vi" tab_events.eng, commenting out types 10, 11, and 12
> util a 17 1 for z35
restarted www_server 4992

This dropped the existing sys01 z35's and will prevent more from being written. After which, I find that my keyword searches *are* now getting results. If for some reason you do want these z35's to be written, you can do "mv tab_events.eng.jssave tab_events.eng" to put the original back.

Note: It is critical that this be done only for the bib library; the ADM z35 is used for Circ statistics and should definitely *not* be re-initialized in this fashion.

Finally, the missing-indexes SQL in lps50 shows that the following Oracle indexes need to be built in that library:

Z16_ID3
Z36H_ID3
Z37H_ID2

• Article last edited: 10/8/2013