From: PFC <lists@peufeu.com>
To: A B <gentosaker@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Where do a novice do to make it run faster?
Date: Mon, 28 Apr 2008 20:23:12 +0200
Message-ID: <op.uab2oycdcigqcu@apollo13.peufeu.com> (raw)
In-Reply-To: <dbbf25900804280856m41c67d96heaa616d39d6c44e0@mail.gmail.com>
References: <dbbf25900804280856m41c67d96heaa616d39d6c44e0@mail.gmail.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@peufeu.com, gentosaker@gmail.com
Subject: Re: Where do a novice do to make it run faster?
In-Reply-To: <op.uab2oycdcigqcu@apollo13.peufeu.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox