Problems with CloudFlare

Problems with CloudFlare

By Strictly-Software

Recently I moved a couple of my sites behind the free Cloudflare proxy option and set the DNS so it pointed to their server rather than the 123reg.co.uk ones I had been using.

However before I did this I tested out whether there was much difference between the sites.

1. I set to use Cloudflare and WP Super Cache.
2. I used WP Super Cache, Widget Cache and WP Minify.

I actually found that that the 2nd set-up gave me better results. Why I don't know.

However in the end due to all the spam and apparent blocking Cloudflare claimed to be able to do I set the other site up behind it.

However after a while I noticed a few things you might want to be aware of

1. I had a number of email scripts that sent out thousands of emails and I used a

set_time_limit(5000);

at the top of the file to ensure it didn't time out by the Virtual Servers standard 30 seconds.

Also and in-between each email (which I appended to a log file) I did a wait command with:

sleep(2);

So that I didn't overkill the server.

However you should be aware that when you use set_time_limit and then use functions like sleep or file_put_contents or file_get_contents the time it takes to wait, access files and retrieve data is not included in the time limit.

Also as I was using Cloudflare, my PHP script which is web based so I can easily call it by hand if I need to, was using the standard domain e.g http://www.mysite.com/mysendemailjob.php.

However Cloudflare it seems has it's own timeout limit which overrides anything you set in Apache or in your PHP file of about 60 seconds.

I noticed this because my script kept bombing out after around 60 seconds and returned a Cloudflare 524 error which you can read about here.




So to get round this problem I used the "direct" domain method they set as default to bypass CloudFlare.

I have obviously changed mine as you should to for security sake but once changed to something else when I ran the url: http://www.mysite.com/mysendemailjob.php it didn't bomb out any-more and carried on until the end.

Another thing you have to be careful about CloudFlare is if you have spent ages filling your IP Table up with IP addresses that you want banned due to spam, hack attempts or just over use.

Because all IP addresses in the Apache Access Log are now CloudFlare addresses these won't be used and you are now relying on ClouldFlare's own security measures to block dangerous IP's.

The same goes for your .htaccess file. If you have banned a whole countries range say China or Russia (biggest hackers on the earth - apart from the NSA of course) then these ranges won't mean jack as the user from China will be going through a CloudFlare proxy IP address to your site so any IP you had banned him from will now be useless.

The only thing left to do is ban by user-agent, blank agents is a good one and so are short ones (less than 10 characters as they are usually jibberish).

I ban most of the standard HTTP libraries like CURL, WGET, WIN HTTP, Snoopy and so on as most script kiddies download a library, and don't even bother changing the user-agent before crawling and spamming. Therefore if someone isn't going to tell me who they really are then they can get a 403!

So they are a few things to watch out for with Cloudflare.

I know you can get modules that replace the Cloudflare IP with the original users IP but if you are on an old Debian Lenny box then they don't have support for that.

They must be supplying x-forwarded-for or other headers as when I did a scan using the bypass URL I got back my original IP but with a www.mysite.com scan I got back CloudFlare IPS e.g 104.28.25.11 etc.

The only thing you can do if you cannot take a modern module and reverse engineer it to older code is use the WordPress CloudFlare plugin that lets you get real Akismet IP addresses so you can still ban them.

It is a pain and one I am debating on whether to return to the days of before Cloudflare where my own security measures meant I banned over 50% of traffic and my server bills didn't go up and up.

"CloudFlare is supposed to save me bandwidth but ever since I have installed it although it claims it has saved me loads my Rackspace bill for bandwidth use has gone up and up!"

So just be careful when using Cloudflare it may seem like a magic tool but all those rocketscripts they add to your code are just "async" attributes and you can get many a plugin to minify your source and compress it on the server without using PHP to do so.

The choice is yours but be warned!

Accessing your computers external IP address from your computer without using a browser

Access your computers external IP address from your desktop without using a browser

