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

[Walter Stanish]

> ... I want to do some testing of MySQL and PostgreSQL 
> performance for a web application which will have a 
> lot of backend data to search on a regular basis. 

IMHO if your SQL performance is getting to the point
where it's disk-bound, then there are much more 
significant optimisations you can perform than switching
to RAID0.  Of course, you are absolutely right that
throwing 2 or 4 x SSD on RAID0 will speed up read
performance, but other strategies can often be more
powerful.

Things like:
 - analysing and restructuring slow queries (thumbs up!)
 - proper indexing / foreign key relations (thumbs up!)
 - database server cache tuning (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)

Failing the above, for a 'quick fix' (less human time, 
more money), simply throw more memory at the database 
server.

For MySQL tuning, I can recommend MySQL Administrator from:
 http://dev.mysql.com/downloads/gui-tools/5.0.html
... it will let you view cache utilisation graphically, which
can make things a lot more comfortable when testing complex
database applications.

More info at: http://forums.mysql.com/read.php?24,92131,92131

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