How to quickly identify and resolve application bottlenecks Johan Forssblad, G4 IT AB Managing Director / Co founder

© 2004 Progress Software Corporation2 Simplify your business Progress Developers World 2004 Performance in general

© 2004 Progress Software Corporation3 Simplify your business Progress Developers World 2004 Experience #1 n Poor performance is often caused by –Missing/incorrect index definitions –Misuse of indexes (bad coding)

© 2004 Progress Software Corporation4 Simplify your business Progress Developers World 2004 Experience #2 n Using promon / vmstat etc is often not very efficient –Especially not for small systems (in terms of # of users and size of database)

© 2004 Progress Software Corporation5 Simplify your business Progress Developers World 2004 Challenges n Quickly find REAL bottlenecks n Quickly analyze index definitions and usage n Quickly determine effect of non-optimal indexes

© 2004 Progress Software Corporation6 Simplify your business Progress Developers World 2004 Progress Profiler Non supported Progress feature Records execution time per statement No extra coding required Works fine in runtime environment Can be started manually or by startup parameters Located in $DLC/src/samples/profiler (documentation, sourcecode and.df)

© 2004 Progress Software Corporation7 Simplify your business Progress Developers World 2004 Progress Profiler

© 2004 Progress Software Corporation8 Simplify your business Progress Developers World 2004 Progress profiler

© 2004 Progress Software Corporation9 Simplify your business Progress Developers World 2004 Static index analysis Home grown tool Using output from compile … xref Shows all source code using indexes (SEARCH in xref) Shows search criteria on all index- components Allows us to quickly find poorly utilized indexes

© 2004 Progress Software Corporation10 Simplify your business Progress Developers World 2004 Static index analysis

© 2004 Progress Software Corporation11 Simplify your business Progress Developers World 2004 Xref tool

© 2004 Progress Software Corporation12 Simplify your business Progress Developers World 2004 Dynamic index analysis Home grown tool Using Virtual System Tables and session db-triggers Showing (per table): Record reads/writes in database Number of useful records (records really wanted by the client) We can quickly determine the effect of non optimal indexing Or: What is the cost of non optimal indexes

© 2004 Progress Software Corporation13 Simplify your business Progress Developers World 2004 Progress Profiler

© 2004 Progress Software Corporation14 Simplify your business Progress Developers World 2004 Tablestat

© 2004 Progress Software Corporation15 Simplify your business Progress Developers World 2004 Tying it all together By starting the tools persistent you can make a simple integration in order to speed up the analysis.

© 2004 Progress Software Corporation16 Simplify your business Progress Developers World 2004 Progress Profiler

© 2004 Progress Software Corporation17 Simplify your business Progress Developers World 2004 Progress Profiler