Magento – Faster Than a Speeding Bullet

speeding-bulletMagento is a popular, powerful ecommerce solution. It’s also quite slow out of the box.

How slow?

The metaphor “as slow as a snail” comes to mind. (and that’s being kind…)

However, with a few changes and some know-how, Magento can be as fast as any other ecommerce application, and likely faster than most.

Here’s what we do for our hosting clients to make Magento fly…

Out of the box vanilla install

To put this into perspective, we first installed a Magento Community version along with the sample data for a fully functional demo store. For this install, we did not perform any optimizations, just installed it as-is on a regular server.

We tested the “Sale” page of the demo, which is a category page with 4 products on it. This is a good representative page for any ecommerce store.

  • The initial page load came in at 3.9 seconds.
  • The repeat view had a page load time of 3.5 seconds.
  • The Time to First Byte (TTFB) was 1.8 seconds

Here’s the results:

Unoptimized Results (click image to enlarge)

Unoptimized Results (click image to enlarge)

The load time of nearly 4 seconds is very slow to render a web page. The TTFB of almost 2 seconds is an eternity. Ideally you want to be under 0.25 seconds.

As you can see, Magento out of the box with no optimizations is a dog.

Optimized Magento Demo on a Virtual Private Server (VPS)

The speeds you saw above with the vanilla install are what you might find at a host that does not specialize in Magento. In fact, that may be considered “fast” on some hosting servers (which is sad).

What could you expect if you were to host a Magento store with us? To answer that, we installed the same Magento version with sample data on a standard VPS (that has other live Magento clients on it). We applied our standard optimizations that we do for any Magento store we host.

Here’s what we did:

  • Latest Apache web server running in threaded mode for the most efficient environment to scale well.
  • Latest PHP 5.4 with proper optimized settings running as php-fpm for the best performance.
  • APC Op-code caching installed and optimized for Magento (size, speed, fragmentation monitoring).
  • MySQL 5.5 with optimized Query Caching and InnoDB settings.
  • .htaccess adjustments for proper gzip compression and proper client side caching
  • Magento admin settings adjusted for caching, indexing, ideal cron, and log rotations.
  • Two extensions installed for a Full Page Cache and Site Optimizer (combining css/js files, compressing images, minifying html code)

Optimized Results

  • The initial page load was 0.9 seconds
  • The repeat view page load time was 0.38 seconds
  • Initial Time to First Byte (TTFB) was 0.16 seconds

Here’s the results:

Optimized VPS (click image to enlarge)

Optimized VPS (click image to enlarge)

We were able to make the initial page view 400% faster. On a repeat view, this increased to almost 1,000% faster!

TTFB dropped by a factor of 10.

Essentially, you can browse this ecommerce Magento site with pages loading in well under a second. Almost seamless browsing from page to page.

Feel free to try the site yourself:

Magento can (and should) be fast

We were able to take a fully populated demo of Magento and make every page fully load in under a second. Each page is rendered viewable in under 0.2 seconds.

That is how it should be.

Unfortunately, many web hosts don’t offer this type of speed for their Magento clients. Overloaded servers, improperly tuned environments, and poorly executed set ups can contribute to a Magento store that can take up to 20 seconds for pages to load.

If your store is not running as fast as you’d like, get in touch with us. We can help you migrate your Magento store with NO downtime, no re-work needed, and we can enable your store to complete more sales due to a vastly improved experience for your customers. All of this in a PCI compliant environment.

No two Magento stores are alike. We personalize each environment to meet the needs and specific requirements of each installation. This is “the magic” that can set your store apart from the rest, and allow it to be “faster than a speeding bullet”.

photo credit

Shopsite Tip – Universal Analytics

universal-analytics-logoAs many of you know, the latest version of ShopSite (v12) now supports Google’s Universal Analytics tracking code.

Universal Analytics includes many improved features over its predecessor, including:

  • Tracking of user sessions across multiple devices
  • Compatibility with more devices
  • More control over configuration options
  • Custom dimensions and metrics for more advanced reporting

A complete list of Universal Analytics features and benefits can be found at:

If you haven’t already upgraded your existing Google Analytics properties to Universal Analytics, now is as good a time as any to make the switch.  Simply follow the upgrade instructions (step 1 only) at:

Once your property is upgraded, the next step is optional.  Although it is recommended, it is not required that you incorporate the new tracking code (at least not immediately).  Your pages and conversions will continue to track using the old code, despite having upgraded your property/account.

Merchants using ShopSite will update their tracking code in one of two ways – either using the built-in option or by adding the code manually.

ShopSite Published Pages

