serbinСтремящайся
Продакшн сервер имеет 64 ГБ оперативной памяти и 8 Ядер процессора.
Стандартные настройки Битрикс использую всего 2Гб оперативной памяти.
Нужно найти оптимальную конфигурацию и настроить сервер MySQL
Проверяем что таблицы на Сервере InnoDB
+--------------------------------------------+--------+ | TABLE_NAME | ENGINE | +--------------------------------------------+--------+ | b_abtest | InnoDB | | b_admin_notify | InnoDB | | b_admin_notify_lang | InnoDB | | b_adv_banner | InnoDB | | b_adv_banner_2_country | InnoDB | | b_adv_banner_2_day | InnoDB | | b_adv_banner_2_group | InnoDB | | b_adv_banner_2_page | InnoDB | | b_adv_banner_2_site | InnoDB | | b_adv_banner_2_stat_adv | InnoDB | | b_adv_banner_2_weekday | InnoDB | | b_adv_contract | InnoDB | | b_adv_contract_2_page | InnoDB | | b_adv_contract_2_site | InnoDB | | b_adv_contract_2_type | InnoDB | | b_adv_contract_2_user | InnoDB | | b_adv_contract_2_weekday | InnoDB | | b_adv_type | InnoDB | | b_agent | InnoDB | | b_app_password | InnoDB | | b_b24connector_button_site | InnoDB | | b_b24connector_buttons | InnoDB | | b_bitrixcloud_option | InnoDB | | b_blog | InnoDB | | b_blog_category | InnoDB | | b_blog_comment | InnoDB | | b_blog_group | InnoDB | | b_blog_image | InnoDB | | b_blog_post | InnoDB | | b_blog_post_category | InnoDB | | b_blog_post_param | InnoDB | | b_blog_site_path | InnoDB | | b_blog_socnet | InnoDB | | b_blog_socnet_rights | InnoDB | | b_blog_trackback | InnoDB | | b_blog_user | InnoDB | | b_blog_user2blog | InnoDB | | b_blog_user2user_group | InnoDB | | b_blog_user_group | InnoDB | | b_blog_user_group_perms | InnoDB | | b_bp_automation_trigger | InnoDB | | b_bp_debugger_session | InnoDB | | b_bp_debugger_session_document | InnoDB | | b_bp_debugger_session_template_shards | InnoDB | | b_bp_debugger_session_workflow_context | InnoDB | | b_bp_global_const | InnoDB | | b_bp_global_var | InnoDB | | b_bp_history | InnoDB | | b_bp_rest_activity | InnoDB | | b_bp_rest_provider | InnoDB | | b_bp_scheduler_event | InnoDB | | b_bp_script | InnoDB | | b_bp_script_queue | InnoDB | | b_bp_script_queue_document | InnoDB | | b_bp_storage_activity | InnoDB | | b_bp_task | InnoDB | | b_bp_task_user | InnoDB | | b_bp_tracking | InnoDB | | b_bp_workflow_duration_stat | InnoDB | | b_bp_workflow_instance | InnoDB | | b_bp_workflow_meta | InnoDB | | b_bp_workflow_permissions | InnoDB | | b_bp_workflow_state | InnoDB | | b_bp_workflow_state_identify | InnoDB | | b_bp_workflow_template | InnoDB | | b_bp_workflow_user | InnoDB | | b_cache_tag | InnoDB | | b_calendar_access | InnoDB | | b_calendar_attendees | InnoDB | | b_calendar_event | InnoDB | | b_calendar_event_connection | InnoDB | | b_calendar_event_original_recursion | InnoDB | | b_calendar_event_sect | InnoDB | | b_calendar_location | InnoDB | | b_calendar_log | InnoDB | | b_calendar_push | InnoDB | | b_calendar_queue_handled_message | InnoDB | | b_calendar_queue_message | InnoDB | | b_calendar_resource | InnoDB | | b_calendar_room_category | InnoDB | | b_calendar_section | InnoDB | | b_calendar_section_connection | InnoDB | | b_calendar_sharing_link | InnoDB | | b_calendar_sharing_link_member | InnoDB | | b_calendar_sharing_link_rule | InnoDB | | b_calendar_sharing_object_rule | InnoDB | | b_calendar_type | InnoDB | | b_captcha | InnoDB | | b_catalog_agent_contract | InnoDB | | b_catalog_agent_contract_file | InnoDB | | b_catalog_agent_product | InnoDB | | b_catalog_contractor | InnoDB | | b_catalog_currency | InnoDB | | b_catalog_currency_lang | InnoDB | | b_catalog_currency_rate | InnoDB | | b_catalog_disc_save_group | InnoDB | | b_catalog_disc_save_range | InnoDB | | b_catalog_disc_save_user | InnoDB | | b_catalog_discount | InnoDB | | b_catalog_discount2cat | InnoDB | | b_catalog_discount2group | InnoDB | | b_catalog_discount2iblock | InnoDB | | b_catalog_discount2product | InnoDB | | b_catalog_discount2section | InnoDB | | b_catalog_discount_cond | InnoDB | | b_catalog_discount_coupon | InnoDB | | b_catalog_discount_entity | InnoDB | | b_catalog_discount_module | InnoDB | | b_catalog_docs_barcode | InnoDB | | b_catalog_docs_element | InnoDB | | b_catalog_export | InnoDB | | b_catalog_exported_product | InnoDB | | b_catalog_exported_product_queue | InnoDB | | b_catalog_extra | InnoDB | | b_catalog_group | InnoDB | | b_catalog_group2group | InnoDB | | b_catalog_group_lang | InnoDB | | b_catalog_iblock | InnoDB | | b_catalog_load | InnoDB | | b_catalog_measure | InnoDB | | b_catalog_measure_ratio | InnoDB | | b_catalog_permission | InnoDB | | b_catalog_price | InnoDB | | b_catalog_product | InnoDB | | b_catalog_product2group | InnoDB | | b_catalog_product_compilation | InnoDB | | b_catalog_product_sets | InnoDB | | b_catalog_role | InnoDB | | b_catalog_role_relation | InnoDB | | b_catalog_rounding | InnoDB | | b_catalog_store | InnoDB | | b_catalog_store_barcode | InnoDB | | b_catalog_store_batch | InnoDB | | b_catalog_store_batch_docs_element | InnoDB | | b_catalog_store_docs | InnoDB | | b_catalog_store_document_file | InnoDB | | b_catalog_store_product | InnoDB | | b_catalog_subscribe | InnoDB | | b_catalog_subscribe_access | InnoDB | | b_catalog_vat | InnoDB | | b_catalog_viewed_product | InnoDB | | b_checklist | InnoDB | | b_clouds_copy_queue | InnoDB | | b_clouds_delete_queue | InnoDB | | b_clouds_file_bucket | InnoDB | | b_clouds_file_hash | InnoDB | | b_clouds_file_resize | InnoDB | | b_clouds_file_save | InnoDB | | b_clouds_file_upload | InnoDB | | b_component_params | InnoDB | | b_composite_log | InnoDB | | b_composite_page | InnoDB | | b_consent_agreement | InnoDB | | b_consent_field | InnoDB | | b_consent_user_consent | InnoDB | | b_consent_user_consent_item | InnoDB | | b_conv_context | InnoDB | | b_conv_context_attribute | InnoDB | | b_conv_context_counter_day | InnoDB | | b_conv_context_entity_item | InnoDB | | b_counter_data | InnoDB | | b_culture | InnoDB | | b_entity_usage | InnoDB | | b_event | InnoDB | | b_event_attachment | InnoDB | | b_event_log | InnoDB | | b_event_message | InnoDB | | b_event_message_attachment | InnoDB | | b_event_message_site | InnoDB | | b_event_type | InnoDB | | b_favorite | InnoDB | | b_file | InnoDB | | b_file_duplicate | InnoDB | | b_file_hash | InnoDB | | b_file_preview | InnoDB | | b_file_search | InnoDB | | b_file_version | InnoDB | | b_filters | InnoDB | | b_finder_dest | InnoDB | | b_form | InnoDB | | b_form_2_group | InnoDB | | b_form_2_mail_template | InnoDB | | b_form_2_site | InnoDB | | b_form_answer | InnoDB | | b_form_crm | InnoDB | | b_form_crm_field | InnoDB | | b_form_crm_link | InnoDB | | b_form_field | InnoDB | | b_form_field_filter | InnoDB | | b_form_field_validator | InnoDB | | b_form_menu | InnoDB | | b_form_result | InnoDB | | b_form_result_answer | InnoDB | | b_form_status | InnoDB | | b_form_status_2_group | InnoDB | | b_form_status_2_mail_template | InnoDB | | b_forum | InnoDB | | b_forum2site | InnoDB | | b_forum_dictionary | InnoDB | | b_forum_email | InnoDB | | b_forum_file | InnoDB | | b_forum_filter | InnoDB | | b_forum_group | InnoDB | | b_forum_group_lang | InnoDB | | b_forum_letter | InnoDB | | b_forum_message | InnoDB | | b_forum_perms | InnoDB | | b_forum_pm_folder | InnoDB | | b_forum_points | InnoDB | | b_forum_points2post | InnoDB | | b_forum_points_lang | InnoDB | | b_forum_private_message | InnoDB | | b_forum_rank | InnoDB | | b_forum_rank_lang | InnoDB | | b_forum_service_deleted_message | InnoDB | | b_forum_service_statistic_queue | InnoDB | | b_forum_stat | InnoDB | | b_forum_subscribe | InnoDB | | b_forum_topic | InnoDB | | b_forum_user | InnoDB | | b_forum_user_forum | InnoDB | | b_forum_user_points | InnoDB | | b_forum_user_topic | InnoDB | | b_geoip_handlers | InnoDB | | b_geoname | InnoDB | | b_group | InnoDB | | b_group_collection_task | InnoDB | | b_group_subordinate | InnoDB | | b_group_task | InnoDB | | b_hlblock_entity | InnoDB | | b_hlblock_entity_lang | InnoDB | | b_hlblock_entity_rights | InnoDB | | b_hlsys_marking_code_group | InnoDB | | b_hot_keys | InnoDB | | b_hot_keys_code | InnoDB | | b_iblock | InnoDB | | b_iblock_28_index | InnoDB | | b_iblock_28_index_val | InnoDB | | b_iblock_33_index | InnoDB | | b_iblock_33_index_val | InnoDB | | b_iblock_cache | InnoDB | | b_iblock_element | InnoDB | | b_iblock_element_iprop | InnoDB | | b_iblock_element_lock | InnoDB | | b_iblock_element_property | InnoDB | | b_iblock_element_right | InnoDB | | b_iblock_fields | InnoDB | | b_iblock_group | InnoDB | | b_iblock_iblock_iprop | InnoDB | | b_iblock_iproperty | InnoDB | | b_iblock_messages | InnoDB | | b_iblock_offers_tmp | InnoDB | | b_iblock_property | InnoDB | | b_iblock_property_enum | InnoDB | | b_iblock_property_feature | InnoDB | | b_iblock_right | InnoDB | | b_iblock_rss | InnoDB | | b_iblock_section | InnoDB | | b_iblock_section_element | InnoDB | | b_iblock_section_iprop | InnoDB | | b_iblock_section_property | InnoDB | | b_iblock_section_right | InnoDB | | b_iblock_sequence | InnoDB | | b_iblock_site | InnoDB | | b_iblock_type | InnoDB | | b_iblock_type_lang | InnoDB | | b_idea_email_subscribe | InnoDB | | b_im_alias | InnoDB | | b_im_app | InnoDB | | b_im_app_lang | InnoDB | | b_im_block_user | InnoDB | | b_im_bot | InnoDB | | b_im_bot_chat | InnoDB | | b_im_bot_token | InnoDB | | b_im_call | InnoDB | | b_im_call_user | InnoDB | | b_im_chat | InnoDB | | b_im_chat_index | InnoDB | | b_im_command | InnoDB | | b_im_command_lang | InnoDB | | b_im_conference | InnoDB | | b_im_conference_user_role | InnoDB | | b_im_external_avatar | InnoDB | | b_im_file_temporary | InnoDB | | b_im_last_search | InnoDB | | b_im_link_calendar | InnoDB | | b_im_link_calendar_index | InnoDB | | b_im_link_favorite | InnoDB | | b_im_link_file | InnoDB | | b_im_link_pin | InnoDB | | b_im_link_reminder | InnoDB | | b_im_link_task | InnoDB | | b_im_link_url | InnoDB | | b_im_link_url_index | InnoDB | | b_im_log | InnoDB | | b_im_message | InnoDB | | b_im_message_disappearing | InnoDB | | b_im_message_favorite | InnoDB | | b_im_message_index | InnoDB | | b_im_message_param | InnoDB | | b_im_message_unread | InnoDB | | b_im_message_uuid | InnoDB | | b_im_message_viewed | InnoDB | | b_im_no_relation_permission_disk | InnoDB | | b_im_option_access | InnoDB | | b_im_option_group | InnoDB | | b_im_option_state | InnoDB | | b_im_option_user | InnoDB | | b_im_permission | InnoDB | | b_im_permission_duration | InnoDB | | b_im_permission_log | InnoDB | | b_im_reaction | InnoDB | | b_im_recent | InnoDB | | b_im_relation | InnoDB | | b_im_status | InnoDB | | b_landing | InnoDB | | b_landing_binding | InnoDB | | b_landing_block | InnoDB | | b_landing_block_last_used | InnoDB | | b_landing_chat | InnoDB | | b_landing_chat_binding | InnoDB | | b_landing_cookies_agreement | InnoDB | | b_landing_demo | InnoDB | | b_landing_designer_repo | InnoDB | | b_landing_domain | InnoDB | | b_landing_entity_lock | InnoDB | | b_landing_entity_rights | InnoDB | | b_landing_file | InnoDB | | b_landing_filter_block | InnoDB | | b_landing_filter_entity | InnoDB | | b_landing_folder | InnoDB | | b_landing_history | InnoDB | | b_landing_history_step | InnoDB | | b_landing_hook_data | InnoDB | | b_landing_placement | InnoDB | | b_landing_repo | InnoDB | | b_landing_role | InnoDB | | b_landing_site | InnoDB | | b_landing_syspage | InnoDB | | b_landing_template | InnoDB | | b_landing_template_ref | InnoDB | | b_landing_update_block | InnoDB | | b_landing_urlchecker_host | InnoDB | | b_landing_urlchecker_status | InnoDB | | b_landing_urlchecker_whitelist | InnoDB | | b_landing_urlrewrite | InnoDB | | b_landing_view | InnoDB | | b_lang | InnoDB | | b_lang_domain | InnoDB | | b_language | InnoDB | | b_learn_answer | InnoDB | | b_learn_attempt | InnoDB | | b_learn_certification | InnoDB | | b_learn_chapter | InnoDB | | b_learn_course | InnoDB | | b_learn_course_site | InnoDB | | b_learn_exceptions_log | InnoDB | | b_learn_gradebook | InnoDB | | b_learn_groups | InnoDB | | b_learn_groups_lesson | InnoDB | | b_learn_groups_member | InnoDB | | b_learn_lesson | InnoDB | | b_learn_lesson_edges | InnoDB | | b_learn_publish_prohibition | InnoDB | | b_learn_question | InnoDB | | b_learn_rights | InnoDB | | b_learn_rights_all | InnoDB | | b_learn_site_path | InnoDB | | b_learn_student | InnoDB | | b_learn_test | InnoDB | | b_learn_test_mark | InnoDB | | b_learn_test_result | InnoDB | | b_list_rubric | InnoDB | | b_lists_field | InnoDB | | b_lists_permission | InnoDB | | b_lists_socnet_group | InnoDB | | b_lists_url | InnoDB | | b_location | InnoDB | | b_location_addr_fld | InnoDB | | b_location_addr_link | InnoDB | | b_location_address | InnoDB | | b_location_area | InnoDB | | b_location_field | InnoDB | | b_location_hierarchy | InnoDB | | b_location_name | InnoDB | | b_location_recent_address | InnoDB | | b_location_source | InnoDB | | b_log_notification | InnoDB | | b_log_notification_action | InnoDB | | b_mail_blacklist | InnoDB | | b_mail_contact | InnoDB | | b_mail_counter | InnoDB | | b_mail_domain_email | InnoDB | | b_mail_entity_options | InnoDB | | b_mail_filter | InnoDB | | b_mail_filter_cond | InnoDB | | b_mail_log | InnoDB | | b_mail_mailbox | InnoDB | | b_mail_mailbox_access | InnoDB | | b_mail_mailbox_dir | InnoDB | | b_mail_mailservices | InnoDB | | b_mail_message | InnoDB | | b_mail_message_access | InnoDB | | b_mail_message_closure | InnoDB | | b_mail_message_delete_queue | InnoDB | | b_mail_message_uid | InnoDB | | b_mail_message_upload_queue | InnoDB | | b_mail_msg_attachment | InnoDB | | b_mail_oauth | InnoDB | | b_mail_spam_weight | InnoDB | | b_mail_user_message | InnoDB | | b_mail_user_relations | InnoDB | | b_mail_user_signature | InnoDB | | b_main_mail_blacklist | InnoDB | | b_main_mail_sender | InnoDB | | b_main_mail_sender_send_counter | InnoDB | | b_medialib_collection | InnoDB | | b_medialib_collection_item | InnoDB | | b_medialib_item | InnoDB | | b_medialib_type | InnoDB | | b_messageservice_channel | InnoDB | | b_messageservice_incoming_message | InnoDB | | b_messageservice_message | InnoDB | | b_messageservice_rest_app | InnoDB | | b_messageservice_rest_app_lang | InnoDB | | b_messageservice_restriction | InnoDB | | b_mobileapp_app | InnoDB | | b_mobileapp_config | InnoDB | | b_module | InnoDB | | b_module_group | InnoDB | | b_module_to_module | InnoDB | | b_numerator | InnoDB | | b_numerator_sequence | InnoDB | | b_operation | InnoDB | | b_option | InnoDB | | b_option_site | InnoDB | | b_perf_cache | InnoDB | | b_perf_cluster | InnoDB | | b_perf_component | InnoDB | | b_perf_error | InnoDB | | b_perf_history | InnoDB | | b_perf_hit | InnoDB | | b_perf_index_ban | InnoDB | | b_perf_index_complete | InnoDB | | b_perf_index_suggest | InnoDB | | b_perf_index_suggest_sql | InnoDB | | b_perf_sql | InnoDB | | b_perf_sql_backtrace | InnoDB | | b_perf_tab_column_stat | InnoDB | | b_perf_tab_stat | InnoDB | | b_perf_test | InnoDB | | b_posting | InnoDB | | b_posting_email | InnoDB | | b_posting_file | InnoDB | | b_posting_group | InnoDB | | b_posting_rubric | InnoDB | | b_pull_channel | InnoDB | | b_pull_push | InnoDB | | b_pull_push_queue | InnoDB | | b_pull_stack | InnoDB | | b_pull_watch | InnoDB | | b_rating | InnoDB | | b_rating_component | InnoDB | | b_rating_component_results | InnoDB | | b_rating_prepare | InnoDB | | b_rating_results | InnoDB | | b_rating_rule | InnoDB | | b_rating_rule_vetting | InnoDB | | b_rating_user | InnoDB | | b_rating_vote | InnoDB | | b_rating_vote_group | InnoDB | | b_rating_voting | InnoDB | | b_rating_voting_prepare | InnoDB | | b_rating_voting_reaction | InnoDB | | b_rating_weight | InnoDB | | b_report | InnoDB | | b_report_sharing | InnoDB | | b_report_visual_report_configuration | InnoDB | | b_report_visual_report_dashboard | InnoDB | | b_report_visual_report_dashboard_row | InnoDB | | b_report_visual_report_entity | InnoDB | | b_report_visual_report_entity_config | InnoDB | | b_report_visual_report_widget | InnoDB | | b_report_visual_report_widget_config | InnoDB | | b_rest_ap | InnoDB | | b_rest_ap_permission | InnoDB | | b_rest_app | InnoDB | | b_rest_app_lang | InnoDB | | b_rest_app_log | InnoDB | | b_rest_configuration_storage | InnoDB | | b_rest_event | InnoDB | | b_rest_event_offline | InnoDB | | b_rest_integration | InnoDB | | b_rest_log | InnoDB | | b_rest_owner_entity | InnoDB | | b_rest_placement | InnoDB | | b_rest_placement_lang | InnoDB | | b_rest_stat | InnoDB | | b_rest_stat_app | InnoDB | | b_rest_stat_method | InnoDB | | b_rest_usage_entity | InnoDB | | b_rest_usage_stat | InnoDB | | b_sale_affiliate | InnoDB | | b_sale_affiliate_plan | InnoDB | | b_sale_affiliate_plan_section | InnoDB | | b_sale_affiliate_tier | InnoDB | | b_sale_affiliate_transact | InnoDB | | b_sale_analytics | InnoDB | | b_sale_auxiliary | InnoDB | | b_sale_b24integration_bind | InnoDB | | b_sale_b24integration_relation | InnoDB | | b_sale_b24integration_stat | InnoDB | | b_sale_b24integration_stat_provider | InnoDB | | b_sale_b24integration_token | InnoDB | | b_sale_basket | InnoDB | | b_sale_basket_archive | InnoDB | | b_sale_basket_archive_packed | InnoDB | | b_sale_basket_props | InnoDB | | b_sale_basket_reservation | InnoDB | | b_sale_basket_reservation_history | InnoDB | | b_sale_bizval | InnoDB | | b_sale_bizval_code_1c | InnoDB | | b_sale_bizval_persondomain | InnoDB | | b_sale_buyer_stat | InnoDB | | b_sale_cashbox | InnoDB | | b_sale_cashbox_check | InnoDB | | b_sale_cashbox_check_correction | InnoDB | | b_sale_cashbox_connect | InnoDB | | b_sale_cashbox_err_log | InnoDB | | b_sale_cashbox_rest_handler | InnoDB | | b_sale_cashbox_z_report | InnoDB | | b_sale_check2cashbox | InnoDB | | b_sale_check_related_entities | InnoDB | | b_sale_company | InnoDB | | b_sale_company2location | InnoDB | | b_sale_company2service | InnoDB | | b_sale_company_group | InnoDB | | b_sale_company_resp_grp | InnoDB | | b_sale_d_ix_element | InnoDB | | b_sale_d_ix_section | InnoDB | | b_sale_delivery2location | InnoDB | | b_sale_delivery2paysystem | InnoDB | | b_sale_delivery_es | InnoDB | | b_sale_delivery_req | InnoDB | | b_sale_delivery_req_shp | InnoDB | | b_sale_delivery_rest_handler | InnoDB | | b_sale_delivery_srv | InnoDB | | b_sale_delivery_yandex_taxi_claims | InnoDB | | b_sale_discount | InnoDB | | b_sale_discount_coupon | InnoDB | | b_sale_discount_entities | InnoDB | | b_sale_discount_group | InnoDB | | b_sale_discount_module | InnoDB | | b_sale_documentgenerator_callback_registry | InnoDB | | b_sale_domain_verification | InnoDB | | b_sale_entity_marker | InnoDB | | b_sale_exchange_log | InnoDB | | b_sale_export | InnoDB | | b_sale_facebook_conversion_params | InnoDB | | b_sale_fuser | InnoDB | | b_sale_gift_related_data | InnoDB | | b_sale_hdaln | InnoDB | | b_sale_lang | InnoDB | | b_sale_loc_2site | InnoDB | | b_sale_loc_def2site | InnoDB | | b_sale_loc_ext | InnoDB | | b_sale_loc_ext_srv | InnoDB | | b_sale_loc_name | InnoDB | | b_sale_loc_type | InnoDB | | b_sale_loc_type_name | InnoDB | | b_sale_location | InnoDB | | b_sale_location2location_group | InnoDB | | b_sale_location_city | InnoDB | | b_sale_location_city_lang | InnoDB | | b_sale_location_country | InnoDB | | b_sale_location_country_lang | InnoDB | | b_sale_location_group | InnoDB | | b_sale_location_group_lang | InnoDB | | b_sale_location_region | InnoDB | | b_sale_location_region_lang | InnoDB | | b_sale_location_zip | InnoDB | | b_sale_order | InnoDB | | b_sale_order_archive | InnoDB | | b_sale_order_archive_packed | InnoDB | | b_sale_order_change | InnoDB | | b_sale_order_converter_crm_error | InnoDB | | b_sale_order_coupons | InnoDB | | b_sale_order_delivery | InnoDB | | b_sale_order_delivery_es | InnoDB | | b_sale_order_delivery_req | InnoDB | | b_sale_order_discount | InnoDB | | b_sale_order_discount_data | InnoDB | | b_sale_order_dlv_basket | InnoDB | | b_sale_order_entities_custom_fields | InnoDB | | b_sale_order_flags2group | InnoDB | | b_sale_order_history | InnoDB | | b_sale_order_modules | InnoDB | | b_sale_order_payment | InnoDB | | b_sale_order_payment_item | InnoDB | | b_sale_order_payment_ps_available | InnoDB | | b_sale_order_processing | InnoDB | | b_sale_order_product_stat | InnoDB | | b_sale_order_props | InnoDB | | b_sale_order_props_group | InnoDB | | b_sale_order_props_relation | InnoDB | | b_sale_order_props_value | InnoDB | | b_sale_order_props_variant | InnoDB | | b_sale_order_round | InnoDB | | b_sale_order_rules | InnoDB | | b_sale_order_rules_descr | InnoDB | | b_sale_order_tax | InnoDB | | b_sale_pay_system_action | InnoDB | | b_sale_pay_system_err_log | InnoDB | | b_sale_pay_system_rest_handlers | InnoDB | | b_sale_person_type | InnoDB | | b_sale_person_type_site | InnoDB | | b_sale_product2product | InnoDB | | b_sale_recurring | InnoDB | | b_sale_service_rstr | InnoDB | | b_sale_site2group | InnoDB | | b_sale_status | InnoDB | | b_sale_status_group_task | InnoDB | | b_sale_status_lang | InnoDB | | b_sale_store_barcode | InnoDB | | b_sale_synchronizer_log | InnoDB | | b_sale_tax | InnoDB | | b_sale_tax2location | InnoDB | | b_sale_tax_exempt2group | InnoDB | | b_sale_tax_rate | InnoDB | | b_sale_tp | InnoDB | | b_sale_tp_ebay_cat | InnoDB | | b_sale_tp_ebay_cat_var | InnoDB | | b_sale_tp_ebay_fq | InnoDB | | b_sale_tp_ebay_fr | InnoDB | | b_sale_tp_map | InnoDB | | b_sale_tp_map_entity | InnoDB | | b_sale_tp_order | InnoDB | | b_sale_tp_vk_log | InnoDB | | b_sale_tp_vk_profile | InnoDB | | b_sale_user_account | InnoDB | | b_sale_user_cards | InnoDB | | b_sale_user_props | InnoDB | | b_sale_user_props_value | InnoDB | | b_sale_user_transact | InnoDB | | b_sale_usergroup_restr | InnoDB | | b_sale_viewed_product | InnoDB | | b_sale_yandex_settings | InnoDB | | b_search_content | InnoDB | | b_search_content_freq | InnoDB | | b_search_content_param | InnoDB | | b_search_content_right | InnoDB | | b_search_content_site | InnoDB | | b_search_content_stem | InnoDB | | b_search_content_text | InnoDB | | b_search_content_title | InnoDB | | b_search_custom_rank | InnoDB | | b_search_phrase | InnoDB | | b_search_stem | InnoDB | | b_search_suggest | InnoDB | | b_search_tags | InnoDB | | b_search_user_right | InnoDB | | b_sec_filter_mask | InnoDB | | b_sec_frame_mask | InnoDB | | b_sec_iprule | InnoDB | | b_sec_iprule_excl_ip | InnoDB | | b_sec_iprule_excl_mask | InnoDB | | b_sec_iprule_incl_ip | InnoDB | | b_sec_iprule_incl_mask | InnoDB | | b_sec_recovery_codes | InnoDB | | b_sec_redirect_url | InnoDB | | b_sec_session | InnoDB | | b_sec_user | InnoDB | | b_sec_virus | InnoDB | | b_sec_white_list | InnoDB | | b_sec_xscan_results | InnoDB | | b_security_sitecheck | InnoDB | | b_sender_abuse | InnoDB | | b_sender_agreement | InnoDB | | b_sender_call_log | InnoDB | | b_sender_contact | InnoDB | | b_sender_contact_list | InnoDB | | b_sender_counter | InnoDB | | b_sender_counter_daily | InnoDB | | b_sender_file | InnoDB | | b_sender_group | InnoDB | | b_sender_group_connector | InnoDB | | b_sender_group_counter | InnoDB | | b_sender_group_data | InnoDB | | b_sender_group_deal_category | InnoDB | | b_sender_group_queue | InnoDB | | b_sender_group_state | InnoDB | | b_sender_group_thread | InnoDB | | b_sender_list | InnoDB | | b_sender_mailing | InnoDB | | b_sender_mailing_attachment | InnoDB | | b_sender_mailing_chain | InnoDB | | b_sender_mailing_chain_group | InnoDB | | b_sender_mailing_group | InnoDB | | b_sender_mailing_subscription | InnoDB | | b_sender_mailing_trigger | InnoDB | | b_sender_message | InnoDB | | b_sender_message_field | InnoDB | | b_sender_message_utm | InnoDB | | b_sender_permission | InnoDB | | b_sender_posting | InnoDB | | b_sender_posting_click | InnoDB | | b_sender_posting_read | InnoDB | | b_sender_posting_recipient | InnoDB | | b_sender_posting_thread | InnoDB | | b_sender_posting_unsub | InnoDB | | b_sender_preset_template | InnoDB | | b_sender_queue | InnoDB | | b_sender_role | InnoDB | | b_sender_role_access | InnoDB | | b_sender_role_permission | InnoDB | | b_sender_role_relation | InnoDB | | b_sender_timeline_queue | InnoDB | | b_seo_adv_autolog | InnoDB | | b_seo_adv_banner | InnoDB | | b_seo_adv_campaign | InnoDB | | b_seo_adv_group | InnoDB | | b_seo_adv_link | InnoDB | | b_seo_adv_log | InnoDB | | b_seo_adv_order | InnoDB | | b_seo_adv_region | InnoDB | | b_seo_keywords | InnoDB | | b_seo_search_engine | InnoDB | | b_seo_service_log | InnoDB | | b_seo_service_queue | InnoDB | | b_seo_service_rtg_queue | InnoDB | | b_seo_service_subscription | InnoDB | | b_seo_service_webhook | InnoDB | | b_seo_sitemap | InnoDB | | b_seo_sitemap_entity | InnoDB | | b_seo_sitemap_iblock | InnoDB | | b_seo_sitemap_job | InnoDB | | b_seo_sitemap_runtime | InnoDB | | b_seo_yandex_direct_stat | InnoDB | | b_short_uri | InnoDB | | b_sidepanel_toolbar | InnoDB | | b_sidepanel_toolbar_item | InnoDB | | b_site_template | InnoDB | | b_sm_version_history | InnoDB | | b_smile | InnoDB | | b_smile_lang | InnoDB | | b_smile_set | InnoDB | | b_sms_template | InnoDB | | b_sms_template_site | InnoDB | | b_socialservices_ap | InnoDB | | b_socialservices_contact | InnoDB | | b_socialservices_contact_connect | InnoDB | | b_socialservices_message | InnoDB | | b_socialservices_user | InnoDB | | b_socialservices_user_link | InnoDB | | b_socialservices_zoom_meeting | InnoDB | | b_socialservices_zoom_meeting_recording | InnoDB | | b_sonet_event_user_view | InnoDB | | b_sonet_features | InnoDB | | b_sonet_features2perms | InnoDB | | b_sonet_group | InnoDB | | b_sonet_group_favorites | InnoDB | | b_sonet_group_pin | InnoDB | | b_sonet_group_site | InnoDB | | b_sonet_group_subject | InnoDB | | b_sonet_group_subject_site | InnoDB | | b_sonet_group_tag | InnoDB | | b_sonet_group_view | InnoDB | | b_sonet_log | InnoDB | | b_sonet_log_comment | InnoDB | | b_sonet_log_counter | InnoDB | | b_sonet_log_events | InnoDB | | b_sonet_log_favorites | InnoDB | | b_sonet_log_follow | InnoDB | | b_sonet_log_index | InnoDB | | b_sonet_log_page | InnoDB | | b_sonet_log_pinned | InnoDB | | b_sonet_log_right | InnoDB | | b_sonet_log_site | InnoDB | | b_sonet_log_smartfilter | InnoDB | | b_sonet_log_subscribe | InnoDB | | b_sonet_log_tag | InnoDB | | b_sonet_log_view | InnoDB | | b_sonet_messages | InnoDB | | b_sonet_scorer | InnoDB | | b_sonet_scorer_event | InnoDB | | b_sonet_scorer_queue | InnoDB | | b_sonet_smile | InnoDB | | b_sonet_smile_lang | InnoDB | | b_sonet_space_composition | InnoDB | | b_sonet_space_user_latest_activity | InnoDB | | b_sonet_space_user_recent_activity | InnoDB | | b_sonet_subscription | InnoDB | | b_sonet_user2group | InnoDB | | b_sonet_user_content_view | InnoDB | | b_sonet_user_events | InnoDB | | b_sonet_user_perms | InnoDB | | b_sonet_user_relations | InnoDB | | b_sonet_user_tag | InnoDB | | b_sonet_user_welltory | InnoDB | | b_sonet_user_welltory_disclaimer | InnoDB | | b_stat_adv | InnoDB | | b_stat_adv_day | InnoDB | | b_stat_adv_event | InnoDB | | b_stat_adv_event_day | InnoDB | | b_stat_adv_guest | InnoDB | | b_stat_adv_page | InnoDB | | b_stat_adv_searcher | InnoDB | | b_stat_browser | InnoDB | | b_stat_city | InnoDB | | b_stat_city_day | InnoDB | | b_stat_city_ip | InnoDB | | b_stat_country | InnoDB | | b_stat_country_day | InnoDB | | b_stat_day | InnoDB | | b_stat_day_site | InnoDB | | b_stat_ddl | InnoDB | | b_stat_event | InnoDB | | b_stat_event_day | InnoDB | | b_stat_event_list | InnoDB | | b_stat_guest | InnoDB | | b_stat_hit | InnoDB | | b_stat_page | InnoDB | | b_stat_page_adv | InnoDB | | b_stat_path | InnoDB | | b_stat_path_adv | InnoDB | | b_stat_path_cache | InnoDB | | b_stat_phrase_list | InnoDB | | b_stat_referer | InnoDB | | b_stat_referer_list | InnoDB | | b_stat_searcher | InnoDB | | b_stat_searcher_day | InnoDB | | b_stat_searcher_hit | InnoDB | | b_stat_searcher_params | InnoDB | | b_stat_session | InnoDB | | b_stat_session_data | InnoDB | | b_sticker | InnoDB | | b_sticker_group_task | InnoDB | | b_stop_list | InnoDB | | b_subscription | InnoDB | | b_subscription_rubric | InnoDB | | b_task | InnoDB | +--------------------------------------------+--------+ 949 rows in set (0,01 sec)Делаем резервное копирование конфигов Mysql, для этого инициализируем git репозиторий в /etc/mysql
Сводка по БД через percona-toolkit
apt-get install percona-toolkit root@d-9116:/etc/mysql# pt-mysql-summary # Percona Toolkit MySQL Summary Report ####################### System time | 2024-10-18 07:33:46 UTC (local TZ: UTC +0000) # Instances ################################################## Port Data Directory Nice OOM Socket ===== ========================== ==== === ====== # MySQL Executable ########################################### Path to executable | /usr/sbin/mysqld Has symbols | No # Slave Hosts ################################################ No slaves found # Report On Port 3306 ######################################## User | root@localhost Time | 2024-10-18 07:33:46 (UTC) Hostname | d-9116 Version | 8.0.39-0ubuntu0.24.04.2 (Ubuntu) Built On | Linux x86_64 Started | 2024-10-16 06:37 (up 2+00:56:26) Databases | 5 Datadir | /var/lib/mysql/ Processes | 1 connected, 2 running Replication | Is not a slave, has 0 slaves connected Pidfile | /var/lib/mysql/d-9116.pid (exists) # Processlist ################################################ Command COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- Daemon 1 1 175000 175000 Query 1 1 0 0 User COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- event_scheduler 1 1 175000 175000 root 1 1 0 0 Host COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- localhost 2 2 175000 175000 db COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- NULL 2 2 175000 175000 State COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- init 1 1 0 0 Waiting on empty queue 1 1 175000 175000 # Status Counters (Wait 10 Seconds) ########################## Variable Per day Per second 10 secs Binlog_cache_disk_use 5 Binlog_cache_use 12500 10 Binlog_stmt_cache_use 450 2 Bytes_received 35000000 400 6000 Bytes_sent 100000000 1250 100000 Com_analyze 225 1 Com_begin 50 Com_check 225 1 Com_commit 50 Com_create_table 15 Com_delete 700 Com_delete_multi 1 Com_flush 1 Com_insert 900 1 Com_insert_select 450 Com_optimize 225 1 Com_replace 45 Com_select 40000 4 Com_set_option 35000 5 Com_show_fields 60 Com_show_keys 125 Com_show_status 1 Com_show_table_status 225 1 Com_show_tables 400 Com_show_variables 1 Com_update 10000 10 Com_update_multi 40 Connections 15000 2 Created_tmp_files 40 Created_tmp_tables 9000 Error_log_buffered_bytes 1250 Error_log_buffered_events 9 Error_log_latest_write 800000000000000 10000000000 Flush_commands 1 Handler_commit 80000 70 Handler_delete 7000 20 Handler_external_lock 350000 4 350 Handler_prepare 25000 25 Handler_read_first 25000 2 Handler_read_key 500000 5 1000 Handler_read_last 125 Handler_read_next 1500000 15 1250 Handler_read_prev 1500 Handler_read_rnd 1500 Handler_read_rnd_next 400000 4 100 Handler_rollback 5 Handler_update 15000 15 Handler_write 15000 80 Innodb_buffer_pool_bytes_data 60000000 700 -3276 Innodb_buffer_pool_bytes_dirty 8000000 90 3500 Innodb_buffer_pool_pages_flushed 50000 30 Innodb_buffer_pool_read_requests 4500000 50 7000 Innodb_buffer_pool_reads 2500 5 Innodb_buffer_pool_write_requests 450000 5 900 Innodb_data_fsyncs 70000 80 Innodb_data_read 40000000 450 90000 Innodb_data_reads 3000 7 Innodb_data_writes 125000 1 150 Innodb_data_written 900000000 10000 600000 Innodb_dblwr_pages_written 50000 30 Innodb_dblwr_writes 12500 8 Innodb_redo_log_uuid 800000000 9000 Innodb_redo_log_checkpoint_lsn 450000000 5000 100000 Innodb_redo_log_current_lsn 450000000 5000 50000 Innodb_redo_log_flushed_to_disk_lsn 450000000 5000 50000 Innodb_redo_log_logical_size 2250000 25 -48844 Innodb_redo_log_physical_size 30000000 350 Innodb_redo_log_capacity_resized 50000000 600 Innodb_log_write_requests 300000 3 700 Innodb_log_writes 60000 100 Innodb_os_log_fsyncs 35000 60 Innodb_os_log_written 50000000 600 80000 Innodb_pages_created 2000 6 Innodb_pages_read 2500 5 Innodb_pages_written 50000 30 Innodb_row_lock_time 60 Innodb_row_lock_waits 30 Innodb_rows_deleted 300 Innodb_rows_inserted 1500 5 Innodb_rows_read 1250000 15 15 Innodb_rows_updated 12500 10 Innodb_system_rows_deleted 7000 20 Innodb_system_rows_inserted 8000 25 Innodb_system_rows_read 1000000 10 2250 Innodb_system_rows_updated 2250 3 Innodb_num_open_files 300 Mysqlx_port 15000 Mysqlx_ssl_ctx_verify_mode 2 Open_table_definitions 300 1 Opened_table_definitions 1000 4 Opened_tables 2000 3 Performance_schema_session_connect_attrs_longest_seen 70 Queries 100000 1 30 Questions 100000 1 30 Select_full_join 200 Select_full_range_join 600 Select_range 2500 Select_scan 35000 2 Sort_rows 500000 6 900 Sort_scan 12500 1 Ssl_session_cache_timeout 150 Table_locks_immediate 3 Table_open_cache_hits 175000 2 175 Table_open_cache_misses 2000 3 Threads_created 4 Uptime 90000 1 1 # Table cache ################################################ Size | 4000 Usage | 40% # Key Percona Server features ################################ Table & Index Stats | Not Supported Multiple I/O Threads | Enabled Corruption Resilient | Not Supported Durable Replication | Not Supported Import InnoDB Tables | Not Supported Fast Server Restarts | Not Supported Enhanced Logging | Not Supported Replica Perf Logging | Disabled Response Time Hist. | Not Supported Smooth Flushing | Not Supported HandlerSocket NoSQL | Not Supported Fast Hash UDFs | Unknown # Percona XtraDB Cluster ##################################### # Plugins #################################################### InnoDB compression | ACTIVE # Schema ##################################################### Specify --databases or --all-databases to dump and summarize schemas # Noteworthy Technologies #################################### SSL | No Explicit LOCK TABLES | No Delayed Insert | No XA Transactions | No NDB Cluster | No Prepared Statements | No Prepared statement count | 0 # InnoDB ##################################################### Version | 8.0.39 Buffer Pool Size | 128,0M Buffer Pool Fill | 90% Buffer Pool Dirty | 10% File Per Table | ON Page Size | 16k Log File Size | 2 * 48,0M = 96,0M Log Buffer Size | 16M Flush Method | fsync Flush Log At Commit | 1 XA Support | Checksums | Doublewrite | ON R/W I/O Threads | 4 4 I/O Capacity | 200 Thread Concurrency | 0 Concurrency Tickets | 5000 Commit Concurrency | 0 Txn Isolation Level | Adaptive Flushing | ON Adaptive Checkpoint | Checkpoint Age | 4M InnoDB Queue | 0 queries inside InnoDB, 0 queries in queue Oldest Transaction | 0 Seconds History List Len | 115 Read Views | 0 Undo Log Entries | 0 transactions, 0 total undo, 0 max undo Pending I/O Reads | 0 buf pool reads, 0 normal AIO, 0 ibuf AIO, 0 preads Pending I/O Writes | 0 buf pool (0 LRU, 0 flush list, 0 page); 0 AIO, 0 sync, 0 log IO (0 log, 0 chkp); 0 pwrites Pending I/O Flushes | 0 buf pool, 0 log Transaction States | 3xnot started # MyISAM ##################################################### Key Cache | 16,0M Pct Used | 20% Unflushed | 0% # Security ################################################### Users | 2 users, 0 anon, 0 w/o pw, 2 old pw Old Passwords | # Encryption ################################################# No keyring plugins found # Binary Logging ############################################# Binlogs | 33 Zero-Sized | 0 Total Size | 63,7M binlog_format | ROW expire_logs_days | 0 sync_binlog | 1 server_id | 1 binlog_do_db | binlog_ignore_db | # Noteworthy Variables ####################################### Auto-Inc Incr/Offset | 1/1 default_storage_engine | InnoDB flush_time | 0 init_connect | init_file | sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION join_buffer_size | 256k sort_buffer_size | 5M read_buffer_size | 128k read_rnd_buffer_size | 256k bulk_insert_buffer | 0,00 max_heap_table_size | 16M tmp_table_size | 16M max_allowed_packet | 64M thread_stack | 1M log | log_error | /var/log/mysql/error.log log_warnings | log_slow_queries | log_queries_not_using_indexes | OFF log_slave_updates | ON # Configuration File ######################################### Config File | /etc/mysql/my.cnf # Memory management library ################################## jemalloc is not enabled in mysql config for process with id 571515 # The End ####################################################Создаем отдельный файл конфиг для настроек Mysql с прицелом на Битрикс
nano /etc/mysql/conf.d/bitrix.cnf# Настройки для сервера с 64Gb оперативной памяти [mysqld] innodb_flush_log_at_trx_commit = 2 sync_binlog = 0 innodb_buffer_pool_size = 36G # 70-80% от доступной памяти innodb_log_buffer_size = 32M # достаточно для большинства задач innodb_buffer_pool_instances = 4 # для разделения буфера innodb_flush_method = O_DIRECT transaction-isolation = READ-COMMITTED max_connections = 300 tmp_table_size = 64M # по умолчанию 16М max_heap_table_size = 64M # должно быть равно tmp_table_sizesystemctl restart mysql
Готово.