Setting Up Percona, Galera and SphinxSE

In the last project I worked on, I had a chance to play with what I would consider improvements to the typical MySQL install that most of us use. There were three aspects to what we were looking for:

1. Naturally we were looking for a system that had the reliability of MySQL, but at the same time we were itching to try some of the forks that have evolved from that codebase.
2. We were hoping to get a better multimaster redundancy system setup than the traditional process.
3. We were hoping to get SphinxSE involved.

I'll probably talk about these three points separately some other time, so for now I will just touch on them lightly...

As far as the MySQL fork is concerned: there are a number of projects that are based off of MySQL that have either improved or replaced the main InnoDB storage engine. These generally provide faster services than the traditional MySQL but some also add some extra features. For this, we wanted to try the Percona Server.

Multimaster replication in MySQL generally is a hassle to setup: not so much on the 'creation' side but on the redundancy side. It's pretty easy to set it up to run, but setting it up to fail 'correctly' generally involves other systems. We had heard of Galera and figured that this might be a good time to try it. Galera is basically a system that links in with the MySQL server/s, but at the same time talks amongst itself to ensure that all of the servers are in sync. If a new server comes up or an old server goes down, Galera is there talking, making sure that everything is still organised correctly.

Sphinx is basically a super fast full text searching service: it is much faster than, and more capable than, the traditional MySQL text searches. While Sphinx has a couple of different ways it can be interacted with, the Sphinx Storage Engine intrigued us. SphinxSE allows you to essentially tunnel through from a 'normal' MySQL table right through to Sphinx allowing you to do the Sphinx searching as 'just another' INNER JOIN in your query.

So with that in mind, we went searching for a solution that was already made :)

Unfortunately we weren't able to find anything complete... they were either missing the Galera component or the SphinxSE part.

So, for anyone interested in setting something like this up themselves, I've put together a quick little hacky guide to try. Hopefully everything makes as much sense as it can. If you read the instructions you'll see that we are using some custom (and possibly strange) directories for things like where the Sphinx data will go, as well as the MySQL/Percona data/configuration etc. You will also notice that there's a point where you download a patch file to apply to Sphinx: this is based on an older patch file for Percona that was aimed at an older version of Sphinx. It just makes sure that the SE will build.

