Tunning postgresql

[Josué Maldonado]

Hello list,

I have 7.3.4 on RH 8, server hardware is a dual processor Intel Xeon 2.4
Ghz, 2G RAM. I was reading about tunning and would like to get some help
from you, I changed some of the default values and the performance
increased a little but I think still I can get more from that box.

What should be the right values to set in kernel and postgresql.conf to
get maximum performance, actually kernel share memory is:
cat /proc/sys/kernel/shmmax
268435456
cat /proc/sys/kernel/shmall
268435456

postgresql.conf contains these configurations modified:

shared_buffers = 17000 # min max_connections*2 or 16, 8KB each
max_fsm_relations = 400 # min 10, fsm is free space map, ~40
max_fsm_pages = 80000 # min 1000, fsm is free space map, ~6
max_locks_per_transaction = 64 # min 10
sort_mem = 16384 # min 64, size in KB
effective_cache_size = 1700000 # typically 8KB each

Still don't understand very well how to combine these parameters to gain
maximun performance for postgresql, any help or comment about this
would be very appreciated.

Thanks,
--
Josué Maldonado.

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

[Stephen Robert Norris]

On Wed, 2003-11-19 at 14:25, Josué Maldonado wrote:

Hello list,

I have 7.3.4 on RH 8, server hardware is a dual processor Intel Xeon 2.4
Ghz, 2G RAM. I was reading about tunning and would like to get some help
from you, I changed some of the default values and the performance
increased a little but I think still I can get more from that box.

What should be the right values to set in kernel and postgresql.conf to
get maximum performance, actually kernel share memory is:
cat /proc/sys/kernel/shmmax
268435456
cat /proc/sys/kernel/shmall
268435456

postgresql.conf contains these configurations modified:

shared_buffers = 17000 # min max_connections*2 or 16, 8KB each
max_fsm_relations = 400 # min 10, fsm is free space map, ~40
max_fsm_pages = 80000 # min 1000, fsm is free space map, ~6
max_locks_per_transaction = 64 # min 10
sort_mem = 16384 # min 64, size in KB
effective_cache_size = 1700000 # typically 8KB each

Still don't understand very well how to combine these parameters to gain
maximun performance for postgresql, any help or comment about this
would be very appreciated.

Thanks,

Speaking from long experimentation, you're much, much better off making
sure your indices and queries are optimal that messing around with
buffer space. Buffer space tuning might get you a few percent
performance once you pick a reasonable value; query tuning can get you
orders of magnitude.

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

http://archives.postgresql.org

[Mark Kirkwood]

Josué Maldonado wrote:

Hello list,
postgresql.conf contains these configurations modified:

shared_buffers = 17000 # min max_connections*2 or 16, 8KB each
max_fsm_relations = 400 # min 10, fsm is free space map, ~40
max_fsm_pages = 80000 # min 1000, fsm is free space map, ~6
max_locks_per_transaction = 64 # min 10
sort_mem = 16384 # min 64, size in KB
effective_cache_size = 1700000 # typically 8KB each

These seem pretty reasonable... apart from the effective cache size,
maybe chop off a zero :

effective_cache_size = 170000 # about 1.2G

I wonder if the original setting, 6 times your ram (if my arithmetic is
ok) *may* result in funny optimizer choices....

The thing to do next is examine EXPLAIN outputs for your queries, and
consider what smarter access plans might be possible (e.g. indexes,
partial indexes) and then maybe clever data re-orgizations (e.g.
clusters, trigger based summaries of aggregates) if you still need more
speed.

regards

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

[Josué Maldonado]

Hi Stephen,

Stephen Robert Norris wrote:

Speaking from long experimentation, you're much, much better off making
sure your indices and queries are optimal that messing around with
buffer space. Buffer space tuning might get you a few percent
performance once you pick a reasonable value; query tuning can get you
orders of magnitude.

I tunned my querys and all uses indexes and seems to be fast, but when
my client app acccess the pg data it seems a little slow. I installed
MSDE (mssql limited version) and copied the same data from pg to MSDE, I
was surprised cuz running the same query with the same data and MSDE
seems to be a little bit faster, after the changes in postgresql.conf
described in the previus message pg perfomance increased a little but
still there is no big difference against MSDE, considering hardware, pg
is in a real server (dual Xeon 2.4 Ghz, 2G RAM, 3 36G SCSI drives on a
RAID5) and the server is not in production, MSDE is in a AMD athlon 1Gh
with 256RAM y should expect better performace from pg. I wonder if ODBC
could be affect performance so high, my current ODBC driver is 7.03.02.

Thanks

--
Josué Maldonado.

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

[Stephen Robert Norris]

On Thu, 2003-11-20 at 02:06, Josué Maldonado wrote:

Hi Stephen,

Stephen Robert Norris wrote:

Speaking from long experimentation, you're much, much better off making
sure your indices and queries are optimal that messing around with
buffer space. Buffer space tuning might get you a few percent
performance once you pick a reasonable value; query tuning can get you
orders of magnitude.

I tunned my querys and all uses indexes and seems to be fast, but when
my client app acccess the pg data it seems a little slow. I installed
MSDE (mssql limited version) and copied the same data from pg to MSDE, I
was surprised cuz running the same query with the same data and MSDE
seems to be a little bit faster, after the changes in postgresql.conf
described in the previus message pg perfomance increased a little but
still there is no big difference against MSDE, considering hardware, pg
is in a real server (dual Xeon 2.4 Ghz, 2G RAM, 3 36G SCSI drives on a
RAID5) and the server is not in production, MSDE is in a AMD athlon 1Gh
with 256RAM y should expect better performace from pg. I wonder if ODBC
could be affect performance so high, my current ODBC driver is 7.03.02.

Thanks

I have never used ODBC so I don't know how much that will effect
performance. What are the schemata for the tables, and what does explain
analyze tell you about the query?

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

http://archives.postgresql.org

[Josué Maldonado]

Stephen,

This is the query code:
SELECT
(cmes(substr(epr_periodo,5,2))||'-'||substr(epr_periodo,3,2))::char(6)
AS hmes,
epr_periodo,
coalesce(epr_venta,0)::numeric(12,4) as epr_venta,
coalesce(epr_costo,0)::numeric(12,4) as epr_costo,
coalesce(epr_qty,0)::numeric(12,4) as epr_qty,
coalesce(epr_qty2,0)::numeric(12,4) as epr_qty2,
coalesce(epr_qty3,0)::numeric(12,4) as epr_qty3,
case when epr_qty=0 then 0 else
coalesce((epr_costo/epr_qty),0)::numeric(12,4)
end as cost_prom,
case when epr_qty=0 then 0 else
coalesce((epr_venta/epr_qty),0)::numeric(12,4)
end as prec_prom,
case when epr_costo=0 then 0 else
coalesce((((epr_venta/epr_costo)*100)-100),0)::numeric(12,4)
end as margen
FROM estprod WHERE pro_code = '1017' and epr_periodo >= '200211' and
epr_periodo <= '200311'

This is the explain:
Index Scan using idx_estx on estprod (cost=0.00..38.29 rows=9 width=67)
(actual time=0.52..1.64 rows=13 loops=1)
Index Cond: ((pro_code = '1017'::bpchar) AND (epr_periodo >=
'200211'::bpchar) AND (epr_periodo <= '200311'::bpchar))
Total runtime: 1.70 msec
(3 rows)
Table estprod is:
CREATE TABLE public.estprod
(
pk_estprod int4 DEFAULT nextval('sqestprod'::text),
product_fk int4,
epr_periodo char(6),
epr_venta numeric(12,4),
epr_costo numeric(12,4),
epr_qty numeric(12,4),
epr_venta2 numeric(12,4),
epr_costo2 numeric(12,4),
epr_qty2 numeric(12,4),
epr_venta3 numeric(12,4),
epr_costo3 numeric(12,4),
epr_qty3 numeric(12,4),
epr_ventax numeric(12,2),
pro_code char(4),
xmes varchar(6),
imes int4
) WITH OIDS;

and it contains 355,513 rows

Stephen Robert Norris wrote:

On Thu, 2003-11-20 at 02:06, Josué Maldonado wrote:

Hi Stephen,

Stephen Robert Norris wrote:

Speaking from long experimentation, you're much, much better off making
sure your indices and queries are optimal that messing around with
buffer space. Buffer space tuning might get you a few percent
performance once you pick a reasonable value; query tuning can get you
orders of magnitude.

I tunned my querys and all uses indexes and seems to be fast, but when
my client app acccess the pg data it seems a little slow. I installed
MSDE (mssql limited version) and copied the same data from pg to MSDE, I
was surprised cuz running the same query with the same data and MSDE
seems to be a little bit faster, after the changes in postgresql.conf
described in the previus message pg perfomance increased a little but
still there is no big difference against MSDE, considering hardware, pg
is in a real server (dual Xeon 2.4 Ghz, 2G RAM, 3 36G SCSI drives on a
RAID5) and the server is not in production, MSDE is in a AMD athlon 1Gh
with 256RAM y should expect better performace from pg. I wonder if ODBC
could be affect performance so high, my current ODBC driver is 7.03.02.

