Re: ofbiz database connection , minerva ???

Wed, 04 Oct 2006 15:02:37 -0700 

Tibor,
50 simultaneous users placing 1000 orders at the same time? We should all be so lucky. 


This does sound like a problem I used to have when we were running on  
a server which could not handle the capacity.  Basically postgresql  
got slow with all the requests, creating a deadlock, and thus the  
problems.  So I do suspect the problem is with postgresql and not  
with ofbiz or the transaction manager.  If you want to "prove" it  
scientifically, maybe you can have postgresql log all the SQL  
statements and then try feeding it just the SQL statements to see  
what happens.



Unfortunately I don't know PostgreSQL nearly well enough to recommend  
an "ideal" configuration.  Craig and Matthew at Contegix are very  
knowledgeable, and I've basically relied on them for all the database  
and server configuration stuff.


Good luck.  Sorry I'm not more helpful.

Si


On Oct 4, 2006, at 9:58 AM, tibor katelbach wrote:


Hi Si
Thanks for your answer.

we are going soon into production so this subject is quite boyling  
hot.




I think the problem you are experiencing may be more related to
PostgreSQL than to the transaction manager layer of opentaps and
OFBiz.  Even OFBiz 3.0 and 3.1, which are a year older than opentaps
0.8.3, could be scaled up with the right configuration of the
database.



would you have an optimum Postgres configuration sample by any  
chance I

could see ?


At some volume of connections, though, PostgreSQL would

have to be clustered.



our limit amount of users 50 simultaneous with 1000 orders

that's when the system starts slowing down (nice numbers :-) but we  
loose

about 10% of orders.

Have you checked whether PostgreSQL is still

running successfully?




well I couldn't say if it's optimal, but we do have good results  
put appart

the benchmarking and load testing.


but when we load test, we get frequent deadlocks (coming from  
XAConnection

or Postgres DB)

as you can see in the logs below this is quite frightning "Process  
7948

waits for ShareLock on transaction 5856857; blocked by process 7966."
on

SQL statement "SELECT 1 FROM ONLY  
"public"."contact_mech_purpose_type" x
WHERE "contact_mech_purpose_type_id" = $1 FOR UPDATE OF x" before  
insert

(from logs below)

I would love an clear descritption of what is going on behind this ?
do you know of a tool to debug PostGres ? and it's connections ?

do you know a tool to follow Minerva connections ? other than the  
Verbose ?


Thanks a lot for any help that can help us go forward cause we are
stagnating a bit.
Regards
Tibor

a few of our Postgres logs
2006-09-29 17:32:57 CEST 172.29.7.30(44627) PROSODIE_ECOMMERCE_PROD
production INSERT waitingCONTEXT:  SQL statement "SELECT 1 FROM ONLY
"public"."geo" x WHERE "geo_id" = $1 FOR UPDATE OF x"
2006-09-29 17:32:57 CEST 172.29.7.30(44627) PROSODIE_ECOMMERCE_PROD
production INSERT waitingSTATEMENT:  INSERT INTO
public.ORDER_ADJUSTMENT(ORDER_ADJUSTMENT_ID, ORDER_ADJUSTMENT_TYPE_ID,
ORDER_ID, ORDER_ITEM_SEQ_ID,
SHIP_GROUP_SEQ_ID, COMMENTS, DESCRIPTION, AMOUNT, AMOUNT_PER_QUANTITY,
PERCENTAGE, PRODUCT_PROMO_ID, PRODUCT_PROMO_RULE_ID,

PRODUCT_PROMO_ACTION_SEQ_ID, PRODUCT_FEATURE_ID,  
CORRESPONDING_PRODUCT_ID,

SOURCE_REFERENCE_ID, SOURCE_PERCENTAGE, CUSTOMER_REFERENCE_ID,
PRIMARY_GEO_ID, SECONDARY_GEO_ID, EXEMPT_AMOUNT, TAX_AUTH_GEO_ID,
TAX_AUTH_PARTY_ID, OVERRIDE_GL_ACCOUNT_ID, INCLUDE_IN_TAX,
INCLUDE_IN_SHIPPING, CREATED_DATE, CREATED_BY_USER_LOGIN,

LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP,  
CREATED_TX_STAMP)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13,  
$14, $15,
$16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28,  
$29, $30,

$31, $32)
2006-09-29 17:33:00 CEST 172.29.7.30(44659) PROSODIE_ECOMMERCE_PROD
production INSERT waitingERROR:  deadlock detected
2006-09-29 17:33:00 CEST 172.29.7.30(44659) PROSODIE_ECOMMERCE_PROD
production INSERT waitingDETAIL:  Process 7983 waits for ShareLock on
transaction 5856663; blocked by process 7898.
   Process 7898 waits for ShareLock on transaction 5856853; blocked by
process 7983.
2006-09-29 17:33:00 CEST 172.29.7.30(44659) PROSODIE_ECOMMERCE_PROD
production INSERT waitingCONTEXT:  SQL statement "SELECT 1 FROM ONLY
"public"."geo" x WHERE "geo_id" = $1 FOR UPDATE OF x"
2006-09-29 17:33:00 CEST 172.29.7.30(44659) PROSODIE_ECOMMERCE_PROD
production INSERT waitingSTATEMENT:  INSERT INTO
public.POSTAL_ADDRESS(CONTACT_MECH_ID, PERSONAL_TITLE, TO_NAME,
ATTN_NAME, ADDRESS1, ADDRESS2,
DIRECTIONS, CITY, POSTAL_CODE, POSTAL_CODE_EXT, COUNTRY_GEO_ID,

STATE_PROVINCE_GEO_ID, POSTAL_CODE_GEO_ID, PHONE_NUMBER, EMAIL,  
COMMENTS,
LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP,  
CREATED_TX_STAMP)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13,  
$14, $15,

$16, $17, $18, $19, $20)
2006-09-29 17:33:03 CEST 172.29.7.30(44574) PROSODIE_ECOMMERCE_PROD
production INSERT waitingERROR:  deadlock detected
2006-09-29 17:33:03 CEST 172.29.7.30(44574) PROSODIE_ECOMMERCE_PROD
production INSERT waitingDETAIL:  Process 7898 waits for ShareLock on
transaction 5856901; blocked by process 7874.
   Process 7874 waits for ShareLock on transaction 5856663; blocked by
process 7898.
2006-09-29 17:33:03 CEST 172.29.7.30(44574) PROSODIE_ECOMMERCE_PROD
production INSERT waitingCONTEXT:  SQL statement "SELECT 1 FROM ONLY

"public"."payment_method_type" x WHERE "payment_method_type_id" =  
$1 FOR

UPDATE OF x"
2006-09-29 17:33:03 CEST 172.29.7.30(44574) PROSODIE_ECOMMERCE_PROD
production INSERT waitingSTATEMENT:  INSERT INTO

public.ORDER_PAYMENT_PREFERENCE (ORDER_PAYMENT_PREFERENCE_ID,  
ORDER_ID,
PAYMENT_METHOD_TYPE_ID, PAYMENT_METHOD_ID, SECURITY_CODE,  
PRESENT_FLAG,

OVERFLOW_FLAG, MAX_AMOUNT, PROCESS_ATTEMPT, BILLING_POSTAL_CODE,
MANUAL_AUTH_CODE, MANUAL_REF_NUM, STATUS_ID, CREATED_DATE,
CREATED_BY_USER_LOGIN, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP,

CREATED_STAMP, CREATED_TX_STAMP) VALUES ($1, $2, $3, $4, $5, $6,  
$7, $8, $9,

$10, $11, $12, $13, $14, $15, $16, $17, $18, $19)
2006-09-29 17:33:06 CEST 172.29.7.30(44550) PROSODIE_ECOMMERCE_PROD
production INSERT waitingERROR:  deadlock detected
2006-09-29 17:33:06 CEST 172.29.7.30(44550) PROSODIE_ECOMMERCE_PROD
production INSERT waitingDETAIL:  Process 7874 waits for ShareLock on
transaction 5856857; blocked by process 7966.
   Process 7966 waits for ShareLock on transaction 5856901; blocked by
process 7874.
2006-09-29 17:33:06 CEST 172.29.7.30(44550) PROSODIE_ECOMMERCE_PROD
production INSERT waitingCONTEXT:  SQL statement "SELECT 1 FROM ONLY

