SQLinfo.ru - Все о MySQL Webew.ru: теория и практика веб-технологий

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 30.09.2021 11:21:07

globus13
Участник
Зарегистрирован: 05.09.2015
Сообщений: 9

Запрос долго отрабатывает

Здравствуйте. Используем InnoDB (MySQL 5.7.25-28)

bvat.cnf


[mysqld]
    query_cache_type = 1
    query_cache_size = 128M
    query_cache_limit = 16M
    innodb_buffer_pool_size = 24576M
    max_connections = 245
    table_open_cache = 18432
    thread_cache_size = 512
    max_heap_table_size = 128M
    tmp_table_size = 128M
    key_buffer_size = 256M
    join_buffer_size = 32M
    sort_buffer_size = 32M
    bulk_insert_buffer_size = 2M
    myisam_sort_buffer_size = 32M

 



/////////////////////////////////////////////////////////////////////////////////////////////
bx_replica.cnf


[mysqld]
 server-id = 1
 ## enable logging message
 log-error                       = /var/log/mysql/error.log
## enable slow requests logging
 slow-query-log-file             = /var/log/mysql/slow.log
 slow-query-log                  = 1
 long-query-time                 = 3
 log-slow-slave-statements
## define binlog options for master server
 binlog_cache_size = 128K
 expire_logs_days  = 5
 max_binlog_size   = 100M
 binlog-format     = mixed
 log-bin           = mysql-bin
## enable this options for security, but it decrease performance
# innodb_flush_log_at_trx_commit = 1
sync_binlog = 3000
 relay-log            = relay-bin
 relay-log-index      = relay-bin.index
 relay-log-info-fil
 



/////////////////////////////////////////////////////////////////////////////////////////
my.cnf

[client]
port = 3306
socket = /var/lib/mysqld/mysqld.sock
default-character-set = utf8

[mysqld_safe]
nice = 0

socket = /var/lib/mysqld/mysqld.sock

[mysqld]
#skip-grant-tables
# Basic mysql server configuration
user = user
port = 3306
basedir = /usr
datadir = /var/lib/mysql
socket = /var/lib/mysqld/mysqld.sock
skip-external-locking
default-storage-engine = innodb
pid-file = /var/run/mysqld/mysqld.pid
transaction-isolation = READ-COMMITTED
max_allowed_packet = 16M
myisam-recover-options = BACKUP
explicit_defaults_for_timestamp = 1

sql_mode = ""


expire_logs_days = 1
max_binlog_size = 100M

# Cache parameters
query_cache_size = 512M
table_open_cache = 4096
thread_cache_size = 32
key_buffer_size = 1024M
thread_stack = 512M
join_buffer_size = 512M
sort_buffer_size = 512M

# Parameters for temporary tables
tmpdir = /tmp
max_heap_table_size     = 32M
tmp_table_size = 32M

# InnoDB parameters
innodb_file_per_table
innodb_buffer_pool_size = 1024M
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 1024M
innodb_flush_method = O_DSYNC
innodb_strict_mode = OFF
# Database charset parameters
character-set-server = utf8
collation-server = utf8_unicode_ci
init-connect = "SET NAMES utf8 COLLATE utf8_unicode_ci"
skip-character-set-client-handshake
skip-name-resolve

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[mysql]

[isamchk]

key_buffer = 16M

# Include additional settings
!includedir /etc/mysql/conf.d/
 



/////////////////////////////////////////////////////////////////////////
dbconn.php


<?
ini_set("memory_limit", "4096M");
define("DBPersistent", false);
$DBType = "mysql";
$DBHost = "";
$DBLogin = "";
$DBPassword = "";
$DBName = "";
$DBDebug = true;
$DBDebugToFile = false;

define("DELAY_DB_CONNECT", true);
define("CACHED_b_file", 3600);
define("CACHED_b_file_bucket_size", 10);
define("CACHED_b_lang", 3600);
define("CACHED_b_option", 3600);
define("CACHED_b_lang_domain", 3600);
define("CACHED_b_site_template", 3600);
define("CACHED_b_event", 3600);
define("CACHED_b_agent", 3660);
define("CACHED_menu", 3600);
define("BX_COMP_MANAGED_CACHE", true);

define("BX_UTF", true);
define("BX_FILE_PERMISSIONS", 0644);
define("BX_DIR_PERMISSIONS", 0755);
umask(000);
@umask(~BX_DIR_PERMISSIONS);
define("BX_DISABLE_INDEX_PAGE", true);

// define("BX_CRONTAB_SUPPORT", true);
define("BC_IB_SETTINGS_ID", 15);
define("BC_IB_CITIES_ID", 17);
define("BC_IB_BRANDS_ID", 9);
define("BC_IB_OFFER_ID", 6);
define("BC_IB_PRODUCTS_ID", 5);
define("BC_IB_NEWS", 1);

define("RECAPTCHA_PUBLIC_KEY", '');
define("RECAPTCHA_SECRET_KEY", '2');

if(!(defined("CHK_EVENT") && CHK_EVENT===true))
   define("BX_CRONTAB_SUPPORT", true);
define( "LOG_FILENAME", $_SERVER["DOCUMENT_ROOT"]."/log.txt");

//define("CACHED_b_search_tags", false);


define("SBRF_STATUS_NOT_PAYED", 0);
define("SBRF_STATUS_PAYED", 1);
define("SBRF_STATUS_DECLINED", 2);

define("BX_USE_MYSQLI", true);
?>

 


///////////////////////////////////////////////////////

SHOW GLOBAL STATUS LIKE 'Qcache%';
Qcache_free_blocks    1684
Qcache_free_memory    5784888
Qcache_hits    16292595
Qcache_inserts    6864173
Qcache_lowmem_prunes    1723639
Qcache_not_cached    1249544
Qcache_queries_in_cache    13601
Qcache_total_blocks    40869
 



//////////////////////////////////////////////////

Суть проблемы в том, что на некоторых страницах одни и те же запросы занимают разное время, например запрос

