I was chatting to my neighbour comparing how Canada differs from South Africa. I mentioned that triple glazing is very uncommon in South Africa. He answered that I should remember to sleep with the bedroom door open – because otherwise the CO2 levels can get too high while you sleep and make you sleep badly and we aren’t used to efficiently sealed ventilation.
I have no idea how true this is. It sounds plausible: you are cooped up for hours in a sealed room doing nothing (give or take) but breathing. I want to buy a CO2 logger and plot this to see if it really does rise to extreme levels. I also need to find out exactly what those extreme levels are.
But accuracy of the theory aside, his comment got me searching on the web, and it’s a fascinating rabbit hole to go down.
Some people who feel they have sleep disturbances due to CO2 buildup put plants in their rooms to help absorb the exhaled CO2. But I thought plants normally absorb CO2 during the daytime, and release small amounts of CO2 at night, photosynthesis and all that.
Crassulacean Acid Metabolism
Crassula capitella, “Poprosie” (directly translated: “doll’s rose” but generally called Campfire Plant in English). Photo by Eric Hunt
Certain plants are adapted to arid and hot regions and keep their stomata closed during the daytime to minimize water loss through transpiration.
They have adapted to open their stomata at night when it’s cooler and absorb CO2 during the dark hours. They then use this stored CO2 during the day without losing too much water.
There are arid regions in South Africa (Karoo, Fynbos regions) where there is a distinct herbaceous “early evening smell” around sunset – I wonder if it isn’t these plants that open up their stomata and we smell their “exhalation”.
The “absorption” of carbon dioxide and conversion into usable carbon compounds is called carbon fixation, and doing so in the dark is also called dark fixation. Dark fixation would also make an OK band name.
Carbon fixation as part of photosynthesis comes in three flavours: C3 and C4 (stomata open during daylight) and CAM (Crassulacean Acid Metabolism, stomata open at night).
The Crassulacean in CAM comes from the genus of succulent plants first used in the modelling of the CAM carbon fixation mechanism. Crassula is a genus of succulent plants common to South Africa. Most of them are beautiful. So by choosing the right South African plants you can absorb CO2 in your Canadian room at night.
How much CO2 do you breathe out per night?
An “average” (whatever that means) person will exhale roughly 0.013 cubic meters of CO2 per hour while sleeping.
Activity
CO2 exhaled per hour, m3/hour
Sleep
0.013
Resting, awake
0.020
Normal awake, light activity
0.080 – 0.130
Working, vigorously active
0.330 – 0.380
If you sleep eight hours let’s say the first hour counts as resting, giving us 0.020m3, and the other seven hours adds up to 0.0913, giving us a rough total of 0.111m3 CO2 per person per night.
For interest, (if I did my math right) that means 0.203 kg of CO2 (at 101kPa and 21⁰C), or since carbon dioxide weighs 44.009g/mol, about 4.6 mol of CO2 per person per night. That feels like a lot, especially considering how many of us there are.
Let’s call 0.111m3 (i.e. 111 liters) of CO2 one night-person of CO2.
How many plants do you need?
We need to know how much CO2 these plants can absorb on average. This is where I started running into Elsevier’s unethical paygates, so I will not directly link to these studies. One study on CO2 removal from hospital rooms did give me a rough indication of how much CO2 a plant can absorb.
The test room was 8m x 4.5m x 4m (i.e. 144m3). For the test it was filled to 400ppm CO2. Since 400ppm is 0.4% (or 1/2500) that gives us about 0.0576m3 of CO2 to begin with.
Five plants of various types and sizes were then left overnight during three separate tests and CO2 levels were measured afterward.
Bryophyllum pinnatum (Air Plant)
Astroloba congesta
Sedum rubrotinctum (Jelly Bean Plant)
Number of pots
5
5
5
Plant height, cm
30
25
30
Leaf area per plant (cm2)
864
384
400
CO2 after 10 hours in dark, ppm
388
263
368
“ppm” CO2 absorbed
12
137
32
Percentage drop
2.9%
34.3%
7.9%
m3 CO2 absorbed
0.00167
0.0198
0.00455
Night-persons absorbed after 10 hours, per 5 plants
0.015
0.178
0.041
Night-persons adjusted to 8 hours, per 5 plants
0.012
0.143
0.033
Conclusion: You need a fuckload of plants.
You’ll need 35 Astroloba congesta pots filled with 25cm high plants to absorb one night-person in eight hours. The study also highlighted that there are big variances in CO2 absorption between the different species. Since CO2 uptake would logically be related to the plant’s dry weight it would make sense to choose plants that grow fast.
To be fair we don’t really have to absorb every last CO2 molecule – just enough to keep it from rising to levels that disturb our sleep. Do we really know that it will improve our sleep? No – but I think aiming for 70 – 100 plants per room is a noble pursuit, so keep going. Why not live in a succulent forest?
It’s often beneficial to trigger a specific command when a file is updated. For instance, when I write stuff in LaTeX I like to keep the resultant PDF open on one of my monitors to immediately see changes (and to see when I invariably mess up).
This means that I need to rebuild the PDF on every save.
A timed watch (e.g. every 30 seconds) works, as does a Gulp task using gulp-pdflatex[1] – though gulp is a bit heavyweight for such a simple task; it just feels wrong to pull in 8MB of node modules to run gulp watch. And then you have a single case – a task that only works for pdflatex, too. I like a more general solution.
So to react on a save without having the weight of gulp I have a custom script that looks something like the following:
#!/bin/zsh
inotifywait -e close_write,moved_to,create -m . |
while read -r directory events filename; do
if [ "$filename" = "resume.tex" ]; then
xelatex resume.tex
fi
done
You’ll need inotify-tools (sudo apt install inotify-tools) if you don’t have it already.
You can check the detail of what is possible in the inotifywait man page. In short:
-e close_write,moved_to,create The events we watch for. Full list in the man page for inotifywait.
-m . monitor the current directory. Monitor also changes inotifywait from exiting after the first event (default behaviour) to run indefinitely.
read -r directory events filename; inotifywait outputs watched_filename EVENT_NAMES event_filename. In case of a watched directory a trailing slash is output for watched_filename
if [ "$filename" = "resume.tex" ]; then Make sure the file that changed is the file we are interested in
xelatex resume.tex Command we want to run on a change.
BTW, I originally had the inotifywait line something like:
The downside being that inotifywait dies when the file is completely overwritten and many editors do exactly that.
So it’s easier to set up a watch on the whole directory that you are interested in and filter for specific files as done in the snippet above.
For interest, the --format %e in the old command is to handle spaces in the watched filenames – it replaces the output with a user specified format (in this case %e , return as comma-separated list of events).
Tech CEOs: Your techies, and especially your tech management (who are hopefully some form of senior techies) need to regularly talk to your customers if you want a chance in hell of having a semi-valuable understanding of your strategic priorities.
Seriously. Isolating techies has no benefits, but I see it often.
Have you ever asked to view more ads, or would you prefer to skip ads and go for content? Do you forward through Game of Thrones to get to the ads?
Surprising answer: chances are that you have skipped content to willingly ask for ads. I’ll explain now.
It was aimed mainly at marketing professionals (about 5 technical types in the room, the rest were marketing people) but there was value for everyone.
You can read through a (rather bland, considering how energetic the talk was) bulleted list of highlights in the second link above, but the main takeaways for me are:
Mobile market is huge, growing explosively, and everything is accelerating.
Don’t eschew the low-tech stuff like SMS and MMS in favour of something more flashy – you might not need an expensive app.
Content drives better sales.
The mobile media (read: advertising) market has grown from $0 in 1998 to $236 billion in 2013. Ahonen puts this into perspective: this is bigger than Hollywood, Nollywood, Bollywood, the global radio industry, all of computer gaming, and the global music industry. Combined. Nothing compares. And this growth is accelerating.
Do you remember when everyone clicked that the Internet was going to be huge? OK, cynically, that insight drove the Internet bubble – contextless macro-scale insight cannot drive micro-scale innovation unless you plan well. We needed web 2.0 to mature a bit.
We are now at the point where everyone clicks that mobile will be huge, bigger than the internet. Already there are more mobile devices on the planet than there are people. And people keep these devices on their person, most (if not all) of the time. Are you planning for this?
Low tech mobile solutions like SMS and MMS get a boring rap – but Ahonen quotes research that puts SMS usage above Facebook use – among teens in the US, the so-called “facebook generation”. More interesting, though, are stats from a telemarketing survey in the UK:
89% of consumers would like to receive delivery notices via SMS, but only 26% have received any;
84% of consumers would like appointment reminders via SMS but only 29% have received any;
and 68% of consumers would like opt-in SMS offers from brands that they purchased from in the past three months, but only 12% have received any.
There is clearly a large gap between consumer attitudes on mobile content and what we make available, and the focus is too much on high tech and not on pervasive tech (like SMS).
Which brings me back to Ahonen’s question about asking for more ads. “Have you ever used the recommendation feature on Amazon?” he asked. If you have, then you have asked for more ads to be served, as a form of content. The recommendation feature drives 30% of Amazon sales! I certainly page through it regularly and have discovered amazing books through it.
The point is that it is advertising, but such good advertising that it becomes content. It’s valuable content, too, not just fluff.
Ahonen’s implicit message was: provide real value and on move away from the scatter-gun spam approach we see in mobile marketing. Instead, provide real value, but make sure it drives sales. Use existing, cheap tech to do so.
It is the story of two boys, Andrey (about fifteen) and Ivan (about twelve), whose father returns unexpectedly after a twelve-year absence. He takes the boys on a week-long “fishing trip” on a remote island, with tragic consequences.
The film can be read quite simply, as the visual representation of a diary that we see the boys keeping on the trip. The boys take turns writing in the diary, and the film swaps perspective at these times. The Return could be a simple recounting of a traumatic week in the lives of two boys, but it would be a mistake to read it this simply.
The first time we see the returned father is also clear confirmation that the film contains deep symbolism. The scene composition exactly echoes Mantegna’s Lamentation of the Christ.
Mantegna’s Lamentation of Christ and the opening scene with the father
We feel that the film tries to identify the father with a Christ-like figure beyond the obvious parallel with the painting – for example, the father wants his sons to call him otets (father), not papa (dad).
This introduction of the father is a bit of a surprise. In the preceding scene the two boys have a fight in an underground parking garage after which they run home to their mother, possibly to tell her and be comforted. She simply turns round, and answers “Quiet. Dad’s sleeping.” The boys find their returned father sleeping on a bed in the pose above. I think it telling that the boys then do not tell their mother about the fight – the returned father immediately changes the boys relationship with their mother. Suddenly, a fistfight, the epitome of male aggression, is no longer discussed with her.
The father’s return (and absence) is not explained to us or the boys. The family sits down to their first (and as it turns out, last) supper in twelve years, and the boys are almost immediately informed that they will be joining him on a fishing trip. Andrey, eager for his father’s approval, manages to seem excited, but Ivan cannot hide his misgivings about going on a trip with this stranger.
There is a lot the film doesn’t explain. This non-explanation does not feel manipulative (withholding information from the audience for the sake of tension) but tragic – the boys, like us, never receive an explanation either and we feel their confusion as they try to make sense of a changed world.
The photography is gorgeous, but the atmosphere of the film is bleakly foreboding. It’s a desolate and bare world: we see very few people other than the main cast, town scenes are grey road surfaces, empty communal spaces, bare buildings with washed-out blue or otherwise leaden skies. The country scenery is faded shades of grey and blue with cold skies. Scenes of great open spaces feel enclosing, limited, decaying and abandoned. The palette is consistent cold colours in desaturated shades.
There seems to be a suggestion that the film has themes of tests of manhood, of ascension to adulthood. The film opens as a group of (mostly pre-pubescent) boys, including Andrey and Ivan, climb a huge phallic tower. Andrey launches himself from this tower into the wide open water, but Ivan, scared of heights, cannot bring himself to jump. The boys abandon him, and he is stuck on top until, tellingly, his mother rescues him.
Interestingly Ivan is called a ninny or chicken by the other boys due to this – this leads to the fight with his brother as mentioned earlier, but I also think it is a reference to Ivan Durak – Ivan the fool, the ninny, the younger son of Russian folklore.
A stronger theme than tests of manhood is that we are seeing a depiction of a father who is not equipped to teach his sons how to become men. He is fundamentally broken himself and cannot deal with his sons nor instruct them on how to act in the real world. The film handles this with sympathy and compassion as well as a deep sense of sadness.
For example, the father gives Andrey his purse so that he can pay for lunch. Andrey is obviously proud of this new responsibility, but the purse gets stolen as they exit the restaurant. The father catches the thief, and brings him to the boys so that they can beat him up. The boys decline, and the father gives the thief some money for food before letting him run off. What lesson should the boys learn here? There is little that can be learnt other that the father is dangerously unpredictable and ill-equipped to navigate the waters between aggression and kindness.
The father watches a woman walk past the car in the car’s mirror, and all he sees is her hips, buttocks and legs. Ivan watches the same scene but he sees her face and smile. Before the father’s return Ivan was in a house with his brother, his mother, and grandmother. After his father’s return he is in a car with only male figures and the only woman they see gets objectified by the father.
The “fishing trip” is not a fishing trip – the whole trip is an excuse to for the father to go on some unnamed secret quest.
He doesn’t explain any of his plans – we just see him making mysterious phone calls, picking up items, and having unexplained irrational reactions to things. When the boys finally manage to catch a fish on the “fishing trip”, the father declines their offer of food, saying “I’ve eaten enough fish in my life”. I didn’t understand this comment – but a friend mentioned that this might mean that he has spent time in prison, as fish is Russian prison food.
What irrational behaviour we see from the father and what secrets are exposed to us are not as important as what we never see. It turns out that the father’s quest during the “fishing trip” is to retrieve a small box containing something secret, but we are never shown what this secret is.
The box holding the father’s secrets it is quite small. Throughout the film we see the father carrying what we imagine to be quite big secrets, including something that looks like a human body wrapped in cloth. In the end, none of these items are what we imagined them to be, and the dirty container of secret things that made him act so irrationally is objectively tiny.
Secrets that seem small, inconsequential, or easily forgivable to those not shamed by them can still be a source of great anguish to their keeper. The film shows this; the father’s secrets were hidden on a faraway uninhabited island, in a hole inside an abandoned house, in a bigger container, and then also locked in the small container. Whatever demon was inside may be small to us as outsiders but it terrified the father.
The fact that his box of secrets vanish along with the father is also important – we never quite know all the secrets or understand all the reasons for the behaviours we see in our parents, just as our children never will with us.
There is also something shown of the resilience of a child – the two boys find the hole the secret was hidden in, play in it, and in it get worms for their fishing attempts. They manage somehow to function within a broader dysfunction.
Even with a father unequipped to teach his sons how to become men the boys do, in fact, become men through some unnamed process. At one point their car gets stuck in the mud, and the father sarcastically tells his sons to use their “little hands” to put sticks under the wheels. They fail, and the father needs to do it himself to get the car unstuck. At the end of the film the boys’ “little hands” are adequate and they manage to push sticks under the body of their dead father, upon which the boys transport him to his final resting place.
The last time the film shows the (now dead) father we see him in the exact same pose as Mantegna’s Lamentation of the Christ. Thus linking the symbolically dead father figure at the beginning of the film with the corporeally dead father at the end of it.
The last scene of the father
The film addresses (and possibly questions) the value of aggression, along with the fear of own aggression, as being part of the standard definition of masculinity. Andrey is being beaten by his father and Ivan grabs a knife to defend his older brother. Ivan is absolutely terrified by his own aggressive response and runs off, climbing another huge phallic tower, from which he threatens to jump. The father falls to his death as he tries to save Ivan from jumping.
This, to me, is the tragedy of the film. Had Ivan murdered his father in defence of his brother it would have been a cleaner ending. But his father died while trying to make amends, and we see the tragedy of a broken parent trying their best to function in complex situations – something that in the film, as often in real life, leads to the child carrying tremendous guilt. There is a universality to this, as we are all someone’s child in some ways. From a narrative viewpoint this serves as a hat tip to Greek tragedy, but it’s a dark end to a bleak, but brilliant, film.
The ending credits show the photo diary the boys kept during the trip. The boys look happier than they do at any point during the actual trip – this suggests some distance from the trauma, some mellowing and insight. It is telling that the father is never shown in this last set of photos.
The credits has a wonderful photo of Ivan that shows the duality of this type of transformative experience. His face in the car window, half of it in the fresh air, the other half blurred by tear-like droplets on the window.
“But have you noticed the slight curl at the end of Sam II’s mouth, when he looks at you? It means that he didn’t want you to name him Sam II, for one thing, and for two other things it means that he has a sawed-off in his left pants leg, and a baling hook in his right pants leg, and is ready to kill you with either of them, given the opportunity. The father is taken aback. What he usually says, in such a confrontation, is “I changed your diapers for you, little snot.” This is not the right thing to say. First, it is not true (mothers change nine diapers out of ten), and second it reminds Sam II of what he is mad about. He is mad about being small when you were big, but no, that’s not it, he is mad about being helpless when you were powerful, but no, not that either, he is mad about being contingent when you were necessary, not quite it, he is insane because when he loved you, you didn’t notice.”
—The Dead Father, Donald Barthelme
Database: Percona XtraDB Cluster. Percona XtraDB is a higher-performance, backwards-compatible drop-in replacement version of the InnoDB storage engine for MySQL. The Percona XtraDB Cluster is the Percona Server setup with the Galera multi-master cluster libraries built in.
Load balancer: HAproxy. HAProxy is a very high performance TCP/HTTP load balancer.
We will be building three database nodes, connected to one load balancer. Something like this:
The steps:
Setup the machines
Install Percona XtraDB Cluster on the database machines
Configure the bootstrap node
Configure the rest of the cluster
Test the cluster
Daemonise the clustercheck script on the database machines with xinetd
Set the HAProxy load balancer up
Win
Create your servers
I used DigitalOcean for this tutorial, but use whomever you like.
Install Percona XtraDB Cluster on the database servers
Log in to each of your database servers and set up Percona XtraDB Cluster.
It will ask for a MySQL root password during each install. I kept them the same over the nodes.
Configure the bootstrap node
Choose a machine that will create the initial cluster. Forming an initial cluster is called bootstrapping the cluster.
Stop MySQL on that machine:
$ service mysql stop
(Create &) Edit /etc/mysql/my.cnf on the bootstrap node.
Add the following:
[mysqld]
datadir=/var/lib/mysql
user=mysql
# Path to Galera library
wsrep_provider=/usr/lib/libgalera_smm.so
# Empty gcomm address is being used when cluster is getting bootstrapped
wsrep_cluster_address=gcomm://
# Cluster connection URL contains the IPs of node#1, node#2 and node#3
#wsrep_cluster_address=gcomm://192.168.10.11,192.168.10.12,192.168.10.13
# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW
# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB
# This is a recommended tuning variable for performance
innodb_locks_unsafe_for_binlog=1
# This changes how InnoDB autoincrement locks are
# managed and is a requirement for Galera
innodb_autoinc_lock_mode=2
# Node #1 address
wsrep_node_address=192.168.10.11
# SST method
wsrep_sst_method=xtrabackup
# Cluster name
wsrep_cluster_name=my_ubuntu_cluster
# Authentication for SST method
wsrep_sst_auth="sstuser:s3cretPass"
wsrep stands for WriteSet REPlication, BTW. wsrep is a project that aims to develop a generic database replication plugin interface, and Galera is a specific wsrep provider.
Notice line 10, where the wsrep_cluster_address (gcomm://) address is empty. This will tell this node to bootstrap itself (create a new cluster) on startup. We will comment line 10 and uncomment line 13 later when the rest of the cluster is ready. You’ll notice that line 13 specifies all the other node addresses in the cluster – it is stricly speaking only necessary to specify one other node, but listing all of them is noted as a best practice in the Percona documentation.
Some notes about some of the settings wsrep_cluster_name is the unique name of your cluster. It should be identical on all nodes that form part of the same cluster.
wsrep_sst_method specifies the way a State Snapshot Transfer (SST) is taken. Cluster nodes are kept in sync through SSTs and ISTs (Incremental State Transfer). An SST is a full data copy from a donor node to a joining node, and an IST a way for a joining node to catch up through a transfer of partial state snapshots (writesets) from the donor node’s writeset cache.
The following SST options exist:
xtrabackup – uses Percona XtraBackup to take an SST. It’s useful because it doesn’t lock the donor database during an SST. We need to set up an SST user for xtrabackup, hence the values in wsrep_sst_auth.
rsync – uses rsync to transfer the relevant info. Donor node is locked as READ-ONLY during transfers.
mysqldump – uses mysqldump to take an SST. Also READ-ONLY locks the database during transfers. Needs a wsrep_sst_auth user, and that user needs MySQL root privileges.
You can also use a custom script to take an SST, but for our purposes we will be using xtrabackup.
Save the my.cnf file.
You can now start the MySQL service on the bootstrap node:
$ service mysql start
If all went well it should start up without a hitch.
We now need to add the SST user as identified in the wsrep_sst_auth directive.
Log into mysql on the bootstrap node:
$ mysql -uroot -p
Let’s check the node status first:
mysql> show status like 'wsrep%';
+-----------+-----------------------------------------------------------+
| Variable_name | Value |
+-----------+-----------------------------------------------------------+
| wsrep_local_state_uuid | 844b3ad2-29ca-11e3-8586-e72851d735f1 |
...
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
...
| wsrep_cluster_size | 1 |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
...
| wsrep_ready | ON |
+----------------------------+------------------------------------------+
40 rows in set (0.01 sec)
Looking good – this output shows a successfully bootstrapped node.
Add the wsrep_sst_auth user:
mysql> CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 's3cretPass';
mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT
ON *.* TO 'sstuser'@'localhost';
mysql> FLUSH PRIVILEGES;
You can use the MySQL root user for SSTs, but it’s not recommended.
Time to configure the other nodes and get them to join the cluster.
Configure the rest of the cluster
Log into the other database cluster nodes, stop their MySQL services, and edit their /etc/mysql/my.cnf files:
[mysqld]
datadir=/var/lib/mysql
user=mysql
# Path to Galera library
wsrep_provider=/usr/lib/libgalera_smm.so
# Cluster connection URL contains IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://192.168.10.11,192.168.10.12,192.168.10.13
# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW
# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB
# This is a recommended tuning variable for performance
innodb_locks_unsafe_for_binlog=1
# This changes how InnoDB autoincrement locks are
# managed and is a requirement for Galera
innodb_autoinc_lock_mode=2
# Node #2 address
wsrep_node_address=192.168.10.12
# Cluster name
wsrep_cluster_name=my_ubuntu_cluster
# SST method
wsrep_sst_method=xtrabackup
#Authentication for SST method
wsrep_sst_auth="sstuser:s3cretPass"
This cnf file differs from the bootstrap node’s cnf file in that line 10 (wsrep_cluster_address) specifies the cluster addresses. Line 26 has to be set to the node’s own IP address.
Repeat for the last node, changing line 25 and 26 to reflect the correct address.
Start MySQL:
$ service mysql start
You should see something like:
* Starting MySQL (Percona XtraDB Cluster) database server mysqld
* SST in progress, setting sleep higher mysqld
* Checking for corrupt, not cleanly closed, and upgrade needing tables.
That SST in progress line tells you that it connected to the server and is receiving an SST.
Once all the nodes are connected you can stop the bootstrap node’s MySQL service, comment line 10 in it’s my.cnf, and uncomment line 13 in it. Restart the MySQL service on it after saving the cnf file. Not fixing the wsrep_cluster_address directive on the bootstrap node will make it re-bootstrap itself on restart.
Test the cluster
All three nodes should now be running happily connected to each other. Let’s test some things.
Look at the cluster size:
mysql> show status like 'wsrep%';
+-----------+-----------------------------------------------------------+
| Variable_name | Value |
+-----------+-----------------------------------------------------------+
| wsrep_local_state_uuid | 844b3ad2-29ca-11e3-8586-e72851d735f1 |
...
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
...
| wsrep_cluster_size | 3 |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
...
| wsrep_ready | ON |
+----------------------------+------------------------------------------+
40 rows in set (0.01 sec)
Nice.
Try creating a database on one node, and running SHOW DATABASES; on another node. Should feel pretty cool to see the live replication 😉
Percona installs a script called clustercheck in /usr/bin/clustercheck. It’s a simple script that checks a node’s MySQL wsrep_local_state variable value and outputs HTTP 200 if the node is in sync, and HTTP 503 if it isn’t (or if it couldn’t connect).
It needs a MySQL user called clustercheckuser with password clustercheckpassword!. You can change this user, BTW – look for the following lines in /usr/bin/clustercheck:
Just change the default values there if you want to change the clustercheck user.
Add the clustercheck user to MySQL:
mysql> grant process on *.* to 'clustercheckuser'@'localhost'
identified by 'clustercheckpassword!';
mysql> flush privileges;
You don’t have to repeat user addition on the other nodes as it will propagate through the cluster by itself. Just remember to edit the clustercheck scripts on the other nodes if you are not using the default username and password.
Now run clustercheck:
$ clustercheck
HTTP/1.1 200 OK
Content-Type: text/plain
Connection: close
Content-Length: 40
Percona XtraDB Cluster Node is synced.
All our tests check out.
Daemonise the clustercheck script
We are going to set up the load balancer soon. But we need to find a way to make the load balancer aware that a node has gone out of sync. That’s why the clustercheck script exists.
To expose the clustercheck script we will be using xinetd. This is a daemon that listens on a port you specify, running a specific command once you connect to that port, and returning the output of the command to you.
Percona, nice people that they are, installs an xinetd config file for clustercheck as part of the server install. It should be located at /etc/xinetd.d/mysqlchk, and the contents probably look something like:
service mysqlchk
{
# this is a config for xinetd, place it in /etc/xinetd.d/
disable = no
flags = REUSE
socket_type = stream
port = 9200
wait = no
user = nobody
server = /usr/bin/clustercheck
log_on_failure += USERID
only_from = 0.0.0.0/0
# recommended to put the IPs that need
# to connect exclusively (security purposes)
per_source = UNLIMITED
}
As you can see, it specifies that xinetd should listen on port 9200, and return the output of /usr/bin/clustercheck if you connect to port 9200.
Edit /etc/services to allow xinetd to listen for this, I added the following line right at the end:
# Local services
mysqlchk 9200/tcp # mysqlchk
Install xinetd if it hasn’t been installed already:
$ apt-get install xinetd
If it has been installed already, just restart xinetd:
$ service xinetd restart
You should see it listening on port 9200 now:
$ netstat -ntlp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address ... State PID/Program name
tcp 0 0 0.0.0.0:3306 ... LISTEN 2877/mysqld
tcp 0 0 0.0.0.0:9200 ... LISTEN 3545/xinetd
tcp 0 0 0.0.0.0:22 ... LISTEN 786/sshd
tcp 0 0 0.0.0.0:4567 ... LISTEN 2877/mysqld
tcp6 0 0 :::22 ... LISTEN 786/sshd
If you telnet to the address you should see the clustercheck output:
$ telnet 127.0.0.1 9200
Trying 127.0.0.1...
Connected to 127.0.0.1.
Escape character is '^]'.
HTTP/1.1 200 OK
Content-Type: text/plain
Connection: close
Content-Length: 40
Percona XtraDB Cluster Node is synced.
Connection closed by foreign host.
Lather, rinse, repeat for the other database nodes.
Set the load balancer up
Log into your server that you want to use for load balancing, 192.168.10.10 in this example.
Install HAProxy
$ apt-get install haproxy
Now, edit /etc/haproxy/haproxy.cfg:
global
log 127.0.0.1 local0
log 127.0.0.1 local1 notice
maxconn 4096
uid 99
gid 99
#daemon
debug
#quiet
defaults
log global
mode http
option tcplog
option dontlognull
retries 3
redispatch
maxconn 2000
contimeout 5000
clitimeout 50000
srvtimeout 50000
listen mysql-cluster 0.0.0.0:3306
mode tcp
balance roundrobin
option tcpka
option httpchk
server db01 192.168.10.11:3306 check port 9200 inter 12000 rise 3 fall 3
server db02 192.168.10.12:3306 check port 9200 inter 12000 rise 3 fall 3
server db03 192.168.10.13:3306 check port 9200 inter 12000 rise 3 fall 3
The interesting detail to look at are lines 28-30, where HAProxy’s httpchk functionality is used to check for an HTTP 200 status on port 9200 of the cluster nodes – thus running the clustercheck script via xinetd, and taking a node out of circulation while it is not synced. It’s a better way to manage nodes than just checking if port 3306 is open.
The HAProxy service won’t start by default – as it has no sane default configuration – but now that we have provided a sane configuration we can set it to start. Edit /etc/default/haproxy, setting ENABLED to 1:
# Set ENABLED to 1 if you want the init script to start haproxy.
ENABLED=1
# Add extra flags here.
#EXTRAOPTS="-de -m 16"
Start HAProxy with
service haproxy start
You will probably get a warning about <debug> mode incompatible with <quiet> and <daemon>. Keeping <debug> only. You can remove this by editing /etc/haproxy/haproxy.cfg and removing the debug option and enabling the quiet and daemon options (lines 7 – 9). I found it useful to mess around in debug mode at first, though.
You can check that it’s running by either looking for the process:
Unexpected results – Host Blocked Errors
I had a problem the first time I set this up. I got Error Code: 1129 Host ‘192.168.10.10’ is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts’ after a while when connected to my cluster. I’m still not sure why it happened, or that my actions cured it; I do know it hasn’t happened again after adding the option tcpka (line 26: enabling TCP Keep Alive) to the haproxy.cfg file. Not understanding it feels a bit like magic. Hopefully someone more clued up can weigh in with some ideas. The MySQL manual notes that it happens after a certain amount of interrupted connection requests, hence my trying to keep the connections alive. Since the setting does nothing much for security it may make sense to set the max_connect_errors value in your MySQL configuration to some insanely high value.
Now that HAProxy is running (and you are hopefully not dealing with unexpected results) you should be able to connect through MySQL to your load balancer, and it should be round-robining the requests to your cluster nodes.
Congrats! Have fun.
PS: Possible Tweaks
Security can be improved a bit – I would suggest at least locking down xinetd to only allow connections from your load balancer (look at line 12 in /etc/xinetd.d/mysqlchk – the directive takes += as operator so you can list multiple IPs below each other).
You can also close ports on the cluster nodes to only allow MySQL and SSH access. The MySQL cluster needs 3306, 4444, 4567, and 4568 open, and SSH is typically running on port 22.
to the /etc/haproxy/haproxy.cfg file. Restart HAProxy, and browse port 8080 on your load balancer’s address. Remember to change Username and Password to values that you like for HTTP authentication.
HAProxy Stats – very colourful
Round-robining between the cluster nodes is an OK, but not great, strategy for the load balancer. If two nodes receive conflicting writes rollbacks can occur. It would be better to only write to a specific node at a time, failing to another node only if the active node goes offline.
You can achieve this by changing the haproxy.cfg file, lines 23-31:
listen mysql-cluster 0.0.0.0:3306
mode tcp
balance leastconn
option tcpka
option httpchk
server db01 192.168.10.11:3306 check port 9200 inter 12000 rise 3 fall 3
server db02 192.168.10.12:3306 check port 9200 inter 12000 rise 3 fall 3 backup
server db03 192.168.10.13:3306 check port 9200 inter 12000 rise 3 fall 3 backup
PPS: Further reading
Here are some of the articles I used to write this howto:
I’m in the process of finishing a spec for a new idea that we (i.e. Flickswitch) are working on (codename: RebelMoose, isn’t that a great name?). I was going though the latest wire frames, and wondered what guidelines I would like to see if I was the one implementing the ideas. So I wrote a few guidelines and put it in the spec.
These turned out to be good general principles, so here they are in full, copypasta from the spec, with only some project-sensitive details removed.
Wireframe from the spec with all the pretty ideas obscured, sorry
Polish Matters. What it says on the box. This idea has been tried before by others, so it better be good to differentiate it from the rest of the smelly herd. Give it a mani and a pedi at minimum.
Simple parts, clearly connected. Actions to be clearly labeled, consistent, with a clear call to action to move on to the next step. Steps already taken need to be reversible in all cases where it makes sense.
Don’t be clever. Be clear.
No surprises. Do the least work possible per action (keep background magic to a minimum and make sure it’s fully transparent and non-destructive). Always aim to do the least surprising thing.
No one true religion. People think in different ways. Provide multiple paths to salvation information.
Beautiful URLs. Love your routes or that .htaccess file – bring a lunch, stay for the day. URLs should always be copy-pastable. Where possible, make them pretty, but the most important thing is that they should contain all state information to recreate an exact same view when copied.
Idempotent refreshes. CTRL+R should not create a mess.
Concise communication. Chat to the user when stuff has been updated – using very short sentences (no-one reads anything, especially not on a screen, you are an exception) – but be informative. Be specific, too: “R 1000 deposited in savings account number 012345” is better than “Money deposited” as an alert.
Be visible when giving feedback. Alerts go above the fold, we colourbox form fields during validation, and keep the validation feedback close to the element that can be used to act on the feedback.
All alerts to be easily dismiss-able and should, where possible, auto-dismiss after, say, 20 seconds. But log them and provide an action log where they can all be viewed in reverse chronological order – like a mini-facebook feed for your RebelMoose life.
Validation feedback. Validation feedback needs to be actionable – if it isn’t then don’t provide the feedback.
Make it safe. Things like “still two steps until the crocodiles will be released” make things safe for the user. Don’t delete anything (set flags in the DB). Destructive / dangerous actions should spell out what they do and when it will happen (e.g. “when you click ‘Change Baby’ we will loosen the diaper clasps”).
Be snappy. Make something happen. Something needs to happen immediately after a user did something. This must not be dependent on connection speed – it needs to happen client side. It can be as simple as minor visual state changes.
Options – do you speak it? “OK / Cancel” are not grammatically correct options for “Yes / No” questions. Yes / No buttons are inferior to verb-based dialogs (because “Log out / Cancel” is clearer than “OK / Cancel”). Make sure the verb corresponds with the action you describe. Multi-word / made-up verbs are fine (“all nouns can be verbed”). Examples:
Terrible: “Do you want to withdraw R 100 from Savings?” -> [OK] [Cancel]
Not emetic: “Do you want to withdraw R 100 from Savings?” -> [Yes] [No]
Better, not perfect: “About to withdraw R 100 from Savings” -> [Withdraw] [Cancel]
Simplicity, revisited. Deliver substance. Say what you mean, briefly. Avoid using unnecessary ornamentation as a quick fix for something that could be better done by choosing UI parts more carefully.
Timelessness. If you can imagine a future where you or someone else would do a
specific thing better, do that better thing right now.
Solve the right problem. Be pragmatic. Don’t blindly follow accepted wisdom if a
pragmatic approach would lead to a more intuitive solution.
Be consistent. Our primary audience for the site will not be technical people – and since the new template eschews affordances* in favour of flat design we need to be especially consistent in order to imply meaning.
Consistent icons and icon placement. Always use similar icons in the same way throughout.
Consistent colour choices. Colour should convey semantic meaning. For example, green can work for forward / updated / success alerts, grey for back and cancel, bright blue for final step confirmation buttons, orange for warnings (need to be actionable), red for errors and failures, etc.
Rely on the user’s sordid past. They know certain dated and distasteful UI elements like old friends. Don’t make them relearn the controls for novelty’s sake. Form elements to be standard-ish. Radio buttons, specifically, to look like radio buttons, not like toggle buttons. Switches can be used for on – off stuff, just make sure the semantics makes sense. Clarity before beauty, conventional usage before novelty. Remember: Be clear, not clever.
Badges and icons can be useful stand-ins for affordance in flat design. Use accordingly to give info about how controls can be used, but very sparingly. Again – always be consistent, e.g. if one cancel button gets an undo icon, then all cancel buttons should have one – so don’t go mad with these things.
Just some principles to help you turn wire frames into reality. Nothing can tick all the boxes – but this is helpful in compromise situations. I expect I’ll be adding to this list in my head for a long time.
*Affordance: Originally a psychology term, but is used in the field of Human-Computer Interaction design in a different way. I use Don Norman’s definition from The Design of Everyday Things:“…the term affordance refers to the perceived and actual properties of the thing, primarily those fundamental properties that determine just how the thing could possibly be used. […] Affordances provide strong clues to the operations of things. Plates are for pushing. Knobs are for turning. Slots are for inserting things into. Balls are for throwing or bouncing. When affordances are taken advantage of, the user knows what to do just by looking: no picture, label, or instruction needed.”.
Gerrie Swart 