Performance and Buffer pool

, ,
1

Dear collegues

We are getting some perfomance issues and I would like some recomendation, help, … whatever.

The problem is a slow response from Tamino in cases that apparently it should not happen. Lets use as reference the following query (the field STLRecordSort is indexed):

…_XQL=STLHoldingIbermarc sortby(STLFields/STLRecordFields/STLRecordSort asc)&_encoding=windows-1252&=&_cursor=open&_position=1&_quantity=10

Just started the DB server, the previous query takes around 60 seconds. I see as the buffer pool usage increase until 90MB, before getting response. Next try with the query, the answer is near to instantaneous. Related queries against the same Doctype, works fine or badly, according to previous requested queries.

I have the idea that, the buffer pool performes some kind of query catching, but I have no idea of the reaching.

May be (this is a supposition) the former queries are penalized translating to RAM indexes, and other Tamino resources, in order to reach a reasonable response rate after some first accesses to the DB.

My question is then, how should we check such response performance. Or if it is reasonable expect ‘performance fails’ in the first steeps of the application running, and then, hopefully, resources will be reasonable balanced to provide reasonable answer.

May be the problem is another and we have any other reason of poor perfomance than the one I am presuming.

The execution context is:

  • W2000
  • 4GB RAM
  • Tamino 4.2.1.8
  • Buffer pool 600MB
  • Around 1.000.000 records in the previous Doctype
  • Records are around 4KB except a few (300) that are around 100 KB.

If useful, the schema is atached.

Thanks for any feedback
Ignacio
ibermarcstorec.tsd (226 KB)

2

Hello,

a definite answer cannot be given without in-depth investigations of your scenario, but what you describe seems to indicate the following:
the query needs to access all documents stored in a doctype. These data have to be read from disk. This is most probably what causes the query to take that long. Then, the data is cached in the buffer pool, no physical IO is needed any more. You can check for the number of physical I/O via System Management Hub / Information / Activity. You can check whether the amount of data read corresponds to the size of your result. I assume it does. Then you can check the IO speed of your system (seems to be 1.5 MB per second).
Summarizing: the buffer pool is a cache for the data on disk, not for query results.

3

Thanks Harald

As far as I know, if the query uses just indexed fields, it should work in the index level, then recover those documents previously selected by the preselection.

In the query that I refers to, there is only one field involved in the selection of records, the STLFields/STLRecordFields/STLRecordSort, what is indexed. The query is posted as a plain uri from an IE 6.0.2

Is supposed that every documents of the doctype are needed in this case ?

ino:explain is attached

Best regards
Ignacio
explain.xml (3.37 KB)

4

Hi Ignacio,

are you saying that not every document contains the path STLFields/STLRecordFields/STLRecordSort?

regards

Harald

5

No, I mean that the conditions that documents must match, are only that they must be recovered in the asc. order of STLFields/STLRecordFields/STLRecordSort.

There are 927710 documents, and all of them with such a field.

I have some suspect about that the time spent when the BD is just started, can be related with the load in buffer pool of the index table (or whatever it is called). The STLHoldingIbermarc doctype contains librarian data, and is full indexed. So, the amount of index space for this doctype I presume is really big, near to the full record size (at least).

Could it be the own index being loaded in buffer pool, what is consuming so long time ?

Bets regards
Ignacio

6

Dear colleagues

Some months ago this issue was not finally solved, and now, it seems to arise again our installation, given us performance problems.

The behaviour is that queries having a very quick response time, starts to run very, very slow. After the response at the end of a so slow process, everything starts to run quick. This behaviour happens from time to time.

  • Does anybody have idea about the buffer pool policy ?
  • Is there any tool that can give us statistics about buffer pool contain ?
  • Any other clue about such behaviour ?

Thanks in advance
Ignacio