Re: [PERFORM] Where do a novice do to make it run faster?

From: PFC <lists(at)peufeu.com>
Date: Mon Apr 28 2008 - 14:23:12 EDT

        You got the order slightly wrong I guess.

> 1) hardware

        Would only come first if your RAM is really too small, or you use RAID5 on write-heavy tables, or what limits you is transaction fsync (hint : 8.3).

        Adding RAM is cheap.

> 2) rewriting my queries and table structures

	This should really come first.
	Log expensive queries. Note that an expensive query can be a slow query,  

or be a rather fast query that you execute lots of times, or a very simple and fast query that you execute really really too often.

        Now ask yourself :
* What is this query supposed to do ?

• Do I need this query ?

	Example :
	You put your sessions in a database ?

	=> Perhaps put them in the good old filesystem ?


	Your PHP is loading lots of configuration from the database for every  
page.

	=> Cache it, generate some PHP code once and include it, put it in the  

session if it depends on the user, but don't reload the thing on each page !

	This feature is useless

	=> Do you really need to display a birthday cake on your forum for those  

users who have their birthday today ?

	UPDATEs...

	=> Do you really need to update the last time a user was online every  

time ? What about updating it every 5 minutes instead ?

• Is this query inside a loop ?
  => Use JOIN.
  
• Do I need all the rows from this query ?

        Example :
You use pagination and perform the same query changing LIMIT/OFFSET ? => Perform the query once, retrieve the first N pages of result, cache it in the session or in a table.

• You have a website ? => Use lighttpd and fastcgi
• Do I need all the columns from this query ?
• Do I suffer from locking ?

        etc.

Now you should see some easy targets.
For the queries that are slow, use EXPLAIN ANALYZE. Question your schema.
etc.

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Received on Mon Apr 28 14:18:47 2008