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: ntop-misc-bounces@listgateway.unipi.it [mailto:ntop-misc-bounces@listgateway.unipi.it] On Behalf Of Luca Deri
Sent: Friday, 28 October 2016 5:03 AM
To: ntop-misc@listgateway.unipi.it
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 <pshute@nuw.org.au<mailto:pshute@nuw.org.au>> 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
Ntop-misc@listgateway.unipi.it<mailto:Ntop-misc@listgateway.unipi.it>
http://listgateway.unipi.it/mailman/listinfo/ntop-misc