You are viewing a plain text version of this content. The canonical link for it is here.
Posted to solr-user@lucene.apache.org by Blargy <zm...@hotmail.com> on 2010/05/14 16:56:31 UTC
Recommended MySQL JDBC driver
Which driver is the "best" for use with solr?
I am currently using mysql-connector-java-5.1.12-bin.jar in my production
setting. However I recently tried downgrading and did some quick indexing
using mysql-connector-java-5.0.8-bin.jar and I close to a 2x improvement in
speed!!! Unfortunately I kept getting the following error using the 5.0.8
version:
"Caused by: com.mysql.jdbc.CommunicationsException: The last communications
with the server was 474 seconds ago, which is longer than the server
configured value of 'wait_timeout'. You should consider either expiring
and/or testing connection validity before use in your application,
increasing the server configured values for client timeouts, or using the
Connector/J connection property 'autoReconnect=true' to avoid this problem."
I tried setting the autoReconnect="true" in my datasource configuration but
I keep getting the same error. Any ideas?
--
View this message in context: http://lucene.472066.n3.nabble.com/Recommended-MySQL-JDBC-driver-tp817458p817458.html
Sent from the Solr - User mailing list archive at Nabble.com.
Re: Recommended MySQL JDBC driver
Posted by Marc Sturlese <ma...@gmail.com>.
I supose you use BatchSize=-1 to index that amount of data. Up from 5.1.7
connector there's this param:
netTimeoutForStreamingResults
The default value is 600. Increasing that maybe can help (2400 for example?)
--
View this message in context: http://lucene.472066.n3.nabble.com/Recommended-MySQL-JDBC-driver-tp817458p924107.html
Sent from the Solr - User mailing list archive at Nabble.com.
Re: Recommended MySQL JDBC driver
Posted by Lukas Kahwe Smith <ml...@pooteeweet.org>.
On 18.05.2010, at 17:22, Shawn Heisey wrote:
> On 5/14/2010 12:40 PM, Shawn Heisey wrote:
>> I downgraded to 5.0.8 for testing. Initially, I thought it was going to be faster, but it slows down as it gets further into the index. It now looks like it's probably going to take the same amount of time.
>>
>> On the server timeout thing - that's a setting you'd have to put in my.ini or my.cfg, there may also be a way to change it on the fly without restarting the server. I suspect that when you are running a multiple query setup like yours, it opens multiple connections, and when one of them is busy doing some work, the others are idle. That may be related to the timeout with the older connector version. On my setup, I only have one query that retrieves records, so I'm probably not going to run into that. I could be wrong about how it works - you can confirm or refute this idea by looking at SHOW PROCESSLIST on your MySQL server while it's working.
>
> I was having no trouble with the 5.0.8 connector on 1.5-dev build 922440M, but then I upgraded the test machine to the latest 4.0 from trunk, and ran into the timeout issue you described, so I am going back to the 5.1.12 connector. I just saw the message on the list about branch_3x in SVN, which looks like a better option than trunk.
Any news on this topic?
regards,
Lukas Kahwe Smith
mls@pooteeweet.org
Re: Recommended MySQL JDBC driver
Posted by Shawn Heisey <so...@elyograg.org>.
On 5/14/2010 12:40 PM, Shawn Heisey wrote:
> I downgraded to 5.0.8 for testing. Initially, I thought it was going
> to be faster, but it slows down as it gets further into the index. It
> now looks like it's probably going to take the same amount of time.
>
> On the server timeout thing - that's a setting you'd have to put in
> my.ini or my.cfg, there may also be a way to change it on the fly
> without restarting the server. I suspect that when you are running a
> multiple query setup like yours, it opens multiple connections, and
> when one of them is busy doing some work, the others are idle. That
> may be related to the timeout with the older connector version. On my
> setup, I only have one query that retrieves records, so I'm probably
> not going to run into that. I could be wrong about how it works - you
> can confirm or refute this idea by looking at SHOW PROCESSLIST on your
> MySQL server while it's working.
I was having no trouble with the 5.0.8 connector on 1.5-dev build
922440M, but then I upgraded the test machine to the latest 4.0 from
trunk, and ran into the timeout issue you described, so I am going back
to the 5.1.12 connector. I just saw the message on the list about
branch_3x in SVN, which looks like a better option than trunk.
Shawn
Re: Recommended MySQL JDBC driver
Posted by Shawn Heisey <so...@elyograg.org>.
>> Lucas.. was there a reason you went with 5.1.10 or was it just the latest
>> when you started your Solr project?
>>
> just what was recent when i set things up.
>
>> Also, how many items are in your index and how big is your index size?
>>
> index size is 4.6GB with about 16M entities.
>
I downgraded to 5.0.8 for testing. Initially, I thought it was going to
be faster, but it slows down as it gets further into the index. It now
looks like it's probably going to take the same amount of time.
On the server timeout thing - that's a setting you'd have to put in
my.ini or my.cfg, there may also be a way to change it on the fly
without restarting the server. I suspect that when you are running a
multiple query setup like yours, it opens multiple connections, and when
one of them is busy doing some work, the others are idle. That may be
related to the timeout with the older connector version. On my setup, I
only have one query that retrieves records, so I'm probably not going to
run into that. I could be wrong about how it works - you can confirm or
refute this idea by looking at SHOW PROCESSLIST on your MySQL server
while it's working.
Each of my shards is reported by the replication handler at a little
over 12GB. This is with 7 million entities.
Shawn
Re: Recommended MySQL JDBC driver
Posted by Lukas Kahwe Smith <ml...@pooteeweet.org>.
On 14.05.2010, at 19:16, Blargy wrote:
>
> Lucas.. was there a reason you went with 5.1.10 or was it just the latest
> when you started your Solr project?
just what was recent when i set things up.
> Also, how many items are in your index and how big is your index size?
index size is 4.6GB with about 16M entities.
regards,
Lukas Kahwe Smith
mls@pooteeweet.org
Re: Recommended MySQL JDBC driver
Posted by Blargy <zm...@hotmail.com>.
Lucas.. was there a reason you went with 5.1.10 or was it just the latest
when you started your Solr project?
Also, how many items are in your index and how big is your index size?
Thanks
--
View this message in context: http://lucene.472066.n3.nabble.com/Recommended-MySQL-JDBC-driver-tp817458p817855.html
Sent from the Solr - User mailing list archive at Nabble.com.
Re: Recommended MySQL JDBC driver
Posted by Lukas Kahwe Smith <ml...@pooteeweet.org>.
Hi,
just FYI I am using mysql-connector-java-5.1.10-bin.jar and I my full import takes about 3 hours and I am not experiencing crashes.
regards,
Lukas
Re: Recommended MySQL JDBC driver
Posted by Blargy <zm...@hotmail.com>.
Shawn, first off thanks for the reply and links!
"As far as the error in the 5.0.8 version, does the import work, or does it
fail when the exception is thrown?"
- The import "works" for about 5-10 minutes then it fails and everything is
rolled-back one the above exception is thrown.
" You might also try doing as it says and increasing the timeout on the
server"
- How is this accomplished? I tried "maxWait" options on the datasource in
data-config.xml but that didn't seem to work.
I'm also torn on whether or not I should file a bug that may or not exist.
The whole reason I tried downgrading to 5.0.8 was due to the fact that
during certain (not all) delta-imports I keep getting the following error
which seems to be all mysql related:
SEVERE: Delta Import Failed
java.lang.StackOverflowError
at java.net.SocketOutputStream.socketWrite0(Native Method)
at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:109)
at java.net.SocketOutputStream.write(SocketOutputStream.java:153)
at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)
at java.io.BufferedOutputStream.write(BufferedOutputStream.java:126)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3296)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1941)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2114)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2690)
at
com.mysql.jdbc.StatementImpl.executeSimpleNonQuery(StatementImpl.java:1545)
at com.mysql.jdbc.RowDataDynamic.close(RowDataDynamic.java:201)
at com.mysql.jdbc.ResultSetImpl.realClose(ResultSetImpl.java:7624)
at com.mysql.jdbc.ResultSetImpl.close(ResultSetImpl.java:908)
at com.mysql.jdbc.StatementImpl.realClose(StatementImpl.java:2364)
at
com.mysql.jdbc.ConnectionImpl.closeAllOpenStatements(ConnectionImpl.java:1583)
at com.mysql.jdbc.ConnectionImpl.realClose(ConnectionImpl.java:4454)
at com.mysql.jdbc.ConnectionImpl.cleanup(ConnectionImpl.java:1359)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2723)
at
com.mysql.jdbc.StatementImpl.executeSimpleNonQuery(StatementImpl.java:1545)
at com.mysql.jdbc.RowDataDynamic.close(RowDataDynamic.java:201)
at com.mysql.jdbc.ResultSetImpl.realClose(ResultSetImpl.java:7624)
at com.mysql.jdbc.ResultSetImpl.close(ResultSetImpl.java:908)
at com.mysql.jdbc.StatementImpl.realClose(StatementImpl.java:2364)
at
com.mysql.jdbc.ConnectionImpl.closeAllOpenStatements(ConnectionImpl.java:1583)
at com.mysql.jdbc.ConnectionImpl.realClose(ConnectionImpl.java:4454)
at com.mysql.jdbc.ConnectionImpl.cleanup(ConnectionImpl.java:1359)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2723)
.... and it keeps going
Once the above exception occurs I can never delta-import again against that
index. I am then forced to do a full-import. Do you have any thoughts or
suggestions on that? Should I file this as a MySQL bug?
Thanks again for your help. I'll try playing around with the latest versions
of the connector and I'll post my results.
--
View this message in context: http://lucene.472066.n3.nabble.com/Recommended-MySQL-JDBC-driver-tp817458p817790.html
Sent from the Solr - User mailing list archive at Nabble.com.
Re: Recommended MySQL JDBC driver
Posted by Shawn Heisey <so...@elyograg.org>.
I would like to know the same thing. I'm using 5.1.12 myself. A full
reindex of one of my shards takes 4-6 hours for 7 million rows,
depending on whether I run them one at a time or all at once. If I run
the same query on the same machine with the commandline client and write
the results to a file, it takes 10-15 minutes. I know that reindexing
will never be as fast as a raw data dump, but it does seem like it could
be faster.
I had been using an older release (which I think was 5.1.7, but I can't
remember) that was actually slower than this one. I haven't tried the
5.0.8 version myself. It is well over two years old, so I'm not
surprised it's buggy, especially if everything else (mysql server, java,
etc) is running recent versions.
There is mention in the change log for 5.1.13 (not yet released) about a
performance regression, but when I look at the bug and follow the
references, it sounds like the regression was actually introduced by a
change after 5.1.12, so it may not be any faster.
Regarding the speed problem in 5.1.12, you could try a development
snapshot on a system not in production, which I will also do on mine.
If that doesn't improve the situation, consider filing a bug on the
MySQL connector.
http://downloads.mysql.com/snapshots.php
As far as the error in the 5.0.8 version, does the import work, or does
it fail when the exception is thrown? I have seen a lot of messages
about autoreconnect not working for people, but no bug filed, and from
what I can tell, autoreconnect does work, but throws the exception even
when it's working. You might also try doing as it says and increasing
the timeout on the server.
Thanks,
Shawn
On 5/14/2010 8:56 AM, Blargy wrote:
> Which driver is the "best" for use with solr?
>
> I am currently using mysql-connector-java-5.1.12-bin.jar in my production
> setting. However I recently tried downgrading and did some quick indexing
> using mysql-connector-java-5.0.8-bin.jar and I close to a 2x improvement in
> speed!!! Unfortunately I kept getting the following error using the 5.0.8
> version:
>
> "Caused by: com.mysql.jdbc.CommunicationsException: The last communications
> with the server was 474 seconds ago, which is longer than the server
> configured value of 'wait_timeout'. You should consider either expiring
> and/or testing connection validity before use in your application,
> increasing the server configured values for client timeouts, or using the
> Connector/J connection property 'autoReconnect=true' to avoid this problem."
>
> I tried setting the autoReconnect="true" in my datasource configuration but
> I keep getting the same error. Any ideas?
>
>