>>gstat when your application is slow

Yup I have. I won't post it, 7500 lines. From 12 days ago.
I have ignored old versions of records. What can that give? is that gstat -r

This means little to me:
Average record length: 270.13, total records: 301679
Average version length: 63.68, total versions: 69, max versions: 3
-from current snapshot of PART table below.

The fill levels mainly above 85-90%. - this is one of the worst, but not
bad:

PART (138)
Primary pointer page: 1371, Index root page: 1372
Data pages: 29137, data page slots: 46814, average fill: 79%
Fill distribution:
0 - 19% = 3672
20 - 39% = 148
40 - 59% = 75
60 - 79% = 73
80 - 99% = 25169

Index PART_BYPARTNO (13)
Depth: 3, leaf buckets: 643, nodes: 301462
Average data length: 1.00, total dup: 3, max dup: 1
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 221
60 - 79% = 4
80 - 99% = 418

Which joins on

PURCHTRAN (160)
Primary pointer page: 1415, Index root page: 1416
Data pages: 91489, data page slots: 93402, average fill: 92%
Fill distribution:
0 - 19% = 1464
20 - 39% = 1118
40 - 59% = 1547
60 - 79% = 1560
80 - 99% = 85800

Index PURCHTRAN_BYPARTNO (2)
Depth: 3, leaf buckets: 2092, nodes: 870334
Average data length: 0.00, total dup: 778033, max dup: 18128
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 1543
60 - 79% = 51
80 - 99% = 497

With page size of 4096
- using a page size of 8192, the are better fill ratios, I changed
it down, to save ram and use what was saved to lower the lock hash chains,
allround performance improved. However for indexes the depth went up by 1,
(not above 3) - I am tempted to go back to 8192, but the server will have to
swap to cope(big slow down). We are looking at more ram. I asking these
questions as in months time I will overhaul the servers.

Database header page information:
Flags 0
Checksum 12345
Generation 843404
Page size 4096
ODS version 10.1
Oldest transaction 694109
Oldest active 777945
Oldest snapshot 694110
Next transaction 843389
Bumped transaction 1
Sequence number 0
Next attachment ID 0
Implementation ID 19
Shadow count 0
Page buffers 15000
Next header page 0
Database dialect 1
Creation date Aug 4, 2007 14:32:56

Variable header data:
Sweep interval: 0
*END*

A sweep fixed the above, the software is often terminated I think,
transactions left to hang and some software left running for weeks
I plan to improve query plans in longterm, I keep records of all long
queries.
Hth

alex


|-----Original Message-----
|From: firebird-support@yahoogroups.com
|[mailto:firebird-support@yahoogroups.com] On Behalf Of Ann W. Harrison
|Sent: 08 November 2007 16:33
|To: firebird-support@yahoogroups.com
|Subject: Re: [firebird-support] questions on firebird, mainly obscure
|
|Alex,
|
| I've got a short answer to a long set of questions. Have you
|used gstat when your application is slow? What does it show you
|about old versions of records and index fill levels? Knowing
|exactly what the problem is will help determine a solution.
|
|Good luck,
|
|
|Ann
|
|
|++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
|
|Visit http://www.firebirdsql.org and click the Resources item
|on the main (top) menu. Try Knowledgebase and FAQ links !
|
|Also search the knowledgebases at http://www.ibphoenix.com
|
|++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
|
|Yahoo! Groups Links
|
|
|
|