Any issues with my tuning...

I've been having performance issues with Postgres (sequential scans vs
index scans in an update statement). I've read that optimizer will
change it's plan based on the resources it thinks are available. In
addition, I've read alot of conflicting info on various parameters, so
I'd like to sort those out as well.

Here's the query I've been having problems with:

UPDATE user_account SET last_name='abc'
FROM commercial_entity ce, commercial_service cs
WHERE user_account.user_account_id = ce.user_account_id AND
ce.commercial_entity_id=cs.commercial_entity_id;

or

UPDATE user_account SET last_name = 'abc'
WHERE EXISTS (SELECT 1 FROM commercial_entity ce, commercial_service cs
WHERE user_account.user_account_id = ce.user_account_id AND
ce.commercial_entity_id = cs.commercial_entity_id);

Both are about the same.

All columns are indexed; all column-types are the same (numeric(10,0)).
A vacuum analyze was run just before the last attempt at running the
above statement.


MACHINE STATS
---------------------------
The machine is a dual-Pentium 3 933mhz, 2 gig of RAM, RAID 5 (3xWestern
Digital 80 gig drives with 8-meg buffers on 3Ware), Red Hat 9.0


POSTGRES TUNING INFO
---------------------------------------

Here are part of the contents of my sysctl.conf file (note that I've
played with values as low as 600000 with no difference)
kernel.shmmax=1400000000
kernel.shmall=1400000000

Here's the uncommented-lines from the postgresql.conf file (not the
default one in the /usr/local/pgsql directory - I've initialzed the
database on a different mount point with more space):

tcpip_socket = true
max_connections = 500
shared_buffers = 96000 # min max_connections*2 or 16, 8KB each
wal_buffers = 64 # min 4, typically 8KB each
sort_mem = 2048 # min 64, size in KB
effective_cache_size = 6000 # typically 8KB each
LC_MESSAGES = 'en_US.UTF-8'
LC_MONETARY = 'en_US.UTF-8'
LC_NUMERIC = 'en_US.UTF-8'
LC_TIME = 'en_US.UTF-8'

Note that I've played with all these values; shared_buffers has been as
low as 5000, and effective_cache_size has been as high as 50000. Sort
mem has varied between 1024 bytes and 4096 bytes. wal_buffers have been
between 16 and 128.


INFO FROM THE MACHINE
-----------------------------------------
Here are the vmstat numbers while running the query.

procs memory swap io system
cpu
r b w swpd free buff cache si so bi bo in cs us
sy id
0 1 2 261940 11624 110072 1334896 12 0 12 748 177 101
2 4 95
0 1 1 261940 11628 110124 1334836 0 0 0 1103 170 59
2 1 97
0 3 1 261928 11616 110180 1334808 3 0 6 1156 169 67
2 2 96
0 2 1 261892 11628 110212 1334636 7 0 7 1035 186 100
2 2 96
0 1 1 261796 11616 110272 1334688 18 0 18 932 169 79
2 1 97
0 1 1 261780 11560 110356 1334964 3 0 3 4155 192 118
2 7 92
0 1 1 261772 11620 110400 1334956 2 0 2 939 162 63
3 0 97
0 1 3 261744 11636 110440 1334872 6 0 9 1871 171 104
3 2 95
0 0 0 261744 13488 110472 1332244 0 0 0 922 195 1271
3 2 94
0 0 0 261744 13436 110492 1332244 0 0 0 24 115 47
0 1 99
0 0 0 261744 13436 110492 1332244 0 0 0 6 109 36
0 5 95
0 0 0 261744 13436 110492 1332244 0 0 0 6 123 63
0 0 100
0 0 0 261744 13436 110492 1332244 0 0 0 6 109 38
0 0 100
0 0 0 261744 13436 110492 1332244 0 0 0 6 112 39
0 1 99

I'm not overly familiar with Linux, but the swap in-out seems low, as
does the io in-out. Have I allocated too much memory? Regardless, it
doesn't explain why the optimizer would decide to do a sequential scan.

Here's the explain-analyze:

