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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
