MySQL Forums :: Newbie :: compressing tables


Advanced Search

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 ;-)

Options: ReplyQuote


Subject Written By Posted
compressing tables M. B. 02/13/2009 04:24PM
Re: compressing tables Rick James 02/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.