Merchants publishing their store pages via ShopSite can simply enable (or update) the Google Analytics feature in the back office under Merchandising > Google Services > Analytics.  Once the configuration changes are complete, save, then publish the site.

ShopSite will automatically add the required tracking code to your store pages, products and shopping cart.  This includes the ecommerce tracking code for the thank you page (requires that ecommerce tracking be enabled in your Analytics account).

(click to enlarge)

(click to enlarge)


Order Anywhere (or Custom Tracking Code)

Merchants building pages outside of ShopSite (and those who require customized tracking code), will need to add the new UA code manually to their site.

To assist you, we have published the following tutorial with instructions for manually adding Universal Tracking code (including ecommerce code) to your pages and shopping cart:

If you are hosted with LexiConn and you have any questions or difficulty implementing Universal Analytics, please don’t hesitate to contact us :)

ShopSite Tip – Quick Tips For Quantity Discounts

Quantity Pricing Sample

Quantity Pricing Sample

Quantity Pricing is a simple but powerful feature in ShopSite Pro which allows you to provide discounts for large orders, and even discount pricing across multiple products in the same product group.

In this post we’ll cover some tips for configuring as well as common mistakes which can occur.


The Settings

Quantity Pricing Settings

[click for larger view]

Quantity Pricing is configured at the product level, on a product’s Edit Product Info screen. In the sample here you’ll see it’s enabled with 3 different pricing quantities configured.

If Quantity Pricing is not working for you, the first thing to check is to make sure the “Check here to turn on Quantity Pricing” checkbox is enabled.  It’s common to miss that setting.

Next, be sure that the “Price/Unit” field is set with the amount you want to charge “Per Item”.  It’s not a total price for the quantity shown, but the price you want to charge per item when that quantity is in the cart.

For example in our sample we charge $2.00/each if you have a quantity of 1-499 in the cart.  But if you have 500-999 in the cart you are only charged $1.50/each.

NOTE: The price shown is charged for the total quantity of that product in the cart.  Ordering 750 will not charge $2.00 for the first 499 products and then $1.50 for 500-750.  Since the quantity is in the 500-999 range all 750 are charged $1.50/each.

Quantity Groups

Quanity Pricing Groups

[click for larger view]

What really expands the Quantity Pricing feature is its ability to classify products into groups.  Let’s say you want to run a special where someone can buy a mixture of 10 or more items at a discounted price:

The first step is to create a group for those products, which is done on the Merchandising Tools > Discounts > Quantity Groups screen.  Shown here is that screen with a “Movies” group created.

Then configure each of those 10 products to have Quantity Pricing enabled and set the 1+ price to the regular retail price and the 10+ price to be your discounted price.

While editing the pricing, set the “Quantity Pricing Group” field on the bottom of that section to the group you created.  You can see that field set to “none” in the first screenshot above.

With that field set, ShopSite will count the quantities of all products in the cart which are assigned to the same group to determine which price they receive.  So if someone buys 5 each of 2 different products in the Movies group they will receive the 10+ discounted price.

Sale Prices

As you may have seen in the first screenshot, Quantity Pricing also supports sale prices, so when the “On Sale Toggle” value (on a product’s Edit Product Layout screen) is enabled the “On Sale Price/Unit” column will be used.  It’s blank in our sample, but you can fill that in if you use the On Sale feature with your products.

Test, Test, Test

When you’re trying a new feature don’t hesitate just to test it.  Add a test page to your store (be sure to give it a filename: ex: “testpage.html”) then add a test product to that page and configure it with quantity pricing and all the settings you want to use.

Then you can visit your test page and your product’s More Info Page see how the pricing displays and test it by adding the product to your cart.  This way you can experiment without using a live product a customer may be trying to order.


Google Shopping: Goodbye Product Listing Ads

Google recently launched Shopping Campaigns, a new type of Adwords campaign, which is set to replace Product Listing Ad campaigns (PLA’s).  New Merchant Center feeds are required to use the new Shopping Campaign type.  According to Google, legacy feeds will be migrated to the new campaign type later this Summer (August 2014 ETA).

The new Shopping Campaign type has many benefits for merchants, including:

  • Streamlined Interfaceshopingcart
  • Advanced Product Targeting
  • Custom Labels
  • Negative Keywords


Within the new interface, Google has included a complete list of all eligible products in your shopping feed.  That’s right, no more switching back and forth between Adwords and Merchant Center just to verify current product list :)

Having the list in Adwords also helps eliminate any question as to whether your most up-to-date product feed is connected to Adwords.  There is, of course, a short delay before Adwords updates with the latest feed, but it’s worth the wait!

