MySQL Forums
Forum List  »  Performance

mysql TABLE tuning and performance
Posted by: talal hegab
Date: March 29, 2009 08:47AM

I am already building a web application for AAA server.
Below is the structure of the main table

+------------------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------------+--------------+------+-----+---------+-------+
| rec_date | date | YES | MUL | NULL | |
| rec_time | varchar(255) | YES | | NULL | |
| ras_client | varchar(255) | YES | MUL | NULL | |
| record_type | varchar(100) | YES | MUL | NULL | |
| full_name | varchar(255) | YES | | NULL | |
| auth_type | varchar(100) | YES | | NULL | |
| user_name | varchar(255) | YES | | NULL | |
| nas_ip | varchar(100) | YES | MUL | NULL | |
| nas_port | varchar(100) | YES | | NULL | |
| service_type | varchar(100) | YES | | NULL | |
| framed_protocol | varchar(100) | YES | | NULL | |
| framed_ip | varchar(100) | YES | | NULL | |
| framed_netmask | varchar(100) | YES | | NULL | |
| framed_routing | varchar(100) | YES | | NULL | |
| filter_id | varchar(100) | YES | | NULL | |
| framed_mtu | varchar(100) | YES | | NULL | |
| framed_compression | varchar(100) | YES | | NULL | |
| login_ip_host | varchar(100) | YES | | NULL | |
| login_service | varchar(100) | YES | | NULL | |
| login_tcp_port | varchar(100) | YES | | NULL | |
| callback_number | varchar(100) | YES | | NULL | |
| callback_id | varchar(100) | YES | | NULL | |
| framed_route | varchar(100) | YES | | NULL | |
| framed_ipx_network | varchar(100) | YES | | NULL | |
| class | varchar(100) | YES | | NULL | |
| session_timeout | varchar(100) | YES | | NULL | |
| idle_timeout | varchar(100) | YES | | NULL | |
| termination_action | varchar(100) | YES | | NULL | |
| called_station_id | varchar(100) | YES | MUL | NULL | |
| calling_station_id | varchar(100) | YES | MUL | NULL | |
| nas_identifier | varchar(100) | YES | | NULL | |
| proxy_state | varchar(100) | YES | | NULL | |
| acct_status_type | varchar(100) | YES | | NULL | |
| acct_delay_time | varchar(100) | YES | | NULL | |
| acct_input_octets | int(11) | YES | | NULL | |
| acct_output_octets | int(11) | YES | | NULL | |
| acct_session_id | varchar(100) | YES | | NULL | |
| acct_authenticate | varchar(100) | YES | | NULL | |
| acct_session_time | varchar(100) | YES | | NULL | |
| acct_input_packets | varchar(100) | YES | | NULL | |
| acct_output_packets | varchar(100) | YES | | NULL | |
| acct_termination_cause | varchar(100) | YES | | NULL | |
| acct_multi_session_id | varchar(100) | YES | | NULL | |
| acct_link_count | varchar(100) | YES | | NULL | |
| nas_port_type | varchar(100) | YES | | NULL | |
| port_limit | varchar(100) | YES | | NULL | |
| tunnel_type | varchar(100) | YES | | NULL | |
| tunnel_medium_type | varchar(100) | YES | | NULL | |
| tunnel_client_endpoint | varchar(100) | YES | | NULL | |
| tunnel_server_endpoint | varchar(100) | YES | | NULL | |
| acct_tunnel_connection | varchar(100) | YES | | NULL | |
| tunnel_private_group_id | varchar(100) | YES | | NULL | |
| tunnel_assignment_id | varchar(100) | YES | | NULL | |
| acct_tunnel_packets_lost | varchar(100) | YES | | NULL | |
| acct_input_gigawords | varchar(100) | YES | | NULL | |
| acct_output_gigawords | varchar(100) | YES | | NULL | |
| connect_info | varchar(100) | YES | | NULL | |
| ms_acct_auth_type | varchar(100) | YES | | NULL | |
| ms_acct_eap_type | varchar(100) | YES | | NULL | |
| event_timestamp | varchar(100) | YES | | NULL | |
| nas_port_id | varchar(100) | YES | | NULL | |
| acc_err_msg | varchar(100) | YES | | NULL | |
| annex_pro_name | varchar(100) | YES | | NULL | |
| annex_sw_version | varchar(100) | YES | | NULL | |
| annex_system_disc_reason | varchar(100) | YES | | NULL | |
| annex_modem_disc_reason | varchar(100) | YES | | NULL | |
| annex_disconnect_reason | varchar(100) | YES | | NULL | |
| annex_transmit_speed | varchar(100) | YES | | NULL | |
| annex_receive_speed | varchar(100) | YES | | NULL | |
| ascend_modem_port_number | varchar(100) | YES | | NULL | |
| ascend_modem_slot_number | varchar(100) | YES | | NULL | |
| ascend_modem_shelf_number | varchar(100) | YES | | NULL | |
| ascend_xmit_rate | varchar(100) | YES | | NULL | |
| nautica_acct_session_id | varchar(100) | YES | | NULL | |
| nautica_acct_direction | varchar(100) | YES | | NULL | |
| nautica_acct_causeProtocol | varchar(100) | YES | | NULL | |
| nautica_acct_causeSource | varchar(100) | YES | | NULL | |
| telebit_accounting_info | varchar(100) | YES | | NULL | |
| last_number_dialed_out | varchar(100) | YES | | NULL | |
| last_number_dialed_indnis | varchar(100) | YES | | NULL | |
| last_callers_number_ani | varchar(100) | YES | | NULL | |
| channel | varchar(100) | YES | | NULL | |
| event_id | varchar(100) | YES | | NULL | |
| event_date_time | varchar(100) | YES | | NULL | |
| call_start_date_time | varchar(100) | YES | | NULL | |
| call_end_date_time | varchar(100) | YES | | NULL | |
| default_date_data_rate | varchar(100) | YES | | NULL | |
| initial_rx_link_data_rate | varchar(100) | YES | | NULL | |
| final_rx_link_data_rate | varchar(100) | YES | | NULL | |
| initial_tx_link_data_rate | varchar(100) | YES | | NULL | |
| final_tx_link_data_rate | varchar(100) | YES | | NULL | |
| sync_async_mode | varchar(100) | YES | | NULL | |
| originate_answer_mode | varchar(100) | YES | | NULL | |
| modulation_type | varchar(100) | YES | | NULL | |
| equalization_type | varchar(100) | YES | | NULL | |
| fallback_enabled | varchar(100) | YES | | NULL | |
| characters_sent | varchar(100) | YES | | NULL | |
| characters_received | varchar(100) | YES | | NULL | |
| blocks_sent | varchar(100) | YES | | NULL | |
| blocks_received | varchar(100) | YES | | NULL | |
| blocks_resent | varchar(100) | YES | | NULL | |
| retrains_requested | varchar(100) | YES | | NULL | |
| retrains_granted | varchar(100) | YES | | NULL | |
| line_reversals | varchar(100) | YES | | NULL | |
| number_of_characters_lost | varchar(100) | YES | | NULL | |
| number_of_blers | varchar(100) | YES | | NULL | |
| number_of_link_timeouts | varchar(100) | YES | | NULL | |
| number_of_fallbacks | varchar(100) | YES | | NULL | |
| number_of_upshifts | varchar(100) | YES | | NULL | |
| number_of_link_naks | varchar(100) | YES | | NULL | |
| back_channel_data_rate | varchar(100) | YES | | NULL | |
| simplified_mnp_levels | varchar(100) | YES | | NULL | |
| simplified_usage | varchar(100) | YES | | NULL | |
| pw_vpn_id | varchar(100) | YES | | NULL | |
| gpp_rate_type | varchar(100) | YES | MUL | NULL | |
| gpp_imeisv | varchar(100) | YES | MUL | NULL | |
| gpp_ms_timezone | varchar(100) | YES | | NULL | |
| gpp_sgsn_address | varchar(100) | YES | | NULL | |
| gpp_sgsn | varchar(100) | YES | | NULL | |
| gpp_charging_id | varchar(100) | YES | | NULL | |
| gpp_pdp_type | varchar(100) | YES | | NULL | |
| gpp_gprs_qos_negotiated_prof | varchar(100) | YES | | NULL | |
+------------------------------+--------------+------+-----+---------+-------+

As u saw it consists of 120 fields.
Every day i collect logs of 6 millions of records (2 GB).
I should keep logs of at least 90 days before.

So i need all your recommendations to make mysql more optimized and work in a very tuned status so that i can search and create reports quickly.

Options: ReplyQuote


Subject
Views
Written By
Posted
mysql TABLE tuning and performance
3031
March 29, 2009 08: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.