Sales Page Link Performance

[ajdruff]

Clicking a sales page takes up to 15-20 seconds. This is interminable and must be stopped.

[ajdruff]

Cause: mysql stored procedure nstock_stats_count_domain_name_click performance. Fix;:

• removed 'IGNORE' from select statement
• added START TRANSACTION and COMMIT statements
• removed DEALLOCATE statements

Final Code:

DELIMITER $$

DROP PROCEDURE IF EXISTS `nstock_stats_count_domain_name_click`$$

CREATE  PROCEDURE `nstock_stats_count_domain_name_click`(`SESSION_ID` VARCHAR(255),`DOMAIN_NAME` VARCHAR(255))
BEGIN
--  -------------------------------------------------------------------------------
-- nstock_stats_count_domain_name_click
-- Increases the click count for the domain
-- call nstock_stats_count_domain_name_click('my_cool_session5','mycooldomain.com')
-----------------------------------------------------------------------------------
SET @SQL="
-- to use the paramater, refer to it by using a question mark.
-- IGNORE is used because clicks more than one will generate a duplicate entry error because
-- of the unique key constraints. if you want to see these errors, then remove the IGNORE keyword,
-- and you'll it generate errors when debug is on.
Insert into nstock_stats_dn_clicks_session (`session_id`,`domain_id`,`date_added`,`time_added`)
VALUES(@SESSION_ID,(SELECT `id` FROM `nstock_domains` where `on_ticker`='y' and domain_name(`id`)=@DOMAIN_NAME),date(now()),NULL);

";

START TRANSACTION;
SET @DOMAIN_NAME=DOMAIN_NAME;
SET @SESSION_ID=SESSION_ID;
PREPARE STMT From @SQL;
EXECUTE STMT;
COMMIT;
-- DEALLOCATE PREPARE STMT;
    END$$

DELIMITER ;

troubleshooting

Overview: The sequence of clicking a sales page link is this :

1. link : http://nomstock-dev.com/stats/clicks/domain-sales-page/zzask.com/
2. Queryvars.php adds a rewrite rule to map the pretty url to one with query paramaters:

        $this->addWPRewriteRule(
                'stats/clicks/domain-sales-page/([^\/]+)\/*?$' //$match_pattern - 
                , 'index.php?' . $this->plugin()->getSlug() . '_action=countDomainSalesPageClick&domain_name=' . '$matches[1]'   //$target_pattern - 
        );

1. an action is added to map the action to a method

       /*
         * Stats Counter - Count a click on a domain name sales page url. 
         */
        add_action( $this->_query_var_prefix . '_action' . '_countDomainSalesPageClick', array( $this->plugin()->getModule( 'NomstockStats' ), 'countDomainSalesPageClick' ) );

1. The method NomstockStats -> countDomainSalesPageClick
   1. session_id is assigned
   2. domain_name is retrieved from query paramaters
   3. stored procedure nstock_stats_count_domain_name_click is called using mySQLCallProc
   4. redirects to final url

debug statements $this->debugDomainSalesPageRedirect( true); $this->debug()->setMethodFilter( 'getDomainSalesPageUrl', true );

thoughts: the choke point is likely the stored procedure or the redirects.

1. test the stored procedure. call the stored procedure directly. Example:

call nstock_stats_count_domain_name_click('ts_1','zzask.com')

I found that this can take a few seconds or up to 15 seconds or so. Why?

things to try:

• limit the number of names returned on front page under the theory that clicking a link won't take effect until the page has fully loaded. to do this :
  • change @MAX_LIMIT to 3 in _procs_nstock_get_ticker_page
  • change LIMIT in _view_domain_listings.sql to 3
  • result: no effect.

observations if I place a debug stop after the mysqlproc, refreshing the resulting page sometimes gets it down to 2 second or less. Refreshing the page thereafter is somethimes 2 seconds, sometimes 10 times that.

transactions after googling slow insertions for mysql, I read the http://dev.mysql.com/doc/refman/5.1/en/insert-speed.html docs. Because I'm using InnoDB, I can't use Insert delayed statement, but can use transactions. I added the START TRANSACTION and COMMIT statements to my stored procedure:

START TRANSACTION;
SET @DOMAIN_NAME=DOMAIN_NAME;
SET @SESSION_ID=SESSION_ID;
PREPARE STMT From @SQL;
EXECUTE STMT;
COMMIT;

This appears to speed things up considerably, close to 2 seconds. If I remove the 'IGNORE' statement, i further speed things up because mysql bails after detecting the duplicate id error, which is fine with me because I want the insertion to fail and it won't impact the rest of the php execution.

Additionally, I found that removing the 'DEALLOCATE' statement also seemed to have some effect. Deallocate happens automatically, so I'm not sure I need to do it explicitly, although there might be some issues with pooled connections.

Long Redirects

I analyzed the redirects in Queryvars.php and found that i had /stats/clicks/domain-sales-page/example.com/ calling a stats method , then redirecting to the final page after that. I refactored the code so I no longer have a /stats/clicks/domain-sales-page/example.com link but now go directly to the final url, and count the click only if the referer is the home page.

final code

        /*
         * Add Action for Showing the Sales Page - Permalink Action Sales Page
         * 
         * Renders the Sales Page
         * 
         * 
         */
        add_action( $this->_query_var_prefix . '_action' . '_permalinkActionShowSalesPage', array( $this->plugin()->getModule( 'Core' ), 'permalinkActionShowSalesPage' ) );

Now add the rewrite rule

        /*
         * Domain Landing Page Permalink
         * Displays the seller's sales page for the domain name
         * url scheme:  /domain/example.com/

         */

        $this->addWPRewriteRule(
                'domain/([^\/]+)\/*?$' //$match_pattern - 
                , 'index.php?' . $this->plugin()->getSlug()
                . '_action=permalinkActionShowSalesPage' //the method within Core that will handle this url     
                . '&domain_name=' . '$matches[1]'   //$target_pattern - 
        );

And finally the method, which is a wrapper around showTemplate that allows me to hardcode values instead of getting them from query paramaters which is a hit on performance.

     /**
     * Permalink Action  - Show Sales Page
     *
     * Wrapper around showTemplate that Handles a Pretty Url that shows the Sales Page

     * 
     *
     * @param none
     * @return void
     */

        public function permalinkActionShowSalesPage() {

        $this->plugin()->getModule( 'NomstockStats' )->countDomainSalesPageClick();

        $this->showTemplate(
                array( $this, 'getTagsForNomstockSalesPage' ) //$tags callback
                , 'nomstock-domain-sales-page' //$template_name 
        );

    }