|
|
Hi,
Not sure this post completely belongs here, but I did some MySQL slow query logging and was surprised with some results.
The first query is especially perplexing, I am not a SQL expert but it shouldnt take 51secs for this query for sure. Can anyone explain why this query is so bad? Its actually causing my import script to fail with "Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
2008/10/30 17:23:39) : at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2975)
2008/11/17 16:36:17 - Upsert ProductCategoryMember.0 - ERROR (version 3.1.0, build 826 from 2008/10/30 17:23:39) : at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1600)"
Count: 1 Time=51.00s (51s) Lock=0.00s (0s) Rows=0.0 (0), ofbiz[ofbiz]@XXXX
INSERT INTO PRODUCT_CATEGORY_MEMBER (PRODUCT_ID, PRODUCT_CATEGORY_ID, FROM_DATE, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP) VALUES ( 'S', 'S', 'S', 'S', 'S', 'S', 'S')
Count: 1 Time=19.00s (19s) Lock=0.00s (0s) Rows=0.0 (0), ofbiz[ofbiz]@XXX
UPDATE PRODUCT_PRICE
SET PRICE = N.N
, FROM_DATE = 'S'
, CREATED_BY_USER_LOGIN = 'S'
, LAST_UPDATED_TX_STAMP = 'S'
WHERE PRODUCT_ID LIKE 'S' AND PRODUCT_PRICE_TYPE_ID = 'S' AND PRODUCT_PRICE_PURPOSE_ID = 'S'
Count: 1 Time=18.00s (18s) Lock=0.00s (0s) Rows=0.0 (0), ofbiz[ofbiz]@XXX
UPDATE PRODUCT_PRICE
SET PRICE = N
, FROM_DATE = 'S'
, CREATED_BY_USER_LOGIN = 'S'
, LAST_UPDATED_TX_STAMP = 'S'
WHERE PRODUCT_ID LIKE 'S' AND PRODUCT_PRICE_TYPE_ID = 'S' AND PRODUCT_PRICE_PURPOSE_ID = 'S'
Count: 2 Time=13.50s (27s) Lock=0.00s (0s) Rows=0.0 (0), ofbiz[ofbiz]@XXX
UPDATE DATA_RESOURCE
SET MIME_TYPE_ID = 'S'
, LAST_UPDATED_TX_STAMP = 'S'
WHERE DATA_RESOURCE_ID LIKE 'S'
Count: 2 Time=0.00s (0s) Lock=0.00s (0s) Rows=796.0 (1592), ofbiz[ofbiz]@XXX
SELECT PARTY_ID, GROUP_NAME FROM PARTY_GROUP
Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=724.0 (724), ofbiz[ofbiz]@XXX
SELECT PRODUCT_CATEGORY_ID, CATEGORY_NAME FROM
PRODUCT_CATEGORY
|
Locked
