RES: RES: 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(dot)miguel(at)terra(dot)com(dot)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(at)sss(dot)pgh(dot)pa(dot)us]
Enviada em: segunda-feira, 18 de abril de 2005 12:58
Para: Rodrigo Moreno
Cc: pgsql-performance(at)postgresql(dot)org
Assunto: Re: [PERFORM] How to improve postgres performace

"Rodrigo Moreno" <rodrigo(dot)miguel(at)terra(dot)com(dot)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(dot)miguel(at)terra(dot)com(dot)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(at)decibel(dot)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?"

Hi all,

I need your help to determine the configuration of a server machine, and the
PG DBMS.
There will be not more than 25 concurrent users. They will use a business
software that accesses tha database. The database will be not that large, it
seems that none of the tables's recordcount will exceed 1-2 million, but
there will be a lot of small (<5000 record) tables. The numbert of tables
will be about 300. What server would you install to such a site to make the
database respond quickly in any case?
I would like to leave fsync on.
Perhaps you need some additional information. In this case just indicate it.

Thanks in advance,
Otto

Any XServe from Apple will do it.

János
On Jun 10, 2005, at 5:12 PM, Havasvölgyi Ottó wrote:

> Hi all,
>
> I need your help to determine the configuration of a server
> machine, and the
> PG DBMS.
> There will be not more than 25 concurrent users. They will use a
> business
> software that accesses tha database. The database will be not that
> large, it
> seems that none of the tables's recordcount will exceed 1-2
> million, but
> there will be a lot of small (<5000 record) tables. The numbert of
> tables
> will be about 300. What server would you install to such a site to
> make the
> database respond quickly in any case?
> I would like to leave fsync on.
> Perhaps you need some additional information. In this case just
> indicate it.
>
> Thanks in advance,
> Otto
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that
> your
> message can get through to the mailing list cleanly
>

------------------------------------------
"There was a mighty king in the land of the Huns whose goodness and
wisdom had no equal."
Nibelungen-Lied

Janos,

Thank you. Sorry, but I wanted to install Linux on the server, I haven't
mentioned it. I am not that familiar in the server-world. So, what
configuration is enough on X86 (32 bit) architecture for PostgreSQL with the
conditions listed in my previous post?

Thanks,
Otto

----- Original Message -----
From: "János" <janos(dot)lobb(at)yale(dot)edu>
To: "Havasvölgyi Ottó" <h(dot)otto(at)freemail(dot)hu>
Cc: <pgsql-novice(at)postgresql(dot)org>
Sent: Monday, June 13, 2005 3:25 PM
Subject: Re: [NOVICE] need suggestion for server sizing

Any XServe from Apple will do it.

János
On Jun 10, 2005, at 5:12 PM, Havasvölgyi Ottó wrote:

> Hi all,
>
> I need your help to determine the configuration of a server
> machine, and the
> PG DBMS.
> There will be not more than 25 concurrent users. They will use a
> business
> software that accesses tha database. The database will be not that
> large, it
> seems that none of the tables's recordcount will exceed 1-2
> million, but
> there will be a lot of small (<5000 record) tables. The numbert of
> tables
> will be about 300. What server would you install to such a site to
> make the
> database respond quickly in any case?
> I would like to leave fsync on.
> Perhaps you need some additional information. In this case just
> indicate it.
>
> Thanks in advance,
> Otto
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that
> your
> message can get through to the mailing list cleanly
>

------------------------------------------
"There was a mighty king in the land of the Huns whose goodness and
wisdom had no equal."
Nibelungen-Lied

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

i currently develop on a winxp laptop. i use cygwin
and my pgsql version is 7.4.x (x=3, maybe?).

i have to manually start up apache and pgsql. i'm
spending some time learning linux. i found that i
could edit my .bash_profile so i don't have to type
the path to cygserver every time i tried to start it.

i'd like to do something similar when using pg_ctl to
start and stop the postmaster.

