MySQL and MariaDB innodb_file_per_table

For those of you pushing your MySQL instances to higher levels, you’ll have realized at some point that all InnoDB tables share their indexing space into a single file (ibdata1, usually).

This has several very bad outcomes:

• the ibdata1 file becomes huge after some time (we’ve seen it pass the 8GB bar)
• starting the MySQL server takes longer
• deleting an InnoDB table *doesn’t* reduce the space used by ibdata1
• you have to shutdown your server, remove ibdata1 completely, then restart the server in order to reinitialize the ibdata1 file to a 0-size file (but this is, of course, difficult or dangerous to do in a high-availability context)

I just found there was a setting called “innodb_file_per_table” since MySQL 5.1 at least:
http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_file_per_table

There is an explanation on what the advantages and disadvantages of the method are (but as you might have guessed, the main disadvantage of using separate files is that you have more files, taking a little more time to do I/O operations when you scan them all – and of course retro-compatibility with previous versions of MySQL)
http://dev.mysql.com/doc/refman/5.1/en/innodb-multiple-tablespaces.html

This seems to be available in MariaDB as well.

Using Chamilo juju charm to setup a dev environment on Digital Ocean

If you’re in a hurry/on speed, know this:

• this procedure is slightly more difficult (so longer) than installing the charm on Amazon
• you can skip directly to “Installing Juju”
• if you already have juju installed, you can skip to the last 2 lines of the “Installing juju” section
• if you already have juju-docean installed and configured, you can skip directly to “Provisioning VMs”
• otherwise, just continue reading, it’s worth a few minutes…

This tutorial regroups a lot of advanced notions, so if you want to know more about one of the following elements, please follow these links:

• Juju
• Ubuntu
• Juju Charm
• Juju Charm for Chamilo
• Digital Ocean
• Juju for Digital Ocean

Before anything else, please note that the following is highly experimental. There are still a series of issues that should be worked out in order to make this process failproof.

Basic setup

Before we start using commands and stuff, you’ll have to note the following:

• We are using a Chamilo Charm developed by José Antonio Rey (kudos to him) as a voluntary contribution to the project
• Charms are configurations to install applications (and stuff) inside the Juju framework
• The Juju framework is developed by the Ubuntu team, so we’re using an Ubuntu (14.04) desktop (or in this case laptop) to launch all the following
• Digital Ocean is one cloud hosting provider, which is particularly cheap and good for development purposes. The “default” environment for Juju is Amazon, so we’ll have a few additional steps because of this choice. The Digital Ocean plugin to Juju is developed by geekmush on Github, and as far as I know he is not related to either Ubuntu nor Digital Ocean, so he is also worth praising for his contribution
• Chamilo requires a web server and a database server. In this Charm, it is assumed that we want both of these on separate virtual machines, so you will need two of them (unless you change the parameters a little)
• Juju is written in Go but relies on several Python libraries. As such, you’ll have to have python installed on your system and maybe Juju will shout because it is missing a few dependencies. Notably, I installed python3-yaml to avoid a few warnings (it is required for the following, although the installer for Juju says it’s optional)

Installing Juju

On a default Ubuntu desktop installation, you’ll have to install Juju first. Because we are going to use Juju connected to Digital Ocean, we need a recent version of Juju, so let’s add it the unconventional way (with the ppa), launching the following on the command line:

sudo add-apt-repository ppa:juju/devel
sudo apt-get update && apt-get install juju
juju version

For some reason, in my case, this created my home directory’s .juju/ folder with root permissions, which then prevented me to reconfigure my environment (requirement for the Digital Ocean Juju plugin), so I changed permissions (my user is “ywarnier”, so change that to your user):

sudo chown -R ywarnier:ywarnier .juju

Then we need to install the juju-docean plugin:

sudo apt-get install python3-yaml
sudo pip install -U juju-docean

Setting up Digital Ocean access

Now we need to configure our Digital Ocean (D.O.) API so the system will be able to call D.O. in our place and create instances (and stuff).

