Thread: Maximum Performance
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
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
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
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
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
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
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
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
"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
"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
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
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
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
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