search

ntop / ntopng

Web-based Traffic and Security Network Traffic Monitoring
http://www.ntop.org
GNU General Public License v3.0
6.24k stars 654 forks source link

No Data for Top Hosts on large queries #680

Closed Alestor closed 8 years ago

Alestor commented 8 years ago

Hello,

using Version 2.4.160805 with a mysql Database. When I create reports or browser historical data for a time range greater than 4 days I don't get any result for Top hosts. On the reports the top host are just missing and under the historical data explorer I get the message "No Results Found" for top host. When I specify a time range of exactly 4 Days I get results back.

Version 2.4.160805 - Pro Small Business Edition Built on Debian GNU/Linux 8.2 (jessie) nDPI 1.8.0-1.8-stable-467-6450ae2 Twitter Bootstrap 3.x Font Awesome 4.x RRDtool 1.4.8 Redis Server 2.8.17 Mongoose web server 3.7 LuaJIT LuaJIT 2.0.3 ØMQ 4.0.5 GeoIP 1.6.2

This product includes GeoLite data created by MaxMind. Data-Driven Documents (d3js) 2.9.1 / 3.0

Best regards

simonemainardi commented 8 years ago

hi, thanks for reporting. Could you please enclose the ntopng logs (or console output)?

Alestor commented 8 years ago

Hello,

which logs do you need exactly? Unfortunately the is no messages related to the issue on the console or in /var/log/ntopng/ntopng.log. I noticed that the error messages happens when I search for top hots from about 700k flows. The Time range doesn't seem to matter that much.

I enabled slow-log for mysql and saw the following output when I get no results found:

Time: 160808 21:18:35 User@Host: ntopng_user[ntopng_user] @ localhost [] Query_time: 5.025693 Lock_time: 0.000217 Rows_sent: 50 Rows_examined: 4696662 SET timestamp=1470683915;

