You are here

Troubles with MySQL 5.5 on FreeBSD 9

FreeBSD 9 seems to have some troubles with MySQL 5.5.20. A customer has moved from MySQL 5.0 on Linux to MySQL 5.5 on FreeBSD 9. He experienced a lot of periodic slow downs on the new, much stronger, system which he has not seen on the old Linux box.

This slow downs were also shown in high CPU system time but we could not see any I/O going on.

When we looked into MySQL we have seen many threads in Opening tables state in the MySQL processlist.

The first idea was to increase table_open_cache to 2048 and later to 4096. This made the Opening tables disappear but then we got a significant amount of threads hanging in Copying to tmp table state in the processlist.

So we suspected that those table are going to disk. But we did not see any I/O (with iostat) and Created_tmp_disk_tables did not change significantly but just Created_tmp_tables.

So I suspect some troubles with Memory allocation on FreeBSD 9 with MySQL.

Then the customer has set table_open_cache = 4!!! And suddenly the problems disappeared. I am a bit confused because this is exactly the opposite of what I expect.

We played a bit around with table_open_cache but as bigger as we make the value as worse the situation became. So be warned if you see similar symptoms...

Anybody any clue what is going on? I have not found any bug in the MySQL bugs database which sounds similar to this...