You first need to grab your API key, client ID and SSH key ID from the Digital Ocean interface. You can do that from the Digital Ocean API page. Obviously, you need a Digital Ocean account to do this and a few bucks of credit (although you can get $10 free credit from several places). If your API key says “Hidden”, that’s because you must have it stored somewhere already (for other services?). If you don’t, you’ll have to re-generate one. Your SSH key ID is the name you gave to the SSH key you use from your computer to connect to your new instances. If you don’t have it, that’s probably because you haven’t configured any. Please do in the “SSH Keys” menu item on the left side of your D.O. panel.

 export DO_CLIENT_ID=aseriesof21alphanumericalcharacters
 export DO_SSH_KEY="user@computer"
 export DO_API_KEY=aseriesof32characters

Setting up the Digital Ocean Juju environment

Now we need a bit of manual config to be able to use Digital Ocean (last bit, promised). Edit the ~/.juju/environments.yaml file and paste the following:

environments:
 digitalocean:
 type: manual
 bootstrap-host: null
 bootstrap-user: root

Just a note: the “type: manual” line implies it is a bit more complicated than on amazon later on, and we will have to launch a few more commands to provision new machines *before* we deploy Chamilo.

Generating the Juju environment

Now we’re going to create our Juju controller. The Juju controller can be an independent Virtual Machine (VM), or it can be the same as the one on which you will deploy Chamilo. It all depends on your budget and your requirements.

juju docean bootstrap --constraints="mem=1g, region=nyc1"
  2014/06/22 11:50.24:INFO Launching bootstrap host
  2014/06/22 11:51.29:INFO Bootstrapping environmen

Note that we took a decision to use a 1GB (RAM) VM here (mem=1g), in a datacenter in New York (region=nyc1). For the record, I tried creating them in nyc2, which is also a valid D.O. datacenter, but it failed miserably (sometimes not creating the VM, sometimes creating it without IP, sometimes creating it fully, but in the end never returning with a proper success response for my environment to be created), so sticking to nyc1 is probably a reasonable time-saver.

Provisioning VMs

To be able to deploy Chamilo, we’ll use two VMs: one for the web server and one for the database

juju docean add-machine -n 2 --constraints="mem=1g, region=nyc1"
2014/06/22 12:44.59:INFO Launching 2 instances
2014/06/22 12:46.42:INFO Registered id:1908893 name:digitalocean-8d14c9bc671555ff872d8d6731f84d68 ip:198.199.82.172 as juju machine
2014/06/22 12:49.08:INFO Registered id:1908894 name:digitalocean-a9ba29cfe55549f58e5f7e365199c5ed ip:208.68.39.19 as juju machine

Now, the “-n 2” above allows you to create these 2 instances, but you could also launch 2 different instances of different properties, doing it one by one. In our case, I suggest you use version Trusty of Ubuntu for the MySQL machine, to avoid a little bug in the Precise version of the charm:

juju docean add-machine --constraints="mem=2g, region=nyc1"
juju docean add-machine --series=trusty --constraints="mem=1g, region=nyc1"

The important thing here being that you can later identify the machine itself by a simple ID, using juju status:

juju status
environment: digitalocean
machines:
 "0":
  agent-state: started
  agent-version: 1.19.3
  dns-name: 192.241.142.154
  instance-id: 'manual:'
  series: precise
  hardware: arch=amd64 cpu-cores=1 mem=994M
  state-server-member-status: has-vote
 "1":
  agent-state: started
  agent-version: 1.19.3
  dns-name: 198.199.82.172
  instance-id: manual:198.199.82.172
  series: precise
  hardware: arch=amd64 cpu-cores=1 mem=994M
 "2":
  agent-state: started
  agent-version: 1.19.3
  dns-name: 208.68.39.19
  instance-id: manual:208.68.39.19
  series: trusty
  hardware: arch=amd64 cpu-cores=1 mem=994M

If you made a mistake at some point or just wanna try things out, you can destroy these instances with

juju docean terminate-machine 1

where “1” is the ID of the machine, as shown above before each of them.

Deploying Chamilo

Now we’ve got our machines, we just need to deploy the Chamilo Charm and the MySQL Charm (you need MySQL to run Chamilo):

