[Gllug] SQL performance tuning (was: RE: Restore RAID0 /var)

[Peter Corlett]

On 26 Aug 2009, at 14:55, Walter Stanish wrote:
[...]
> Things like:
> - analysing and restructuring slow queries (thumbs up!)
> - memcache for frequent and expensive to generate content
>   (remove SQL use altogether).  Memcache can allow you
>   to store structures native to your webapp's language
>   direct in memory, so no parsing is necessary at all.
>   See http://www.danga.com/memcached/
>   (The drawback is that it requires reasonable knowledge
>   of the application, which can be a pain if you didn't
>   write it or are not familiar with the language it was
>   implemented in)


Those are actually application tuning, which is rather different to  
database tuning. Now, the greatest win is usually in application  
tuning, because you're usually optimising-out long queries, and the  
fastest query is one that isn't made, but sometimes that's not possible.

> - proper indexing / foreign key relations (thumbs up!)
> - database server cache tuning (thumbs up!)


- spindle optimisations: use Pg tablespaces to shuffle indices/tables  
onto other disks and/or move the transaction log to another disk or SSD.


-- 
Gllug mailing list  -  Gllug at gllug.org.uk
http://lists.gllug.org.uk/mailman/listinfo/gllug