Netqos RA slow query

[d.pereyra]

Hi every body. we are experimenting  to show graphs in Report Analyzer, the query takes more than 5 minutes.

I already check restarting services and running an optimization of MySQL.

Any idea what may be happening?

 

here a screenshot

 

 

Thanks a Lot !!

 

Diego P

 

Update:
I ran theNFA Parser v9 for 5 minutes, here result:

Time Span for this report: 2016-10-04 14:34:00 - 2016-10-04 14:39:00

5.00 Minutes | 2823991 Flows | 564798.20 Fpm | 394 Routers | 2359 Total Interfaces

[Christopher Walsh]

What version of NFA are you running?
How much RAM/CPU do you have on the Harvester for this interface?

Are you in a 3 tier or 2 tier environment?

Do you see a spike in CPU or Memory on the Harvester/DSA when loading the report?

 

Do you have Symantec Endpoint installed on the Server?

[d.pereyra]

Hi Christopher ! thanks for reply.

I have an old version of NFA: Version: Reporter 9.0 (build 161) Update 1

is an virtual machine with 8gb RAM and 4vCPU

I have the Symantec Endpoint installed, but.. i unistalled because is out of date and an older version too.

I dont see any spike on cpu or ram utilization, is very relax the performance.

3 tier or 2tier?, remember what is that. I have two server, a harvester, and a RA.

 

Thanks, Diego Pereyra

[Christopher Walsh]

RA 9.0 is an older unsupported version you may want to try upgrading to at least 9.1.4. Then to the later versions if possible.

[d.pereyra]

Is an option do the update from 9.0 to 9.1 Sp4, I will find for a release notes before update. But is a major update or just a service pack ?

Thanks.

 

Meanwhile I have new errors...

Server Error in '/RA' Application.

---

A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Net.Sockets.SocketException: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SocketException (0x274c): A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond]    System.Net.Sockets.Socket.Receive(Byte[] buffer, Int32 offset, Int32 size, SocketFlags socketFlags) +73    System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size) +131  [IOException: Unable to read data from the transport connection: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.]    System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size) +294    Devart.Common.a.b(Byte[] A_0, Int32 A_1, Int32 A_2) +28    Devart.Common.b.c(Byte[] A_0, Int32 A_1, Int32 A_2) +152    Devart.Common.w.f(Byte[] A_0, Int32 A_1, Int32 A_2) +60  [MySqlException (0x80004005): Lost connection to MySQL server during query]    Devart.Common.al.a(Exception A_0) +6    Devart.Data.MySql.bd.a(Exception A_0) +45    Devart.Common.w.f(Byte[] A_0, Int32 A_1, Int32 A_2) +108    Devart.Data.MySql.bg.a(Byte[] A_0, Int32 A_1, Int32 A_2) +575    Devart.Data.MySql.bg.a() +26    Devart.Data.MySql.bg.c() +30    Devart.Data.MySql.u.e(String A_0) +58    Devart.Data.MySql.u.a(String A_0, String A_1, String A_2, String A_3, Int32 A_4, String A_5, Int32 A_6, SshOptions A_7, SslOptions A_8, ProxyOptions A_9, MySqlHttpOptions A_10, HttpOptions A_11) +1829    Devart.Data.MySql.MySqlInternalConnection.Connect(String userId, String password, String host, String database, Int32 port, Int32 connectionTimeout, MySqlProtocol protocol, Boolean compress, Boolean clientInteractive) +512    Devart.Data.MySql.MySqlInternalConnection..ctor(p connectionOptions) +120    Devart.Data.MySql.ao.a(z A_0, Object A_1, DbConnectionBase A_2) +53    Devart.Common.DbConnectionFactory.a(DbConnectionPool A_0, z A_1) +43    Devart.Common.DbConnectionPoolGroup.c(DbConnectionPool A_0) +18    Devart.Common.DbConnectionPool.a() +44    Devart.Common.DbConnectionPool.GetObject() +83    Devart.Common.DbConnectionFactory.a(DbConnectionBase A_0) +77    Devart.Common.DbConnectionClosed.Open(DbConnectionBase outerConnection) +129    Devart.Common.DbConnectionBase.Open() +120    Devart.Data.MySql.MySqlConnection.Open() +189    NetQoS.Core.Database.DBConnection.Open(DBProvider provider, String connectionString, Int32 commandTimeout, Boolean retryLostConnection) in d:\Builds\RA\Current\CORE LIBRARIES\dotnet\NetQoS.Core.Database\DBConnection.cs:254    NetQoS.Core.Database.DBReconnection.QueryTable(String sql) in d:\Builds\RA\Current\CORE LIBRARIES\dotnet\NetQoS.Core.Database\DBReconnection.cs:116    NetQoS.Services.Config.PerformanceCenterProperties.LoadData() +64    NetQoS.Services.Config.PerformanceCenterProperties.LoadCheck() +180    NetQoS.Services.Config.PerformanceCenterProperties.GetProperty(K propName, E defaultValue) +64    NetQoS.ReporterAnalyzer.Business.RASingleSignOn.get_CookieName() in d:\Builds\RA\Current\REPORTER\NetQoS.ReporterAnalyzer.Business\RASingleSignOn.cs:68    NetQoS.DataSource.Client.SingleSignOnUtility.Validate(ISingleSignOn sso, UInt32& userId, Boolean& automaticallySignedIn) in d:\Builds\RA\Current\CORE LIBRARIES\dotnet\NetQoS.DataSource.Client\SingleSignOnUtility.cs:60    NetQoS.ReporterAnalyzer.WebSite.Global.Application_PreRequestHandlerExecute(Object sender, EventArgs e) in d:\Builds\RA\Current\REPORTER\NetQoS.ReporterAnalyzer.WebSite\Global.asax.cs:182    System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +68    System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +75

 

