[jboss-user] [JBoss Messaging] - Performance issue for persistent manager of JBoss Messaging

[gary.c.chen]

I am very confused by the internal behavior of persistent manager of jboss messaging with ms sql server.

I use event tracer of ms sql server to capture sql statements sent out from one jms message comes into jms queue to consumed by consumer.

The following sql statements shows one jms message persistent can cause about twenty insert statement to be executed, which cause excessive disk I/O activity.

Does anyone can help me explain why did it happen? And What's the solution to obtain higher performance?

Best Regards.


  | SET IMPLICIT_TRANSACTIONS ON
  | go
  | exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 2, 0, NULL, N'C', 39832854870163456, NULL, 0, 0
  | go
  | IF @@TRANCOUNT > 0 ROLLBACK TRAN
  | go
  | exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 2, 0, NULL, N'C', 39832854886809601, NULL, 0, 0
  | go
  | IF @@TRANCOUNT > 0 ROLLBACK TRAN
  | go
  | exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 2, 0, NULL, N'C', 39832854899261442, NULL, 0, 0
  | go
  | IF @@TRANCOUNT > 0 ROLLBACK TRAN
  | go
  | exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 2, 0, NULL, N'C', 39832854900899843, NULL, 0, 0
  | go
  | IF @@TRANCOUNT > 0 ROLLBACK TRAN
  | go
  | exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 2, 0, NULL, N'C', 39832854907125764, NULL, 0, 0
  | go
  | IF @@TRANCOUNT > 0 ROLLBACK TRAN
  | go
  | exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 2, 0, NULL, N'C', 39832854909747205, NULL, 0, 0
  | go
  | IF @@TRANCOUNT > 0 ROLLBACK TRAN
  | go
  | exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 2, 0, NULL, N'C', 39832854918266886, NULL, 0, 0
  | go
  | IF @@TRANCOUNT > 0 ROLLBACK TRAN
  | go
  | exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 2, 0, NULL, N'C', 39832854922854407, NULL, 0, 0
  | go
  | IF @@TRANCOUNT > 0 ROLLBACK TRAN
  | go
  | exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 2, 0, NULL, N'C', 39832854933012488, NULL, 0, 0
  | go
  | IF @@TRANCOUNT > 0 ROLLBACK TRAN
  | go
  | exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 2, 0, NULL, N'C', 39832854943825929, NULL, 0, 0
  | go
  | IF @@TRANCOUNT > 0 ROLLBACK TRAN
  | go
  | exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 2, 0, NULL, N'C', 39832854953984010, NULL, 0, 0
  | go
  | IF @@TRANCOUNT > 0 ROLLBACK TRAN
  | go
  | exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 2, 0, NULL, N'C', 39832854960537611, NULL, 0, 0
  | go
  | IF @@TRANCOUNT > 0 ROLLBACK TRAN
  | go
  | exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 2, 0, NULL, N'C', 39832854969057292, NULL, 0, 0
  | go
  | IF @@TRANCOUNT > 0 ROLLBACK TRAN
  | go
  | exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 2, 0, NULL, N'C', 39832854970368013, NULL, 0, 0
  | go
  | IF @@TRANCOUNT > 0 ROLLBACK TRAN
  | go
  | exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 2, 0, NULL, N'C', 39832854978560014, NULL, 0, 0
  | go
  | IF @@TRANCOUNT > 0 ROLLBACK TRAN
  | go
  | exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 2, 0, NULL, N'C', 39832854986424335, NULL, 0, 0
  | go
  | IF @@TRANCOUNT > 0 ROLLBACK TRAN
  | go
  | exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 2, 0, NULL, N'C', 39832854998220816, NULL, 0, 0
  | go
  | IF @@TRANCOUNT > 0 ROLLBACK TRAN
  | go
  | exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 2, 0, NULL, N'C', 39832855000514577, NULL, 0, 0
  | go
  | IF @@TRANCOUNT > 0 ROLLBACK TRAN
  | go
  | exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 2, 0, NULL, N'C', 39832855007395858, NULL, 0, 0
  | go
  | IF @@TRANCOUNT > 0 ROLLBACK TRAN
  | go
  | exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 2, 0, NULL, N'C', 39832855020830739, NULL, 0, 0
  | go
  | IF @@TRANCOUNT > 0 ROLLBACK TRAN
  | go
  | exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 2, 0, NULL, N'C', 39832855023779860, NULL, 0, 0
  | go
  | IF @@TRANCOUNT > 0 ROLLBACK TRAN
  | go
  | exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 2, 0, NULL, N'C', 39832855029022741, NULL, 0, 0
  | go
  | IF @@TRANCOUNT > 0 ROLLBACK TRAN
  | go
  | exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 2, 0, NULL, N'C', 39832855034920982, NULL, 0, 0
  | go
  | IF @@TRANCOUNT > 0 ROLLBACK TRAN
  | go
  | exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 2, 0, NULL, N'C', 39832855045406743, NULL, 0, 0
  | go
  | IF @@TRANCOUNT > 0 ROLLBACK TRAN
  | go
  | exec sp_executesql N'INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)', N'@P1 bigint , at P2 bigint , at P3 bigint , at P4 nvarchar(4000) , at P5 bigint , at P6 bigint , at P7 int , at P8 bigint ', 2, 0, NULL, N'C', 39832855048683544, NULL, 0, 0
  | go
  | IF @@TRANCOUNT > 0 ROLLBACK TRAN
  | go
  | 

View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4163548#4163548

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=4163548