select CASE WHEN addrv4 IS NOT NULL THEN INET_NTOA(addrv4) ELSE addrv6 END addr, SUM(bytes_sent + bytes_rcvd) tot_bytes, SUM(packets) tot_packets, SUM(bytes_sent) bytes_sent, SUM(bytes_rcvd) bytes_rcvd, count(*) tot_flows FROM ( SELECT IP_SRC_ADDR addrv4, NULL addrv6, PACKETS as packets, IN_BYTES as bytes_sent, OUT_BYTES as bytes_rcvd, FIRST_SWITCHED, LAST_SWITCHED FROM flowsv4 WHERE FIRST_SWITCHED <= 1470538338 and FIRST_SWITCHED >= 1469944338 AND (NTOPNG_INSTANCE_NAME=''OR NTOPNG_INSTANCE_NAME IS NULL) AND (INTERFACE_ID='2') UNION ALL SELECT IP_DST_ADDR addrv4, NULL addrv6, PACKETS as packets, OUT_BYTES as bytes_sent, IN_BYTES as bytes_rcvd, FIRST_SWITCHED, LAST_SWITCHED FROM flowsv4 WHERE FIRST_SWITCHED <= 1470538338 and FIRST_SWITCHED >= 1469944338 AND (NTOPNG_INSTANCE_NAME=''OR NTOPNG_INSTANCE_NAME IS NULL) AND (INTERFACE_ID='2') UNION ALL SELECT NULL addrv4, IP_SRC_ADDR addrv6, PACKETS as packets, IN_BYTES as bytes_sent, OUT_BYTES as bytes_rcvd, FIRST_SWITCHED, LAST_SWITCHED FROM flowsv6 WHERE FIRST_SWITCHED <= 1470538338 and FIRST_SWITCHED >= 1469944338 AND (NTOPNG_INSTANCE_NAME='OR NTOPNG_INSTANCE_NAME IS NULL) AND (INTERFACE_ID='2') UNION ALL SELECT NULL addrv4, IP_DST_ADDR addrv6, PACKETS as packets, OUT_BYTES as bytes_sent, IN_BYTES as bytes_rcvd, FIRST_SWITCHED, LAST_SWITCHED FROM flowsv6 WHERE FIRST_SWITCHED <= 1470538338 and FIRST_SWITCHED >= 1469944338 AND (NTOPNG_INSTANCE_NAME=''OR NTOPNG_INSTANCE_NAME IS NULL) AND (INTERFACE_ID='2') ) talkers group by addr order by tot_bytes desc limit 0,50;

simonemainardi commented 8 years ago

@Alestor report creation doesn't use MySQL. Can you confirm that you can't generate reports with a timespan greater than 4 days?

MySQL access occurs when browsing "historical data explorer". I argue that some timeout occurs at some point and this causes a "no results found". Do you get this message immediately or after a while?

Btw, did you try to tune my.cnf in order to increase myisam buffers and caches?

Alestor commented 8 years ago

After adjusting some buffer in my.cnf I was able to list top hosts for 6 to 7 days in the Historical data explorer. I think the time range varies depending on how many flows in this period occurred. The error messages pops up after 5 to 6 seconds approx.

When I create a reports I do see only top hosts for a time range of 1h or 1 day. In a Report for one week they are missing. Didn't tried a custom time range gt 1 and lt 7 Days when doing Reports yet. I'll try this later.

Alestor commented 8 years ago

When I create a report, top hosts and other stats are only shown when I go two days back. Generating a report for the last 3 days doesn't show the stats for Top Countries , Top Hosts (local and remote) and Top ASN.

Alestor commented 8 years ago

Displaying Top Talker in the Historical data exporer seems to work as long as the mysql-server is able to answer the query in less then 5 seconds. Does ntopng have a timeout of 5 seconds for sql queries?

Alestor commented 8 years ago

Now I have ntopng running for one Month. A Report for the last 30 Days doesn't show the stats for Top Countries,Top Remote Hosts , Top Local Hosts, Top AS, Top Local OS and even Total Traffic for Local/Remote, Local Networks and all L7 Protocols are missing. Sorry but Reports like this are useless :-(

Version 2.4.160818 - Pro Small Business Edition

simonemainardi commented 8 years ago

@Alestor we have made a fix that solved the missing hosts issue on debian. Please, try with the latest dev build.

Note that reports don't use mysql.

Also keep in mind that when upgrading from 2.4 to 2.5 there will be mysql schema updates that may take some time. So for you in order to try 2.5 I would recommend first disabling mysql flows dump. You can re-enable it afterwards.

Alestor commented 8 years ago

Thanks for the Update - I'll try it out next weekend.

Alestor commented 8 years ago

Hello,

there are no changes to the issues which I have with the Appication.

  1. I get "No Results Found" on the historical data explorer when I have searched for a big time Range and want to display talkers.
  2. When I generate Reports for more then 4 Days stats are not displayed for Top Countries , Top Hosts (local and remote) and Top ASN.
  3. When I generate Reports for 1 Month I just see some Charts (without stats) and Total Traffic for Local/Remote, Local Networks and all L7 Protocols are also missing.

In additional ntopng 2.5 crashed last night with a segfault:

Sep 14 01:00:01 kernel: [911573.751156] ntopng[6136]: segfault at 80 ip 00007f1cccb0bbc0 sp 00007f1cb1ffa248 error 4 in libmysqlclient.so.18.0.0[7f1cccad3000+2b8000] Sep 14 01:00:01 kernel: [911573.754740] device eth1 left promiscuous mode Sep 14 01:00:01 logger: ntopng stop Sep 14 01:00:06 logger: ntopng start Sep 14 01:00:06 kernel: [911579.046227] device eth1 entered promiscuous mode Sep 14 01:00:07 kernel: [911579.513731] ntopng[32482]: segfault at 80 ip 00007fe517f14bc0 sp 00007fe5057f9248 error 4 in libmysqlclient.so.18.0.0[7fe517edc000+2b8000] Sep 14 01:00:07 kernel: [911579.516550] device eth1 left promiscuous mode Sep 14 01:00:07 logger: ntopng stop

Rolled back to 2.4

simonemainardi commented 8 years ago

@Alestor what is the ntopng version that crashed? can you generate a core dump and upload it along with the ntopng binary?

Alestor commented 8 years ago

Both the Versions 2.4 and 2.5 are now crashing every night at exactly 1:00AM.

lucaderi commented 8 years ago

@Alestor Can you provide a core dump to analyse?

Alestor commented 8 years ago

I'll try to get one this night. I'm in timezone gmt+2. The Last logs I see before the crash are

16/Sep/2016 01:00:01 [MySQLDB.cpp:549] Attempting to connect to MySQL for interface eth1... 16/Sep/2016 01:00:01 [MySQLDB.cpp:589] Succesfully connected to MySQL [localhost:ntopng_user] for interface eth1

it may be related to https://github.com/ntop/ntopng/commit/e34d7806f19f77c354f7e9276c3c1a273d16a433 ?

simonemainardi commented 8 years ago

let's see please upload the dump when you get it and we'll inspect.

simonemainardi commented 8 years ago

@Alestor crash should be fixed, packages are being rebuilt. Try again tomorrow.

Alestor commented 8 years ago

@simonemainardi ntopng running stable now for over 24hours, thanks.

Alestor commented 8 years ago

Ok, the Issue on the SQL Queries at the historical data explorer is my own bad - using Apache proxy for ssl and didn't take the timeout of that into Account - sorry about that!

Anyway when creating Reports (yes they don't rely on the SQL Queries like historical data explorer ;-) ) for 30Days I get the following Error message:

[Lua.cpp:5134] WARNING: Script failure [/usr/share/ntopng/scripts/lua/pro/get_from_top_talkers_db.lua][not enough memory]

The Server where ntop is running has 8GB RAM and the utilization is 12% or around 50% with buffers and cached data

simonemainardi commented 8 years ago

the topic of the issue is solved. Please, re-open if you experience other errors.

souzaeric commented 7 years ago

Hi Alestor, How did you fix this problem:

Anyway when creating Reports (yes they don't rely on the SQL Queries like historical data explorer ;-) ) for 30Days I get the following Error message:

[Lua.cpp:5134] WARNING: Script failure [/usr/share/ntopng/scripts/lua/pro/get_from_top_talkers_db.lua][not enough memory]