juju deploy cs:~jose/chamilo --to 1
juju deploy mysql --to 2

Please note that the “–to n” option is to specify on which machine you want to deploy the selected service.

Now, we need to configure Chamilo a little. We’re going to give it a domain name (you’ll have to redirect this domain name to the IP of the first machine – the one with the Chamilo service – in order to use it when ready) and a password for the “admin” user (the user created by default):

juju set chamilo domain=test.chamilo.net pass=blabla

Now we still need to tell Juju to link the Chamilo service with the MySQL service:

juju add-relation chamilo mysql

And finally, apply all the above and expose the chamilo service to the public:

juju expose chamilo

If something goes wrong with a service, you can always remove it with:

juju destroy-service chamilo

You can replace “chamilo” by the service with which you are having the issue, of course. If that doesn’t work out, you can always remove (terminate) the machine itself (see above).

Useful tricks

You can connect at any time to any of your virtual machines through the command

juju ssh chamilo/0

where “chamilo/0” is the name appearing below “units” in your services.

You can check the status of all your instances with

juju status

Note that, sometimes, you might end up with dozens or hundreds of instances. In this case, it won’t be as practical to show the status of all instances (I have no solution for that now, but I’m sure there is a way to filter the results of a juju status).

You can launch a command on the virtual machines’ command line like this:

juju run --service chamilo "tail /var/log/juju/unit-chamilo-0.log"

This way, you are actually executing the command remotely and getting the results locally.

You can also see the error log locally, connecting in SSH (first) and then launching:

 tail /var/log/juju/unit-chamilo-0.log

Obviously, that gives you a little more flexibility.

Notes about unexpected errors

One of the “silent” things is that Juju considers the default machine to be Ubuntu Precise. In the case of MySQL, the default Charm is configured for Trusty. This means that if you want to install this package, you need to install a virtual machine in Trusty. Otherwise, you might get some other issues. In my case, the Precise Charm didn’t really work (missing yaml), so I decided to go for Trusty.

You can choose the distribution of your machine with –series=trusty, for example:

juju docean add-machine --series=trusty --constraints="mem=2g, region=nyc1"

We tested the chamilo charm relatively extensively.

Unmounting the whole thing

If this was just a test, and you’re happy, maybe you want to remove everything. If so, the quickest way to do that is to launch a destroy-environment command, but you will first need to destroy each machine and, before that, each services that :

juju destroy service chamilo mysql
juju destroy machine 1 2
juju destroy-environment digitalocean

This should reasonnably quickly remove the whole setup.

You should still check your Digital Ocean’s dashboard, though, as apparently it doesn’t always delete the nodes you created with Juju…

Quick commands list for the impatient

Assuming you’re running Ubuntu 14.04 and that you know which values to change in the commands below:

sudo add-apt-repository ppa:juju/devel
sudo apt-get update && apt-get install juju
sudo chmod -R 0700 .juju
sudo apt-get install python3-yaml
sudo pip install -U juju-docean
export DO_CLIENT_ID=aseriesof21alphanumericalcharacters 
export DO_SSH_KEY="user@computer" 
export DO_API_KEY=aseriesof32characters
juju docean bootstrap --constraints="mem=1g, region=nyc1"
juju docean add-machine --constraints="mem=2g, region=nyc1"
juju docean add-machine --series=trusty --constraints="mem=1g, region=nyc1"
juju deploy cs:~jose/chamilo --to 1
juju deploy mysql --to 2
juju set chamilo domain=test.chamilo.net pass=blabla
juju add-relation chamilo mysql
juju expose chamilo

And connect your browser to test.chamilo.net (that you must have redirected to the corresponding IP first) and login with admin/blabla.

Embedding webcam with SimpleScreenRecorder

In case, like me, you use SimpleScreenRecorder on Ubuntu to do screencasts, you might be missing something: the ability to embed your webcam’s feed *inside* the screencast (like this little capture on the right side which shows how amazed I am).

