Хранимая процедура MySQL, вызывающая проблемы?
Править:
Я сузился, мои mysql ожидают тайм-аут вниз к этой строке:
IF @resultsFound > 0 THEN
INSERT INTO product_search_query (QueryText, CategoryId) VALUES (keywords, topLevelCategoryId);
END IF;
Какая-либо идея, почему это вызвало бы проблему? Я не могу разработать его!
Привет парни, я записал сохраненный proc для поиска продуктов в определенных категориях, из-за определенных ограничений, с которыми я столкнулся, я не мог сделать то, что я хотел (ограничение, но все еще возвращая общее количество найденных строк, с сортировкой, и т.д.)
Это означало, разделяет строку категории Ids, от 1,2,3 в к временной таблице, затем создает запрос полнотекстового поиска на основе сортировки опций и пределов, выполняет строку запроса и затем выбирает общее количество результатов.
Теперь, я знаю, что я не гуру MySQL, очень отнюдь нет, у меня есть он работа, но я продолжаю получать время outs с поисками продукта и т.д. таким образом, я думаю, что это может вызывать некоторую проблему?
У кого-либо есть какие-либо идеи, как я могу убрать это или даже сделать это намного лучшим способом, о котором я, вероятно, не знаю?
Спасибо..
DELIMITER $$
DROP PROCEDURE IF EXISTS `product_search` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `product_search`(keywords text, categories text, topLevelCategoryId int, sortOrder int, startOffset int, itemsToReturn int)
BEGIN
declare foundPos tinyint unsigned;
declare tmpTxt text;
declare delimLen tinyint unsigned;
declare element text;
declare resultingNum int unsigned;
drop temporary table if exists categoryIds;
create temporary table categoryIds
(
`CategoryId` int
) engine = memory;
set tmpTxt = categories;
set foundPos = instr(tmpTxt, ',');
while foundPos <> 0 do
set element = substring(tmpTxt, 1, foundPos-1);
set tmpTxt = substring(tmpTxt, foundPos+1);
set resultingNum = cast(trim(element) as unsigned);
insert into categoryIds (`CategoryId`) values (resultingNum);
set foundPos = instr(tmpTxt,',');
end while;
if tmpTxt <> '' then
insert into categoryIds (`CategoryId`) values (tmpTxt);
end if;
CASE
WHEN sortOrder = 0 THEN
SET @sortString = "ProductResult_Relevance DESC";
WHEN sortOrder = 1 THEN
SET @sortString = "ProductResult_Price ASC";
WHEN sortOrder = 2 THEN
SET @sortString = "ProductResult_Price DESC";
WHEN sortOrder = 3 THEN
SET @sortString = "ProductResult_StockStatus ASC";
END CASE;
SET @theSelect = CONCAT(CONCAT("
SELECT SQL_CALC_FOUND_ROWS
supplier.SupplierId as Supplier_SupplierId,
supplier.Name as Supplier_Name,
supplier.ImageName as Supplier_ImageName,
product_result.ProductId as ProductResult_ProductId,
product_result.SupplierId as ProductResult_SupplierId,
product_result.Name as ProductResult_Name,
product_result.Description as ProductResult_Description,
product_result.ThumbnailUrl as ProductResult_ThumbnailUrl,
product_result.Price as ProductResult_Price,
product_result.DeliveryPrice as ProductResult_DeliveryPrice,
product_result.StockStatus as ProductResult_StockStatus,
product_result.TrackUrl as ProductResult_TrackUrl,
product_result.LastUpdated as ProductResult_LastUpdated,
MATCH(product_result.Name) AGAINST(?) AS ProductResult_Relevance
FROM
product_latest_state product_result
JOIN
supplier ON product_result.SupplierId = supplier.SupplierId
JOIN
category_product ON product_result.ProductId = category_product.ProductId
WHERE
MATCH(product_result.Name) AGAINST (?)
AND
category_product.CategoryId IN (select CategoryId from categoryIds)
ORDER BY
", @sortString), "
LIMIT ?, ?;
");
set @keywords = keywords;
set @startOffset = startOffset;
set @itemsToReturn = itemsToReturn;
PREPARE TheSelect FROM @theSelect;
EXECUTE TheSelect USING @keywords, @keywords, @startOffset, @itemsToReturn;
SET @resultsFound = FOUND_ROWS();
SELECT @resultsFound as 'TotalResults';
IF @resultsFound > 0 THEN
INSERT INTO product_search_query (QueryText, CategoryId) VALUES (keywords, topLevelCategoryId);
END IF;
END $$
DELIMITER ;
Любая справка очень очень ценится!
3 ответа
С этим запросом вы мало что можете сделать.
Попробуй это:
Создайте
PRIMARY KEYнаcategoryIds (categoryId)Убедитесь, что
supplier (supplied_id)являетсяPRIMARY KEYУбедитесь, что
category_product (ProductID, CategoryID)(в данном порядке) являетсяPRIMARY KEY, или у Вас есть индекс сProductIDвпереди.
Обновление:
Если проблема связана с INSERT и product_search_query в таблице MyISAM, то проблема может быть связана с блокировкой MyISAM.
MyISAM блокирует всю таблицу, если он решает вставить строку в свободный блок в середине таблицы, что может привести к таймаутам.
Попробуйте использовать INSERT DELAYED вместо:
IF @resultsFound > 0 THEN
INSERT DELAYED INTO product_search_query (QueryText, CategoryId) VALUES (keywords, topLevelCategoryId);
END IF;
Это поместит записи в очередь на вставку и вернётся немедленно. Позже запись будет добавлена асинхронно.
Обратите внимание, что вы можете потерять информацию, если сервер умрет после выдачи команды, но до того, как записи действительно будут вставлены.
Обновление:
Так как ваша таблица InnoDB, это может быть проблемой с блокировкой таблицы. Запросы INSERT DELAYED не поддерживаются на InnoDB.
В зависимости от характера запроса, запросы DML на таблицу InnoDB могут содержать блокировки зазоров, которые будут блокировать вставки. Например:
CREATE TABLE t_lock (id INT NOT NULL PRIMARY KEY, val INT NOT NULL) ENGINE=InnoDB;
INSERT
INTO t_lock
VALUES
(1, 1),
(2, 2);
Этот запрос выполняет ref сканирование и установку замков на отдельные записи:
-- Session 1
START TRANSACTION;
UPDATE t_lock
SET val = 3
WHERE id IN (1, 2)
-- Session 2
START TRANSACTION;
INSERT
INTO t_lock
VALUES (3, 3)
-- Success
Этот запрос, делая то же самое, выполняет сканирование в диапазоне и устанавливает блокировку зазоров после значения ключа 2 , что не позволит вставить значение ключа 3 :
-- Session 1
START TRANSACTION;
UPDATE t_lock
SET val = 3
WHERE id BETWEEN 1 AND 2
-- Session 2
START TRANSACTION;
INSERT
INTO t_lock
VALUES (3, 3)
-- Locks
Включите медленные запросы, что даст вам представление о том, что занимает так много времени, чтобы выполнить, что есть время ожидания.
http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html
Выберите медленный запрос и оптимизировать это. затем беги на некоторое время и повторите.
Здесь есть какая-то отличная информация и инструменты http://hackmysql.com/nontech
dc
Обновление:
У вас есть проблема с сетью, вызывающей время ожидания, если вы используете Местный экземпляр MySQL, то это вряд ли или что-то блокирует столик для давно, вызывая тайм-аут. Процесс, который блокирует таблицу или таблицы для далеко слишком длинного, будет перечислена в медленном журнале как медленный запрос. Вы также можете получить медленный запрос журнала для отображения любых запросов, которые не используют индекс, приводящий к неэффективному запросу.
Если вы можете получить проблему, когда вы присутствуете, тогда вы также можете использовать инструмент, такой как phpmyadmin или командная линия для запуска "Show placeList \ g", это даст вам список того, какие запросы работают, пока проблема происходит.
Вы думаете, что проблема в вашем выписке вставки, поэтому что-то блокируется на этой таблице. Поэтому вам нужно найти то, что блокирует эту таблицу, поэтому вам нужно найти то, что работает так медленно, блокируя таблицу слишком долго. Медленные запросы - это один из способов сделать это.
Другие вещи, чтобы посмотреть на
CPU - это простаивает ли он или работает в полном шпельте
IO - это IO, вызывая HOLDUPS
RAM - вы заменяете все время (вызовут чрезмерный IO)
Настольный Product_Search_Query использует индекс?
Что является основным ключом?
Если ваш индекс использует строки, которые слишком длинные? Вы можете построить огромный файл индекса, который вызывает очень медленные вставки (медленный журнал запросов также покажет, что)
, и да, проблема может быть в другом месте, но вы должны начать где-то, не должны.
DC
Похоже на проблему с устройством, а не с R, попробуйте переустановить GTK +. Если это не сработает, попробуйте построить jpegs вместо png, если можете.
-121--4501567-Попробуйте обернуть EXECUTE следующим образом:
УСТАНОВИТЕ УРОВЕНЬ ИЗОЛЯЦИИ ТРАНЗАКЦИИ СЕАНСА, ПРОЧИТАЙТЕ НЕЗАФИКСИРОВАНО;
EXECUTE Выберите USING @ keywords, @ keywords, @ startOffset, @ iteyToReturn;
НАБОР SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Я делаю что-то похожее в TSQL для всех хранимых в отчете процедур и поисков, где повторяющиеся чтения не важны для уменьшения проблем блокировки/блокировки с другими процессами, выполняемыми в базе данных.