Re: [PERFORM] postgreSQL performance 8.2.6 vs 8.3.3
On Fri, 20 Feb 2009, David Rees wrote: On Fri, Feb 20, 2009 at 1:34 PM, Battle Mage battlem...@gmail.com wrote: The amount of tps almost doubled, which is good, but i'm worried about the load. For my application, a load increase is bad and I'd like to keep it just like in 8.2.6 (a load average between 3.4 and 4.3). What parameters should I work with to decrease the resulting load average at the expense of tps? Why is it bad? High load can mean a number of things. The only way to reduce the load is to get the client to submit requests slower. I don't think you'll be successful in tuning the database to run slower. I think you're headed in the wrong direction. note that on linux the loadave includes processes that are stalled waiting for I/O to complete. as a result loadave isn't the entire picture. you need to also look to see what the cpu idle time looks like. that being said, I am generally very happy with loadave = # cores and consider loadave = 2x # cores to be acceptable it's nowhere near perfect, but it seems to serve me well as a rule of thumb. David Lang -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] postgreSQL performance 8.2.6 vs 8.3.3
On Mon, Feb 23, 2009 at 2:02 PM, da...@lang.hm wrote: On Fri, 20 Feb 2009, David Rees wrote: On Fri, Feb 20, 2009 at 1:34 PM, Battle Mage battlem...@gmail.com wrote: The amount of tps almost doubled, which is good, but i'm worried about the load. For my application, a load increase is bad and I'd like to keep it just like in 8.2.6 (a load average between 3.4 and 4.3). What parameters should I work with to decrease the resulting load average at the expense of tps? Why is it bad? High load can mean a number of things. The only way to reduce the load is to get the client to submit requests slower. I don't think you'll be successful in tuning the database to run slower. I think you're headed in the wrong direction. note that on linux the loadave includes processes that are stalled waiting for I/O to complete. as a result loadave isn't the entire picture. you need to also look to see what the cpu idle time looks like. that being said, I am generally very happy with loadave = # cores and consider loadave = 2x # cores to be acceptable it's nowhere near perfect, but it seems to serve me well as a rule of thumb. And it's very dependent on type of load. For our primary customer data database a load of 80 to 120 is not uncommon during certain operations (like adding a slave back to the fark and it gets a ton of requests while it's loading up its cache) and it stays responsive. OTOH, a load of 20 on a reporting server doing tons of sequential scans and allocating a lot of memory is way overloaded for the same server type. I had responsive behaviour into the 300 or 400 load range running pgbench in destroy all servers mode (-c 500 -t 1000 or something like that) on that machine. Sure, it wasn't exactly peppy or anything, but most small queries were still running in well under a second. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] postgreSQL performance 8.2.6 vs 8.3.3
I have a server box that has 4GB of RAM, Quad core CPU AMD Opteron 200.152 Mhz (1024 KB cache size each) with plenty of hard drive space. I installed both postgresql 8.2.6 and 8.3.3 on it. I've created a basic test db and used pgbench -i -s 1 -U test -h localhost test to create a sample test db. Then, to benchmark the postgreSQLs, I executed this separately on each of them: pg_bench -h localhost -d test -t 2000 -c 50 -s 50 -U test (2000 transactions per client, 50 clients, scalability factor of 50) Using the above, I get on postgreSQL 8.2.6: Load average: Between 3.4 and 4.3 tps = 589 (including connections establishing) tps = 590 (excluding connections establishing) I get on postgreSQL 8.3.3 Load: Between 4.5 and 5.6 tps = 949 (including connections establishing) tps = 951 (excluding connections establishing) The amount of tps almost doubled, which is good, but i'm worried about the load. For my application, a load increase is bad and I'd like to keep it just like in 8.2.6 (a load average between 3.4 and 4.3). What parameters should I work with to decrease the resulting load average at the expense of tps? Down below is my 8.3.3 configuration file. I removed everything that is commented since if it's commented, it's default value. I also removed from the sample below parameters related to logging. = postgresql.conf begins = port = 5432# (change requires restart) max_connections = 180# (change requires restart) superuser_reserved_connections = 5# (change requires restart) unix_socket_directory = '/var/run/postgresql'# (change requires restart) ssl = off# (change requires restart) shared_buffers = 512MB# min 128kB or max_connections*16kB temp_buffers = 8MB# min 800kB max_prepared_transactions = 5# can be 0 or more work_mem = 16MB# min 64kB maintenance_work_mem = 512MB# min 1MB max_stack_depth = 2MB# min 100kB # - Free Space Map - max_fsm_pages = 240# min max_fsm_relations*16, 6 bytes each 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# 1-1 credits fsync = off# turns forced synchronization on or off #-- # QUERY TUNING #-- seq_page_cost = 1.0# measured on an arbitrary scale random_page_cost = 3.0# same scale as above effective_cache_size = 1024MB #-- # AUTOVACUUM PARAMETERS #-- autovacuum = on# Enable autovacuum subprocess? 'on' autovacuum_naptime = 1min# time between autovacuum runs autovacuum_vacuum_threshold = 500# min number of row updates before autovacuum_analyze_threshold = 250# min number of row updates before autovacuum_vacuum_scale_factor = 0.2# fraction of table size before vacuum autovacuum_analyze_scale_factor = 0.1# fraction of table size before analyze autovacuum_vacuum_cost_delay = 0# default vacuum cost delay for autovacuum_vacuum_cost_limit = 200# default vacuum cost limit for #-- # CLIENT CONNECTION DEFAULTS #-- datestyle = 'iso, mdy' timezone = UTC# actually, defaults to TZ environment lc_messages = 'en_US.UTF-8'# locale for system error message # strings lc_monetary = 'en_US.UTF-8'# locale for monetary formatting lc_numeric = 'en_US.UTF-8'# locale for number formatting lc_time = 'en_US.UTF-8'# locale for time formatting #-- # VERSION/PLATFORM COMPATIBILITY #-- escape_string_warning = off = postgresql.conf ends =
Re: [PERFORM] postgreSQL performance 8.2.6 vs 8.3.3
On Fri, Feb 20, 2009 at 04:34:23PM -0500, Battle Mage wrote: I have a server box that has 4GB of RAM, Quad core CPU AMD Opteron 200.152 Mhz (1024 KB cache size each) with plenty of hard drive space. I installed both postgresql 8.2.6 and 8.3.3 on it. I've created a basic test db and used pgbench -i -s 1 -U test -h localhost test to create a sample test db. Then, to benchmark the postgreSQLs, I executed this separately on each of them: pg_bench -h localhost -d test -t 2000 -c 50 -s 50 -U test (2000 transactions per client, 50 clients, scalability factor of 50) Using the above, I get on postgreSQL 8.2.6: Load average: Between 3.4 and 4.3 tps = 589 (including connections establishing) tps = 590 (excluding connections establishing) I get on postgreSQL 8.3.3 Load: Between 4.5 and 5.6 tps = 949 (including connections establishing) tps = 951 (excluding connections establishing) The amount of tps almost doubled, which is good, but i'm worried about the load. For my application, a load increase is bad and I'd like to keep it just like in 8.2.6 (a load average between 3.4 and 4.3). What parameters should I work with to decrease the resulting load average at the expense of tps? Down below is my 8.3.3 configuration file. I removed everything that is commented since if it's commented, it's default value. I also removed from the sample below parameters related to logging. Please evaluate your load on the 8.3.3 box at 590 tps. If the load is proportional to the tps than the scaled load will be: 2.8 to 3.5 for an equivalent tps. There is no free lunch but 8.3 performs much better than 8.2 and I suspect that this trend will continue. :) Cheers, Ken -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] postgreSQL performance 8.2.6 vs 8.3.3
On Fri, Feb 20, 2009 at 1:34 PM, Battle Mage battlem...@gmail.com wrote: The amount of tps almost doubled, which is good, but i'm worried about the load. For my application, a load increase is bad and I'd like to keep it just like in 8.2.6 (a load average between 3.4 and 4.3). What parameters should I work with to decrease the resulting load average at the expense of tps? Why is it bad? High load can mean a number of things. The only way to reduce the load is to get the client to submit requests slower. I don't think you'll be successful in tuning the database to run slower. I think you're headed in the wrong direction. -Dave -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] postgreSQL performance 8.2.6 vs 8.3.3
On Fri, Feb 20, 2009 at 2:34 PM, Battle Mage battlem...@gmail.com wrote: I have a server box that has 4GB of RAM, Quad core CPU AMD Opteron 200.152 Mhz (1024 KB cache size each) with plenty of hard drive space. I installed both postgresql 8.2.6 and 8.3.3 on it. I've created a basic test db and used pgbench -i -s 1 -U test -h localhost test to create a sample test db. Then, to benchmark the postgreSQLs, I executed this separately on each of them: pg_bench -h localhost -d test -t 2000 -c 50 -s 50 -U test (2000 transactions per client, 50 clients, scalability factor of 50) If you're goint to test with -c50 you should initialize with -s50. -s 50 after initialization doesn't mean anything. It's the first pgbench -i -s nnn where you need to set nnn to 50 (or higher) if you're gonna test with it. Using the above, I get on postgreSQL 8.2.6: Load average: Between 3.4 and 4.3 tps = 589 (including connections establishing) tps = 590 (excluding connections establishing) I get on postgreSQL 8.3.3 Load: Between 4.5 and 5.6 tps = 949 (including connections establishing) tps = 951 (excluding connections establishing) Nice improvement. The amount of tps almost doubled, which is good, but i'm worried about the load. For my application, a load increase is bad and I'd like to keep it just like in 8.2.6 (a load average between 3.4 and 4.3). What parameters should I work with to decrease the resulting load average at the expense of tps? I agree with the other poster. Why is a load increase bad? What does it mean here. I've got one load that runs smoothly with a load factor of 60 to 150 on a server, while the same server with a different load starts to bog down with load factors between 10 and 15. It's a very broad measurement. Don't try to tune to your load factor, try to tune to the real load being applied, and opimtize there. Down below is my 8.3.3 configuration file. I removed everything that is commented since if it's commented, it's default value. I also removed from the sample below parameters related to logging. = postgresql.conf begins = fsync = off# turns forced synchronization on or off So, I assume either your data is easily reproduceable, unimportant, or replicated in such a way that you can survive sudden power loss / kernel crash? Also, is there are reason you're running two different out of date releases of postgresql? -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
