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
On Fri, Sep 14, 2012 at 3:44 AM, Maya Opperman m...@omniaccounts.co.zawrote: 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]
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 Wed, Sep 12, 2012 at 3:06 AM, Maya Opperman m...@omniaccounts.co.zawrote: 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]
