Minimalist Wordpress Deployment

by Martin Westin in

So, my "sugar daddy" datacenter is kicking me out. This is where I have been hosting absolutely free for a decade so I have nothing bad to say about them... at all. :-) My 4U quad-core2duo, 4GB RAM, 8TB SATA beast will soon become homeless.... Did I mention they let me host that monster for free?

This website was a minor task for that server but last Sunday I moved it onto a minuscule "cloud" VPS at Gelsys. Running PHP and MySQL on 256MB of RAM (and no swap) and expecting a stable server under load did require some tinkering. I am not going to detail every little thing. I'll only outline the basic setup.

I have Wordpress spitting out static html files which is what is being served for 99% of all requests. PHP is only being called on to re-generate the files when the caches expire and to serve up the admin interface. In this way my mini-VPS can handle a pretty decent amount of traffic. I don't generally get a lot of traffic but I am curious to see how the server behaves during a spike.

Wordpress caching is handled by the excellent W3 Total Cache plugin which recently got official support for Nginx. Yay! no more custom rewrite rules. My only problem getting it running this time was that it refused to cache the index page, which is kind-of bad when it is one of the heaviest pages to render. Turned out it was a simple precedence issue. "/" is considered a folder which I caught in a rule before the caching rules had a chance to do their thing.

Nginx is the web server. I have been running it for a few years already but for this it is absolutely essential to save on ram and get the best performance serving static files.

PHP is called from Nginx over an unix socket and served by php-fpm. PHP is compiled with my favorite accelerator eaccelerator. It is just the simplest to get going and has shown good performance for my apps. PHP has been scaled down a lot in php.ini, reducing mainly memory usage and timeouts.

MySQL loves memory so I had to do the equivalent here. Basically taking the standard performance tips and doing the opposite. I know I will only server Wordpress and I know I don't have any enormous datasets so MySQl should be fine on very low memory. Also since I limit the number of php processes I consequently limit the maximum number of client connections MySQL will need to keep track of.

The only other service running is ssh and I was surprised to find that sftp was one of the biggest memory hogs. Uploading a bunch of files of sftp and the server would immediately come dangerously close to running our of memory. In fact it did a bunch of times during my tweaking.

I have been thinking about switching from Wordpress to some static website generator but that is for another time. This setup looks fairly smooth at the moment.

Finding ordered position of a row in MySQL

by Martin Westin in

This is slightly modified from something I found @ Using high-score lists or similar you often have a large number of rows where you want to know who is in 7th place or how well is id:254 doing. This eliminates the slow looping in php of big result-sets by making a sub-query in MySQL.

SET @rownum := 0;

SELECT @rownum := @rownum+1 AS rank, id, points
FROM highscores 
) AS highscores WHERE id = 254;

Locating potential duplicate before switching collation in MySQL

by Martin Westin in

When you need to switch a fields collation you can check if any existing data will conflict with this change. For example a keyword-field might contain data that is considered unique in one collation but not in another. The Swedish character ä is considered a unique character in swedish collation but in general collation it is only an a in an accented form.

Comparing the results from these two queries will show what effect, if any, an change in collation will have.

SELECT id, keyword as key, count(*) as num FROM keywords GROUP BY key having num > 1;
SELECT id, keyword COLLATE utf8_unicode_ci as key, count(*) as num FROM keywords GROUP BY key having num > 1;

Fixing existing data when switching character set in MySQL

by Martin Westin in

When altering a database, table or field from one character set to another, existing data will probably look garbled since it is expected to be in the new character set. To "convert" existing data is not always easy. This method (found in the comments of a blog entry I have lost the url for) works as long as you can keep the database "locked" when you do this. Otherwise you will convert any new data entered and make that data look garbled.

Read More

Altering character set and collation of tables in MySQL

by Martin Westin in

Altering the character set and collation of a table is sometimes not enough. You may have to alter the actual fields in the table to get MySQL to comply in some cases. I don't know why or when MySQL does this.

ALTER TABLE my_table DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;

ALTER TABLE my_table MODIFY my_field varchar(255) CHARACTER SET utf8 COLLATE utf8_swedish_ci;

Preferred character set and collation in MySQL

by Martin Westin in

My preferred character set is utf8.One big drawback in many cases can be that the default collation ignores accents for characters. The result is that "halla" and "hallå" is interpreted as the same word. This is of-course bad for storing general data such as names users or products that may very well have accented characters that should be respected.

The sollution for me is to not use "utf8_general_ci".

For generic data that have no particular language I prefer "utf8_bin" For data that will be sorted and that can be given a "language" I prefer the collation most appropriate, such as "utf8_swedish_ci"

Replacing characters in MySQL table data

by Martin Westin in

This may be useful when for example the character encoding has been messed up and you need to correct existing data. This example will correct swedish accented characters in a uft8 - latin1 mixup.

UPDATE my_table
set my_field = replace(my_field,'Ã¥','å');

UPDATE my_table
set my_field = replace(my_field,'ä','ä');

UPDATE my_table
set my_field = replace(my_field,'ö','ö');

UPDATE my_table
set my_field = replace(my_field,'Ã…','Å');

UPDATE my_table
set my_field = replace(my_field,'Ä','Ä');

UPDATE my_table
set my_field = replace(my_field,'Ö','Ö');

(Yes, you can concatenate them if you like but they become very hard to read.)