MySQL Server slows down with large slow query log

I’ve seen this on multiple mysql servers now, and I don’t really know why it happens, but let’s start in the beginning…

The MySQL Server provides a so-called “slow-query-log” which logs all queries that take longer than a certain time.

There are mainly two parameters which allow you to control the behaviour:

slow_query_log=[0|1] controls whether the slow query log is written or not.
long_query_time=N controls the minimum query execution time in seconds for which a query should be logged.

A lot of pages recommend to activate the slow query log since it let’s you easily identify which queries on your server take a lot of time, and it really is very good for it. However, if you don’t take care of it, then your log file might become really large, e.g. if you are running your server for more than a year and you have set a long_query_time of one second, you might finally end up with a log file of more then 50 GB. This seems to have quite an impact on the performance of the mysql server for all kinds of queries (actually causing more queries to be logged).

What usually helps is

  1. Delete the slow query log file on the server
  2. Flush the logs of the server by running flush logs; in the query browser. Take care, this might actually cause you server to get blocked for multiple seconds.
  3. Increase the long_query_time to a very high value so nothing is logged. You can always lower the value if you need data.

I saw three times more queries being processed per second after taking the steps above!

grep and Repetitions

A few days ago I was trying to achieve something with grep (which is usually not a big problem) and a large file with one word per line (a dictionary). I wanted to grep out all the lines which contained words with three or less characters. And after wasting some time I decided to write a note to myself:

If you have a file like this:

yyy@xxx:~$ cat bla

and use grep like this

yyy@xxx:~$ grep "^.{1,3}$" bla

then you don’t get any output.
It also won’t help if you add -e. You will have to add -E in order to activate extended regular expressions, or you can call egrep(which is deprecated).

yyy@xxx:~$ grep -E "^.{1,3}$" bla

Switching jkManager nodes from the command line

If you are using apache plus tomcat to run your website, you are probably also using jkManager as your loadbalancer. jkManager has a small and lightweight web interface where you can see the load of the working nodes, and you can change their states. Wouldn’t it be nice if you could start and stop your workers from the command line?

It is very easy to change the worker states from shell using wget and a small shell script. When administrate you workers through the web interface, you might notice that after you apply the new setting (before being forwarded to the overview page), the location bar of the browser shows a rather long url with a lot of parameters. E.g. if you activate a worker called “node2″ you will see something like this:

As you can see, node2 is mentioned two times, and there is another parameter call wa. The number behind wa correspends to the new state to be set:

  • 0: activate
  • 1: disable
  • 2: stop

So all you need to do if you want to change the worker state from shell is to modify the url shown above by setting your domain, the desired state and replace the occurrences of node2 by your worker name, and then request that url through wget.
If your jkmanager is protected by basic http authentication (.htpasswd) you can set the user name and the password for wget by using

wget --http-user=USER --http-password=PASSWORD ...

Also, wget will actually download the index.html to the current working directory. You can change the location of the downloaded file by using

wget -O /tmp/not-needed.html

How to convert images to video using ffmpeg

ffmpeg is a powerful tool for all kinds of stuff to do with videos.

It is pretty easy to convert a sequence of images into a video:

ffmpeg -sameq -f image2 -i test%03d.png video.avi

This will take an image sequence from the files test001.png, test002.png, …, test010.png, … and convert it into the video video.avi.

%03d is a format string which defines that the number is filled up with zeros to have at least three digits. You can change it according to the naming schema of your input files.

-sameq tells ffmpeg to use the same quality for output as it found in input. For the image sequence this gives you nearly no loss of quality, but it also results in a really big video.

You see, it is really easy to convert images into video with ffmpeg! is back

I finally found the time to revive after the site has been down for nearly one year now. I changed the hoster since my former one crashed their mysql database (and did not restore it), so all the content was lost.

I guess I will spend some time to see what content I can still get back from google caches,, etc.

And also the theme is not as nice as before anymore…

Nevertheless, I’m happy to get started again :-)