MySQL Forums :: Performance :: mysql TABLE tuning and performance


Advanced Search

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 2843 talal hegab 03/29/2009 08:47AM
Re: mysql TABLE tuning and performance 1776 Rick James 03/29/2009 10:35PM


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.