Teradata ODBC logging

msch · Post by **msch** » Fri Apr 23, 2010 7:52 am

Hi,

We currently have an Enterprise instance of HostMonitor installed at our company, that we use to monitor network connections to remote sites.

Here are the environment details:

- HostMonitor installed on a quad-CPU Windows 2003 Server, hosted in Europe
- ODBC logging setup to a Teradata DB, hosted in the US (130 msec network latency), through a Stored Procedure call (SP processing time takes around 100 msec)
- Teradata ODBC driver 13.00.00.02 (latest)
- Around 10 000 TCP tests, scheduled every 40 mins during GMT business days (12 hours window)

HostMonitor performance is fine (though GUI is slow, but still in acceptable range), between 1 & 2% CPU, with peaks at 5-6%.
Without ODBC logging, the app can perform around 5 tests/sec.
With ODBC logging enabled, log ATC is between 240 & 300 msec/sec depending on DB load, and the app runs around 3 tests/sec.
Doing a netstat, i only see one connection established to the DB.
No data loss, every test is indeed logged properly to the DB.

Now, looking inside the DB, i can see around 3 to 4 records are inserted every second, and monitoring the Stored Procedure calls confirms HostMonitor ATC counter.

The figures above seems to indicate ODBC calls to the DB are serialized, though the Teradata ODBC driver is indeed capable of running queries asynchronously...
Connection pooling is enabled as well.

You mentioned in another post that due to buggy ODBC implementations for other vendors, HostMonitor implements its own cache for ODBC logging...

Is this the reason for the above behavior ? wrong ODBC settings ?
faulty Teradata ODBC implementation (i seriously doubt that...) ?

I don't quite understand which component is at fault that would prevent concurrent calls to the SP...

KS-Soft · Post by **KS-Soft** » Fri Apr 23, 2010 10:52 am

Yes, if average logging time for single ODBC record is about 300ms, this can lead to problems when you use Full (or Reply) logging mode.
I assume Auditing Tool recommends you to change logging mode or use faster SQL server for logging?

This happens because HostMonitor uses single thread for logging and HostMonitor does not start new tests when it cannot log "old" records. Well, if HostMonitor cannot log test results at all, it will pause logging or switch to backup log and continue monitoring. While "slow" logging will slow down entire monitoring process.

Solutions?
H'm, you can use faster SQL server or better ODBC driver. Change logging mode (use Brief mode instead of Full) or logging method (Text, DBF or HTML file instead of SQL database).
We can add option to use multithreaded code for logging. It may work fine with some ODBC drivers and cause problems with other

- HostMonitor installed on a quad-CPU Windows 2003 Server, hosted in Europe
- ODBC logging setup to a Teradata DB, hosted in the US (130 msec network latency), through a Stored Procedure call (SP processing time takes around 100 msec)

We cannot recommend such configuration. Its much better to use SQL server installed in the same LAN with HostMonitor.
If you cannot move SQL server or install another one in Europe and you need to store full logging in database, then I would recommend to move HostMonitor to US and use RMA installed in Europe to perform tests.

Regards
Alex

msch · Post by **msch** » Fri Apr 23, 2010 1:27 pm

Solutions?
H'm, you can use faster SQL server or better ODBC driver.

The ODBC driver is not at fault here, stored procedure processing time is identical directly from Teradata SQL client.
And Teradata IS the fastest RDBMS for that volume of data

Change logging mode (use Brief mode instead of Full) or logging method (Text, DBF or HTML file instead of SQL database).

My business case is to store every test results, including reply integer, in order to 1. display them real-time 2. build trends and metrics. Pretty much the same that HostMonitor does internally, though i need to store them in an analytic DB to further process them and share the results with other apps.
Considering those 2 requirements, am afraid full logging to the DB is my only option.

We cannot recommend such configuration. Its much better to use SQL server installed in the same LAN with HostMonitor.
If you cannot move SQL server or install another one in Europe and you need to store full logging in database, then I would recommend to move HostMonitor to US and use RMA installed in Europe to perform tests.

I built my initial system using that approach, a dedicated Oracle DB sitting next to the HostMonitor server, however Enterprise Architects made it mandatory to use a global DB as back-end.

I understand your point, but even though i could manage to reduce network latency using either of those two approaches, similar problem could still occur if i was to 'beef-up' my stored procedure to implement more complex business logic, hence increasing its processing time...

If tomorrow i make this HostMonitor architecture global, i won't have 10000 systems to monitor every hour, but possibly 30000 to 50000...
Even using 4 or more RMAs agents, i would still have a requirement for a single instance of HostMonitor to call the stored procedure 8 or 10 times a second...

We can add option to use multithreaded code for logging. It may work fine with some ODBC drivers and cause problems with other

Considering the above, such an option would be more than handy.
I'd be surprised if i was the only user facing such an issue actually...

KS-Soft · Post by **KS-Soft** » Fri Apr 23, 2010 1:50 pm

The ODBC driver is not at fault here, stored procedure processing time is identical directly from Teradata SQL client.

Yes, I understand. Faster in your case means move server to local network or move HostMonitor to another network.

If tomorrow i make this HostMonitor architecture global, i won't have 10000 systems to monitor every hour, but possibly 30000 to 50000...
Even using 4 or more RMAs agents, i would still have a requirement for a single instance of HostMonitor to call the stored procedure 8 or 10 times a second...

Yes. But its not a problem. Each test probe is performed by separate thread, HostMonitor can perform 100 tests per second. So it will work faster when SQL server will be located in the same LAN (or the same server).

Considering the above, such an option would be more than handy.
I'd be surprised if i was the only user facing such an issue actually...

May be we can implement this option in version 8.60 but I am not sure

Regards
Alex

msch · Post by **msch** » Fri Apr 23, 2010 2:10 pm

Yes. But its not a problem. Each test probe is performed by separate thread, HostMonitor can perform 100 tests per second. So it will work faster when SQL server will be located in the same LAN (or the same server).

True, i am sure HM can perform way more than my requirements in terms of # of tests per seconds.

Now, still, 100 tests a second would mean 10 msec for each call to the DB in order for the logging not to impact overall application performance...

You mentioned in other posts that using a dedicated DB as backend for logging was on your roadmap, i don't see how single threaded full logging would then not become obviously a huge bottleneck for overall application performance and scalability...

My point here is just that i consider this issue as a real limitation of the tool as of now, and would be great if this feature could be pushed up in the roadmap todo list

Thanks & Regards

KS-Soft · Post by **KS-Soft** » Fri Apr 23, 2010 2:37 pm

Now, still, 100 tests a second would mean 10 msec for each call to the DB in order for the logging not to impact overall application performance...
You mentioned in other posts that using a dedicated DB as backend for logging was on your roadmap, i don't see how single threaded full logging would then not become obviously a huge bottleneck for overall application performance and scalability...

According to our tests SQL server installed locally (e.g. Postgree or MS SQL server) processes simple Insert query within 2-4ms. 100 tests per second -> 200-400ms for logging. Not a problem.

Regards
Alex