Re: [firebird-support] RE: Stored Proc optimisation advice
On Wed, Sep 12, 2012 at 3:06 AM, Maya Opperman wrote: > > >>I have a stored proc that calculates account balances. Most of the time > it takes between 1-2 seconds, but every now and then it takes 22 seconds. > It seems to be approx. every 5 or 6 records. > > >>If I run the sub proc to get the balance one by one though, it runs at > 1-2 seconds. So, using IBExpert, I manually worked my way through the > list, and on the 5th account - 22 seconds to execute. I try the very same > account again, and it takes 1 seconds. I need to carry on down the list > and do a few more, before I can get it to go slow again. > > >>Any ideas what to try? Get the statistics on the various runs of the sub-procedure- reads, writes, fetches, and marks. There may be other statistics available now describing your cache hit rate. You should also check the depth of indexes (gstat). Which type of garbage collection are you using separate thread, cooperative, or blended? How many tables and indexes are you touching in computing an account balance? Good luck, Ann > [Non-text portions of this message have been removed]
RE: [firebird-support] RE: Stored Proc optimisation advice
>> If I run the sub proc to get the balance one by one though, it runs at >> 1-2 seconds. So, using IBExpert, I manually worked my way through the >> list, and on the 5th account - 22 seconds to execute. I try the very >> same account again, and it takes 1 seconds. I need to carry on down >> the list and do a few more, before I can get it to go slow again. >> >> Any ideas what to try? >Get the statistics on the various runs of the sub-procedure- reads, writes, >fetches, and marks. > There may be other statistics available now describing your cache hit rate. > You should also check the depth of indexes (gstat). > Which type of garbage collection are you using separate thread, > cooperative, or blended? How many tables and indexes are you touching in >computing an account balance? Thanks, Ann! I'll poke around in IBExpert, and the monitoring tables and see if I can figure out how to do all this - it'll be a learning curve for me ;-)
RE: [firebird-support] RE: Stored Proc optimisation advice
>> If I run the sub proc to get the balance one by one though, it runs >> at >> 1-2 seconds. So, using IBExpert, I manually worked my way through >> the list, and on the 5th account - 22 seconds to execute. I try the >> very same account again, and it takes 1 seconds. I need to carry on >> down the list and do a few more, before I can get it to go slow again. >> >> Any ideas what to try? >Get the statistics on the various runs of the sub-procedure- reads, writes, >fetches, and marks. No writes. 47 000 indexed reads. > There may be other statistics available now describing your cache hit rate. > You should also check the depth of indexes (gstat). I'm not 100% sure you mean here... > Which type of garbage collection are you using separate thread, > cooperative, or blended? Using 2.5 classic, so after googling, I take it, it must be cooperative (By the way, I haven't changed any oconfig settings when installing either) How many tables and indexes are you touching in >computing an account balance? Just one. Er, no actually two. Having another look now at the main procedure, it's actually calculating 3 balances using table A, and then one using table B. Excet the order is: TableA's balance TableB's balance Table A's other balance Table A's third balance with different criteria. I changed the order to work with the three balances from table A first, then table B, and voila - the whole thing went from well over a minute down to 12 seconds ;-) I am guessing what's happening, is there is now a lot less swapping out of data between buffer and disk. I'm not sure if that is a sign something is wrong with my setup, or whether I just need to be careful of that kind of thing when writing stored procedures? Thanks Maya
Re: [firebird-support] RE: Stored Proc optimisation advice
On Thu, Sep 13, 2012 at 9:50 AM, Maya Opperman wrote: > > >Get the statistics on the various runs of the sub-procedure- reads, > writes, fetches, and marks. > No writes. 47 000 indexed reads. > Lets try that one again. I'd like both reads and fetches, with statistics for a run of the subquery that's fast and one that's slow. Did you mean that computing one account balance involved forty-seven thousand indexed reads? That's some account! > > > There may be other statistics available now describing your cache hit > rate. You should also check the depth of indexes (gstat). > I'm not 100% sure you mean here... > Gstat will tell you how deep your indexes are. Firebird indexes are very broad based triangles, with a single page at the top, lots of pages on the next level down, and enormous numbers of pages on the level below that. The taller (or deeper) the triangle, the slower it is. The height and width are determined by page size and key size. An index with four or more levels is an indication that you should increase the page size for your database. Run gstat and search for the indexes used by this sub-procedure. I'm sure you mentioned it somewhere, but what is the database page size? And the cache size? > > > Which type of garbage collection are you using separate thread, > > cooperative, or blended? > Using 2.5 classic, so after googling, I take it, it must be cooperative > (By the way, I haven't changed any oconfig settings when installing either) > Just looking for something that might push a lot of pages out of cache. Probably not garbage collection. > > >How many tables and indexes are you touching in > >computing an account balance? > Just one. > Er, no actually two. > Having another look now at the main procedure, it's actually calculating 3 > balances using table A, and then one using table B. > Excet the order is: > TableA's balance > TableB's balance > Table A's other balance > Table A's third balance with different criteria. > > I changed the order to work with the three balances from table A first, > then table B, and voila - the whole thing went from well over a minute down > to 12 seconds ;-) I am guessing what's happening, is there is now a lot > less swapping out of data between buffer and disk. > That's good. > > I'm not sure if that is a sign something is wrong with my setup, or > whether I just need to be careful of that kind of thing when writing stored > procedures? Preserving locality of reference is always a good thing. I would also try a large cache size and possibly a large page size, but would first look for differences in the number of reads on fast and slow runs of the sub-procedure and index depth. Good luck, Ann > [Non-text portions of this message have been removed]
RE: [firebird-support] RE: Stored Proc optimisation advice
>> >Get the statistics on the various runs of the sub-procedure- reads, >> writes, fetches, and marks. >> No writes. 47 000 indexed reads. >Lets try that one again. I'd like both reads and fetches, with statistics >for a run of the subquery that's fast and one that's slow. Did you mean >that computing one account balance involved forty-seven thousand indexed >reads? That's some account! Yup. BUT, the weird thing is, it's that SAME account that is sometimes fast and sometimes slow. It just depends on what other table/account you've queried beforehand. >Gstat will tell you how deep your indexes are. Firebird indexes are very >broad based triangles, with a single page at the top, lots of pages on the >>next level down, and enormous numbers of pages on the level below that. >The taller (or deeper) the triangle, the slower it is. The height and width >are determined by page size and key size. An index with four or more >levels >is an indication that you should increase the page size for your database. >Run gstat and search for the indexes used by this sub-procedure. Max depth in the whole database is 3 ;-) >I'm sure you mentioned it somewhere, but what is the database page size? > And the cache size? Generation 1311 Page size 8192 ODS version 11.2 Oldest transaction 1292 Oldest active 1293 Oldest snapshot 1293 Next transaction1294 Bumped transaction 1 Sequence number 0 Next attachment ID 9 Implementation ID 16 Shadow count0 Page buffers1000 was 150, but I tried increasing to see if it would make a difference - it didn't seem to have any effect Next header page0 Database dialect3 Creation date Sep 11, 2012 11:43:22 Attributes force write, no reserve Sweep interval: 20
Re: [firebird-support] RE: Stored Proc optimisation advice
Get the statistics on the various runs of the sub-procedure- reads, >>> writes, fetches, and marks. >>> No writes. 47 000 indexed reads. > > >> Lets try that one again. I'd like both reads and fetches, with statistics >> for a run of the subquery that's fast and one that's slow. Did you mean >> that computing one account balance involved forty-seven thousand indexed >> reads? That's some account! > > Yup. BUT, the weird thing is, it's that SAME account that is sometimes fast > and sometimes slow. It just depends on what other table/account you've > queried beforehand. > > >> Gstat will tell you how deep your indexes are. Firebird indexes are very >> broad based triangles, with a single page at the top, lots of pages on the >> >next level down, and enormous numbers of pages on the level below that. > >The taller (or deeper) the triangle, the slower it is. The height and > width are determined by page size and key size. An index with four or more > >levels is an indication that you should increase the page size for your > database. Run gstat and search for the indexes used by this sub-procedure. > > Max depth in the whole database is 3 ;-) > >> I'm sure you mentioned it somewhere, but what is the database page size? >> And the cache size? > > Generation 1311 > Page size 8192 > ODS version 11.2 > Oldest transaction 1292 > Oldest active 1293 > Oldest snapshot 1293 > Next transaction1294 > Bumped transaction 1 > Sequence number 0 > Next attachment ID 9 > Implementation ID 16 > Shadow count0 > Page buffers1000 was 150, but I tried increasing > to see if it would make a difference - it didn't seem to have any effect > Next header page0 > Database dialect3 > Creation date Sep 11, 2012 11:43:22 > Attributes force write, no reserve > Sweep interval: 20 Having "no reserve" isn't a good option for a regular read/write production database, because this basically prevents having back record versions on the same page as the primary record version, thus additional page reads are necessary. -- With regards, Thomas Steinmaurer http://www.upscene.com/
RE: [firebird-support] RE: Stored Proc optimisation advice
>> Attributes force write, no reserve >Having "no reserve" isn't a good option for a regular read/write production >database, because this basically prevents having back record versions on the >same page as the primary record version, thus additional page reads are >necessary. Thanks Thomas! Is this by any chance related to the USE_ALL_SPACE option you have when restoring a database? Or is GFIX the only way of updating it?
Re: [firebird-support] RE: Stored Proc optimisation advice
>>> Attributes force write, no reserve > >> Having "no reserve" isn't a good option for a regular read/write production >> database, because this basically prevents having back record versions on the >> same page as the primary record version, thus additional page reads are >> necessary. > > Thanks Thomas! > > Is this by any chance related to the USE_ALL_SPACE option you have when > restoring a database? Correct. > Or is GFIX the only way of updating it? gfix also has an -use option, but this doesn't change the fill distribution of existing pages. -- With regards, Thomas Steinmaurer http://www.upscene.com/
RE: [firebird-support] RE: Stored Proc optimisation advice
Attributes force write, no reserve >>> Having "no reserve" isn't a good option for a regular read/write production >>> database, because this basically prevents having back record versions on >>> the same page as the primary record version, thus additional page reads are >>> necessary. > >> Thanks Thomas! > OK, I figured out what happened. If you restore just once using 'use all space', then the next time you backup/restore, it remembers that setting! (I'm using FIBPlus's components, but I presume GBAK will do the same thing) So now, I'm going to have to make sure I run GFIX after every restore, where 'use all space' has not been specified, to make sure it doesn't stay in that mode.
Re: [firebird-support] RE: Stored Proc optimisation advice
On Fri, Sep 14, 2012 at 3:44 AM, Maya Opperman wrote: > > > I'd like both reads and fetches, with statistics > >for a run of the subquery that's fast and one that's slow. Did you mean > >that computing one account balance involved forty-seven thousand indexed > >reads? That's some account! > > Yup. BUT, the weird thing is, it's that SAME account that is sometimes > fast and sometimes slow. It just depends on what other table/account you've > queried beforehand. > Right, but in order to understand why one is fast and one is slow, it would be good to get statistics on a fast run and a slow one, regardless of which account is actually being balanced. > > Max depth in the whole database is 3 ;-) > Good. > > >I'm sure you mentioned it somewhere, but what is the database page size? > > And the cache size? > > Page size 8192 > Page buffers1000 was 150, but I tried increasing > to see if it would make a difference - it didn't seem to have any effect > Attributes force write, no reserve > Sweep interval: 20 > Very odd.. Good luck, Ann [Non-text portions of this message have been removed]
