Re: MySQL versus Postgres

2010/8/12 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>
> Unfortunately, there are quite a few of us for whom "correctness"
> doesn't mean "automatically try to eat all the resources available".
> Your view of what is useful behavior is far too narrow-minded ...

The point is, some one installing PostgreSQL for the first time is not
aware that configuration parameters should be changed to much higher
values than defaults in postgresql.conf

There can be a stronger and more prominent hint / recommendation in
postgresql.conf file, in install README, Resource Consumption section
of manual (http://www.postgresql.org/docs/8.4/interactive/runtime-config-resource.html)
and other appropriate places :

1. README has after Post Installation Setup, Getting Started under What Now?

"------------ The default configuration is, however, not designed for
optimum performance. To achieve optimum performance, several server
parameters must be adjusted, the two most common being shared_buffers
and work_mem. ---------------"

===== This comment can be included immediately after Post Installation Setup.

3. Then in the manual, shared_buffers section says

"The default is typically 32 megabytes (32MB), but might be less if
your kernel settings will not support it (as determined during
initdb). This setting must be at least 128 kilobytes. (Non-default
values of BLCKSZ change the minimum.) However, settings significantly
higher than the minimum are usually needed for good performance."

"If you have a dedicated database server with 1GB or more of RAM, a
reasonable starting value for shared_buffers is 25% of the memory in
your system."

and then there are comments like
"it is unlikely that an allocation of more than 40% of RAM to
shared_buffers will work better than a smaller amount."

"Increasing this parameter might cause PostgreSQL to request more
System V shared memory than your operating system's default
configuration allows. See Section 17.4.1 for information on how to
adjust those parameters, if necessary"

====== Can a final

"We recommend that you to set shared_buffers to 25% of system memory
you can allocate for PostgreSQL"

be included.

4. work_mem section says

"Note that for a complex query, several sort or hash operations might
be running in parallel; each one will be allowed to use as much memory
as this value specifies before it starts to put data into temporary
files. Also, several running sessions could be doing such operations
concurrently. So the total memory used could be many times the value
of work_mem; it is necessary to keep this fact in mind when choosing
the value. Sort operations are used for ORDER BY, DISTINCT, and merge
joins. Hash tables are used in hash joins, hash-based aggregation, and
hash-based processing of IN subqueries. "

This makes Increasing work_mem is a complex calculation for a new user
trying out PostgreSQL.

==== Can something like this included?

"It is recommended that about X% of your system memory to be set as
work_mem. You may have to monitor system performance and adjust this
setting as required"

Best regards,

Ma Sivakumar
எல்லோரும் எல்லாமும் பெற வேண்டும்
http://masivakumar.blogspot.com