"public"."contact_mech_purpose_type" x WHERE  
"contact_mech_purpose_type_id"

= $1 FOR UPDATE OF x"
2006-09-29 17:33:06 CEST 172.29.7.30(44550) PROSODIE_ECOMMERCE_PROD
production INSERT waitingSTATEMENT:  INSERT INTO
public.PARTY_CONTACT_MECH_PURPOSE (PARTY_ID, CONTACT_MECH_ID,

CONTACT_MECH_PURPOSE_TYPE_ID, FROM_DATE, THRU_DATE,  
LAST_UPDATED_STAMP,
LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP) VALUES ($1,  
$2, $3,

$4, $5, $6, $7, $8, $9)
2006-09-29 17:33:09 CEST 172.29.7.30(44624) PROSODIE_ECOMMERCE_PROD
production INSERT waitingERROR:  deadlock detected
2006-09-29 17:33:09 CEST 172.29.7.30(44624) PROSODIE_ECOMMERCE_PROD
production INSERT waitingDETAIL:  Process 7948 waits for ShareLock on
transaction 5856857; blocked by process 7966.
   Process 7966 waits for ShareLock on transaction 5856900; blocked by
process 7948.
2006-09-29 17:33:09 CEST 172.29.7.30(44624) PROSODIE_ECOMMERCE_PROD
production INSERT waitingCONTEXT:  SQL statement "SELECT 1 FROM ONLY
"public"."geo" x WHERE "geo_id" = $1 FOR UPDATE OF x"
2006-09-29 17:33:09 CEST 172.29.7.30(44624) PROSODIE_ECOMMERCE_PROD
production INSERT waitingSTATEMENT:  INSERT INTO
public.POSTAL_ADDRESS(CONTACT_MECH_ID, PERSONAL_TITLE, TO_NAME,
ATTN_NAME, ADDRESS1, ADDRESS2,
DIRECTIONS, CITY, POSTAL_CODE, POSTAL_CODE_EXT, COUNTRY_GEO_ID,

STATE_PROVINCE_GEO_ID, POSTAL_CODE_GEO_ID, PHONE_NUMBER, EMAIL,  
COMMENTS,
LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP,  
CREATED_TX_STAMP)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13,  
$14, $15,

$16, $17, $18, $19, $20)
2006-09-29 17:33:12 CEST 172.29.7.30(44642) PROSODIE_ECOMMERCE_PROD
production INSERT waitingERROR:  deadlock detected
2006-09-29 17:33:12 CEST 172.29.7.30(44642) PROSODIE_ECOMMERCE_PROD
production INSERT waitingDETAIL:  Process 7966 waits for ShareLock on
transaction 5856987; blocked by process 7951.
   Process 7951 waits for ShareLock on transaction 5856857; blocked by
process 7966.
2006-09-29 17:33:12 CEST 172.29.7.30(44642) PROSODIE_ECOMMERCE_PROD
production INSERT waitingCONTEXT:  SQL statement "SELECT 1 FROM ONLY

"public"."payment_method_type" x WHERE "payment_method_type_id" =  
$1 FOR

UPDATE OF x"
2006-09-29 17:33:12 CEST 172.29.7.30(44642) PROSODIE_ECOMMERCE_PROD
production INSERT waitingSTATEMENT:  INSERT INTO

public.ORDER_PAYMENT_PREFERENCE (ORDER_PAYMENT_PREFERENCE_ID,  
ORDER_ID,
PAYMENT_METHOD_TYPE_ID, PAYMENT_METHOD_ID, SECURITY_CODE,  
PRESENT_FLAG,

OVERFLOW_FLAG, MAX_AMOUNT, PROCESS_ATTEMPT, BILLING_POSTAL_CODE,
MANUAL_AUTH_CODE, MANUAL_REF_NUM, STATUS_ID, CREATED_DATE,
CREATED_BY_USER_LOGIN, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP,

CREATED_STAMP, CREATED_TX_STAMP) VALUES ($1, $2, $3, $4, $5, $6,  
$7, $8, $9,

$10, $11, $12, $13, $14, $15, $16, $17, $18, $19)


Best Regards,

Si
[EMAIL PROTECTED]

























					Reply via email to