(click to enlarge)

(click to enlarge)


Dissecting the ‘All Products’ group is another strength of Shopping Campaigns.  The improved interface is easier and more powerful than its predecessors often irritating task of setting “Auto-Targets”.

When sub-dividing a group, Adwords automatically verifies the attribute exists in your feed.  Then,  it loads the appropriate items into the left window.  To select items for your sub-group, simply drag from the left window to the right window.

(click to enlarge)

(click to enlarge)

Below are the attributes available for sub-dividing the ‘All products’ group:

  • Category
  • Brand
  • Item ID
  • Condition
  • Product type
  • Custom label (0-4)

While there are several new options, it’s important to note that “adwords labels” and “adwords grouping” are no longer available for targeting products.  If you use these fields currently, you will want to migrate those values to one of the newly available custom labels.  Also noteworthy, is that “Category” represents the first level of the Google taxonomy (assigned to each product in your shopping feed).

Item ID works quite well in the new interface and gives merchants the ability to set CPC targets for a single product.  If you recall, this option did not work as expected in the PLA version.

Google also delivers five new custom attributes to help further organize and segment your product list.  Using these new fields of course, requires that you add them to your shopping feed.

To take advantage of custom labels, ShopSite users can leverage Extra Product Fields to send across new product categories, labels, descriptors, etc…

To create Extra Product Fields:

  • Navigate to Preferences > Extra Fields
  • Determine which Extra Product Field is to be used, assign it the name ‘custom_label_0′ (You can add up to 5 custom fields – last one would be named ‘custom_label_4′)
  • Update the ‘Number of product fields to display’, then Save

Assigning Extra Product Fields to the Google Shopping Feed:

  • Navigate to Merchandising > Google Services > Shopping > Configure
  • In the Attributes section, check the boxes next to each custom label you wish to include in the feed, then Save
    (click to enlarge)

    (click to enlarge)

  • Click ‘Send Feed’

The steps required for Magento users will vary based on the extension used in their respective stores.  The steps below apply primarily to those store owners using the core Google API.

  • Create new product attributes under Catalog > Attributes > Manage Attributes.
(click to enlarge)

(click to enlarge)

  • Assign new attribute to the appropriate Attribute Set under Catalog > Attributes > Manage Attribute Sets.
(click to enlarge)

(click to enlarge)

  • Map new attributes to the Google Shopping feed using Catalog > Google Content > Manage Attributes.
(click to enlarge)

(click to enlarge)

  • Synchronize products under Catalog >  Google Content > Manage Items.

Negative Keywords

Using negative keywords helps prevent ads from displaying where they have little opportunity to convert.  While this doesn’t necessarily change your budget, it should help ensure advertising dollars are better spent.  the new interface allows negative keywords to be assigned at the campaign and/or ad group level.  For negative keywords to be successful, you should choose terms used to find your products that also have keywords which do not relate to items you sell.

(click to enlarge)

(click to enlarge)

Be careful not to include a negative keyword or phrase that matches one of your valid keywords.  Doing so will prevent your ads from running!

Here are some links that may be helpful to understanding and using negative keywords:

To read more about the features in Shopping Campaigns, please visit Google.

photo credit


How To Set Up Selective Master Slave Replication in MySQL

mysqlThere are a number of tutorials out there for setting up Replication in MySQL. However, I couldn’t find one that fully addressed setting up selective master-slave replication in MySQL.

By selective, I am referring to only having one or a few databases that are replicated from the master database to the slave database. Any other databases on the master server are not copied/replicated.

Master-slave replication for a MySQL database refers to having a secondary MySQL server where any changes made to the main database are replicated (copied) to the secondary MySQL database. It becomes a copy of the main database. This secondary database can be used as a “hot” backup database, or used to run queries against that you don’t want to run on the live database, or used to allow for backups to be made without affecting performance of the live database.

Tutorials out there now

There is a great tutorial for setting up Master Slave Replication for all databases. It is well documented.

Another tutorial that is quite good and is almost complete (with a few typos) is One database set up for master-slave replication.

Then there is the ancient HowToForge MySQL Replication tutorial. It is thorough, but is very out of date.

Each of these tutorials is missing one or more items, or is not clear on some steps, and you can run into issues when setting it up.


