|
|
Задавайте вопросы, мы ответим
Вы не зашли.
Приветствую всех на этом форуме!
Сразу к делу:
Проблема - при выборке из таблицы каталога с примерно 60000 наименований, запрос "пробегает" по таблице каталога несколько раз. Скорость запросов варьируется, как правило ищатся артикулы от 6 до 25 буквенно-символьных комбинаций. Средняя величина ожидания пользователя 8 -10 секунд на один запрос в поисковую систему. Каталог не имеет изображений, вывод данных пользователю представляется в виде таблицы, где фиксированное кол-во колонок - 7, и найденные соответствия поисковому запросу построчно.
Очень надо найти узкое место, для этого прошу вашей оценки. Основной вопрос - есть ли возможность перенастроить буферы и конфигурацию MySQL в целом,
либо необходимо расширить возможности железа, либо попытаться переписать запросы (позволит ли такое Битрикс?) чтобы сократить время оиждания?
Статические страницы отрабатывают в среднем за 0,02 сек. Но как мне объяснили разработчики, в БД в таблице каталога нет ни индексов, и она не кэшируется.
Очень хочется максимально сократить время ожидания (до <1 секунды), я как админ, могу лишь предложить другое железо, память проц все дела.
В MySQL указано значение max_connections равным 151, нужно ли столько при посещаемости 3-5 одновременных пользователей на сайте, подозреваю это значение нежно снизить?
К великому сожалению нет возможности проконсультироваться с грамотными DBA, поэтому пишу сюда. Спасибо за ваши ответы!
Хостинг VPS - 4ГБ RAM
Apache, MySQL 5.1.73 (InnoDB)
CMS - 1С-Битрикс: Управление сайтом 14.0.10 - генерирует запросы SQL сама.
Файл my.cnf - который применен сейчас.
[mysqld]
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
transaction-isolation = READ-COMMITTED
# Cache parameters
query_cache_size = 64M
table_open_cache = 4096
thread_cache_size = 32
key_buffer_size = 64M
sort_buffer_size = 2M
read_buffer_size = 8M
thread_stack = 256K
join_buffer_size = 2M
# Parameters for temporary tables
tmpdir = /tmp
max_heap_table_size = 32M
tmp_table_size = 32M
# InnoDB parameters
innodb_buffer_pool_size = 768M
innodb_additional_mem_pool_size=128M
innodb_file_io_threads=8
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 192M
innodb_flush_method = O_DIRECT
innodb_log_buffer_size=16M
innodb_thread_concurrency=8
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
innodb_buffer_pool_size = 780M
innodb_additional_mem_pool_size=32M
innodb_file_io_threads=8
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 64M
innodb_flush_method = O_DIRECT
innodb_log_buffer_size=16M
innodb_thread_concurrency=8
Пример запроса, который отрабатывает за 19 секунд (просьба сильно не пинать):
Отредактированно Punkt (04.03.2014 11:52:33)
Неактивен
Покажите определения таблиц входящих в этот запрос:
show create table b_iblock;
show create table b_lang;
...
и план запроса, т.е.
explain extended ваш запрос \G
show warnings \G
Запрос содержит независимый IN подзапрос, но в вашей версии он будет выполняться для каждой строки внешнего запроса. Попробуйте выполнить запрос на более новой версии (MySQL 5.6 или MariaDB 5.3 и выше)
Неактивен
vasya написал:
Покажите определения таблиц входящих в этот запрос:
show create table b_iblock;
show create table b_lang;
...
Из всего FROM где есть INNER JOIN и LEFT JOIN?
vasya написал:
и план запроса, т.е.
explain extended ваш запрос \G
show warnings \G
Это две разные комманды, свой запрос надо вставить только в explain или в show тоже?
Неактивен
CREATE TABLE `b_iblock` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `TIMESTAMP_X` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `IBLOCK_TYPE_ID` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `LID` char(2) COLLATE utf8_unicode_ci NOT NULL, `CODE` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL, `NAME` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `ACTIVE` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Y', `SORT` int(11) NOT NULL DEFAULT '500', `LIST_PAGE_URL` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `DETAIL_PAGE_URL` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `SECTION_PAGE_URL` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `PICTURE` int(18) DEFAULT NULL, `DESCRIPTION` text COLLATE utf8_unicode_ci, `DESCRIPTION_TYPE` char(4) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'text', `RSS_TTL` int(11) NOT NULL DEFAULT '24', `RSS_ACTIVE` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Y', `RSS_FILE_ACTIVE` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N', `RSS_FILE_LIMIT` int(11) DEFAULT NULL, `RSS_FILE_DAYS` int(11) DEFAULT NULL, `RSS_YANDEX_ACTIVE` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N', `XML_ID` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `TMP_ID` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL, `INDEX_ELEMENT` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Y', `INDEX_SECTION` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N', `WORKFLOW` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Y', `BIZPROC` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N', `SECTION_CHOOSER` char(1) COLLATE utf8_unicode_ci DEFAULT NULL, `LIST_MODE` char(1) COLLATE utf8_unicode_ci DEFAULT NULL, `RIGHTS_MODE` char(1) COLLATE utf8_unicode_ci DEFAULT NULL, `VERSION` int(11) NOT NULL DEFAULT '1', `LAST_CONV_ELEMENT` int(11) NOT NULL DEFAULT '0', `SOCNET_GROUP_ID` int(18) DEFAULT NULL, `EDIT_FILE_BEFORE` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `EDIT_FILE_AFTER` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `SECTIONS_NAME` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, `SECTION_NAME` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, `ELEMENTS_NAME` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, `ELEMENT_NAME` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, `SECTION_PROPERTY` char(1) COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (`ID`), KEY `ix_iblock` (`IBLOCK_TYPE_ID`,`LID`,`ACTIVE`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `b_lang` ( `LID` char(2) COLLATE utf8_unicode_ci NOT NULL, `SORT` int(18) NOT NULL DEFAULT '100', `DEF` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N', `ACTIVE` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Y', `NAME` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `DIR` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `FORMAT_DATE` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL, `FORMAT_DATETIME` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL, `WEEK_START` int(11) DEFAULT NULL, `CHARSET` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `LANGUAGE_ID` char(2) COLLATE utf8_unicode_ci NOT NULL, `DOC_ROOT` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `DOMAIN_LIMITED` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N', `SERVER_NAME` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `SITE_NAME` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `EMAIL` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `FORMAT_NAME` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `CULTURE_ID` int(11) DEFAULT NULL, PRIMARY KEY (`LID`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
Неактивен
phpmyadmin
Show warnings показал следующее:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT BE.ID as ID, BE.IBLOCK_ID as IBLOCK_ID, BE.CODE as CODE, ' at line 2
Неактивен
phpmyadmin
Show warnings показал следующее:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '), CAT_IB.VAT_ID, CAT_PR.VAT_ID ) WHERE 1 = 1 ' at line 166
Неактивен
Punkt написал:
vasya написал:
Покажите определения таблиц входящих в этот запрос:
show create table b_iblock;
show create table b_lang;
...Из всего FROM где есть INNER JOIN и LEFT JOIN?
Да, для всех таблиц.
Punkt написал:
vasya написал:
и план запроса, т.е.
explain extended ваш запрос \G
show warnings \GЭто две разные комманды, свой запрос надо вставить только в explain или в show тоже?
Только в explain.
Неактивен
CREATE TABLE `b_iblock_element` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `TIMESTAMP_X` datetime DEFAULT NULL, `MODIFIED_BY` int(18) DEFAULT NULL, `DATE_CREATE` datetime DEFAULT NULL, `CREATED_BY` int(18) DEFAULT NULL, `IBLOCK_ID` int(11) NOT NULL DEFAULT '0', `IBLOCK_SECTION_ID` int(11) DEFAULT NULL, `ACTIVE` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Y', `ACTIVE_FROM` datetime DEFAULT NULL, `ACTIVE_TO` datetime DEFAULT NULL, `SORT` int(11) NOT NULL DEFAULT '500', `NAME` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `PREVIEW_PICTURE` int(18) DEFAULT NULL, `PREVIEW_TEXT` text COLLATE utf8_unicode_ci, `PREVIEW_TEXT_TYPE` varchar(4) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'text', `DETAIL_PICTURE` int(18) DEFAULT NULL, `DETAIL_TEXT` longtext COLLATE utf8_unicode_ci, `DETAIL_TEXT_TYPE` varchar(4) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'text', `SEARCHABLE_CONTENT` text COLLATE utf8_unicode_ci, `WF_STATUS_ID` int(18) DEFAULT '1', `WF_PARENT_ELEMENT_ID` int(11) DEFAULT NULL, `WF_NEW` char(1) COLLATE utf8_unicode_ci DEFAULT NULL, `WF_LOCKED_BY` int(18) DEFAULT NULL, `WF_DATE_LOCK` datetime DEFAULT NULL, `WF_COMMENTS` text COLLATE utf8_unicode_ci, `IN_SECTIONS` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N', `XML_ID` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `CODE` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `TAGS` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `TMP_ID` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL, `WF_LAST_HISTORY_ID` int(11) DEFAULT NULL, `SHOW_COUNTER` int(18) DEFAULT NULL, `SHOW_COUNTER_START` datetime DEFAULT NULL, PRIMARY KEY (`ID`), KEY `ix_iblock_element_1` (`IBLOCK_ID`,`IBLOCK_SECTION_ID`), KEY `ix_iblock_element_4` (`IBLOCK_ID`,`XML_ID`,`WF_PARENT_ELEMENT_ID`), KEY `ix_iblock_element_3` (`WF_PARENT_ELEMENT_ID`), KEY `ix_iblock_element_code` (`IBLOCK_ID`,`CODE`)) ENGINE=InnoDB AUTO_INCREMENT=118518 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `b_iblock_property` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `TIMESTAMP_X` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `IBLOCK_ID` int(11) NOT NULL, `NAME` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `ACTIVE` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Y', `SORT` int(11) NOT NULL DEFAULT '500', `CODE` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL, `DEFAULT_VALUE` text COLLATE utf8_unicode_ci, `PROPERTY_TYPE` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'S', `ROW_COUNT` int(11) NOT NULL DEFAULT '1', `COL_COUNT` int(11) NOT NULL DEFAULT '30', `LIST_TYPE` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'L', `MULTIPLE` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N', `XML_ID` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, `FILE_TYPE` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL, `MULTIPLE_CNT` int(11) DEFAULT NULL, `TMP_ID` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL, `LINK_IBLOCK_ID` int(18) DEFAULT NULL, `WITH_DESCRIPTION` char(1) COLLATE utf8_unicode_ci DEFAULT NULL, `SEARCHABLE` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N', `FILTRABLE` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N', `IS_REQUIRED` char(1) COLLATE utf8_unicode_ci DEFAULT NULL, `VERSION` int(11) NOT NULL DEFAULT '1', `USER_TYPE` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `USER_TYPE_SETTINGS` text COLLATE utf8_unicode_ci, `HINT` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (`ID`), KEY `ix_iblock_property_1` (`IBLOCK_ID`), KEY `ix_iblock_property_3` (`LINK_IBLOCK_ID`), KEY `ix_iblock_property_2` (`CODE`)) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `b_iblock_element_property` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `IBLOCK_PROPERTY_ID` int(11) NOT NULL, `IBLOCK_ELEMENT_ID` int(11) NOT NULL, `VALUE` text COLLATE utf8_unicode_ci NOT NULL, `VALUE_TYPE` char(4) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'text', `VALUE_ENUM` int(11) DEFAULT NULL, `VALUE_NUM` decimal(18,4) DEFAULT NULL, `DESCRIPTION` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (`ID`), KEY `ix_iblock_element_property_1` (`IBLOCK_ELEMENT_ID`,`IBLOCK_PROPERTY_ID`), KEY `ix_iblock_element_property_2` (`IBLOCK_PROPERTY_ID`), KEY `ix_iblock_element_prop_enum` (`VALUE_ENUM`,`IBLOCK_PROPERTY_ID`), KEY `ix_iblock_element_prop_num` (`VALUE_NUM`,`IBLOCK_PROPERTY_ID`)) ENGINE=InnoDB AUTO_INCREMENT=8659894 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `b_catalog_price` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `PRODUCT_ID` int(11) NOT NULL, `EXTRA_ID` int(11) DEFAULT NULL, `CATALOG_GROUP_ID` int(11) NOT NULL, `PRICE` decimal(18,2) NOT NULL, `CURRENCY` char(3) COLLATE utf8_unicode_ci NOT NULL, `TIMESTAMP_X` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `QUANTITY_FROM` int(11) DEFAULT NULL, `QUANTITY_TO` int(11) DEFAULT NULL, `TMP_ID` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (`ID`), KEY `IXS_CAT_PRICE_PID` (`PRODUCT_ID`,`CATALOG_GROUP_ID`), KEY `IXS_CAT_PRICE_GID` (`CATALOG_GROUP_ID`)) ENGINE=InnoDB AUTO_INCREMENT=1204531 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `b_catalog_product` ( `ID` int(11) NOT NULL, `QUANTITY` double NOT NULL, `QUANTITY_TRACE` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N', `WEIGHT` double NOT NULL DEFAULT '0', `TIMESTAMP_X` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `PRICE_TYPE` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'S', `RECUR_SCHEME_LENGTH` int(11) DEFAULT NULL, `RECUR_SCHEME_TYPE` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'D', `TRIAL_PRICE_ID` int(11) DEFAULT NULL, `WITHOUT_ORDER` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N', `SELECT_BEST_PRICE` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Y', `VAT_ID` int(11) DEFAULT '0', `VAT_INCLUDED` char(1) COLLATE utf8_unicode_ci DEFAULT 'Y', `TMP_ID` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL, `CAN_BUY_ZERO` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N', `NEGATIVE_AMOUNT_TRACE` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'D', `BARCODE_MULTI` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N', `PURCHASING_PRICE` decimal(18,2) DEFAULT NULL, `PURCHASING_CURRENCY` char(3) COLLATE utf8_unicode_ci DEFAULT NULL, `QUANTITY_RESERVED` double DEFAULT '0', `MEASURE` int(11) DEFAULT NULL, `WIDTH` double DEFAULT NULL, `LENGTH` double DEFAULT NULL, `HEIGHT` double DEFAULT NULL, `SUBSCRIBE` char(1) COLLATE utf8_unicode_ci DEFAULT NULL, `TYPE` int(11) DEFAULT NULL, PRIMARY KEY (`ID`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `b_catalog_iblock` ( `IBLOCK_ID` int(11) NOT NULL, `YANDEX_EXPORT` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N', `SUBSCRIPTION` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N', `VAT_ID` int(11) DEFAULT '0', `PRODUCT_IBLOCK_ID` int(11) NOT NULL DEFAULT '0', `SKU_PROPERTY_ID` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`IBLOCK_ID`), KEY `IXS_CAT_IB_PRODUCT` (`PRODUCT_IBLOCK_ID`), KEY `IXS_CAT_IB_SKU_PROP` (`SKU_PROPERTY_ID`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `b_catalog_vat` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `TIMESTAMP_X` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `ACTIVE` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Y', `C_SORT` int(18) NOT NULL DEFAULT '100', `NAME` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `RATE` decimal(18,2) NOT NULL DEFAULT '0.00', PRIMARY KEY (`ID`), KEY `IX_CAT_VAT_ACTIVE` (`ACTIVE`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
Неактивен
А план выполнения?
Неактивен
vasya написал:
А план выполнения?
не запускается, ругается на вот этот кусок
Строки 167-173
Отредактированно Punkt (04.03.2014 19:26:42)
Неактивен
А откуда вы сам запрос скопировали? Он синтаксически неверен, там неправильно указаны скобки.
Неактивен
vasya написал:
А откуда вы сам запрос скопировали? Он синтаксически неверен, там неправильно указаны скобки.
Мог ошибиться при форматировании. Запрос копировал из Битрикса, там где отражены медленные запросы к базе.
Перепроверю и отпишусь.
Неактивен
