[Gllug] SQL performance tuning

[Karanbir Singh]

On 08/27/2009 02:31 PM, Walter Stanish wrote:
> IMHO and IIRC the other poster's, only the poor lost
> souls floating about the bureaucracy of large organisations
> (often not the case) have to put up with such artificial
> distinctions as DBA/webdev/sysadmin roles, commonly they
> are the same person.

In small - 2 to 4 man companies, yes they need to make do with 1 person, 
who often has either very basic knowledge of all the things and focus's 
only on one side[1], or they 'make do' with things since they are 
unaware of issues that might be impacting them.

Ofcourse, I dont know what skill levels are being spoken about here - so 
I am assuming its at a middle to upper level's. And that level I would 
not expect a php coder to be working on elevator logic or fabric tuning.

> Also IMHO at a cultural level, the whole idea of 'not my
> problem' and turf-setting isn't a very (hackerly or)
> admirable approach to technical problems, but rather a
> terrible shame, in that it artificially limits the curious
> mind / motivated programmer!

I spent most of my time in an organisation where these areas are well 
defined, and people tend to 'pair' in groups that allow a much wider 
technical expertise tackle individual problems. Often these pairs are 
stretched to across 'teams'.

The sort of 'not my problem' attitude stems from either bad management ( 
or the lack of ), or shallow technical capabilities in the trenches. 
I've also seen the flip side : people jumping into problems best handled 
elsewhere - and that is just exceptionally bad management.

> I'm curious as to what percentage of PgSQL databases you
> estimate actually hit the disk for indexes?  I'd have

This is actually more of a problem with mysql than it is with pgsql. 
Both of them handle memory buffers / caching very differently and 
depending on what your rate of change for the db is give you different 
results. I've seen pgsql > mysql for most typical small db roles ( where 
datachange rate is <= 10% a day ). The single hardest impact comes from 
what happens with caches' when an index update takes place. But here are 
quite a few other contributing factors as well, including join planing.[2]

Pgsql creates transaction logs and depending on how you load / change 
data, its quite advisable to put that on a different spindle so as to 
not create disk contention. There isnt much to it really.

Fwiw, I've successfully used partitioning within the db, on mysql-5.1 as 
well, so its not something that only works on pgsql anymore.

- KB

[1]: In the second half of last year, I helped 3 companies dig 
themselves out of sysadmin-hell they had created for themselves by 
trusting people with app development experience ( and not sysadmin ) to 
manage their machines. All of these companies employ less than 15 
people. One company was able to reduced overall operating cost by 12% - 
just doing things differently.

[2]: I am sure there are more db specific lists that would get into the 
individual nitty gritty of things.

-- 
Karanbir Singh : http://www.karan.org/  : 2522219 at icq
-- 
Gllug mailing list  -  Gllug at gllug.org.uk
http://lists.gllug.org.uk/mailman/listinfo/gllug