Some final notes: I did all of this a while ago and so some things have changed since then. Galera has been updated since I wrote these instructions, as has Sphinx. I also just saw that Percona is expecting a beta release at the end of February 2012 of their 'Percona XtraDB Cluster' server which, from what I understand, is Percona with Galera built into it. So with that in mind, this is really just some notes on how to get things working if you're super keen on at least playing with this stuff. Oh yeah, and these notes are all based off of Ubuntu 11.10 Server. It assumes that you haven't already installed MySQL, as that gets to be a problem (as you're basically going to be trying to replace it). And with that in mind, here goes...

Be nice to yourself: become root...

sudo -s

Make sure that for all of the main repositories (at least) you not only have the deb repos, but also the deb-src repos. This can be checked in /etc/apt/sources.list.

Update and get some things you'll need...

apt-get update
apt-get install dpkg-dev bzr cmake cmake-curses-gui chkconfig libssl0.9.8
apt-get build-dep mysql-server

Create a MySQL user: this would normally be done by the MySQL installation.

groupadd mysql
useradd -g mysql mysql

Get some directories ready.

mkdir /etc/mysql
mkdir /etc/mysql/conf.d
mkdir /var/lib/mysql
mkdir /var/lib/mysql/data

chown -R mysql:mysql /etc/mysql
chown -R mysql:mysql /etc/mysql/conf.d
chown -R mysql:mysql /var/lib/mysql

mkdir /etc/sphinx
mkdir /var/lib/sphinx
mkdir /var/lib/sphinx/log
mkdir /var/lib/sphinx/data

Start getting source...

cd /usr/local/src
wget http://launchpad.net/galera/1.x/21.1.0/+download/galera-21.1.0-amd64.deb
dpkg -i galera-21.1.0-amd64.deb

bzr branch lp:~vadim-tk/percona-server/percona-5.5.15-galera
wget http://www.sphinxsearch.com/files/sphinx-2.0.1-beta.tar.gz
tar -zxvf sphinx-2.0.1-beta.tar.gz
wget http://files.codefromaway.net/sphinx-2.0.1-beta-to-sphinx-2.0.1-beta-5.5.15.patch
cd sphinx-2.0.1-beta
patch -p1 < ../sphinx-2.0.1-beta-to-sphinx-2.0.1-beta-5.5.15.patch
cp -R mysqlse ../percona-5.5.15-galera/storage

Build and install Percona

cd ../percona-5.5.15-galera
ccmake .

You will be given a UI at this point... press 'c' for configure, then 'e' to exit help then finally 't' to toggle to advanced mode. Now, scroll through the list and edit the following values (setting them to the values listed):

CMAKE_INSTALL_PREFIX /usr/bin/mysql
INSTALL_MYSQLDATADIR /var/lib/mysql/data
MYSQL_DATADIR  /var/lib/mysql/data
SYSCONFDIR  /etc/mysql
WITH_READLINE  ON
WITH_WSREP  ON

Now press 'c' for configure, 'e' to exit help and then 'g' to finally generate the configuration file.
Make your newly configured application:

make
make test
make install

Build and install Sphinx

cd /usr/local/src/sphinx-2.0.1-beta
./configure --prefix=/usr/bin/sphinx --sysconfdir=/etc/sphinx --libdir=/usr/libs --datarootdir=/var/lib/sphinx --with-mysql --with-mysql-includes=/usr/bin/mysql/include --with-mysql-libs=/usr/bin/mysql/lib
make install

Prepare some shared libraries. Create /etc/ld.so.conf.d/mysql.conf and then:

echo "/usr/bin/mysql/lib" > /etc/ld.so.conf.d/mysql.conf
ldconfig

Start getting Percona ready by copying configuration files as well as the init script for the service:

cd /usr/bin/mysql
cp ./support-files/my-medium.cnf /etc/mysql/my.cnf
cp ./support-files/wsrep.cnf /etc/mysql/conf.d/wsrep.cnf
cp ./support-files/mysql.server /etc/init.d

Edit the /etc/mysql/conf.d/wsrep.cnf file and set the following values:

wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://"
wsrep_sst_auth=wsrep_sst:some_random_password

NOTE: the wsrep_cluster_address line is initially commented out! you have to remove the commenting hash AS WELL as make the gcomm change... Also note that the 'some_random_password' will get used towards the end of this whole process, so remember it!

Now, we enable Galera...
Edit /etc/mysql/my.cnf: add the following lines to the end of the file:

binlog_format=row
!includedir /etc/mysql/conf.d

Edit /etc/bash.bashrc and add the following lines:

PATH=$PATH:/usr/bin/mysql/bin:/usr/bin/sphinx/bin
export PATH

Reload bashrc so that the MySQL binaries and Sphinx binaries are now in your path:

source /etc/bash.bashrc
source ~/.bashrc

Prepare the MySQL database and finally start the service!

./scripts/mysql_install_db --basedir=/usr/bin/mysql --user=mysql --datadir=/var/lib/mysql/data
/etc/init.d/mysql.server start

Use the following two lines to get MySQL to load on startup:

chkconfig --list mysql.server
chkconfig mysql.server on --level 2,3,4,5

Restart the server now, just to be sure :)

Enable the WSREP user so that it can read from and write to the databases:

mysql -u root -e "SET wsrep_on=OFF; DELETE FROM mysql.user WHERE user='';"
mysql -u root -e "SET wsrep_on=OFF; GRANT ALL ON *.* TO wsrep_sst@'%' IDENTIFIED BY 'some_random_password';"

