mysql TABLE tuning and performance
Posted by: talal hegab
Date: March 29, 2009 08:47AM
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.
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.
Subject
Views
Written By
Posted
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.