[PERFORM] upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

[Douglas J Hunley]

Hello great gurus of performance:
Our 'esteemed' Engr group recently informed a customer that in their testing, 
upgrading to 8.2.x improved the performance of our J2EE 
application "approximately 20%", so of course, the customer then tasked me 
with upgrading them. We dumped their db, removed pgsql, installed the 8.2.4 
rpms from postgresql.org, did an initdb, and the pg_restored their data. It's 
been about a week now, and the customer is complaining that in their testing, 
they are seeing a 30% /decrease/ in general performance. Of course, our Engr 
group is being less than responsive, and I have a feeling all they're doing 
is googling for answers, so I'm turning to this group for actual 
assistance :)
I'd like to start by examining the poistgresql.conf file. Under 7.4.x, we had 
spent the better part of their 2 years as a customer tuning and tweaking 
setting. I've attached the file that was in place at the time of upgrade. I 
did some cursory googling of my own, and quickly realized that enough has 
changed in v8 that I'm not comfortable making the exact same modification to 
their new config file as some options are new, some have gone away, etc. I've 
attached the existing v8 conf file as well. 
I'd really like it if someone could assist me in determining which of the v8 
options need adjusted to be 'functionally equivalent' to the v7 file. Right 
now, my goal is to get the customer back to the previous level of 
performance, and only then pursue further optimization. I can provide any and 
all information needed, but didn't know what to include initially, so I've 
opted to include the minimal :)
The DB server in question does nothing else, is running CentOS 4.5, kernel 
2.6.9-55.ELsmp. Hyperthreading is disabled in the BIOS and there are 2 Xeon 
3.4Ghz cpus. There is 8Gb of RAM in the machine, and another 8Gb of swap.

Thank you in advance for any and all assistance you can provide.
-- 
Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778
http://doug.hunley.homeip.net

Handy Guide to Modern Science:
1. If it's green or it wiggles, it's biology.
2. If it stinks, it's chemistry.
3. If it doesn't work, it's physics.
# -
# PostgreSQL configuration file
# -
#
# This file consists of lines of the form:
#
#   name = value
#
# (The '=' is optional.) White space may be used. Comments are introduced
# with '#' anywhere on a line. The complete list of option names and
# allowed values can be found in the PostgreSQL documentation. The
# commented-out settings shown in this file represent the default values.
#
# Any option can also be given as a command line switch to the
# postmaster, e.g. 'postmaster -c log_connections=on'. Some options
# can be changed at run-time with the 'SET' SQL command.
#
# This file is read on postmaster startup and when the postmaster
# receives a SIGHUP. If you edit the file on a running system, you have 
# to SIGHUP the postmaster for the changes to take effect, or use 
# "pg_ctl reload".


#---
# CONNECTIONS AND AUTHENTICATION
#---

# - Connection Settings -

tcpip_socket = true
max_connections = 220
# note: increasing max_connections costs about 500 bytes of shared
# memory per connection slot, in addition to costs from shared_buffers
# and max_locks_per_transaction.
#superuser_reserved_connections = 2
#port = 5432
#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777 # octal
#virtual_host = ''  # what interface to listen on; defaults to any
#rendezvous_name = ''   # defaults to the computer name

# - Security & Authentication -

#authentication_timeout = 60# 1-600, in seconds
#ssl = false
#password_encryption = true
#krb_server_keyfile = ''
#db_user_namespace = false


#---
# RESOURCE USAGE (except WAL)
#---

# - Memory -

# Needs an actual restart to change!!
shared_buffers = 25000  # min 16, at least max_connections*2, 8KB each
#shared_buffers = 75000 # min 16, at least max_connections*2, 8KB each
#shared_buffers = 20# min 16, at least max_connections*2, 
8KB each
sort_mem = 15000# min 64, size in KB
vacuum_mem = 10 # min 1024, size in KB
#vacuum_mem = 32768 # min 1024, size in KB

# - Free Space Map -

max_fsm_pages = 20  # min max_fsm_relations*16, 6 bytes each
#max_fsm_pages = 2  # min max_fsm_relations*16, 6 bytes each
#max_fsm_relations = 1000   # min 100, ~50 bytes each

# - Kernel Resource Usage -

#max_files_per_process = 1000   # min 25
#preload_libraries = ''


#-

Re: [PERFORM] upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

[Michael Fuhr]

