Thread: Needed: Simplified guide to optimal memory configuration
I deeply apologize if this has been covered with some similar topic before, but I need a little guidance in the optimization department. We use Postgres as our database and we're having some issues dealing with customers who are, shall we say, "thrifty" when it comes to buying RAM. We tell them to buy at least 1GB, but there's always the bargain chaser who thinks 256MB of RAM "is more than enough. So here's what I need--in layman's terms 'cause I'll need to forward this message on to them to prove what I'm saying (don't ya love customers?). 1. Our database has a total of 35 tables and maybe 300 variables 2. There are five primary tables and only two of these are written to every minute, sometimes up to a menial 1500 transactions per minute. 3. Our customers usually buy RAM in 256MB, 512MB, 1GB or 2GB. We've tried to come up with a optimization scheme based on what we've been able to discern from lists like this, but we don't have a lot of confidence. Using the default settings seems to work best with 1GB, but we need help with the other RAM sizes. What's the problem? The sucker gets s-l-o-w on relatively simple queries. For example, simply listing all of the users online at one time takes 30-45 seconds if we're talking about 800 users. We've adjusted the time period for vacuuming the tables to the point where it occurs once an hour, but we're getting only a 25% performance gain from that. We're looking at the system settings now to see how those can be tweaked. So, what I need is to be pointed to (or told) what are the best settings for our database given these memory configurations. What should we do? Thanks Todd Don't know if this will help, but here's the result of show all: NOTICE: enable_seqscan is on NOTICE: enable_indexscan is on NOTICE: enable_tidscan is on NOTICE: enable_sort is on NOTICE: enable_nestloop is on NOTICE: enable_mergejoin is on NOTICE: enable_hashjoin is on NOTICE: ksqo is off NOTICE: geqo is on NOTICE: tcpip_socket is on NOTICE: ssl is off NOTICE: fsync is on NOTICE: silent_mode is off NOTICE: log_connections is off NOTICE: log_timestamp is off NOTICE: log_pid is off NOTICE: debug_print_query is off NOTICE: debug_print_parse is off NOTICE: debug_print_rewritten is off NOTICE: debug_print_plan is off NOTICE: debug_pretty_print is off NOTICE: show_parser_stats is off NOTICE: show_planner_stats is off NOTICE: show_executor_stats is off NOTICE: show_query_stats is off NOTICE: stats_start_collector is on NOTICE: stats_reset_on_server_start is on NOTICE: stats_command_string is off NOTICE: stats_row_level is off NOTICE: stats_block_level is off NOTICE: trace_notify is off NOTICE: hostname_lookup is off NOTICE: show_source_port is off NOTICE: sql_inheritance is on NOTICE: australian_timezones is off NOTICE: fixbtree is on NOTICE: password_encryption is off NOTICE: transform_null_equals is off NOTICE: geqo_threshold is 20 NOTICE: geqo_pool_size is 0 NOTICE: geqo_effort is 1 NOTICE: geqo_generations is 0 NOTICE: geqo_random_seed is -1 NOTICE: deadlock_timeout is 1000 NOTICE: syslog is 0 NOTICE: max_connections is 64 NOTICE: shared_buffers is 256 NOTICE: port is 5432 NOTICE: unix_socket_permissions is 511 NOTICE: sort_mem is 2048 NOTICE: vacuum_mem is 126622 NOTICE: max_files_per_process is 1000 NOTICE: debug_level is 0 NOTICE: max_expr_depth is 10000 NOTICE: max_fsm_relations is 500 NOTICE: max_fsm_pages is 10000 NOTICE: max_locks_per_transaction is 64 NOTICE: authentication_timeout is 60 NOTICE: pre_auth_delay is 0 NOTICE: checkpoint_segments is 3 NOTICE: checkpoint_timeout is 300 NOTICE: wal_buffers is 8 NOTICE: wal_files is 0 NOTICE: wal_debug is 0 NOTICE: commit_delay is 0 NOTICE: commit_siblings is 5 NOTICE: effective_cache_size is 79350 NOTICE: random_page_cost is 2 NOTICE: cpu_tuple_cost is 0.01 NOTICE: cpu_index_tuple_cost is 0.001 NOTICE: cpu_operator_cost is 0.0025 NOTICE: geqo_selection_bias is 2 NOTICE: default_transaction_isolation is read committed NOTICE: dynamic_library_path is $libdir NOTICE: krb_server_keyfile is FILE:/etc/pgsql/krb5.keytab NOTICE: syslog_facility is LOCAL0 NOTICE: syslog_ident is postgres NOTICE: unix_socket_group is unset NOTICE: unix_socket_directory is unset NOTICE: virtual_host is unset NOTICE: wal_sync_method is fdatasync NOTICE: DateStyle is ISO with US (NonEuropean) conventions NOTICE: Time zone is unset NOTICE: TRANSACTION ISOLATION LEVEL is READ COMMITTED NOTICE: Current client encoding is 'SQL_ASCII' NOTICE: Current server encoding is 'SQL_ASCII' NOTICE: Seed for random number generator is unavailable
On Wed, 15 Jun 2005, Todd Landfried wrote: > So, what I need is to be pointed to (or told) what are the best > settings for our database given these memory configurations. What > should we do? Maybe this will help: http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html > NOTICE: shared_buffers is 256 This looks like it's way too low. Try something like 2048. -- /Dennis Björklund
Dennis Bjorklund <db@zigo.dhs.org> writes:
> On Wed, 15 Jun 2005, Todd Landfried wrote:
>> NOTICE: shared_buffers is 256
> This looks like it's way too low. Try something like 2048.
It also is evidently PG 7.2 or before; SHOW's output hasn't looked like
that in years. Try a more recent release --- there's usually nontrivial
performance improvements in each major release.
regards, tom lane
On Wed, Jun 15, 2005 at 02:06:27 -0700, Todd Landfried <tlandfried@viatornetworks.com> wrote: > > What's the problem? The sucker gets s-l-o-w on relatively simple > queries. For example, simply listing all of the users online at one > time takes 30-45 seconds if we're talking about 800 users. We've > adjusted the time period for vacuuming the tables to the point where > it occurs once an hour, but we're getting only a 25% performance gain > from that. We're looking at the system settings now to see how those > can be tweaked. It might be useful to see example slow queries and the corresponding explain analyze output.
Dennis, > http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html > > > NOTICE: shared_buffers is 256 For everyone's info, the current (8.0) version is at: http://www.powerpostgresql.com/PerfList -- --Josh Josh Berkus Aglio Database Solutions San Francisco
Yes, it is 7.2. Why? because an older version of our software runs on RH7.3 and that was the latest supported release of Postgresql for RH7.3 (that we can find). We're currently ported to 8, but we still have a large installed base with the other version. On Jun 15, 2005, at 7:18 AM, Tom Lane wrote: > Dennis Bjorklund <db@zigo.dhs.org> writes: > >> On Wed, 15 Jun 2005, Todd Landfried wrote: >> >>> NOTICE: shared_buffers is 256 >>> > > >> This looks like it's way too low. Try something like 2048. >> > > It also is evidently PG 7.2 or before; SHOW's output hasn't looked > like > that in years. Try a more recent release --- there's usually > nontrivial > performance improvements in each major release. > > regards, tom lane > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to > majordomo@postgresql.org) >
We run the RPM's for RH 7.3 on our 7.2 install base with no problems. RPM's as recent as for PostgreSQL 7.4.2 are available here: ftp://ftp10.us.postgresql.org/pub/postgresql/binary/v7.4.2/redhat/redhat-7.3/ Or you can always compile from source. There isn't any such thing as a 'supported' package for RH7.2 anyway. -- Mark Lewis On Thu, 2005-06-16 at 07:46 -0700, Todd Landfried wrote: > Yes, it is 7.2. Why? because an older version of our software runs on > RH7.3 and that was the latest supported release of Postgresql for > RH7.3 (that we can find). We're currently ported to 8, but we still > have a large installed base with the other version. > > > On Jun 15, 2005, at 7:18 AM, Tom Lane wrote: > > > Dennis Bjorklund <db@zigo.dhs.org> writes: > > > >> On Wed, 15 Jun 2005, Todd Landfried wrote: > >> > >>> NOTICE: shared_buffers is 256 > >>> > > > > > >> This looks like it's way too low. Try something like 2048. > >> > > > > It also is evidently PG 7.2 or before; SHOW's output hasn't looked > > like > > that in years. Try a more recent release --- there's usually > > nontrivial > > performance improvements in each major release. > > > > regards, tom lane > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to > > majordomo@postgresql.org) > > > > > ---------------------------(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
On Thu, Jun 16, 2005 at 07:46:45 -0700, Todd Landfried <tlandfried@viatornetworks.com> wrote: > Yes, it is 7.2. Why? because an older version of our software runs on > RH7.3 and that was the latest supported release of Postgresql for > RH7.3 (that we can find). We're currently ported to 8, but we still > have a large installed base with the other version. You can build it from source. I run 8.0 stable from CVS on a RH 6.1 box.
Thanks for the link. I'll look into those. I'm going only on what my engineers are telling me, but they say upgrading breaks a lot of source code with some SQL commands that are a pain to hunt down and kill. Not sure if that's true, but that's what I'm told. Todd On Jun 16, 2005, at 10:01 AM, Mark Lewis wrote: > We run the RPM's for RH 7.3 on our 7.2 install base with no problems. > RPM's as recent as for PostgreSQL 7.4.2 are available here: > ftp://ftp10.us.postgresql.org/pub/postgresql/binary/v7.4.2/redhat/ > redhat-7.3/ > > Or you can always compile from source. There isn't any such thing > as a > 'supported' package for RH7.2 anyway. > > -- Mark Lewis > > > On Thu, 2005-06-16 at 07:46 -0700, Todd Landfried wrote: > > >> Yes, it is 7.2. Why? because an older version of our software runs on >> RH7.3 and that was the latest supported release of Postgresql for >> RH7.3 (that we can find). We're currently ported to 8, but we still >> have a large installed base with the other version. >> >> >> On Jun 15, 2005, at 7:18 AM, Tom Lane wrote: >> >> >> >>> Dennis Bjorklund <db@zigo.dhs.org> writes: >>> >>> >>> >>>> On Wed, 15 Jun 2005, Todd Landfried wrote: >>>> >>>> >>>> >>>>> NOTICE: shared_buffers is 256 >>>>> >>>>> >>>>> >>> >>> >>> >>> >>>> This looks like it's way too low. Try something like 2048. >>>> >>>> >>>> >>> >>> It also is evidently PG 7.2 or before; SHOW's output hasn't looked >>> like >>> that in years. Try a more recent release --- there's usually >>> nontrivial >>> performance improvements in each major release. >>> >>> regards, tom lane >>> >>> ---------------------------(end of >>> broadcast)--------------------------- >>> TIP 2: you can get off all lists at once with the unregister command >>> (send "unregister YourEmailAddressHere" to >>> majordomo@postgresql.org) >>> >>> >>> >> >> >> ---------------------------(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 >> >> > > > ---------------------------(end of > broadcast)--------------------------- > TIP 8: explain analyze is your friend > >
On Thu, Jun 16, 2005 at 07:15:08PM -0700, Todd Landfried wrote: > Thanks for the link. I'll look into those. > > I'm going only on what my engineers are telling me, but they say > upgrading breaks a lot of source code with some SQL commands that are > a pain to hunt down and kill. Not sure if that's true, but that's > what I'm told. This is true. Migrating to a newer version is not a one-day thing. But increasing shared_buffers is trivially done, would get you lots of benefit, and it's very unlikely to break anything. (Migrating one version can be painful already -- migrating three versions on one shot might be a nightmare. OTOH it's much better to pay the cost of migration once rather than three times ...) -- Alvaro Herrera (<alvherre[a]surnet.cl>) "The Postgresql hackers have what I call a "NASA space shot" mentality. Quite refreshing in a world of "weekend drag racer" developers." (Scott Marlowe)
Todd, > I'm going only on what my engineers are telling me, but they say > upgrading breaks a lot of source code with some SQL commands that are > a pain to hunt down and kill. Not sure if that's true, but that's > what I'm told. Depends on your app, but certainly that can be true. Oddly, 7.2 -> 8.0 is less trouble than 7.2 -> 7.4 because of some type casting issues which were resolved. Mind you, in the past a quick "sed" script has been adequate for me to fix compatibility issues. -- Josh Berkus Aglio Database Solutions San Francisco
For those who provided some guidance, I say "thank you." You comments helped out a lot. All of our customers who are using the older release are now very pleased with the performance of the database now that we were able to give them meaningful configuration settings. I'm also pleased to see that Frank WIles has taken upon himself the effort to write this guidance down for folks like me. Kudos to you all. Thanks again. Todd On Jun 15, 2005, at 2:06 AM, Todd Landfried wrote: > I deeply apologize if this has been covered with some similar topic > before, but I need a little guidance in the optimization > department. We use Postgres as our database and we're having some > issues dealing with customers who are, shall we say, "thrifty" when > it comes to buying RAM. > > We tell them to buy at least 1GB, but there's always the bargain > chaser who thinks 256MB of RAM "is more than enough. So here's what > I need--in layman's terms 'cause I'll need to forward this message > on to them to prove what I'm saying (don't ya love customers?). > > 1. Our database has a total of 35 tables and maybe 300 variables > 2. There are five primary tables and only two of these are written > to every minute, sometimes up to a menial 1500 transactions per > minute. > 3. Our customers usually buy RAM in 256MB, 512MB, 1GB or 2GB. We've > tried to come up with a optimization scheme based on what we've > been able to discern from lists like this, but we don't have a lot > of confidence. Using the default settings seems to work best with > 1GB, but we need help with the other RAM sizes. > > What's the problem? The sucker gets s-l-o-w on relatively simple > queries. For example, simply listing all of the users online at one > time takes 30-45 seconds if we're talking about 800 users. We've > adjusted the time period for vacuuming the tables to the point > where it occurs once an hour, but we're getting only a 25% > performance gain from that. We're looking at the system settings > now to see how those can be tweaked. > > So, what I need is to be pointed to (or told) what are the best > settings for our database given these memory configurations. What > should we do? > > Thanks > > Todd > > Don't know if this will help, but here's the result of show all: > > NOTICE: enable_seqscan is on > NOTICE: enable_indexscan is on > NOTICE: enable_tidscan is on > NOTICE: enable_sort is on > NOTICE: enable_nestloop is on > NOTICE: enable_mergejoin is on > NOTICE: enable_hashjoin is on > NOTICE: ksqo is off > NOTICE: geqo is on > NOTICE: tcpip_socket is on > NOTICE: ssl is off > NOTICE: fsync is on > NOTICE: silent_mode is off > NOTICE: log_connections is off > NOTICE: log_timestamp is off > NOTICE: log_pid is off > NOTICE: debug_print_query is off > NOTICE: debug_print_parse is off > NOTICE: debug_print_rewritten is off > NOTICE: debug_print_plan is off > NOTICE: debug_pretty_print is off > NOTICE: show_parser_stats is off > NOTICE: show_planner_stats is off > NOTICE: show_executor_stats is off > NOTICE: show_query_stats is off > NOTICE: stats_start_collector is on > NOTICE: stats_reset_on_server_start is on > NOTICE: stats_command_string is off > NOTICE: stats_row_level is off > NOTICE: stats_block_level is off > NOTICE: trace_notify is off > NOTICE: hostname_lookup is off > NOTICE: show_source_port is off > NOTICE: sql_inheritance is on > NOTICE: australian_timezones is off > NOTICE: fixbtree is on > NOTICE: password_encryption is off > NOTICE: transform_null_equals is off > NOTICE: geqo_threshold is 20 > NOTICE: geqo_pool_size is 0 > NOTICE: geqo_effort is 1 > NOTICE: geqo_generations is 0 > NOTICE: geqo_random_seed is -1 > NOTICE: deadlock_timeout is 1000 > NOTICE: syslog is 0 > NOTICE: max_connections is 64 > NOTICE: shared_buffers is 256 > NOTICE: port is 5432 > NOTICE: unix_socket_permissions is 511 > NOTICE: sort_mem is 2048 > NOTICE: vacuum_mem is 126622 > NOTICE: max_files_per_process is 1000 > NOTICE: debug_level is 0 > NOTICE: max_expr_depth is 10000 > NOTICE: max_fsm_relations is 500 > NOTICE: max_fsm_pages is 10000 > NOTICE: max_locks_per_transaction is 64 > NOTICE: authentication_timeout is 60 > NOTICE: pre_auth_delay is 0 > NOTICE: checkpoint_segments is 3 > NOTICE: checkpoint_timeout is 300 > NOTICE: wal_buffers is 8 > NOTICE: wal_files is 0 > NOTICE: wal_debug is 0 > NOTICE: commit_delay is 0 > NOTICE: commit_siblings is 5 > NOTICE: effective_cache_size is 79350 > NOTICE: random_page_cost is 2 > NOTICE: cpu_tuple_cost is 0.01 > NOTICE: cpu_index_tuple_cost is 0.001 > NOTICE: cpu_operator_cost is 0.0025 > NOTICE: geqo_selection_bias is 2 > NOTICE: default_transaction_isolation is read committed > NOTICE: dynamic_library_path is $libdir > NOTICE: krb_server_keyfile is FILE:/etc/pgsql/krb5.keytab > NOTICE: syslog_facility is LOCAL0 > NOTICE: syslog_ident is postgres > NOTICE: unix_socket_group is unset > NOTICE: unix_socket_directory is unset > NOTICE: virtual_host is unset > NOTICE: wal_sync_method is fdatasync > NOTICE: DateStyle is ISO with US (NonEuropean) conventions > NOTICE: Time zone is unset > NOTICE: TRANSACTION ISOLATION LEVEL is READ COMMITTED > NOTICE: Current client encoding is 'SQL_ASCII' > NOTICE: Current server encoding is 'SQL_ASCII' > NOTICE: Seed for random number generator is unavailable > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to > majordomo@postgresql.org) >