Hi David,
My site at {link saved} currently has just under 90K products which is less than I have successfully used on other sites.
The search seems slow returning any category, merchant or brand pages. I understand that the cheap hosting I am using probably doesn't help much, however is there a way to optimise and speed up the search results?
Thanks
Richard
Hi. I came here because my searches are very slow!
I have modified my.cnf
But when I run that dbmob, I get this error
[ALTER TABLE `pt_products_import` ENABLE KEYS][Table 'tapestry.pt_products_import' doesn't exist]
Where is my import database?
BTW: I have 1.120.213 products. Server VPS 2GB with SSD.
Under [mysqld], I have
key_buffer_size = 500M
sort_buffer_size = 32M
Also, as read here https://haydenjames.io/mysql-query-cache-size-performance/
I modified these parameters
query_cache_type = 1
query_cache_limit = 256K
query_cache_min_res_unit = 2k
query_cache_size = 80M
Thank you for your help!
Hi,
The dbmod.php script from above incorrectly referenced products_import instead of products - corrected above, so I would suggest running that in the first instance. (products_import is the temporary table created during the import process so that the site is not offline at all even when importing 1000s of products)
I'm currently researching the best key / sort buffer size approach further - I would suggest trying slightly larger values for sort_buffer_size - don't forget to restart MySQL (and also I would suggest your web server) after making configuration changes to ensure that everything takes effect...
Cheers,
David.
--
PriceTapestry.com
Hi again David.
I have spotted one problem besides the search. It affects into products.php
In that script we have 3 database_querySelect. The last one slows the page heavily!
If a measure time of the script I have that line 140 is very slow! It takes 20 seconds to finish.
$sql2 = "SELECT id,name,normalised_name,image_url,description,price,rating,MIN(price) AS minPrice, COUNT(id) AS numMerchants FROM `".$config_databaseTablePrefix."products` WHERE name IN (".$in.") GROUP BY name";
echo "|Time before: ". (microtime(true) - $start); //0.0034360885620117
database_querySelect($sql2,$rows2);
echo "|Time after: ". (microtime(true) - $start); //20.329293012619 Hi,
That's the Related Products re-query, using only indexed fields in the WHERE clause, so it is again pointing towards a possible MySQL indexing issue - that particular query runs fine on some very large installations. Please could you try a REPAIR TABLE against pt_products as that may recover the situation - the following dbfix.php script will run the query;
<?php
set_time_limit(0);
require("includes/common.php");
$sql = "REPAIR TABLE `".$config_databaseTablePrefix."products`";
database_queryModify($sql,$result);
print "Done.";
?>
If there is an indexing issue and REPAIR TABLE has to rebuild all indexes the above may take some time so would be best run from the command line e.g.
$cd public_html
$php dbfix.php
...or better still, directly from the MySQL console;
$mysql
mysql>USE database_name
mysql>REPAIR TABLE pt_products
Hope this helps! If still no joy, please could you email me and I'll forward a profiling version of the database library that displays all queries and time taken for each page view...
Cheers,
David.
--
PriceTapestry.com
Hi Richard,
The indexed queries should be very fast, firstly, although this is almost certainly not the case there is remote possibility that the table has ended up in state with keys disabled. To check that keys are enabled, create and run the following dbmod.php script:
<?phprequire("includes/common.php");
$sql = "ALTER TABLE `".$config_databaseTablePrefix."products` ENABLE KEYS";
database_queryModify($sql,$result);
?>
If you have root access, or a managed service level that provides configuration support MySQL's key_buffer_size and sort_buffer_size should be increased significantly from their defaults - I normally suggest around 1/4 of total server instance available memory equally between them, so for a 1GB server;
key_buffer_size=128Msort_buffer_size=128M
(settings go in the [mysqld] section of /etc/my.cnf)
Hope this helps!
Cheers,
David.
--
PriceTapestry.com