Diagnosing a sick website getting 500,000 to 1 million views a day

So today I had a customer that had some woes. I don’t even think they were aware they were getting 504’s but I had to come up with some novel ways to

A) show them where teh failure happened
B) Show them the failed pages that failed to load (i.e. get a 504 gateway timeout)
C) show them the number of requests and how they changed based on the day of the outage, and a ‘regular normal’ day.
D) show them specific type of pages which are failing to give better idea of where the failure was

In this case a lot of the failures were .html pages, so it could be a cache was being triggered too much, it could be that their application was really inefficient, or in many cases, were catalog search requests which no doubt would scratch the db pretty nastily if the database or the query wasn’t refactored or designed with scalability in mind.

With all that in mind I explained to the customer, even the most worrysome (or woesome) of applications and frameworks, and even the most grizzly of expensive MySQL queries can be combatted, simply by a more adaptable or advanced cache mechanism. Anyway, all of that out of the way, I said to them it’s important to understand the nature of the problem with the application, since in their case were getting a load average of over 600.

I don’t have their solution,. I have the solution to showing them the problem. Enter the sysad, blazing armour, etc etc. Well, thats the way it’s _supposed_ to happen !

cat /var/log/httpd/access_log | grep '26/Mar' | grep 'HTTP/1.1" 50' | wc -l
26081

cat /var/log/httpd/access_log | grep '27/Mar' | grep 'HTTP/1.1" 50' | wc -l
2

So we can see 504’s the day before wasn’t an issue, but how many requests did the site get for each day comparatively?

[[email protected] httpd]# cat access_log | grep '26/Mar' | wc -l
437598
[[email protected] httpd]# cat access_log | grep '25/Mar' | wc -l
339445

The box received 25% more traffic, but even based from the figures in the SAR, cpuload had gone up 1500% beyond what the 32 cores on their server could do. Crazy. It must be because requests are getting queued or rather ‘building up’, and there are so many requests reaching apache, hitting the request for mysql, that either mysql formed a bottleneck and might need more memory, or, at this scale, a larger or smaller (probably larger) sized packet for the request, this can speed up significantly how fast the memory bucket fills and empties, and request queue gets killed. Allowing it to build up is going to be a disaster, because it will mean not just slow queries get a 504 gateway timeout, but also normal requests to regular html pages too (or even cached pages), since at that stage the cpu is completely overwhelmed.

I wrote a script,

to find a majority of the 504’s for the 26 Mar you can use this piece:

cat access_log | grep '26/Mar' | grep 'HTTP/1.1" 50' | awk {'print $7'}

to generate a unique list for your developer/team of pages which failed you can run:

cat access_log | grep '26/Mar' | grep 'HTTP/1.1" 50' | awk {'print $7'} | sort | uniq

To be honest. In the simplicity of this post somewhere, is a stroke of inspiration (if not ingenuity). Also it’s kind of hacky and crap, but, it does work and it is effective for doing the job.

AND that is What counts.

Fixing phpmyadmin, Connection for controluser as defined in your configuration failed.

This happens when the phpmyadmin package is installed, but for some reason or another the automation the package manager and phpmyadmin have for setting up the phpmyadmin user, and phpmyadmin database doesn’t properly use the table schema from /usr/share. Here is the process of fixing this error for those that get it.

Create a database called phpmyadmin

create database phpmyadmin;

You can actually call the database anything as long as you remember what you changed it to later.

Create a database user

MariaDB [(none)]> GRANT ALL PRIVILEGES ON phpmyadmin.* to [email protected] identified by 'AVERYSECUREpasswordgoeshere98123123sdabcsd123' ;
Query OK, 0 rows affected (0.00 sec)

Locate the create_tables.sql file copied by the package manager (or zip if installing from source)

[[email protected] phpMyAdmin]# find /usr/share | grep create_table
/usr/share/phpMyAdmin/sql/create_tables.sql
/usr/share/phpMyAdmin/sql/create_tables_drizzle.sql
/usr/share/phpMyAdmin/libraries/display_create_table.lib.php
/usr/share/phpMyAdmin/test/libraries/PMA_display_create_table_test.php

Import the database schema

# Check the file is correct
[[email protected] phpMyAdmin]# vi /usr/share/phpMyAdmin/sql/create_tables.sql

# Import it
[[email protected] wp phpMyAdmin]# mysql -u root -p < /usr/share/phpMyAdmin/sql/create_tables.sql
Enter password:

Afterwards you will need to make phpmyadmin aware of the creds in /etc/phpMyAdmin/config.inc.php

vi /etc/phpMyAdmin/confing.inc.php

Confirm your changes

[[email protected] phpMyAdmin]# cat /etc/phpMyAdmin/config.inc.php | grep -A3 phpmyadmin
 * wiki <http://wiki.phpmyadmin.net>.
 */

