Here is a little bash script we knocked together to track down some malicious activity on a clients server.

Using a bit of awk etc to parse the log files we could quickly track down an IP address that was overloading the server and then take steps to block that person.

Here is the script:

#!/bin/bash

###### SETUP ############
LOG_FOLDER=/var/www/vhosts/domain.co.uk/statistics/logs
ACCESS_LOG=$LOG_FOLDER/access_log

HOW_MANY_ROWS=20000



######### FUNCTIONS ##############


function title() {
    echo "
---------------------------------
$@
---------------------------------
"
}

function urls_by_ip() {
    local IP=$1
    tail -5000 $ACCESS_LOG | awk -v ip=$IP ' $1 ~ ip {freq[$7]++} END {for (x in freq) {print freq[x], x}}' | sort -rn | head -20
}


function ip_addresses_by_user_agent(){
    local USERAGENT_STRING="$1"
    local TOP_20_IPS=&quot;<code>tail  -$HOW_MANY_ROWS $ACCESS_LOG | grep &quot;${USERAGENT_STRING}&quot;  | awk '{freq[$1]++} END {for (x in freq) {print freq[x], x}}' | sort -rn | head -20</code>&quot;
    echo &quot;$TOP_20_IPS&quot;
}

####### RUN REPORTS #############


title &quot;top 20 URLs&quot;
TOP_20_URLS=&quot;<code>tail -$HOW_MANY_ROWS $ACCESS_LOG | awk '{freq[$7]++} END {for (x in freq) {print freq[x], x}}' | sort -rn | head -20</code>&quot;
echo &quot;$TOP_20_URLS&quot;


title &quot;top 20 URLS excluding POST data&quot;
TOP_20_URLS_WITHOUT_POST=&quot;<code>tail  -$HOW_MANY_ROWS $ACCESS_LOG | awk -F&quot;[ ?]&quot; '{freq[$7]++} END {for (x in freq) {print freq[x], x}}' | sort -rn | head -20</code>&quot;
echo &quot;$TOP_20_URLS_WITHOUT_POST&quot;


title &quot;top 20 IPs&quot;
TOP_20_IPS=&quot;<code>tail  -$HOW_MANY_ROWS $ACCESS_LOG | awk '{freq[$1]++} END {for (x in freq) {print freq[x], x}}' | sort -rn | head -20</code>&quot;
echo &quot;$TOP_20_IPS&quot;

title &quot;top 20 user agents&quot;
TOP_20_USER_AGENTS=&quot;<code>tail  -$HOW_MANY_ROWS $ACCESS_LOG | cut -d\  -f12- | sort | uniq -c | sort -rn | head -20</code>&quot;
echo &quot;$TOP_20_USER_AGENTS&quot;


title &quot;IP Addresses for Top 3 User Agents&quot;

for ((I=1; I&lt;=3; I++))
do
    UA=&quot;<code>echo &quot;$TOP_20_USER_AGENTS&quot; | head -n $I | tail -n 1 | awk '{$1=&quot;&quot;; print $0}'</code>&quot;
    echo &quot;$UA&quot;
    echo &quot;~~~~~~~~~~~~~~~~~~&quot;
    ip_addresses_by_user_agent &quot;$UA&quot;
    echo &quot;
    &quot;
done

If you do any work at all on Magento performance optimisation with a view to making pages load faster then you must have come across block caching. To understand block caching you need to first of all understand that a Magento page is actually made up of a lot of nested chunks called blocks.

Blocks can have their HTML generated dynamically every time though it is also possible to make Magento cache the block HTML output so that it loads a LOT faster, especially with heavy and slow blocks such as best sellers etc.

When setting up block caching, you need to specify a cache key. If your cache key is not unique enough then there is a risk that you will display the wrong cache for a particular scenario.

For this reason I created a block caching helper with this method that can generate a very unique cache key based on the current block usage.
/app/code/local/EdmondsCommerce/Helper/Data.php

/**
     * Generate a nice unique block cache key
     *
     * @param Mage_Core_Block_Abstract $block
     * @return string
     */
    public function ecCacheKey(Mage_Core_Block_Abstract $block){
        $class = get_class($block);
        $template = $block->getTemplateFile();
        $md5 = md5(var_export($block->getData(), true));
        $params = trim(implode(",", $block->getRequest()->getParams()), ',');
        $category=($block->getCurrentCategory())?$block->getCurrentCategory()->getName():'no-category';
        $store_id = Mage::app()->getStore()->getId();
        return 'edmondscommerce-'. $params . '-'. $category . '-' . $store_id . '-' . $class.'-'.$template.'-'.$md5;
    }

You would use this in the following way:

In a block class, set up the following:

    public function _construct(){
        parent::_construct();
        $this->addData(array(
            'cache_lifetime' => 999999999,
            'cache_tags' => array(Mage_Catalog_Model_Product::CACHE_TAG, Mage_Catalog_Model_Category::CACHE_TAG),
            'cache_key' => Mage::helper('blockcaching')->ecCacheKey($this)
        ));

    }

