compressing tables
Posted by:
M. B.
Date: February 13, 2009 04:24PM
Hi,
I'm trying to compress my database which has about 1500 inserts a second ;-)
This is what the table looks like:
mysql> describe sflow;
+-----------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+------------------+------+-----+---------+----------------+
| stamp | int(10) | NO | | NULL | |
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| agent_ip | varchar(64) | YES | MUL | NULL | |
| in_int | int(4) | YES | | NULL | |
| out_int | int(4) | YES | | NULL | |
| src_mac | varchar(16) | YES | | NULL | |
| dst_mac | varchar(16) | YES | | NULL | |
| eth_type | varchar(16) | YES | | NULL | |
| in_vlan | int(4) | YES | | NULL | |
| out_vlan | int(4) | YES | | NULL | |
| src_ip | varchar(16) | YES | | NULL | |
| dst_ip | varchar(16) | YES | | NULL | |
| ip_protocol | int(3) | YES | | NULL | |
| ip_tos | varchar(16) | YES | | NULL | |
| ip_ttl | int(3) | YES | | NULL | |
| src_port | int(5) | YES | | NULL | |
| dst_port | int(5) | YES | | NULL | |
| tcp_flags | varchar(16) | YES | | NULL | |
| packet_size | int(5) | YES | | NULL | |
| ip_size | int(5) | YES | | NULL | |
| sample_rate | int(6) | YES | | NULL | |
| src_mask | int(2) | YES | | NULL | |
| dst_mask | int(2) | YES | | NULL | |
| my_as | int(5) | YES | | NULL | |
| src_as | int(5) | YES | | NULL | |
| src_peer_as | int(5) | YES | | NULL | |
| dst_as_path_len | int(2) | YES | | NULL | |
| dst_peer_as | int(5) | YES | | NULL | |
| dst_as | int(5) | YES | | NULL | |
+-----------------+------------------+------+-----+---------+----------------+
29 rows in set (0.00 sec)
And I would like to insert all records to a new table (sflow_5min) where:
* stamp is in between 5 minutes AND
* agent_ip, src_ip, dst_ip, src_as, src_peer_as, dst_peer_as, dst_as are matching so we could sum ip_size * sample_rate.
I know it's possible with SQL, hope you could help me out ;-)
Subject
Written By
Posted
compressing tables
February 13, 2009 04:24PM
February 14, 2009 02:47AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.