Archive for the mysql Category

MySQL stoped accepting connections.

Thursday, August 7th, 2008 | Permalink

Strange things are going on.

Actually, today one of my most frequented servers, running lighttpd, stopped serving php files after working for months.
After searching for a while in the lighttpd logs and php logs we figured out, that lighttpd still serves plain html files. So the problem seemed to come from the fast-cgi php. After swichting to apache for about 30 minutes, apache stopped too.
So we figured out that there must be something with the db server, which is an external machine.
Connection local worked fine. Connecting via socket didn’t!
We looked at the processes with:

mysql>show processlist;

The result was a lot of these:

| 108160 | unauthenticated user | 10.0.0.1:3449 | NULL | Connect | NULL | login | NULL

So we figured out that there is a problem with the authentification. First we thougt it is a switch or some kind of hardware problem. Nothing.
After looking around, we found out that MySQL is using reverse lookup at socket connections.
We added the machines in /etc/hosts and everything works fine for now.

What happend?
We are not using our own DNS for lookups. So what I think is that there was actually an entry for 10.0.0.1 in the dns server we use. This dns server served some domain name. Definitly not for our server, but it was a dns record. Mysql just checks and says yes or no. Someone must have updated the dns server and deleted this record so our system stopped.
Strange thing!
I think there is no good reason why mysql should do a reverse lookup. Do you have one?

Posted in linux, mysql | No Comments »

MySQL Performance is more than just using the query cache

Friday, February 29th, 2008 | Permalink

I’m working on a project with a huge MySQL database and for that reason, I was looking out for some mysql performance tips.
One information I found in nearly every blog was using the slow_query function from mysql.

Put that in your my.cnf:

log-slow-queries=/tmp/slow_queries.log
long_query_time=10


After that you will see all querys that take longer than 10 sec. in the /tmp/slow_queries.log.
To see what you can optimize at this point, you can use EXPLAIN.

So that’s something you find everywhere. Something else you can see everywhere is to activate the query cache. If you are using mysql 5 > this should be enabled by default.

But there are these simple and little optimization points you should use everyday to bring you database on the speedway.

  • Use only the datatypes you really need. Numbers should be saved as int. And text should be saved in the right type.
  • If you’re working with numbers always use only the number no quotes around it. select * from bla where id = 123. Otherwise the server will have to cast the number and will work with a string.
  • Don’t use rand()

Posted in coding, mysql | No Comments »