SEO

So, it is time to improve performance, it is running to slow.
AFAIK (as a novice) there are a few general areas:

1) hardware
2) rewriting my queries and table structures
3) using more predefined queries
4) tweek parameters in the db conf files

Of these points:
1) is nothing I can do about right now, but in the future perhaps.
2) will be quite hard right now since there is more code than time.
3) almost like 2 but perhaps more do-able with the current constraints.
4) This seems to be the easiest one to start with...

So what should I do/read concerning point 4?
If you have other good suggestions I'd be very interested in that.

Thank you :-)

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

A B wrote:
> So, it is time to improve performance, it is running to slow.
> AFAIK (as a novice) there are a few general areas:
>
> 1) hardware
> 2) rewriting my queries and table structures
> 3) using more predefined queries
> 4) tweek parameters in the db conf files
>
> Of these points:
> 1) is nothing I can do about right now, but in the future perhaps.
> 2) will be quite hard right now since there is more code than time.
> 3) almost like 2 but perhaps more do-able with the current constraints.
> 4) This seems to be the easiest one to start with...
>
> So what should I do/read concerning point 4?
> If you have other good suggestions I'd be very interested in that.
>
> Thank you :-)
>

1st, change your log settings log_min_duration_statement to something
like 1000 (one second). This will allow you to see which statements
take the longest.

2nd. Use EXPLAIN ANALYZE on those statements to determine what is
taking a long time and focus on optimizing those statements that take
the longest to execute.

That ought to get you a long way down the road.

-Dennis

> 1) hardware
> 2) rewriting my queries and table structures
> 3) using more predefined queries
> 4) tweek parameters in the db conf files
>
> Of these points:
> 1) is nothing I can do about right now, but in the future perhaps.
> 2) will be quite hard right now since there is more code than time.
> 3) almost like 2 but perhaps more do-able with the current constraints.
> 4) This seems to be the easiest one to start with...
>
> So what should I do/read concerning point 4?
> If you have other good suggestions I'd be very interested in that.
>
> Thank you :-)

You can provide information postgresql-version, what type of queries
you're running, some explain analyze of those, and what type of
hardware you're running and what OS is installed.

--
regards
Claus

When lenity and cruelty play for a kingdom,
the gentlest gamester is the soonest winner.

Shakespeare

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

A B wrote:
> So, it is time to improve performance, it is running to slow.
> AFAIK (as a novice) there are a few general areas:
>
> 1) hardware
> 2) rewriting my queries and table structures
> 3) using more predefined queries
> 4) tweek parameters in the db conf files
>
> Of these points:
> 1) is nothing I can do about right now, but in the future perhaps.
> 2) will be quite hard right now since there is more code than time.
> 3) almost like 2 but perhaps more do-able with the current constraints.
> 4) This seems to be the easiest one to start with...
>
> So what should I do/read concerning point 4?
> If you have other good suggestions I'd be very interested in that.
>
Go back to step zero - gather information that would be helpful in
giving advice. For starters:
- What hardware do you currently have?
- What OS and version of PG?
- How big is the database?
- What is the nature of the workload (small queries or data-mining, how
many simultaneous clients, transaction rate, etc.)?
- Is PG sharing the machine with other workloads?

Then edit your postgresql.conf file to gather data (see
http://www.postgresql.org/docs/8.3/i...ng-stats.html).
With stat collection enabled, you can often find some low-hanging fruit
like indexes that aren't used (look in pg_stat_user_indexes) - sometime
because the query didn't case something in the where-clause correctly.

Also look at
http://www.postgresql.org/docs/8.3/i...g-logging.html
- especially the log_min_duration_statement setting to find long-running
queries. You will probably need to try different settings and watch the
log. Logging impacts performance so don't just set to log everything and
forget. You need to play with it.

Don't discount step 2 - you may find you can rewrite one inefficient but
frequent query. Or add a useful index on the server.

Cheers,
Steve




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

gentosaker@gmail.com ("A B") writes:
> So, it is time to improve performance, it is running to slow.
> AFAIK (as a novice) there are a few general areas:
>
> 1) hardware
> 2) rewriting my queries and table structures
> 3) using more predefined queries
> 4) tweek parameters in the db conf files
>
> Of these points:
> 1) is nothing I can do about right now, but in the future perhaps.
> 2) will be quite hard right now since there is more code than time.
> 3) almost like 2 but perhaps more do-able with the current constraints.
> 4) This seems to be the easiest one to start with...
>
> So what should I do/read concerning point 4?
> If you have other good suggestions I'd be very interested in that.
>
> Thank you :-)

In the order of ease of implementation, it tends to be...

1. Tweak postgresql.conf
2. Make sure you ran VACUUM + ANALYZE
3. Find some expensive queries and try to improve them, which might
involve changing the queries and/or adding relevant indices
4. Add RAM to your server
5. Add disk to your server
6. Redesign your application's DB schema so that it is more performant
by design

URL below may have some material of value...
--
select 'cbbrowne' || '@' || 'cbbrowne.com';
http://linuxfinances.info/info/postg...rformance.html
It is usually a good idea to put a capacitor of a few microfarads
across the output, as shown.

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

Here is some more information.

Size of database:

du -sh /var/lib/pgsql/data/base/*
4,1M /var/lib/pgsql/data/base/1
4,1M /var/lib/pgsql/data/base/10792
4,1M /var/lib/pgsql/data/base/10793
9,1M /var/lib/pgsql/data/base/16388
11M /var/lib/pgsql/data/base/19233
1,6G /var/lib/pgsql/data/base/20970

I'm not sure what the size acctually is... But I can't imagine that it
is 1,6 GB!!! I'd say I have 11MB of data in it...

Cpu is Intel CoreDuo E6750, 4 GB RAM
Harddiscs are two Segate 320 GB SATA discs. running software raid
(!!), raid-1.Yes, this might be a big performance hit, but that is
what I have right now, in the future I can throw more money on
hardware.

Will I see a general improvement in performance in 8.3.X over 8.1.11?


2008/4/29 A B <gentosaker@gmail.com>:
> Right now, version 8.1.11 on centos.x86-64, intel dual core cpu with 2
> sata discs (mirror raid)
>
> The queries are most select/inserts.. I guess... I'm not sure exactly
> what to answer on that.
> "explain analyze" is something I have not read about yet.
>
>
> 2008/4/28 Claus Guttesen <kometen@gmail.com>:
>
>
> > > 1) hardware
> > > 2) rewriting my queries and table structures
> > > 3) using more predefined queries
> > > 4) tweek parameters in the db conf files
> > >
> > > Of these points:
> > > 1) is nothing I can do about right now, but in the future perhaps.
> > > 2) will be quite hard right now since there is more code than time.
> > > 3) almost like 2 but perhaps more do-able with the current constraints.
> > > 4) This seems to be the easiest one to start with...
> > >
> > > So what should I do/read concerning point 4?
> > > If you have other good suggestions I'd be very interested in that.
> > >
> > > Thank you :-)
> >
> > You can provide information postgresql-version, what type of queries
> > you're running, some explain analyze of those, and what type of
> > hardware you're running and what OS is installed.
> >
> > --
> > regards
> > Claus
> >
> > When lenity and cruelty play for a kingdom,
> > the gentlest gamester is the soonest winner.
> >
> > Shakespeare
> >
>

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

"A B" <gentosaker@gmail.com> writes:
> I'm not sure what the size acctually is... But I can't imagine that it
> is 1,6 GB!!! I'd say I have 11MB of data in it...

Sounds like you've got a rather severe case of table and/or index bloat.
This is typically caused by not vacuuming often enough.

The easiest way to get the size back down is probably to dump and reload
the database. After that you need to look at your vacuuming practices.

> Will I see a general improvement in performance in 8.3.X over 8.1.11?

Probably so, if only because it has autovacuum turned on by default.
That's not really a substitute for careful administration practices,
but it helps.

regards, tom lane

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