RE: DIH, Full-Import, DB and Performance.
my batchSize is -1 and the load ist to big for us. why i should increase it ? what is a normal serverload ? our server is a fast server. 4 cores 3 GB Ram but we dont want a serverload from over 2 when index a starts. -- View this message in context: http://lucene.472066.n3.nabble.com/DIH-Full-Import-DB-and-Performance-tp861068p864297.html Sent from the Solr - User mailing list archive at Nabble.com.
Re: DIH, Full-Import, DB and Performance.
do you think that the option responseBuffer=adaptive should solve my problem ? From DIH FAQ ...: I'm using DataImportHandler with MS SQL Server database with sqljdbc driver. DataImportHandler is going out of memory. I tried adjustng the batchSize values but they don't seem to make any difference. How do I fix this? There's a connection property called responseBuffering in the sqljdbc driver whose default value is full which causes the entire result set to be fetched. See http://msdn.microsoft.com/en-us/library/ms378988.aspx for more details. You can set this property to adaptive to keep the driver from getting everything into memory. Connection properties like this can be set as an attribute (responseBuffering=adaptive) in the dataSource configuration OR directly in the jdbc url specified in DataImportHandler's dataSource configuration. -- View this message in context: http://lucene.472066.n3.nabble.com/DIH-Full-Import-DB-and-Performance-tp861068p861134.html Sent from the Solr - User mailing list archive at Nabble.com.
Re: DIH, Full-Import, DB and Performance.
another question we have sometimes a load from over 3,.. on our server and only from different tomcat instances. no import is running and not much requests send to solr. we have 4 cores running for our search. 2 cores have each 4 Million doc's and the other two cores have each around 200.000 doc's. why is the load so much -- View this message in context: http://lucene.472066.n3.nabble.com/DIH-Full-Import-DB-and-Performance-tp861068p861262.html Sent from the Solr - User mailing list archive at Nabble.com.
RE: DIH, Full-Import, DB and Performance.
The settings and defaults will depend on which version of SQL Server you are using and which version of the JDBC driver. The default for resonseBuffering was changed to adaptive after version 1.2 so unless you are using 1.2 or earlier you don't need to set it to adaptive. Also if I remember correctly the batchsize will only take affect if you are using cursors, the default is for all data to be sent to the client (selectMethod is direct). Using the default settings for the MS sqljdbc driver caused locking issues in our database. As soon as the full import started shared locks would be set on all rows and wouldn't be removed until all the data had been sent, which for us would be around 30 minutes. During that time no updates could get an exclusive lock which of course led to huge problems. Setting selectMethod=cursor solved the problem for us although it does slow down the full import. Another option that worked for us was to not set the selectMethod and set readOnly=true, but be sure you understand the implications. This causes all data to be sent to the client (which is the default), giving maximum performance, and causes no locks to be set which resolves the other issues. However, this sets transaction isolation to TRANSACTION_READ_UNCOMMITTED which will cause the select statement to ignore any locks when getting data so the consistency of the data cannot be guaranteed, which may or may not be an issue depending on your particular situation. Colin. -Original Message- From: stockii [mailto:st...@shopgate.com] Sent: Tuesday, June 01, 2010 7:44 AM To: solr-user@lucene.apache.org Subject: Re: DIH, Full-Import, DB and Performance. do you think that the option responseBuffer=adaptive should solve my problem ? From DIH FAQ ...: I'm using DataImportHandler with MS SQL Server database with sqljdbc driver. DataImportHandler is going out of memory. I tried adjustng the batchSize values but they don't seem to make any difference. How do I fix this? There's a connection property called responseBuffering in the sqljdbc driver whose default value is full which causes the entire result set to be fetched. See http://msdn.microsoft.com/en-us/library/ms378988.aspx for more details. You can set this property to adaptive to keep the driver from getting everything into memory. Connection properties like this can be set as an attribute (responseBuffering=adaptive) in the dataSource configuration OR directly in the jdbc url specified in DataImportHandler's dataSource configuration. -- View this message in context: http://lucene.472066.n3.nabble.com/DIH- Full-Import-DB-and-Performance-tp861068p861134.html Sent from the Solr - User mailing list archive at Nabble.com.
RE: DIH, Full-Import, DB and Performance.
thx for the reply =) i try out selectMethod=cursor but the load of the server is going bigger and bigger during a import =( selectMethod=cursor only solve the problem with the locking ? right ? -- View this message in context: http://lucene.472066.n3.nabble.com/DIH-Full-Import-DB-and-Performance-tp861068p862043.html Sent from the Solr - User mailing list archive at Nabble.com.
RE: DIH, Full-Import, DB and Performance.
Performance is dependent on your server/data and the batchsize. To reduce the server load experiment with different batchsize settings. The higher the batch size the faster the import and the higher your SQL Server load will be. Try starting with a small batch and then gradually increasing it. Colin. -Original Message- From: stockii [mailto:st...@shopgate.com] Sent: Tuesday, June 01, 2010 12:31 PM To: solr-user@lucene.apache.org Subject: RE: DIH, Full-Import, DB and Performance. thx for the reply =) i try out selectMethod=cursor but the load of the server is going bigger and bigger during a import =( selectMethod=cursor only solve the problem with the locking ? right ? -- View this message in context: http://lucene.472066.n3.nabble.com/DIH- Full-Import-DB-and-Performance-tp861068p862043.html Sent from the Solr - User mailing list archive at Nabble.com.
