On 07/06/10 17:28, Stephen Thompson wrote:
>
> It doesn't sound like I'm doing anything egregiously wrong.
>
> I am still surprised at how slow postgres is compared to mysql on the
> same hardware after all I've read and heard about postgres superiority.
>    

One the scale you're using it should be, however I have doubts about the 
amount of physical memory you have installed for the number of records 
being handled - if at all possible you should add more.


Here are my Postgres settings:

shared_buffers = 24GB                   # (change requires restart)
temp_buffers = 1GB                         # (change requires restart)
work_mem = 64MB
maintenance_work_mem = 256MB
max_stack_depth = 128MB
full_page_writes = off # ok with battery backed RAID
wal_buffers = 16MB     # (change requires restart)
commit_delay = 1000
checkpoint_segments = 256 # 4Gb far more suitable for bulk loads used by 
Bacula
effective_cache_size = 32GB    # 3/4 system memory. Could be tuned upwards


Just as importantly (on linux) are various sysctl tweaks in addition to 
the limits.conf items I've already posted:


# Controls the maximum size of a message, in bytes
kernel.msgmnb = 65536

# Controls the default maxmimum size of a mesage queue
kernel.msgmax = 65536

# Controls the maximum shared segment size, in bytes
kernel.shmmax = 68719476736

# Controls the maximum number of shared memory segments, in pages
kernel.shmall = 4294967296

# from www.performancewiki/com/linux-tuning.html
kernel.msgmni = 1024
# Same as above. CRITICAL for large numbers of GFS mounts.
kernel.sem = 500 512000 64 2048

net.core.rmem_default = 25165824
net.core.rmem_max = 40000000
net.core.wmem_default = 25165824
net.core.wmem_max = 40000000

net.ipv4.tcp_mem = 1966080 2621440   39321600
net.ipv4.tcp_rmem =  40960  873800   41943040
net.ipv4.tcp_wmem =  40960  163840   41943040

# increase allowable connects for nfs4
sunrpc.tcp_slot_table_entries = 128
sunrpc.udp_slot_table_entries = 128

# don't cache ssthresh from previous connection
net.ipv4.tcp_no_metrics_save = 1
net.ipv4.tcp_moderate_rcvbuf = 1
# recommended to increase this for 1000 BT or higher
net.core.netdev_max_backlog = 2500
# net.core.netdev_max_backlog = 30000

# Bigger backlog of SYN packets
net.ipv4.tcp_max_syn_backlog = 10240
# Increase maximum backlog for accepting new connections
net.core.somaxconn = 1024
# More efficient handling of lots of old connections
# in the TIME_WAIT state
net.ipv4.tcp_max_tw_buckets = 1800000







------------------------------------------------------------------------------
ThinkGeek and WIRED's GeekDad team up for the Ultimate 
GeekDad Father's Day Giveaway. ONE MASSIVE PRIZE to the 
lucky parental unit.  See the prize list and enter to win: 
http://p.sf.net/sfu/thinkgeek-promo
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users