mysql startup debugging tip

If you are not able to start the mysql daemon repeatedly using your linux distribution init scripts and you are ready to pull out your hair in frustration, here is a tip that might help you in finding the problem.

Try running the mysqld_safe from the command line (without using the init scripts). Try running /usr/bin/mysqld_safe -v, which should spit out some debugging information.

If that fails, try calling the mysqld daemon directly from the command line, with the "-v" option . mysqld is usually present under /usr/sbin/mysqld. mysqld can be called with --print-defaults to get the command line options it would be run with.

/usr/sbin/mysqld would have been started with the following arguments: --user=mysql --pid-file=/var/run/mysqld/mysqld.pid ..... --max_binlog_size=100M

Try adding the -v option to these options to get more verbose details. When run from command line, mysqld will not detach from console and will print debugging info that might be useful in finding the cause of the error.

I suggest using 3 terminals to figure out what is going on

• one terminal with tail -f /var/log/mysql/mysqld.err
• one terminal with tail -f /var/log/messages
• one terminal where I ran the mysqld_safe / mysqld command

The init scripts are usually good for day to day work. However, sometimes the init scripts can impede a innodb crash recovery process on a large database. Some init scripts have timeout built into them and they can kill mysql while the innodb is still trying to recover its tables.

Got my copy

Look what I got in my mail today . Bought it second hand at amazon. I am so pleased to see this is a hardcover edition. 6 bucks really well spent.

Caching is not a silver bullet

Let us take a this hypothetical situation. You have to serve a web page. You want the whole page to be sent back in 500 ms (milliseconds). If your user has a good network and he is not too far from your webserver, you can further assume that around 50 ms will be spent on the network. This means that you have 450 ms to collect all the data about this web request, do the fancy manipulations (sorting/filtering/updating files etc.) and serve it to the user. You need to make four external calls to get this data - 2 of them to an external web service and 2 of them to your own database.

Now assume that one of your external webservice calls take one second to send back the result 50% of the time and one of your database queries can take upto a second to give back the result 25% of the time. What will you do to make sure none of your users ever have to wait for more than 500 ms to get back the page? (500 ms excludes the time taken to download the images/css/do fancy javascript magic).

Read more on my website