Thread: Tunning postgresql

Tunning postgresql

From
Josué Maldonado
Date:
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.



Re: Tunning postgresql

From
Stephen Robert Norris
Date:
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


Re: Tunning postgresql

From
Mark Kirkwood
Date:

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


Re: Tunning postgresql

From
Josué Maldonado
Date:
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.



Re: Tunning postgresql

From
Stephen Robert Norris
Date:
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


Re: Tunning postgresql

From
Josué Maldonado
Date:
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.



Re: Tunning postgresql

From
Stephen Robert Norris
Date:
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


Re: Tunning postgresql

From
Mark Kirkwood
Date:
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