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=20=
=20
on write-heavy tables, or what limits you is transaction fsync (hint :=20=
=20
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,=
=20=20
or be a rather fast query that you execute lots of times, or a very
simple=
=20=20
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 ?
=3D> Perhaps put them in the good old filesystem ?
Your PHP is loading lots of configuration from the database for every=20=
=20
page.
=3D> Cache it, generate some PHP code once and include it, put it in the=
=20=20
session if it depends on the user, but don't reload the thing on each
page=
=20=20
!
This feature is useless
=3D> Do you really need to display a birthday cake on your forum for
those=
=20=20
users who have their birthday today ?
UPDATEs...
=3D> Do you really need to update the last time a user was online
every=20=
=20
time ? What about updating it every 5 minutes instead ?
* Is this query inside a loop ?
=3D> Use JOIN.
* Do I need all the rows from this query ?
Example :
You use pagination and perform the same query changing LIMIT/OFFSET ?
=3D> Perform the query once, retrieve the first N pages of result, cache
it=
=20=20
in the session or in a table.
* You have a website ?
=3D> 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.
--=20
Sent via pgsql-performance mailing list (pgsql-performance@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
|