'some_random_password' is what we remembered back up towards the middle of this process.
Finally, run MySQL from the command line to get the CLI and run:

INSTALL PLUGIN sphinx SONAME 'ha_sphinx.so';

You should now have Percona up and running with Galera replication and SphinxSE on the side. Have fun... I hope it helps *someone*!

Simple Cachebusting for JS

Browsers caching your assets is a good thing. It not only reduces the bandwidth requirements of your site, but it also speeds up loading on the client side every time they visit.

But what happens when you have some changes that you need pushed everywhere to have the site still working? The big thing I'm thinking of here is Javascript. You change the HTML format of a page and all of a sudden your jQuery fails, or your AJAX responses have been tweaked and now your 'onSuccess' functions that work with that data need to be changed... and if they're not, then things don't work.

So what do you do? Of course, there's always the option to stop caching: but that will increase server load, used bandwidth and slow down the loading on the client side. Even if you do disable caching, you're still at the mercy of the client... maybe the browser decides to ignore your cache control requests (or possibly the proxies between the client and the server ignore the requests).

So in trying to work this out, I realised that by adding a random GET parameter will help resolve this issue. Every browser that I have tested against treats the (partial) URL of "random.js?12345" as entirely different from "random.js?12346" as far as caching is concerned, and with good reason. The GET parameter is used for exactly that: to "get" a given resource. It's understandable that the browser would treat these two things as different.

I could use a random number every time to force the cache busting, but that basically sends me back to losing any benefit of browser caching. So the question becomes, what do I use for the GET parameter? Initially I considered using a version number. Every time I changed the JS file, I would change the URL that the generated HTML would use... it would go from "random.js?1.2.3" to "random.js?1.2.4" and so on. In thinking about that implementation, though, I realised that my laziness and forgetfulness would be a deal breaker here as there is no way I would be able to remember to update the URL in (every?) file that references the JS file.

What I ended up doing was actually pretty simple. Every time I include a JS file in the HTML generated by a PHP script, I first quickly find the last modified time of the file and I then append that modified timestamp to the URL.

For my random.js example above, I would do something like the following in my index.php file:
$stats = stat(dirname(__FILE__) . '/random.js');
echo '<script src="random.js?' . $stats['mtime'] . '"/>';
Really simple, perfectly functional and highly cross platform, which is something I always strive to do. If you're not using PHP, then the concept is still the same... you would just need to translate it to your favourite programming language.

Now, if you have a lot of resources that you want to include, maybe you would write a function to try and remove the redundancy, but generally when I do this I only have one or two files to process anyway.

Naturally, the better alternative is to use a framework that does this all for you anyway. A lot of those frameworks do a lot more for you than just the cache busting, so of course they're worth the effort, but if you're not in a situation where you can choose this route, then hopefully the above will help!

Will the PHP Community Ever Drop the "Silly n00b" Presumption?

A long, long time ago, I learned my first scripting language: Perl. Admittedly, I wasn't doing any database interactions and to be honest the code wasn't entirely complex (it was a basic user management system with access to different data sets depending on account information).

I wouldn't say that I asked a lot of questions, but I definitely *read* a lot of them and not once can I remember anyone basically answering a question by just pointing out an insecurity in the original posters' question.

Cut now to my current web language of choice: PHP. It seems as though more often than not, one of the first answers to a question online is pointing out the security issues with the question, and a lot of the time, the original question isn't even being answered!

Time and time again, "SQL injection!" or some such stuff is the answer, even when the question had nothing to do with it. It's repetitive and to be completely honest, a little boring now. I almost feel as though Person A got hit with one of these replies once and so now they feel as though they have to pass on the 'love' to Person B.

I don't know what it is that's causing this... there's just too many variables. Is it just a sign of the times? I don't know: I don't spend much time in other language support fields. Is it just because I wasn't aware of these types of replies before? Again, I don't know. Maybe it's just me being a cranky old man looking back on the good old days. Maybe, too, Perl went through these growing pains and I was just fortunate enough to be on the other side of them.