Following on from yesterdays blog post about what can happen if you get given a new IP address and don't realise it, you might want a quick way to check your external IP address from your computer without having to open an Internet browser.

There are many "What is my IP address" sites about that show you your IP address plus other request headers such as the user-agent but you might want a quick way of seeing your external IP without having to open a browser first.

If you are using a LINUX computer it's pretty easy to use CURL or WGET to write a small script to scrape an IP checker page and return the HTML contents.

For instance in a command prompt this will return you the IP address using CURL by scraping the contents of icanhazip.com.

This site is good because it outputs the computers IP address that's accessing the URL in plain text so it means you don't have to do any reformatting at all.

curl icanhazip.com

However if you are on a Windows computer there is no simple way of getting your external IP address (the IP address your computer is seen on the outside world) without either installing Windows versions of CURL or WGET first or writing a script to do it for you using Microsoft objects.

Of course it would be nice if you could just use ipconfig from the command prompt to show your external address as well as your internal network details but unfortunately you can't do that.

As you're connected to the Internet through your router your PC isn't directly connected to the Internet.

Therefore there is no easy way you can get the IP address your ISP has assigned to your computer without seeing it from another computer on the Internet.

Therefore you can either use one of the many IP checker tools like whatismyip.com or icanhazip.com to get the details. Or you can even just click this link to search for "what is my IP address" and get Google to show you your IP address above the results.

However if you do want to do it without a browser you can write a simple VBS script to do it for you and then you can access your external IP from your desktop with a simple double click of the mouse.

How to make a VBS Script to get your computers external IP address.

1. Open notepad.
2. Copy and paste the following VBS code into a new notepad window. 
3. Save the file as "whatismyip.vbs" on to your desktop.
4. To view your IP address just double click the file icon and a Windows message box will open and show you the IP address.

The script is very simple and all it does is scrape the plain text contents of the webpage at icanhazip.com and output it in a pop-up - simples!

Option Explicit
Dim objHTTP : Set objHTTP = WScript.CreateObject("MSXML2.ServerXmlHttp")
objHTTP.Open "GET", "http://icanhazip.com", False
objHTTP.Send
Wscript.Echo objHTTP.ResponseText
Set objHTTP  = Nothing

If you really want to use this from the command line you can do it by following these steps.

1. Open a command prompt.
2. Type "cscript " leaving a space afterwards (and without the quotes!).
3. Drag the whatismyip.vbs file to the command prompt so that you have a space between cscript and the path of the file e.g C:\Documents and Settings\myname>cscript "C:\Documents and Settings\myname\Desktop\whatismyip.vbs"
4. Hit Enter.
5. The IP address will appear after some guff about the Windows Script Host Version.

The output should look something like this:

C:\Documents and Settings\
myname >cscript "C:\Documents and Settings\myname\Desktop\whatismyip.vbs"
Microsoft (R) Windows Script Host Version 5.7
Copyright (C) Microsoft Corporation. All rights reserved.
89.42.212.239

So there you go, a LINUX and WINDOWS way of accessing your external IP address from your desktop without having to open Chrome or FireFox.

PayPal and Instant Payment Notification (IPN) Messages

Blocking bad bots and issues with PayPal's IPN service

If you are someone like me who likes to ban as much bad BOT traffic as possible then you might have added a rule in your .htaccess or httpd.ini file that blocks blank user-agents. Something like this.

# Block blank or very short user-agents. If they cannot be bothered to tell me who they are or provide gibberish then they are not welcome!                                        
RewriteCond %{HTTP_USER_AGENT} ^(?:-?|[a-z1-9\-\_]{1,10})$ [NC]
RewriteRule .* - [F,L]


This is because many bad bots wont supply a user-agent because of the following reasons:

