Re: [PERFORM] Hardware opinions wanted
Dan Harris wrote: I am torn right now between these two systems to replace my aging DB server: 4 x 2.2 GHz Opteron 8GB RAM Ultra320 15kRPM RAID5 with 128MB cache and 2-way 1.2GHz POWER4+ IBM pSeries 615 8GB RAM Ultra320 15kRPM RAID5 with 64MB cache I don't know anything about the pSeries, but have a look in the archives, there was recently a rather long thread about Xeon vs. Opteron. The Opteron was the clear winner. Personally I think that you can't be wrong with the 4-way Opteron. It scales very well and if you don't need the fault tolerance of the pSeries platform, then you should be able to save one or two bucks with opteron way. Btw: If you want to save a few more bucks, then drop the 15k and take 10k drives. They are of almost same speed. Regards, Bjoern ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] [GENERAL] performance very slow
OK. Thank fou your help. In this moment the size of database its 2GB. And the machine it´s only to postgresql. Gracias Mario Soto wrote: Hi. i hava a postresql 7.4.2 in a production server. tha machine is a Pentium IV 2,6 GHZ AND 1 GB IN RAM with lINUX RH 9.0. Mario, Start with reading this: http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html Without knowing anything about the size of your database, your usage patterns, or your disk subsystem (the most important part of a database server, imho) I would suggest you first increase the number of shared_buffers allocated to Postgres. Most recommend keeping this number below 1, but I've found I get the best performance with about 24000 shared_buffers with a ~5GB database on a machine with 4GB of ram, dedicated to Postgres. You'll have to experiment to see what works best for you. Also, make sure you VACUUM and ANALYZE on a regular basis. Again, the frequency of this really depends on your data and usage patterns. More frequent write operations require more frequent vacuuming. Good luck. Best Regards, Bill Montgomery The postresql.conf say: #--- # RESOURCE USAGE (except WAL) #--- # - Memory - shared_buffers = 1000 # min 16, at least max_connections*2, 8KB each sort_mem = 1024 # min 64, size in KB vacuum_mem = 8192 # min 1024, size in KB # - Free Space Map - max_fsm_pages = 2 # min max_fsm_relations*16, 6 bytes each max_fsm_relations = 1000# min 100, ~50 bytes each # - Kernel Resource Usage - max_files_per_process = 1000# min 25 #preload_libraries = '' #--- # WRITE AHEAD LOG #--- # - Settings - fsync = true# turns forced synchronization on or off wal_sync_method = fsync # the default varies across platforms: # fsync, fdatasync, open_sync, or open_datasync wal_buffers = 8 # min 4, 8KB each # - Checkpoints - checkpoint_segments = 3 # in logfile segments, min 1, 16MB each checkpoint_timeout = 300# range 30-3600, in seconds checkpoint_warning = 30 # 0 is off, in seconds commit_delay = 0# range 0-10, in microseconds commit_siblings = 5 # range 1-1000 #--- # QUERY TUNING #--- # - Planner Method Enabling - enable_hashagg = true enable_hashjoin = true enable_indexscan = true enable_mergejoin = true enable_nestloop = true enable_seqscan = true enable_sort = true enable_tidscan = true # - Planner Cost Constants - effective_cache_size = 1000 # typically 8KB each random_page_cost = 4# units are one sequential page fetch cost cpu_tuple_cost = 0.01 # (same) cpu_index_tuple_cost = 0.001# (same) cpu_operator_cost = 0.0025 # (same) # - Genetic Query Optimizer - geqo = true geqo_threshold = 11 geqo_effort = 1 geqo_generations = 0 geqo_pool_size = 0 # default based on tables in statement, # range 128-1024 geqo_selection_bias = 2.0 # range 1.5-2.0 # - Other Planner Options - default_statistics_target = 100 # range 1-1000 from_collapse_limit = 30 join_collapse_limit = 30# 1 disables collapsing of explicit JOINs #--- # ERROR REPORTING AND LOGGING #--- # - Syslog - #syslog = 0 # range 0-2; 0=stdout; 1=both; 2=syslog #syslog_facility = 'LOCAL0' #syslog_ident = 'postgres' # - When to Log - #client_min_messages = notice # Values, in order of decreasing detail: # debug5, debug4, debug3, debug2, debug1, # log, info, notice, warning, error #log_min_messages = notice # Values, in order of decreasing detail: # debug5, debug4, debug3, debug2, debug1, # info, notice, warning, error, log, fatal, # panic #log_error_verbosity = default # terse, default, or verbose messages #log_min_error_statement = panic # Values in order of increasing severity: # debug5, debug4, debug3, debug2, debug1, # info, notice, warning, error, panic(off) #log_min_duration_statement = -1 # Log all statements whose # execution time exceeds the value, in # milliseconds. Zero prints all queries. # Minus-one disables. #silent_mode = false # DO NOT USE without Syslog! # - What to Log - debug_print_parse = true
Re: [PERFORM] filesystem option tuning
Hi! On Mon, May 17, 2004 at 06:04:54PM +0100, Richard Huxton wrote: [EMAIL PROTECTED] wrote: [...] In no official capacity whatsoever, welcome aboard. Thanks ;-) There is just one point where I found the documentation lacking any description and practical hints (as opposed to all other topics), namely that of how to tune a setup for maximum performance regarding the layout of partitions on hard-disks and their mount options. I'm not a Sun user, so I can't give any OS-specific notes, but in general: - Don't bypass the filesystem, but feel free to tinker with mount options if you think it will help Right, raw partitions are too low-level for me these days anyhow... I assume that all postgres partitions can be mounted with noatime? - If you can put WAL on separate disk(s), all the better. Does that mean only the xlog, or also the clog? As far as I understand, the clog contains some meta-information on the xlog, so presumably it is flushed to disc synchronously together with the xlog? That would mean that they each need a separate disk to prevent one disk having to seek too often...? - Battery-backed write-cache for your SCSI controller can be a big performance win I probably won't be able to get such a setup for this project; that's why I am bothering about which disk will be seeking how often. - Tablespaces _should_ be available in the next release of PG, we'll know for sure soon. That might make life simpler for you if you do want to spread your database around by hand, Ok, I think tablespaces are not the important thing - at least for this project of ours. What I should add is that reliability is a premium for us, we do not want to sacrifice integrity for speed, and that we are tuning for a high commit rate of small, simple transactions... Make sure the WAL is on fast disks I'd suggest. At a guess that'll be your bottleneck. For more info, your best bet is to check the archives on the plpgsql-performance list, and then post there. People will probably want to know more about your database size/number of concurrent transactions/disk systems etc. Here goes ... we are talking about a database cluster with two tables where things are happening, one is a kind of log that is simply appended to and will expect to reach a size of several million entries in the time window that is kept, the other is a persistent backing of application data that will mostly see read-modify-writes of single records. Two writers to the history, one writer to the data table. The volume of data is not very high and RAM is enough... If any more information is required feel free to ask - I would really appreciate getting this disk layout sorted out. Thanks, Colin ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Not using Primary Key in query
Josh Sacks [EMAIL PROTECTED] writes: I can't understand what's going on in this simple query: If you are using anything older than PG 7.4, you should not expect good performance from WHERE ... IN (sub-SELECT) queries. There's essentially no optimization happening there. regards, tom lane ---(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