I will go out on a limb, though, and guess it's a combination of two things: 1) the gamification of questions and answers; and 2) the increase in people teaching themselves to code.

Not that either of these things are a problem in and of themselves. Having said that, sites like stackoverflow.com have really made it worthwhile to try and get in and get some upvotes and I hate to say it, but the PHP section of that site is looking less and less usable to me every day. At the same time, more and more people are learning to code in a 'non-professional' environment. Again, this isn't a bad thing, however I get the feeling that these basic security issues seem to have been missed the first time over when people teach themselves so to them, it's amazingly impressive to be able to find these issues with someone elses code, to the point where the answer to the actual question doesn't matter as much as this.

Maybe I'm just tired, but I just feel as though at some point, the PHP community as a whole needs to get over this whole thing about "commenting on security problems is my number one concern, even if I can't answer the original question"... it just isn't helping anyone.

Don't get me wrong: a good, correct answer is fantastic, even if there's an aside of "BTW, SQL injection!" but please, that's not always the answer!

Would You Flattr My Wishlist?

The other day I was reading a blog post somewhere that answered enough of my questions that I saved a significant amount of time. It was the first time that I was driven to really try and find a way to get the poster some sign of appreciation.

The closest thing I could find, though, was an Amazon wishlist. That was fine, however the cheapest thing on that list was around $60. Don't get me wrong, I appreciated the information, but it was a big mental leap from never having given anything to a blog poster to giving $60.

It was at that point that I realised that there was no way to give a 'partial payment' to a wishlist item. From what I could find, anyway.

This is my request, to anyone that cares: please make it possible for me to just give a random amount of money to an item on a wishlist. That way I can give something that really matters to the wishlist owner, and it's a little more 'personal' than sending a gift card / credit / whatever it is that I would have had to have done otherwise.

Then I started thinking about flattr.com: maybe they could do something similar, by integrating their system so that as a person that can be 'flattred', the money goes towards items on my wishlist or something similar.

To me, at least, the outcome of this is way cooler than getting a bit of money in some electronic account... it would be awesome to just every now and again get a random item out of my wishlist as a total surprise!

Does anyone know of anyone working on this?

Is Google (and mod_pagespeed) the Poor Man's CDN?

EDIT: As per the comment by barryhunter below, this whole article is bogus. The CDN stuff was something I found under the 'Page Speed' heading but is related to Page Speed Service, as opposed to mod_pagespeed itself... Sorry for the confusion.


Since it was available as a browser plugin, I've been intrigued by the Page Speed concepts that Google have promoted. There was just something about the entire project that I loved... the fact that people out there are so passionate about these ideas that they were writing code to help me better understand the problems my site was having... it's a great project.

Then when the Page Speed module for Apache was released, I was blown away. Automating the work that can be done, basically 'earning' a lot of the benefits of the Page Speed knowledge, simply by installing an extension to Apache... again, just fantastic.

But to be honest, lately, I've been ignoring it. We installed it on our servers, appreciated the work that it was doing for us and then we moved on.

Just recently, though, when trying to explain the concepts to a workmate, I had reason to look back at the site and read through the documentation again. Now admittedly I have not had any need for a content distribution network until recently. In fact, if it wasn't for a friend of mine I still would have no need for it. So I don't know if it is my 'new found knowledge' of CDNs, new changes to mod_pagespeed or a combination of both, but looking back over the documentation I found something really cool: mod_pagespeed has some 'proxying' options that will proxy your images, JS and CSS!

Basically by enabling these options, mod_pagespeed will rewrite your HTML so that the CSS, JS and images are 'proxied' through the Google network. That is to say, instead of requesting http://www.example.com/my.css, the rewritten HTML will have the browser request something like http://3-www-accel-pss.googleusercontent.com/www.example.com/my.css. These servers appear to be a CDN: that is to say, the browser is sent to the nearest server.