Thanks

I have never used ODBC so I don't know how much that will effect
performance. What are the schemata for the tables, and what does explain
analyze tell you about the query?

Stephen

--
Josué Maldonado.

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

[Stephen Robert Norris]

On Thu, 2003-11-20 at 10:20, Josué Maldonado wrote:

Stephen,

This is the query code:
SELECT
(cmes(substr(epr_periodo,5,2))||'-'||substr(epr_periodo,3,2))::char(6)
AS hmes,
epr_periodo,
coalesce(epr_venta,0)::numeric(12,4) as epr_venta,
coalesce(epr_costo,0)::numeric(12,4) as epr_costo,
coalesce(epr_qty,0)::numeric(12,4) as epr_qty,
coalesce(epr_qty2,0)::numeric(12,4) as epr_qty2,
coalesce(epr_qty3,0)::numeric(12,4) as epr_qty3,
case when epr_qty=0 then 0 else
coalesce((epr_costo/epr_qty),0)::numeric(12,4)
end as cost_prom,
case when epr_qty=0 then 0 else
coalesce((epr_venta/epr_qty),0)::numeric(12,4)
end as prec_prom,
case when epr_costo=0 then 0 else
coalesce((((epr_venta/epr_costo)*100)-100),0)::numeric(12,4)
end as margen
FROM estprod WHERE pro_code = '1017' and epr_periodo >= '200211' and
epr_periodo <= '200311'

This is the explain:
Index Scan using idx_estx on estprod (cost=0.00..38.29 rows=9 width=67)
(actual time=0.52..1.64 rows=13 loops=1)
Index Cond: ((pro_code = '1017'::bpchar) AND (epr_periodo >=
'200211'::bpchar) AND (epr_periodo <= '200311'::bpchar))
Total runtime: 1.70 msec
(3 rows)

This looks pretty good - 1.7ms is very quick for a query. If you're
seeing performance problems, it must be from the ODBC layer.

Stephen
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Stephen Robert Norris]

On Thu, 2003-11-20 at 02:06, Josué Maldonado wrote:

Hi Stephen,

Stephen Robert Norris wrote:

Speaking from long experimentation, you're much, much better off making
sure your indices and queries are optimal that messing around with
buffer space. Buffer space tuning might get you a few percent
performance once you pick a reasonable value; query tuning can get you
orders of magnitude.

I tunned my querys and all uses indexes and seems to be fast, but when
my client app acccess the pg data it seems a little slow. I installed
MSDE (mssql limited version) and copied the same data from pg to MSDE, I
was surprised cuz running the same query with the same data and MSDE
seems to be a little bit faster, after the changes in postgresql.conf
described in the previus message pg perfomance increased a little but
still there is no big difference against MSDE, considering hardware, pg
is in a real server (dual Xeon 2.4 Ghz, 2G RAM, 3 36G SCSI drives on a
RAID5) and the server is not in production, MSDE is in a AMD athlon 1Gh
with 256RAM y should expect better performace from pg. I wonder if ODBC
could be affect performance so high, my current ODBC driver is 7.03.02.

Thanks

I have never used ODBC so I don't know how much that will effect
performance. What are the schemata for the tables, and what does explain
analyze tell you about the query?

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

http://archives.postgresql.org

[Josué Maldonado]

Stephen,

This is the query code:
SELECT
(cmes(substr(epr_periodo,5,2))||'-'||substr(epr_periodo,3,2))::char(6)
AS hmes,
epr_periodo,
coalesce(epr_venta,0)::numeric(12,4) as epr_venta,
coalesce(epr_costo,0)::numeric(12,4) as epr_costo,
coalesce(epr_qty,0)::numeric(12,4) as epr_qty,
coalesce(epr_qty2,0)::numeric(12,4) as epr_qty2,
coalesce(epr_qty3,0)::numeric(12,4) as epr_qty3,
case when epr_qty=0 then 0 else
coalesce((epr_costo/epr_qty),0)::numeric(12,4)
end as cost_prom,
case when epr_qty=0 then 0 else
coalesce((epr_venta/epr_qty),0)::numeric(12,4)
end as prec_prom,
case when epr_costo=0 then 0 else
coalesce((((epr_venta/epr_costo)*100)-100),0)::numeric(12,4)
end as margen
FROM estprod WHERE pro_code = '1017' and epr_periodo >= '200211' and
epr_periodo <= '200311'