/*
--
$cfg['Servers'][$i]['controluser']   = 'phpmyadmin';          // MySQL control user settings
                                                    // (this user must have read-only
$cfg['Servers'][$i]['controlpass']   = 'AVERYSECUREpasswordgoeshere98123123sdabcsd123';          // access to the "mysql/user"

$cfg['Servers'][$i]['pmadb']         = 'phpmyadmin'

Your work is done, and that pesky error is gone now phpmyadmin has it’s DB. This tutorial has been a long time coming as I see this all the time.

Disable/Enable TLS v1.0 v1.1 and v1.2 for plesk

This actually applies to any website, but is specifically aimed at plesk. Today a customer had complained that we’d disabled both tls 1 and 1.1, they wanted 1.1 for compatibility in the meantime, so it requires doing 1 of 2 things.

plesk bin server_pref -u -ssl-protocols 'TLSv1.1 TLSv1.2'

Or alternatively it can be done directly from within the conf.d ssl.conf for plesk in /etc/httpd/conf.d/ssl.conf, this also applies to httpd users not using plesk.

[[email protected] ~]# cat /etc/httpd/conf.d/ssl.conf | grep TLS
#SSLProtocol +TLSv1 +TLSv1.1 +TLSv1.2
##     This exports the standard SSL/TLS related `SSL_*' environment variables.
##   The safe and default but still SSL/TLS standard compliant shutdown
##     the SSL/TLS standard but is needed for some brain-dead browsers. Use
##     alert of the client. This is 100% SSL/TLS standard compliant, but in
SSLProtocol +TLSv1.1 +TLSv1.2

A pretty simple operation here.

Redirect HTTP to HTTPS

It’s pretty simple after adding a HTTPS site in apache, to forward your existing HTTP website traffic to HTTPS. There might be reasons why you don’t forward everything, but in this case today I was asked to forward everything. Here is how I achieved it:

RewriteEngine On
RewriteCond %{HTTPS} !=on
RewriteRule ^/?(.*) https://%{SERVER_NAME}/$1 [R,L] 

It could be configured for a specific directory tho

RewriteEngine On
RewriteCond %{HTTPS} !=on
RewriteRule ^/?somedir/(.*) https://%{SERVER_NAME}/secure/$1 [R,L] 

Pretty simple stuff.

Apache2 Module installed but not loaded

I came across a customer recently that had a module installed on their apache2 installation, but they couldn’t understand why it wasn’t loaded. In this case it was the filter module.

[[email protected] ~]# yum provides /usr/lib64/httpd/modules/mod_filter.so
Loaded plugins: fastestmirror, replace
Loading mirror speeds from cached hostfile
drivesrvr                                                                                                                                                                                                            | 2.2 kB     00:00
httpd-2.2.15-59.el6.centos.x86_64 : Apache HTTP Server
Repo        : base
Matched from:
Filename    : /usr/lib64/httpd/modules/mod_filter.so



httpd24u-2.4.27-1.ius.centos6.x86_64 : Apache HTTP Server
Repo        : ius
Matched from:
Filename    : /usr/lib64/httpd/modules/mod_filter.so



httpd-2.2.15-60.el6.centos.4.x86_64 : Apache HTTP Server
Repo        : updates
Matched from:
Filename    : /usr/lib64/httpd/modules/mod_filter.so



httpd-2.2.15-60.el6.centos.4.x86_64 : Apache HTTP Server
Repo        : installed
Matched from:
Other       : Provides-match: /usr/lib64/httpd/modules/mod_filter.so


You can activated it by adding following line to httpd.conf;

It was simple to install just throw this in your httpd.conf

LoadModule filter_module modules/mod_filter.so

Job done.

Calculating the Average Hits per minute en-mass for thousands of sites

So, I had a customer having some major MySQL woes, and I wanted to know whether the MySQL issues were query related, as in due to the frequency of queries alone, or the size of the database. VS it being caused by the number of visitors coming into apache, therefore causing more frequency of MySQL hits, and explaining the higher CPU usage.

The best way to achieve this is to inspect /var/log/httpd with ls -al,

First we take a sample of all of the requests coming into apache2, as in all of them.. provided the customer has used proper naming conventions this isn’t a nightmare. Apache is designed to make this easy for you by the way it is setup by default, hurrah!

[[email protected] logparser]# time tail -f /var/log/httpd/*access_log > allhitsnow
^C

real	0m44.560s
user	0m0.006s
sys	0m0.031s

Time command prefixed here, will tell you how long you ran it for.

[[email protected] logparser]# cat allhitsnow | wc -l
1590

The above command shows you the number of lines in allhitsnow file, which was written to with all the new requests coming into sites from all the site log files. Simples! 1590 queries a minute is quite a lot.

Setting X-Frame-Options HTTP Header to allow SAME or NON SAME ORIGINS

It’s possible to increase the security of a webserver running a website, by ensuring that the X-FRAME-OPTIONS header pushes a header to the browser, which enforces the origin (server) serving the site. It prevents the website then providing objects which are not local to the site, in the stream. An admirable option for those which wish to increase their server security.

Naturally, there are some reasons why you might want to disable this, and in proper context, it can be secure. Always be sure to discuss with your pentester or PCI compliance officer, such considerations before proceeding, especially making sure that if you do not want to use SAME ORIGIN you always use the most secure option for the required task. Always check if there is a better way to achieve what your trying to do, when making such changes to your server configuration.

Insecure X-Frame-Option allows remote non matching origins

Header always append X-Frame-Options ALLOWALL

Secure X-Frame-Option imposes on the browser to not allow non origin(al) connections for the domain, which can prevent clickjack and other attacks.

Header always append X-Frame-Options SAMEORIGIN

Moving a WordPress site – much ado about nothing !

Have you noticed, there is all kinds of advise on the internet about the best way to move WordPress websites? There is literally a myriad of ways to achieve this. One of the methods I read on
wp.com was:

Changing Your Domain Name and URLs

Moving a website and changing your domain name or URLs (i.e. from http://example.com/site to http://example.com, or http://example.com to http://example.net) requires the following steps - in sequence.

    Download your existing site files.
    Export your database - go in to MySQL and export the database.
    Move the backed up files and database into a new folder - somewhere safe - this is your site backup.
    Log in to the site you want to move and go to Settings > General, then change the URLs. (ie from http://example.com/ to http://example.net ) - save the settings and expect to see a 404 page.
    Download your site files again.
    Export the database again.
    Edit wp-config.php with the new server's MySQL database name, user and password.
    Upload the files.
    Import the database on the new server.

I mean this is truly horrifying steps to take, and I don’t see the point at all. This is how I achieved it for one my customers.

1. Take customer Database Dump
2. Edit the database searching for 'siteurl' with vi
vi mysqldump.sql
:?siteurl

And just swap out the values, confirming after editing the file;

[[email protected]]# cat somemysqldump.sql  | grep siteurl -A 2
(1, 'siteurl', 'https://www.newsiteurl.com', 'yes'),
(2, 'home', 'https://www.newsiteurl.com', 'yes'),
(3, 'blogname', 'My website name', 'yes'),

Job done, no stress https://codex.wordpress.org/Moving_WordPress.

There might be additional bits but this is certainly enough for them to access the wp-admin panel. If you have problems add this line to the wp-config.php file;

define('RELOCATE',true);

Just before the line which says

/* That’s all, stop editing! Happy blogging. */

And then just do the import/restore as normal;

mysql -u newmysqluser -p newdatabase_to_import_to < old_database.sql

Simples! I really have no idea why it is made to be so complicated on other hosting sites or platforms.

Plesk website running FastCGI Timeout Gateway Errors and Slow Loads

So my friend Paul shown me how to troubleshoot fastCGI on plesk boxes, very easy..

# pstree | grep cgi
     |       `-httpd---20*[php-cgi]

We can see that 20 php-cgi processes run. If we check the maximum in the configuration file set for fastCGI of Apache2..

# cat /etc/httpd/conf.d/fcgid.conf  | grep MaxProc
  FcgidMaxProcesses 20

We see 20 is the maximum, so it’s definitely hitting the FastCGI limit, we need to increase the limits, so we just edit the file and increase the limits for that variable;

vi /etc/httpd/conf.d/fcgid.conf

echo "FcgidMaxProcesses 50" >> /etc/httpd/conf.d/fcgid.conf

Setting a seperate memory limit for PhpMyAdmin to the rest of the sites

A common issue I see Rackspace customers with is there PhpMyAdmin not having enough memory, often I ‘ll see countless tickets where the memory_limit is increased for phpmyadmin, and when one of their virtualhosts falls over, it is then decreased for all of the sites, until someone wants to use phpmyadmin again.

not very good really is it? Actually, fixing this is quite easy. Lets provide a php.ini for phpmyadmin that only phpmyadmin uses;

# Copy original php configuration
cp /etc/php.ini /usr/share/phpMyAdmin/php.ini

# Modify /usr/share/phpMyAdmin/php.ini so that the following variable is set as a higher value
memory_limit = 256M

Naturally if you now goto the customerwebsite.com/phpmyadmin/php.ini you’ll see a nice php file waiting for you… not good… we need to protect the php.ini file as it can expose stuff we don’t want to be seen; lets make it as hard to find out the server configuration and hide php.ini altogether.

# File to edit may differ but it can be any file inside conf.d make one if you like call it phpini.conf or something
vi /etc/httpd/conf.d/php.conf
<Files php.ini>
          Order allow,deny
          Deny from all
</Files>

Dont’t forget the most important step

# Check apache syntax
apachectl -t

# Restart the apache process
apachectl graceful

Another pretty simples thing to do. That isn’t pretty simple until you do it.