Performance tuning suggestions to improve Financial Report?

[Andy Conn]

Does anybody have any performance tuning suggestions to improve Financial Report?

We have a freshly installed system with very few financial transactions (select count(*) from fact_acct --> ~900) and yet running a Financial Report to report account balances is abysmally slow. I have already set the following PostgreSQL parameters (from the default .conf) based on pgtune:

max_connections = 200

shared_buffers = 512MB

effective_cache_size = 3GB

work_mem = 6116kB

maintenance_work_mem = 256MB

min_wal_size = 1GB

max_wal_size = 2GB

checkpoint_completion_target = 0.7

wal_buffers = 16MB

default_statistics_target = 100

Suggestions?

Alternatively, has anyone developed a technique to report current account balances in Window? Can someone suggest a query?

[Denis González]

Andy, I don't remember if fact_acct comes with indexes by default.

You should take a look at the Financial Report code and make the appropriate indexes needed to speed it.

I currently have the following indexes:

---------------------------

-- Index: fact_acct_account

-- DROP INDEX fact_acct_account;

CREATE INDEX fact_acct_account

  ON fact_acct

  USING btree

  (ad_client_id, ad_org_id, c_acctschema_id, account_id);

---------------------------

-- Index: fact_acct_ad_table_id_record_id_idx

-- DROP INDEX fact_acct_ad_table_id_record_id_idx;

CREATE INDEX fact_acct_ad_table_id_record_id_idx

  ON fact_acct

  USING btree

  (ad_table_id, record_id);

---------------------------

-- Index: fact_acct_dateacct

-- DROP INDEX fact_acct_dateacct;

CREATE INDEX fact_acct_dateacct

  ON fact_acct

  USING btree

  (dateacct);

-----------------------------------

I do have other indexes, but those others are very aimed to my own business logic, so they could not mean any impact to your case.

Check if index creation could help your case, because I don't think postgresql.conf is your problem with that small dataset.

--
You received this message because you are subscribed to the Google Groups "iDempiere" group.
To unsubscribe from this group and stop receiving emails from it, send an email to idempiere+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/idempiere/fb8f9662-86c2-424a-983c-8df5d1580cd6%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Andy Conn]

SOLVED

It had nothing to do with the database it turns out. I discovered this after testing with another system pointing to the same database. The culprit (drum roll): "header-logo.png". It was somehow corrupted and causing this error (which I failed to notice):

22:28:53.779 Language.getLanguage: Adding Language=es, Country=CO, Locale=es_CO [31]

sun.awt.image.PNGImageDecoder$PNGException: Broken file

        at sun.awt.image.PNGImageDecoder.pngassert(PNGImageDecoder.java:94)

        at sun.awt.image.PNGImageDecoder.handleChunk(PNGImageDecoder.java:107)

        at sun.awt.image.PNGImageDecoder.getData(PNGImageDecoder.java:726)

        at sun.awt.image.PNGImageDecoder.produceImage(PNGImageDecoder.java:252)

        at sun.awt.image.InputStreamImageSource.doFetch(InputStreamImageSource.java:269)

        at sun.awt.image.ImageFetcher.fetchloop(ImageFetcher.java:205)

        at sun.awt.image.ImageFetcher.run(ImageFetcher.java:169)

sun.awt.image.PNGImageDecoder$PNGException: Broken file

        at sun.awt.image.PNGImageDecoder.pngassert(PNGImageDecoder.java:94)

        at sun.awt.image.PNGImageDecoder.handleChunk(PNGImageDecoder.java:107)

        at sun.awt.image.PNGImageDecoder.getData(PNGImageDecoder.java:726)

        at sun.awt.image.PNGImageDecoder.produceImage(PNGImageDecoder.java:252)

        at sun.awt.image.InputStreamImageSource.doFetch(InputStreamImageSource.java:269)

        at sun.awt.image.ImageFetcher.fetchloop(ImageFetcher.java:205)

        at sun.awt.image.ImageFetcher.run(ImageFetcher.java:169)

and subsequent attempts to run would show a timeout error in ImageElement.waitForLoad (Image image)

So it was the timeout (about 30 seconds) causing the performance problem. I do not know why header-logo.png is connected.'

- A

--
You received this message because you are subscribed to a topic in the Google Groups "iDempiere" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/idempiere/5tJZ_HrJtBU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to idempiere+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/idempiere/CAPzrMzLsQi%2BGNYyyd_h-dFXhvNhg7pnFpB3HLB1TUCzhta%2ByJg%40mail.gmail.com.

[hunonu]

My simple trick is: After clicking Ok on any report windows, instead of waiting on the same window --> switch to another window --> the report will be generated immediately --> switch back to your report window.

Not sure if this is a bug or a weird feature.

[Andy Conn]

Now THAT is weird. Thanks Nu Nuynh.