Показано с 1 по 3 из 3

Тема: Mysql-server долго обрабатывает сложные Join запросы.

  1. #1
    Member
    Регистрация
    19.04.2017
    Сообщений
    36

    По умолчанию Mysql-server долго обрабатывает сложные Join запросы.

    Возникла проблема при переносе сайта с mysql сервера 5.0 на mysql сервер под управлением ISPmanager (mariadb 5.5).
    После переноса запрос, например такой:
    Код:
    SELECT COUNT(*) AS expression
    FROM 
    (SELECT 1 AS expression
    FROM 
    node node
    INNER JOIN users users_node ON node.uid = users_node.uid
    LEFT JOIN field_data_field_poly users_node__field_data_field_poly ON users_node.uid = users_node__field_data_field_poly.field_poly_uid
    LEFT JOIN node field_poly_users ON users_node__field_data_field_poly.entity_id = field_poly_users.nid
    LEFT JOIN field_data_field_poly_city users_node__field_data_field_poly_city ON users_node.uid = users_node__field_data_field_poly_city.entity_id AND (users_node__field_data_field_poly_city.entity_type = 'user' AND users_node__field_data_field_poly_city.deleted = '0')
    LEFT JOIN taxonomy_term_data taxonomy_term_data_field_data_field_poly_city ON users_node__field_data_field_poly_city.field_poly_city_tid = taxonomy_term_data_field_data_field_poly_city.tid
    INNER JOIN field_data_field_paperformat field_data_field_paperformat ON node.nid = field_data_field_paperformat.entity_id AND (field_data_field_paperformat.entity_type = 'node' AND field_data_field_paperformat.deleted = '0')
    INNER JOIN field_data_field_color field_data_field_color ON node.nid = field_data_field_color.entity_id AND (field_data_field_color.entity_type = 'node' AND field_data_field_color.deleted = '0')
    INNER JOIN field_data_field_sides field_data_field_sides ON node.nid = field_data_field_sides.entity_id AND (field_data_field_sides.entity_type = 'node' AND field_data_field_sides.deleted = '0')
    INNER JOIN field_data_field_pdoc_paper field_data_field_pdoc_paper ON node.nid = field_data_field_pdoc_paper.entity_id AND (field_data_field_pdoc_paper.entity_type = 'node' AND field_data_field_pdoc_paper.deleted = '0')
    INNER JOIN field_data_field_poly_city users_node__field_data_field_poly_city2 ON users_node.uid = users_node__field_data_field_poly_city2.entity_id AND (users_node__field_data_field_poly_city2.entity_type = 'user' AND users_node__field_data_field_poly_city2.deleted = '0')
    LEFT JOIN field_data_field_ban_ext_price1 field_data_field_ban_ext_price1 ON node.nid = field_data_field_ban_ext_price1.entity_id AND (field_data_field_ban_ext_price1.entity_type = 'node' AND field_data_field_ban_ext_price1.deleted = '0')
    LEFT JOIN field_data_field_ban_ext_price2 field_data_field_ban_ext_price2 ON node.nid = field_data_field_ban_ext_price2.entity_id AND (field_data_field_ban_ext_price2.entity_type = 'node' AND field_data_field_ban_ext_price2.deleted = '0')
    LEFT JOIN field_data_field_ban_ext_price3 field_data_field_ban_ext_price3 ON node.nid = field_data_field_ban_ext_price3.entity_id AND (field_data_field_ban_ext_price3.entity_type = 'node' AND field_data_field_ban_ext_price3.deleted = '0')
    LEFT JOIN field_data_field_ban_ext_price4 field_data_field_ban_ext_price4 ON node.nid = field_data_field_ban_ext_price4.entity_id AND (field_data_field_ban_ext_price4.entity_type = 'node' AND field_data_field_ban_ext_price4.deleted = '0')
    LEFT JOIN field_data_field_ban_ext_price5 field_data_field_ban_ext_price5 ON node.nid = field_data_field_ban_ext_price5.entity_id AND (field_data_field_ban_ext_price5.entity_type = 'node' AND field_data_field_ban_ext_price5.deleted = '0')
    LEFT JOIN field_data_field_ban_ext_price6 field_data_field_ban_ext_price6 ON node.nid = field_data_field_ban_ext_price6.entity_id AND (field_data_field_ban_ext_price6.entity_type = 'node' AND field_data_field_ban_ext_price6.deleted = '0')
    LEFT JOIN field_data_field_ban_int_price1 field_data_field_ban_int_price1 ON node.nid = field_data_field_ban_int_price1.entity_id AND (field_data_field_ban_int_price1.entity_type = 'node' AND field_data_field_ban_int_price1.deleted = '0')
    LEFT JOIN taxonomy_vocabulary taxonomy_term_data_field_data_field_poly_city__taxonomy_vocabulary ON taxonomy_term_data_field_data_field_poly_city.vid = taxonomy_term_data_field_data_field_poly_city__taxonomy_vocabulary.vid
    LEFT JOIN field_data_field_prior_a4a3 field_poly_users__field_data_field_prior_a4a3 ON field_poly_users.nid = field_poly_users__field_data_field_prior_a4a3.entity_id AND (field_poly_users__field_data_field_prior_a4a3.entity_type = 'node' AND field_poly_users__field_data_field_prior_a4a3.deleted = '0')
    LEFT JOIN field_data_field_rate field_poly_users__field_data_field_rate ON field_poly_users.nid = field_poly_users__field_data_field_rate.entity_id AND (field_poly_users__field_data_field_rate.entity_type = 'node' AND field_poly_users__field_data_field_rate.deleted = '0')
    WHERE (( (node.status = '1') AND (node.type IN  ('price_printdoc1')) AND (field_data_field_paperformat.field_paperformat_value = 'А4') AND (field_data_field_color.field_color_value = 'Черно-белая') AND (field_data_field_sides.field_sides_value = 'c 1-ой стороны') AND (field_data_field_pdoc_paper.field_pdoc_paper_value = '0') AND (users_node__field_data_field_poly_city2.field_poly_city_tid = '1') AND (field_data_field_ban_ext_price1.field_ban_ext_price1_value >= '1') AND (field_data_field_ban_ext_price2.field_ban_ext_price2_value >= '0') AND (field_data_field_ban_ext_price3.field_ban_ext_price3_value >= '0') AND (field_data_field_ban_ext_price4.field_ban_ext_price4_value >= '0') AND (field_data_field_ban_ext_price5.field_ban_ext_price5_value >= '0') AND (field_data_field_ban_ext_price6.field_ban_ext_price6_value >= '0') AND (field_data_field_ban_int_price1.field_ban_int_price1_value >= '0') ))) subquery
    отрабатывает за 5-6 минут, когда на прежнем mysql-сервере отрабатывал за милисекунды. Подумал сразу что дело в конфигах, но когда привел конфиг mariadb в тот же вид что и старый mysql-сервер, то ничего не поменялось.
    Конфиг на данный момент такой:
    Код:
    [mysqld]
    local-infile=0
    #innodb_file_per_table = 1
    pid-file = /var/run/mysqld/mysqld.pid
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0
    bind-address = ::
    max_allowed_packet=64M
    # Settings user and group are ignored when systemd is used.
    # If you need to run mysqld under a different user or group,
    # customize your systemd unit file for mariadb according to the
    # instructions in http://fedoraproject.org/wiki/Systemd
    
    query_cache_size                        = 128M
    query_cache_limit                       = 32M
    query_cache_type = 0
    table_open_cache                        = 9126
    key_buffer_size                         = 1610612736
    
    max_allowed_packet                      = 52428800
    max_connections                         = 1024
    
    innodb_flush_method = O_DIRECT
    innodb_buffer_pool_size = 5G
    innodb_log_files_in_group = 2
    innodb_flush_log_at_trx_commit = 2
    innodb_large_prefix                     = true
    innodb_strict_mode                      = 0
    innodb_open_files                       = 1024
    innodb_flush_log_at_trx_commit          = 2
    innodb_buffer_pool_size                 = 5368709120
    innodb_log_buffer_size                  = 8M
    innodb_file_format=barracuda
    innodb_file_per_table=true
    
    long_query_time                         = 10
    query_cache_type                        = OFF
    
    join_buffer_size = 134217728
    max_join_size = 2000000
    
    max_heap_table_size = 32M
    tmp_table_size = 32M
    table_definition_cache = 4096
    thread_cache_size = 500
    thread_concurrency = 16
    open_files_limit = 65535
    
    max_user_connections = 60
    
    [mysqld_safe]
    log-error=/var/log/mariadb/mariadb.log
    pid-file=/var/run/mariadb/mariadb.pid
    #
    # include all files from the config directory
    #
    !includedir /etc/my.cnf.d
    На mysql-ноде 24 Гб ОЗУ.

    Есть ли какие настройки в конфигах самой панели которые ограничивают работу таких запросов?

  2. #2
    Senior Member Аватар для VA
    Регистрация
    27.01.2007
    Сообщений
    1,346

    По умолчанию

    ISPmanager тут вообще никаким боком.

    Посмотрите в сторону optimizer_search_depth

  3. #3
    Member
    Регистрация
    19.04.2017
    Сообщений
    36

    По умолчанию

    Цитата Сообщение от VA Посмотреть сообщение
    ISPmanager тут вообще никаким боком.

    Посмотрите в сторону optimizer_search_depth
    Огромное спасибо за наводку! Нашел проблему.

Ваши права

  • Вы не можете создавать новые темы
  • Вы не можете отвечать в темах
  • Вы не можете прикреплять вложения
  • Вы не можете редактировать свои сообщения
  •