Re: solr indexing not working when i try to insert 1000000 rows but works fine when i try to index 400000 rows or below
Hi Ahmet, Thanks a ton. You were absolutely right the moment i added the line batchsize=-1 it worked . Thank you so much its been 7 days and i just could not figure out what the issue was. Its working like a charm now. Thanks Again regards Madhav Bahuguna On Tue, Jun 3, 2014 at 5:28 PM, Ahmet Arslan wrote: > Hi Madhav, > > Just a guess, try using batchSize="-1" > > batchSize="-1"/> > > Ahmet > > > On Tuesday, June 3, 2014 12:48 PM, madhav bahuguna < > madhav.bahug...@gmail.com> wrote: > HI > > iam using solr 4.7.1 and trying to do a full import.My data source is a > table in mysql. It has 1000 rows and 20 columns. > > Whenever iam trying to do a full import solr stops responding. But when i > try to do a import with a limit of 40 or less it works fine. > > If i try to import more than this solr wont index the result it either > stops responding or will show "indexing failed". In the error log it says > "Unable to execute query".But i dont understand how is the query running > fine for lesser number of records but fails when i run more number of > records > > My system config are follows > > CPU-i7 Ram -6Gb OS-64 bit windows 7 I am not able to figure out what the > problem is ,i have tried increasing the max_allowed_packet to 1000M and > even java heap size. > > please help thanks in advance > > *This is the error code* > > `Exception while processing: playername document : > SolrInputDocument(fields: > []):org.apache.solr.handler.dataimport.DataImportHandlerException: Unable > to execute query: SELECT > player_id,firstname,lastname,value1,value2,value3,value4,value5,value6, > value7,value8,value9,value10, value11,value18,value19,value20, country_id, > playername_modtime,player_flag from playername WHERE 'true' != 'false' OR > playername.playername_modtime > '2014-05-23 10:38:56' Processing Document # > 1 at > > org.apache.solr.handler.dataimport.DataImportHandlerException.wrapAndThrow(DataImportHandlerException.java:71) > at > > org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator.(JdbcDataSource.java:281) > at > > org.apache.solr.handler.dataimport.JdbcDataSource.getData(JdbcDataSource.java:238) > at > > org.apache.solr.handler.dataimport.JdbcDataSource.getData(JdbcDataSource.java:42) > at > > org.apache.solr.handler.dataimport.SqlEntityProcessor.initQuery(SqlEntityProcessor.java:59) > at > > org.apache.solr.handler.dataimport.SqlEntityProcessor.nextRow(SqlEntityProcessor.java:73) > at > > org.apache.solr.handler.dataimport.EntityProcessorWrapper.nextRow(EntityProcessorWrapper.java:243) > at > > org.apache.solr.handler.dataimport.DocBuilder.buildDocument(DocBuilder.java:477) > at > > org.apache.solr.handler.dataimport.DocBuilder.buildDocument(DocBuilder.java:416) > at > > org.apache.solr.handler.dataimport.DocBuilder.doFullDump(DocBuilder.java:331) > at > org.apache.solr.handler.dataimport.DocBuilder.execute(DocBuilder.java:239) > at > > org.apache.solr.handler.dataimport.DataImporter.doFullImport(DataImporter.java:411) > at > > org.apache.solr.handler.dataimport.DataImporter.runCmd(DataImporter.java:483) > at > > org.apache.solr.handler.dataimport.DataImporter$1.run(DataImporter.java:464) > Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: > Communications link failure > > The last packet successfully received from the server was 130,037 > milliseconds ago. The last packet sent successfully to the server was > 130,038 milliseconds ago. at > sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at > sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source) at > sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source) > at java.lang.reflect.Constructor.newInstance(Unknown Source) at > com.mysql.jdbc.Util.handleNewInstance(Util.java:409) at > com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1127) > at com.mysql.jdbc.MysqlIO.nextRowFast(MysqlIO.java:2288) at > com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:2044) at > com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:3549) at > com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:489) at > com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:3240) at > com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:2411) at > com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2834) at > com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2832) at > com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2781) at > com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:908) at > com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:788) at > > org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator.(JdbcDataSource.java:274) > ... 12 more Caused by: java.io.EOFException: Can not read response from > server. Expected to read 6 bytes, read 4 bytes before connection was > unexpectedly lost. at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3161) > at com.mysql.jdbc.MysqlIO.nextRowFast(MysqlIO.java:2269) ... 23 more > 5/23/2014 8:32:18 PM ER
Re: solr indexing not working when i try to insert 1000000 rows but works fine when i try to index 400000 rows or below
On 6/3/2014 12:00 AM, madhav bahuguna wrote: > iam using solr 4.7.1 and trying to do a full import.My data source is a > table in mysql. It has 1000 rows and 20 columns. > > Whenever iam trying to do a full import solr stops responding. But when i > try to do a import with a limit of 40 or less it works fine. > Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: > Communications link failure > > The last packet successfully received from the server was 130,037 > milliseconds ago. The last packet sent successfully to the server was > 130,038 milliseconds ago. at If you aren't already using batchSize=-1 as Ahmet mentioned, you should be ... but I suspect that your actual problem is different, and has to do with multiple simultaneous merge tiers. When indexing continues long enough, you end up with multiple simultaneous merges scheduled. As long as there are more than the configured limit of merges (default 2) on the schedule, all indexing will stop. If this continues for long enough, JDBC (or MySQL itself) will close the database connection because of inactivity. You need to increase indexConfig/mergeScheduler/maxMergeCount in solrconfig.xml. Here's my indexConfig settings. The important part for the problem I have described is the mergeScheduler config: 35 35 105 1 6 48 false I filed this issue to deal with this problem before people run into it: https://issues.apache.org/jira/browse/LUCENE-5705 I am still working out the best way to tackle the problem with other committers. Thanks, Shawn
Re: solr indexing not working when i try to insert 1000000 rows but works fine when i try to index 400000 rows or below
Hi Madhav, Just a guess, try using batchSize="-1" Ahmet On Tuesday, June 3, 2014 12:48 PM, madhav bahuguna wrote: HI iam using solr 4.7.1 and trying to do a full import.My data source is a table in mysql. It has 1000 rows and 20 columns. Whenever iam trying to do a full import solr stops responding. But when i try to do a import with a limit of 40 or less it works fine. If i try to import more than this solr wont index the result it either stops responding or will show "indexing failed". In the error log it says "Unable to execute query".But i dont understand how is the query running fine for lesser number of records but fails when i run more number of records My system config are follows CPU-i7 Ram -6Gb OS-64 bit windows 7 I am not able to figure out what the problem is ,i have tried increasing the max_allowed_packet to 1000M and even java heap size. please help thanks in advance *This is the error code* `Exception while processing: playername document : SolrInputDocument(fields: []):org.apache.solr.handler.dataimport.DataImportHandlerException: Unable to execute query: SELECT player_id,firstname,lastname,value1,value2,value3,value4,value5,value6, value7,value8,value9,value10, value11,value18,value19,value20, country_id, playername_modtime,player_flag from playername WHERE 'true' != 'false' OR playername.playername_modtime > '2014-05-23 10:38:56' Processing Document # 1 at org.apache.solr.handler.dataimport.DataImportHandlerException.wrapAndThrow(DataImportHandlerException.java:71) at org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator.(JdbcDataSource.java:281) at org.apache.solr.handler.dataimport.JdbcDataSource.getData(JdbcDataSource.java:238) at org.apache.solr.handler.dataimport.JdbcDataSource.getData(JdbcDataSource.java:42) at org.apache.solr.handler.dataimport.SqlEntityProcessor.initQuery(SqlEntityProcessor.java:59) at org.apache.solr.handler.dataimport.SqlEntityProcessor.nextRow(SqlEntityProcessor.java:73) at org.apache.solr.handler.dataimport.EntityProcessorWrapper.nextRow(EntityProcessorWrapper.java:243) at org.apache.solr.handler.dataimport.DocBuilder.buildDocument(DocBuilder.java:477) at org.apache.solr.handler.dataimport.DocBuilder.buildDocument(DocBuilder.java:416) at org.apache.solr.handler.dataimport.DocBuilder.doFullDump(DocBuilder.java:331) at org.apache.solr.handler.dataimport.DocBuilder.execute(DocBuilder.java:239) at org.apache.solr.handler.dataimport.DataImporter.doFullImport(DataImporter.java:411) at org.apache.solr.handler.dataimport.DataImporter.runCmd(DataImporter.java:483) at org.apache.solr.handler.dataimport.DataImporter$1.run(DataImporter.java:464) Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure The last packet successfully received from the server was 130,037 milliseconds ago. The last packet sent successfully to the server was 130,038 milliseconds ago. at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source) at java.lang.reflect.Constructor.newInstance(Unknown Source) at com.mysql.jdbc.Util.handleNewInstance(Util.java:409) at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1127) at com.mysql.jdbc.MysqlIO.nextRowFast(MysqlIO.java:2288) at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:2044) at com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:3549) at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:489) at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:3240) at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:2411) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2834) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2832) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2781) at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:908) at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:788) at org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator.(JdbcDataSource.java:274) ... 12 more Caused by: java.io.EOFException: Can not read response from server. Expected to read 6 bytes, read 4 bytes before connection was unexpectedly lost. at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3161) at com.mysql.jdbc.MysqlIO.nextRowFast(MysqlIO.java:2269) ... 23 more 5/23/2014 8:32:18 PM ERROR DataImporter Full Import failed:java.lang.RuntimeException: java.lang.RuntimeException: org.apache.solr.handler.dataimport.DataImportHandlerException: Unable to execute query: SELECT player_id,firstname,lastname,value1,value2,value3,value4,value5,value6, value7,value8,value9,value10, value11,value18,value19,value20, country_id, playername_modtime,player_flag from playername WHERE 'true' != 'false' OR playername.playername_modtime > '2014-05-23 10:38:56' Processing Document # 1 Last Check: 5/23/2014 8:36:34 PM` One more thing i have noticed
solr indexing not working when i try to insert 1000000 rows but works fine when i try to index 400000 rows or below
HI iam using solr 4.7.1 and trying to do a full import.My data source is a table in mysql. It has 1000 rows and 20 columns. Whenever iam trying to do a full import solr stops responding. But when i try to do a import with a limit of 40 or less it works fine. If i try to import more than this solr wont index the result it either stops responding or will show "indexing failed". In the error log it says "Unable to execute query".But i dont understand how is the query running fine for lesser number of records but fails when i run more number of records My system config are follows CPU-i7 Ram -6Gb OS-64 bit windows 7 I am not able to figure out what the problem is ,i have tried increasing the max_allowed_packet to 1000M and even java heap size. please help thanks in advance *This is the error code* `Exception while processing: playername document : SolrInputDocument(fields: []):org.apache.solr.handler.dataimport.DataImportHandlerException: Unable to execute query: SELECT player_id,firstname,lastname,value1,value2,value3,value4,value5,value6, value7,value8,value9,value10, value11,value18,value19,value20, country_id, playername_modtime,player_flag from playername WHERE 'true' != 'false' OR playername.playername_modtime > '2014-05-23 10:38:56' Processing Document # 1 at org.apache.solr.handler.dataimport.DataImportHandlerException.wrapAndThrow(DataImportHandlerException.java:71) at org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator.(JdbcDataSource.java:281) at org.apache.solr.handler.dataimport.JdbcDataSource.getData(JdbcDataSource.java:238) at org.apache.solr.handler.dataimport.JdbcDataSource.getData(JdbcDataSource.java:42) at org.apache.solr.handler.dataimport.SqlEntityProcessor.initQuery(SqlEntityProcessor.java:59) at org.apache.solr.handler.dataimport.SqlEntityProcessor.nextRow(SqlEntityProcessor.java:73) at org.apache.solr.handler.dataimport.EntityProcessorWrapper.nextRow(EntityProcessorWrapper.java:243) at org.apache.solr.handler.dataimport.DocBuilder.buildDocument(DocBuilder.java:477) at org.apache.solr.handler.dataimport.DocBuilder.buildDocument(DocBuilder.java:416) at org.apache.solr.handler.dataimport.DocBuilder.doFullDump(DocBuilder.java:331) at org.apache.solr.handler.dataimport.DocBuilder.execute(DocBuilder.java:239) at org.apache.solr.handler.dataimport.DataImporter.doFullImport(DataImporter.java:411) at org.apache.solr.handler.dataimport.DataImporter.runCmd(DataImporter.java:483) at org.apache.solr.handler.dataimport.DataImporter$1.run(DataImporter.java:464) Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure The last packet successfully received from the server was 130,037 milliseconds ago. The last packet sent successfully to the server was 130,038 milliseconds ago. at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source) at java.lang.reflect.Constructor.newInstance(Unknown Source) at com.mysql.jdbc.Util.handleNewInstance(Util.java:409) at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1127) at com.mysql.jdbc.MysqlIO.nextRowFast(MysqlIO.java:2288) at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:2044) at com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:3549) at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:489) at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:3240) at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:2411) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2834) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2832) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2781) at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:908) at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:788) at org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator.(JdbcDataSource.java:274) ... 12 more Caused by: java.io.EOFException: Can not read response from server. Expected to read 6 bytes, read 4 bytes before connection was unexpectedly lost. at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3161) at com.mysql.jdbc.MysqlIO.nextRowFast(MysqlIO.java:2269) ... 23 more 5/23/2014 8:32:18 PM ERROR DataImporter Full Import failed:java.lang.RuntimeException: java.lang.RuntimeException: org.apache.solr.handler.dataimport.DataImportHandlerException: Unable to execute query: SELECT player_id,firstname,lastname,value1,value2,value3,value4,value5,value6, value7,value8,value9,value10, value11,value18,value19,value20, country_id, playername_modtime,player_flag from playername WHERE 'true' != 'false' OR playername.playername_modtime > '2014-05-23 10:38:56' Processing Document # 1 Last Check: 5/23/2014 8:36:34 PM` One more thing i have noticed is that when i try to do a full import with just 2 columns and increase the jvm size(java -Xmx1024m -jar start.jar) it w
