Need tester for performance tweaks

Sadly that isn't even close to all the products.. that just my asi csv file. the csv import extension on the shop here was able to import it. Not quite sure how xsecrets pulling off the 2 million product thing... After working some of my other issues I'll be pasting in his speed boost tweaks.

That 30 dollar csv importer def worth it btw. I have about 80-90 prods on my live store and those added one by one horrible..

I'd like to go from regular site to newegg overnight..

well my 2million products were not imported through csv it was a direct db to db import, and Like I said it still takes 10-15 sec for a page to load, but before the optimizations it would simply timeout every time.

Qphoria wrote:Hmm the "WHERE AND" to "WHERE" bit in the last block of vqmod doesn't seem to be matching for some reason. Let me try it again

If you are trying to load 47000 in google base that will never work unless your host timeout is set really high. RSS feeds weren't ever meant to be that large and even with this db optimization, it won't change the fact that trying to load data for 47000 products on one page will still run out of memory. Perhaps an export to googlebase xml file on the server is an option, but still loading that much data from database will cause most hosts to timeout

My friend made suggestion on trying to make it call like 3-5k at a time until it has the entire thing instead of yanking 50k products at once and having mysql go WTF? sitemap does about the same thing lol.

Btw thanks for the help you guys do some nice work.

Demon5 wrote:

Qphoria wrote:Hmm the "WHERE AND" to "WHERE" bit in the last block of vqmod doesn't seem to be matching for some reason. Let me try it again

If you are trying to load 47000 in google base that will never work unless your host timeout is set really high. RSS feeds weren't ever meant to be that large and even with this db optimization, it won't change the fact that trying to load data for 47000 products on one page will still run out of memory. Perhaps an export to googlebase xml file on the server is an option, but still loading that much data from database will cause most hosts to timeout

My friend made suggestion on trying to make it call like 3-5k at a time until it has the entire thing instead of yanking 50k products at once and having mysql go WTF? sitemap does about the same thing lol.

Btw thanks for the help you guys do some nice work.

Yea.. it would need some sort of smaller batch grouping... kinda like how bigdump works

on your vqmod.xml commenting out
seems to make it work. Not sure if that will cause problem or defeat purpose though

Demon5 wrote:on your vqmod.xml commenting out

Code: Select all

   <operation error="skip"><!-- 1 places-->
            <search position="replace"><![CDATA[
            WHERE AND
            ]]></search>
            <add trim="true"><![CDATA[
           WHERE
            ]]></add>
        </operation>

seems to make it work. Not sure if that will cause problem or defeat purpose though

Oddly, that is the reverse.. you should only get the error if you DONT have this block of code... but anyway ..if it works your way ... great. That won't affect the test. It was just there to prevent the error it apparently causes for you. Weird.

Anyway, is it any faster for you using this ? Been waiting to hear more results to figure if this should go into the core or not.

Qphoria wrote:

Code: Select all

   <operation error="skip"><!-- 1 places-->
            <search position="replace"><![CDATA[
            WHERE AND
            ]]></search>
            <add trim="true"><![CDATA[
           WHERE
            ]]></add>
        </operation>

Oddly, that is the reverse.. you should only get the error if you DONT have this block of code... but anyway ..if it works your way ... great. That won't affect the test. It was just there to prevent the error it apparently causes for you. Weird.

Anyway, is it any faster for you using this ? Been waiting to hear more results to figure if this should go into the core or not.

I thought you couldn't do a vQmod search-and-replace without using whole lines. Wouldn't the <add> data need to be the complete database query string?

Johnathan wrote:

Qphoria wrote:

Code: Select all

   <operation error="skip"><!-- 1 places-->
            <search position="replace"><![CDATA[
            WHERE AND
            ]]></search>
            <add trim="true"><![CDATA[
           WHERE
            ]]></add>
        </operation>

Oddly, that is the reverse.. you should only get the error if you DONT have this block of code... but anyway ..if it works your way ... great. That won't affect the test. It was just there to prevent the error it apparently causes for you. Weird.

Anyway, is it any faster for you using this ? Been waiting to hear more results to figure if this should go into the core or not.

I thought you couldn't do a vQmod search-and-replace without using whole lines. Wouldn't the <add> data need to be the complete database query string?

replace can be inline.. everything else must be whole line simply because I can use str_replace for replace but use the line number +/- 1 for the rest

Qphoria wrote:replace can be inline.. everything else must be whole line simply because I can use str_replace for replace but use the line number +/- 1 for the rest

Didn't know that...thanks for the info.

with the xml file as you have it it gives
when you do a search. actually I just removed that section now and get error either way with or without removing bottom part..

Well the file was made a while back and maybe updates have changed.. regardless of search.. just load the homepage. load a category is it faster?

http://www.core-router.net/store/query_time.txt is without the mod
http://www.core-router.net/store/query_time2.txt is with the mod

Demon5 wrote:http://www.core-router.net/store/query_time.txt is without the mod
http://www.core-router.net/store/query_time2.txt is with the mod

that's not really a fair comparison. Those are two completely different pages, besides neither of them shows anything like the 4 second product queries you were seeing earlier.

Well I made some changes to csv. with my current csv from vendor with current products compared against the one with all current and past products and made new file that just combined prices and stock with descriptions (which was the big one had prob with) so now only like 9k products.

Just tested the file from Qphoria, and while it throws me no error on the site, it gives this error when using search:
Tried commenting out this: to no use. Same error.

Not really shure I need this thoug, I got the site down from a 29 second page load to under 5 seconds first try, and under 2 seconds try.

Cheers
Amrit

I manually edited the files and also changed the non admin part of cart to use memcache.

Should that vQmod xml work also with OC version 1.5.x? The tweak is great! I am getting few seconds queries load with few thousands of products and 1.5.1 OC version so it would be nice to use this mod.



Best regards,
Sebastian

I had a customer using a slow line and XP OPSYS complaining about load speed today, so I gave this a try he said the load is signifacantly faster. I am using 1.5.1.2 although I am getting this error in Vqmod:

----------------------20110913-011641-------------------------
SOURCEFILE: /home/ozfarmer/public_html/store/catalog/model/catalog/product.php
MODFILE: /home/ozfarmer/public_html/store/vqmod/xml/db_speed_clause_shift.xml
SEARCH: (f.product_id = pd.product_id)
OPERATION FAILED (ABORTED): NO MATCH FOR SEARCH!
--------------------------------------------------------------

But it doesnt seem to affect the site at all.

Paul

Ozfarmer wrote:I had a customer using a slow line and XP OPSYS complaining about load speed today, so I gave this a try he said the load is signifacantly faster. I am using 1.5.1.2 although I am getting this error in Vqmod:

----------------------20110913-011641-------------------------
SOURCEFILE: /home/ozfarmer/public_html/store/catalog/model/catalog/product.php
MODFILE: /home/ozfarmer/public_html/store/vqmod/xml/db_speed_clause_shift.xml
SEARCH: (f.product_id = pd.product_id)
OPERATION FAILED (ABORTED): NO MATCH FOR SEARCH!
--------------------------------------------------------------

But it doesnt seem to affect the site at all.

Paul

well that would make sense. you see the last part of that error ABORTED that means it didn't make any changes, so no it wouldn't affect the site at all.

Is there any way to stop the error occurring?