Thread: Where do a novice do to make it run faster?
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 :-)
> 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
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
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/interactive/monitoring-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/interactive/runtime-config-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
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/postgresqlperformance.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.
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 > > >
"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