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 fabigol <fa...@vialtis.com> on 2016/03/20 23:25:04 UTC

How fast indexing?

hi,
i have a soir project where i do the indexing since a database postgre.
the indexation is very long.
How i can accelerate it.
I can modify autocommit in the file solrconfig.xml?
someone has some ideas. I looking on google but I found little
help me please




--
View this message in context: http://lucene.472066.n3.nabble.com/How-fast-indexing-tp4264994.html
Sent from the Solr - User mailing list archive at Nabble.com.

Re: How fast indexing?

Posted by Shawn Heisey <ap...@elyograg.org>.
On 3/21/2016 7:48 PM, Amit Jha wrote:
> When I run the same sql on DB it takes only 1 sec. And 6-7 documents are getting indexed per second. 

That's really slow.  It seems likely that you are having extreme
performance issues due to garbage collection problems, possibly from a
heap that needs to be larger.  I will need a lot more information about
your hardware/Solr setup to figure anything out.  Some info that might
be useful:

* Solr version.
* RAM installed in each machine.
* The max heap size on each machine.
* The amount of index data contained on each machine.
* How many Solr documents live on each machine.
* Anything else you can think of that might be helpful.

> As I've 4 node solrCloud setup, can I run 4 import handler to index the same data? Will it not over write?

DIH is generally not the best way to index to SolrCloud.  The DIH
feature was created *long* before SolrCloud ever existed -- it was
designed for single-core indexes.  The best option for indexing to
SolrCloud is a SolrJ program using CloudSolrClient, or another program
that can create indexing requests you can send to the /update handler,
ideally having multiple requests in parallel.

> 10-20k is very high in numbers, where can I get the actual size of document.

You'd need to check your database, add up the sizes of all the columns
that Solr is indexing for a typical document.

Thanks,
Shawn


Re: How fast indexing?

Posted by Amit Jha <sh...@gmail.com>.
When I run the same sql on DB it takes only 1 sec. And 6-7 documents are getting indexed per second. 

As I've 4 node solrCloud setup, can I run 4 import handler to index the same data? Will it not over write? 

10-20k is very high in numbers, where can I get the actual size of document.

Rgds
AJ

> On 22-Mar-2016, at 05:32, Shawn Heisey <ap...@elyograg.org> wrote:
> 
>> On 3/20/2016 6:11 PM, Amit Jha wrote:
>> In my case I am using DIH to index the data and Query is having 2 join statements. To index 70K documents it is taking 3-4Hours. Document size would be around 10-20KB. DB is MSSQL and using solr4.2.10 in cloud mode.
> 
> My source data is in a MySQL database.  I use DIH for full rebuilds and
> SolrJ for maintenance.
> 
> My index is sharded, but I'm not running SolrCloud.  When using DIH, all
> of my shards build at once, and each one achieves about 750 docs per
> second.  With six large shards, rebuilding a 146 million document index
> takes 9-10 hours.  It produces a total index size in the ballpark of 170GB.
> 
> DIH has a performance limitation -- it's single-threaded.  I obtain the
> speeds that I do because all of my shards import at the same time -- six
> dataimport instances running at the same time, each one with a single
> thread, importing a little more than 24 million documents.  I have
> discovered that Solr is the bottleneck on my setup.  The data retrieval
> from MySQL can proceed much faster than Solr can handle with a single
> indexing thread.  My situation is a little bit unusual -- as Erick
> mentioned, usually the bottleneck is data retrieval, not Solr.
> 
> At this point, if I want to make bulk indexing go faster, I need to
> build a SolrJ application that can index with multiple threads to each
> Solr core at the same time.  This is on my roadmap, but it's not going
> to be a trivial project.
> 
> At 10-20K, your documents are large, but not excessively so.  If 70000
> documents takes 3-4 hours, then there's one of a few problems happening.
> 
> 1) your database is VERY slow.
> 2) your analysis chain in schema.xml is running SUPER slow analysis
> components.
> 3) Your server or its configuration is not providing enough resources
> (CPU/RAM/IO) so Solr can run efficiently.
> 
> #2 seems rather unlikely, so I would suspect one of the other two.
> 
> ----
> 
> I have seen one situation related to the Microsoft side of your setup
> that might cause a problem like this.  If any of your machines are
> running on Windows Server 2012 and you have bridged NICs (usually for
> failover in the event of a switch failure), then you will need to break
> the bridge and just run one NIC.
> 
> The performance improvement on the network when a bridged NIC is removed
> from Server 2012 is enough to blow your mind, especially if the access
> is over a high-latency network link, like a VPN or WAN connection.  The
> same setup on Server 2003 or Server 2008 has very good performance.
> Microsoft seems to have a bug with bridged NICs in Server 2012.  Last
> time I tried to figure out whether it could be fixed, I ran into this
> problem:
> 
> https://xkcd.com/979/
> 
> Thanks,
> Shawn
> 

Re: How fast indexing?

Posted by Shawn Heisey <ap...@elyograg.org>.
On 3/20/2016 6:11 PM, Amit Jha wrote:
> In my case I am using DIH to index the data and Query is having 2 join statements. To index 70K documents it is taking 3-4Hours. Document size would be around 10-20KB. DB is MSSQL and using solr4.2.10 in cloud mode.

My source data is in a MySQL database.  I use DIH for full rebuilds and
SolrJ for maintenance.

My index is sharded, but I'm not running SolrCloud.  When using DIH, all
of my shards build at once, and each one achieves about 750 docs per
second.  With six large shards, rebuilding a 146 million document index
takes 9-10 hours.  It produces a total index size in the ballpark of 170GB.

DIH has a performance limitation -- it's single-threaded.  I obtain the
speeds that I do because all of my shards import at the same time -- six
dataimport instances running at the same time, each one with a single
thread, importing a little more than 24 million documents.  I have
discovered that Solr is the bottleneck on my setup.  The data retrieval
from MySQL can proceed much faster than Solr can handle with a single
indexing thread.  My situation is a little bit unusual -- as Erick
mentioned, usually the bottleneck is data retrieval, not Solr.

At this point, if I want to make bulk indexing go faster, I need to
build a SolrJ application that can index with multiple threads to each
Solr core at the same time.  This is on my roadmap, but it's not going
to be a trivial project.

At 10-20K, your documents are large, but not excessively so.  If 70000
documents takes 3-4 hours, then there's one of a few problems happening.

1) your database is VERY slow.
2) your analysis chain in schema.xml is running SUPER slow analysis
components.
3) Your server or its configuration is not providing enough resources
(CPU/RAM/IO) so Solr can run efficiently.

#2 seems rather unlikely, so I would suspect one of the other two.

----

I have seen one situation related to the Microsoft side of your setup
that might cause a problem like this.  If any of your machines are
running on Windows Server 2012 and you have bridged NICs (usually for
failover in the event of a switch failure), then you will need to break
the bridge and just run one NIC.

The performance improvement on the network when a bridged NIC is removed
from Server 2012 is enough to blow your mind, especially if the access
is over a high-latency network link, like a VPN or WAN connection.  The
same setup on Server 2003 or Server 2008 has very good performance.
 Microsoft seems to have a bug with bridged NICs in Server 2012.  Last
time I tried to figure out whether it could be fixed, I ran into this
problem:

https://xkcd.com/979/

Thanks,
Shawn


Re: How fast indexing?

Posted by Amit Jha <sh...@gmail.com>.
Yes, I do have multiple modes in my solr cloud setup.

Rgds
AJ

> On 21-Mar-2016, at 22:20, fabigol <fa...@vialtis.com> wrote:
> 
> Amit Jha,
> do you have several sold server with solr cloud?
> 
> 
> 
> 
> --
> View this message in context: http://lucene.472066.n3.nabble.com/How-fast-indexing-tp4264994p4265122.html
> Sent from the Solr - User mailing list archive at Nabble.com.

Re: How fast indexing?

Posted by fabigol <fa...@vialtis.com>.
Erick,
in fact, i looked % of cpu. The % often changed but sometime it was very low
(<10%) but the memory was heavy. I think what there was a problem and i cut
the indexation.
I don't index  documents but some data of postgres database.
If i decrease the number of fields, do you win time?
is it normal if the memory is full with 10go?

what are the right values for the parameter 'auto commit"?
 




--
View this message in context: http://lucene.472066.n3.nabble.com/How-fast-indexing-tp4264994p4265125.html
Sent from the Solr - User mailing list archive at Nabble.com.

Re: How fast indexing?

Posted by fabigol <fa...@vialtis.com>.
Amit Jha,
do you have several sold server with solr cloud?




--
View this message in context: http://lucene.472066.n3.nabble.com/How-fast-indexing-tp4264994p4265122.html
Sent from the Solr - User mailing list archive at Nabble.com.

Is dataimporter.functions.escapeSql() functional ?

Posted by Joachim DORNBUSCH <jo...@agroparistech.fr>.
Hi,

I wonder if the function ${dataimporter.functions.escapeSql()} is available in Solr 5.3.1.

Whenever i use it in my data import handlers, Solr replaces '${dataimporter.functions.escapeSql(field)}' by '' (an empty string).

How can I escape strings when building sql queries in DIHconfigFile ?

I have 2 entities : ref_entity0 and ref_entity1.