1. They think websites will check for user-agent strings and by not supplying one they will slip under the radar.
2. The people crawling are using simple methods e.g they have just grabbed bits of code off the web that don't actually set a user-agent and used them as is.
3. They are using simple HTTP functions like file_get_contents('http://www.mysite.com'); to get the HTTP response and not passing in any context data such as user-agent, timeouts, headers etc.
4. They are making requests from websites that use CURL or WGet from places like WebMin to test Cron jobs or run crawl scripts and not supplying the user-agent parameters which are available.

Obviously it's always best to supply a user-agent as it identifies (or masks) your real code in any log file and prevents you getting blocked by people who ban blank user-agent strings.

However there come times when even big companies like PayPal send information without user-agent details.

One instance of this is the IPN notification messages they send (if set up) on sites that are using PayPal as their payment gateway.

When a customer makes a payment or cancels a subscription then PayPal will send a POST request to a special page on the website that then checks the validity of the request to make sure it comes from PayPal. If so it sends back the data along with a special key so that PayPal can confirm they made the request and the website owner is sure it's not a spoofer trying to get free items.

Once this handshake is done the relevant IPN data such as payment information can be obtained from the PayPal IPN Response.

However today I received an email in my inbox that said.

Dear Rob Reid,

Please check your server that handles PayPal Instant Payment Notifications (IPN). Instant Payment Notifications sent to the following URL(s) are failing:

http://www.mysite.com/?myIPN_paypal_handler

If you do not recognize this URL, you may be using a service provider that is using IPN on your behalf. Please contact your service provider with the above information. If this problem continues, IPNs may be disabled for your account. 

Thank you for your prompt attention to this issue.


Yours sincerely, 

PayPal 

After checking my PayPal account and the IPN history page I could see that a message was stuck in the queue an being re-sent every so often:

Message ID1W03166E61323432D

Date/time created18/08/2012 09:01 BST

Original/ResentOriginal

Latest delivery attempt date/time---

Notification URLhttp://www.mysite.com/?myIPN_paypal_handler

HTTP response codeWhat's this?403

Delivery statusResending

No. of retries14

Transaction ID0U508347GT8609TY0

IPN typeTransaction made

Therefore I checked my website logs for any instance of this request and I found that they were all returning 403 / Forbidden status codes.



66.211.170.66 - - [18/Aug/2012:08:54:47 +0000] "POST /?myIPN_paypal_handler HTTP/1.0" 403 417 "-" "-" 0/55005
66.211.170.66 - - [18/Aug/2012:09:27:11 +0000] "POST /?myIPN_paypal_handler HTTP/1.0" 403 417 "-" "-" 0/55010

As you can see - no user-agent!

Therefore I changed my .htaccess file to allow blank user-agents and re-sent the IPN message from the PayPal console and low and behold it was allowed.

Obviously if you are not using PayPal and their IPN system or not using any Payment service then you might consider to keep blocking blank agents due to the amount of bandwidth you will save (lots from my own stats).

Or you should check that payment systems own requests to your site to ensure you are not blocking them from doing anything important like sending payment information to your website.

Just be-warned in case you get a similar email from PayPal.

How to install WebMin on LINUX

How to install WebMin on LINUX

If you are from a Windows background and used to graphical interfaces then moving to a LINUX system can be quite a challenge due to the fact that most techies like to use commands from the console to carry out their work.

Whilst it is a good opportunity to learn some of the commands when you first rent a virtual server from Rackspace or buy your own box then you want to get your sites up and running quickly without having to read a lot of information first.

Therefore if you don't have the time to learn all the LINUX commands installing WebMin on your system is a quick way of providing you with a graphical interface to allow you to edit files and stop and start services like the Apache webserver or MySQL database server, as well as managing these services and configuring settings through a visual interface. You will need to have Java installed to use parts of WebMin.

If you want to learn how to use the console for carrying out your work then a good list of Linux applications and commands can be found here: Linux Commands and you should read some of the following articles I have written specifically about performance turning and managing a system such as Wordpress on a LINUX based system.

The Wordpress Survival Guide Part 1
The Wordpress Survival Guide Part 2
Performance Tuning Tools for MySQL
Using Host Headers to setup a test site on LINUX
Problems with LINUX Apache and PHP
Debugging Memory Issues on Wordpress