i read the the help files for pg_ctl and it said
PGDATA was the default if there was no -D flag and
then a directy path to the data directory.

i want to set PGDATA path to my DATA directory, but i
can't find PGDATA on my system.

can anyone help here?

tia...

__________________________________
Yahoo! Mail Mobile
Take Yahoo! Mail with you! Check email on your mobile phone.
http://mobile.yahoo.com/learn/mail

--- operationsengineer1(at)yahoo(dot)com wrote:

> i currently develop on a winxp laptop. i use cygwin
> and my pgsql version is 7.4.x (x=3, maybe?).
>
> i have to manually start up apache and pgsql. i'm
> spending some time learning linux. i found that i
> could edit my .bash_profile so i don't have to type
> the path to cygserver every time i tried to start
> it.
>
> i'd like to do something similar when using pg_ctl
> to
> start and stop the postmaster.
>
> i read the the help files for pg_ctl and it said
> PGDATA was the default if there was no -D flag and
> then a directy path to the data directory.
>
> i want to set PGDATA path to my DATA directory, but
> i
> can't find PGDATA on my system.
>
> can anyone help here?
>
> tia...

i was able to solve this one. i added...

export PGDATA=/usr/share/postgresql/data
export
PATH=/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/share/postgresql/data

to my .bash_profile (i want a local configuration).

now all i have to do in cygwin to start up pgsql is to
type the following...

first...
cygserver &

once cygserver is up and running, i hit [enter] to get
a prompt then...

second...
pg_ctl start -o -i

kinda cool for a rookie - and we won't mention i've
been typing the directory path for almost a year... -lol-

__________________________________
Do you Yahoo!?
Yahoo! Mail - Helps protect you from nasty viruses.
http://promotions.yahoo.com/new_mail

Mr. Operations, I've had a similar frustrating experience with PGDATA;
basically, i can't make it "stick." Once I shut down the postgresql
server the PGDATA configuration is cleared and has to be stated again
before starting postgres, or I'll get an error message.

So I had to insert the command into the system start-up process.
In Gentoo, it's /etc/conf.d/local.start
and the command that works is ---

export PGDATA=/var/lib/postgresql/data

Mike

On 6/14/05, operationsengineer1(at)yahoo(dot)com <operationsengineer1(at)yahoo(dot)com> wrote:
> --- operationsengineer1(at)yahoo(dot)com wrote:
>
> > i currently develop on a winxp laptop. i use cygwin
> > and my pgsql version is 7.4.x (x=3, maybe?).
> >
> > i have to manually start up apache and pgsql. i'm
> > spending some time learning linux. i found that i
> > could edit my .bash_profile so i don't have to type
> > the path to cygserver every time i tried to start
> > it.
> >
> > i'd like to do something similar when using pg_ctl
> > to
> > start and stop the postmaster.
> >
> > i read the the help files for pg_ctl and it said
> > PGDATA was the default if there was no -D flag and
> > then a directy path to the data directory.
> >
> > i want to set PGDATA path to my DATA directory, but
> > i
> > can't find PGDATA on my system.
> >
> > can anyone help here?
> >
> > tia...
>
> i was able to solve this one. i added...
>
> export PGDATA=/usr/share/postgresql/data
> export
> PATH=/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/share/postgresql/data
>
> to my .bash_profile (i want a local configuration).
>
> now all i have to do in cygwin to start up pgsql is to
> type the following...
>
> first...
> cygserver &
>
> once cygserver is up and running, i hit [enter] to get
> a prompt then...
>
> second...
> pg_ctl start -o -i
>
> kinda cool for a rookie - and we won't mention i've
> been typing the directory path for almost a year... -lol-
>
>
>
> __________________________________
> Do you Yahoo!?
> Yahoo! Mail - Helps protect you from nasty viruses.
> http://promotions.yahoo.com/new_mail
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>