View Issue Details
ID | Project | Category | View Status | Date Submitted | Last Update |
---|---|---|---|---|---|
0002740 | OXID eShop (all versions) | 4.05. Performance | public | 2011-04-12 14:18 | 2012-12-10 13:29 |
Reporter | andreas_ziethen | Assigned To | |||
Priority | high | Severity | major | Reproducibility | always |
Status | resolved | Resolution | fixed | ||
Product Version | 4.4.8 revision 34028 | ||||
Fixed in Version | 4.6.0_beta3 | ||||
Summary | 0002740: Logging of not found SEO URLs in oxubase causes performance problems in big shops | ||||
Description | In 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. | ||||
Tags | Performance | ||||
Theme | Both | ||||
Browser | All | ||||
PHP Version | any | ||||
Database Version | any | ||||
has duplicate | 0003282 | closed | dainius.bigelis | make "oxseologs" optional? |
|
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; |
|
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. |