A quick cheat sheet of the most popular commands I find myself constantly using from the command line through a tool like PUTTY are below. Make a copy and save them to your desktop for quick and easy access.

Command	Details
date	Show the current date and time on the server
cd	change drive e.g cd /var (go to the var directory)
cd ../	go back up one directory
cd ../../	go back up two directories
ls	list out the contents of a directory
whoami	see who you are logged in as
su - [username]	Assume the permissions of the specified user
sudo [command]	Run a command as root but stay as the user you are logged in as
top	Show the current running processes and server load
top -d .2	Show the current running processes with .2 second refresh
tail -f access_log	View the most current entries in the sites access log
grep "61.252.14.247" access_log | tail	View the most current entries in the sites access log for a certain IP address
netstat -ta	Show all current connections to the server
grep "27/Feb/2012:" access_log | sed 's/ - -.*//' | sort | uniq -c | sort -nr | less	View the IP's that appear in your access log the most for a certain date ordered by the most frequent first.
/etc/init.d/apache2 restart	Restart Apache
apache2ctl configtest	Test the Apache configuration for configuration errors
/etc/init.d/mysql restart	Restart MySQL
wget [URL]	Remotely access, load and save a file to the current directory
chmod 777 [filepath]	Grant full read/write/delete permission to everyone to a file or folder
chmod +x [filepath]	Grant execute permission to a script
reboot	Reboot the server

How to install WebMin on a LINUX

Back to WebMin, to install WebMin on a LINUX based server you should do the following.

1. Install PUTTY if you haven't already.
2. Connect to your server through SSH using PUTTY.
3. Download the WebMin file with the following command:
wget http://prdownloads.sourceforge.net/webadmin/webmin_1.580_all.deb

4. Run the following command: dpkg --install webmin_1.580_all.deb

You should have now installed WebMin to /usr/share/webmin.

The administration username will be set to root and the password will be set to your current root password.

You should now be able to login to Webmin by accessing your hostname/IP on the port 10000.

For example e.g if your hostname is myhost.some-site.com then use http://myhost.some-site.com:10000/.

If you server is running in SSL mode (which it should) then it will initially give you a "Bad Request" error and ask you to try a secure URL instead e.g https://myhost.some-site.com:10000/

The WebMin options will appear as a tab to the right of the VirtualMin tab in the top left of the screen.

Performance Tuning Tools for MySQL on LINUX

How to Performance Tune MySQL for Wordpress

Since I have been working a lot with Wordpress, PHP and Apache I have had to get used to the limitations of MySQL and the applications that are available to connect to MySQL databases such as Navicat or PHPMyAdmin.

As well as all the missing DML such as CTE's I really miss the very useful Data Management Views (DMV's) that has as they make optimising a database very easy and I have built up a large collection of SQL Server Performance Reports and tools to help me debug database performance issues.

Since working with Wordpress I have seen a lot of Plugins that use very poor SQL techniques and it seems most plugin authors that create their own tables in the Wordpress database don't even think about indexes that could increase performance of some very badly written SQL.

