| Subject | Re: [firebird-support] Re: Query is running slowly for the first time. |
|---|---|
| Author | Ann Harrison |
| Post date | 2012-10-09T21:28:28Z |
On Tue, Oct 9, 2012 at 11:37 AM, Thomas Steinmaurer <ts@...>wrote:
about the same number of reads as the first time. That's very interesting.
The Firebird cache isn't doing much good, probably because the default for
Classic is way to small for this query. The slight decline would be due to
the need to prepare the query on the first iteration. And the cost of
preparing could be significant since Firebird reads all the pointer pages
for the table. Increasing the page size decreases the number of pointer
pages, so that may help too.
The majority of what you're seeing is the file system cache filling itself
with pages that Firebird will need. Changing the parameters of the query
changes the pages that Firebird needs to resolve the query makes the query
slow again. What you're seeing is the benefit reading pages from memory
rather than reading from disk.
Shutting Firebird down doesn't flush the OS page cache. Rebooting the
machine does.
The clues are here:
Executing statement...
1825600 fetches, 9 marks, 88863 reads, 6 writes.
0 inserts, 0 updates, 0 deletes, 856790 index, 3374 seq.
Delta memory: 265568 bytes.
Total execution time: 36.321s
Executing statement...
1818330 fetches, 0 marks, 88768 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 856550 index, 410 seq.
Delta memory: 31192 bytes.
Total execution time: 2.527s
Each run of the statement performs about 89,000 page reads. One takes 36
seconds, the other takes 2.5 seconds. The second run is fast even if
Firebird is shut down, meaning that the Firebird cache is not significant
and that the time to prepare the query is not very significant.
So how do you fix the problem, given that you will have to run the
procedure with different parameters? Increase the amount of system memory.
Get a faster disk. Increasing the page size so you don't have to do as
many random page reads on disk may help too.
Good luck,
Ann
[Non-text portions of this message have been removed]
>The query stats show that the second time the procedure runs, it's doing
> >>> What is interesing: I need to restart my machine to recreate the slow
> execution time.
> >>
> >> Or unpreparing the query and/or disconneting in case of Classic to purge
> >> the local per-connection page cache.
> >
> > As I've mentioned earlier I've restarted Firebird (without restarting
> machine) and after this, query was running fast.
>
> Could be cooperative garbage collection with Classic as well. Is the
> large table frequently updated with larger batches?
>
about the same number of reads as the first time. That's very interesting.
The Firebird cache isn't doing much good, probably because the default for
Classic is way to small for this query. The slight decline would be due to
the need to prepare the query on the first iteration. And the cost of
preparing could be significant since Firebird reads all the pointer pages
for the table. Increasing the page size decreases the number of pointer
pages, so that may help too.
The majority of what you're seeing is the file system cache filling itself
with pages that Firebird will need. Changing the parameters of the query
changes the pages that Firebird needs to resolve the query makes the query
slow again. What you're seeing is the benefit reading pages from memory
rather than reading from disk.
Shutting Firebird down doesn't flush the OS page cache. Rebooting the
machine does.
The clues are here:
Executing statement...
1825600 fetches, 9 marks, 88863 reads, 6 writes.
0 inserts, 0 updates, 0 deletes, 856790 index, 3374 seq.
Delta memory: 265568 bytes.
Total execution time: 36.321s
Executing statement...
1818330 fetches, 0 marks, 88768 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 856550 index, 410 seq.
Delta memory: 31192 bytes.
Total execution time: 2.527s
Each run of the statement performs about 89,000 page reads. One takes 36
seconds, the other takes 2.5 seconds. The second run is fast even if
Firebird is shut down, meaning that the Firebird cache is not significant
and that the time to prepare the query is not very significant.
So how do you fix the problem, given that you will have to run the
procedure with different parameters? Increase the amount of system memory.
Get a faster disk. Increasing the page size so you don't have to do as
many random page reads on disk may help too.
Good luck,
Ann
[Non-text portions of this message have been removed]