Mobilink replication to SAP IQ consolidate is very slow

[former_SQLA_member1695008]

Hi,

We have setup a Mobilink replication between an SAP SQL Anywhere as a remote database and an SAP IQ server as a consolidate database.

Upon initial synchronization, we noticed that the insert into SAP IQ was happening in a batch size of 10 rows. We understood that this was due to default batch size Mobilink uses. So, we tried to optimize the inserts into IQ by increasing the batch size by adding the -s option to the mlsrv17/mobilink server command. After adding this option, we noticed that the inserts into some tables were happening at a larger batch size but for some tables it was inserting one row at a time/batch size of 1 row. We couldn't figure out why Mobilink server was behaving this way for different tables.

We tried using transactional uploads by adding the -tu option to the mobilink client dbmlsync and added option -tx for the Mobilink server. We saw no difference with this approach and Mobilink server was still doing inserts into some large tables in one row batches.

We don't see any option to do bulk insert data into SAP IQ via Mobilink. Any changes we do with batch sizes on both the Mobilink client or Mobilink server did not yield any noticeable performance gain.

Environment details below:

Remote Database: Operating System: CentOS 8.2.2004 Database: SAP SQL Anywhere 17.0.10.6155 Platform: Azure cloud RAM: 64 GB CPU: 16

Consolidate Database: Operating System: CentOS 8.3.2011 Database: SAP IQ 16.1 SP04.08 Platform: Azure cloud RAM: 256 GB CPU: 8

Please let us know if there is a way to make Mobilink use bulk insert/load commands into SAP IQ consolidate database instead of batch inserts.

[regdomaratzki]

The -s switch should be the switch that will increase the number of number of rows in each bulk operation during upload. Transactional uploads at the remote site should only be used when you require transactions to be applied to the consolidated in the same order that they were applied at the remote database. While it was a good experiment to try, transactional uploads add a bit of overhead that I think will actually make this problem worse as opposed to better, so I would revert those changes.

Questions :

1. What exact version of IQ, MobiLink and the IQ ODBC driver are you using?
2. Were there any errors or warnings posted to the MobiLink log? The MobiLink will revert to single row uploads for a given table if there was a failure when applying a multi-row bulk operation.
3. Is is safe to assume that if you have set the -s switich to "X" that you are uploading more than "X" rows on a given table?

Thanks,
Reg

[former_SQLA_member1695008]

Thanks Reg for the quick reply.

Please find the requested information.

What exact version of IQ, MobiLink and the IQ ODBC driver are you using?

IQ - SAP IQ/16.1.040.1549/14760/P/SP04.08/Linux/Linux64 - x86_64 - 3.10.0-327/64bit/2020-11-24 00:59:00 Mobilink - Installed with SQL Anywhere 17.0.10.6160 on windows server 2016 IQ ODBA driver - IQ 16.1 SP04 on windows server 2016

Were there any errors or warnings posted to the MobiLink log? The MobiLink will revert to single row uploads for a given table if there was a failure when applying a multi-row bulk operation.

We were running with mininum verbosity to reduce performance issues, but we were getting a warning message about inserts running for a very long time for certain tables. This was beyond the the default threshold allowed by Mobilink server. These were the same tables for which insert was running one row at a time.

Is is safe to assume that if you have set the -s switich to "X" that you are uploading more than "X" rows on a given table?

Yes, we specified the batch size as 100000 and the new data that was added is more than this.

Please let me know if any additional information is required.

Thanks.

[regdomaratzki]

So the consolidated IQ database is running in the Azure cloud on CentOS, but the ML Server is running on Windows Server 2016.

Is the Windows machine running MobiLink also located in the Azure cloud as well? An ODBC connection from the ML Server through the network infrastructure that is the Azure cloud to the IQ Server is a recipe for slow performance, regardless of the number of rows you are trying to batch in the same operation.

Reg

[former_SQLA_member1695008]

Good morning Reg. Yes, all the servers(CentOS/Windows server 2016) are running on Azure cloud.

When we added the batch size -s 100000 some tables got loaded pretty fast but for some tables Mobilink chose to insert one row at a time into IQ. So, the performance of the insert into IQ was not same for all the tables. I am guessing there is something different about the IQ tables which I didn't figure out yet as I am new into SAP IQ.

[regdomaratzki]

We'd be interested in seeing what errors and warnings were posted in the MobiLink Log for the table that did not use bulk inserts.

I suspect "something" went wrong in the initial insert of 100,000 rows, causing the ML Server to revert to a savepoint, at which point ML inserts the rows one at a time. Knowing what that "something" was that went wrong will help us sort this out.

Reg

[Breck_Carter]

> I am new into SAP IQ

That's OK. You are probably also new to MobiLink, because you have not posted one single message from the MobiLink server log.

Reg has to be polite, I don't... SHOW US THE MOBILINK MESSAGES, that's where you will find the answer.

You may have to turn up the verbosity of the messages.

REM -vcefhikmnopqrRstuU  all options (some redundancy)
REM -vcefhkmnopstuU logs everything except data and remote ID
REM   -v+   Turn on all logging options that increase verbosity.
REM   -vc   Show scripts.
REM   -ve   Show system event scripts.
REM   -vf   Show first-read errors.
REM   -vh   Show the remote schema.
REM   -vi   Show uploaded column values.
REM   -vk   Show cache growth and shrinkage.
REM   -vm   Show the duration of each synch phase.
REM   -vn   Show row-count summaries. 
REM   -vo   Show SQL passthrough activity. 
REM   -vp   Show progress offsets. 
REM   -vq   VERBOSE - Show downloaded column values.
REM   -vr   VERBOSE - Show uploaded and downloaded column values.
REM   -vR   Show remote ID in each message.
REM   -vs   Show script names.
REM   -vt   Show the translated SQL scripts.
REM   -vu   Show undefined table scripts.
REM   -vU   Show user name in each message.

[former_SQLA_member1695008]

Hi Breck,

This was a POC. So, I am trying to get access back into the servers to be able to pull the Mobilink server logs or rerun the synchronization with the highest verbosity setting.

I will post the log contents once I get my access in place.

Thanks a ton.

[Breck_Carter]

FWIW one of the hallmarks of MobiLink is "centralized administration"... in practice that means the mlsrv17.exe -o diagnostic log text file is where you will find 99.9% of the information required to solve problems. The other 0.1% may be found in the dbmlsync.exe -o logs, but usually only when all contact is lost between remote and consolidated.

[Breck_Carter]

Tip: Never set -vq -vr in a production environment, not unless you are desperate and you don't mind filling up the disk drive 🙂