If ref_entity0's name contains a simple quote ('), the following exception occurs : ref_entity1:org.apache.solr.handler.dataimport.DataImportHandlerException: Unable to execute query: select ...

Below an excerpt of the code.

<entity name="ref_entity0" onError="skip" dataSource="psql"

       query="select id, parent_name

                    from ref_entity

                    where ref_entity.id = 1">

      <field column="parent_name" name="parent_name" />

      <entity name="ref_entity1" onError="skip" dataSource="psql"

                    query="select id , concat(name,'§','$dataimporter.functions.escapeSql(ref_entity0.parent_name)}') as hierarchical_name
...

Thanks!

------ 
Joachim Dornbusch 
Direction des Systèmes d'Information 
AgroParisTech : 16, rue Claude Bernard 75005 Paris 



Re: How fast indexing?

Posted by fabigol <fa...@vialtis.com>.
For the indexation i use DIH.
I find this link for solar Indexation.
With solar it is more quick?



--
View this message in context: http://lucene.472066.n3.nabble.com/How-fast-indexing-tp4264994p4265050.html
Sent from the Solr - User mailing list archive at Nabble.com.

Re: How fast indexing?

Posted by fabigol <fa...@vialtis.com>.
for the jvm i have 8GO



--
View this message in context: http://lucene.472066.n3.nabble.com/How-fast-indexing-tp4264994p4265047.html
Sent from the Solr - User mailing list archive at Nabble.com.

Re: How fast indexing?

Posted by fabigol <fa...@vialtis.com>.
thank for your response.
soir worked on a server with 6CPU and 10 Go Memory Ram
We receive some data evrytime. Now, we do indexing 2 times per day
the database contains 5 tables (18k row, 3times 300k row and one of 6
millions rows)
The indexation was done in 6 hours.
I didn't modify the original solrconfig.xml file
I'm going to do a incremental indexation.




--
View this message in context: http://lucene.472066.n3.nabble.com/How-fast-indexing-tp4264994p4265046.html
Sent from the Solr - User mailing list archive at Nabble.com.

Re: How fast indexing?

Posted by Erick Erickson <er...@gmail.com>.
My guess is that Solr isn't doing much and DIH is
taking much more time to get the data from
the database than Solr is using to index it.
DIH can have some tricky bits to get it to be
fast. Among them are:

1> caching certain table results
2> batching the rows returned from the DB
3> ??? I'll leave that part to DIH experts, I
tend to prefer using a JDBC driver and using
SolrJ...

Best,
Erick

On Sun, Mar 20, 2016 at 5:11 PM, Amit Jha <sh...@gmail.com> wrote:

> Hi All,
>
> In my case I am using DIH to index the data and Query is having 2 join
> statements. To index 70K documents it is taking 3-4Hours. Document size
> would be around 10-20KB. DB is MSSQL and using solr4.2.10 in cloud mode.
>
> Rgds
> AJ
>
> > On 21-Mar-2016, at 05:23, Erick Erickson <er...@gmail.com>
> wrote:
> >
> > In my experience, a majority of the time the bottleneck is in
> > the data acquisition, not the Solr indexing per-se. Take a look
> > at the CPU utilization on Solr, if it's not running very heavy,
> > then you need to look upstream.
> >
> > You haven't told us anything about _how_ you're indexing.
> > SolrJ? DIH? Something from some other party? so it's hard to
> > say much useful.
> >
> > You might review:
> >
> > http://wiki.apache.org/solr/UsingMailingLists
> >
> > Best,
> > Erick
> >
> > On Sun, Mar 20, 2016 at 3:31 PM, Nick Vasilyev <nick.vasilyev1@gmail.com
> >
> > wrote:
> >
> >> There can be a lot of factors, can you provide a bit of additional
> >> information to get started?
> >>
> >> - How many items are you indexing per second?
> >> - How does the indexing process look like?
> >> - How large is each item?
> >> - What hardware are you using?
> >> - How is your Solr set up? JVM memory, collection layout, etc...
> >> - What is your current commit frequency?
> >> - What is the query volume while you are indexing?
> >>
> >> On Sun, Mar 20, 2016 at 6:25 PM, fabigol <fa...@vialtis.com>
> >> wrote:
> >>
> >>> hi,
> >>> i have a soir project where i do the indexing since a database postgre.
> >>> the indexation is very long.
> >>> How i can accelerate it.
> >>> I can modify autocommit in the file solrconfig.xml?
> >>> someone has some ideas. I looking on google but I found little
> >>> help me please
> >>>
> >>>
> >>>
> >>>
> >>> --
> >>> View this message in context:
> >>> http://lucene.472066.n3.nabble.com/How-fast-indexing-tp4264994.html
> >>> Sent from the Solr - User mailing list archive at Nabble.com.
> >>
>

Re: How fast indexing?

Posted by Amit Jha <sh...@gmail.com>.
Hi All,

In my case I am using DIH to index the data and Query is having 2 join statements. To index 70K documents it is taking 3-4Hours. Document size would be around 10-20KB. DB is MSSQL and using solr4.2.10 in cloud mode.

Rgds
AJ

> On 21-Mar-2016, at 05:23, Erick Erickson <er...@gmail.com> wrote:
> 
> In my experience, a majority of the time the bottleneck is in
> the data acquisition, not the Solr indexing per-se. Take a look
> at the CPU utilization on Solr, if it's not running very heavy,
> then you need to look upstream.
> 
> You haven't told us anything about _how_ you're indexing.
> SolrJ? DIH? Something from some other party? so it's hard to
> say much useful.
> 
> You might review:
> 
> http://wiki.apache.org/solr/UsingMailingLists
> 
> Best,
> Erick
> 
> On Sun, Mar 20, 2016 at 3:31 PM, Nick Vasilyev <ni...@gmail.com>
> wrote:
> 
>> There can be a lot of factors, can you provide a bit of additional
>> information to get started?
>> 
>> - How many items are you indexing per second?
>> - How does the indexing process look like?
>> - How large is each item?
>> - What hardware are you using?
>> - How is your Solr set up? JVM memory, collection layout, etc...
>> - What is your current commit frequency?
>> - What is the query volume while you are indexing?
>> 
>> On Sun, Mar 20, 2016 at 6:25 PM, fabigol <fa...@vialtis.com>
>> wrote:
>> 
>>> hi,
>>> i have a soir project where i do the indexing since a database postgre.
>>> the indexation is very long.
>>> How i can accelerate it.
>>> I can modify autocommit in the file solrconfig.xml?
>>> someone has some ideas. I looking on google but I found little
>>> help me please
>>> 
>>> 
>>> 
>>> 
>>> --
>>> View this message in context:
>>> http://lucene.472066.n3.nabble.com/How-fast-indexing-tp4264994.html
>>> Sent from the Solr - User mailing list archive at Nabble.com.
>> 

Re: How fast indexing?

Posted by Erick Erickson <er...@gmail.com>.
In my experience, a majority of the time the bottleneck is in
the data acquisition, not the Solr indexing per-se. Take a look
at the CPU utilization on Solr, if it's not running very heavy,
then you need to look upstream.

You haven't told us anything about _how_ you're indexing.
SolrJ? DIH? Something from some other party? so it's hard to
say much useful.

You might review:

http://wiki.apache.org/solr/UsingMailingLists

Best,
Erick

On Sun, Mar 20, 2016 at 3:31 PM, Nick Vasilyev <ni...@gmail.com>
wrote:

> There can be a lot of factors, can you provide a bit of additional
> information to get started?
>
> - How many items are you indexing per second?
> - How does the indexing process look like?
> - How large is each item?
> - What hardware are you using?
> - How is your Solr set up? JVM memory, collection layout, etc...
> - What is your current commit frequency?
> - What is the query volume while you are indexing?
>
> On Sun, Mar 20, 2016 at 6:25 PM, fabigol <fa...@vialtis.com>
> wrote:
>
> > hi,
> > i have a soir project where i do the indexing since a database postgre.
> > the indexation is very long.
> > How i can accelerate it.
> > I can modify autocommit in the file solrconfig.xml?
> > someone has some ideas. I looking on google but I found little
> > help me please
> >
> >
> >
> >
> > --
> > View this message in context:
> > http://lucene.472066.n3.nabble.com/How-fast-indexing-tp4264994.html
> > Sent from the Solr - User mailing list archive at Nabble.com.
> >
>

Re: How fast indexing?

Posted by Nick Vasilyev <ni...@gmail.com>.
There can be a lot of factors, can you provide a bit of additional
information to get started?

- How many items are you indexing per second?
- How does the indexing process look like?
- How large is each item?
- What hardware are you using?
- How is your Solr set up? JVM memory, collection layout, etc...
- What is your current commit frequency?
- What is the query volume while you are indexing?

On Sun, Mar 20, 2016 at 6:25 PM, fabigol <fa...@vialtis.com> wrote:

> hi,
> i have a soir project where i do the indexing since a database postgre.
> the indexation is very long.
> How i can accelerate it.
> I can modify autocommit in the file solrconfig.xml?
> someone has some ideas. I looking on google but I found little
> help me please
>
>
>
>
> --
> View this message in context:
> http://lucene.472066.n3.nabble.com/How-fast-indexing-tp4264994.html
> Sent from the Solr - User mailing list archive at Nabble.com.
>