Re: Insert speed question

Hello Shridhar,

El 02/06/2004 1:16 AM, Shridhar Daithankar en su mensaje escribio:

> I am not sure I understand. You could not insert? Why? Was there any problem
> with database? Can you use typical linux tools such as vmstat/top to locate
> the bottleneck?
>

I was unable to make the insert at that moment, after the changes to
postgresql.conf the speed increased, here is the explain:

dbmund=# explain analyze
dbmund-# insert into pk2
dbmund-# (pkd_stamp,pkd_fecha,
doctipo,'YYY',-1,-1,-1,-1,prod_no,impues,
dbmund(# pkd_docto,pkd_es,pkd_qtysold,pkd_qtyinv,
dbmund(# pkd_unidad,pkd_price,pkd_costo,pkd_saldo,
dbmund(# pkd_procode,pkd_custid,pkd_vendedor,pkd_tipprice,
dbmund(# pkd_totprice,pkd_estanulo,pkd_estmes,pkd_porcomision,
dbmund(# pkd_rutafk,pkd_provcode,pkd_depto,pkd_pk,
dbmund(# pkd_totcost,pkd_doctipo2,pkd_doctipo,fk_autorizacion,
dbmund(# fk_devolucion,pkd_udindexfk,pkd_unidadfk,pkd_prodno,
dbmund(# pkd_gravada,pkd_fraimp,pkd_imsove,pkd_clmayor,
dbmund(# pkd_cajanum,pkd_es)
dbmund-# select fkardex,facfec,facnum,es,tqtysold,
dbmund-# invqty,unidad,fprice,fcost,saldo,
dbmund-# substr(prod_no,8,4),codclie,who_sold,
dbmund-# pre_tipo,fprice*tqtysold,'U',dtos(fkardex),
dbmund-# por_comisi,'XXX',substr(prod_no,1,3),
dbmund-# substr(prod_no,5,2),OID,fcost*tqtysold,
dbmund-# doctipo,'YYY',-1,-1,-1,-1,prod_no,impues,
dbmund-# fra_imp,imsove,clmayor,cajanum,es
dbmund-# from hisventa
dbmund-# ;
ERROR:  column "pkd_es" specified more than once
dbmund=# explain analyze
dbmund-# insert into pk2
dbmund-# (pkd_stamp,pkd_fecha,
doctipo,'YYY',-1,-1,-1,-1,prod_no,impues,
dbmund(# pkd_docto,pkd_es,pkd_qtysold,pkd_qtyinv,
dbmund(# pkd_unidad,pkd_price,pkd_costo,pkd_saldo,
dbmund(# pkd_procode,pkd_custid,pkd_vendedor,pkd_tipprice,
dbmund(# pkd_totprice,pkd_estanulo,pkd_estmes,pkd_porcomision,
dbmund(# pkd_rutafk,pkd_provcode,pkd_depto,pkd_pk,
dbmund(# pkd_totcost,pkd_doctipo2,pkd_doctipo,fk_autorizacion,
dbmund(# fk_devolucion,pkd_udindexfk,pkd_unidadfk,pkd_prodno,
dbmund(# pkd_gravada,pkd_fraimp,pkd_imsove,pkd_clmayor,
dbmund(# pkd_cajanum)
dbmund-# select fkardex,facfec,facnum,es,tqtysold,
dbmund-# invqty,unidad,fprice,fcost,saldo,
dbmund-# substr(prod_no,8,4),codclie,who_sold,
dbmund-# pre_tipo,fprice*tqtysold,'U',dtos(fkardex),
dbmund-# por_comisi,'XXX',substr(prod_no,1,3),
dbmund-# substr(prod_no,5,2),OID,fcost*tqtysold,
dbmund-# doctipo,'YYY',-1,-1,-1,-1,prod_no,impues,
dbmund-# fra_imp,imsove,clmayor,cajanum
dbmund-# from hisventa;
                                                         QUERY PLAN


---------------------------------------------------------------------------------------------------------------------------
  Seq Scan on hisventa  (cost=0.00..633607.24 rows=4882546 width=149)
(actual time=26.647..363517.935 rows=4882546 loops=1)
  Total runtime: 1042927.167 ms
(2 rows)

dbmund=#


>
>>Did some changes to postgresql.conf according the tuning guide:
>>tcpip_socket = true
>>max_connections = 28
>>shared_buffers = 32768          # min max_connections*2 or 16, 8KB each
>>max_fsm_relations = 500     # min 10, fsm is free space map, ~40 bytes
>>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
>>vacuum_mem = 419430             # min 1024, size in KB
>>checkpoint_segments = 10
>>effective_cache_size = 819200   # typically 8KB each
>
>
> OK, I would say the parameters are still slightly oversized but there is no
> perfect set of parameters. You still might have to tune it according to your
> usual workload.
>

As I said before the server is not yet in production, the expected
connections are 80-100 in normal day, the users tasks in the system
affects the following areas: inventory, sales, customers, banks, and
accounting basically, I know there is no rule for tuning but I'll
aprecciate your comment about the parameters for such scenario.

> I would ask the question otherway round. What is the level of performance you
> are looking at for your current workload. By how much this performance is
> worse than your expectation?

Since I have not tested the server with the production workload yet,
maybe my perpception of performance is not rigth focused, basically my
expectation is database must be faster than the current old legacy
Foxpro system.

Thanks,


--
Sinceramente,
Josué Maldonado.
"La monogamia es como estar obligado a comer papas fritas todos los
dias." -- Henry Miller. (1891-1980) Escritor estadounidense.