There are 4 gotchas when setting up MySQL Master-Slave replication:

  • 1. Using the setting “replicate-do-db” on the slave instance can cause issues with not all queries being replicated. Instead, I recommend using “replicate-wild-do-table” so that all queries (regardless of construct) will be replicated in all scenarios.
  • 2. Only using one “binlog-do-db” line for multiple databases will cause replication to fail. Instead, if replicating multiple databases, have multiple “binlog-do-db” lines, one for each database.
  • 3. Don’t put master settings in the my.cnf configuration file. These settings won’t work in MySQL 5.5 or higher.
  • 4. Using a second window / session correctly for the initial database dump file creation to prevent locking from expiring. Get this wrong and your slave instance will be corrupt.

Steps to set up MySQL master slave replication


1. The first step is to set up the master database for replication. This can be done while the database server is running. You would edit the my.cnf file on the master database:

log-bin = /var/log/mysql/mysql-bin.log

where “DATABASE_NAME1″ and “DATABASE_NAME2″ are the names of the databases you plan to replicate.

2. Next, restart MySQL on the master server.

3. Then, on the master database, log into MySQL on the command line (mysql -p) and issue the following SQL queries:

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';

4. When you are ready to create the database dump of the master database(s), you will run the following commands. You *must* stay logged into this session, and do *not* quit or even issue another command:


If you have more than one database to replicate, you will need a separate window/session for each database that you stayed logged into. You would repeat these commands in each window for each database, staying logged in after issuing the commands.

5. In a second ssh window, you will log into MySQL again and run the following query and record the values:


It should look something like this (You want to record the File value and Position value):

| File             | Position  | Binlog_Do_DB       | Binlog_Ignore_DB |
| mysql-bin.000013 | 250789445 | DB_NAME1,DB_NAME2  |                  |
1 row in set (0.00 sec)

6. The next step is to dump the database(s) from the master server:

mysqldump -p --opt DATABASE_NAME1 >DATABASE_NAME1.sql
mysqldump -p --opt DATABASE_NAME2 >DATABASE_NAME2.sql

You will then want to transfer these files to the slave server, as you’ll use them to seed the slave databases later on.

7. Once you have the dump files, and you recorded the values of the master status, you can unlock the database by going back to the first window (and other window(s) for each database) that is still logged into MySQL and running:



1. Log into MySQL (mysql -p) and set up your databases:


2. In the my.cnf file on the slave server, add the following lines:


This will match any type of query run against the databases, and ensure they are fully replicated to the slave server.

3. Restart MySQL on the slave server.

4. Log back into MySQL (mysql -p) on the slave server and run the following queries. Make sure you stay logged into this session. You’ll need the values you recorded from the master database, as well as IP addresses and usernames/passwords:


Where “″ is the IP of the master server, and “slave_user” and “password” are the username and password of the MySQL user for replication. The “XX” values in the query are those you recorded in step 5 on the master server.

5. In a second window, import the databases:


6. Back in the first window (that is still logged into MySQL), you may now run the following MySQL queries:


That’s it. The slave server is now running, and should be replicating with the master server.

How to verify the slave server is in sync with the master

This is not an easy thing to do manually. There is no simple command that tells you everything is in sync.

Fortunately, the good folks at Percona have a toolkit that makes this easy to verify. You install it on the master server (simple perl Makefile.PL, make, and make install).

Once installed, you use a separate database (that should also be replicated) to track the sync status. I called ours “percona” and set it up on the master with:

create database percona;
CREATE TABLE checksums (
db             char(64)     NOT NULL,
tbl            char(64)     NOT NULL,
chunk          int          NOT NULL,
chunk_time     float            NULL,
chunk_index    varchar(200)     NULL,
lower_boundary text             NULL,
upper_boundary text             NULL,
this_crc       char(40)     NOT NULL,
this_cnt       int          NOT NULL,
master_crc     char(40)         NULL,
master_cnt     int              NULL,
ts             timestamp    NOT NULL,
PRIMARY KEY (db, tbl, chunk),
INDEX ts_db_tbl (ts, db, tbl)

I set this table up to be replicated along with our other databases.

Then, you simply run a command on the master server (via ssh, cron, etc…) to verify all is in sync:

pt-table-checksum --user=XXX --password=YYY --databases DATABASE_NAME1 --nocheck-replication-filters

This will give you an output and indicate if there are any differences between the databases. The column you are most concerned with is “DIFF”, which should be all zeroes if everything is in sync. You may see a number greater than zero in the “ERRORS” column. Many times this can be ignored depending on the details of the error.


Hopefully this helps you set up selective master-slave replication in MySQL without running into corruption or missing queries / data on the slave instance. I’ve found this process to work quite well for the replication set ups we have put in place. I’m sure there are many other ways to skin the cat, but the “gotchas” I listed above are items to consider no matter which plan you implement.