Thread: Maximum Performance

Maximum Performance

From
"Jean Huveneers"
Date:
Hi,

Within an couple of weeks we will start using PostgreSQL on Mandrake 8.1
in real business (we have been testing, for over a half year).

In future we will have some tables with 100.000+ records an the system
has te work very fast.

I know that speed of querries depend much on the amount of availible RAM
to PostgreSQL, the server will only run the databases. What amount is
RAM is usefull (I meen, does Postgres use the RAM, if availible, up to 2
GB)?

I'm can chose for a Dual Athlon MP on a Tyan Thunder K7 motherbord, but
does PostgreSQL use the 2 processors?

Regards,

Jean Huveneers

Re: Maximum Performance

From
Luis Amigo
Date:
Jean Huveneers wrote:

> Hi,
>
> Within an couple of weeks we will start using PostgreSQL on Mandrake 8.1
> in real business (we have been testing, for over a half year).
>
> In future we will have some tables with 100.000+ records an the system
> has te work very fast.
>
> I know that speed of querries depend much on the amount of availible RAM
> to PostgreSQL, the server will only run the databases. What amount is
> RAM is usefull (I meen, does Postgres use the RAM, if availible, up to 2
> GB)?
>
> I'm can chose for a Dual Athlon MP on a Tyan Thunder K7 motherbord, but
> does PostgreSQL use the 2 processors?
>
> Regards,
>
> Jean Huveneers
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

On my own experience I will tell you that if you're able to force postgres
to keep all database in memory it will be very fast, so memory only depends
on your
database size.
Each backend may run on a different processor, so the more processors u
have the more backends u can run at once
hope it helps


Attachment

Re: Maximum Performance

From
Manuel Trujillo
Date:
El jue, 24-01-2002 a las 09:27, Luis Amigo escribió:
> On my own experience I will tell you that if you're able to force postgres
> to keep all database in memory it will be very fast, so memory only depends
> on your
> database size.
> Each backend may run on a different processor, so the more processors u
> have the more backends u can run at once
> hope it helps

Yes, but... How can I know the exact size of my database? And, if I
compile the postgresql under four processors, don't work like (or in a)
SMP, distributing the charge into the four processors??

--
Manuel Trujillo         manueltrujillo@dorna.es
Technical Engineer      http://www.motograndprix.com
Dorna Sports S.L.       +34 93 4702864


Re: Maximum Performance

From
Radu-Adrian Popescu
Date:
On Thu, 2002-01-24 at 10:02, Jean Huveneers wrote:
> Hi,
>
> Within an couple of weeks we will start using PostgreSQL on Mandrake 8.1
> in real business (we have been testing, for over a half year).
>
Not that i have anything against Mandrake, but it is a desktop
distribution. I've heard several admins say their kernels are pretty
weak when it comes to server applications. So get a RedHat kernel
instead (2.2 or 2.4 series), because they are tested very thouroughly
and they include the best of Alan Cox' patches.
> In future we will have some tables with 100.000+ records an the system
> has te work very fast.

100.000+ records it's a pretty thin number. A PIII with 256 MB and X/Gnome
desktop running handles tables of 1.000.000 records on my workstation very
well.

> I know that speed of querries depend much on the amount of availible RAM
> to PostgreSQL, the server will only run the databases. What amount is
> RAM is usefull (I meen, does Postgres use the RAM, if availible, up to 2
> GB)?

Postgresql can use alot of ram. Read the pgsql docs on shared memory and kernel
parameters; you can reserve alot of shmem to postgresql, and yes, with a
large DB and/or alot of concurent connections it will even eat up your
swap. Also, check out Bruce M.'s articles on PostgreSQL optimization, as
they cover this and many more issues.

> I'm can chose for a Dual Athlon MP on a Tyan Thunder K7 motherbord, but
> does PostgreSQL use the 2 processors?

