Tuning MySQL

Tags

, , ,

MySQL is so abundant in web stacks you’re bound to encounter it at some point. One of the weaknesses of it is the lack of tools and metrics to monitor things like memory usage. It’d be lovely to just see a graph of how much of your database, buffers etc are in memory. The default my.cnf files that ship with MySQL are a little, let’s say general for most circumstances. Two tools that I’ve found helpful in getting MySQL sized correctly are:

MySQL Tuning Primer from https://launchpad.net/mysql-tuning-primer

MySQL Tuning Primer is a script that you run against your database which will inspect it’s running state and make suggestions for improvements to your MySQL configuration.

The other tool is the MySQL Configuration Wizard from Percona

This is an online tool that takes you through a set of questions to determine an appropriate set up for your needs.

Both tools can produce a custom my.cnf file but you’ll need to use your own judgement to choose the correct settings.  Tuning MySQL is an iterative process: find a setting to tweak, understand what it does, shift it, check the monitors to see if you’ve had a positive change, repeat.

Getting as much of your database in memory is going to give you the best performance but as with all scaling or performance tasks you need to look at your whole system and work out what’s the best thing to tackle first. As a case in point I recently had a simple page that could take 7 seconds to load. I assumed that the problem was with the database and went about sticking my fat fingers in. When I took a step back and actually stuck a profiler on it, yes there was a slow query in there but once it was cached it was actually pretty quick. The most time spent was actually processing the huge (and unnecessary) amount of data that came back. Once I’d sorted out the code there were no more slow queries and the page was snappy again.

Long story short: Find out where your problems actually are and work out how to fix them.

MySQL Tuning Primer and MySQL Configuration Wizard are two simple tools if your problem is a MySQL server that isn’t using its memory particularly well.

Advertisements

How to check your MySQL my.cnf file

Tags

,

It’s always a good idea to check your config files before you throw them at your live servers. You can parse your MySql my.cnf file by running this at the command line:

mysqld --help --verbose

It’ll pick up you my.cnf file in the default locations and try to interpret all the values, but it won’t actually load them. If anything’s not right it’ll error out with an explanation. If everything’s good you’ll see a table with the configuration that will get loaded.

Generate a CSR

Tags

, , ,

If you want to run your web server over a https connection you’ll need an SSL certificate. Which involves creating a private key, generating a certificate signing request (CSR) and they getting an authority to sign your certificate.

Generating a new key and CSR can be achieved with openSSL with this line:

openssl req -new -newkey rsa:2048 -nodes -keyout domain.key -out domain.csr

Replace domain.key and domain.csr with the name of your domain just to keep things in order. You’ll be asked a few questions which should be straight forward. The country code needs to be the official ISO standard . The Common Name needs to be the domain you wish to use the certificate for. The domain not the host.

Once it’s complete you should have two new files. The contents of the .csr file can be sent to your certificate authority for signing. Keep your private key safe and don’t allow of to become public. You’ll need to look up the specifics of how to install your certificate on your particular web server.

Grep your little friend

Tags

, , ,

The Linux command “grep” is so useful and simple, if you’re not sure what it does you should take two minutes to have a look.

Essentially it allows you to search a line of text for a particular string and print out the line if a match is found. You could use grep to look through text files, log files or datasets to look for relevant lines. For example, if you have a log file of web server access you could use something like:

grep 404 access.log

to find lines that include 404. Obviously this is overly simplified but you get the idea. Grep can take regex expressions as well so you could search for log events that occur during a specified time period.

Using the pipe | operator you can pass the output of one operation into grep for further processing. For example, searching the file system. To find all places in the file system that have the php.ini file.

find / | grep php.ini

To find processes with the name mysql:

ps -A | grep mysql

In short grep allows you to filter any list of text down to just the lines you’re interested in.

Grep man page

Website Deployment with Git

Tags

, , ,

I’ve recently started to experiment with deploying sites directly from a git repository. Up until now my current main deployment method has been based on a handmade script which involves rsyncing code up to the live server. I’ve been using git for source code management for sometime time now and for the most part I get along well with it. Particularly how I can easily create and work with branches for different feature developments and merge them back into the main release when they’re ready.

As with a lot of things with git there’s multiple ways of achieving what you want.  After trial and error I’ve settled on this method . It works for me because it’s straight forward and keeps the git specific stuff out of the website document root. It also means I can update the remote server directly from local machine, which is handy for a development server where you might be making lots of changes and keeping the number of steps to a minimum really helps the workflow.

So what’s actually involved?

