[TYPO3-commerce] Speeding up commerce?

[robelix]

Is it possible that your server has not enough ram?



Franz Koch wrote:
> Hi Ingo,
> 
>> Our Shops are running without any problems, and without speed problems.
>> The MySQL Problems looks like a cartesian product. Could you please 
>> start the slow query log and post your results here?
> 
> I already activated the slow query log. Here some stats:
> 
> Queries > 2 seconds:
> ------------------------------
> # User at Host: root[root] @ localhost [127.0.0.1]
> # Query_time: 0  Lock_time: 0  Rows_sent: 1  Rows_examined: 163
> SELECT cu_symbol_left, cu_symbol_right, cu_sub_symbol_left, 
> cu_sub_symbol_right, cu_decimal_point, cu_thousands_point, 
> cu_decimal_digits, cu_sub_divisor
> FROM static_currencies
> WHERE cu_iso_3="EUR";
> ------------------------------
> after that I added a new index key to the static_currencies including 
> cu_iso_3 as key and this 'problem' was gone. Btw - why does moneyLib 
> have to fetch the static info over and over again from db? It should use 
> a temporary cache for it in some global var.
> 
> 
> Queries > 1 second:
> ------------------------------
> # User at Host: root[root] @ localhost [127.0.0.1]
> # Query_time: 0  Lock_time: 0  Rows_sent: 1  Rows_examined: 81
> SELECT uid
> FROM pages
> WHERE doktype=2 and module="dam" AND deleted=0;
> 
> 
> # User at Host: root[root] @ localhost [127.0.0.1]
> # Query_time: 0  Lock_time: 0  Rows_sent: 1  Rows_examined: 6275
> SELECT uid
> FROM tx_commerce_articles
> WHERE classname = 'sysdelivery';
> 
> 
> # User at Host: root[root] @ localhost [127.0.0.1]
> # Query_time: 0  Lock_time: 0  Rows_sent: 0  Rows_examined: 2
> SELECT *
> FROM sys_lockedrecords
> WHERE sys_lockedrecords.userid!=1
> 	AND sys_lockedrecords.tstamp > 1191584481;
> 
> # User at Host: root[root] @ localhost [127.0.0.1]
> # Query_time: 0  Lock_time: 0  Rows_sent: 3  Rows_examined: 44
> SELECT distinct 
> tx_commerce_attributes.uid,tx_commerce_attributes.sys_language_uid,tx_commerce_articles.uid 
> as article ,tx_commerce_attributes.title, tx_commerce_attributes.unit, 
> tx_commerce_attributes.valueformat, 
> tx_commerce_attributes.internal_title,tx_commerce_attributes.icon, 
> tx_commerce_articles_article_attributes_mm.sorting
> 
> FROM 
> tx_commerce_articles,tx_commerce_articles_article_attributes_mm,tx_commerce_attributes
> 
> WHERE 
> tx_commerce_articles.uid=tx_commerce_articles_article_attributes_mm.uid_local 
> AND 
> tx_commerce_attributes.uid=tx_commerce_articles_article_attributes_mm.uid_foreign 
>   AND tx_commerce_articles.uid_product = 3791  AND 
> tx_commerce_articles.uid in (3796) order by 
> tx_commerce_articles_article_attributes_mm.sorting;
> 
> ------------------------------
> 
> I already added a new index key to tx_commerce_articles for the field 
> 'classname', so this is fixed, too. But commerce is still slow (around 5 
> to 7 seconds for pages on first hit).
> 
> I'm no MySQL expert, but might it help to change the database tables 
> from myISAM to InnoDB?
> 
>> Could you also give some figures on your installation (amount of 
>> products, categories, attributes ...) and installed extensions?
> 
> well - the shop is not very special:
> - 6272 Products
> - 6277 Articles
> - 9061 Prices (some only for certain fe_groups)
> - 395 Categories (some only visible for certain fe_groups)
> 
> used extensions besides commerce (and related):
> - dam 1.0.11
>    (using references with dam_ttcontent, css_filelinks, dam_filelinks,
>     dam_multimedia)
> - feuserloginsystem 0.1.2 (modified version)
> - sr_feuser_register 2.5.7
> - fh_library 0.0.19 (needed for sr_feuser_register)
> - moneylib 1.2.1
> - graytree 0.1.1
> - static_info_tables 2.0.5
> - templavoila 1.3.3
> - some small own extensions for modifying the BE
> 
> 
> Hope that helps so far and thanks for the help.
> 
> --
> Kind regards,
> Franz Koch
> _______________________________________________
> TYPO3-project-commerce mailing list
> TYPO3-project-commerce at lists.netfielders.de
> http://lists.netfielders.de/cgi-bin/mailman/listinfo/typo3-project-commerce
>