My site is causing a high load on the server. I'm getting alot od slow queries. My host say its because my database isn't indexed properly. Is there a way to speedup queries? I tried editing my.cnf suggested in another post but my site seems to run faster at the default setting.
I made the changes I'll see does this helps.
I haven't seen much improvement in proformance. Maybe alittle faster with feeds thats small but larger feeds the queries are slower. Since I made the change last night there have been over 500 slow queries.
Hi,
When you say the speed depends on the feed being queried, are these just clicking on the merchant themselves from the merchant list? If you are logging the slow queries, is it possible for you to copy one from the log and post it so that I can see exactly what SQL is being generated...
Cheers,
David.
Most are from clicking on the merchant or product.
# Query_time: 15 Lock_time: 0 Rows_sent: 1 Rows_examined: 1
SELECT COUNT(DISTINCT(name)) as resultcount FROM `products` WHERE brand='Aiyana ';
# Time: 071102 2:23:24
# User@Host: root[root] @ localhost []
# Query_time: 25 Lock_time: 0 Rows_sent: 3 Rows_examined: 6
SELECT * FROM `products` WHERE name = 'Wincraft Matt Kenseth 150 Piece Puzzle Set' ORDER BY price LIMIT 10;
# Time: 071102 2:23:35
# User@Host: root[root] @ localhost []
# Query_time: 10 Lock_time: 0 Rows_sent: 10 Rows_examined: 30160
SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants FROM `products` WHERE merchant='YOOX.com' GROUP BY name LIMIT 80,10;
# Time: 071102 2:25:56
# User@Host: root[root] @ localhost []
# Query_time: 642 Lock_time: 0 Rows_sent: 10 Rows_examined: 1308858
SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants FROM `products` WHERE merchant='Overstock.com' GROUP BY name LIMIT 0,10;
# Time: 071102 2:27:21
# User@Host: root[root] @ localhost []
# Query_time: 10 Lock_time: 0 Rows_sent: 1 Rows_examined: 1
SELECT COUNT(DISTINCT(name)) as resultcount FROM `products` WHERE brand='A Oral ';
I'm also getting slow queries while inserting products in the database.
# Time: 071104 9:00:31
# User@Host: root[root] @ localhost []
# Query_time: 30 Lock_time: 8 Rows_sent: 0 Rows_examined: 0
SET insert_id=87804652;
INSERT INTO `products` SET
merchant='Buy.com Books',
name='Philosophy of the Arts An Introduction to Aesthetics ',
description='Now in its third edition, 1st edn 1997, 2nd edn 2000 i Philosophy of the Arts i is distinctive edn. aesthetics textbook because it is structured largely by art form, with a chapter each on music, poetry, architecture, painting and film most other books in this area study the subject by theory art as imitationexpressionsignificant formetc. or by concept evaluation, interpretation, representation, etc.. ',
image_url='http://ak.buy.com/db_assets/prod_images/273/31255273.jpg\n ',
buy_url='http://affiliate.buy.com/gateway.aspx?adid=17662&aid=10387780&pid=1790701&sURL=http%3a%2f%2fwww.buy.com%2fprod%2fphilosophy-of-the-arts-an-introduction-to-aesthetics%2fq%2floc%2f106%2f31255273.html\n ',
price='29.95',
search_name='PhilosophyoftheArtsAnIntroductiontoAesthetics',
category='Books Aesthetics ',
brand='Gordon Graham ',
dupe_hash='21e522ee0af4e9db3b99cce4e09ec497';
# Time: 071104 9:02:35
# User@Host: root[root] @ localhost []
# Query_time: 10 Lock_time: 4 Rows_sent: 0 Rows_examined: 0
SET insert_id=87804831;
INSERT INTO `products` SET
merchant='DicksSportingGoods',
name='Columbia Notre Dame Fighting Irish QB Microfleece Mock Turtleneck',
description='Support your favorite team in comfort and style by wearing this Notre Dame Fighting Irish QBtm Microfleece Mock Turtleneck from Columbiar. The officially licensed long sleeve polyester featherweight microfleece mock turtleneck is decorated in the team colors and designed with the school name embroidered on the left side of the collar.',
image_url='http://DSP.imageg.net/graphics/product_images/p2447440t130.jpg',
buy_url='http://click.linksynergy.com/fs-bin/click?id=xdBFK56PPk4&offerid=134861.2176544&type=15&subid=0',
price='39.99',
search_name='ColumbiaNotreDameFightingIrishQBMicrofleeceMockTurtleneck',
category='Apparel',
brand='Columbia',
dupe_hash='da8225d510d4724596670b1cf9de509e';
Hi,
Approximately how many products do you have altogether?
Is anything in the 1 Million+ products discussion relevant to your configuration:
http://www.pricetapestry.com/node/732
Cheers,
David.
Yes I have over a Million products. I'm using a VPS; the server has 4G of ram, Processor is 3.5GHz processor. I'm not sure how much ram is each vps gets.
David, could you tell me which lines should I modify in order to take advantage or fulltext in queries for related products?
Thanks in advice.
Hi,
Related Products will use FULLTEXT if there is no category or brand similarity to go on (the idea is that using category / brand improves the relevance), but you can disable this which will let the FULLTEXT index be used - to do this, look for the following code beginning at line 92 in products.php:
if ($product["products"][0]["category"])
{
$wheres[] = "category = '".database_safe($product["products"][0]["category"])."'";
$ignoreFullText = TRUE;
}
if ($product["products"][0]["brand"])
{
$wheres[] = "brand = '".database_safe($product["products"][0]["brand"])."'";
$ignoreFullText = TRUE;
}...and either comment out or delete that section.
Cheers,
David.
--
PriceTapestry.com
Ah ok. What do you think about to improve performance making these fields fulltext as we have for name? Does it have any sense?
Hi sirmanu,
Related Products doesn't implement the minimum length checking in the same way as normal search so no changes are required in order to take advantage of the change to minimum word length of 1...
Cheers,
David.
--
PriceTapestry.com
Hi,
For normal product page views, and search queries of 4 or more characters the database uses MySQL's FULL TEXT index, which should not cause any performance problems on even moderate hardware. For short queries however, a full table scan needs to be performed in order to find products where the text is contained within the product name rather than at the beginning.
In order to confirm that this is the case, I think it would be a worthwhile test in the first instance to force the Full Text index for all queries. You can do this by changing the following code on line 63 of search.php:
if (strlen($parts[0]) > 3)to...
if (1)Cheers,
David.