使用solr的DIHandler 构建mysql大表全量索引,内存溢出问题的解决方法

solr官方给出的解决方式是:DataImportHandler is designed to stream row one-by-one. It passes a fetch size value (default: 500) to Statement#setFetchSize which some drivers do not honor. For MySQL, add batchSize property to dataSource configu

solr官方给出的解决方式是:

DataImportHandler is designed to stream row one-by-one. It passes a fetch size value (default: 500) to Statement#setFetchSize which some drivers do not honor. For MySQL,add batchSize property to dataSource configuration with value -1Should look like:

<dataSource type=”JdbcDataSource” name=”ds-2″ driver=”com.mysql.jdbc.Driver” url=”jdbc:mysql://localhost:8889/mysqldatabase” batchSize=”-1″ user=”root” password=”root”/>

说明:DataImportHandler 设计是支持按行获取的。它通过Statement#setFetchSize来设置每次获取的数量,默认是500个。然而一些驱动不支持设置fetchSize。对mysql来说,传递fetchSize属性值-1到Datasource配置中。它将将Integer.MIN_VALUE(-231,-2147483648 [0x80000000])传给驱动作为fetchsize,此时确保大表不会造成大表移除。

mysql官方给出的解释是:

By default,ResultSets are completely retrieved and stored in memory. In most cases this is the most efficient way to operate and,due to the design of the MySQL network protocol,is easier to implement. If you are working with ResultSets that have a large number of rows or large values and cannot allocate heap space in your JVM for the memory required,<span style=”color: #000000;”> you can tell the driver to stream the results back one row at a time.

To enable this functionality,<span style=”color: #000000;”> create a Statement instance in the following manner:

<span style=”color: #ff0000;”>stmt <span style=”color: #ff0000;”>= conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);
<span style=”color: #0000ff;”>The combination of a forward-only,read-only result set,with a fetch size of Integer.MIN_VALUE serves as a signal to the driver to stream result sets row-by-row. After this,<span style=”color: #000000;”><span style=”color: #0000ff;”> any result sets created with the statement will be retrieved row-by-row.

There are some caveats with this approach. You must read all of the rows in the result set (or close it) before you can issue any other queries on the connection,<span style=”color: #000000;”> or an exception will be thrown.

The earliest the locks these statements hold can be released (whether they be MyISAM table-level locks or row-level locks in some other storage engine such as InnoDB) is when the statement completes.

If the statement is within scope of a transaction,then locks are released when the transaction completes (which implies that the statement needs to complete first). As with most other databases,<span style=”color: #000000;”> statements are not complete until all the results pending on the statement are read or the active result set for the statement is closed.

Therefore,if using streaming results,process them as quickly as possible if you want to maintain concurrent access to the tables referenced by the statement producing the result set.

通过联合使用forward-only,read-only resultSet和fetchsize值为Integer.MIN_VALUE作为驱动一行行获取结果流的信号。设置完以后,所有statement创建的resultSet将会一行行的获取结果集。

参考文献:

【1】https://wiki.apache.org/solr/DataImportHandlerFaq

【2】http://dev.mysql.com/doc/connector-j/en/connector-j-reference-implementation-notes.html

作者: dawei

【声明】:永州站长网内容转载自互联网,其相关言论仅代表作者个人观点绝非权威,不代表本站立场。如您发现内容存在版权问题,请提交相关链接至邮箱:bqsm@foxmail.com,我们将及时予以处理。

为您推荐

联系我们

联系我们

0577-28828765

在线咨询: QQ交谈

邮箱: xwei067@foxmail.com

工作时间:周一至周五,9:00-17:30,节假日休息

返回顶部