PrestaShop has a really nice structure for handling all the CSS that will appear in a site. It starts by splitting the CSS so that the CSS is split up per modules. There is a global CSS sheet but this should only be used for things that genuinely appear on all pages.

Extending the CSS of a module in PrestaShop is easy. All you need to do is create a CSS file that matches the naming convention of the module you are looking to extend.

For example if you wanted to change the CSS of the “Wishlist block” module (a.k.a. blockwishlist) in your theme, you would create the file themes/mytheme/css/modules/blockwishlist/blockwishlist.css. This file will then be included on every page that the CSS from the blockwishlist module appears on.

This system is good because it minimises the amount of CSS the browser has to load and also splits the concern of CSS into separate files making it much easier to manage. It also makes adding to a modules CSS with in a theme much easier.

One problem with this method of handling CSS is that by default a PrestaShop site may want the browser to download ten or more individual CSS files which is quite a overhead. Fortunately since PrestaShop 1.4 it has supported CCS merging (referred to as CCC in the admin) which causes the CSS for a page to be merged into a single file on the server to prevent the client having to download tens of CSS files.

Many of our clients use osCommerce for their online store. osCommerce was very popular a few years ago and to date still powers a really large number of stores. Due to a variety of technical reasons, many osCommerce stores are still hosting on PHP version 5.2.

The latest version of PHP is 5.4 and 5.5 is due out fairly soon. Most hosting companies will now regard PHP 5.3 to be the minimum version they will support. By switching to PHP 5.3 they get better performance and better security. Unfortunately if you are running an osCommerce store and your hosting company decides to upgrade PHP then you might find that your entire store stops working.

The first instinct here is to get your store back online as quickly as possible and that is entirely understandable. These days though we really think that osCommerce store owners should be running on PHP 5.3. It is entirely possible to do this and does not necessarily entail a large amount of work depending on which contributions you are running.

The last release for PHP 5.2 was version 5.2.16 released back in 2010. In software terms that is really quite a long time ago and really underlines how much of an issue this is.

You can read about some of the performance gains of switching to PHP 5.3 or greater on this page.

If you would like some help getting your osCommerce store working on an up to date version of PHP then get in touch with Edmonds Commerce today.

Of course another option is to think about moving away from osCommerce and onto a more up to date platform such as osCommerce or OpenCart. If you are thinking of ugprading then 5.3 compatability will be a bonus. If you are not thinking of upgrading platform any time soon though you really do need to schedule in an upgrade to PHP 5.3 at the earliest opportunity.

The Magento profiler is useful when you are trying to discover what potential bottlenecks are in place in a specific Magento implementation that are slowing down the performance of the site. Often when doing this kind of analysis you might find that a particular block or extension is responsible for more than 70% of the total page load time. At this point you would choose to either optimise, cache or disable the block altogether to remove this performance bottleneck and improve page speed significantly.

If you have ever tried to use Magento’s built in profiler you will no doubt agree with Fabrizio’s comments that, when you have it working, the output is hard to read and not really very useful in terms of seeing what is actually going on.

To aid with this process and give us a nice colourful and friendly user interface to the Magento profiler, Fabrizio has built a Magento module that extends the default profiler and makes it a much more useful tool.

You can download the extension from git hub here.

Read the full blog story here.

If you would like some help optimising the performance of your Magento store, feel free to get in touch with Edmonds Commerce today and we can assist in getting the very best performance from your Magento web site.

If you are having performance issues with your Magento store and you are running on a dedicated or VPS server that you think should be up to the task of running your store properly but you continue to have performance problems then this post is for you.

Having decent server specification is only the first step on the road to having a high performance Magento store. Without proper configuration your server is not going to make the best use of its resources and that could make the difference of literally seconds or even tens of seconds of page load time.

The first and most important thing to check is that you are running a PHP opcode cacher such as APC. Opcode caching takes your PHP source code and compiles it to opcodes and then stores this in a cache. This opcode is actually what is run when people visit your store and the process of creating it, especially if you have a very large application with lots of file (like Magento), can be a real performance bottleneck. This problem is easily resolved by having APC installed and configured. If you are not sure, ring your hosting company and find out and if you don’t have it running, ask them to set it up for you.

The next thing to check is MySQL configuration. The standard MySQL configuration defaults were set when server hardware and memory was a tiny fraction of what it is today and that means that the configuration is generally way too sparse with allocation of memory for caching and other optimisations. Tweaking MySQL can be a little tricky, its definitely not something you should do if you are not sure, but it is well worth getting someone to optimise your MySQL configuration.

After that, the next major performance gain with Magento is to make proper use of block caching. Magento has a brilliant built in feature where every block (page section) can be cached so that next time someone visits the page, the logic used to generate that section of page (for example a best sellers list) does not have to be run, we simply redisplay the cached copy of that block’s HTML.

Beyond these three steps there are still many more things that can be done to improve the performance of your Magento store. If you would like professional help getting the best out of your server and Magento with a view to getting the lowest possible page load speeds then get in touch with Edmonds Commerce today.

Virtualbox is an amazing tool for testing and developing on various systems.

