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 JavaGuy84 <bb...@gmail.com> on 2010/03/06 17:36:05 UTC
SOLR takes more than 9 hours to index 300000 rows
Hi,
I am facing performance issue in SOLR when indexing huge data. Please find
below the stats,
<str name="Time Elapsed">8:57:17.334</str>
<str name="Total Requests made to DataSource">42778</str>
<str name="Total Rows Fetched">273725</str>
<str name="Total Documents Processed">42775</str>
<str name="Total Documents Skipped">0</str>
Indexing of 273725 rows is taking almost 9 hours. Please find below my Data
config file
<dataConfig>
<dataSource driver="com.metamatrix.jdbc.MMDriver" url="jdbc:" />
<document name="doc">
<entity name="object"
query="select objectuid as uid, objectid, objecttype, objectname,
repositoryname, a.lastupdateddate from MetaModel.POC.Object a,
MetaModel.POC.Repository b where a.repositoryid = b.repositoryid"
transformer="RegexTransformer,DateFormatTransformer,TemplateTransformer">
<field column="objectname" name="name"/>
<field column="uid" name="uid"/>
<field column="objectid" name="id"/>
<field column="objecttype" name="type"/>
<field column="repositoryname" name="repository"/>
<entity name="property" query="select ObjectUID,ObjectPropertyName as
name, ObjectPropertyValue as value from MetaModel.POC.ObjectProperty"
processor="CachedSqlEntityProcessor" cacheKey="ObjectUID"
cacheLookup="object.uid"
transformer="RegexTransformer,DateFormatTransformer,TemplateTransformer">
<field column="value" name="${property.name}"/>
</entity>
<entity name="relationship_entity" query="select OBJECT1uid,Object2name as
rname,Object2type as rtype,relationshiptype as rship, b.RepositoryName as
rrepname from MetaModel.POC.BinaryRelationShip a, MetaModel.POC.Repository
b where a.Object2RepositoryId=b.repositoryId"
processor="CachedSqlEntityProcessor" cacheKey="OBJECT1uid"
cacheLookup="object.uid"
transformer="RegexTransformer,DateFormatTransformer,TemplateTransformer">
<field column="rship" name="relationship"/>
<field column="rname" name="related_name" />
<field column="rtype" name="related_type"/>
<field column="rrepname" name="repositoryname"/>
</entity>
</entity>
</document>
Time taken to directly query the database with the above mentioned SQL
statements,
select objectuid as uid, objectid, objecttype, objectname, repositoryname,
a.lastupdateddate from MetaModel.POC.Object a, MetaModel.POC.Repository b
where a.repositoryid = b.repositoryid ---> 3 minutes
select ObjectUID,ObjectPropertyName as name, ObjectPropertyValue as value
from MetaModel.POC.ObjectProperty --> 5 minutes
select OBJECT1uid,Object2name as rname,Object2type as rtype,relationshiptype
as rship, b.RepositoryName as rrepname from
MetaModel.POC.BinaryRelationShip a, MetaModel.POC.Repository b where
a.Object2RepositoryId=b.repositoryId" --> 3 seconds
As I am using CachedSqlEntityProcessor I assume that SOLR first issues these
select statements (mentioned above first) and then it match based on
cacheKey (from caching), so SOLR should ideally take (addition of time
taken to execute the above 3 queries + some time for doing filtering based
on cacheKey ). But in my case its taking hours and hours for indexing.
Can someone please let me know if I am doing anything wrong which might
cause this issue?
Thanks,
Barani
--
View this message in context: http://old.nabble.com/SOLR-takes-more-than-9-hours-to-index-300000-rows-tp27805403p27805403.html
Sent from the Solr - User mailing list archive at Nabble.com.
Re: SOLR takes more than 9 hours to index 300000 rows
Posted by Shawn Heisey <so...@elyograg.org>.
I have now learned more about the CachedSqlEntityProcessor, so in theory
it should be done with the server connections, but if it were me, I'd
verify that.
How big are the resultsets from those queries? SOLR has to put all the
table data into RAM, which is very likely going to take considerably
more memory than the raw size of the results. Do you see any evidence
that perhaps your machine is swapping? If you're on a UNIX variant, run
"vmstat 5" and look for nonzero values in the "si" and "so" fields. If
you're on Windows, pull up task manager's Performance tab and see if the
PF Usage parameter is larger than physical memory. If this is
happening, you'd definitely see constant hard drive light blinking.
On 3/6/2010 10:20 AM, Shawn Heisey wrote:
> At the 9+ hour mark, is your database server showing active
> connections that are sending data, or is all the activity local to SOLR?
>
> We have a 40 million row database in MySQL, with each row comprising
> more than 80 fields. I'm including the config from one of our
> shards. There are about 6.6 million rows in this shard, and it
> indexes to a 16GB index (9.6GB of which is the .fdt file) in 2-3 hours
> depending on how loaded the database server is at the time. I once
> indexed the all 40 million rows into a shard and that only took 11
> hours to build a 91GB index.
>
> The batchSize parameter is necessary to have the jdbc driver stream
> the results instead of trying to cache them all before sending them to
> the application. The server doesn't have enough memory for that.
>
> <dataConfig>
> <dataSource type="JdbcDataSource"
> driver="com.mysql.jdbc.Driver"
> encoding="UTF-8"
>
> url="jdbc:mysql://[SERVER]:3306/[SCHEMA]?zeroDateTimeBehavior=convertToNull"
>
> batchSize="-1"
> user="[REMOVED]"
> password="[REMOVED]"/>
> <document>
> <entity name="[TABLE]"
> query="select * from [TABLE] where (did mod 6) = 0">
> </entity>
> </document>
> </dataConfig>
>
> On 3/6/2010 9:36 AM, JavaGuy84 wrote:
>> Hi,
>>
>> I am facing performance issue in SOLR when indexing huge data.
>> Please find
>> below the stats,
>>
>> <str name="Time Elapsed">8:57:17.334</str>
>> <str name="Total Requests made to DataSource">42778</str>
>> <str name="Total Rows Fetched">273725</str>
>> <str name="Total Documents Processed">42775</str>
>> <str name="Total Documents Skipped">0</str>
>>
>> Indexing of 273725 rows is taking almost 9 hours. Please find below
>> my Data
>> config file
>>
>> <dataConfig>
>> <dataSource driver="com.metamatrix.jdbc.MMDriver" url="jdbc:" />
>> <document name="doc">
>> <entity name="object"
>> query="select objectuid as uid, objectid, objecttype,
>> objectname,
>> repositoryname, a.lastupdateddate from MetaModel.POC.Object a,
>> MetaModel.POC.Repository b where a.repositoryid = b.repositoryid"
>> transformer="RegexTransformer,DateFormatTransformer,TemplateTransformer">
>>
>> <field column="objectname" name="name"/>
>> <field column="uid" name="uid"/>
>> <field column="objectid" name="id"/>
>> <field column="objecttype" name="type"/>
>> <field column="repositoryname" name="repository"/>
>>
>> <entity name="property" query="select ObjectUID,ObjectPropertyName as
>> name, ObjectPropertyValue as value from MetaModel.POC.ObjectProperty"
>> processor="CachedSqlEntityProcessor" cacheKey="ObjectUID"
>> cacheLookup="object.uid"
>> transformer="RegexTransformer,DateFormatTransformer,TemplateTransformer">
>>
>>
>> <field column="value" name="${property.name}"/>
>>
>> </entity>
>>
>> <entity name="relationship_entity" query="select
>> OBJECT1uid,Object2name as
>> rname,Object2type as rtype,relationshiptype as rship,
>> b.RepositoryName as
>> rrepname from MetaModel.POC.BinaryRelationShip a,
>> MetaModel.POC.Repository
>> b where a.Object2RepositoryId=b.repositoryId"
>> processor="CachedSqlEntityProcessor" cacheKey="OBJECT1uid"
>> cacheLookup="object.uid"
>> transformer="RegexTransformer,DateFormatTransformer,TemplateTransformer">
>>
>> <field column="rship" name="relationship"/>
>> <field column="rname" name="related_name" />
>> <field column="rtype" name="related_type"/>
>> <field column="rrepname" name="repositoryname"/>
>> </entity>
>>
>> </entity>
>> </document>
>>
>>
>> Time taken to directly query the database with the above mentioned SQL
>> statements,
>>
>>
>> select objectuid as uid, objectid, objecttype, objectname,
>> repositoryname,
>> a.lastupdateddate from MetaModel.POC.Object a,
>> MetaModel.POC.Repository b
>> where a.repositoryid = b.repositoryid ---> 3 minutes
>>
>> select ObjectUID,ObjectPropertyName as name, ObjectPropertyValue as
>> value
>> from MetaModel.POC.ObjectProperty --> 5 minutes
>>
>>
>> select OBJECT1uid,Object2name as rname,Object2type as
>> rtype,relationshiptype
>> as rship, b.RepositoryName as rrepname from
>> MetaModel.POC.BinaryRelationShip a, MetaModel.POC.Repository b where
>> a.Object2RepositoryId=b.repositoryId" --> 3 seconds
>>
>> As I am using CachedSqlEntityProcessor I assume that SOLR first
>> issues these
>> select statements (mentioned above first) and then it match based on
>> cacheKey (from caching), so SOLR should ideally take (addition of time
>> taken to execute the above 3 queries + some time for doing filtering
>> based
>> on cacheKey ). But in my case its taking hours and hours for indexing.
>>
>> Can someone please let me know if I am doing anything wrong which might
>> cause this issue?
>
Re: SOLR takes more than 9 hours to index 300000 rows
Posted by JavaGuy84 <bb...@gmail.com>.
Shawn,
Please find below the resulset size of each query,
select objectuid as uid, objectid, objecttype, objectname,
repositoryname,
a.lastupdateddate from MetaModel.POC.Object a,
MetaModel.POC.Repository b
where a.repositoryid = b.repositoryid ---> 300000 rows ---> Query 1
select ObjectUID,ObjectPropertyName as name, ObjectPropertyValue as
value
from MetaModel.POC.ObjectProperty --> 600000 rows --> Query 2
select OBJECT1uid,Object2name as rname,Object2type as
rtype,relationshiptype
as rship, b.RepositoryName as rrepname from
MetaModel.POC.BinaryRelationShip a, MetaModel.POC.Repository b where
a.Object2RepositoryId=b.repositoryId" --> 600 rows --> Query 3
I want my second and third query to run for every row returned by my first
query without hitting the DB for every loop hence started using
SqlCachedEntityProcessor.
Please find below RAM stats of my server running SOLR / MetaMatrix DB
SOLR - 100,176 K
MetaMatrix DB - 1106048 K almost 1080.125 MB
Thanks,
Barani
Shawn Heisey-4 wrote:
>
> Do keep looking into the batchSize, but I think I might have found the
> issue. If I understand things correctly, you will need to add
> processor="CachedSqlEntityProcessor" to your first entity. It's only
> specified on the other two. Assuming you have enough RAM and heap space
> available in your JVM to load the results of all three queries, that
> ought to make it work very quickly.
>
> If I'm right, basically what it's doing is issuing a real SQL query
> against your first table for every entry it has read for the other two
> tables.
>
> Shawn
>
> On 3/6/2010 11:58 AM, JavaGuy84 wrote:
>> Shawn,
>>
>> Thanks a lot for your response,
>>
>> Yes, still the DB connection is active.. It is still fetching the data
>> from
>> the DB.
>>
>> I am using Redhat MetaMatrix DB as backend and I am trying to find out
>> the
>> parameter for setting the JDBC fetch size..
>>
>> Do you think that this problem will be mostly due to fetch size?
>>
>> Thanks,
>> Barani
>>
>
>
>
--
View this message in context: http://old.nabble.com/SOLR-takes-more-than-9-hours-to-index-300000-rows-tp27805403p27806552.html
Sent from the Solr - User mailing list archive at Nabble.com.
Re: SOLR takes more than 9 hours to index 300000 rows
Posted by JavaGuy84 <bb...@gmail.com>.
Shawn,
Increasing the fetch size and increasing my heap based on that did the
trick.. Thanksss a lot for your help.. your suggestions helped me a lot..
Hope these suggestions will be helpful to others too who are facing similar
kind of issue.
Thanks,
Barani
Shawn Heisey-4 wrote:
>
> Do keep looking into the batchSize, but I think I might have found the
> issue. If I understand things correctly, you will need to add
> processor="CachedSqlEntityProcessor" to your first entity. It's only
> specified on the other two. Assuming you have enough RAM and heap space
> available in your JVM to load the results of all three queries, that
> ought to make it work very quickly.
>
> If I'm right, basically what it's doing is issuing a real SQL query
> against your first table for every entry it has read for the other two
> tables.
>
> Shawn
>
> On 3/6/2010 11:58 AM, JavaGuy84 wrote:
>> Shawn,
>>
>> Thanks a lot for your response,
>>
>> Yes, still the DB connection is active.. It is still fetching the data
>> from
>> the DB.
>>
>> I am using Redhat MetaMatrix DB as backend and I am trying to find out
>> the
>> parameter for setting the JDBC fetch size..
>>
>> Do you think that this problem will be mostly due to fetch size?
>>
>> Thanks,
>> Barani
>>
>
>
>
--
View this message in context: http://old.nabble.com/SOLR-takes-more-than-9-hours-to-index-300000-rows-tp27805403p27825172.html
Sent from the Solr - User mailing list archive at Nabble.com.
Re: SOLR takes more than 9 hours to index 300000 rows
Posted by Shawn Heisey <so...@elyograg.org>.
Do keep looking into the batchSize, but I think I might have found the
issue. If I understand things correctly, you will need to add
processor="CachedSqlEntityProcessor" to your first entity. It's only
specified on the other two. Assuming you have enough RAM and heap space
available in your JVM to load the results of all three queries, that
ought to make it work very quickly.
If I'm right, basically what it's doing is issuing a real SQL query
against your first table for every entry it has read for the other two
tables.
Shawn
On 3/6/2010 11:58 AM, JavaGuy84 wrote:
> Shawn,
>
> Thanks a lot for your response,
>
> Yes, still the DB connection is active.. It is still fetching the data from
> the DB.
>
> I am using Redhat MetaMatrix DB as backend and I am trying to find out the
> parameter for setting the JDBC fetch size..
>
> Do you think that this problem will be mostly due to fetch size?
>
> Thanks,
> Barani
>
Re: SOLR takes more than 9 hours to index 300000 rows
Posted by JavaGuy84 <bb...@gmail.com>.
Shawn,
Thanks a lot for your response,
Yes, still the DB connection is active.. It is still fetching the data from
the DB.
I am using Redhat MetaMatrix DB as backend and I am trying to find out the
parameter for setting the JDBC fetch size..
Do you think that this problem will be mostly due to fetch size?
Thanks,
Barani
Shawn Heisey-4 wrote:
>
> At the 9+ hour mark, is your database server showing active connections
> that are sending data, or is all the activity local to SOLR?
>
> We have a 40 million row database in MySQL, with each row comprising
> more than 80 fields. I'm including the config from one of our shards.
> There are about 6.6 million rows in this shard, and it indexes to a 16GB
> index (9.6GB of which is the .fdt file) in 2-3 hours depending on how
> loaded the database server is at the time. I once indexed the all 40
> million rows into a shard and that only took 11 hours to build a 91GB
> index.
>
> The batchSize parameter is necessary to have the jdbc driver stream the
> results instead of trying to cache them all before sending them to the
> application. The server doesn't have enough memory for that.
>
> <dataConfig>
> <dataSource type="JdbcDataSource"
> driver="com.mysql.jdbc.Driver"
> encoding="UTF-8"
>
> url="jdbc:mysql://[SERVER]:3306/[SCHEMA]?zeroDateTimeBehavior=convertToNull"
> batchSize="-1"
> user="[REMOVED]"
> password="[REMOVED]"/>
> <document>
> <entity name="[TABLE]"
> query="select * from [TABLE] where (did mod 6) = 0">
> </entity>
> </document>
> </dataConfig>
>
> On 3/6/2010 9:36 AM, JavaGuy84 wrote:
>> Hi,
>>
>> I am facing performance issue in SOLR when indexing huge data. Please
>> find
>> below the stats,
>>
>> <str name="Time Elapsed">8:57:17.334</str>
>> <str name="Total Requests made to DataSource">42778</str>
>> <str name="Total Rows Fetched">273725</str>
>> <str name="Total Documents Processed">42775</str>
>> <str name="Total Documents Skipped">0</str>
>>
>> Indexing of 273725 rows is taking almost 9 hours. Please find below my
>> Data
>> config file
>>
>> <dataConfig>
>> <dataSource driver="com.metamatrix.jdbc.MMDriver" url="jdbc:" />
>> <document name="doc">
>> <entity name="object"
>> query="select objectuid as uid, objectid, objecttype, objectname,
>> repositoryname, a.lastupdateddate from MetaModel.POC.Object a,
>> MetaModel.POC.Repository b where a.repositoryid = b.repositoryid"
>> transformer="RegexTransformer,DateFormatTransformer,TemplateTransformer">
>> <field column="objectname" name="name"/>
>> <field column="uid" name="uid"/>
>> <field column="objectid" name="id"/>
>> <field column="objecttype" name="type"/>
>> <field column="repositoryname" name="repository"/>
>>
>> <entity name="property" query="select ObjectUID,ObjectPropertyName as
>> name, ObjectPropertyValue as value from MetaModel.POC.ObjectProperty"
>> processor="CachedSqlEntityProcessor" cacheKey="ObjectUID"
>> cacheLookup="object.uid"
>> transformer="RegexTransformer,DateFormatTransformer,TemplateTransformer">
>>
>> <field column="value" name="${property.name}"/>
>>
>> </entity>
>>
>> <entity name="relationship_entity" query="select OBJECT1uid,Object2name
>> as
>> rname,Object2type as rtype,relationshiptype as rship, b.RepositoryName as
>> rrepname from MetaModel.POC.BinaryRelationShip a,
>> MetaModel.POC.Repository
>> b where a.Object2RepositoryId=b.repositoryId"
>> processor="CachedSqlEntityProcessor" cacheKey="OBJECT1uid"
>> cacheLookup="object.uid"
>> transformer="RegexTransformer,DateFormatTransformer,TemplateTransformer">
>> <field column="rship" name="relationship"/>
>> <field column="rname" name="related_name" />
>> <field column="rtype" name="related_type"/>
>> <field column="rrepname" name="repositoryname"/>
>> </entity>
>>
>> </entity>
>> </document>
>>
>>
>> Time taken to directly query the database with the above mentioned SQL
>> statements,
>>
>>
>> select objectuid as uid, objectid, objecttype, objectname,
>> repositoryname,
>> a.lastupdateddate from MetaModel.POC.Object a, MetaModel.POC.Repository
>> b
>> where a.repositoryid = b.repositoryid ---> 3 minutes
>>
>> select ObjectUID,ObjectPropertyName as name, ObjectPropertyValue as value
>> from MetaModel.POC.ObjectProperty --> 5 minutes
>>
>>
>> select OBJECT1uid,Object2name as rname,Object2type as
>> rtype,relationshiptype
>> as rship, b.RepositoryName as rrepname from
>> MetaModel.POC.BinaryRelationShip a, MetaModel.POC.Repository b where
>> a.Object2RepositoryId=b.repositoryId" --> 3 seconds
>>
>> As I am using CachedSqlEntityProcessor I assume that SOLR first issues
>> these
>> select statements (mentioned above first) and then it match based on
>> cacheKey (from caching), so SOLR should ideally take (addition of time
>> taken to execute the above 3 queries + some time for doing filtering
>> based
>> on cacheKey ). But in my case its taking hours and hours for indexing.
>>
>> Can someone please let me know if I am doing anything wrong which might
>> cause this issue?
>>
>
>
>
--
View this message in context: http://old.nabble.com/SOLR-takes-more-than-9-hours-to-index-300000-rows-tp27805403p27806375.html
Sent from the Solr - User mailing list archive at Nabble.com.
Re: SOLR takes more than 9 hours to index 300000 rows
Posted by Shawn Heisey <so...@elyograg.org>.
At the 9+ hour mark, is your database server showing active connections
that are sending data, or is all the activity local to SOLR?
We have a 40 million row database in MySQL, with each row comprising
more than 80 fields. I'm including the config from one of our shards.
There are about 6.6 million rows in this shard, and it indexes to a 16GB
index (9.6GB of which is the .fdt file) in 2-3 hours depending on how
loaded the database server is at the time. I once indexed the all 40
million rows into a shard and that only took 11 hours to build a 91GB index.
The batchSize parameter is necessary to have the jdbc driver stream the
results instead of trying to cache them all before sending them to the
application. The server doesn't have enough memory for that.
<dataConfig>
<dataSource type="JdbcDataSource"
driver="com.mysql.jdbc.Driver"
encoding="UTF-8"
url="jdbc:mysql://[SERVER]:3306/[SCHEMA]?zeroDateTimeBehavior=convertToNull"
batchSize="-1"
user="[REMOVED]"
password="[REMOVED]"/>
<document>
<entity name="[TABLE]"
query="select * from [TABLE] where (did mod 6) = 0">
</entity>
</document>
</dataConfig>
On 3/6/2010 9:36 AM, JavaGuy84 wrote:
> Hi,
>
> I am facing performance issue in SOLR when indexing huge data. Please find
> below the stats,
>
> <str name="Time Elapsed">8:57:17.334</str>
> <str name="Total Requests made to DataSource">42778</str>
> <str name="Total Rows Fetched">273725</str>
> <str name="Total Documents Processed">42775</str>
> <str name="Total Documents Skipped">0</str>
>
> Indexing of 273725 rows is taking almost 9 hours. Please find below my Data
> config file
>
> <dataConfig>
> <dataSource driver="com.metamatrix.jdbc.MMDriver" url="jdbc:" />
> <document name="doc">
> <entity name="object"
> query="select objectuid as uid, objectid, objecttype, objectname,
> repositoryname, a.lastupdateddate from MetaModel.POC.Object a,
> MetaModel.POC.Repository b where a.repositoryid = b.repositoryid"
> transformer="RegexTransformer,DateFormatTransformer,TemplateTransformer">
> <field column="objectname" name="name"/>
> <field column="uid" name="uid"/>
> <field column="objectid" name="id"/>
> <field column="objecttype" name="type"/>
> <field column="repositoryname" name="repository"/>
>
> <entity name="property" query="select ObjectUID,ObjectPropertyName as
> name, ObjectPropertyValue as value from MetaModel.POC.ObjectProperty"
> processor="CachedSqlEntityProcessor" cacheKey="ObjectUID"
> cacheLookup="object.uid"
> transformer="RegexTransformer,DateFormatTransformer,TemplateTransformer">
>
> <field column="value" name="${property.name}"/>
>
> </entity>
>
> <entity name="relationship_entity" query="select OBJECT1uid,Object2name as
> rname,Object2type as rtype,relationshiptype as rship, b.RepositoryName as
> rrepname from MetaModel.POC.BinaryRelationShip a, MetaModel.POC.Repository
> b where a.Object2RepositoryId=b.repositoryId"
> processor="CachedSqlEntityProcessor" cacheKey="OBJECT1uid"
> cacheLookup="object.uid"
> transformer="RegexTransformer,DateFormatTransformer,TemplateTransformer">
> <field column="rship" name="relationship"/>
> <field column="rname" name="related_name" />
> <field column="rtype" name="related_type"/>
> <field column="rrepname" name="repositoryname"/>
> </entity>
>
> </entity>
> </document>
>
>
> Time taken to directly query the database with the above mentioned SQL
> statements,
>
>
> select objectuid as uid, objectid, objecttype, objectname, repositoryname,
> a.lastupdateddate from MetaModel.POC.Object a, MetaModel.POC.Repository b
> where a.repositoryid = b.repositoryid ---> 3 minutes
>
> select ObjectUID,ObjectPropertyName as name, ObjectPropertyValue as value
> from MetaModel.POC.ObjectProperty --> 5 minutes
>
>
> select OBJECT1uid,Object2name as rname,Object2type as rtype,relationshiptype
> as rship, b.RepositoryName as rrepname from
> MetaModel.POC.BinaryRelationShip a, MetaModel.POC.Repository b where
> a.Object2RepositoryId=b.repositoryId" --> 3 seconds
>
> As I am using CachedSqlEntityProcessor I assume that SOLR first issues these
> select statements (mentioned above first) and then it match based on
> cacheKey (from caching), so SOLR should ideally take (addition of time
> taken to execute the above 3 queries + some time for doing filtering based
> on cacheKey ). But in my case its taking hours and hours for indexing.
>
> Can someone please let me know if I am doing anything wrong which might
> cause this issue?
>