Доброе время суток уважаемые админы.
Пользуемся сборкой 5.3-001. ОС Дебина, Processor Xeon CPU E5-2640 v2 @ 2.00GHz, ОЗУ 32Гб
Последнее время заметил что билинг стал медленнее работать.
Оказалось что всю оперативную память использует mysql.
Размер базы UTM5 вырос до 162715Mb, тип innodb.
Делал дамп базы с помощью mysqldump и через utm5_backup.sh
Не могли бы посоветовать что сделать чтоб исправить ситуацию.
Заранее благодарю за помощь.
Оптимизация базы
Размер таблиц и общий размер базы ниже.
каким образом можно очистить ненужную таблицу?
каким образом можно очистить ненужную таблицу?
Код: Выделить всё
mysql> SELECT table_name, data_length, index_length FROM information_schema.tables WHERE table_schema = 'UTM5';
+-------------------------------------+-------------+--------------+
| table_name | data_length | index_length |
+-------------------------------------+-------------+--------------+
| account_invoice | 16384 | 0 |
| account_tariff_link | 1589248 | 409600 |
| accounts | 1589248 | 180224 |
| accounts_shape | 1377 | 4096 |
| acts | 16384 | 0 |
| acts_templates | 16384 | 0 |
| additional_ip_zones2houses | 16384 | 0 |
| archives | 16384 | 16384 |
| balance_history | 12075008 | 0 |
| banks | 16384 | 0 |
| blocks_info | 11026432 | 14778368 |
| card_info | 16384 | 16384 |
| card_info_expired | 16384 | 0 |
| card_pool_info | 16384 | 0 |
| card_pool_owners | 16384 | 0 |
| contracts | 16384 | 0 |
| contracts_templates | 16384 | 0 |
| credits | 2637824 | 0 |
| currency_list | 16384 | 0 |
| currency_rates | 16384 | 0 |
| custom_services | 16384 | 0 |
| dealer_access_list | 16384 | 0 |
| dealer_details | 16384 | 0 |
| dealer_log | 16384 | 0 |
| dhs_access_log | 16384 | 0 |
| dhs_access_log_attrs | 16384 | 0 |
| dhs_sessions_detail | 252428288 | 0 |
| dhs_sessions_log | 706740224 | 185843712 |
| dhs_sessions_log_attrs | 16384 | 0 |
| dialup_cost_info | 49152 | 0 |
| dialup_service_links | 1589248 | 0 |
| dialup_services_data | 49152 | 0 |
| dir_zones | 16384 | 0 |
| discount_periods | 16384 | 16384 |
| discount_transactions_all | 32544653312 | 51637125120 |
| discount_transactions_iptraffic_all | 36834377728 | 48021635072 |
| downloaded | 5783552 | 2637824 |
| dtagg_cust | 16384 | 0 |
| dtagg_hotspot | 1589248 | 245760 |
| dtagg_iptraffic | 4734976 | 4227072 |
| dtagg_once | 327680 | 196608 |
| dtagg_periodic | 1589248 | 294912 |
| dtagg_telephony | 16384 | 0 |
| dtagg_telephony_v2 | 16384 | 16384 |
| dynashape_borders | 147456 | 0 |
| dynashape_data | 393216 | 0 |
| dynashape_radius_attrs | 81920 | 0 |
| dynashape_services | 81920 | 0 |
| dynashape_settings | 16384 | 0 |
| dynashape_tclasses | 196608 | 0 |
| firewall_rules | 16384 | 0 |
| firewall_rules_new | 49152 | 0 |
| funds_flow_settings | 16384 | 0 |
| funds_flow_transactions | 16384 | 0 |
| groups | 16384 | 0 |
| hotspot_allowed_net | 16384 | 0 |
| hotspot_cost_info | 16384 | 0 |
| hotspot_network_list | 16384 | 0 |
| hotspot_service_links | 16384 | 0 |
| hotspot_services_data | 16384 | 0 |
| houses | 16384 | 0 |
| ic_integration | 16384 | 0 |
| ic_settings | 16384 | 0 |
| invoice_entry | 23642112 | 2637824 |
| invoice_entry_details | 458752 | 180224 |
| invoices | 14172160 | 4734976 |
| ip_groups | 177897472 | 248512512 |
| ip_zones | 16384 | 0 |
| ip_zones_detail | 16384 | 0 |
| iptraffic_borders | 49152 | 0 |
| iptraffic_service_links | 1589248 | 1572864 |
| iptraffic_services_data | 65536 | 0 |
| license_data | 32768 | 0 |
| messages | 98304 | 32768 |
| messages_status | 16384 | 16384 |
| nas_info | 16384 | 0 |
| once_service_data | 16384 | 0 |
| once_service_links | 1589248 | 0 |
| payment_methods | 16384 | 0 |
| payment_transactions | 18366464 | 12107776 |
| payments_timed | 16384 | 0 |
| periodic_service_links | 3686400 | 0 |
| periodic_services_data | 98304 | 0 |
| promised_payment_data | 16384 | 0 |
| promised_payment_settings | 16384 | 0 |
| radius_data | 294912 | 114688 |
| radius_pool_info | 16384 | 0 |
| routers_info | 16384 | 0 |
| service_links | 2637824 | 2555904 |
| services_data | 229376 | 163840 |
| slink_tech_param | 16384 | 0 |
| slink_tech_param_type | 16384 | 0 |
| supplier_charges | 16384 | 0 |
| supplier_info | 16384 | 0 |
| supplier_payments | 16384 | 0 |
| switch_tariff_data | 16384 | 0 |
| switch_tariff_settings | 16384 | 0 |
| system_accounts | 16384 | 0 |
| system_group_info | 16384 | 0 |
| system_groups | 16384 | 0 |
| system_rights | 65536 | 0 |
| t_class | 16384 | 0 |
| t_class_detail | 131072 | 0 |
| tariffs | 114688 | 0 |
| tariffs_history | 2637824 | 0 |
| tariffs_services_link | 81920 | 0 |
| tel_consumption_info | 16384 | 16384 |
| tel_directions | 16384 | 0 |
| tel_directions_v2 | 16384 | 0 |
| tel_emergency_calls | 16384 | 0 |
| tel_numbers | 16384 | 0 |
| tel_service_links | 16384 | 0 |
| tel_service_links_downloaded | 16384 | 0 |
| tel_service_links_prepaid | 16384 | 0 |
| tel_services_borders | 16384 | 0 |
| tel_services_data | 16384 | 0 |
| tel_services_fixed_cost | 16384 | 0 |
| tel_services_prepaid | 16384 | 0 |
| tel_services_tr_mult | 16384 | 0 |
| tel_sessions_detail | 16384 | 16384 |
| tel_sessions_log | 16384 | 65536 |
| tel_sessions_log_attrs | 16384 | 0 |
| tel_zones | 16384 | 0 |
| tel_zones_v2 | 16384 | 0 |
| temp_ip | 16384 | 0 |
| template_type | 16384 | 0 |
| time_range | 16384 | 0 |
| time_range_days | 16384 | 0 |
| time_range_detail | 16384 | 0 |
| traf_serv_tclasses | 49152 | 0 |
| turbo_mode_links | 399 | 2048 |
| turbo_mode_settings | 198 | 2048 |
| uaddparams_desc | 16384 | 0 |
| user_additional_params | 16384 | 0 |
| user_contact_em | 16384 | 0 |
| user_contacts | 1589248 | 0 |
| user_log | 32047104 | 0 |
| users | 5783552 | 0 |
| users_accounts | 425984 | 0 |
| users_groups_link | 2162688 | 1064960 |
| utm5_cashier_settings | 16384 | 0 |
| utm5_settings | 16384 | 16384 |
| utm5_tray_settings | 16384 | 0 |
| voluntary_suspension_data | 16384 | 0 |
| voluntary_suspension_settings | 16384 | 0 |
+-------------------------------------+-------------+--------------+
145 rows in set (12.08 sec)
mysql> SELECT table_schema "DB Name",
-> Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"
-> FROM information_schema.tables
-> GROUP BY table_schema;
+--------------------+---------------+
| DB Name | DB Size in MB |
+--------------------+---------------+
| information_schema | 0.0 |
| mysql | 0.6 |
| performance_schema | 0.0 |
| UTM5 | 162905.5 |
+--------------------+---------------+
4 rows in set (5.64 sec)
Скрип который написал JAO и своем посте Rico-X текстом написал
cat utm521_arc_clear_old и cat utm521007_arc будет работать на сборкой 5.3-001 не нужно нечего менять?
(viewtopic.php?t=8316&postdays=0&postorder=asc&start=45 )
Заранее благодарю за помощь.
cat utm521_arc_clear_old и cat utm521007_arc будет работать на сборкой 5.3-001 не нужно нечего менять?
(viewtopic.php?t=8316&postdays=0&postorder=asc&start=45 )
Заранее благодарю за помощь.