Recent

Author Topic: IBX - Buffer Chunks - every day use questions  (Read 1081 times)

Nicole

  • Hero Member
  • *****
  • Posts: 620
IBX - Buffer Chunks - every day use questions
« on: November 02, 2022, 08:42:19 am »
In the manual at page 52, chapter 6.2.1 there is a header "BufferChunks".

What happens, if the bufferchunks value is too low? Will there be a problem in performance, or will there be a loss of data?
Are there code-snipets how to read blocks of the buffer one by one?

IF I run a count of my query, how shall I choose them?

e.g. the count returns 100.
Shall I set BufferChunks to 100 or to 101 or to 110?

When is such a count-query appropriate and when is there a evaluation not to do it because of performance?

Thanks

Thaddy

  • Hero Member
  • *****
  • Posts: 12599
Re: IBX - Buffer Chunks - every day use questions
« Reply #1 on: November 02, 2022, 09:20:08 am »
- If the code is implemented correctly you should never have data loss. (I am not an IBX fan!)
- A buffer size is best chosen as a power of two, so 8,16,32,64,128,256,512, 1024 etc, because many compilers including FPC can optimize such sizes very efficiently. E.g. a buffer of 101 or 100 will usually be slower than either 64 or 128.
- Too large a buffer is dependent on CPU, storage speed and memory size, it is a bit trial and error, unless you can specify your hardware, but too small or too large a buffer will definitely affect performance.
« Last Edit: November 02, 2022, 09:24:37 am by Thaddy »
Writing code in an overly complex way is usually merely a complete misunderstanding of the goal you are trying to achieve.

rvk

  • Hero Member
  • *****
  • Posts: 5074
Re: IBX - Buffer Chunks - every day use questions
« Reply #2 on: November 02, 2022, 10:17:00 am »
Quote
- A buffer size is best chosen as a power of two, so 8,16,32,64,128,256,512, 1024 etc, because many compilers including FPC can optimize such sizes very efficiently. E.g. a buffer of 101 or 100 will usually be slower than either 64 or 128.
It's not buffer size we are talking about here but BufferChunks.

In the manual at page 52, chapter 6.2.1 there is a header "BufferChunks".
For reference, below is the complete text.

You mention setting the BufferChunks too low. Well, a BufferChunk is one row. So you can't set it below 1. And in that case, when reading additional records, IBX needs to reallocate the buffer pool for every count of records again, which will give you a performance hit. There will be no data loss.

If you count your records and you know for sure there are 100, you can set the count to 100.
Only when you need to add records during the session, a higher number would be logical.

But before you go tinkering with this property... the default is 1000... you need to wonder why you would want to change this.
Do you have an answer for that? How large are your records? Why is 1000 too high (or low) for you?
Do you have memory problems, or extremely large records?

I have lots (and lots) of TIBQueries in my program and I've never touched BufferChunks (I had no need to).


Quote
BufferChunks
Important Performance Parameter: This parameter determines the size by which the internal buffer allocation pool is increased every time it becomes fully used. The default is 1000 rows.

IBX will eventually cache the complete dataset in internal buffers. If the dataset is known to only ever have a few rows then BufferChunks can be set to a small number (e.g. 10 if the number of rows is typically less than 10) and the memory footprint is reduced.

On the other hand, if the number of rows is large (e.g. 100,000) then setting the BufferChunks to a larger figure (e.g. 25000) avoids a too frequent reallocation of the buffer pool as the dataset is read in. However, the figure should be chosen carefully to avoid a large number of unused buffers once the dataset has been read in. In some cases, it may even be appropriate to determine this figure at run time by first querying the database to return a count of the number of rows in the dataset and then setting BufferChunks just before the dataset is opened.
« Last Edit: November 02, 2022, 10:18:59 am by rvk »

Nicole

  • Hero Member
  • *****
  • Posts: 620
Re: IBX - Buffer Chunks - every day use questions
« Reply #3 on: November 02, 2022, 11:04:33 am »
Thank you for your answers.

Which I thought about changing it?
I have a problem with a TAChart. About 12 fields every day, back for decades.

The behaviour of my TAChart is fuzzy. The most probable reason is a typo of mine, - which I have not found up to now. There are so many options, what may be wrong, that I will not make me friends by posting the source here.

Therefore, I go through it one by one. One thing are the input data. As I portaged the code from Embarcadero's FireDac, - there I worked with blocks of 50. They were pushed in as query-blocks and I addressed them as such.

This is behind: The code snippet from Delphi / Firedac which has dealt with the buffer. I cannot find it any more. I seemed to have been that glad, I seem not to use it any more, that I even have deleted the out-commented lines of it.

Anyhow: If I asked in (Delphi XE3 with Firedac)  "how much data do you have", - it shouted "50!!", because the dummy counted the block-size instead of the data-count. The old code had to arrange itself with such answers.


rvk

  • Hero Member
  • *****
  • Posts: 5074
