Wednesday, 16 April 2014

Please Don't Upgade to Wordpress 3.9!

Please Don't Upgade to Wordpress 3.9!

I have just been sent this email by WordPress telling me of the changes they are making.

As some of these changes have not been made in my plugins and you DO upgrade to WordPress 3.9 and any of my plugins break e.g

Strictly AutoTags
Strictly TweetBot
Strictly Google Sitemap (no longer supported)
Strictly Content Cleaner
Strictly System Check

Then you WILL HAVE problems due to code they are now using not being in the plugins.

I wanted a quite Easter Weekend not one spend coding!

So if you do get problems by upgrading it's your own fault for not following my blog!

Thursday, 20 February 2014

Quickly Generate A Random Number Between 1 and 9

Quickly Generate A Random Number Between 1 and 9

By Strictly-Software

As you should know by now the RAND() function in SQL has the downside that it will return the same value if the same seed is provided to it.

E.G if I call this code multiple times I get the same number for each result:

SELECT RAND(1) AS [RandomNumber]

This always returns 0.713591993212924 for me.

Therefore when people use RAND() they often end up coming up with various ways to generate a unique seed value as well.

One of the quickest ways I have found to return a random number is by using the NEWID() function which generates a random GUID.

If I wanted to return a random record from a recordset I would use the NEWID() function in my ORDER BY statement. 

I use this a lot in my Jobboard code when I want to return a random banner from a banner group like so:


SELECT TOP 1 BannerPK, BannerURL, RedirectURL
FROM   BANNERS
ORDER BY NEWID()


However if I wanted to return a single number between 1 and 9 I could use the following code.

This basically converts the GUID to a VARBINARY value before converting it to a VARCHAR and then taking off the the first character with a LEFT statement.

I have found that this gives a larger proportion of 1's than any other number but it is useful if you want a quick way in SQL to generate a single number (or more if you extend the numbers returned with a string function) e.g LEFT(val,2) or use a MID or RIGHT. Then this code could do the trick.


DECLARE @RANDNO INT
SELECT @RANDOMNO = CAST(LEFT(CAST(ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS VARCHAR),1) AS INT)

Tuesday, 18 February 2014

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.