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 "Vazquez, Maria (STM)" <Ma...@DexOne.com> on 2011/09/22 21:17:33 UTC
JdbcDataSource and threads
Hi!
So as of 3.4 JdbcDataSource doesn't work with threads, correct?
https://issues.apache.org/jira/browse/SOLR-2233
I'm using Microsoft SQL Server, my data-config.xml has a lot of very
complex SQL queries and it takes a long time to index.
I'm migrating from Lucene to Solr and the Lucene code uses threads so it
takes little time to index, now in Solr if I add threads=xx to my
rootEntity I get lots of errors about connections being closed.
Thanks a lot,
Maria
Re: JdbcDataSource and threads
Posted by pu...@gmail.com.
Seems to be a rather innocent network issue based on your stacktrace:
Caused by: java.sql.SQLException: Network error IOException: Address
already in use: connect
Can you recheck connections and retry?
Sent from my iPhone
On Sep 23, 2011, at 3:34 PM, "Vazquez, Maria \(STM\)" <Ma...@DexOne.com> wrote:
> I tried the patch
> (https://issues.apache.org/jira/secure/attachment/12481497/SOLR-2233-001
> .patch)
>
> And now I get these errors. Am I doing something wrong? Using MS SQL
> Server
>
> 23 Sep 2011 12:26:14,418
> [org.apache.solr.handler.dataimport.ThreadedEntityProcessorWrapper]
> Exception in entity : keyword_atts_expedite
> org.apache.solr.handler.dataimport.DataImportHandlerException: Unable to
> execute query: SELECT String AS keyword_atts_expedite FROM
> tbObjectProperty WITH (NOLOCK) WHERE FKObject =
> '97F67CC9-B25D-416F-801C-863B5D5E4911' AND state = 0 AND FKProperty =
> 4226 ORDER BY String
> at
> org.apache.solr.handler.dataimport.DataImportHandlerException.wrapAndThr
> ow(DataImportHandlerException.java:72)
> at
> org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator.<ini
> t>(JdbcDataSource.java:251)
> at
> org.apache.solr.handler.dataimport.JdbcDataSource.getData(JdbcDataSource
> .java:206)
> at
> org.apache.solr.handler.dataimport.JdbcDataSource.getData(JdbcDataSource
> .java:39)
> at
> org.apache.solr.handler.dataimport.SqlEntityProcessor.initQuery(SqlEntit
> yProcessor.java:59)
> at
> org.apache.solr.handler.dataimport.SqlEntityProcessor.nextRow(SqlEntityP
> rocessor.java:73)
> at
> org.apache.solr.handler.dataimport.ThreadedEntityProcessorWrapper.nextRo
> w(ThreadedEntityProcessorWrapper.java:84)
> at
> org.apache.solr.handler.dataimport.DocBuilder$EntityRunner.runAThread(Do
> cBuilder.java:445)
> at
> org.apache.solr.handler.dataimport.DocBuilder$EntityRunner.run(DocBuilde
> r.java:398)
> at
> org.apache.solr.handler.dataimport.DocBuilder$EntityRunner.runAThread(Do
> cBuilder.java:465)
> at
> org.apache.solr.handler.dataimport.DocBuilder$EntityRunner.run(DocBuilde
> r.java:398)
> at
> org.apache.solr.handler.dataimport.DocBuilder$EntityRunner.runAThread(Do
> cBuilder.java:465)
> at
> org.apache.solr.handler.dataimport.DocBuilder$EntityRunner.run(DocBuilde
> r.java:398)
> at
> org.apache.solr.handler.dataimport.DocBuilder$EntityRunner.runAThread(Do
> cBuilder.java:465)
> at
> org.apache.solr.handler.dataimport.DocBuilder$EntityRunner.access$000(Do
> cBuilder.java:352)
> at
> org.apache.solr.handler.dataimport.DocBuilder$EntityRunner$1.run(DocBuil
> der.java:405)
> at
> java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecuto
> r.java:886)
> at
> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.ja
> va:908)
> at java.lang.Thread.run(Thread.java:662)
> Caused by: java.sql.SQLException: Network error IOException: Address
> already in use: connect
> at
> net.sourceforge.jtds.jdbc.ConnectionJDBC2.<init>(ConnectionJDBC2.java:41
> 0)
> at
> net.sourceforge.jtds.jdbc.ConnectionJDBC3.<init>(ConnectionJDBC3.java:50
> )
> at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:184)
> at
> org.apache.solr.handler.dataimport.JdbcDataSource$1.call(JdbcDataSource.
> java:157)
> at
> org.apache.solr.handler.dataimport.JdbcDataSource$1.call(JdbcDataSource.
> java:124)
> at
> org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator.<ini
> t>(JdbcDataSource.java:238)
> ... 17 more
>
> -----Original Message-----
> From: Rahul Warawdekar [mailto:rahul.warawdekar@gmail.com]
> Sent: Friday, September 23, 2011 11:21 AM
> To: solr-user@lucene.apache.org
> Subject: Re: JdbcDataSource and threads
>
> I am using Solr 3.1.
> But you can surely try the patch with 3.3.
>
> On Fri, Sep 23, 2011 at 1:35 PM, Vazquez, Maria (STM) <
> Maria.Vazquez@dexone.com> wrote:
>
>> Thanks Rahul.
>> Are you using 3.3 or 3.4? I'm on 3.3 right now
>> I will try the patch today
>> Thanks again,
>> Maria
>>
>>
>> -----Original Message-----
>> From: Rahul Warawdekar [mailto:rahul.warawdekar@gmail.com]
>> Sent: Thursday, September 22, 2011 12:46 PM
>> To: solr-user@lucene.apache.org
>> Subject: Re: JdbcDataSource and threads
>>
>> Hi,
>>
>> Have you applied the patch that is provided with the Jira you
> mentioned
>> ?
>> https://issues.apache.org/jira/browse/SOLR-2233
>>
>> Please apply the patch and check if you are getting the same
> exceptions.
>> It has worked well for me till now.
>>
>> On Thu, Sep 22, 2011 at 3:17 PM, Vazquez, Maria (STM) <
>> Maria.Vazquez@dexone.com> wrote:
>>
>>> Hi!
>>>
>>> So as of 3.4 JdbcDataSource doesn't work with threads, correct?
>>>
>>>
>>>
>>> https://issues.apache.org/jira/browse/SOLR-2233
>>>
>>>
>>>
>>> I'm using Microsoft SQL Server, my data-config.xml has a lot of very
>>> complex SQL queries and it takes a long time to index.
>>>
>>> I'm migrating from Lucene to Solr and the Lucene code uses threads
> so
>> it
>>> takes little time to index, now in Solr if I add threads=xx to my
>>> rootEntity I get lots of errors about connections being closed.
>>>
>>>
>>>
>>> Thanks a lot,
>>>
>>> Maria
>>>
>>>
>>
>>
>> --
>> Thanks and Regards
>> Rahul A. Warawdekar
>>
>
>
>
> --
> Thanks and Regards
> Rahul A. Warawdekar
RE: JdbcDataSource and threads
Posted by "Vazquez, Maria (STM)" <Ma...@DexOne.com>.
Thanks a lot for your response!
I think that is exactly what's happening. It runs ok for a short time and starts throwing that error while some of the ueriea run successfully.
I had it setup with 10 threads, maybe that was too much.
I'd be very interested in that code if you don't mind sharing.
I'm migrating code from pure Lucene to Solr and indexing time went from less that one hour to more than 4 because it's using only one thread.
Thanks a lot again, very helpful.
Maria
Sent from my Motorola ATRIX™ 4G on AT&T
-----Original message-----
From: rkuris <sw...@gmail.com>
To: solr-user@lucene.apache.org
Sent: Sat, Sep 24, 2011 18:11:20 GMT+00:00
Subject: RE: JdbcDataSource and threads
My guess on this is that you're making a LOT of database requests and have a
million TIME-WAIT connections, and your port range for local ports is
running out.
You should first confirm that's true by running netstat on the machine while
the load is running. See if it gives a lot of output.
One way to solve this problem is to use a connection pool. Look at adding a
pooled JNDI connection into your web service and connect with that instead.
The best way is to avoid making the extra connections. If the data in the
subqueries is really short, look into caching the results using a
CachedSqlEntityProcessor instead. I wasn't able to use this approach
because I had a lot of data in the inner queries. What I ended out doing
was writing my own OrderedSqlEntityProcessor which correlates an outer
ordered query with an inner ordered query. This ran a lot faster and
reduced my load times from 20 hours to 20 minutes. Let me know if you're
interested in that code.
--
View this message in context: http://lucene.472066.n3.nabble.com/JdbcDataSource-and-threads-tp3359874p3364831.html
Sent from the Solr - User mailing list archive at Nabble.com.
RE: JdbcDataSource and threads
Posted by rkuris <sw...@gmail.com>.
My guess on this is that you're making a LOT of database requests and have a
million TIME-WAIT connections, and your port range for local ports is
running out.
You should first confirm that's true by running netstat on the machine while
the load is running. See if it gives a lot of output.
One way to solve this problem is to use a connection pool. Look at adding a
pooled JNDI connection into your web service and connect with that instead.
The best way is to avoid making the extra connections. If the data in the
subqueries is really short, look into caching the results using a
CachedSqlEntityProcessor instead. I wasn't able to use this approach
because I had a lot of data in the inner queries. What I ended out doing
was writing my own OrderedSqlEntityProcessor which correlates an outer
ordered query with an inner ordered query. This ran a lot faster and
reduced my load times from 20 hours to 20 minutes. Let me know if you're
interested in that code.
--
View this message in context: http://lucene.472066.n3.nabble.com/JdbcDataSource-and-threads-tp3359874p3364831.html
Sent from the Solr - User mailing list archive at Nabble.com.
RE: JdbcDataSource and threads
Posted by "Vazquez, Maria (STM)" <Ma...@DexOne.com>.
I tried the patch
(https://issues.apache.org/jira/secure/attachment/12481497/SOLR-2233-001
.patch)
And now I get these errors. Am I doing something wrong? Using MS SQL
Server
23 Sep 2011 12:26:14,418
[org.apache.solr.handler.dataimport.ThreadedEntityProcessorWrapper]
Exception in entity : keyword_atts_expedite
org.apache.solr.handler.dataimport.DataImportHandlerException: Unable to
execute query: SELECT String AS keyword_atts_expedite FROM
tbObjectProperty WITH (NOLOCK) WHERE FKObject =
'97F67CC9-B25D-416F-801C-863B5D5E4911' AND state = 0 AND FKProperty =
4226 ORDER BY String
at
org.apache.solr.handler.dataimport.DataImportHandlerException.wrapAndThr
ow(DataImportHandlerException.java:72)
at
org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator.<ini
t>(JdbcDataSource.java:251)
at
org.apache.solr.handler.dataimport.JdbcDataSource.getData(JdbcDataSource
.java:206)
at
org.apache.solr.handler.dataimport.JdbcDataSource.getData(JdbcDataSource
.java:39)
at
org.apache.solr.handler.dataimport.SqlEntityProcessor.initQuery(SqlEntit
yProcessor.java:59)
at
org.apache.solr.handler.dataimport.SqlEntityProcessor.nextRow(SqlEntityP
rocessor.java:73)
at
org.apache.solr.handler.dataimport.ThreadedEntityProcessorWrapper.nextRo
w(ThreadedEntityProcessorWrapper.java:84)
at
org.apache.solr.handler.dataimport.DocBuilder$EntityRunner.runAThread(Do
cBuilder.java:445)
at
org.apache.solr.handler.dataimport.DocBuilder$EntityRunner.run(DocBuilde
r.java:398)
at
org.apache.solr.handler.dataimport.DocBuilder$EntityRunner.runAThread(Do
cBuilder.java:465)
at
org.apache.solr.handler.dataimport.DocBuilder$EntityRunner.run(DocBuilde
r.java:398)
at
org.apache.solr.handler.dataimport.DocBuilder$EntityRunner.runAThread(Do
cBuilder.java:465)
at
org.apache.solr.handler.dataimport.DocBuilder$EntityRunner.run(DocBuilde
r.java:398)
at
org.apache.solr.handler.dataimport.DocBuilder$EntityRunner.runAThread(Do
cBuilder.java:465)
at
org.apache.solr.handler.dataimport.DocBuilder$EntityRunner.access$000(Do
cBuilder.java:352)
at
org.apache.solr.handler.dataimport.DocBuilder$EntityRunner$1.run(DocBuil
der.java:405)
at
java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecuto
r.java:886)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.ja
va:908)
at java.lang.Thread.run(Thread.java:662)
Caused by: java.sql.SQLException: Network error IOException: Address
already in use: connect
at
net.sourceforge.jtds.jdbc.ConnectionJDBC2.<init>(ConnectionJDBC2.java:41
0)
at
net.sourceforge.jtds.jdbc.ConnectionJDBC3.<init>(ConnectionJDBC3.java:50
)
at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:184)
at
org.apache.solr.handler.dataimport.JdbcDataSource$1.call(JdbcDataSource.
java:157)
at
org.apache.solr.handler.dataimport.JdbcDataSource$1.call(JdbcDataSource.
java:124)
at
org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator.<ini
t>(JdbcDataSource.java:238)
... 17 more
-----Original Message-----
From: Rahul Warawdekar [mailto:rahul.warawdekar@gmail.com]
Sent: Friday, September 23, 2011 11:21 AM
To: solr-user@lucene.apache.org
Subject: Re: JdbcDataSource and threads
I am using Solr 3.1.
But you can surely try the patch with 3.3.
On Fri, Sep 23, 2011 at 1:35 PM, Vazquez, Maria (STM) <
Maria.Vazquez@dexone.com> wrote:
> Thanks Rahul.
> Are you using 3.3 or 3.4? I'm on 3.3 right now
> I will try the patch today
> Thanks again,
> Maria
>
>
> -----Original Message-----
> From: Rahul Warawdekar [mailto:rahul.warawdekar@gmail.com]
> Sent: Thursday, September 22, 2011 12:46 PM
> To: solr-user@lucene.apache.org
> Subject: Re: JdbcDataSource and threads
>
> Hi,
>
> Have you applied the patch that is provided with the Jira you
mentioned
> ?
> https://issues.apache.org/jira/browse/SOLR-2233
>
> Please apply the patch and check if you are getting the same
exceptions.
> It has worked well for me till now.
>
> On Thu, Sep 22, 2011 at 3:17 PM, Vazquez, Maria (STM) <
> Maria.Vazquez@dexone.com> wrote:
>
> > Hi!
> >
> > So as of 3.4 JdbcDataSource doesn't work with threads, correct?
> >
> >
> >
> > https://issues.apache.org/jira/browse/SOLR-2233
> >
> >
> >
> > I'm using Microsoft SQL Server, my data-config.xml has a lot of very
> > complex SQL queries and it takes a long time to index.
> >
> > I'm migrating from Lucene to Solr and the Lucene code uses threads
so
> it
> > takes little time to index, now in Solr if I add threads=xx to my
> > rootEntity I get lots of errors about connections being closed.
> >
> >
> >
> > Thanks a lot,
> >
> > Maria
> >
> >
>
>
> --
> Thanks and Regards
> Rahul A. Warawdekar
>
--
Thanks and Regards
Rahul A. Warawdekar
Re: JdbcDataSource and threads
Posted by Rahul Warawdekar <ra...@gmail.com>.
I am using Solr 3.1.
But you can surely try the patch with 3.3.
On Fri, Sep 23, 2011 at 1:35 PM, Vazquez, Maria (STM) <
Maria.Vazquez@dexone.com> wrote:
> Thanks Rahul.
> Are you using 3.3 or 3.4? I'm on 3.3 right now
> I will try the patch today
> Thanks again,
> Maria
>
>
> -----Original Message-----
> From: Rahul Warawdekar [mailto:rahul.warawdekar@gmail.com]
> Sent: Thursday, September 22, 2011 12:46 PM
> To: solr-user@lucene.apache.org
> Subject: Re: JdbcDataSource and threads
>
> Hi,
>
> Have you applied the patch that is provided with the Jira you mentioned
> ?
> https://issues.apache.org/jira/browse/SOLR-2233
>
> Please apply the patch and check if you are getting the same exceptions.
> It has worked well for me till now.
>
> On Thu, Sep 22, 2011 at 3:17 PM, Vazquez, Maria (STM) <
> Maria.Vazquez@dexone.com> wrote:
>
> > Hi!
> >
> > So as of 3.4 JdbcDataSource doesn't work with threads, correct?
> >
> >
> >
> > https://issues.apache.org/jira/browse/SOLR-2233
> >
> >
> >
> > I'm using Microsoft SQL Server, my data-config.xml has a lot of very
> > complex SQL queries and it takes a long time to index.
> >
> > I'm migrating from Lucene to Solr and the Lucene code uses threads so
> it
> > takes little time to index, now in Solr if I add threads=xx to my
> > rootEntity I get lots of errors about connections being closed.
> >
> >
> >
> > Thanks a lot,
> >
> > Maria
> >
> >
>
>
> --
> Thanks and Regards
> Rahul A. Warawdekar
>
--
Thanks and Regards
Rahul A. Warawdekar
RE: JdbcDataSource and threads
Posted by "Vazquez, Maria (STM)" <Ma...@DexOne.com>.
Thanks Rahul.
Are you using 3.3 or 3.4? I'm on 3.3 right now
I will try the patch today
Thanks again,
Maria
-----Original Message-----
From: Rahul Warawdekar [mailto:rahul.warawdekar@gmail.com]
Sent: Thursday, September 22, 2011 12:46 PM
To: solr-user@lucene.apache.org
Subject: Re: JdbcDataSource and threads
Hi,
Have you applied the patch that is provided with the Jira you mentioned
?
https://issues.apache.org/jira/browse/SOLR-2233
Please apply the patch and check if you are getting the same exceptions.
It has worked well for me till now.
On Thu, Sep 22, 2011 at 3:17 PM, Vazquez, Maria (STM) <
Maria.Vazquez@dexone.com> wrote:
> Hi!
>
> So as of 3.4 JdbcDataSource doesn't work with threads, correct?
>
>
>
> https://issues.apache.org/jira/browse/SOLR-2233
>
>
>
> I'm using Microsoft SQL Server, my data-config.xml has a lot of very
> complex SQL queries and it takes a long time to index.
>
> I'm migrating from Lucene to Solr and the Lucene code uses threads so
it
> takes little time to index, now in Solr if I add threads=xx to my
> rootEntity I get lots of errors about connections being closed.
>
>
>
> Thanks a lot,
>
> Maria
>
>
--
Thanks and Regards
Rahul A. Warawdekar
Re: JdbcDataSource and threads
Posted by Rahul Warawdekar <ra...@gmail.com>.
Hi,
Have you applied the patch that is provided with the Jira you mentioned ?
https://issues.apache.org/jira/browse/SOLR-2233
Please apply the patch and check if you are getting the same exceptions.
It has worked well for me till now.
On Thu, Sep 22, 2011 at 3:17 PM, Vazquez, Maria (STM) <
Maria.Vazquez@dexone.com> wrote:
> Hi!
>
> So as of 3.4 JdbcDataSource doesn't work with threads, correct?
>
>
>
> https://issues.apache.org/jira/browse/SOLR-2233
>
>
>
> I'm using Microsoft SQL Server, my data-config.xml has a lot of very
> complex SQL queries and it takes a long time to index.
>
> I'm migrating from Lucene to Solr and the Lucene code uses threads so it
> takes little time to index, now in Solr if I add threads=xx to my
> rootEntity I get lots of errors about connections being closed.
>
>
>
> Thanks a lot,
>
> Maria
>
>
--
Thanks and Regards
Rahul A. Warawdekar