Re: IBX - Buffer Chunks - every day use questions
« Reply #4 on: November 02, 2022, 11:13:22 am »
The behaviour of my TAChart is fuzzy. The most probable reason is a typo of mine, - which I have not found up to now. There are so many options, what may be wrong, that I will not make me friends by posting the source here.
Than the default of 1000 BufferChunks is not the problem. With 50 records you could set it at a lower value (like 100) but you will not solve your problem and you will not gain much (especially if you have no out of memory problems). And changing the BufferChunks might give (performance) problems when that record count increases.

So, although changing the BufferChunks has it's advantages, it won't solve any of those specific problems (depending on what fuzzy problem you have with TAChart). I would only look at changing the BufferChunks if you have performance or memory problems.

rvk

  • Hero Member
  • *****
  • Posts: 5074
Re: IBX - Buffer Chunks - every day use questions
« Reply #5 on: November 02, 2022, 11:21:15 am »
Here is an explanation from Jeff (on IBX Delphi).

Quote
BufferChunks only tells IBX how much to grow the local buffer size when it needs to grow.  The default is 1000 records at a time.  This means when you open an IBQuery IBX sets up enough memory to hold 1000 records.  When you try to read/insert the 1001st record it will allocate another 1000 records worth of space and move the first 1000 over to that new memory space.  This takes time so you will want to minimize the number of times you will probably do this. Smaller BufferChunks size means less wasted memory space on average (if the size is 1000 and your result set is 100 you are wasting 900 records worth of memory).  Smaller sizes for larger results sets means slightly slower (in some situations much slower) memory resizing.  For instance is you set it to 100 and you are reading 50,000 records you will have to resize 5000 times instead of 500 for a buffer size of 1000.
« Last Edit: November 02, 2022, 11:31:13 am by rvk »

wp

  • Hero Member
  • *****
  • Posts: 10475
Re: IBX - Buffer Chunks - every day use questions
« Reply #6 on: November 02, 2022, 11:33:50 am »
I have a problem with a TAChart. About 12 fields every day, back for decades.
Do you use the TDBChartSource to access IBX? This is a bad idea because this type of chart source is very general and thus has to query the same data from the dataset again and again. Use it only once to fetch all data, and then copy the TDBChartSource into a TListChartSource which is connected to the series. Didn't we have this discussion already?

Thaddy

  • Hero Member
  • *****
  • Posts: 12599
Re: IBX - Buffer Chunks - every day use questions
« Reply #7 on: November 02, 2022, 12:04:10 pm »
It's not buffer size we are talking about here but BufferChunks.
In this case your invention of size versus chuck does not help: it should still be a power of two for maximum efficiency. Using IBX does not help anyway.
Use a proper database backend so your customers can use it over multiple platforms. AND can maintain it.... >:D >:D >:D
« Last Edit: November 02, 2022, 12:07:49 pm by Thaddy »
Writing code in an overly complex way is usually merely a complete misunderstanding of the goal you are trying to achieve.

rvk

  • Hero Member
  • *****
  • Posts: 5074
Re: IBX - Buffer Chunks - every day use questions
« Reply #8 on: November 02, 2022, 12:24:44 pm »
It's not buffer size we are talking about here but BufferChunks.
In this case your invention of size versus chuck does not help: it should still be a power of two for maximum efficiency. Using IBX does not help anyway.
Use a proper database backend so your customers can use it over multiple platforms. AND can maintain it.... >:D >:D >:D
Firebird and IBX is maintained (and can be used on multiple platforms).
So quit your FB bashing. We know you don't like it. It's getting boring.

And size is different from chunks.
1000 chunks of 32 bytes record size is still a power of 2 sorry, dividable by 2, which is fine in this situation. You really don't need to set it at 16.384.

« Last Edit: November 02, 2022, 12:29:17 pm by rvk »

Thaddy

  • Hero Member
  • *****
  • Posts: 12599
Re: IBX - Buffer Chunks - every day use questions
« Reply #9 on: November 02, 2022, 01:52:53 pm »
Yes I know you know and for good reasons that I explained many times.
BTW dividable by two is NOT a single shift operation so it is definitely more costly than a power of two..
Using a niche database is simply unacceptable since it is always about the DATA and access to it under most circumstances and most languages.
IBX does not fit that purpose, maintained or unmaintained, either way.
« Last Edit: November 02, 2022, 01:58:59 pm by Thaddy »
Writing code in an overly complex way is usually merely a complete misunderstanding of the goal you are trying to achieve.

Nicole

  • Hero Member
  • *****
  • Posts: 620
Re: IBX - Buffer Chunks - every day use questions
« Reply #10 on: November 02, 2022, 02:37:18 pm »
Quote
Do you use the TDBChartSource to access IBX? This is a bad idea because this type of chart source is very general and thus has to query the same data from the dataset again and again. Use it only once to fetch all data, and then copy the TDBChartSource into a TListChartSource which is connected to the series. Didn't we have this discussion already?

 :D
I read your postings (and those of all the other helpful guys): I copy the data from the query into an array. As this copy process works as a filter as well, there are really many options where I may mess my TAChart / data...