On Sat, Jun 02, 2007 at 09:13:32AM -0400, Douglas J Hunley wrote:
> Our 'esteemed' Engr group recently informed a customer that in their testing, 
> upgrading to 8.2.x improved the performance of our J2EE 
> application "approximately 20%", so of course, the customer then tasked me 
> with upgrading them. We dumped their db, removed pgsql, installed the 8.2.4 
> rpms from postgresql.org, did an initdb, and the pg_restored their data. It's 
> been about a week now, and the customer is complaining that in their testing, 
> they are seeing a 30% /decrease/ in general performance.

After the restore, did you ANALYZE the entire database to update
the planner's statistics?  Have you enabled autovacuum or are you
otherwise vacuuming and analyzing regularly?  What kind of queries
are slower than desired?  If you post an example query and the
EXPLAIN ANALYZE output then we might be able to see if the slowness
is due to query plans.

A few differences between the configuration files stand out.  The
7.4 file has the following settings:

  shared_buffers = 25000
  sort_mem = 15000
  effective_cache_size = 196608

The 8.2 config has:

  #shared_buffers = 32MB
  #work_mem = 1MB
  #effective_cache_size = 128MB

To be equivalent to the 7.4 config the 8.2 config would need:

  shared_buffers = 195MB
  work_mem = 15000kB
  effective_cache_size = 1536MB

