Hostmonitor Stops Recording to Database

jromariz · Post by **jromariz** » Thu Feb 04, 2010 7:09 am

Hi,

We use hostmonitor mainly to capture and write statistc data about computers and processes to our SQL database. Some days ago we started to notice some strange gaps in databse records whre no information is written. As the machine where hostmonitor was running was too old we supected this could be the cause. We transfered hostmonitor to a new Blade machine with 8 cores and 16Gb of RAM. Now hostmonitor is using only 3 to 4% of CPU and less then 1Gb of RAM. The only problem is that the problem is still happening. No errors on system log or any other clue about the cause, it only stops writting tests result to database and some minutes latter it starts writing again.

We are using the last available version. Any ideas on how to debug this?

Thanks for helping.

Jromariz.

jromariz · Post by **jromariz** » Thu Feb 04, 2010 9:20 am

Another info: everytime this happens the hostmon application hangs and cannot be accessed even from the machine console. It seems to be running but it is not possible to access it.

Thanks,

Jromariz.

KS-Soft · Post by **KS-Soft** » Thu Feb 04, 2010 11:32 am

Looks like some problem related to ODBC driver.
What SQL server do you use? ODBC driver?

Regards
Alex

jromariz · Post by **jromariz** » Thu Feb 04, 2010 11:51 am

Hi Alex,

Thanks for helping. I´m using Microsoft SQL. Another information: I started watchdog in another computer to monitor Hostmon. When it stopped to write to the database, watchdog alerted that the connection to hostmon was lost. Some minutes latter it started to write to database again and the watchdog connection was stablished again.

Thanks for helping.

Regards,

Jromariz.

KS-Soft · Post by **KS-Soft** » Thu Feb 04, 2010 1:14 pm

Well, if HostMonitor does not hung forever, this means ODBC driver just too busy waiting for some results from SQL server. Probably there are some connection issues or busy server...
We plan to change our code a little so ODBC problems will have less impact on HostMonitor GUI but we cannot fix directly any problems caused by ODBC driver itself or any network problems...

If you do not see errors in system log file, this means ODBC driver does not return any error code to HostMonitor.
You may try to set lower logging timeout (option located in the Options dialog) however some ODBC drivers ignore this parameter.
Also you may try to use PostgreSQL instead of MS SQL, it works pretty good in our environment. Actually MS SQL 2005 and 2008 work good as well.
While Oracle is relatively slow; MySQL clients are buggy.

Regards
Alex

jromariz · Post by **jromariz** » Fri Feb 05, 2010 5:32 am

Ok Alex.

Is there any possibility that, in the future, hostmon will be able to write directly to SQL database?

Thanks again and regards.

Jromariz.

jromariz · Post by **jromariz** » Fri Feb 05, 2010 6:09 am

Alex,

Searching the net I found that it is possible to the application using ODBC to enable connection pooling. Do hostmon use this option?

Thanks,

Jromariz.

jromariz · Post by **jromariz** » Fri Feb 05, 2010 6:31 am

Just completing the last message: the reason I´m asking is because when I issue a netstat command on hostmon´s machine a see dozens of "TIME_WAIT" connections to the databse where the logs are been recorded. If I´m not wrong, if connection pooling was used, I should only see few "ESTABLISHED" connections to the database.

Regards,

Jromariz.

KS-Soft · Post by **KS-Soft** » Fri Feb 05, 2010 7:29 am

Is there any possibility that, in the future, hostmon will be able to write directly to SQL database?

We already implemented this option in latest version (not available for download yet) but test results not too promising:
1) its not much faster, just a little bit
2) there are still some bugs that we cannot fix. E.g. many versions of MySQL client has bugs...
So I am not sure we will include this option in release.

If "directly" means without using any 3rd party client DLLs (like oci.dll or LIBMYSQL.DLL) then answer is no.

Regards
Alex

KS-Soft · Post by **KS-Soft** » Fri Feb 05, 2010 7:31 am

Searching the net I found that it is possible to the application using ODBC to enable connection pooling. Do hostmon use this option?

Option located on Misc page in the Options dialog.
Plus for ODBC logging HostMonitor uses its own cache because not every ODBC driver supports this option.

Regards
Alex

KS-Soft · Post by **KS-Soft** » Fri Feb 05, 2010 7:36 am

Just completing the last message: the reason I´m asking is because when I issue a netstat command on hostmon´s machine a see dozens of "TIME_WAIT" connections to the databse where the logs are been recorded. If I´m not wrong, if connection pooling was used, I should only see few "ESTABLISHED" connections to the database.

Time_wait status for TCP socket means connection was closed by application (ODBC driver).
If we are talking about logging, HostMonitor keeps connection for a while even if no records should be recorded. There is some undocumented option that allows you to specify time period for such connections. You may try to increase it but I don't think this can help much.
I think there is some network delay or delay caused by SQL server

Regards
Alex

jromariz · Post by **jromariz** » Mon Feb 08, 2010 8:15 am

Hi Alex,

Still believe connection pooling is not working as it should. I double-checked configuration on ODBC driver and hostmon that enables connection pooling, but I still get hundreds of connections to the database in TIME_WAIT state. If connection pooling was working, shouldn´t I see only some ESTABLISHED connections?

Thanks again.

Regards,

Jromariz.

KS-Soft · Post by **KS-Soft** » Mon Feb 08, 2010 9:15 am

As I said
1) not all ODBC drivers support this option
2) that's why HostMonitor uses its own connection cache for ODBC logging
3) if connection pool is used and/or HostMonitor keeps connection, this does not mean connection should be held forewer. Connection is kept within specific timeout.
You may change this timeout by adding ODBCConnectionTimeout=N line into [Logging] section of Hostmon.ini file.
E.g. ODBCConnectionTimeout=60 tells HostMonitor to keep connection for 60 sec (at least 60 sec, if HostMonitor adds some log records within these 60 sec, it keeps connection for another 60 sec).

Also, even if HostMonitor keeps connection, this does not mean your SQL server cannot drop connection from another end. I think ODBC driver can do the same due to some mistake in the code or some special settings...
So, if you do not like how your SQL server/ODBC driver works, you may try to change them

Regards
Alex

jromariz · Post by **jromariz** » Mon Feb 08, 2010 9:18 am

Thanks.

Jromariz.

KS-Soft · Post by **KS-Soft** » Mon Feb 08, 2010 9:35 am

P.S.
If you modify hostmon.ini file, you should restart HostMonitor to apply settings.

Regards
Alex