Optimization tips and tricks

Question

I need to optimize our web service, but don't know where to begin. We're running GWT, PHP and PostgreSQL. Without even having peaked at any performance data, I'm guessing that the major optimizations are going to happen in the database.

I don't know anything about restructuring the DB, nor indexing. (Don't know anything 'bout DBs really.) All pointers greatly appreciated in any of the three areas!

Answer 1

Always start with measurement. Until you know where any bottlenecks are, you don't know what to improve. "Without even having peeked at any performance data" you're unlikely to make the right call - or at least, I frequently find that I make incorrect guesses about where performance is hurting. Maybe you're better at guessing than me :) (Of course, it may well be in the database in your code...)

Measuring tools can be as simple as log statements to see how long different types of requests take, to full profilers, query analyzers etc. On the database side, you'll almost certainly want to use EXPLAIN to look at the query execution plans... but before that you'll want to know which queries are hurting you in the first place, and more than that, which requests to the web service itself are costly.

Answer 2

Look at the best practices from yahoo before touching the db (and before that identify your bottleneck). You don't want to spend lot of time for a small speed improvement, usually big speed ups could be achieved using the tips in the link.

To profile your frontend you could use page speed.

Answer 3

As the rest have said make sure you identify your bottlenecks first.

As far as your concerns about the DB goes i would suggest creating some indices on your tables to speed up slow queries (only when identified)

http://www.postgresql.org/docs/8.2/static/sql-createindex.html

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] name ON table [ USING method ]
    ( { column | ( expression ) } [ opclass ] [, ...] )
    [ WITH ( storage_parameter = value [, ... ] ) ]
    [ TABLESPACE tablespace ]
    [ WHERE predicate ]

Please make sure to do some reading on when and when not to use an index