With 8GB of RAM you might try increasing shared_buffers to 400MB - 800MB
(less if the entire database isn't that big) and effective_cache_size
to 5GB - 6GB.  You might have to increase the kernel's shared memory
settings before increasing shared_buffers.

Some of the other settings are the same between the configurations
but deserve discussion:

  fsync = off

Disabling fsync is dangerous -- are all parties aware of the risk
and willing to accept it?  Has the risk been weighed against the
cost of upgrading to a faster I/O subsystem?  How much performance
benefit are you realizing by disabling fsync?  What kind of activity
led to the decision to disable fynsc?  Are applications doing
anything like executing large numbers of insert/update/delete
statements outside of a transaction block when they could be done
in a single transaction?

  commit_delay = 2
  commit_siblings = 3

What kind of activity led to the above settings?  Are they a guess
or were they determined empirically?  How much benefit are they
providing and how did you measure that?

  enable_mergejoin = off
  geqo = off

I've occasionally had to tweak planner settings but I prefer to do
so for specific queries instead of changing them server-wide.

-- 
Michael Fuhr

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [PERFORM] upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

[Rafael Martinez]

Douglas J Hunley wrote:

Hello

The DB server in question does nothing else, is running CentOS 4.5, kernel 
2.6.9-55.ELsmp. Hyperthreading is disabled in the BIOS and there are 2 Xeon 
3.4Ghz cpus. There is 8Gb of RAM in the machine, and another 8Gb of swap.




After a very quick read of your configuration files, I found some 
paramaters that need to be change if your server has 8GB of RAM. The 
values of these parameters depend a lot of how much RAM you have, what 
type of database you have (reading vs. writing) and how big the database is.


I do not have experience with 8.2.x yet, but with 8.1.x we are using as 
defaults in out 8GB RAM servers these values in some of the paramaters 
(they are not the only ones, but they are the minimum to change):


25% of RAM for shared_buffers
2/3 of ram for effective_cache_size
256MB for maintenance_work_mem
32-64MB for work_mem
128 checkpoint_segments
2 random_page_cost

And the most important of all:

fsync should be ***ON*** if you appreciate your data.

It looks like you are using default values 



#shared_buffers = 32MB  # min 128kB or max_connections*16kB
#work_mem = 1MB # min 64kB
#maintenance_work_mem = 16MB# min 1MB
fsync = off # turns forced synchronization on or off
#effective_cache_size = 128MB

[]

--
 Rafael Martinez, <[EMAIL PROTECTED]>
 Center for Information Technology Services
 University of Oslo, Norway

 PGP Public Key: http://folk.uio.no/rafael/>

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

Re: [PERFORM] upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

[Tom Lane]

Douglas J Hunley <[EMAIL PROTECTED]> writes:
> ... We dumped their db, removed pgsql, installed the 8.2.4 
> rpms from postgresql.org, did an initdb, and the pg_restored their data. It's 
> been about a week now, and the customer is complaining that in their testing, 
> they are seeing a 30% /decrease/ in general performance.

Well, you've definitely blown it on transferring the config-file
settings --- a quick look says that shared_buffers, work_mem, and
max_fsm_pages are all still default in the 8.2 config file.
Don't be frightened off by the "KB/MB" usages in the 8.2 file ---
you can still write "shared_buffers = 25000" if you'd rather specify
it in number of buffers than in megabytes.

There are some things you *did* transfer that I find pretty
questionable, like "enable_mergejoin = false".  There are very major
differences between the 7.4 and 8.2 planners, so you need to revisit
the tests that led you to do that.

Another thing that seems strange is that the 8.2 config file does not
seem to have been processed by initdb --- or did you explicitly comment
out the settings it made?

Another thing to check is whether you ANALYZEd the new database after
loading data; a pg_dump/reload sequence doesn't do that.

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [PERFORM] upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

[Stefan Kaltenbrunner]

Michael Fuhr wrote:
> On Sat, Jun 02, 2007 at 09:13:32AM -0400, Douglas J Hunley wrote:
>> Our 'esteemed' Engr group recently informed a customer that in their 
>> testing, 
>> upgrading to 8.2.x improved the performance of our J2EE 
>> application "approximately 20%", so of course, the customer then tasked me 
>> with upgrading them. We dumped their db, removed pgsql, installed the 8.2.4 
>> rpms from postgresql.org, did an initdb, and the pg_restored their data. 
>> It's 
>> been about a week now, and the customer is complaining that in their 
>> testing, 
>> they are seeing a 30% /decrease/ in general performance.
> 
> After the restore, did you ANALYZE the entire database to update
> the planner's statistics?  Have you enabled autovacuum or are you
> otherwise vacuuming and analyzing regularly?  What kind of queries
> are slower than desired?  If you post an example query and the
> EXPLAIN ANALYZE output then we might be able to see if the slowness
> is due to query plans.
> 
> A few differences between the configuration files stand out.  The
> 7.4 file has the following settings:
> 
>   shared_buffers = 25000
>   sort_mem = 15000
>   effective_cache_size = 196608
> 
> The 8.2 config has:
> 
>   #shared_buffers = 32MB
>   #work_mem = 1MB
>   #effective_cache_size = 128MB
> 
> To be equivalent to the 7.4 config the 8.2 config would need:
> 
>   shared_buffers = 195MB
>   work_mem = 15000kB
>   effective_cache_size = 1536MB
> 
> With 8GB of RAM you might try increasing shared_buffers to 400MB - 800MB
> (less if the entire database isn't that big) and effective_cache_size
> to 5GB - 6GB.  You might have to increase the kernel's shared memory
> settings before increasing shared_buffers.

some testing here has shown that while it is usually a good idea to set
effective_cache_size rather optimistically in versions <8.2 it is
advisable to make it accurate or even a bit less than that in 8.2 and up.


Stefan

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

Re: [PERFORM] Append table

[Hanu Kurubar]

Any luck on appending two table in PostgreSQL.
Below are two table with same schema that have different values. In this
case EmpID is unique value.

tabelA

EmpId (Int) EmpName (String)
1   Hanu
2   Alvaro


tabelB

EmpId (Int) EmpName (String)
3   Michal
4   Tom


I would be looking below output after appending tableA with tableB. Is this
possible in PostgreSQL?


tabelA

EmpId (Int) EmpName (String)
1   Hanu
2   Alvaro
3   Michal
4   Tom


Thanks,
Hanu


On 5/30/07, Hanu Kurubar <[EMAIL PROTECTED]> wrote:


Can you help me appending two table values into single table without
performing INSERT?
Note that these tables are of same schema.

Is there any sql command is supported?

Thanks,
Hanu


On 5/29/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote:
>
> Michal Szymanski wrote:
> > There is another strange thing. We have two versions of our test
> > >>environment one with production DB copy and second genereated with
> > >>minimal data set and it is odd that update presented above on copy
> of
> > >>production is executing 170ms but on small DB it executing 6s 
> > >
> > >How are you vacuuming the tables?
> > >
> > Using pgAdmin (DB is installed on my laptop) and I use this tool for
> > vaccuminh, I do not think that vaccuming can help because I've tested
> on
> > both database just after importing.
>
> I think you are misunderstanding the importance of vacuuming the table.
> Try this: on a different terminal from the one running the test, run a
> VACUUM on the updated table with vacuum_cost_delay set to 20, on an
> infinite loop.  Keep this running while you do your update test.  Vary
> the vacuum_cost_delay and measure the average/min/max UPDATE times.
> Also try putting a short sleep on the infinite VACUUM loop and see how
> its length affects the UPDATE times.
>
> One thing not clear to me is if your table is in a clean state.  Before
> running this test, do a TRUNCATE and import the data again.  This will
> get rid of any dead space that may be hurting your measurements.
>
> --
> Alvaro Herrera
> http://www.advogato.org/person/alvherre
> "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 4: Have you searched our list archives?
>
>   http://archives.postgresql.org
>



--
With best regards,
Hanumanthappa Kurubar
Mobile: 98 801 800 65





--
With best regards,
Hanumanthappa Kurubar
Mobile: 98 801 800 65

Re: [PERFORM] Append table

[Arjen van der Meijden]

There are two solutions:
You can insert all data from tableB in tableA using a simple insert 
select-statement like so:

INSERT INTO tabelA SELECT EmpId, EmpName FROM tabelB;

Or you can visually combine them without actually putting the records in 
a single table. That can be with a normal select-union statement or with 
a view, something like this:

SELECT EmpId, EmpName FROM tabelA UNION EmpID, EmpName FROM tabelB;

You can use this query as a table-generating subquery in a FROM-clause, 
like so:


SELECT * FROM (SELECT EmpId, EmpName FROM tabelA UNION EmpID, EmpName 
FROM tabelB) as emps WHERE EmpId = 1;


Or with the view:
CREATE VIEW tabelC AS SELECT EmpId, EmpName FROM tabelA UNION EmpID, 
EmpName FROM tabelB;


And then you can use the view as if it was a normal table (altough 
inserts are not possible without applying rules to them, see the manual 
for that).


SELECT * FROM tabelC WHERE EmpId = 1;

Best regards,

Arjen

On 2-6-2007 17:52 Hanu Kurubar wrote:

Any luck on appending two table in PostgreSQL.
Below are two table with same schema that have different values. In this 
case EmpID is unique value.
 
tabelA


EmpId (Int) EmpName (String)
1   Hanu
2   Alvaro
 
 
tabelB


EmpId (Int) EmpName (String)
3   Michal
4   Tom
 
 
I would be looking below output after appending tableA with tableB. Is 
this possible in PostgreSQL?


 
tabelA


EmpId (Int) EmpName (String)
1   Hanu
2   Alvaro
3   Michal
4   Tom

 


Thanks,
Hanu

 
On 5/30/07, *Hanu Kurubar* <[EMAIL PROTECTED] 
> wrote:


Can you help me appending two table values into single table without
performing INSERT?
Note that these tables are of same schema.
 
Is there any sql command is supported?
 
Thanks,

Hanu

 
On 5/29/07, *Alvaro Herrera* <[EMAIL PROTECTED]

> wrote:

Michal Szymanski wrote:
 > There is another strange thing. We have two versions of our test
 > >>environment one with production DB copy and second
genereated with
 > >>minimal data set and it is odd that update presented above
on copy of
 > >>production is executing 170ms but on small DB it executing
6s 
 > >
 > >How are you vacuuming the tables?
 > >
 > Using pgAdmin (DB is installed on my laptop) and I use this
tool for
 > vaccuminh, I do not think that vaccuming can help because
I've tested on
 > both database just after importing.

I think you are misunderstanding the importance of vacuuming the
table.
Try this: on a different terminal from the one running the test,
run a
VACUUM on the updated table with vacuum_cost_delay set to 20, on an
infinite loop.  Keep this running while you do your update
test.  Vary
the vacuum_cost_delay and measure the average/min/max UPDATE times.
Also try putting a short sleep on the infinite VACUUM loop and
see how
its length affects the UPDATE times.

One thing not clear to me is if your table is in a clean
state.  Before
running this test, do a TRUNCATE and import the data
again.  This will
get rid of any dead space that may be hurting your measurements.

--
Alvaro
Herrerahttp://www.advogato.org/person/alvherre
"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 4: Have you searched our list archives?

  http://archives.postgresql.org





-- 
With best regards,

Hanumanthappa Kurubar
Mobile: 98 801 800 65 





--
With best regards,
Hanumanthappa Kurubar
Mobile: 98 801 800 65


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [PERFORM] Append table

[Gregory Stark]

"Arjen van der Meijden" <[EMAIL PROTECTED]> writes:

> There are two solutions:
...
> Or you can visually combine them without actually putting the records in a
> single table. That can be with a normal select-union statement or with a view,
> something like this:
> SELECT EmpId, EmpName FROM tabelA UNION EmpID, EmpName FROM tabelB;

If you're sure the two sets are distinct or you want to get any duplicates and
not eliminate them then if you went with this option you would want to use
"UNION ALL" not just a plain union.

In SQL UNION has to remove duplicates which often involves gathering all the
records and performing a big sort and lots of extra work. UNION ALL is much
faster and can start returning records right away.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com


---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [PERFORM] Postgres Benchmark Results

[Josh Berkus]

PFC,

Thanks for doing those graphs.  They've been used by Simon &Heikki, and 
now me, to show our main issue with PostgreSQL performance: consistency. 
 That is, our median response time beats MySQL and even Oracle, but our 
bottom 10% does not, and is in fact intolerably bad.


If you want us to credit you by your real name, let us know what it is.

Thanks!

--Josh Berkus

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [PERFORM] Append table

[Hanu Kurubar]

Thanks for quick answer.

Previsoly I have exported table records into employee.csv file using COPY
command which has 36,00, records.

After that I have added few more entries in database and EmpId is
incremented.

I want put the exported data back into database with re-generating new
EmpId. Like importing back all data without harming existing data.

If I choose INSERT opeartion, it is very time consuming.

I am thinking of creating new table (dummy table) and copying all data (COPY
from command) into that table and maniplate the data so that EmpId is unique
in parent table and dummy table and then append these two tables.

I feel creating views and joins will make things complex.

Do you have inputs on this?

On 6/2/07, Arjen van der Meijden <[EMAIL PROTECTED]> wrote:


There are two solutions:
You can insert all data from tableB in tableA using a simple insert
select-statement like so:
INSERT INTO tabelA SELECT EmpId, EmpName FROM tabelB;

Or you can visually combine them without actually putting the records in
a single table. That can be with a normal select-union statement or with
a view, something like this:
SELECT EmpId, EmpName FROM tabelA UNION EmpID, EmpName FROM tabelB;

You can use this query as a table-generating subquery in a FROM-clause,
like so:

SELECT * FROM (SELECT EmpId, EmpName FROM tabelA UNION EmpID, EmpName
FROM tabelB) as emps WHERE EmpId = 1;

Or with the view:
CREATE VIEW tabelC AS SELECT EmpId, EmpName FROM tabelA UNION EmpID,
EmpName FROM tabelB;

And then you can use the view as if it was a normal table (altough
inserts are not possible without applying rules to them, see the manual
for that).

SELECT * FROM tabelC WHERE EmpId = 1;

Best regards,

Arjen

On 2-6-2007 17:52 Hanu Kurubar wrote:
> Any luck on appending two table in PostgreSQL.
> Below are two table with same schema that have different values. In this
> case EmpID is unique value.
>
> tabelA
> 
> EmpId (Int) EmpName (String)
> 1   Hanu
> 2   Alvaro
>
>
> tabelB
> 
> EmpId (Int) EmpName (String)
> 3   Michal
> 4   Tom
>
>
> I would be looking below output after appending tableA with tableB. Is
> this possible in PostgreSQL?
>
>
> tabelA
> 
> EmpId (Int) EmpName (String)
> 1   Hanu
> 2   Alvaro
> 3   Michal
> 4   Tom
>
>
>
> Thanks,
> Hanu
>
>
> On 5/30/07, *Hanu Kurubar* <[EMAIL PROTECTED]
> > wrote:
>
> Can you help me appending two table values into single table without
> performing INSERT?
> Note that these tables are of same schema.
>
> Is there any sql command is supported?
>
> Thanks,
> Hanu
>
>
> On 5/29/07, *Alvaro Herrera* <[EMAIL PROTECTED]
> > wrote:
>
> Michal Szymanski wrote:
>  > There is another strange thing. We have two versions of our
test
>  > >>environment one with production DB copy and second
> genereated with
>  > >>minimal data set and it is odd that update presented above
> on copy of
>  > >>production is executing 170ms but on small DB it executing
> 6s 
>  > >
>  > >How are you vacuuming the tables?
>  > >
>  > Using pgAdmin (DB is installed on my laptop) and I use this
> tool for
>  > vaccuminh, I do not think that vaccuming can help because
> I've tested on
>  > both database just after importing.
>
> I think you are misunderstanding the importance of vacuuming the
> table.
> Try this: on a different terminal from the one running the test,
> run a
> VACUUM on the updated table with vacuum_cost_delay set to 20, on
an
> infinite loop.  Keep this running while you do your update
> test.  Vary
> the vacuum_cost_delay and measure the average/min/max UPDATE
times.
> Also try putting a short sleep on the infinite VACUUM loop and
> see how
> its length affects the UPDATE times.
>
> One thing not clear to me is if your table is in a clean
> state.  Before
> running this test, do a TRUNCATE and import the data
> again.  This will
> get rid of any dead space that may be hurting your measurements.
>
> --
> Alvaro
> Herrera
http://www.advogato.org/person/alvherre
> "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 4: Have you searched our list archives?
>
>   http://archives.postgresql.org
> 
>
>
>
>
> --
> With best regards,
> Hanumanthappa Kurubar
> Mobile: 98 801 800 65
>
>
>
>
> -