Having to sift through the slow query log and then run an EXPLAIN on each query is a time consuming job whereas setting up a scheduled job to monitor missing indexes and then list all suggestions is very easy to do in MSSQL with their Data Management Views (DMV's) that hold information about missing indexes, high cost queries, cached query plan re-use and much more.


My SQL Optimisation for Wordpress and LINUX

There are a number of tools available for performance tuning MySQL and I have listed a few below.


MySQLTuner.pl

MySQLTuner is a Perl script that analyzes your MySQL performance and, based on the statistics it gathers, returns recommendations based on the ShowVariables SQL that you can adjust to increase performance.

One of the good things about having root access to your own LINUX server (VPS or dedicated) is the ability to SSH in and then load and install programs remotely with a few lines of code from the command prompt.

To load and run MySQLTuner.pl on your server do the following:

Change the directory to your program folder e.g:

cd /usr/bin

Load the tool in remotely with a WGET command e.g:

wget http://mysqltuner.pl/mysqltuner.pl

Change the permissions to make it executable e.g:


chmod +x mysqltuner.pl

Run the tool e.g

myhost:/usr/bin# /usr/bin/mysqltuner.pl

>>  MySQLTuner 1.2.0 - Major Hayden 
>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
>>  Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.51a-24+lenny5-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB +Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 419M (Tables: 98)
[!!] Total fragmented tables: 9

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 3d 3h 0m 33s (5M q [22.010 qps], 58K conn, TX: 102B, RX: 1B)
[--] Reads / Writes: 87% / 13%
[--] Total buffers: 314.0M global + 2.6M per thread (100 max threads)
[OK] Maximum possible memory usage: 576.5M (55% of installed RAM)
[OK] Slow queries: 1% (70K/5M)
[OK] Highest usage of available connections: 16% (16/100)
[OK] Key buffer size / total MyISAM indexes: 64.0M/196.8M
[OK] Key buffer hit rate: 100.0% (18B cached / 5M reads)
[OK] Query cache efficiency: 83.1% (4M cached / 5M selects)
[!!] Query cache prunes per day: 61605
[OK] Sorts requiring temporary tables: 0% (207 temp sorts / 356K sorts)
[!!] Joins performed without indexes: 35147
[!!] Temporary tables created on disk: 44% (309K on disk / 690K total)
[OK] Thread cache hit rate: 99% (329 created / 58K connections)
[!!] Table cache hit rate: 13% (191 open / 1K opened)
[OK] Open file limit used: 22% (231/1K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (> 40M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    tmp_table_size (> 200M)
    max_heap_table_size (> 200M)
    table_cache (> 200)

You should carefully read the output, especially the recommendations at the end.

It shows exactly which variables you could adjust in the [mysqld] section of your my.cnf (on Debian and Ubuntu the full path is /etc/mysql/my.cnf). but be careful as this is only advice and you should find out as much as you can before changing core configuration settings.

Whenever you change your my.cnf file, make sure that you restart MySQL with this command (or use your GUI)

/etc/init.d/mysql restart


You can then run MySQLTuner again to see if it has further recommendations to improve the MySQL performance.

Another tool of a similar nature is the MySQLReport tool which can be found at http://hackmysql.com.

Information can be found here about how to read and analyse the report that is produces from this link http://hackmysql.com/mysqlreportguide.

You can load it up remotely and build it on your server in a similar way making use of an HTTP tool like CURL or WGET etc:

wget hackmysql.com/scripts/mysqlreport

--2011-11-13 02:58:47--  http://hackmysql.com/scripts/mysqlreport
Resolving hackmysql.com... 64.13.232.157
Connecting to hackmysql.com|64.13.232.157|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 38873 (38K) [application/x-perl]
Saving to: `mysqlreport'

100%[======================================>] 38,873      --.-K/s   in 0.1s

2011-11-13 02:58:47 (254 KB/s) - `mysqlreport' saved [38873/38873] 

Once loaded give the newly installed file execute permission with the following command

chmod +x mysqlreport1.pl

You then call it by passing through the details of the system you want to analyse e.g:

mysqlreport --user root --host localhost --password mypsw100

MySQL 5.0.51a-24+lenny5  uptime 3 3:21:16       Sun Nov 13 03:04:11 2011

__ Key _________________________________________________________________
Buffer used    52.33M of  64.00M  %Used:  81.76
  Current      61.15M            %Usage:  95.55
Write hit      99.96%
Read hit       99.97%

__ Questions ___________________________________________________________
Total           5.96M    22.0/s
  QC Hits       4.60M    17.0/s  %Total:  77.25
  DMS           1.07M     3.9/s           17.97
  Com_        226.52k     0.8/s            3.80
  COM_QUIT     58.22k     0.2/s            0.98
  +Unknown        408     0.0/s            0.01
Slow (2)       70.43k     0.3/s            1.18  %DMS:   6.58  Log:  ON
DMS             1.07M     3.9/s           17.97
  SELECT      935.60k     3.4/s           15.70         87.35
  UPDATE      127.41k     0.5/s            2.14         11.90
  INSERT        7.63k     0.0/s            0.13          0.71
  DELETE          450     0.0/s            0.01          0.04
  REPLACE           0       0/s            0.00          0.00
Com_          226.52k     0.8/s            3.80
  set_option  169.55k     0.6/s            2.84
  change_db    56.71k     0.2/s            0.95
  optimize         91     0.0/s            0.00

__ SELECT and Sort _____________________________________________________
Scan           68.94k     0.3/s %SELECT:   7.37
Range          42.09k     0.2/s            4.50
Full join      35.21k     0.1/s            3.76
Range check         0       0/s            0.00
Full rng join       0       0/s            0.00
Sort scan     300.33k     1.1/s
Sort range     56.97k     0.2/s
Sort mrg pass     207     0.0/s

__ Query Cache _________________________________________________________
Memory usage   36.03M of  40.00M  %Used:  90.07
Block Fragmnt  10.04%
Hits            4.60M    17.0/s
Inserts       842.73k     3.1/s
Insrt:Prune    4.33:1     2.4/s
Hit:Insert     5.46:1

__ Table Locks _________________________________________________________
Waited          1.44k     0.0/s  %Total:   0.08
Immediate       1.77M     6.5/s

__ Tables ______________________________________________________________
Open              200 of  200    %Cache: 100.00
Opened          1.54k     0.0/s

__ Connections _________________________________________________________
Max used           16 of  100      %Max:  16.00
Total          58.52k     0.2/s

__ Created Temp ________________________________________________________
Disk table    310.39k     1.1/s
Table         381.99k     1.4/s    Size: 200.0M
File              431     0.0/s

__ Threads _____________________________________________________________
Running             1 of    1
Cached              7 of    8      %Hit:  99.44
Created           329     0.0/s
Slow                3     0.0/s

__ Aborted _____________________________________________________________
Clients           588     0.0/s
Connects           17     0.0/s

__ Bytes _______________________________________________________________
Sent          102.63G  378.3k/s
Received        1.95G    7.2k/s

__ InnoDB Buffer Pool __________________________________________________
Usage               0 of       0  %Used:   0.00
Read hit        0.00%
Pages
  Free              0            %Total:   0.00
  Data              0                      0.00 %Drty:   0.00
  Misc              0                      0.00
  Latched           0                      0.00
Reads               0       0/s
  From file         0       0/s            0.00
  Ahead Rnd         0       0/s
  Ahead Sql         0       0/s
Writes              0       0/s
Flushes             0       0/s
Wait Free           0       0/s

__ InnoDB Lock _________________________________________________________
Waits               0       0/s
Current             0
Time acquiring
  Total             0 ms
  Average           0 ms
  Max               0 ms

__ InnoDB Data, Pages, Rows ____________________________________________
Data
  Reads             0       0/s
  Writes            0       0/s
  fsync             0       0/s
  Pending
    Reads           0
    Writes          0
    fsync           0

Pages
  Created           0       0/s
  Read              0       0/s
  Written           0       0/s

Rows
  Deleted           0       0/s
  Inserted          0       0/s
  Read              0       0/s
  Updated           0       0/s

In a similar way you will need to know how to analyse each section to make the neccessary changes and you can find out what each calculation result means here: http://hackmysql.com/mysqlreportdoc

Another good tool to use to find out what is going on in your database is MyTop. This is an application like Top that shows the current processes running on a server but for a MySQL database rather than the whole server. It does this by analysing the same data that SHOW PROCESSLIST would output.

You can obtain the code from http://jeremy.zawodny.com/mysql/mytop/

Once loaded you call it from your command prompt like the other commands passing in the host, database and password as well as a refresh rate if you require it. I think it defaults to 5 seconds but I like to use 1 second which you can change by supplying a parameter for --s parameter e.g:

mytop --user root --password mypsw --db myDB --s 1

The results look like this with a header that shows how many queries have run per second as well as slow queries from the slow quert log.

MySQL on localhost (5.0.51a-24+lenny5-log)                                 up 3+03:48:10 [03:31:05]
 Queries: 5.7M   qps:   22 Slow:     0.0         Se/In/Up/De(%):    77/00/00/00
             qps now:    2 Slow qps: 0.0  Threads:    3 (   3/   5) 00/00/00/00
 Key Efficiency: 100.0%  Bps in/out:   0.0/  0.6   Now in/out:  41.2/10.0k

      Id      User         Host/IP         DB      Time    Cmd Query or State
      --      ----         -------         --      ----    --- ----------
   58766      root       localhost strictly         0  Query show full processlist
   58772 darkpolit       localhost strictly         1  Query select CONCAT('http://www.strictly         
   58771 strictly        localhost strictly         4  Query select CONCAT('http://www.strictly         

Another method for those who don't have direct access into their LINUX server but only a control panel like CPANEL and use Wordpress for their website is my own Wordpress Plugin - the Strictly System Checker Plugin.

The Strictly System Checker is a Wordpress plugin that is designed to allow webmasters to monitor their site at regular intervals throughout the day and to be notified if the site goes down or experiences database problems or high server loads.

This plugin was not designed to be a replacement for professional server monitoring tools however it is a nice easy to use system that can aid webmasters in monitoring their Wordpress site as well as notifying the right person whenever the site is down or running into performance problems.

How it works

• A CRON / WebCron job initiates an HTTP request to check whether the site can be accessed.
• The system will check for the Error establishing a database connection error message as well as searching for an optional piece of text which can help indicate whether the page has loaded correctly.
• If the error is found or the text cannot be found then a connection to the database is attempted.
• If successful a CHECK and REPAIR is carried out on any tables that maybe corrupted.
• An option exists to also check for fragmented tables and an OPTIMIZE command is carried out to fix any found.
• An SQL report is carried out to report on some key performance indicators such as the number of connections, queries, reads, writes and more.
• A report is carried out on the webserver to look at the current server load average and if it's above a specified threshold a report to the site administrator can be triggered.
• A similar check is carried out on the database to ensure that there are no slow running queries or the connection limit hasn't been reached.
• If problems are found an email is then sent to the site administrator with details of the report.

An example of a report that can be emailed to system administrators or viewed from the admin part of the website is below.

System Report: 2011-11-13 04:07:07

Initiating System Report...
Initiating an HTTP request to http://www.strictly-software.com
The HTTP request to http://www.strictly-software.com took 0 second(s) to respond and returned a status code of 200
The specified search text [read more] was found within the HTTP response
The server load is currently 0.48
The server load is okay
MySQL has been running for: 26 days 4 hours 18 mins 48 secs
Total Connections: 467253 - Aborted: 0 - Connections Per Hour 743
Total Queries: 21592142 - Queries / Per Hour 34365
Joins without indexes: 0 - Joins without indexes Per Hour 0
Total Reads: 4819507 (88%) - Total Writes 630029 (12%)
The system is currently configured to accept a maximum of 100 database connections
At the time of reporting the database was running 3 query
The current database load is 3%
The database load is okay
Initiating a check for fragmented tables and indexes
Optimized table: wp_options
Optimized table: wp_postmeta
Completed check for fragmented tables and indexes
The system report has completed all its tests successfully.


Report Completed At 2011-11-13 04:07:07

Strictly Software Plugins for Wordpress

So whilst I feel the tools available for MySQL performance monitoring are lacking when compared with those available with SQL 2008 there are some that will enable you to get the job done. Hopefully this article has been helpful and if you have any tools of your own please add them to t the comment section.