[PERFORM] tuning for TPC-C benchmark
Hello all, I'm doing tests on various Database and in particular I'm running a comparison between Oracle 10g and Postgres 8.1 on a dedicated server with 2 processors Dual-Core AMD Opteron 2218 2.6 GHz, 4GB of memory and Debian GNU / Linux version 2.6.18-5. Performance is very similar up to 30 users, but from 40 onwards with Postgres fall quickly. That is not what happens with Oracle that comes to 600 users. Can you help me with the tuning ? Thanks a lot My postgresql.conf configuration is: #--- # 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 = '/etc/postgresql/8.1/main/pg_hba.conf' # host-based authentication file ident_file = '/etc/postgresql/8.1/main/pg_ident.conf' # IDENT configuration file # If external_pid_file is not explicitly set, no extra pid file is written. external_pid_file = '/var/run/postgresql/8.1-main.pid' # write an extra pid file #--- # CONNECTIONS AND AUTHENTICATION #--- # - Connection Settings - #listen_addresses = 'localhost' # what IP address(es) to listen on; # comma- separated list of addresses; # defaults to 'localhost', '*' = all listen_addresses = '*' port = 5432 max_connections = 220 # 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 = '/var/run/postgresql' #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 = true #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 = 49152# min 16 or max_connections*2, 8KB each, 384MB temp_buffers = 1000 # min 100, 8KB each max_prepared_transactions = 350 # 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 = 1024 # min 64, size in KB maintenance_work_mem = 524288 # min 1024, size in KB, -512 MB- max_stack_depth = 6144 # min 100, size in KB # - Free Space Map - max_fsm_pages = 58000 # min max_fsm_relations*16, 6 bytes each max_fsm_relations = 3000# 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# 0-1 credits # - Background writer - #bgwriter_delay = 5000 # 10-1 milliseconds between rounds bgwriter_lru_percent = 0# 0-100% of LRU buffers scanned/round bgwriter_lru_maxpages = 0 # 0-1000 buffers max written/round bgwriter_all_percent = 0# 0-100% of all buffers scanned/round bgwriter_all_maxpages = 0 # 0-1000 buffers max written/round #--- # WRITE AHEAD LOG
Re: [PERFORM] tuning for TPC-C benchmark
[EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hello all, I'm doing tests on various Database and in particular I'm running a comparison between Oracle 10g and Postgres 8.1 on a dedicated server with 2 processors Dual-Core AMD Opteron 2218 2.6 GHz, 4GB of memory and Debian GNU / Linux version 2.6.18-5. Performance is very similar up to 30 users, but from 40 onwards with Postgres fall quickly. That is not what happens with Oracle that comes to 600 users. Can you help me with the tuning ? If you're doing perf comparisons, you should start out with the latest PostgreSQL: 8.2.5 Also, beware that you may violate license agreements if you publish benchmarks of Oracle ... and posting partial results to a mailing list could potentially be considered publishing benchmarks to Oracle's lawyers. I've added a few more comments inline, but overall it looks like you've done a good job tuning. In order to tweak it any further, we're probably going to need more details, such as iostat output during the run, details of the test you're running, etc. Thanks a lot My postgresql.conf configuration is: #--- # 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 = '/etc/postgresql/8.1/main/pg_hba.conf' # host-based authentication file ident_file = '/etc/postgresql/8.1/main/pg_ident.conf' # IDENT configuration file # If external_pid_file is not explicitly set, no extra pid file is written. external_pid_file = '/var/run/postgresql/8.1-main.pid' # write an extra pid file #--- # CONNECTIONS AND AUTHENTICATION #--- # - Connection Settings - #listen_addresses = 'localhost' # what IP address(es) to listen on; # comma- separated list of addresses; # defaults to 'localhost', '*' = all listen_addresses = '*' port = 5432 max_connections = 220 # 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 = '/var/run/postgresql' #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 = true #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 = 49152# min 16 or max_connections*2, 8KB each, 384MB With 4G of ram, you might want to try this closer to 1G and see if it helps. You may want to install the pg_buffercache module to monitor shared_buffer usage. I doubt you want to use it during actual timing of the test, but it should help you get a feel for what the best setting is for shared_buffers. temp_buffers = 1000 # min 100, 8KB each max_prepared_transactions = 350 # 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 = 1024 # min 64, size in KB maintenance_work_mem = 524288 # min 1024, size in KB, -512 MB- max_stack_depth = 6144 # min 100, size in KB # - Free Space Map - max_fsm_pages = 58000 # min max_fsm_relations*16, 6 bytes each max_fsm_relations = 3000
Re: [PERFORM] tuning for TPC-C benchmark
On Nov 22, 2007 10:45 AM, Kevin Grittner [EMAIL PROTECTED] wrote: I suggest testing with some form of connection pooling. Yeah, that's one of the reasons I suggested DBT-2. It pools connections and is the most mature TPC-C-like test for Postgres. -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 499 Thornall Street, 2nd Floor | [EMAIL PROTECTED] Edison, NJ 08837| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] tuning for TPC-C benchmark
[EMAIL PROTECTED] wrote: Hello all, I'm doing tests on various Database and in particular I'm running a comparison between Oracle 10g and Postgres 8.1 on a dedicated server with 2 processors Dual-Core AMD Opteron 2218 2.6 GHz, 4GB of memory and Debian GNU / Linux version 2.6.18-5. Performance is very similar up to 30 users, but from 40 onwards with Postgres fall quickly. That is not what happens with Oracle that comes to 600 users. Can you help me with the tuning ? The fact that you didn't give any details on your I/O configuration tells me that you don't have much experience with TPC-C. TPC-C is basically limited by random I/O. That means that a good RAID controller and a lot of disks is a must. Looking at some of the results at www.tpc.org, systems with 4 cores have multiple RAID controllers and about a hundred hard drives. You can of course run smaller tests, but those 4 cores are going spend all their time waiting for I/O. See for example these old DBT-2 results I ran to test the Load Distributed Checkpoints feature in 8.3. Now that we got that out of the way, what kind of a test configuration are you using? How many warehouses? Are you using the think-times, per the spec, or are you running something like BenchmarkSQL which just pushes as many queries it can to the server? I'm not sure what you mean by # of users, but you shouldn't use more than 10-30 connections on a test like that. More won't help, because they'll all have to queue for the same resources, whether it's I/O or CPU. How long tests are you running? After some time, you'll need to run vacuums, which make a big difference. 8.3 will perform better, thanks to HOT which reduces the need to vacuum, varvarlen which reduces storage size, leading to better use of the cache and less I/O, and Load Distributed Checkpoints, which reduce the checkpoint spikes which otherwise throw you over the response time requirements. And last but not least, why are you running the benchmark? It's going to be practically irrelevant for any real application. You should benchmark with your application, and your data, to get a comparison that matters for you. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
