For what it's worth, this query took 1 minute 37 seconds: select ip_src_addr from flowsv4 order by ip_src_addr limit 10;
There are about 26,000,000 records in flowsv4 (and none in flowsv6). Is that normal? > -----Original Message----- > From: [email protected] [mailto:ntop-misc- > [email protected]] On Behalf Of Peter Shute > Sent: Friday, 28 October 2016 7:56 AM > To: [email protected] > Subject: Re: [Ntop-misc] Access to historical charts very slow in ntopng > > Do I have to run "use performance_schema;" first? I'm new to mysql. It took > 0.02 seconds, is that fast enough? > > *************************** 1. row > *************************** > > SCHEMA_NAME: ntopng > > DIGEST: 58bffbb800986c1b1147f462d49802e5 > > DIGEST_TEXT: INSERT INTO `flowsv4` ( `VLAN_ID` , `L7_PROTO` , > `IP_SRC_ADDR` , `L4_SRC_PORT` , `IP_DST_ADDR` , `L4_DST_PORT` , > `PROTOCOL` , `IN_BYTES` , `OUT_BYTES` , `PACKETS` , `FIRST_SWITCHED` , > `LAST_SWITCHED` , `INFO` , JSON , `NTOPNG_INSTANCE_NAME` , > `INTERFACE_ID` , PROFILE ) VALUES ( ?, ... , `COMPRESS` (?) , ?, ... ) > > COUNT_STAR: 1696935 > > SUM_TIMER_WAIT: 6731129622996000 > > MIN_TIMER_WAIT: 220633000 > > AVG_TIMER_WAIT: 3966639000 > > MAX_TIMER_WAIT: 665490453714000 > > SUM_LOCK_TIME: 5213877550000000 > > SUM_ERRORS: 3 > > SUM_WARNINGS: 0 > > SUM_ROWS_AFFECTED: 1696932 > > SUM_ROWS_SENT: 0 > > SUM_ROWS_EXAMINED: 0 > > SUM_CREATED_TMP_DISK_TABLES: 0 > > SUM_CREATED_TMP_TABLES: 0 > > SUM_SELECT_FULL_JOIN: 0 > > SUM_SELECT_FULL_RANGE_JOIN: 0 > > SUM_SELECT_RANGE: 0 > > SUM_SELECT_RANGE_CHECK: 0 > > SUM_SELECT_SCAN: 0 > > SUM_SORT_MERGE_PASSES: 0 > > SUM_SORT_RANGE: 0 > > SUM_SORT_ROWS: 0 > > SUM_SORT_SCAN: 0 > > SUM_NO_INDEX_USED: 0 > > SUM_NO_GOOD_INDEX_USED: 0 > > FIRST_SEEN: 2016-10-26 14:55:39 > > LAST_SEEN: 2016-10-28 07:51:04 > > 1 row in set (0.02 sec) > > > > > > From: [email protected] [mailto:ntop-misc- > [email protected]] On Behalf Of Luca Deri > Sent: Friday, 28 October 2016 5:03 AM > To: [email protected] > Subject: Re: [Ntop-misc] Access to historical charts very slow in ntopng > > > > Peter > > it is very likely that your MySQL is not fast enough. > > > > Try to run the query below on your DB to see how long it took (MySQL 5.6 or > later) > > > > Luca > > > > mysql> select * from events_statements_summary_by_digest order by > MAX_TIMER_WAIT desc limit 1 \G > > *************************** 1. row > *************************** > > SCHEMA_NAME: ntopng > > DIGEST: 79669e73b0e9bcf17c7ebc9c5ba6b8de > > DIGEST_TEXT: SELECT COUNT ( * ) AS `TOT_FLOWS` , SUM ( > `IN_BYTES` + `OUT_BYTES` ) AS `TOT_BYTES` , SUM ( `PACKETS` ) AS > `TOT_PACKETS` FROM `flowsv4` WHERE `FIRST_SWITCHED` <= ? AND > `FIRST_SWITCHED` >= ? AND ( `NTOPNG_INSTANCE_NAME` = ? OR > `NTOPNG_INSTANCE_NAME` IS NULL ) AND ( `INTERFACE_ID` = ? ) AND > `L7_PROTO` = ? AND ( `IP_SRC_ADDR` = `INET_ATON` (?) OR `IP_DST_ADDR` > = `INET_ATON` (?) ) > > COUNT_STAR: 37 > > SUM_TIMER_WAIT: 2475543000000 > > MIN_TIMER_WAIT: 22072000000 > > AVG_TIMER_WAIT: 66906567000 > > MAX_TIMER_WAIT: 471173000000 > > SUM_LOCK_TIME: 10407000000 > > SUM_ERRORS: 0 > > SUM_WARNINGS: 0 > > SUM_ROWS_AFFECTED: 0 > > SUM_ROWS_SENT: 37 > > SUM_ROWS_EXAMINED: 817254 > > SUM_CREATED_TMP_DISK_TABLES: 0 > > SUM_CREATED_TMP_TABLES: 0 > > SUM_SELECT_FULL_JOIN: 0 > > SUM_SELECT_FULL_RANGE_JOIN: 0 > > SUM_SELECT_RANGE: 1 > > SUM_SELECT_RANGE_CHECK: 0 > > SUM_SELECT_SCAN: 34 > > SUM_SORT_MERGE_PASSES: 0 > > SUM_SORT_RANGE: 0 > > SUM_SORT_ROWS: 0 > > SUM_SORT_SCAN: 0 > > SUM_NO_INDEX_USED: 34 > > SUM_NO_GOOD_INDEX_USED: 0 > > FIRST_SEEN: 2016-10-26 16:11:04 > > LAST_SEEN: 2016-10-26 16:43:11 > > 1 row in set (0.00 sec) > > > > > > > > On 27 Oct 2016, at 01:26, Peter Shute <[email protected] > <mailto:[email protected]> > wrote: > > > > In the charts tab of the Interfaces section, we can choose time > ranges between 5 minutes and 1 year to display the traffic levels on a chart. > I > haven't been able to get the chart to display more than one week of data. If I > ask it to display two weeks, it waits for a very long time then seems to give > up. > > I assume it's having trouble querying the mysql database for that > much data. Are there any tests I can do to prove this, and is there anything I > can do to speed it up? > > The mysql I installed still has all the default configuration settings. > It's > running on a recent version of Ubuntu server. > > Peter Shute > _______________________________________________ > Ntop-misc mailing list > [email protected] <mailto:Ntop- > [email protected]> > http://listgateway.unipi.it/mailman/listinfo/ntop-misc > > _______________________________________________ Ntop-misc mailing list [email protected] http://listgateway.unipi.it/mailman/listinfo/ntop-misc
