Re: [PERFORM] Opteron/FreeBSD/PostgreSQL performance poor
Hi Merlin, Thanks for the input. Please see below ... Merlin Moncure wrote: On 7/5/06, andy rost [EMAIL PROTECTED] wrote: fsync = on # turns forced synchronization have you tried turning this off and measuring performance? No, not yet. We're trying a couple of outher avenues before manipulating this parameter. stats_command_string = on I would turn this off unless you absoltely require it. It is expensive for what it does. We've turned this off a) All 4 CPUs are nearly always 0% idle; b) The system load level is nearly always in excess of 20; I am guessing your system is spending all it's time syncing. If so, it's solvable (again, just run fsync=off for a bit and compare). We've reduced the load significantly primarily by moving pg_xlog to its own drive and by increasing the effective cache size. While we still see high load levels, they don't last very long. We're trying improve performance from several angles but are taking it one step at a time. Eventually we'll experiment with fsynch c) the output from vmstat -w 10 looks like: procs memory pagedisks faults cpu r b w avmfre flt re pi po fr sr aa0 aa1 in sy cs us sy id 21 0 3 1242976 327936 2766 0 0 0 2264 0 2 2 17397 140332 104846 18 82 1 is that 100k context switches over 10 seconds or one second? that might be something to check out. pg 8.1 is regarded as the solution to any cs problem, though. According to man top, that's 100K per second. I'm interested in your recommendation but am not sure what pg 8.1 references NOTE - small user demands and high system demands d) Running top indicates a significant number or sblock states and occasional smwai states; e) ps auxww | grep postgres doesn't show anything abnormal; f) ESQL applications are very slow. We VACUUM ANALYZE user databases every four hours. We VACUUM template1 every 4 hours. We make a copy of the current WAL every minute. We create a PIT recovery archive daily daily. None of these, individually seem to place much strain on the server. your server should be able to handle this easily. Hopefully I've supplied enough information to start diagnosing the problem. Any ideas, thoughts, suggestions are greatly appreciated ... can you please approximate roughly how many transactions per second your server is handling while you are getting the 20 load condition (and, if possible, broken down into read and write transactions)? Do you have any suggestions on how I might obtain these metrics? merlin Thanks again Merlin ... Andy -- Andrew Rost National Operational Hydrologic Remote Sensing Center (NOHRSC) National Weather Service, NOAA 1735 Lake Dr. West, Chanhassen, MN 55317-8582 Voice: (952)361-6610 x 234 Fax: (952)361-6634 [EMAIL PROTECTED] http://www.nohrsc.noaa.gov ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[PERFORM] Opteron/FreeBSD/PostgreSQL performance poor
We're in the process of porting from Informix 9.4 to PostgreSQL 8.1.3. Our PostgreSQL server is an AMD Opteron Dual Core 275 with two 2.2 Ghz 64-bit processors. There are two internal drives and an external enclosure containing 14 drives (configured as 7 pairs of mirrored drives - four pairs for table spaces, one pair for dbcluster, two pairs for point in time recovery). The operating system is FreeBSD 6.0-RELEASE #10 The output from ulimit -a is: ulimit -a core file size (blocks, -c) unlimited data seg size (kbytes, -d) 33554432 file size (blocks, -f) unlimited max locked memory (kbytes, -l) unlimited max memory size (kbytes, -m) unlimited open files (-n) 11095 pipe size(512 bytes, -p) 1 stack size (kbytes, -s) 524288 cpu time (seconds, -t) unlimited max user processes (-u) 5547 virtual memory (kbytes, -v) unlimited Shared memory kernel parameters are set to: shmmax 1073741000 shmmin 1 shmall 262144 shmseg 128 shmmni 192 semmni 256 semmns 512 semmsl 256 semmap 256 semvmx 32767 shm_use_phys 1 The postgresql.conf file contains: #--- # FILE LOCATIONS #--- # The default values of these variables are driven from the -D command line # switch or PGDATA environment variable, represented here as ConfigDir. #data_directory = 'ConfigDir' # use data in another directory #hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file #ident_file = 'ConfigDir/pg_ident.conf' # IDENT configuration file # If external_pid_file is not explicitly set, no extra pid file is written. #external_pid_file = '(none)' # write an extra pid file #--- # CONNECTIONS AND AUTHENTICATION #--- # - Connection Settings - listen_addresses = '*' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost', '*' = all port = 5432 max_connections = 102 # note: increasing max_connections costs ~400 bytes of shared memory per # connection slot, plus lock space (see max_locks_per_transaction). You # might also need to raise shared_buffers to support more connections. superuser_reserved_connections = 2 unix_socket_directory = '' unix_socket_group = '' unix_socket_permissions = 0777 # octal bonjour_name = '' # defaults to the computer name # - Security Authentication - authentication_timeout = 60 # 1-600, in seconds ssl = off password_encryption = on db_user_namespace = off # Kerberos krb_server_keyfile = '' krb_srvname = 'postgres' krb_server_hostname = ''# empty string matches any keytab entry krb_caseins_users = off # - TCP Keepalives - # see 'man 7 tcp' for details tcp_keepalives_idle = 0 # TCP_KEEPIDLE, in seconds; # 0 selects the system default tcp_keepalives_interval = 0 # TCP_KEEPINTVL, in seconds; # 0 selects the system default tcp_keepalives_count = 0# TCP_KEEPCNT; # 0 selects the system default #--- # RESOURCE USAGE (except WAL) #--- # - Memory - shared_buffers = 125000 # min 16 or max_connections*2, 8KB each temp_buffers = 1000 # min 100, 8KB each max_prepared_transactions = 0 # can be 0 or more # note: increasing max_prepared_transactions costs ~600 bytes of shared memory # per transaction slot, plus lock space (see max_locks_per_transaction). work_mem = 1# min 64, size in KB maintenance_work_mem = 5# min 1024, size in KB max_stack_depth = 50# in 100, size in KB # ulimit -a or ulimit -s # - Free Space Map - max_fsm_pages = 60 # min max_fsm_relations*16, 6 bytes each max_fsm_relations = 1000# min 100, ~70 bytes each # - Kernel Resource Usage - max_files_per_process = 1000# min 25 preload_libraries = '' # - Cost-Based Vacuum Delay - vacuum_cost_delay = 0 # 0-1000 milliseconds vacuum_cost_page_hit = 1# 0-1 credits vacuum_cost_page_miss = 10 # 0-1 credits vacuum_cost_page_dirty = 20 # 0-1 credits vacuum_cost_limit = 200 #
Re: [PERFORM] Opteron/FreeBSD/PostgreSQL performance poor
Hi Stephen, Thanks for your input. My follow ups are interleaved below ... Stephen Frost wrote: * andy rost ([EMAIL PROTECTED]) wrote: We're in the process of porting from Informix 9.4 to PostgreSQL 8.1.3. Our PostgreSQL server is an AMD Opteron Dual Core 275 with two 2.2 Ghz 64-bit processors. There are two internal drives and an external enclosure containing 14 drives (configured as 7 pairs of mirrored drives - four pairs for table spaces, one pair for dbcluster, two pairs for point in time recovery). The operating system is FreeBSD 6.0-RELEASE #10 Not sure it matters, but is the mirroring done with a hardware controller or in software? I'll have to check on this when our system administrator returns tomorrow. I performed a quick test while the server was under load by moving a couple of Gigs of data while running iostat.I was getting disk I/O rates of about 125 KB per transaction, 250 transactions per second, and 35 Mg per second on all drives. shared_buffers = 125000 # min 16 or max_connections*2, 8KB each temp_buffers = 1000 # min 100, 8KB each max_prepared_transactions = 0 # can be 0 or more # note: increasing max_prepared_transactions costs ~600 bytes of shared memory # per transaction slot, plus lock space (see max_locks_per_transaction). work_mem = 1# min 64, size in KB maintenance_work_mem = 5# min 1024, size in KB max_stack_depth = 50# in 100, size in KB # ulimit -a or ulimit -s These seem kind of.. backwards... Just an example of one system I've got shows: shared_buffers = 1 work_mem = 32768 maintenance_work_mem = 65535 Defaults for the rest. This is more of a data-warehouse than an OLTP, so I'm sure these aren't perfect for you, but you might try playing with them some. Originally shared_buffers was set to 32768. I set it to its current value out of desperations (newby response). # - Free Space Map - max_fsm_pages = 60 # min max_fsm_relations*16, 6 bytes each This seems somewhat hgih from the default of 20,000, but for a very frequently changing database it may make sense. This value is based on the output from VACUUM ANALYZE archive_command = 'archive_wal -email -txtmsg %p %f'# command to use Are WALs being archived very frequently? Any idea if this takes much time? I wouldn't really think it'd be an issue, but might be useful to know. Yes, about 100 times per hour. No, I don't think it takes much time effective_cache_size = 27462# typically 8KB each This seems like it might be a little low... How much memory do you have in the system? Then again, with your shared_mem set so high, perhaps it's not that bad, but it might make sense to swap those two settings, or at least that'd be a more common PG setup. Oops, forgot to mention that we have 6 Gigs of memory. This value was set based on sysctl -n vfs.hibufspace / 8192 random_page_cost = 2# units are one sequential page That's quite a bit lower than the default of 4... May make sense for you but it's certainly something to look at. This value set per web page entitiled Annotated POSTGRESQL.CONF Guide for PostgreSQL We're running an OLTP database with a small number of connections (50) performing mostly reads and inserts on modest sized tables (largest is 2,000,000 records). The symptoms are: a) All 4 CPUs are nearly always 0% idle; b) The system load level is nearly always in excess of 20; At a guess I'd say that the system is doing lots of sequential scans rather than using indexes, and that's why the processes are ending up in a disk-wait state, which makes the load go up. Have you looked at the plans which are being generated for the most common queries to see what they're doing? We thought of that too. However, executing: select * from pg_stat_user_tables suggests that we are using indexes where needed. We confirmed this by checking and running manually queries reported by select * from pg_stat_activity while the server is suffering I'd also wonder if the shared_mem setting isn't set *too* high and causing problems with the IPC or something... Not something I've heard of (generally, going up with shared_mem doesn't degrade performance, just doesn't improve it) but might be possible. Possible I suppose but we had the same trouble while the server was configured with 32768 buffers We VACUUM ANALYZE user databases every four hours. We VACUUM template1 every 4 hours. We make a copy of the current WAL every minute. We create a PIT recovery archive daily daily. None of these, individually seem to place much strain on the server. This doesn't sound too bad at all. How long do the vacuum's run for? If it's 3 hours, then that might start to be an issue with disk I/O contention... VACUUM ANALYZE lasts about
