Wednesday 17 February 2010

Database performance

Over the last couple of days I have been attending an Oracle 10g/11g performance tuning course given by iTrain, which had been booked by another team of programmers in the University where I work.


I have always been reluctant to be labeled as a relational database programmer or even worse as a part-time dba, since it has never appeared to be the most thrilling element of web development. Along with the need to keep it in perspective with other storage options such as RDF or object databases like the ZODB.


As a result I have probably under prioritized acquiring knowledge of database theory and skills, so it was a real eye opener to go on this course and realise just how little I knew about coding for database high performance. So my query optimization was pretty much limited to trying rewrites or the odd view, maybe adding the default type of index, and very occasionally running explain plan - but not really knowing how to fully interpret it!


Although the course was one focused on Oracle wrt. the details, the general principles were entirely applicable to any other relational database such as Postgres or MySQL.
For example the fundamental rdbms architecture of the optimizer (rule or cost based) that generates the query execution plans that govern query performance. How indexing, analyze statistics and other factors determine this. Are all common to Postgres and MySQL, and the process of writing and tuning your schema and queries based on this is one that should really be applied to all but the smallest scale data projects - ie a few hundred rows or less per table.


Some parts had wider applicability than that, for example the majority of the indexes information could apply just as well to the indexes used within the ZCatalog that comes as part of zopes object database.


So I aim to finish writing up a quick tips summary of the course for database performance tweaks that is fairly non-db platform specific.

Thursday 4 February 2010

django freaks ... or not?

I went to the local bristol and bath django users group meet up at the Watershed bar in Bristol last night.

Although I had feared they would be rather hardcore platform geeks ... actually they were surprisingly platform unspecific with pretty much everyone having done or doing stuff in something else, eg java .net or php.
No perl though - I guess that indicates the under 40s profile that I was attempting to subvert
;-)

There was a slight hint of plone / zope refugee status. Although nobody was using django as a CMS solution some were using drupal like we do in the ILRT that I work in at the University of Bristol.

Aside from the pure techiness it was good to chat with freelancers outside of academia, and as a group of practical developers there was none of that platform evangelism that be rather wearing at highly platform specific events.