mysql high transaction

we have an sflow system which got records values as given below every second and sometimes it goes up to x10

All values recording into mysql with different columns. How would be a efficient usage of insert into to a better performance for that


           2015-05-11 11:21:10.044      178.211.33.2    37.123.96.67    45552   22      1518    0x10    6       200
           2015-05-11 11:21:10.044      78.169.186.8    178.20.225.195  13695   7777    64      0x10    17      200
           2015-05-11 11:21:10.044      178.211.33.2    37.123.96.67    40122   22      1518    0x10    6       200
           2015-05-11 11:21:10.044      212.174.165.104 37.123.99.9     35387   80      64      0x10    6       200
           2015-05-11 11:21:10.044      85.107.113.250  37.123.101.83   14843   15001   64      0x10    6       200
           2015-05-11 11:21:10.044      88.248.97.231   37.123.99.2     46822   80      78      0x02    6       200
           2015-05-11 11:21:10.044      78.190.233.105  37.123.101.164  1397    80      64      0x10    6       200
           2015-05-11 11:21:10.044      178.211.33.2    37.123.96.67    45552   22      1518    0x10    6       200
           2015-05-11 11:21:10.131      95.5.180.61     178.20.230.167  15795   49944   66      0x00    17      200
           2015-05-11 11:21:10.131      188.132.244.3   37.123.101.194  80      43455   1518    0x10    6       200
           2015-05-11 11:21:10.131      95.70.241.146   37.123.99.9     51804   80      64      0x10    6       200
           2015-05-11 11:21:10.131      212.156.88.174  37.123.99.9     51352   80      70      0x10    6       200
           2015-05-11 11:21:10.131      178.211.33.2    37.123.96.67    45552   22      1518    0x10    6       200
           2015-05-11 11:21:10.131      88.248.97.231   37.123.99.2     40053   80      70      0x10    6       200
           2015-05-11 11:21:10.131      154.52.115.6    178.20.224.199  49701   80      64      0x10    6       200
           2015-05-11 11:21:10.131      154.52.115.6    178.20.224.199  49701   80      64      0x10    6       200
           2015-05-11 11:21:10.224      88.232.4.37     178.20.226.139  22139   13000   64      0x10    6       200
           2015-05-11 11:21:10.224      88.236.138.61   37.123.96.178   57612   902     95      0x18    6       200
           2015-05-11 11:21:10.224      178.211.33.2    37.123.96.67    45552   22      1518    0x10    6       200
           2015-05-11 11:21:10.224      178.211.33.2    37.123.96.67    45552   22      1518    0x10    6       200
           2015-05-11 11:21:10.224      95.8.83.103     37.123.101.83   51369   15001   70      0x18    6       200
           2015-05-11 11:21:10.224      78.164.27.209   178.20.231.161  27113   2828    64      0x18    17      200
           2015-05-11 11:21:10.224      78.171.167.209  37.123.103.166  18119   80      64      0x10    6       200
           2015-05-11 11:21:10.224      178.211.33.2    37.123.96.67    62205   22      1518    0x10    6       200
           2015-05-11 11:21:10.297      178.211.33.2    37.123.96.67    62205   22      1518    0x10    6       200
           2015-05-11 11:21:10.297      78.171.167.209  37.123.103.166  18119   80      64      0x10    6       200
           2015-05-11 11:21:10.297      85.111.14.121   37.123.101.194  80      59243   1518    0x10    6       200
           2015-05-11 11:21:10.297      85.111.14.121   37.123.101.194  80      59243   1518    0x10    6       200
           2015-05-11 11:21:10.297      78.169.186.215  37.123.101.202  57893   80      64      0x10    6       200
           2015-05-11 11:21:10.297      88.247.65.211   37.123.99.9     12515   80      747     0x18    6       200
           2015-05-11 11:21:10.297      178.211.33.2    37.123.96.67    45552   22      1518    0x10    6       200
           2015-05-11 11:21:10.393      85.111.14.121   37.123.101.194  80      59243   1518    0x10    6       200
           2015-05-11 11:21:10.393      154.52.115.6    178.20.224.199  49701   80      64      0x10    6       200
           2015-05-11 11:21:10.393      154.52.115.6    178.20.224.199  49701   80      64      0x10    6       200
           2015-05-11 11:21:10.393      78.179.214.196  178.20.231.139  49779   7080    126     0x10    17      200
           2015-05-11 11:21:10.393      85.109.134.174  178.20.227.10   50418   13000   64      0x10    6       200
           2015-05-11 11:21:10.393      178.211.33.2    37.123.96.67    40122   22      1518    0x10    6       200
           2015-05-11 11:21:10.393      85.99.223.114   37.123.99.9     61620   80      64      0x10    6       200
           2015-05-11 11:21:10.393      212.252.20.108  185.9.157.76    65062   50010   64      0x10    6       200
           2015-05-11 11:21:10.482      178.211.33.2    37.123.96.67    40122   22      1518    0x10    6       200
           2015-05-11 11:21:10.482      82.222.201.69   178.20.231.252  52459   27      67      0x18    6       200
           2015-05-11 11:21:10.483      178.211.33.2    37.123.96.67    40122   22      1518    0x10    6       200
           2015-05-11 11:21:10.483      212.156.88.174  37.123.99.9     51358   80      70      0x10    6       200
           2015-05-11 11:21:10.483      62.248.47.247   37.123.96.151   53073   51      64      0x10    17      200
           2015-05-11 11:21:10.483      78.187.97.66    178.20.231.252  16565   27      64      0x10    6       200
           2015-05-11 11:21:10.483      78.190.233.105  37.123.101.164  1397    80      64      0x10    6       200
           2015-05-11 11:21:10.483      78.190.205.29   37.123.96.168   51124   80      550     0x18    6       200
           2015-05-11 11:21:10.566      178.211.33.2    37.123.96.67    40122   22      1518    0x10    6       200
           2015-05-11 11:21:10.566      178.211.33.2    37.123.96.67    40122   22      1518    0x10    6       200
           2015-05-11 11:21:10.566      78.171.167.209  37.123.103.166  18119   80      64      0x10    6       200
           2015-05-11 11:21:10.566      178.211.33.2    37.123.96.67    45552   22      1518    0x10    6       200
           2015-05-11 11:21:10.566      178.211.33.2    37.123.96.67    45552   22      1518    0x10    6       200
           2015-05-11 11:21:10.566      185.22.184.53   37.123.101.194  80      42894   392     0x18    6       200
           2015-05-11 11:21:10.566      95.15.53.41     37.123.99.9     10399   80      64      0x10    6       200
           2015-05-11 11:21:10.653      176.41.171.186  37.123.96.242   55317   80      64      0x10    6       200
           2015-05-11 11:21:10.653      82.222.239.179  37.123.97.70    46153   32259   70      0x10    6       200
           2015-05-11 11:21:10.653      95.15.8.7       37.123.99.9     64774   80      64      0x10    6       200
           2015-05-11 11:21:10.653      95.5.180.61     178.20.230.167  15795   49944   66      0x10    17      200
           2015-05-11 11:21:10.653      154.52.115.6    178.20.224.199  49701   80      64      0x10    6       200
           2015-05-11 11:21:10.653      78.186.179.127  37.123.98.238   2832    3389    111     0x18    6       200
           2015-05-11 11:21:10.653      82.222.201.69   178.20.231.252  52459   27      64      0x10    6       200
           2015-05-11 11:21:10.653      178.211.33.2    37.123.96.67    45552   22      1518    0x10    6       200
           2015-05-11 11:21:10.757      212.252.166.32  185.9.157.213   53913   443     70      0x02    6       200
           2015-05-11 11:21:10.757      95.5.180.61     178.20.230.167  15795   49944   66      0x02    17      200
           2015-05-11 11:21:10.757      81.214.75.208   37.123.100.66   63703   3389    64      0x02    17      200
           2015-05-11 11:21:10.757      78.171.167.209  37.123.103.166  18119   80      64      0x10    6       200
           2015-05-11 11:21:10.757      78.190.233.105  37.123.101.164  1397    80      64      0x10    6       200
           2015-05-11 11:21:10.757      78.171.167.209  37.123.103.166  18119   80      64      0x10    6       200
           2015-05-11 11:21:10.757      78.171.167.209  37.123.103.166  18119   80      64      0x10    6       200
           2015-05-11 11:21:10.757      88.231.228.118  37.123.103.92   20798   13000   64      0x10    6       200
           2015-05-11 11:21:10.757      78.182.121.82   37.123.103.167  50185   80      64      0x10    6       200
           2015-05-11 11:21:10.831      178.211.33.2    37.123.96.67    62205   22      1518    0x10    6       200
           2015-05-11 11:21:10.831      178.211.33.2    37.123.96.67    62205   22      1518    0x10    6       200
           2015-05-11 11:21:10.831      154.52.115.6    178.20.224.199  49701   80      64      0x10    6       200
           2015-05-11 11:21:10.831      78.166.152.63   37.123.101.83   15206   15001   64      0x10    6       200
           2015-05-11 11:21:10.831      178.240.51.132  185.9.157.99    55883   80      64      0x10    6       200
           2015-05-11 11:21:10.831      178.211.33.2    37.123.96.67    40122   22      1518    0x10    6       200
           2015-05-11 11:21:10.831      178.211.33.2    37.123.96.67    40122   22      1518    0x10    6       200
           2015-05-11 11:21:10.912      178.211.33.2    37.123.96.67    40122   22      1518    0x10    6       200
           2015-05-11 11:21:10.913      178.211.33.2    37.123.96.67    40122   22      1518    0x10    6       200
           2015-05-11 11:21:10.913      178.211.33.2    37.123.96.67    40122   22      1518    0x10    6       200
           2015-05-11 11:21:10.913      88.234.127.230  37.123.101.202  58663   80      64      0x10    6       200
           2015-05-11 11:21:10.913      178.211.33.2    37.123.96.67    40122   22      1518    0x10    6       200
           2015-05-11 11:21:10.913      85.103.233.97   185.9.159.200   12078   80      64      0x10    6       200
           2015-05-11 11:21:10.913      154.52.115.6    178.20.224.199  49701   80      64      0x10    6       200

Open in new window

FireBallITAsked:
Who is Participating?
 
gheistCommented:
100k records per second might be 100000 iops, clos to the limits of SSD.
0
 
arnoldCommented:
You are only providing statistics.  There is no query nor is there information on your table setup, triggers, constraints, etc.

run explain your_insert_query and it may shed light on your issue.

if you have phpmyadmin installed, you can check it for suggestions.
You might have too many indexes, triggers ......

Separating certain columns such that you do not have duplicate records for Ips, ports
...

i.e. table of IPS
0
 
gheistCommented:
Also look for mysql optimization scripts, they will tell same as php but without php.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
FireBallITAuthor Commented:
We use perl scripts to insert into mysql but when it reaches 100k + records per second read and write goes so slowly we just nees ideas
0
 
arnoldCommented:
What is the storage configuration? What is the spec of the system?
0
 
FireBallITAuthor Commented:
We are using SSD raid 0 disk and 5670 double cpu with 64 Gb of memory
0
 
gheistCommented:
Run mysqltuner.pl
100k+ records should be batched in transactions, so it saves some IOPs.
0
 
arnoldCommented:
your issue might be more related to the resource needs for perl to load up the data and process the inserts, rather than attributing it all to mysql.


How is the data made available to the perl script.
using load data in file

It seems as though this is an output from firewall log on access.

So what if it takes longer to perform the insert?
triggers, indexes, etc. might contribute to the delay.

check your disk I/O to see whether that is where the delay is write iostat

what settings on the DB/table engine innodb/table/cell locking?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.