Merge Join (cost=11819.21..15258.55 rows=12007 width=752) (actual
time=4107.64..5587.81 rows=20880 loops=1)
Merge Cond: ("outer".commercial_entity_id =
"inner".commercial_entity_id)
-> Index Scan using comm_serv_comm_ent_id_i on commercial_service cs
(cost=0.00..3015.53 rows=88038 width=12) (actual time=0.05..487.23
rows=88038 loops=1)
-> Sort (cost=11819.21..11846.08 rows=10752 width=740) (actual
time=3509.07..3955.15 rows=25098 loops=1)
Sort Key: ce.commercial_entity_id
-> Merge Join (cost=0.00..9065.23 rows=10752 width=740)
(actual time=0.18..2762.13 rows=7990 loops=1)
Merge Cond: ("outer".user_account_id =
"inner".user_account_id)
-> Index Scan using user_account_pkey on user_account
(cost=0.00..8010.39 rows=72483 width=716) (actual time=0.05..2220.86
rows=72483 loops=1)
-> Index Scan using comm_ent_usr_acc_id_i on
commercial_entity ce (cost=0.00..4787.69 rows=78834 width=24) (actual
time=0.02..55.64 rows=7991 loops=1)
Total runtime: 226239.77 msec
(10 rows)

------------------------------------------------------------

Tied up in all this is my inability to grasp what shared_buffers do

From " http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
<http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html> ":

"shbufShared buffers defines a block of memory that PostgreSQL will use
to hold requests that are awaiting attention from the kernel buffer and
CPU." and "The shared buffers parameter assumes that OS is going to
cache a lot of files and hence it is generally very low compared with
system RAM."

From " http://www.lyris.com/lm_help/6.0/tuning_postgresql.html
<http://www.lyris.com/lm_help/6.0/tuning_postgresql.html> "

"Increase the buffer size. Postgres uses a shared memory segment among
its subthreads to buffer data in memory. The default is 512k, which is
inadequate. On many of our installs, we've bumped it to ~16M, which is
still small. If you can spare enough memory to fit your whole database
in memory, do so."

Our database (in Oracle) is just over 4 gig in size; obviously, this
won't comfortably fit in memory (though we do have an Opteron machine
inbound for next week with 4-gig of RAM and SCSI hard-drives). The more
of it we can fit in memory the better.

What about changing these costs - the doc at
http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html
<http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.htm
l> doesn't go into a lot of detail. I was thinking that maybe the
optimizer decided it was faster to do a sequential scan rather than an
index scan based on an analysis of the cost using these values.

#random_page_cost = 4 # units are one sequential page fetch
cost
#cpu_tuple_cost = 0.01 # (same)
#cpu_index_tuple_cost = 0.001 # (same)
#cpu_operator_cost = 0.0025 # (same)

David

On Mon, 2003-10-13 at 14:43, David Griffiths wrote:
> I've been having performance issues with Postgres (sequential scans vs
> index scans in an update statement). I've read that optimizer will
> change it's plan based on the resources it thinks are available. In
> addition, I've read alot of conflicting info on various parameters, so
> I'd like to sort those out as well.
>
> Here's the query I've been having problems with:
>
> UPDATE user_account SET last_name='abc'
> FROM commercial_entity ce, commercial_service cs
> WHERE user_account.user_account_id = ce.user_account_id AND
> ce.commercial_entity_id=cs.commercial_entity_id;
>
> or
>
> UPDATE user_account SET last_name = 'abc'
> WHERE EXISTS (SELECT 1 FROM commercial_entity ce, commercial_service
> cs
> WHERE user_account.user_account_id = ce.user_account_id AND
> ce.commercial_entity_id = cs.commercial_entity_id);
>
> Both are about the same.
>
> All columns are indexed; all column-types are the same
> (numeric(10,0)). A vacuum analyze was run just before the last attempt
> at running the above statement.

First thing is to change ce.user_account_id, ce.commercial_entity_id,
and cs.commercial_entity_id from numeric(10,0) to INTEGER. PG uses
them much more efficiently than it does NUMERIC, since it's a simple
scalar type.

--
-----------------------------------------------------------------
Ron Johnson, Jr. ron(dot)l(dot)johnson(at)cox(dot)net
Jefferson, LA USA

