# mysql 5.7 insert netflow data (30 000 rcords /per sec)

**URL:** <https://forums.percona.com/t/mysql-5-7-insert-netflow-data-30-000-rcords-per-sec/6055>\
**Category:** Other MySQL® Questions\
**Created:** [December 6, 2017, 4:18pm UTC](https://forums.percona.com/t/mysql-5-7-insert-netflow-data-30-000-rcords-per-sec/6055 "2017-12-06T16:18:52Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![dancer2001](https://avatars.discourse-cdn.com/v4/letter/d/bcef8e/32.png) [@dancer2001](https://forums.percona.com/u/dancer2001)\
**Post date:** [December 6, 2017, 4:18pm UTC](https://forums.percona.com/t/mysql-5-7-insert-netflow-data-30-000-rcords-per-sec/6055/1 "2017-12-06T16:18:52Z")

</div>

Hello Everybody  
i am developing netflow monitor and i am stuck in mysql 5.7 on windows PC.  
The application i am working on only for windows PC  
I have netflow packets (30 000 per sec) and i need to insert them to DB  
I created innodb table with partitions ( 1 partition for 1 day).  
This is table structure  
CREATE TABLE IF NOT EXISTS netflow\_1 (  
`id` int(10) unsigned NOT NULL AUTO\_INCREMENT,  
`stimestamp` int(10) unsigned NOT NULL,  
`flow_start_time` int(10) unsigned NOT NULL,  
`input_snmp` SMALLINT(5) unsigned DEFAULT NULL,  
`output_snmp` SMALLINT(5) unsigned DEFAULT NULL,  
`direction` TINYINT(3) unsigned DEFAULT NULL,  
`ipv4_src_addr` int(11) unsigned DEFAULT NULL,  
`ipv4_dst_addr` int(11) unsigned DEFAULT NULL,  
`netflow_bytes` BIGINT(20) unsigned DEFAULT NULL,  
`protocol` TINYINT(3) unsigned DEFAULT NULL,  
`l4_src_port` SMALLINT(5) unsigned DEFAULT NULL,  
`l4_dst_port` SMALLINT(5) unsigned DEFAULT NULL,  
`src_as` SMALLINT(5) unsigned DEFAULT NULL,  
`dst_as` SMALLINT(5) unsigned DEFAULT NULL,  
PRIMARY KEY (`id`,`stimestamp`),  
KEY `idx_stimestamp` (`stimestamp`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8  
PARTITION BY RANGE (stimestamp) (  
PARTITION p2017\_12\_01 VALUES LESS THAN (UNIX\_TIMESTAMP(“2017-12-02”))  
);

And i use procedure to insert data . It takes me 45-50 sec to insert 1 million records  
How to improve i do not know  
Also how to  
1 - select port, sum(netflow\_bytes) as \_netflow\_sum from netflow\_1 where stimestamp \> 0 and stimestamp \< 4000000 group by port order by \_netflow\_sum desc limit 0,10;  
2 - select src\_as, dst\_as, sum(netflow\_bytes) as \_netflow\_sum from netflow\_1 where stimestamp \> 0 and stimestamp \< 4000000 group by concat(src\_as, dst\_as) desc limit 0 , 10;  
User should be able to select netflow by time range. In GUI it is usual table with pagination and calendar.

Netflow data will be stored during 30 days. It means the table with have 30 partitions.  
Thanks for any advice

---

<div class="post-metadata">

**Author:** ![Bob565656](https://avatars.discourse-cdn.com/v4/letter/b/85e7bf/32.png) [@Bob565656](https://forums.percona.com/u/Bob565656)\
**Post date:** [December 7, 2017, 8:09am UTC](https://forums.percona.com/t/mysql-5-7-insert-netflow-data-30-000-rcords-per-sec/6055/2 "2017-12-07T08:09:13Z")

</div>

For your insertion performance, I would first try dropping the partition altogether to see if that helps. If that does help, then I would suggest the following (I do this as well in practice)  
[LIST]  
[_]insert one more column, partition\_id (int) which is a concat of YYYMM or YYYYMMDD. Have this be your partition column instead of timestamp (still partition on range). As you know, timestamp is 8 bytes, and for the most part unique, so on a range partition, this does hurt, especially on your insert frequency  
[/LIST][LIST=1]  
[_]That select is fine. Make sure port is an index. You can try to select from the specific partition instead of specifying a WHERE clause (if it’s for the entire day) SELECT x FROM y PARTITION(p2017\_12\_01)  
[\*]Don’t concat the GROUP BY, it’s not required (it’s the same thing as GROUP BY src\_as, dst\_as). Same suggestion as above for WHERE  
[/LIST]

---

<div class="post-metadata">

**Author:** ![dancer2001](https://avatars.discourse-cdn.com/v4/letter/d/bcef8e/32.png) [@dancer2001](https://forums.percona.com/u/dancer2001)\
**Post date:** [December 7, 2017, 2:42pm UTC](https://forums.percona.com/t/mysql-5-7-insert-netflow-data-30-000-rcords-per-sec/6055/3 "2017-12-07T14:42:03Z")

</div>

1 - i use stimestamp (it is int) as a partition column  
2 - thanks foGROUP BY concat(…), i forgot about this  
But i have many options i have to "GROUP BY "  
1 ) (CONCAT(ifnull(n.protocol,‘’), LEAST(ifnull(n.l4\_src\_port,0), ifnull(n.l4\_dst\_port,0))))  
2 ) port  
3 ) ipv4\_src\_addr  
4 ) ipv4\_dst\_add  
5 ) concat ( ipv4\_src\_addr , ipv4\_dst\_add )  
6 ) src\_as  
7 ) dst\_as  
8 ) concat ( src\_as , dst\_as)

‘Make sure port is an index.’ In my case it means i should create a lot of indexes. And some of them should be complex ( concat ( ipv4\_src\_addr , ipv4\_dst\_add ) )

About inserting performance  
[url][Screenshot by Lightshot](https://prnt.sc/hjuoms%5B/url%5D)

Thanks for advice