Thanks - forwarding to the list for sharing.
···
---------- Forwarded message ----------
From: Jason Pickering jason.p.pickering@gmail.com
Date: Fri, Jun 12, 2009 at 3:19 PM
Subject: Re: [Blueprint db-indexing-optimization] Improved indexing and optimization of the databases
To: Knut Staring knutst@gmail.com
Cc: Ola Hodne Titlestad olatitle@gmail.com
Well, I do not think that I ever wrote anything down on this. But this
was the methodology that I was going to follow more or less…
I wanted to crank up the logging on Postgres and begin to record the
actual queries on the DB. One would probably have to find/write some
scripts to parse the log to extract out the queries and their
execution times. Some sort of statistical analysis could be done then
to find the 1) most frequently executed and 2) the most expensive to
execute and perhaps some variation in between. This part is not very
clear to me either, but I have not really come up with a better
starting point to begin to determine where to actually start. I
suppose you could begin to look at the fundamental structure of the
DB, but this really tells you nothing unless you know the actual
queries that are being executed against it. With these statistics in
hand, one could then begin to use “Query analyze” to see how the query
planner actually will execute the query, and using standard Postgres
tuning procedures from there.
As an extension of this, I think I am still not satisfied with the
discussion that we had in Geneva regarding pushing some work back to
the DB. I am convinced that certain procedures, such as the generation
of the datamart/report tables could be better accomplished using
native DB procedures. The trade-off here of course is that you would
lock yourself into a particular DB, but there could be big advantages
in terms of performance and scalability.
Looking at other things, like the extreme number of Postgres
connections that are opened up by DHIS, could also be maybe lumped
into a student project.
Just some more thoughts but maybe at least a starting point for a project?
Regards,
Jason
On Fri, Jun 12, 2009 at 3:09 PM, Knut Staringknutst@gmail.com wrote:
Did you ever record your ideas on this, Jason? I agree that it can be a
student project, but there should be a couple of quick wins possible -
indexes don’t take much effort to create.
k
On Fri, Jun 12, 2009 at 1:54 PM, Ola Hodne Titlestad olatitle@gmail.com > wrote:
Blueprint changed by Ola Hodne Titlestad:
Whiteboard changed to:
this might be a possible student project for fall09 in INF5750
–
Improved indexing and optimization of the databases
https://blueprints.launchpad.net/dhis2/+spec/db-indexing-optimization
–
Cheers,
Knut Staring
–
Cheers,
Knut Staring