MySQL Bugs: #69434: com.mysql.jdbc.AbandonedConnectionCleanupThread slowing down load of data

Bug #69434	com.mysql.jdbc.AbandonedConnectionCleanupThread slowing down load of data
Submitted:	8 Jun 2013 20:44	Modified:	18 Jul 2013 16:31
Reporter:	John Byrne	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.1.25	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
I'm loading data with a simple query:
select * from imagemapdata;

The problem isn't the SQL however.  It's with the driver loading I think.  I've profiled the program with JVisualVM and see that com.mysql.jdbc.AbandonedConnectionCleanupThread.run is taking 21 seconds.

At first I wasn't closing my connections.  After modifying my code and restarting both my computer and the server the problem still exists.  

How to repeat:
Run the code without the conn.close() method for about 40 times.  Then put the conn.close() statement back in and restart the server.

The code is pretty simple.  Run attached code.  I can give you the image I'm drawing to but can't attach an image to this email.  I don't know how to package up my database.  If you can tell me how I will.  Also, if you want the profiling data file I can give that to you too.  But you'll need to ask me for it as I'll have to figure out how to get that to you.

Thanks,
John Byrne

Java file to load database and run query.

Attachment: TestDB.java (text/plain), 1.34 KiB.

Profiling of TestDB Run

Attachment: snapshot-1370724889509.nps (application/octet-stream, text), 5.94 KiB.

I've simplified and taken out just the code to connect to the database.  (without the drawing code)  If there is some way to give you my database, let me know.  I don't know how to do that.

Thanks,
John

John Byrne,

Thank you for this bug report.

Please provide the following information so that we can try to reproduce the problem:

1. MySQL version you are connecting to?
2. MySQL my.cnf file (please remove any personal information you may have there defore sending).
3. Table imagemapdata DDL.

Best regards,

I'm using MySQL Version 5.6

This is what came from the MySQL client under some random directory.
MySQL --version
output:
mysql  Ver 14.14 Distrib 5.6.11, for Win64 (x86_64)

configuration file

Attachment: my.ini (application/octet-stream, text), 13.90 KiB.

I've attached the my.ini file from c:\programdata\mysql\mysql server 5.6

However, I don't know how or what the imagemapdata ddl is.  Can you give me directions on how to find it?  (I also can't find it on the internet).

Thanks,
John Byrne

I've attached the imagemapdata.frm file.  This is one of my views.  I hope this is what you wanted.  I don't really know where to get what you are asking for if it's not.  Please explain what you need in more detail.

Thanks,
John Byrne

Hi John Byrne,

Please issue this command in MySQL Command-Line Tool (mysql) and paste here the results : "USE <your_db>; SHOW CREATE TABLE imagemapdata;"

** replace <your_db> accordingly.

Find related information here: http://dev.mysql.com/doc/refman/5.6/en/mysql.html, and here: http://dev.mysql.com/doc/refman/5.6/en/show-create-table.html.

Thank you,

Here's the output from the MySQL client for the imagemapdata view.
Thanks,
John Byrne
==============================================================================

mysql> use voting;
Database changed
mysql> show create table imagmapdata;
ERROR 1146 (42S02): Table 'voting.imagmapdata' doesn't exist
mysql> show create table imagemapdata;
+--------------+----------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------+----------------------+----------------------+
| View         | Create View

                                | character_set_client | collation_connection |
+--------------+----------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------+----------------------+----------------------+
| imagemapdata | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECUR
ITY DEFINER VIEW `imagemapdata` AS select `longlat`.`id` AS `id`,`housescores`.`
Score` AS `Score`,`longlat`.`longitude` AS `longitude`,`longlat`.`latitude` AS `
latitude` from (`housescores` join `longlat` on((`housescores`.`szMailAddress1`
= `longlat`.`szMailAddress1`))) | utf8                 | utf8_general_ci      |
+--------------+----------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------+----------------------+----------------------+
1 row in set (0.21 sec)

mysql>

Hi John Byrne,

Could you provide the same information for tables `housescores` and `longlat`?

By the way, this problem occurs only when retrieving data from this query or it happens with all/some other queries?

Thank you,

mysql> show create table housescores;
+-------------+-----------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------+
| Table       | Create Table

             |
+-------------+-----------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------+
| housescores | CREATE TABLE `housescores` (
  `Score` int(11) DEFAULT NULL,
  `szMailAddress1` varchar(40) DEFAULT NULL,
  `City` varchar(10) NOT NULL DEFAULT 'Sunnyvale',
  `State` varchar(3) NOT NULL DEFAULT 'CA'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------------+-----------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------+
1 row in set (0.01 sec)

mysql> show create table longlat;
+---------+---------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------+
| Table   | Create Table

                     |
+---------+---------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------+
| longlat | CREATE TABLE `longlat` (
  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `szMailAddress1` varchar(40) DEFAULT NULL,
  `city` varchar(40) DEFAULT NULL,
  `state` char(2) DEFAULT NULL,
  `longitude` double DEFAULT NULL,
  `latitude` double DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1801 DEFAULT CHARSET=utf8 |
+---------+---------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------+
1 row in set (0.02 sec)

mysql>

I don't think this has anything to do with the SQL query execution.  I completely took out the statements that created the statement and executed it and the AbandonedConnectionCleanupThread still takes 6 seconds to run.  I have attached the profiling as WithoutSQLExecution.nps  I have also attached the code.

Profiling of TestDB Run without creation and execution of SQL statement

Attachment: WithoutSQLExecution.nps (application/octet-stream, text), 5.52 KiB.

Source that loads the database driver and does nothing.

Attachment: TestDB.java (text/plain), 1.36 KiB.

Thank you for the feedback.

Please run same statement in MySQL command line client and send us how much time it spend on executing this query like in this example:

mysql> select * from imagemapdata;
....
... rows in set (11.00 sec)

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".