Like the post before said, using a SMP kernel (again, use RedHat's
kernels !) will send backends to both processors. Also you should note
that *any* program that forks or creates threads on a SMP machine will
be able to use (all) the processors.

--



Radu-Adrian Popescu
CSA, DBA, Programmer


Re: Maximum Performance

From
Radu-Adrian Popescu
Date:
On Thu, 2002-01-24 at 11:00, Manuel Trujillo wrote:
> El jue, 24-01-2002 a las 09:27, Luis Amigo escribió:
> > On my own experience I will tell you that if you're able to force postgres
> > to keep all database in memory it will be very fast, so memory only depends
> > on your
> > database size.
> > Each backend may run on a different processor, so the more processors u
> > have the more backends u can run at once
> > hope it helps
>
> Yes, but... How can I know the exact size of my database? And, if I
> compile the postgresql under four processors, don't work like (or in a)
> SMP, distributing the charge into the four processors??

First off, you could do a "du -h" in the data sub-directory in your
postgresql installation, to get an ideea about how large it is.

Even better, you could do a select count(*) from table1; and repeat this
in order to get the number of rows you have in each table in your
database. You can also get the size of a row in a table. Obviously, you
can multiply the n_rows with the row_width and get the approx. size of a
table. So there you go. But do add space for the indexes, the sequences
and the pg_ tables. This sould be more accurate, but for a gross (and
faster) estimation use "du -h ."

Hope that helps. Make sure the amount of shram you get thru your kernel
is enough, but not large enough to prevent everything else run :-)


--



Radu-Adrian Popescu
CSA, DBA, Programmer


Re: Maximum Performance

From
Luis Amigo
Date:
Manuel Trujillo wrote:

> El jue, 24-01-2002 a las 09:27, Luis Amigo escribió:
> > On my own experience I will tell you that if you're able to force postgres
> > to keep all database in memory it will be very fast, so memory only depends
> > on your
> > database size.
> > Each backend may run on a different processor, so the more processors u
> > have the more backends u can run at once
> > hope it helps
>
> Yes, but... How can I know the exact size of my database? And, if I
> compile the postgresql under four processors, don't work like (or in a)
> SMP, distributing the charge into the four processors??
>
> --
> Manuel Trujillo         manueltrujillo@dorna.es
> Technical Engineer      http://www.motograndprix.com
> Dorna Sports S.L.       +34 93 4702864

Si prefieres hablamos en español.


we're currently working on a 8-processor sgi server with irix by now each
backend run in one processor there is no load balance
the amount of memory u will need is as I know (table size+indexes size) they are
in your base directory, if u can afford it then u will be working on memory
but fsync and walfiles
hope it help


Attachment

Re: Maximum Performance

From
DHSC Webmaster
Date:
Jean,
We are using a quad pentium xeon machine with 6 GB ram RH 7.1 enterprise
kernel. Postgres uses everything that is available. Actually, its only
idling with the hardware we have and we have tables with over 2M rows.
Your disk subsystem is very important as well as it can easily become a
bottleneck that can cripple an otherwise capable system. We have placed
our largest and most active tables as well as indices on separate
physical drives for best performance.

Jean Huveneers wrote:
>
> Hi,
>
> Within an couple of weeks we will start using PostgreSQL on Mandrake 8.1
> in real business (we have been testing, for over a half year).
>
> In future we will have some tables with 100.000+ records an the system
> has te work very fast.
>
> I know that speed of querries depend much on the amount of availible RAM
> to PostgreSQL, the server will only run the databases. What amount is
> RAM is usefull (I meen, does Postgres use the RAM, if availible, up to 2
> GB)?
>
> I'm can chose for a Dual Athlon MP on a Tyan Thunder K7 motherbord, but
> does PostgreSQL use the 2 processors?
>
> Regards,
>
> Jean Huveneers
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

--
Bill MacArthur
Webmaster
DHS Club

Re: Maximum Performance Follow-up Question