Well, there’s a simple, command-line based solution to that, as explained by Maarten Baert, developer of SimpleScreenRecorder: just use one of the 3 following commands (for me, the last one is the only one that works and I haven’t searched *at all* for why the others don’t. I just copy-pasted it.

ffplay -f v4l2 /dev/video0
avplay -f v4l2 /dev/video0

mplayer tv:// -tv driver=v4l2:width=640:height=480 -vo xv

Obviously, for the last one, you can change the resolution just by changing the number there.

You can check the details here: https://github.com/MaartenBaert/ssr/issues/64#issuecomment-27766079

SimpleScreenRecorder is not inside the default Ubuntu packages, so you’ll have to add it to your sources following the procedure in the “Download” paragraph here: http://www.maartenbaert.be/simplescreenrecorder/#features

Correo de lectores – Desarrollo de modelo de negocios

Un lector nos escribe:

“Queria hacerte una pregunta sobre los modelos de comercializacion para el chamilo, ya que conozco una compañia de un amigo, que quiere distribuir chamilo pero no bajo la licencia GPL. Queria saber si la empresa BeezNest, tiene algun modelo de comercializacion alternativo para el Chamilo tipo open source, licencia BSD o alguno similar en donde el cliente pague una licencia por el uso del sistema y que el codigo fuente sea opcional entregarlo.”

Mi respuesta:

“Hola estimado,

Chamilo *no se puede* distribuir bajo ninguna otra licencia que GNU/GPLv3 o superior. Es una infracción legal tratar de hacerlo de otro
modo, así que te recomiendo sugieres a tu amigo verificar sus derechos según la licencia.

Por lo tanto, tratando de modificar este modelo, tu amigo se transformaría automáticamente en un enemigo del proyecto Chamilo, lo que dudo que quiera hacer. Si desea desarrollar un modelo de este tipo, deberá apuntar a soluciones no libres, tipo e-ducativa, Blackboard, Desire2Learn, e-Doceo, etc. No se puede obtener los beneficios de un producto de software libre (por ejemplo en este caso actuar de manera independiente) y al mismo tiempo los de un producto de software privativo (en este caso vender la licencia).

Queda terminadamente prohibido por la licencia. Eso es para el aviso importante (me hizo abrir los ojos bien grandes lo que leí).

De otro lado, el proyecto Chamilo es un proyecto de software libre radical: no acepta ninguna parte que no esté bajo licencia de software libre. Acepta la inclusión de otras licencias consideradas de software libre y compatibles con GNU/GPLv3 según la Free Software Foundation, pero su calidad surge de esta visión radical: la educación no puede estar frenada por asuntos comerciales!

Aun así, BeezNest basa su modelo de negocio sobre Chamilo gracias a la provisión de servicios de distintos niveles a sus clientes, los cuales
nunca impactan el proyecto software mismo de manera negativa:

* soporte
* instalación
* capacitación
* desarrollo (la totalidad de los útiles para el público se contribuye al proyecto)
* consultoría
* diseño e implementación de infraestructura
”

Y tu, que opinas? La parte legal es estricta, pero te parece que Chamilo es demasiado radical?

Grabar secuencia de navegación web para pruebas de carga con JMeter

Este vídeo es excelente para mostrar lo que se tiene que hacer para grabar una secuencia de navegación de un usuario en un sitio web (cualquiera) con JMeter.

Entonces los pasos son:

• Crear un grupo de hilos
• Crear un grabador HTTP
• Iniciar un servidor proxy
• Iniciar su navegador con configuración para usar este proxy
• Navegar en el sitio deseado

Nota: he tenido malas experiencias tratando de hacer este procedimiento en HTTPS. Parecía como si no funcionara correctamente, pero no pude investigar mucho más.

El Consejo de Estado Francés recuerda el derecho de las comunidades locales a elegir software libre

Fuente: http://www.april.org/le-conseil-detat-rappelle-le-droit-des-collectivites-locales-de-choisir-du-logiciel-libre

El 30 de septiembre 2011, el Consejo del Estado Francés dió una decisión sobre los mercados de provisión de servicios en software libre1. Este decreto subraya la diferencia entre los mercados de servicios y los mercados de provisión y confirma por la jurisprudencia la posibilidad de los actores públicos de optar por el software libre.

En este caso, la región de Picardía había decidido poner en marcha el software libre Lilie para los espacios numéricos de trabajo de los liceos de la región. Había, posteriormente, lanzado una licitación pública para la puesta en marcha, la explotación, el mantenimiento y el hosting de una plataforma de servicios de este entorno de trabajo. Esta decisión había sido atacada por dos empresas, argumentando que la elección de un software libre específico antes de la redacción de la licitación pública para los servicios malograba el principio de libre competencia.

El Consejo de Estado Francés ha dado razón a la Región de Picardía, recordando que, para software libre, no hay mercado de provisión de software, ya que la región ha podido “gratuitamente y libremente” obtenerlo: no hay juridicamente una compra (ya que el software es gratuito), por lo tanto no hay mercado. En otras palabras, y es el primer aporte de esta jurisprudencia, las colectividades locales pueden elegir libremente software libre y usarlos sin pasar por un mercado de provisión de software.

El Consejo de Estado Francés aprovecha luego para subrayar que las libertades del software libre permiten la competencia entre los prestatarios de servicios, y que por lo tanto el mercado de provisión de servicios para el despliegue y la adecuación del software responde a todas las exigencias de igualdad entre los candidatos. En efecto, y segun los términos mismos del Consejo de Estado Francés (ndlr: texto traducido del original),

« En lo que se refiere a su caracter de software libre, [este software] estaba libremente y gratuitamente accesible y modificable por el conjunto de empresas especializadas […] que estaban por lo tanto todas en capacidad de adecuarlo a las necesidades de la colectividad y de presentar una propuesta indicando las modalidades de esta adaptación. »

Los jueces del Palacio Royal precisan luego que, por consecuencia, hasta una empresa co-propietaria del software no tien ventaja de competencia indebido para responder a la licitación, lo cual es, por lo tanto, perfectamente legal.

Esta decisión conforta el análisis de la April sobre los mercados públicos informáticos2 : la elección de un software libre puede ser hecha librement por las colectividades, ya que las libertades del software no se limitan por la competencia a continuación. La April recuerda que estas libertades se traducen igualmente en otras ventajas para las colectividades locales, ya que permiten evitar encerrarse tecnológicamente y garantizan la perenidad de los datos.

Un análisis más detallado de las consecuencias de esta decisión será publicada próximamente (en el sitio de April).

BeezNest fixes new Chamilo security flaws in 48h

Secunia gave the Chamilo Security team full details of three security flaws detected in version 1.9.4 on Monday the 4th of March 2013. These flaws were detected by Fernando Muñoz, a regular contact for Chamilo in terms of security. We are very grateful for his and Secunia’s work.

Within less than 48 hours, the Chamilo Security team, part of BeezNest‘s development team, has reviewed, patched, published and communicated a fix to its community through the Chamilo wiki and Chamilo Security team’s Twitter account, doing its best effort to inform everyone in time.
In fact, a patch was available within 12h, but it took us some time to go through the right communication channels and make the patch accessible to the greatest number.

Our fast turn around time on this security issue is a reflection of the priority and focus we place on security. Security is more than a side item for us, it’s part of our core principles.

At Chamilo, we embrace security research and show our appreciation to all involved. Thank you for helping us make Chamilo better!

BeezNest offers hosting, development and maintenance services for any Chamilo LMS portal. Feel free to contact us for a quote if you need help. Chamilo is free software and you are absolutely not forced to use our services. We can help you out if you need it, that’s all!

More free software and open source good news from Europe

On the line of my latest post about Linagora, a European data researcher recently published the results of a study concluding in that the European Community is saving 450 billion euros per year thanks to Open Source software.

In the same orientation, the APRIL (French Association for digital freedom) published a study reporting the substantial savings of the French Government thanks to Open Source and Free Software.

These are all good news that have taken too many years and financial crisis to come, but let’s rejoyce in their presence at this point.

Munin 2.0 on Debian

Munin 2.0 has been released and packaged for Debian, and even backported to Squeeze (from backports.debian.org).

Even though there are still some quirks in this version (or just the Debian packaging), it is far better (more scalable, more powerful and prettier) than version 1.4.

Basically, the following article should cover it all: http://munin-monitoring.org/wiki/CgiHowto2, but doesn’t quite achieve it, so far.

Let’s see together how to install it successfully on Debian Squeeze. I will however not cover the agent (Munin Node), as there is no significant difference between basic installation of its 1.4 and 2.0 versions.

As a first significant performance improvement, Munin is now able to use RRDcached (it fairly reduces the disk I/O pressure on RRD files), and it is fairly easy to setup. Just install package rrdcached (who would have guessed?), then add the following options to OPTS in /etc/default/rrdcached:

OPTS="-s munin -l unix:/var/run/rrdcached.sock -b /var/lib/munin/ -B -j /var/lib/munin/journal/ -F"

This will override its defaults. And of course, restart then the daemon.

Adapt /etc/munin/apache.conf to your likings, in this case, we are going to uncomment all cgi and fastcgi-related blocks.

Install packages libapache2-mod-fcgid and spawn-fcgi, then download the following script and install it as an initscript on your system (e.g. as /etc/init.d/spaw-fcgi-munin-graph and running insserv):

http://files.julienschmidt.com/public/cfg/munin/spawn-fcgi-munin-graph (though this version is still buggy and quite fragile, contact me for a slightly improved version)

apt-get install libapache2-mod-fcgid spawn-fcgi

Add user munin and www-data to group adm, and allow group adm to write to /var/log/munin/munin*-cgi-*.log:

adduser munin adm
adduser www-data adm
chmod g+w /var/log/munin/munin*-cgi-*.log

Add user www-data to group munin and the opposite:

adduser www-data munin; adduser munin www-data

Start the spawn-fcgi-munin-graph service and check it is indeed running.

Enable the fcgid and rewrite Apache modules and restart the Apache2 service.

Customize /etc/munin/munin.conf to your likings, enabling the (Fast)CGI parts.

Whenever monitoring more than a single host, I recommend moving (i.e. commenting and copying) the localhost definition to some new /etc/munin/munin-conf.d/ file per domain (e.g. beeznest.conf), and add your hosts there, with a meaningful domain name.

Nginx Anti-DOS filter for Fail2Ban

We are currently trying out this Fail2Ban rule on one of our server, to block simple (but very upsetting) DOS attacks on Nginx automatically (after 30 seconds).

New filter in /etc/fail2ban/filter.d/nginx-dos.conf:

# Fail2Ban configuration file
#
# Generated on Fri Jun 08 12:09:15 EST 2012 by BeezNest
#
# Author: Yannick Warnier
#
# $Revision: 1 $
#

[Definition]
# Option:  failregex
# Notes.:  Regexp to catch a generic call from an IP address.
# Values:  TEXT
#
failregex = ^<HOST> -.*"(GET|POST).*HTTP.*"$

# Option:  ignoreregex
# Notes.:  regex to ignore. If this regex matches, the line is ignored.
# Values:  TEXT
#
ignoreregex =

In our jail.local, we have (at the end of the file):

[nginx-dos]
# Based on apache-badbots but a simple IP check (any IP requesting more than
# 240 pages in 60 seconds, or 4p/s average, is suspicious)
# Block for two full days.
# @author Yannick Warnier
enabled = true
port    = http,8090
filter  = nginx-dos
logpath = /var/log/nginx/*-access.log
findtime = 60
bantime  = 172800
maxretry = 240

Of course, in case you would be logging all resources of your site (images, css, js, etc), it would be really easy to get to those numbers as a normal user. To avoid this, use the access_log off directive of Nginx, like so:

 # Serve static files directly
        location ~* \.(png|jpe?g|gif|ico)$ {
                expires 1y;
                access_log off;
                try_files $uri $uri/ @rewrite;
                gzip off;
        }
        location ~* \.(mp3)$ {
                expires 1y;
                access_log off;
                gzip off;
        }
        location ~* \.(css)$ {
                expires 1d;
                access_log off;
        }
        location ~* \.(js)$ {
                expires 1h;
                access_log off;
        }

We’ll see how that works for us… (and report here)