Windows version is old, hardware is old.. nq version is old too... but have some many many years working great! I prefer fix it. 

Diego

[Christopher Walsh]

Yes you can upgrade from 9.0.161 to 9.1.4.  The upgrade was only written for 9.1.3 as 9.1.4 was a minor update to 9.1.3, there was no new guide written.

 

This is a fairly major upgrade so the documentation should be followed vert carefully.

 

Seems like you are having some mysql performance issues at this point based on your current error.  Could be a disk issue or something else, Mysql should not time out while loading the RA web page.

You may want to get an issue open so someone can investigate further.

[Christopher Walsh]

Just a few thoughts on what possibly could be slowing down mysql besides hardware related issues.

Check the \NetQos\MySql51\data directory for the <serverName>.err and see if there are any errors at the end of the file.  Also check to see how big the file is, being that this is an old system, it may be huge, if it is very large, delete it and recycle Netqos Mysql51 service.

 

You can also run the following and share the results so we can see if there are any abnormally large tables:

mysql -P3308 -D reporter -t -e "show table status;" > table_status.txt

 

One table we know grows large and doesn't prune in RA 9.0, is the 'address_definitions' table.  If this has over 1 million rows you can truncate it, which may help with performance.  

 

mysql -P3308 reporter

truncate address_definitions;

 

If you share the file first we can take a look though.

 

Then recycle Netqos Mysql51 service.

[d.pereyra]

Thanks a lot Christopher !

I recycle the service and no error file have been created yet.

I attach the file table_status.txt, the | address_definitions |  have  866522 rows.

 

Diego Pereyra

Attachment(s)

table_status.txt.zip   6 KB 1 version

nq02.err.zip   220 KB 1 version

[Christopher Walsh]

I don't see anything that is so large that it would cause performance issues like you are seeing.

I think its probably best at this point if you can open Support case so we can investigate further.

[d.pereyra]

Hi, what happen if I truncate  the address_definitions table ? Some info will be lost ?

Thankd

Diego MP

[Christopher Walsh]

These are the host name resolutions for the hosts and conversation data. The data will repopulate right away as data comes in for those hosts and start resolving right away as they come in.  So there is really no loss there.  

mysql -P3308 reporter

truncate table address_definitions;

 

However based on the number of rows in the table I don't think this will help much in this case.