Squeezing better performance

Discussions about the Enuuk Platform in general - both the GPL and Commercial versions
Post Reply
RWAP
Site Admin
Posts: 748
Joined: Fri Jan 08, 2010 2:23 am
Location: Stoke-on-Trent
Contact:

Squeezing better performance

Post by RWAP » Fri Jun 15, 2012 10:56 am

As sellmyretro.com becomes increasingly popular, I am always looking for ways of improving response times and reducing the load on the server (especially as I had the dreaded 'too many connections' error from SQL this morning).

Anyway, I was looking at the options for PDO which is used to connect Enuuk to the MySQL database and have tried the following change to the class\DB.php

Code: Select all

    static public function getInstance()
    {
        global $persistenceConfig;
        if(!self::$db){
            switch($persistenceConfig['type']){
            case "PDO":
                try{
                    self::$db = new MyPDO($persistenceConfig['DSN'],
                                  $persistenceConfig['user'],
                                  $persistenceConfig['password']);
                    self::$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                }catch (PDOException $e){
                    throw new Exception("FATAL ERROR: can't connect to database... ".$e->getMessage());
                }
                //set UTF8 as default charset in mysql
                if(substr($persistenceConfig['DSN'], 0, 6) == "mysql:"){
                    self::$db->query("SET NAMES 'utf8'");
                }
                break;
            default:
                throw new Exception("FATAL ERROR: system database not defined!");
                break;
            }
        }
        return self::$db;
    }

Becomes:

Code: Select all

    static public function getInstance()
    {
        global $persistenceConfig;
        if(!self::$db){
            switch($persistenceConfig['type']){
            case "PDO":
                try{
                    self::$db = new MyPDO($persistenceConfig['DSN'],
                                  $persistenceConfig['user'],
                                  $persistenceConfig['password'],
                                  array(
      								PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
                                    PDO::ATTR_PERSISTENT => true,
                                    PDO::MYSQL_ATTR_COMPRESS =>true
				    ));
                    self::$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                }catch (PDOException $e){
                    throw new Exception("FATAL ERROR: can't connect to database... ".$e->getMessage());
                }
                //set UTF8 as default charset in mysql
                if(substr($persistenceConfig['DSN'], 0, 6) == "mysql:"){
                    self::$db->query("SET NAMES 'utf8'");
                }
                break;
            default:
                throw new Exception("FATAL ERROR: system database not defined!");
                break;
            }
        }
        return self::$db;
    }

This appears to be working and provide a better response - but it may not work on all servers

What do you think ? It may well be that in reality the improvement is minimal, but worth a try!

RWAP
Site Admin
Posts: 748
Joined: Fri Jan 08, 2010 2:23 am
Location: Stoke-on-Trent
Contact:

Re: Squeezing better performance

Post by RWAP » Tue Aug 21, 2012 8:18 am

There are a few more means of improving performance and reducing memory - my own Increased Speed module covers these, but I have identified the following:

a) Reduce number of SQL calls in class\Offer.php and class\Invoice.php - if you look at some of the calls in SaveToDB() you will see that a few SQL calls are placed in a loop (for example to save custom offer fields) - you are better to use the loop to construct just one SQL call and then execute the single SQL call afterwards

b) Create a lazy loader for class\Item.php - so that it only loads the image gallery when actually required

c) Add a __destruct() call for class\Offer.php and class\Item.php so that they release the memory as soon as possible

d) Create faster SQL calls for cron job and JSON lookups - as the amount of information required from each table is a lot less than that provided by GetListFromDB() calls

RWAP
Site Admin
Posts: 748
Joined: Fri Jan 08, 2010 2:23 am
Location: Stoke-on-Trent
Contact:

Re: Squeezing better performance

Post by RWAP » Fri Nov 23, 2012 5:34 pm

Another major improvement has been to completely re-write the store class (with some minor improvements from re-writing the userAccess class) - both of these are used quite extensively

- Again these improvements have been incorporated into our Increased Speed module - viewtopic.php?f=11&t=120&start=0

Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest