A WordPress Performance Killer - Automatic OPTIMIZE or REPAIR on big Databases

A WordPress Performance Killer - Automatic OPTIMIZE or REPAIR on big Databases

By Strictly-Software

I used to use the WordPress plugin WP-DBManager. I still do for the automatic backups (when they work), I have found on one site the scheduling just doesn't work so I have to do it by hand. No difference between that site and another that works but hey that's WordPress!

However one thing I found, especially once I reached 20k+ articles is that my scheduled OPTIMIZE / REPAIR jobs would totally kill my sites every Friday for an hour or so.

It took me a while to find the cause but when I used the Server Monitor in Navicat and saw that both my Databases were doing a REPAIR by SORT at the same time, I soon realised it was my scheduled job in WP-DBManager to REPAIR / OPTMIZE every Friday that was the culprit.

With MyISAM tables (the default storage engine for WordPress), an OPTIMIZE or REPAIR job will take the whole table out of action as the process is carried out. The table is locked as the sorting and repairing is done which means no other users or processes can access it.

Unlike MS SQL which lets you De-Frag a tables index whilst connections to it carry on MyISAM just locks up until the job is complete.

Symptoms

• Your website is hanging for ages, or timing out. If you are using Cloudflare you might be lucky enough to see cached pages either that a 503 service unavailable.
• Running a TOP command will show MySQL process consuming 90-100% of your CPU.
• Checking your Server Monitor in Navicat shows a SORT going on with a long execution time. The more pages and posts the longer it will take.

Risks

• If you have an scheduled job to REPAIR / OPTIMIZE your tables, either through a plugin like WP-DBManager then you could suffer this problem.
• The type and size of table matters. If it is using MyISAM it will be locked until the job is over. 
• The bigger the table e.g WP-Posts the longer it will take. 
• The more records the longer it will take. If the table is used a lot then it will be crushed until the job is over.

Solutions

• I turned off the automatic OPTIMIZE and REPAIR jobs on WP-DBManager.
• If I have scheduled downtime then I will make use of it whilst the werbserver is offline to carry out DB maintanance.
• If you are clever enough (and I don't know if MySQL supports SYNONYMS like MS SQL) but you could create copies of your existing tables with new names, let your system carry on using the old tables whilst your OPTIMIZE / REPAIR the new ones. Once finished toggle the SYNONYM back to the newly formatted tables. A SYNONYM would be perfect for this but you could write SQL to create a table, insert records, repair and then re-name. Maybe a plugin could do this (ideas for developers? I would be willing to write one for ££)
• I know use my own Strictly System Check plugin instead to handle automatic REPAIRS / OPTIMIZE as it will only carry them out if the system is already suffering. My plugin checks the server load, the time it took to load a page, the number of connections and so on. If the plugin determines that the system is having trouble you can set it to OPTIMIZE or REPAIR (after a check to see if one is needed). This way you are only fixing something that needs it rather than causing havoc for no need.

It is something to be on the lookout for. If your site suddenly acts slow with high MySQL CPU use then a scheduled REPAIR might be the cause.

If this happens it is best to turn off APACHE, let the REPAIR do it's job and then turn the webserver back on.

Nightmare with Wordpress, Crashed Corrupt Table and Fixing The Problem

Nightmare with Wordpress, Crashed Corrupt Table and Fixing The Problem

By Strictly Software

This has been a real nightmare day for me!

You can read all about it on the Wordpress forum here: Suddenly Templates Not Working. (not that I actually got any help from anyone!)

I first noticed I had a problem on one of my Wordpress sites when I went to one of my pages that uses a custom page template and saw that it was totally blank. It should have been showing a feed with a Twitter widget in the sidebar and special content in the middle.

I edited the page and saw that the template was set to "Default Template". I set it to the correct template and tried saving it only for the page to reload with the Default Template still selected!

I turned on the WP_DEBUG constant and was met with a load of MySQL errors that all indicated that my wp_postmeta table was corrupt e.g

WordPress database error: [Incorrect file format 'wp_postmeta']
INSERT INTO wp_postmeta (post_id,meta_key,meta_value) VALUES (7381,'_edit_last','1')

I then ran a REPAIR (FULL) on the table only for it come back ASAP with error messages such as:

.wp_postmeta check Error Incorrect file format 'wp_postmeta'
.wp_postmeta check error Corrupt

If a REPAIR or OPTIMIZE wouldn't fix it I knew I was in the shit!

I tried searching the web but I couldn't find anything of use.

I was using the WP-DBManager plugin to create backups for me on this site but for some reason it had stopped working after my recent upgrade to WP 3.8. By the way, the amount of problems I have had since upgrading to this version would fill a book by now!

Anyway the last manual database update was done just before Christmas so it wasn't too far back and anyway the table only holds SEO META, Flags set by plugins and mostly guff that you don't really need anyway.

All my tags, categories, pictures and so on were still the site so it wasn't too major a problem except I hate BUGS! Especially ones I can't fix!

Therefore I created a new empty table with the same structure, indexes and settings as the existing wp_postmeta table and called it wp_postmetaNEW.

I turned off APACHE and then dropped the old table. I renamed my new table to wp_postmeta (I could have truncated the old one by the way) and checked it was working.

I then restored my old backup to a new database on the server called RESTORE.

I checked the wp_postmeta table wasn't corrupt first and then ran a statement (whilst logged in as root to Navicat) to copy across all the meta data I did have from the old database to my new table e.g

INSERT INTO MYDB.wp_postmeta
(post_id,meta_key,meta_value)
SELECT post_id,meta_key,meta_value
FROM RESTORE.wp_postmeta

This took a long time!

Especially for only 94,310 records.

I was waiting over 30 minutes with the server monitor ticking away saying it was repairing the table.

Then I noticed in my Putty console with a TOP command that no MYSQL process was actually running!

This made me think the process had crashed and NAVICAT wasn't telling me the truth.

By the way I have had this happen many a time whilst doing REPAIRS, waiting for the NAVICAT window to give me the results when I notice no MYSQL process is running and that the actual job had finished ages ago.

So I killed the process and checked how many rows were in the table - none. I did another REPAIR and it said the table was CRASHED again!

By this time I was so pissed off and wanting to go home I just truncated the table and turned APACHE back on.

From looking at the old RESTORE wp_postmeta table it was mostly guff and flags anyway and custom SEO META's. Therefore I thought fuck it, I'm not wasting anymore time on this. However before I could go the sites freezed up again and the Server Monitor was telling me a REPAIR by SORT was going on.

However yet again on the servers console running a TOP showed 0.00 load and no MySQL processes!

What was going on I have no idea but it seems truncating a corrupt / crashed table didn't help. So I had to manually re-create the table, indexes, options, PK, auto-increment amount etc by hand.

Then I turned APACHE back on and everything was blistering fast. I waited a little bit to ensure an automatic REPAIR wouldn't start again and then ensured all my pages were using the correct templates, filling in Yoasts SEO boxes at the same time and then left.So far so good it is all working and I don't seem to have lost any data apart from 90,000 rows of guff.

As I don't add my images in by hand (a lot of these blogs are pretty much automated) they are not in the wp_postmeta table. Instead they are just referenced from their local or foreign location which was lucky for me.

The key to this lesson is - always have up to date backups and don't rely on Wordpress Plugins to do it for you. 

You can see my debate over WP-DBManager not working on Wordpress here if you want and it was only a manual backup I did through NAVICAT that would have saved me if the insert had worked as the plugins backups were so out of date.

Anyway that was my "fix" for my problem.

Listen, learn and take notes from my mistakes so you don't make the same ones!

MySQL Server won't restart

MySQL Server won't restart

Today I went to restart MySQL from my SSH console with the following command:

/etc/init.d/mysql restart

However even though the database server stopped it wouldn't restart.

I tried opening another console and running the status command.

/etc/init.d/mysql status

But this just told me it was stopped and a start command kept failing.

Even when I went into my VirtualMin website that manages my virtual server the service wouldn't restart.

I dug into the services and databases and tried accessing a database from VMIN and saw a message saying the system couldn't retrieve a list of databases. Further digging gave me this error:

can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'

Now I knew I had changed some settings in the my.cnf configuration file the other day for performance tuning. So I searched the web and found that I had added a command that wasn't supported by my older version i.e MySQL 5.0.51.

The command in question was:

skip-external-locking

Because Java had crashed and I couldn't access the file to edit it easily.

I quickly ran a:

CHMOD 777 /etc/mysql/my.cnf 

command to allow me to edit the file from FTP and then I swapped the new command with the older one which my version of MySQL supported:

skip-locking

I copied the file back and then hey presto a start command got the server back and running:

/etc/init.d/mysql status

I then made sure to CHMOD the file back so it couldn't be written by the website.

However on checking my website I only got to see the theme but NO articles.

It was a Wordpress site and the problem was probably WP-Super-Cache caching a page without data. I needed to run a REPAIR command on my wp_posts table to ensure all posts were visible again.

This is something I have seen many times before with hard reboots. The system comes back up but no articles appear. I always REPAIR and OPTIMIZE my wp_posts and wp_posts_meta table to rectify this.

This obviously locked the database up, as well as consuming 99% CPU whilst it ran - something which really annoys me, but afterwards the site was working.

So if you have been performance tuning your own MySQL database make sure you are not adding commands that your server doesn't support. A failed restart is a sure sign of unsupported configuration commands.

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.