SELECT DISTINCT BE.ID as ID,BE.IBLOCK_ID as IBLOCK_ID,BE.CODE as CODE,BE.XML_ID as XML_ID,BE.NAME as NAME,BE.ACTIVE as ACTIVE,IF(EXTRACT(Tov_SECOND
FROM
BE.ACTIVE_FROM)>0, DATE_FORMAT(BE.ACTIVE_FROM, '%d.%m.%Y %H:%i:%s'), DATE_FORMAT(BE.ACTIVE_FROM, '%d.%m.%Y')) as DATE_ACTIVE_FROM,IF(EXTRACT(Tov_SECOND
FROM
BE.ACTIVE_TO)>0, DATE_FORMAT(BE.ACTIVE_TO, '%d.%m.%Y %H:%i:%s'), DATE_FORMAT(BE.ACTIVE_TO, '%d.%m.%Y')) as DATE_ACTIVE_TO,BE.SORT as SORT,BE.PREVIEW_TEXT as PREVIEW_TEXT,BE.PREVIEW_TEXT_TYPE as PREVIEW_TEXT_TYPE,BE.DETAIL_TEXT as DETAIL_TEXT,BE.DETAIL_TEXT_TYPE as DETAIL_TEXT_TYPE,DATE_FORMAT(BE.DATE_CREATE, '%d.%m.%Y %H:%i:%s') as DATE_CREATE,BE.CREATED_BY as CREATED_BY,DATE_FORMAT(BE.TIMESTAMP_X, '%d.%m.%Y %H:%i:%s') as TIMESTAMP_X,BE.MODIFIED_BY as MODIFIED_BY,BE.TAGS as TAGS,BE.IBLOCK_SECTION_ID as IBLOCK_SECTION_ID,B.DETAIL_PAGE_URL as DETAIL_PAGE_URL,BE.DETAIL_PICTURE as DETAIL_PICTURE,BE.PREVIEW_PICTURE as PREVIEW_PICTURE,L.DIR as LANG_DIR, FPEN0.VALUE as PROPERTY_A_STATUSTOVARASPRAVOCHNIKNOMENKLATURAOBSHCHIE_VALUE, FPEN0.ID as PROPERTY_A_STATUSTOVARASPRAVOCHNIKNOMENKLATURAOBSHCHIE_ENUM_ID, FPV0.ID as PROPERTY_A_STATUSTOVARASPRAVOCHNIKNOMENKLATURAOBSHCHIE_VALUE_ID,BE.XML_ID as EXTERNAL_ID,B.IBLOCK_TYPE_ID as IBLOCK_TYPE_ID,B.CODE as IBLOCK_CODE,B.XML_ID as IBLOCK_EXTERNAL_ID,B.LID as LID , CAT_P2.ID as CATALOG_PRICE_ID_2, CAT_P2.CATALOG_GROUP_ID as CATALOG_GROUP_ID_2, CAT_P2.PRICE as CATALOG_PRICE_2, CAT_P2.CURRENCY as CATALOG_CURRENCY_2, CAT_P2.QUANTITY_FROM as CATALOG_QUANTITY_FROM_2, CAT_P2.QUANTITY_TO as CATALOG_QUANTITY_TO_2, 'Цена' as CATALOG_GROUP_NAME_2, 'Y' as CATALOG_CAN_ACCESS_2, 'Y' as CATALOG_CAN_BUY_2, CAT_P2.EXTRA_ID as CATALOG_EXTRA_ID_2, CAT_PR.QUANTITY as CATALOG_QUANTITY, CAT_PR.QUANTITY_RESERVED as CATALOG_QUANTITY_RESERVED, IF (CAT_PR.QUANTITY_TRACE = 'D', 'Y', CAT_PR.QUANTITY_TRACE) as CATALOG_QUANTITY_TRACE, CAT_PR.QUANTITY_TRACE as CATALOG_QUANTITY_TRACE_ORIG, IF (CAT_PR.CAN_BUY_ZERO = 'D', 'Y', CAT_PR.CAN_BUY_ZERO) as CATALOG_CAN_BUY_ZERO, CAT_PR.CAN_BUY_ZERO as CATALOG_CAN_BUY_ZERO_ORIG, IF (CAT_PR.NEGATIVE_AMOUNT_TRACE = 'D', 'Y', CAT_PR.NEGATIVE_AMOUNT_TRACE) as CATALOG_NEGATIVE_AMOUNT_TRACE, CAT_PR.NEGATIVE_AMOUNT_TRACE as CATALOG_NEGATIVE_AMOUNT_ORIG, IF (CAT_PR.SUBSCRIBE = 'D', 'Y', CAT_PR.SUBSCRIBE) as CATALOG_SUBSCRIBE, CAT_PR.SUBSCRIBE as CATALOG_SUBSCRIBE_ORIG, CAT_PR.AVAILABLE as CATALOG_AVAILABLE, CAT_PR.WEIGHT as CATALOG_WEIGHT, CAT_PR.WIDTH as CATALOG_WIDTH, CAT_PR.LENGTH as CATALOG_LENGTH, CAT_PR.HEIGHT as CATALOG_HEIGHT, CAT_PR.MEASURE as CATALOG_MEASURE, CAT_VAT.RATE as CATALOG_VAT, CAT_PR.VAT_ID as CATALOG_VAT_ID, CAT_PR.VAT_INCLUDED as CATALOG_VAT_INCLUDED, CAT_PR.PRICE_TYPE as CATALOG_PRICE_TYPE, CAT_PR.RECUR_SCHEME_TYPE as CATALOG_RECUR_SCHEME_TYPE, CAT_PR.RECUR_SCHEME_LENGTH as CATALOG_RECUR_SCHEME_LENGTH, CAT_PR.TRIAL_PRICE_ID as CATALOG_TRIAL_PRICE_ID, CAT_PR.WITHOUT_ORDER as CATALOG_WITHOUT_ORDER, CAT_PR.SELECT_BEST_PRICE as CATALOG_SELECT_BEST_PRICE, CAT_PR.PURCHASING_PRICE as CATALOG_PURCHASING_PRICE, CAT_PR.PURCHASING_CURRENCY as CATALOG_PURCHASING_CURRENCY, CAT_PR.TYPE as CATALOG_TYPE, CAT_PR.BUNDLE as CATALOG_BUNDLE
FROM
b_iblock B
INNER JOIN b_lang L ON B.LID=L.LID
INNER JOIN b_iblock_element BE ON BE.IBLOCK_ID = B.ID
LEFT JOIN b_iblock_property FP0 ON FP0.IBLOCK_ID = B.ID AND FP0.CODE='A_STATUSTOVARASPRAVOCHNIKNOMENKLATURAOBSHCHIE'
INNER JOIN b_iblock_property FP1 ON FP1.IBLOCK_ID = B.ID AND FP1.CODE='MINIMUM_PRICE'
LEFT JOIN b_iblock_property FP2 ON FP2.IBLOCK_ID = B.ID AND FP2.CODE='SHOPS'
LEFT JOIN b_iblock_element_property FPV0 ON FPV0.IBLOCK_PROPERTY_ID = FP0.ID AND FPV0.IBLOCK_ELEMENT_ID = BE.ID
INNER JOIN b_iblock_element_property FPV1 ON FPV1.IBLOCK_PROPERTY_ID = FP1.ID AND FPV1.IBLOCK_ELEMENT_ID = BE.ID
LEFT JOIN b_iblock_element_property FPV2 ON FPV2.IBLOCK_PROPERTY_ID = FP2.ID AND FPV2.IBLOCK_ELEMENT_ID = BE.ID
LEFT JOIN b_iblock_property_enum FPEN0 ON FPEN0.PROPERTY_ID = FPV0.IBLOCK_PROPERTY_ID AND FPV0.VALUE_ENUM = FPEN0.ID
INNER JOIN ( SELECT DISTINCT BSE.IBLOCK_ELEMENT_ID
FROM
b_iblock_section_element BSE
INNER JOIN b_iblock_section BSubS ON BSE.IBLOCK_SECTION_ID = BSubS.ID
INNER JOIN b_iblock_section BS ON (BSubS.IBLOCK_ID=BS.IBLOCK_ID AND BSubS.LEFT_MARGIN>=BS.LEFT_MARGIN AND BSubS.RIGHT_MARGIN<=BS.RIGHT_MARGIN)
WHERE
((BS.ID IN (2177))) ) BES ON BES.IBLOCK_ELEMENT_ID = BE.ID
left join b_catalog_price CAT_P2 on (CAT_P2.PRODUCT_ID = BE.ID AND CAT_P2.CATALOG_GROUP_ID = 2) and ((CAT_P2.QUANTITY_FROM <= 1 OR CAT_P2.QUANTITY_FROM IS NULL) AND (CAT_P2.QUANTITY_TO >= 1 OR CAT_P2.QUANTITY_TO IS NULL))
left join b_catalog_product CAT_PR on (CAT_PR.ID = BE.ID)
left join b_catalog_iblock CAT_IB on ((CAT_PR.VAT_ID IS NULL OR CAT_PR.VAT_ID = 0) AND CAT_IB.IBLOCK_ID = BE.IBLOCK_ID)
left join b_catalog_vat CAT_VAT on (CAT_VAT.ID = IF((CAT_PR.VAT_ID IS NULL OR CAT_PR.VAT_ID = 0), CAT_IB.VAT_ID, CAT_PR.VAT_ID))
WHERE
1=1 AND ( (((BE.DETAIL_PICTURE IS NOT NULL))) AND ((((BE.IBLOCK_ID = '5')))) AND (EXISTS ( SELECT IBLOCK_ID
FROM
b_iblock_site
WHERE
IBLOCK_ID = B.ID AND (((SITE_ID='s1'))) )) AND ((((B.ACTIVE='Y')))) AND (((BE.ACTIVE_TO >= now() OR BE.ACTIVE_TO IS NULL) AND (BE.ACTIVE_FROM <= now() OR BE.ACTIVE_FROM IS NULL))) AND ((((BE.ACTIVE='Y')))) AND ( 1=1 ) AND ((( FPEN0.VALUE IS NULL OR NOT (FPEN0.VALUE LIKE 'Не выгружать')))) AND (((FPV1.VALUE_NUM IS NOT NULL))) AND (((NOT(FPV2.VALUE IS NULL OR length(FPV2.VALUE)<=0)))) AND ((((FPV1.VALUE_NUM > '0')))) ) AND (((BE.WF_STATUS_ID=1 AND BE.WF_PARENT_ELEMENT_ID IS NULL)))
ORDER BY
FPEN0.VALUE asc ,BE.SORT asc ,BE.NAME asc
LIMIT 0, 24
 


на некоторых страницах отрабатывает за 0,003 сек, а на некоторых за 2 сек, а то и за 5 сек. И как результат где-то время загрузки страницы 0,5 сек, а где-то 6 сек. Вопрос простой, можно ли оптимизировать и если да, то как mysql (не на уровне запросов, а на уровне сервиса mysql), что бы сделать запросы быстрее? Да и собственно почему такая разница? Заранее спасибо за ответ.

Неактивен

 

#2 04.10.2021 09:39:47

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5838

Re: Запрос долго отрабатывает

Разница из-за того, что в одном случае результат берется из кэша, а в другом выполняется? Планы выполнения отличаются у медленного и быстрого случая?

Неактивен

 

#3 23.07.2024 14:58:07

allbases
Участник
Откуда: Екатеринбург
Зарегистрирован: 23.07.2024
Сообщений: 1

Re: Запрос долго отрабатывает

Спроси у ChatGPT как решить эту проблему?

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson