SEO

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)

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/Gener...bits/perf.html

> NOTICE: shared_buffers is 256

This looks like it's way too low. Try something like 2048.

--
/Dennis Björklund


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

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)

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.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Dennis,

> http://www.varlena.com/varlena/Gener...bits/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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

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

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/po...at/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: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.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

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/po...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
>
>



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

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)

---------------------------(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