Thread: Tunning postgresql
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.
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
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
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.
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
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.
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
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