This is the explain:
Index Scan using idx_estx on estprod (cost=0.00..38.29 rows=9 width=67)
(actual time=0.52..1.64 rows=13 loops=1)
Index Cond: ((pro_code = '1017'::bpchar) AND (epr_periodo >=
'200211'::bpchar) AND (epr_periodo <= '200311'::bpchar))
Total runtime: 1.70 msec
(3 rows)
Table estprod is:
CREATE TABLE public.estprod
(
pk_estprod int4 DEFAULT nextval('sqestprod'::text),
product_fk int4,
epr_periodo char(6),
epr_venta numeric(12,4),
epr_costo numeric(12,4),
epr_qty numeric(12,4),
epr_venta2 numeric(12,4),
epr_costo2 numeric(12,4),
epr_qty2 numeric(12,4),
epr_venta3 numeric(12,4),
epr_costo3 numeric(12,4),
epr_qty3 numeric(12,4),
epr_ventax numeric(12,2),
pro_code char(4),
xmes varchar(6),
imes int4
) WITH OIDS;

and it contains 355,513 rows

Stephen Robert Norris wrote:

On Thu, 2003-11-20 at 02:06, Josué Maldonado wrote:

Hi Stephen,

Stephen Robert Norris wrote:

Speaking from long experimentation, you're much, much better off making
sure your indices and queries are optimal that messing around with
buffer space. Buffer space tuning might get you a few percent
performance once you pick a reasonable value; query tuning can get you
orders of magnitude.

I tunned my querys and all uses indexes and seems to be fast, but when
my client app acccess the pg data it seems a little slow. I installed
MSDE (mssql limited version) and copied the same data from pg to MSDE, I
was surprised cuz running the same query with the same data and MSDE
seems to be a little bit faster, after the changes in postgresql.conf
described in the previus message pg perfomance increased a little but
still there is no big difference against MSDE, considering hardware, pg
is in a real server (dual Xeon 2.4 Ghz, 2G RAM, 3 36G SCSI drives on a
RAID5) and the server is not in production, MSDE is in a AMD athlon 1Gh
with 256RAM y should expect better performace from pg. I wonder if ODBC
could be affect performance so high, my current ODBC driver is 7.03.02.

Thanks

I have never used ODBC so I don't know how much that will effect
performance. What are the schemata for the tables, and what does explain
analyze tell you about the query?

Stephen

--
Josué Maldonado.

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

[Stephen Robert Norris]

On Thu, 2003-11-20 at 10:20, Josué Maldonado wrote:

Stephen,

This is the query code:
SELECT
(cmes(substr(epr_periodo,5,2))||'-'||substr(epr_periodo,3,2))::char(6)
AS hmes,
epr_periodo,
coalesce(epr_venta,0)::numeric(12,4) as epr_venta,
coalesce(epr_costo,0)::numeric(12,4) as epr_costo,
coalesce(epr_qty,0)::numeric(12,4) as epr_qty,
coalesce(epr_qty2,0)::numeric(12,4) as epr_qty2,
coalesce(epr_qty3,0)::numeric(12,4) as epr_qty3,
case when epr_qty=0 then 0 else
coalesce((epr_costo/epr_qty),0)::numeric(12,4)
end as cost_prom,
case when epr_qty=0 then 0 else
coalesce((epr_venta/epr_qty),0)::numeric(12,4)
end as prec_prom,
case when epr_costo=0 then 0 else
coalesce((((epr_venta/epr_costo)*100)-100),0)::numeric(12,4)
end as margen
FROM estprod WHERE pro_code = '1017' and epr_periodo >= '200211' and
epr_periodo <= '200311'

This is the explain:
Index Scan using idx_estx on estprod (cost=0.00..38.29 rows=9 width=67)
(actual time=0.52..1.64 rows=13 loops=1)
Index Cond: ((pro_code = '1017'::bpchar) AND (epr_periodo >=
'200211'::bpchar) AND (epr_periodo <= '200311'::bpchar))
Total runtime: 1.70 msec
(3 rows)

This looks pretty good - 1.7ms is very quick for a query. If you're
seeing performance problems, it must be from the ODBC layer.

Stephen
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Mark Kirkwood]

Stephen Robert Norris wrote:

This looks pretty good - 1.7ms is very quick for a query. If you're
seeing performance problems, it must be from the ODBC layer.

I recall striking this sort of problem with old Oracle ODBC drivers - we
used to set "Pass Through" mode for all the queries. However, I am don't
know if the Pg driver supports it (or if it would actually help
significantly).

Might be worth trying out some other ODBC drivers for Pg. I am pretty
sure there are some, however I can recall the brands :-(

regards

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