From
"Peter T. Brown"
Date:
But how can Postgres be 'forced' to keep a table in memory? I've noticed
that on our Dual Pentium4, 1GB RAM machine, the size of the individual
postgres threads is very small. Top reports it as like 5K or 20K (I believe
that's what it means). Shouldn't this number be 100's of MB if postgres is
properly moving my tables to RAM? I do notice that the system cache is very
very large... Is there any way to specify that a certain table should have
priority for being transferred into RAM? Should I reduce the system cache
size so that postgres has more room to play with?

Thanks for any help!


here is a top dump:

89 processes: 88 sleeping, 1 running, 0 zombie, 0 stopped
CPU0 states: 10.0% user,  2.3% system,  0.0% nice, 87.0% idle
CPU1 states:  0.0% user,  0.5% system,  0.0% nice, 99.0% idle
Mem:  1035688K av, 1023896K used,   11792K free,  190424K shrd,  213836K
buff
Swap: 1036184K av,    2520K used, 1033664K free                  554284K
cached

  PID USER     PRI  NI  SIZE  RSS SHARE LC STAT %CPU %MEM   TIME COMMAND
 5861 postgres  17   0 12172  11M 11156  1 S     9.7  1.1   0:01 postmaster
25544 postgres   0   0  3280 3280  3124  0 S     0.0  0.3   1:11 postmaster
31682 postgres   0   0  5844 5844  4904  1 S     0.0  0.5   0:00 postmaster
31886 postgres   0   0  5600 5600  4712  0 S     0.0  0.5   0:00 postmaster
31903 postgres   0   0  5596 5596  4704  0 S     0.0  0.5   0:00 postmaster
31925 postgres   0   0  5600 5600  4712  1 S     0.0  0.5   0:00 postmaster
31929 postgres   0   0  5600 5600  4708  1 S     0.0  0.5   0:00 postmaster
 5441 postgres   0   0  5916 5916  4992  0 S     0.0  0.5   0:00 postmaster
 5551 postgres   0   0  5744 5744  4868  1 S     0.0  0.5   0:00 postmaster
 5552 postgres   0   0  5888 5888  4988  1 S     0.0  0.5   0:00 postmaster
 5553 postgres   0   0  5528 5528  4652  0 S     0.0  0.5   0:00 postmaster
 5554 postgres   0   0  5764 5764  4888  0 S     0.0  0.5   0:00 postmaster
 5555 postgres   0   0  5768 5768  4892  0 S     0.0  0.5   0:00 postmaster
 5556 postgres   0   0  5724 5724  4848  1 S     0.0  0.5   0:00 postmaster
 5563 postgres   0   0  5520 5520  4644  1 S     0.0  0.5   0:00 postmaster
 5564 postgres   0   0  5684 5684  4784  1 S     0.0  0.5   0:00 postmaster
 5565 postgres   0   0  5516 5516  4640  0 S     0.0  0.5   0:00 postmaster
 5570 postgres   0   0  5548 5548  4668  1 S     0.0  0.5   0:00 postmaster
 5572 postgres   0   0  5540 5540  4660  0 S     0.0  0.5   0:00 postmaster
 5573 postgres   0   0  5516 5516  4636  1 S     0.0  0.5   0:00 postmaster
 5709 postgres   0   0  5524 5524  4648  1 S     0.0  0.5   0:00 postmaster
 5710 postgres   0   0  5752 5752  4876  1 S     0.0  0.5   0:00 postmaster
 5711 postgres   0   0  5508 5508  4628  1 S     0.0  0.5   0:00 postmaster
 5712 postgres   0   0  5756 5756  4876  0 S     0.0  0.5   0:00 postmaster
 5713 postgres   0   0  5516 5516  4628  1 S     0.0  0.5   0:00 postmaster
 5715 postgres   0   0  5504 5504  4600  1 S     0.0  0.5   0:00 postmaster
 5781 postgres   0   0 13660  13M 12560  0 S     0.0  1.3   0:04 postmaster
 5803 postgres   0   0  5516 5516  4608  0 S     0.0  0.5   0:00 postmaster
 5826 postgres   0   0  6920 6920  5868  1 S     0.0  0.6   0:00 postmaster
 5833 postgres   0   0  7360 7360  6244  1 S     0.0  0.7   0:00 postmaster
 5835 postgres   0   0 12424  12M 11288  0 S     0.0  1.1   0:01 postmaster
 5860 postgres   0   0  7596 7596  6460  1 S     0.0  0.7   0:00 postmaster

-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Luis Amigo
Sent: Thursday, January 24, 2002 12:27 AM
To: Jean Huveneers
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Maximum Performance


Jean Huveneers wrote:

> Hi,
>
> Within an couple of weeks we will start using PostgreSQL on Mandrake 8.1
> in real business (we have been testing, for over a half year).
>
> In future we will have some tables with 100.000+ records an the system
> has te work very fast.
>
> I know that speed of querries depend much on the amount of availible RAM
> to PostgreSQL, the server will only run the databases. What amount is
> RAM is usefull (I meen, does Postgres use the RAM, if availible, up to 2
> GB)?
>
> I'm can chose for a Dual Athlon MP on a Tyan Thunder K7 motherbord, but
> does PostgreSQL use the 2 processors?
>
> Regards,
>
> Jean Huveneers
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

On my own experience I will tell you that if you're able to force postgres
to keep all database in memory it will be very fast, so memory only depends
on your
database size.
Each backend may run on a different processor, so the more processors u
have the more backends u can run at once
hope it helps



Re: Maximum Performance Follow-up Question

From
Luis Amigo
Date:
"Peter T. Brown" wrote:

> But how can Postgres be 'forced' to keep a table in memory? I've noticed
> that on our Dual Pentium4, 1GB RAM machine, the size of the individual
> postgres threads is very small. Top reports it as like 5K or 20K (I believe
> that's what it means). Shouldn't this number be 100's of MB if postgres is
> properly moving my tables to RAM? I do notice that the system cache is very
> very large... Is there any way to specify that a certain table should have
> priority for being transferred into RAM? Should I reduce the system cache
> size so that postgres has more room to play with?
>
> Thanks for any help!
>
> here is a top dump:
>

I don't know about linuxes, so I can't tell u about cache, what I can tell u
is if u asign a high number to shared_buffers(I would use 65536 for 1Gb ram
machine)
and a high sort_mem(16384kb on this machine) in postgres.conf u can do that
all the data a query needs could be on memory.
here is sizes for one of our backends(i've no time so i don't leave him to
charge all in memory(659M demanded 15M used) with a 100Mb base(a lot more with
indexes)

  PID       PGRP USERNAME PRI  SIZE   RES STATE    TIME WCPU% CPU% COMMAND
     97441      94523 lamigo    20  659M   15M sleep    0:00  8.8  8.83
postgre

this is a memory snap shot of a typical load(7 backends with AD-HOC queries)
 IRIX64 congrio 6.5 IP25        load averages: 6.04 5.03
3.73
20:19:56
 97 processes:  90 sleeping, 7 running
 8 CPUs: 25.3% idle, 72.1% usr,  2.5% ker,  0.0% wait,  0.0% xbrk,  0.1% intr
 Memory: 1024M max, 940M avail, 221M free, 2304M swap, 2294M free swap

feel free of making questions
hope it helps
regards

Attachment

Re: Maximum Performance Follow-up Question

From
Tom Lane
Date:
"Peter T. Brown" <peter@memeticsystems.com> writes:
> But how can Postgres be 'forced' to keep a table in memory? I've noticed
> that on our Dual Pentium4, 1GB RAM machine, the size of the individual
> postgres threads is very small. Top reports it as like 5K or 20K (I believe
> that's what it means). Shouldn't this number be 100's of MB if postgres is
> properly moving my tables to RAM? I do notice that the system cache is very
> very large...

System cache == RAM.  This is the behavior you want.

            regards, tom lane

Re: Maximum Performance Follow-up Question

From
"Peter T. Brown"
Date:
That helps a great deal! I am learning about the 'run-fast' option in
postgres: Increasing the shared_buffers is critical. Here is a copy of my
postgresql.conf file. I'll make the increases you suggested, but is there
anything else you can recomend?

Thanks again,

Peter



postgresql.conf:

#       Connection Parameters
#
tcpip_socket = true
#ssl = false

max_connections = 128 # 1-1024

#port = 5432
#hostname_lookup = false
#show_source_port = false

#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777

#virtual_host = ''

#krb_server_keyfile = ''


#
#       Performance
#
sort_mem = 32168
shared_buffers = 15200 # min 16
fsync = true

#       Optimizer Parameters
#
#enable_seqscan = true
#enable_indexscan = true
#enable_tidscan = true
#enable_sort = true
#enable_nestloop = true
#enable_mergejoin = true
#enable_hashjoin = true

#ksqo = false
#geqo = true

#effective_cache_size = 1000  # default in 8k pages
#random_page_cost = 4
#cpu_tuple_cost = 0.01
#cpu_index_tuple_cost = 0.001
#cpu_operator_cost = 0.0025
#geqo_selection_bias = 2.0 # range 1.5-2.0


#
#       GEQO Optimizer Parameters
#
#geqo_threshold = 11
#geqo_pool_size = 0  #default based in tables, range 128-1024
#geqo_effort = 1
#geqo_generations = 0
#geqo_random_seed = -1 # auto-compute seed


#
#       Inheritance
#
#sql_inheritance = true


#
#       Deadlock
#
#deadlock_timeout = 1000

#       Expression Depth Limitation
#
#max_expr_depth = 10000 # min 10


#
#       Write-ahead log (WAL)
#
#wal_buffers = 8 # min 4
#wal_files = 0 # range 0-64
#wal_debug = 0 # range 0-16
#commit_delay = 5 # range 0-1000
#checkpoint_timeout = 300 # range 30-1800


#
#       Debug display
#
#silent_mode = false

log_connections = true
log_timestamp = true
log_pid = true

#debug_level = 0 # range 0-16

#debug_print_query = false
#debug_print_parse = false
#debug_print_rewritten = false
#debug_print_plan = false
#debug_pretty_print = false

#ifdef USE_ASSERT_CHECKING
#debug_assertions = true
#endif


#
#       Syslog
#
#ifdef ENABLE_SYSLOG
#syslog = 0 # range 0-2
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'
#endif

#       Statistics
#
#show_parser_stats = false
#show_planner_stats = false
#show_executor_stats = false
#show_query_stats = false
#ifdef BTREE_BUILD_STATS
#show_btree_build_stats = false
#endif


#
#       Lock Tracing
#
#trace_notify = false
#ifdef LOCK_DEBUG
#trace_locks = false
#trace_userlocks = false
#trace_spinlocks = false
#debug_deadlocks = false
#trace_lock_oidmin = 16384
#trace_lock_table = 0
#endif


-----Original Message-----
From: lamigo@atc.unican.es [mailto:lamigo@atc.unican.es]
Sent: Thursday, January 24, 2002 10:24 AM
To: Peter T. Brown
Cc: 'Jean Huveneers'; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Maximum Performance Follow-up Question


"Peter T. Brown" wrote:

> But how can Postgres be 'forced' to keep a table in memory? I've noticed
> that on our Dual Pentium4, 1GB RAM machine, the size of the individual
> postgres threads is very small. Top reports it as like 5K or 20K (I
believe
> that's what it means). Shouldn't this number be 100's of MB if postgres is
> properly moving my tables to RAM? I do notice that the system cache is
very
> very large... Is there any way to specify that a certain table should have
> priority for being transferred into RAM? Should I reduce the system cache
> size so that postgres has more room to play with?
>
> Thanks for any help!
>
> here is a top dump:
>

I don't know about linuxes, so I can't tell u about cache, what I can tell u
is if u asign a high number to shared_buffers(I would use 65536 for 1Gb ram
machine)
and a high sort_mem(16384kb on this machine) in postgres.conf u can do that
all the data a query needs could be on memory.
here is sizes for one of our backends(i've no time so i don't leave him to
charge all in memory(659M demanded 15M used) with a 100Mb base(a lot more
with
indexes)

  PID       PGRP USERNAME PRI  SIZE   RES STATE    TIME WCPU% CPU% COMMAND
     97441      94523 lamigo    20  659M   15M sleep    0:00  8.8  8.83
postgre

this is a memory snap shot of a typical load(7 backends with AD-HOC queries)
 IRIX64 congrio 6.5 IP25        load averages: 6.04 5.03
3.73
20:19:56
 97 processes:  90 sleeping, 7 running
 8 CPUs: 25.3% idle, 72.1% usr,  2.5% ker,  0.0% wait,  0.0% xbrk,  0.1%
intr
 Memory: 1024M max, 940M avail, 221M free, 2304M swap, 2294M free swap

feel free of making questions
hope it helps
regards


Re: Maximum Performance Follow-up Question

From
Radu-Adrian Popescu
Date:
On Thu, 2002-01-24 at 20:47, Peter T. Brown wrote:
> That helps a great deal! I am learning about the 'run-fast' option in
> postgres: Increasing the shared_buffers is critical. Here is a copy of my
> postgresql.conf file. I'll make the increases you suggested, but is there
> anything else you can recomend?
>
> Thanks again,
>
> Peter
>
>
>
> postgresql.conf:
>
....
I belive you should set
    fsync=false
in case you mainly select and do inserts rather rare. The ideea is to
keep/operate the db in/from memory, while fsync=true will get your
system to sync every write to hdd. Or at least that's what i understood
from the docs.

--



Radu-Adrian Popescu
CSA, DBA, Programmer


Re: Maximum Performance Follow-up Question

From
Tom Lane
Date:
Radu-Adrian Popescu <radu.popescu@aldratech.com> writes:
> I belive you should set
>     fsync=false
> in case you mainly select and do inserts rather rare.

No, that's a really horrid reason to turn off fsync.  A read-only
transaction never syncs and thus has no fsync penalty.  If update
performance isn't a serious problem for you, you may as well keep
fsync on and not have to worry about data loss in the case of a
system crash.

            regards, tom lane

Re: Maximum Performance Follow-up Question

From
"Peter T. Brown"
Date:
Actually, a great many of the queries we run are Very dependant on
inserts -- we do inserts into a big 'pointers' table and use that as a basis
for other queries. Its also the case that for our web tracking application
nearly every select is paried with an insert (lookup a visitor, add a row
recording their hit to the website). So I think that gaining efficiency on
inserts would really help...

Is there any BIG risk in turning fsync off? I mean, if I miss the last 30
minutes of tracking data from our website because of a system crash, thats
no big deal to me. If the entire database gets corrupted and must be
scrubbed, that's a big deal.

And isn't there some way to use fsync but just use it less frequently, so
that postgres writes a bunch of changes to disk when it less busy or
something?

Thanks

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, January 25, 2002 7:24 AM
To: Radu-Adrian Popescu
Cc: Peter T. Brown; Postgres Admin List
Subject: Re: [ADMIN] Maximum Performance Follow-up Question


Radu-Adrian Popescu <radu.popescu@aldratech.com> writes:
> I belive you should set
>     fsync=false
> in case you mainly select and do inserts rather rare.

No, that's a really horrid reason to turn off fsync.  A read-only
transaction never syncs and thus has no fsync penalty.  If update
performance isn't a serious problem for you, you may as well keep
fsync on and not have to worry about data loss in the case of a
system crash.

            regards, tom lane