The basic idea is this. I have a repository, lets call it “local”. I also set up a repository on the remote webserver, lets call it “webserver”. I tell my local repository about webserver, i.e add it as a remote. In the webserver repository I add a post-receive hook that will checkout the latest version of code to my webroot when it receives a new push from another machine. What this gives me is the ability to update my remote website code by just pushing to the repository. Of course you’ll have to decide for yourself if you want this behaviour. On a live site you may want to be very explicit about when the site gets updated. But for small teams or development sites I think it works well. You’ll need to be able to ssh into your remote site and have git set up on your local and remote server.

What to do

The steps you’ll need to take are in the link above but I’ll repeat them here for convenience.

On the remote (webserver) set up a new bare repository. N.B. My directory structure is:

/var/www/www.domain.name (folder for all files for a site)

/var/www/www.domain.name/public_html (web root of site, where web server looks for files)

/var/www/www.domain.name/git (git repository files)

/var/www/www.domain.name/nondeployed (folder for items that are specific to server and not deployed e.g user uploads)

On the remote “webserver”

$ cd /var/www/www.domain.name 

$ mkdir git 

$ cd git/ 

$ git init --bare 

$ git --bare update-server-info 

$ git config core.worktree /var/www/www.domain.name/public_html

$ git config core.bare false 

$ git config receive.denycurrentbranch ignore 

$ cat > hooks/post-receive

#!/bin/sh

git checkout -f

^D

$ chmod +x hooks/post-receive

On your local repository

If you’re using a GUI set up a new remote specifying the git repository to be at:

/var/www/www.domain.name/git

If you’re on the command line something like this should set up a remote:

$ git remote add web ssh://<username>@<server address>/var/www/www.domain.name/git
I've assumed you already have your local repository set up and are checking code into it. 

Some points to bear in mind. The post-receive hook is run by the user who is doing the push. You can see who this user is by looking at the username defined in the config file for the remote. The directory specified by “core.worktree” will need to be writable by that user.

Also if you want to checkout a specific branch change the line “git checkout -f”  to “git checkout  branch-name -f”

If you get an error that git can’t update a working copy try creating a new branch on your local machine, pushing that to the remote, checking it out on the remote, then try pushing your original branch again.

As I said before, I am still testing this set up. My next step will be to alter the hook script to account for user uploads and config files specific to a server. I imagine this will be done by creating a symlink from the nondeployed directory to the public_html directory.

Add a ppa archive to Debian

Tags

, , ,

If you want to add a newer package to a Debian install than you can find in the default repositories you have a couple of options, either update to whole Debian install to an unstable or testing release, install from source or add a PPA repository so you can manage the install with “apt”. The script here will allow you to add a Ubuntu PPA to your Debian install. N.B Debian intentionally runs stable/older versions of code to insure each release is as stable as possible. Pissing all over that by adding Ubuntu PPAs might not always lead to the most stable install so check with a testing box first. This is useful for situations though when you’re running a newer piece or software which is updated often and want to keep the updates as smooth as possible.

Source: How to add a PPA in Debian

Kohana’s Cascading Filesystem

Tags

, ,

One of the features of Kohana that I didn’t grasp straight away was its cascading filesystem. Which in short allows you to “override” files lower down in the hierarchy with your own version. For the full run down have a look at explanation on Kohan’s documentaion . Why is it useful? Well for a start it helps keep your grubby mitts off of external module code when you want to customise them and keeps all your application specific changes in one place.

So for example imagine you download a module to help manage users on your site. You can drop it in your modules folder, activate it and the world is good(ish). Even with all your CSS wizardry you can’t get the default views to look the way you want on the page. So you take a look at the relevant view in the module and see some changes you’d like to make. But if you go and change the module’s files directly you end up making it specific to your application which means it’s tough to use in another application or keep up to date if the author releases a new version.

But with the cascading filesystem you can just take a copy of the relevant view file in the modules folder and put it in a matching folder within your applications folder (which is higher in priority), modify it and then when your application is run, Kohana will use your altered file and not the modules default one. The cascading filesystem also means you can keep all of your application specific configuration files in one place within your application so you don’t risk overwriting them when the module changes.

You can search the filesystem yourself with the Kohana::find_file method.

I think it’s a really useful feature especially when you’re dealing with 3rd party modules, or want to make some of your code more transportable. Check out the diagram for the hierarchy and what overrides what.

Kohana my New PHP Framework of Choice

Tags

, ,

After a bit of a hiatus from this blog I want to get back in the swing of things. Just a quickie this time. If you’re looking for a framework to ease your PHP development you could do a lot worse than checking out Kohana. I’ve been using it for about 6 months and think I’ve found what I was looking for in a PHP framework. Namely something that doesn’t fight against PHP and allows me to build web apps without endless faffing repeating the common tasks.