Now it’s even easier as there is a nicely organised repository of clean VirtualBox images you can download and get running with in a matter of minutes.

http://virtualboxes.org/

We are going to use them for some Magento performance optimisation testing on various platforms as we perfect our Magento Server Optimisation skills and try to benchmark some new ideas.

Just came across this forum post of MySQL that looks to be a gold mine of information related to performance tuning MySQL.

For reference I am copying and pasting the full thing here though all credit goes to the above.

Book: High Performance MySQL (2nd Edition)
http://www.highperfmysql.com/
MySQL Performance Tuning – Best Practices:
http://jpipes.com/presentations/perf_tuning_best_practices.pdf

MySQL Index Tuning and Coding Techniques for Optimal Performance:
http://jpipes.com/presentations/index_coding_optimization.pdf

Web Performance and Scalability with MySQL:
http://develooper.com/talks/

PHP Applications: 120 Performance Tuning screws for MySQL
http://blog.ulf-wendel.de/?p=268
http://blog.ulf-wendel.de/?p=163

MySQL Server Variables
http://forge.mysql.com/wiki/ServerVariables

MySQL Server Variables – SQL layer or Storage Engine specific

http://www.mysqlperformanceblog.com/2006/06/08/mysql-server-variables-sql-layer-or-storage-engine-specific/

“Show profile” + “Information_schema.profiling”
http://blogs.mysql.com/peterg/2008/11/06/show-profile-information_schemaprofiling/
PeterZ presentations:
http://www.mysqlperformanceblog.com/mysql-performance-presentations/

Using MMM to ALTER huge tables
http://www.mysqlperformanceblog.com/2008/03/27/using-mmm-to-alter-huge-tables/

MySQL File System Fragmentation Benchmarks
http://www.mysqlperformanceblog.com/2008/03/21/mysql-file-system-fragmentation-benchmarks/
http://www.mysqlperformanceblog.com/2008/03/18/working-with-many-files-and-file-system-fragmentation/

Finding/killing long running InnoDB transactions with Events
http://www.markleith.co.uk/?p=730

Using the event scheduler to purge the process list
http://datacharmer.blogspot.com/2008/10/using-event-scheduler-to-purge-process.html

`kill_run_aways` Stored Proc
http://forge.mysql.com/tools/tool.php?id=106

Yoshinori: Tracking long running transactions in MySQL
http://yoshinorimatsunobu.blogspot.com/2011/04/tracking-long-running-transactions-in.html

How to debug long-running transactions in MySQL
http://www.mysqlperformanceblog.com/2011/03/08/how-to-debug-long-running-transactions-in-mysql/

If you need to update a large number of rows on a single table then your first reaction may be to write a loop that updates one row at a time.

Of course if the table is large then this can result in a very large number of SQL queries.

Taking a bit of inspiration from this post I have put together this simple PHP function that will allow you to update as many rows as you want.

The trade off is basically memory usage as you build up a big array of row update information preparing for the batch. For this reason you may want to tune the number of rows you update per query.

function bulkUpdateSingleColumn($table, $id_column, $update_column, array &$idstovals){
        $sql = "update $table set $update_column = CASE $id_column ";
        foreach($idstovals as $id=>$val){
            $sql .= " WHEN '$id' THEN '$val' \n";
        }
        $sql .= " END 
        WHERE $id_column in (" . implode(',', array_keys($idstovals)) . ")";
//debugging info
        echo '<small>'.$sql.'</small>';
        $idstovals=array();        
        db_query($sql);       
        done();        
    }

If you are looking to gain the most out of your Magento hosting and to optimise your Magento hosting stack, here are a few top tips that you can try.

1. Ensure Caching is Enabled
It may sound obvious, but if you do not have caching enabled then the performance of your Magento store is going to suffer massively.

Generally whilst developing, caching will need to be at least partially disabled to ensure that changes are reflected. Unfortunately some times when a site goes live, enabling caching is a forgotten step.

2. Upgrade your Magento
More recent versions of Magento offer much better performance than earlier versions. If you are running a Magento version older than 1.4 then you should definitely look to upgrade as soon as possible as there are vast improvements to be made in terms of performance.

3. Disable or Improve Bad Extensions
Some custom modules or forum inspired template hacks can be functionally fine but absolutely terrible in terms of performance. Fancy a best sellers list? Lets calculate the top ten products on every single page load and display the results in the right hand column!

4. Use Block Caching
In order to deal with the above scenario, it is fairly easy to use block caching – something built into Magento as standard – to cache the html output of a block and serve from the cache rather than doing lots of heavy processing on an unnecessarily regular basis.

5. Move Hosts
Some times you just have to accept that your hardware or hosting is no up to scratch. A well optimised LAMP stack can out perform an unoptimised standard stack by a surprising degree. Combine that with some choice extensions such as opcode caching etc and the performance difference can be really quite significant – and without any hardware upgrades. If you are running a VPS or dedicated server then it is possible to optimise the stack yourselves, or hire an agency like us to help you with this. Alternatively you can try to move hosts to a more specialist Magento hosting company that will provide this service as standard.