Thread: How to improve postgres performace
Hi all, Some months ago i post a similar problem here i it was solved by running vaccumdb time by time. So, when i started using the postgres, i never been used the vacuumdb, and after 2 months i started using once a week, after few weeks, i tried once a day and now twice a day. At this weekend i have started to use pg_autovacuum with default settings. I really worried about that, because it's no enough anymore, and users claim about performace. But running the vacuumdb full, everthing starts to run better again, so i think the problem is not related to a specific query. What I can do to check what I have change to get more performance ? Could I use vacuum verbose to check what is going on ? So, how ? Most all the time, even user querying the server the machine is 96%-100% idle. The discs are SCSI, FreeBSD 5.3, the size of database is 1.1Gb, max 30 connections and 10 concurrent conections. My server have 512Mb Ram and 256Mb has changed to SHMAX. There is max 1000 inserted/excluded/Updated row by day. These are my kernel params: -------------------------- options SHMMAXPGS=65536 options SEMMNI=40 options SEMMNS=240 options SEMUME=40 options SEMMNU=120 Postgresql.conf non-default settings ------------------------------------ tcpip_socket = true max_connections = 30 shared_buffers = 1024 sort_mem = 2048 vacuum_mem = 16384 wal_buffers = 16 checkpoint_segments = 5 effective_cache_size = 16384 random_page_cost = 2 stats_start_collector = true stats_row_level = true I follow the most of all discussions in this group and tried myself change the parameters, but now, I don't know more what to do to get better performance. Thanks a Lot Rodrigo Moreno
"Rodrigo Moreno" <rodrigo.miguel@terra.com.br> writes:
> At this weekend i have started to use pg_autovacuum with default settings.
> I really worried about that, because it's no enough anymore, and users claim
> about performace. But running the vacuumdb full, everthing starts to run
> better again, so i think the problem is not related to a specific query.
It sounds like you may not have the FSM settings set large enough for
your database. The default settings are only enough for a small DB
(perhaps a few hundred meg).
regards, tom lane
Tom,
How to check if the value it's enough ? The log generate by vacuum verbose
can help ?
The current values for:
max_fsm_pages = 1048576
max_fsm_relations = 1000
this is enough ?
Regards,
Rodrigo
-----Mensagem original-----
De: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Enviada em: segunda-feira, 18 de abril de 2005 12:58
Para: Rodrigo Moreno
Cc: pgsql-performance@postgresql.org
Assunto: Re: [PERFORM] How to improve postgres performace
"Rodrigo Moreno" <rodrigo.miguel@terra.com.br> writes:
> At this weekend i have started to use pg_autovacuum with default settings.
> I really worried about that, because it's no enough anymore, and users
> claim about performace. But running the vacuumdb full, everthing
> starts to run better again, so i think the problem is not related to a
specific query.
It sounds like you may not have the FSM settings set large enough for your
database. The default settings are only enough for a small DB (perhaps a
few hundred meg).
regards, tom lane
"Rodrigo Moreno" <rodrigo.miguel@terra.com.br> writes:
> The current values for:
> max_fsm_pages = 1048576
> max_fsm_relations = 1000
> this is enough ?
That max_fsm_pages value is enough to cover 8Gb, so it should work OK
for a database disk footprint up to 10 or so Gb. I don't know how many
tables in your installation so I can't say if max_fsm_relations is high
enough, but you can check that by looking at the tail end of the output
of VACUUM VERBOSE. (Or just count 'em ;-))
Offhand these look reasonable, though, so if you are seeing database
bloat over time it probably means you need to tweak your autovacuum
settings. I'm not much of an autovacuum expert, but maybe someone
else can help you there.
You might want to keep track of physical file sizes over a period of
time and try to determine exactly where the bloat is happening.
regards, tom lane
> That max_fsm_pages value is enough to cover 8Gb, so it should work OK for a database disk footprint up to 10 or so Gb. > I don't know how many tables in your installation so I can't say if max_fsm_relations is high enough, but you can check >that by looking at the tail end of the output of VACUUM VERBOSE. (Or just count 'em ;-)) The last count in vacuum verbose shows me 92 relations, and I know the lower value for max_fsm_relations is enough, maybe I'll change to 500. > Offhand these look reasonable, though, so if you are seeing database bloat over time it probably means you need to tweak > your autovacuum settings. I'm not much of an autovacuum expert, but maybe someone else can help you there. I'll let the autovacuum running this week to see what happen. > You might want to keep track of physical file sizes over a period of time and try to determine exactly where the bloat > is happening. There is two mostly used and bigger tables, I'll keep eyes on both tables. Thanks Rodrigo Moreno
On Mon, Apr 18, 2005 at 11:36:01AM -0300, Rodrigo Moreno wrote: > I really worried about that, because it's no enough anymore, and users claim > about performace. But running the vacuumdb full, everthing starts to run > better again, so i think the problem is not related to a specific query. Vacuum full will skew your results, unless you plan on running vacuum full all the time. This is because you will always have some amount of dead tuples in a table that has any update or delete activity. A regular vacuum doesn't remove these tuples, it just marks them as available. So over time, depending on how frequently a table is vacuumed, it will settle down to a steady-state size that is greater than it's size after a vacuum full. -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"