And the best thing? It's free! For now, at least.

So that was my little 'excitement' for the day. If you can't afford to pay for a CDN and you have enough control over your server, do yourself a favour: install mod_pagespeed and turn on the proxying features.

First Things First

So here we go... as we move into 2012 I'm going to try and make a real go of blogging: this will generally be centred around software development technology and random things that I find interesting.

2012 is the year of me trying to move into a more independent position as far as work goes... so that means a bit of product development and hopefully more contracting and so on. As I move towards that goal, I want to try and outline my beliefs and reasoning behind them regarding how I approach my work.

So hopefully this goes well. But first things first, I want to copy into here a couple of posts I made at another site I ran for a while, just to get everything together in one spot...

Static Analysis with PHP CodeSniffer

PHP CodeSniffer is a great tool to have in your development environment. Originally developed to ensure that your code matches your coding standards, I think its strength is somewhere else...

If you take the time to read through some of the coding standards that ship with PHP CodeSniffer, you will find that some of them don't really relate to coding standards exactly. Some are more related to code analysis, and after a recent update to a project I was working on, I think that this is PHP CodeSniffer's real strength...

You may recall from an earlier post that one of the projects I was working on used an in house DB abstraction layer. For reasons that are probably obvious, we began porting our code to MDB2. It was a large codebase, with queries sprinkled throughout. Quickly we realised that search and replace was not going to cut it, as we needed to begin using a result set variable.

The original class used a single variable for the connection and the result set. That is to say, with the single variable you would connect to the database, make the query and then iterate over the result set. MDB2 is different (and in this regard, more like the basic MySQL functions in PHP) in that you have one variable for the connection, call the query on that connection and then store the return value into another variable (the result set). So in effect, you use one variable for some things (calling queries, getting error messages and so on) and another for iterating over the results. Our problem with porting was that our class used one variable for all of that, so we couldn't easily search and replace.

 So what to do? Well naturally we could have looked everywhere for any instance where we made a connection, then ensured that the variable was used correctly, as were any variables returned from function calls. Or, we could have automated it.

So automate it I did.

I had been playing with PHP CodeSniffer anyway, and so I saw a great use for it here. Of course, it's really the tokeniser that we're taking advantage of, but PHPCS does a great job of providing us with extra features related to moving through the token list.
A couple of hours later, I had written the sniff. But what does it do? Well basically it looks for function calls that make a connection to the database. When it finds that, it looks back a little bit and determines the variable name that was being assigned to. We now know a variable that holds a DB connection, and with a little bit more magic, we can determine the scope that this variable is used in. Now look around in that scope, and ensure that when that variable gets used, it gets used in the right context. Let's go a bit deeper though... if the connection gets used in the right function, we know that the return value is a result set. Surely we can then iterate through it, doing the same work, testing that it gets used correctly?

And that's what I did. Because we were using MDB2, I even had more information... using reflection, I was able to determine return types of all sorts of functions and functions that existed on all sorts of classes. I could make sure that those variables were used correctly and that any functions called on those variables were valid for their class.

It was great, even if I do say so myself! In a couple of hours, I was able to test our entire codebase and ensure, recursively, that all of the variables that we were assigning to, passing as parameters, and calling functions on were being used in the right context.

And we found a heap of places where our attempts at porting went wrong. Which is what it's all about.

After that, I went nuts with static analysis. I started looking even at the queries that were being called and checking that when doing a SELECT COUNT, for example, that we used the queryOne function to just grab the value instead of returning it into an array to simply pull it out from $row[0].

I also wrote another sniff that ensured that when using references in a foreach loop, that we unset the by-reference variable before reusing it (so as to make sure we didn't accidentally overwrite part of the initial array).

I think using PHP CodeSniffer this way is awesome. It takes care of so much stuff and when adding in reflection on classes it's amazing what can be achieved in just a few lines of code.

I'd love to hear if you have ever solved a static analysis problem with PHP CodeSniffer!