Some of the highlights for me are:

  • Flexible URLs/routing
  • SQL query builder, SQL injection protection, ORM if you want it
  • Caching options built-in (memcache, file, database)
  • HMVC (MVC that can call other controllers)
  • It plays well with external libraries

I found Kohana after hitting some friction with CodeIgniter, which seems to be a fairly common situation. I’m not going to get hands on in this post but I’m planning on putting together a few related posts to share my experiences. The documentaion that helped me most when I started was the unofficial wiki and the sites it links to. At the time of writing the wiki was targeted at the 3.0 Kohana release. So you might want to check you’ve got a matching version if you’re just starting out. The official Kohana documentation is here.

A quick word of warning. My biggest annoyance when I was trying to learn Kohana, was finding the right documentation. One of the things I really appreciated about CodeIgniter was the link to the clear relevant documentation at the top of nearly every page on their site. Part of the problem with Kohana is the rapid/API breaking development. Partly I think it’s just poor documentation practices. It seems to be getting better though. At least the outdated documents say they’re outdated now. But the old stuff still seems to be up at the top of a lot of Google results. My advice is go through the unofficial wiki, check the forum, get your hands dirty and stick with it.

MySQLdb, Python, MySQL and OS X: A match made in Satan’s bum

Tags

, , ,

Excuse me while I vent for a while. I’ve just spent an age trying to get Python talking to MySQL via MySQL for Python. Which for unknown reasons seems to be a pain in the backside if you’re on a Mac. I just wanted to be like the cool kids and try Django…

Ok enough of the nonsense. This is intended to help you if you’re having trouble getting MySQL-python installed or don’t know what to do when you get the message “No module named MySQLdb”. There seems to be a million and one articles out there about setting this combo up most of them seem to work for some people but not all. Hopefully I’ll cover a couple of cracks with this. Your amount of millage will vary depending on how similar your setup is to mine so for the record:

  • OS: OS X Snow Leopard
  • Python version 2.6.4 (32 bit)
  • MySQL 5.1.44 (32 bit)
  • MySQL-python-1.2.3c1
  • Xcode installed from the Snow Leopard disk with 10.4 support.

1) If you have a 32bit version of Python you will need to use the 32 bit version of MySQL to compile against. Vice versa for 64 bit.

2) You may also need setuptools. Follow these instructions.

3) Grab MySQL-python you’ll want the .tar version. Unzip/untar it and open “setup_posix.py” from the directory containing  MySQL-python.

4) Find the line starting with “mysql_config =” and replace the whole line with:

mysql_config.path = “/usr/local/mysql/bin/mysql_config”

Save and close it.

5) Create this symobolic link:

$ sudo ln -s /usr/local/mysql/lib /usr/local/mysql/lib/mysql

6) Now in a terminal make sure you are in the MySQL-python directory and run this:

$ python setup.py clean

7) Now for 32bit versions::

$ python setup.py build

You should notice some folders created in the build directory. If you’re building 32 bit versions they’ll have the word fat in the name. You might get a warning about wrong versions or something, it seems ok to ignore it.

8) Now run:

$ sudo python setup.py install

For 64bit versions:

ARCHFLAGS="-arch x86_64" python setup.py build

You should notice some folders created in the build directory. If you’re building 64 bit versions they’ll have the word 86_64 in the name.

Now run:

sudo ARCHFLAGS="-arch x86_64" python setup.py install

You should see a message about it coping the .egg file.

You should be be done now. Check it by running python and import MySQLdb. You may get a warning first time but not an error.

If you get the error:

ImportError: dynamic module does not define init function (init_mysql)

Then you are probably mixing 64 bit and 32 bit versions of python and MySQL.

I found these sites useful for getting this sorted: http://www.mangoorange.com/2008/08/01/installing-python-mysqldb-122-on-mac-os-x/ , http://websaucesoftware.com/blog/?p=461 , http://davidmichaelthompson.com/2008/02/18/notes-installing-django-python-mysql-on-os-x/

If you’re involved with Django’s development I’d really recommend you throw some weight behind getting a decent Mac solution for MySQL driver installation. At the moment this is horrible. There must be countless people who’ve given up on the framework because they couldn’t get past the first page of the beginners tutorial.

Doughnuts, pony tails and the birth of Firefox

Tags

,

Geeks, geeks, geeks hooooooo

If you’re quite sad like me you may like to give up/invest an hour of your life watching Code Rush, the documentary following Netscape as it first open sourced the code behind Netscape Communicator. If nothing else it does seem to hint that healthy levels of social interaction and fashion sense are indeed linked.

clickmovement.org/coderush