[Blueprint db-indexing-optimization] Improved indexing and optimization of the databases

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?



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.


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



Knut Staring

Knut Staring