• PHP
  • Ruby on Rails
  • MySQL
  • Linux
    • SELINUX
    • Fedora
    • debian
  • Apache
  • nginx
  • AJAX
Albertech.net

Quick tuning MySQL performance

June 11, 2009 8:32 pm / Albertech.net

I’ve been working on tuning my Apache/MySQL/PHP configuration lately to see how to improve the performance. Lately, I’ve been noticing some crawlers (same IPs all the time) that have been ignoring my robots.txt file and hitting my server really hard with several page requests every second.

So, I decided to run some tests to see where I could speed up my website in the code.

One test was significant. I commented one update statement in my code and that sped up my page 3 times. Page display time went down from 0.15 second to 0.045 second! It seems as though my VMWARE server has slow write access times in comparison to reads, so this is a source of the cause. I decided to use this to my advantage.  So, basically create an array with the blacklisted IPs (but you can’t block them entirely since I need certain crawlers to index websites/etc. for searches)

$is_crawler = 0;
$crawler_array = array("[CRAWLER IP]","[CRAWLER IP2]");
foreach($crawler_array as $crawler_item)
{
if ($crawler_item == $current_ip)
{
$is_crawler = 1;
break;
}
}

In using this, only run the update query if the source IP isn’t a crawler… so something like this:

if ($is_crawler==0)
{

// Run MySQL Update command
} 

Other ways to speed up your MySQL performance.

  • Only use ORDER BY when necessary. Ordering is costly with large datasets
  • Joins are costly as well. Design a database table cache that has the union set if performance is a concern
  • Memcache is a viable option as well for pages that use many tables to generate the page.

Share this:

  • Facebook
  • Google
  • Twitter
  • Print
  • Email
Posted in: MySQL, PHP / Tagged: MySQL, performance, throttle, tuning

2 Thoughts on “Quick tuning MySQL performance”

  1. gyrolistic on June 12, 2009 at 1:53 pm said:

    Hi Al, what did you use to measure the page display time?

  2. admin on June 12, 2009 at 2:07 pm said:

    I used a script to figure out the page render time.

    http://albertech.net/2009/06/php-display-page-render-time/

Post Navigation

← Previous Post
Next Post →

Categories

  • AJAX
  • Android
  • Apache
  • Canon Cameras
  • Cloud
  • CMS
  • Computer Mods
  • Conferences
  • Deals
  • debian
  • Fedora
  • Flash
  • Frameworks
  • git
  • Hardware
  • HTML
  • IDE
  • iPhone
  • iPhone App Review
  • jQuery
  • Linux
  • Mac OS X
  • MySQL
  • nginx
  • PHP
  • portfolio
  • Puppet
  • Ruby on Rails
  • Script Reviews
  • SELINUX
  • Software
  • Software Review
  • SQL Server
  • statistics
  • Tech
  • Tomcat
  • Uncategorized
  • VMWARE
  • VPS
  • Windows
  • wordpress
  • Zend Framework

Blogroll

  • DragonAl Flickr
  • Dropbox – Free 2GB Account
  • James' Blog
  • Javascript Compressor
  • PHP Builder Community
  • PHP-Princess.net
  • Rubular – Regular Expression Validator
  • The Scale-Out Blog
  • Tiny MCE

Tags

activation AJAX android antec Apache AWS awstats canon coda codeigniter debian enclosure external free G1 install vmware tools Internet Explorer iphone 5 jquery Linux mx-1 MySQL office 2007 OSX photoshop PHP plugin plugins portfolio redesigned website review rewrite script security SELinux ssh tinymce tutorial upgrade VMWARE vmware server wordpress wordpress mu XSS zend framework
© Copyright 2013 Albertech.net
Infinity Theme by DesignCoral / WordPress
loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.