View Issue Details

IDProjectCategoryView StatusLast Update
0002740OXID eShop (all versions)4.05. Performancepublic2012-12-10 13:29
Reporterandreas_ziethen Assigned To 
PriorityhighSeveritymajorReproducibilityalways
Status resolvedResolutionfixed 
Product Version4.4.8 revision 34028 
Fixed in Version4.6.0_beta3 
Summary0002740: Logging of not found SEO URLs in oxubase causes performance problems in big shops
DescriptionIn oxubase class you find the method _processRequest() which may cause serious performance problems due to a problematic database query and some senseless logging:

First there's an if-clause like this:
if ( $this->_canRedirect() && ( $sRedirectUrl = oxSeoEncoder::getInstance()->fetchSeoUrl( $sStdUrl ) ) ) {
...
}

1. This will be executed on each URL which comes as parameter URL like "index.php?...". The according db query is done in oxseoencoder::fetchSeoUrl(). This query has oxstdurl in its where-clause, but oxstdurl is a text field with no index. So this query is slow (0,5 sec in our special case with about 350.000 entries in oxseo).
We tried to change the field from 'text' to 'varchar(500)' and put an index on it - then the query takes 0.0004 sec.!

2. The query mentioned above is called each time also if there is a 'force_sid' or 'stoken' parameter in the URL. This does not make any sense, cause you should never find 'force_sid' or 'stoken' in oxstdurl in oxseo table.

3. Many external applications (like pixi e. g.) do call their shop interfaces by using HTTP-GET calls. Those calls will never be found in oxseo table, so those should NOT be searched for!

4. Each time a SEO-URL is not found in oxseo table, the _processRequest() function in oxubase will write a log entry in oxseologs table using a REPLACE statement. These log entries are not used anywhere in the shop. But - in shops with many external GET calls you will very easily find millions of entries in this table after a while. In our special example (very big shop) today I found nearly 10 millions (!) of entries there. The whole table was of a size of 2,5 GB!! - REPLACEs on such big tables do slow down the whole database.
Another shop using pixi has had about 5 millions of entries in oxseolog. And these entries contained all the XML stuff pixi submits to the shop to update stock, order state and so on ...

So what we need is:
1. Change oxstdurl to varchar instead of text and put an index on that field.
2. Prevent looking for SEO-URLs for parameter URLs having 'force_sid' or 'stoken' in it - or strip this before looking into oxseo table. (You might think about making a configurable array of some keywords - if a parameter URL contains one or more of those keywords, it should not been looked up in oxseo table.
3. Switch of the senseless logging of not found SEO URLs in oxseologs table.
TagsPerformance
ThemeBoth
BrowserAll
PHP Versionany
Database Versionany

Relationships

has duplicate 0003282 closeddainius.bigelis make "oxseologs" optional? 

Activities

sarunas_valaskevicius

2011-06-03 13:19

reporter   ~0004690

Last edited: 2011-06-03 13:20

1. Change oxstdurl to varchar instead of text and put an index on that field.
column's type changes are WAITING to be implemented in the upcoming UPDATE;
index to oxseo for oxstdurl field's 100 characters is added to the upcoming PATCH

2. Prevent looking for SEO-URLs for parameter URLs having 'force_sid' or 'stoken' in it - or strip this before looking into oxseo table.
DONE for the upcoming PATCH:
see ::getRequestUrl()

3. Switch of the senseless logging of not found SEO URLs in oxseologs table.
DONE for the upcoming PATCH:
see config.inc.php
    /**
     * should requests, coming via stdurl and not redirected to seo url be logged to seologs db table?
     * note: only active if in productive mode, as the eShop in non productive more will always log such urls
     */
    $this->blSeoLogging = false;

dainius.bigelis

2011-06-22 15:05

reporter   ~0004757

Most of the stuff is already done in Patch (upcomming 4.5.1 version). What's left - DB changes for column types (will be done in next Update). So priority decreased to High.