tonyw

  • Sr. Member
  • ****
  • Posts: 294
    • MWA Software
Re: IBX - Buffer Chunks - every day use questions
« Reply #11 on: November 17, 2022, 04:15:12 pm »
Buffer chunks is a "feature" inherited from the original IBX Borland source code. IBX Buffer Management is something I have yet to improve - but it is on my "to do list". Here's the background:

The Delphi TDataset model allows for either uni-directional or bi-directional movement through the rows of the underlying dataset (a single table or the result of an SQL Query). On the other hand, most database engines only provide uni-directional movement through a dataset - from top to bottom - and return each row through successive calls to a "fetch" function. This difference has to be resolved by the actual implementation of TDataset (e.g. that provided by IBX - by subclassing TDataset).

The TDataset model also implements a sliding window approach. It expects the TDataset implementation  to provide pointers to a fixed number of buffers when represent those rows in the "window". For example, the rows shown in a TDBGrid. How those buffers are provided is a matter for the TDataset subclass. TDataset allows for both uni-directional (forward only) and bidirectional movement of the sliding window. This mode is selected when the dataset is opened.

For most of its history, Firebird has only provided uni-directional movement - although Firebird 4 has introduced the idea of scrollable cursors.  IBX does provide access to scrollable cursors through its low level Firebird Pascal Interface, while I have yet to investigate any possible use of this feature in support of TDataset. As far as IBX datasets are concerned, only Firebird uni-directional cursors are used.

In IBX there are currently three ways in which the underlying buffers are managed in support of TDataset uni and bidirectional modes:

1. Uni-directional.

In uni-direction mode, the sliding window can only be advanced and cam never be reversed. Only a small number of buffers need be allocated given that as the window moves forwards those left behind can be reused for the most recently fetched rows. This is the most efficient mode of use and has a minimal memory footprint. This is the best choice if you only scroll forwards through the dataset. The BufferChunks parameter is ignored in this mode.

2. Bi-directional.

In this mode, IBX allocates a buffer pool from a single memory allocation. Each buffer has a fixed size which makes random access easy given that each buffer can be accessed using a readily computable offset from the start of the buffer pool. Successive buffers are used to save the dataset rows as they are fetched on demand as the user scrolls forwards. The user can scroll backwards at any time as the required row will always be in a buffer.

The problem with this approach is that the size of the buffer pool will always be finite and can only accommodate a limited number of buffers/rows. As the number of rows is not known when the dataset is opened, the approach used is to create a buffer pool for a fixed number of buffers and if that proves to be too small when the buffer pool needs to be expanded, the underlying memory is reallocated as a larger size providing for an increased number of buffers.

Depending on the underlying memory manager, when the buffer pool size is increased, a new block of contiguous memory is allocated for the resized memory block and the contents of the original memory block copied to it, before the original memory block is deallocated.

The BufferChunks parameter is used to determine both the initial size of the original memory block and each successive increase in size. The initial block is computed as "buffer size required" * BufferChunks. Each reallocation increases the size of the memory block by the same amount.

If BufferChunks is set too high them memory use is inefficient as there are always many unused buffers. If it is too low then there are frequent reallocations each taking a finite time to perform and performance suffers. There is no perfect setting unless you know in advance how big the dataset will be. Sometimes it may even be worth querying the database to find out how many rows are present before the dataset is opened, and setting BufferChunks appropriately.

3. Bidirectional with cached updates.

This is the same as bidirectional mode but requires a second buffer pool to hold a copy of each buffer prior to it being edited. This is to allow for the cancellation of cached updates.

Future Steps


What I would like to do is to move away from the single contiguous memory block approach used for the buffer pool and instead allow for each increase to use a separate memory block. This will avoid the need to copy the buffer pool contents each time the underlying memory block has to be increased, reducing the performance overhead and allowing for smaller initial allocations and hence improved memory utilisation.



Nicole

  • Hero Member
  • *****
  • Posts: 620
Re: IBX - Buffer Chunks - every day use questions
« Reply #12 on: November 26, 2022, 02:14:17 pm »
Thank you for the explanation. I appreciate that!
Is the time-plan still thinkable, that there will be a huge update around the winter holiday?

tonyw

  • Sr. Member
  • ****
  • Posts: 294
    • MWA Software
Re: IBX - Buffer Chunks - every day use questions
« Reply #13 on: December 02, 2022, 12:06:03 am »
Thank you for the explanation. I appreciate that!
Is the time-plan still thinkable, that there will be a huge update around the winter holiday?
Certainly no post Christmas mega update. The IBX issues list is actually very short. A better memory management algorithm is on my to do list, but as to when - that depends on what other demands there are on my time.

Nicole

  • Hero Member
  • *****
  • Posts: 620
Re: IBX - Buffer Chunks - every day use questions
« Reply #14 on: December 02, 2022, 01:34:31 pm »
"Certainly no post Christmas mega update."

English is not my native tongue. I am not sure, what this sentence means.
Certainly before Christmas?
Or certainly after Christmas?

 

TinyPortal © 2005-2018