LUKE: Is Perl better than Python?
YODA: No... no... no. Quicker, easier, more seductive.
LUKE: But how will I know why Python is better than Perl?
YODA: You will know. When your code you try to read six months
from now.

David,

> shared_buffers = 96000 # min max_connections*2 or 16, 8KB each

This seems a little high to me, even for 2gb RAM. What % of your available
RAM does it work out to?

> effective_cache_size = 6000 # typically 8KB each

This is very, very low. Given your hardware, I'd set it to 1.5GB.

> Note that I've played with all these values; shared_buffers has been as
> low as 5000, and effective_cache_size has been as high as 50000. Sort
> mem has varied between 1024 bytes and 4096 bytes. wal_buffers have been
> between 16 and 128.

If large updates are slow, increasing checkpoint_segments has the largest
effect on this.

> Tied up in all this is my inability to grasp what shared_buffers do
>
> From " http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
> <http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html> ":
>
> "shbufShared buffers defines a block of memory that PostgreSQL will use
> to hold requests that are awaiting attention from the kernel buffer and
> CPU." and "The shared buffers parameter assumes that OS is going to
> cache a lot of files and hence it is generally very low compared with
> system RAM."

This is correct. Optimal levels among the people on this list who have
bothered to do profiling have ranged btw. 6% and 12% of available RAM, but
never higher.

> From " http://www.lyris.com/lm_help/6.0/tuning_postgresql.html
> <http://www.lyris.com/lm_help/6.0/tuning_postgresql.html> "
>
> "Increase the buffer size. Postgres uses a shared memory segment among
> its subthreads to buffer data in memory. The default is 512k, which is
> inadequate. On many of our installs, we've bumped it to ~16M, which is
> still small. If you can spare enough memory to fit your whole database
> in memory, do so."

This is absolutely incorrect. They are confusing shared_buffers with the
kernel cache, or perhaps confusing PostgreSQL configuration with Oracle
configuration.

I have contacted Lyris and advised them to update the manual.

> Our database (in Oracle) is just over 4 gig in size; obviously, this
> won't comfortably fit in memory (though we do have an Opteron machine
> inbound for next week with 4-gig of RAM and SCSI hard-drives). The more
> of it we can fit in memory the better.

This is done through increasing the effective_cache_size, which encourages the
planner to use data kept in the kernel cache.

> What about changing these costs - the doc at
> http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html
> <http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.htm
> l> doesn't go into a lot of detail. I was thinking that maybe the
> optimizer decided it was faster to do a sequential scan rather than an
> index scan based on an analysis of the cost using these values.
>
> #random_page_cost = 4 # units are one sequential page fetch
> cost
> #cpu_tuple_cost = 0.01 # (same)
> #cpu_index_tuple_cost = 0.001 # (same)
> #cpu_operator_cost = 0.0025 # (same)

That's because nobody to date has done tests on the effect of tinkering with
these values on different machines and setups. We would welcome your
results.

On high-end machines, random_page_cost almost inevatibly needs to be lowered
to 2 or even 1.5 to encourage the use of indexes.

--
-Josh Berkus
Aglio Database Solutions
San Francisco

On Mon, 2003-10-13 at 15:43, David Griffiths wrote:
> Here are part of the contents of my sysctl.conf file (note that I've
> played with values as low as 600000 with no difference)
> kernel.shmmax=1400000000
> kernel.shmall=1400000000

This is only a system-wide limit -- it either allows the shared memory
allocation to proceed, or it does not. Changing it will have no other
effect on the performance of PostgreSQL.

> -> Index Scan using comm_ent_usr_acc_id_i on
> commercial_entity ce (cost=0.00..4787.69 rows=78834 width=24) (actual
> time=0.02..55.64 rows=7991 loops=1)

Interesting that we get this row count estimate so completely wrong
(although it may or may not have anything to do with the actual
performance problem you're running into). Have you run ANALYZE on this
table recently? If so, does increasing this column's statistics target
(using ALTER TABLE ... ALTER COLUMN ... SET STATISTICS) improve the row
count estimate?

-Neil