How to improve JDBC performance

[fuyou]

hi all

My program insert some data to MySQL Server,becasue some reason ,can't
batch insert.
In my benchmarks,the bottleneck is jdbc,how to improve performance?
see the jstack



"recvMsgWorkTPConfig-11-thread-20" prio=10 tid=0x00002aaac0445000
nid=0x399b runnable [0x000000004af94000]
java.lang.Thread.State: RUNNABLE
at java.util.zip.CRC32.updateBytes(Native Method)
at java.util.zip.CRC32.update(CRC32.java:74)
//omission ...
"recvMsgWorkTPConfig-11-thread-6" prio=10 tid=0x000000000bcc4000 nid=0x3987
runnable [0x0000000049b7f000]
java.lang.Thread.State: RUNNABLE
at java.net.SocketInputStream.socketRead0(Native Method)
at java.net.SocketInputStream.read(SocketInputStream.java:152)
at java.net.SocketInputStream.read(SocketInputStream.java:122)




show create talbe :


CREATE TABLE `test` (
`MESSAGE_ID` varchar(32) NOT NULL,
`COMMITTED` varchar(1) NOT NULL,
`GMT_CREATE` datetime NOT NULL,
`GMT_LAST_DELIVERY` datetime NOT NULL,
`DELIVERY_COUNT` smallint(6) NOT NULL,
`FLAG` int(11) NOT NULL,
`TIME_TO_LIVE` int(11) NOT NULL,
`DLQ_TIME` int(11) NOT NULL,
`POST_TIME_OUT` int(11) NOT NULL,
`BORN_TIME` bigint(20) NOT NULL,
`GROUP_ID` varchar(64) NOT NULL,
`TOPIC` varchar(32) NOT NULL,
`MESSAGE_TYPE` varchar(64) NOT NULL,
`HOST_NAME` varchar(32) NOT NULL,
`SERVER_TAG` varchar(32) NOT NULL,
`NEXT_DELIVER_TIME` bigint(20) DEFAULT NULL,
`FAILED_TARGET` text,
`SYSTEM_DEFINED_PROPERTIES` varchar(4000) DEFAULT NULL,
`USER_DEFINED_PROPERTIES` varchar(4000) DEFAULT NULL,
`CONTEXT_DATA` varchar(4000) DEFAULT NULL,
`BYTES_BODY` blob NOT NULL,
`ID` bigint(64) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`ID`),
KEY `SERVER_TOPIC_MESSAGE_TYPE_NEXT_DELIVER`
(`SERVER_TAG`,`NEXT_DELIVER_TIME`,`TOPIC`,`MESSAGE_TYPE`),
KEY `idx_create` (`GMT_CREATE`),
KEY `IDX_MESSAGE_ID` (`MESSAGE_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2806173 DEFAULT CHARSET=utf8;

[Mark Matthews]

Post by fuyou
hi all
My program insert some data to MySQL Server,becasue some reason ,can't
batch insert.
In my benchmarks,the bottleneck is jdbc,how to improve performance?
see the jstack
"recvMsgWorkTPConfig-11-thread-20" prio=10 tid=0x00002aaac0445000
nid=0x399b runnable [0x000000004af94000]
java.lang.Thread.State: RUNNABLE
at java.util.zip.CRC32.updateBytes(Native Method)
at java.util.zip.CRC32.update(CRC32.java:74)
//omission ...
"recvMsgWorkTPConfig-11-thread-6" prio=10 tid=0x000000000bcc4000 nid=0x3987
runnable [0x0000000049b7f000]
java.lang.Thread.State: RUNNABLE
at java.net.SocketInputStream.socketRead0(Native Method)
at java.net.SocketInputStream.read(SocketInputStream.java:152)
at java.net.SocketInputStream.read(SocketInputStream.java:122)

Hi,

I'm assuming that's from the driver, I can't tell because the stack has
been cut off. If so, that's network latency, which is time, not
cpu-based. Only way to make that better if mysqld is configured to do
i/o as fast as it can (a topic for ***@lists.mysql.com), is to batch
to cut down on round-trips, so you'll have to get that working.

-Mark

[Mark Matthews]

Post by Mark Matthews
Hi,
I'm assuming that's from the driver, I can't tell because the
stack has been cut off. If so, that's network latency, which is
time, not cpu-based. Only way to make that better if mysqld is
configured to do i/o as fast as it can (a topic for
to cut down on round-trips, so you'll have to get that working.
-Mark
--
--
MySQL Java Mailing List
For list archives: http://lists.mysql.com/java
To unsubscribe: http://lists.mysql.com/java

Hi,

That's all of the threads, but it's not any full stacks from an
"interesting" thread. However, it looks like you have a lot of threads
waiting on reading results from the database. Because we don't have the
full stack of any of those threads that are waiting on reading from a
socket, we do not know if they are waiting on the results of DML, or if
there's SELECTs going on there, anybody would just be guessing. Can you
characterize the workload at all? What do you expect to happen
performance-wise, and what do you observe? What do various related
metrics on your database server look like? Is the I/O subsystem busy?
idle? overloaded? Are you attempting to insert single rows into a single
table from many, many threads? Is each operation done as auto-commit, or
are multiple statements bracketed by transactions?

Also, please make sure to include the list in your replies.

Thanks!

-Mark