MySQL Bugs: #65645: READ/WRITE/WAIT timeouts are too long on FreeBSD

Bug #65645	READ/WRITE/WAIT timeouts are too long on FreeBSD
Submitted:	17 Jun 2012 16:08	Modified:	27 Aug 2012 17:45
Reporter:	Lucas Holt	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.5.25	OS:	FreeBSD (9.0)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	Contribution

Description:
Performance is abysmal on FreeBSD due to the time-outs being to large.  Some queries take minutes to run that previous took seconds.  

The FreeBSD ports system includes a fix with this description: 

Add a patch to lower the valid range of mysql read/write/wait timeouts.
Such timeouts are implemented via setsockopt that doesn't accept values
larger than (INT_MAX / kern.hz).

This problem also affects MidnightBSD and possibly other BSDs. 

How to repeat:
Run MySQL 5.5.25 on FreeBSD. Run large queries.

Suggested fix:
http://www.freebsd.org/cgi/cvsweb.cgi/ports/databases/mysql55-server/files/patch-sql_sys_v...

--- sql/sys_vars.cc.orig	2012-06-15 17:03:32.000000000 +0200
+++ sql/sys_vars.cc	2012-06-15 17:16:23.000000000 +0200
@@ -900,7 +900,7 @@
        "connection before closing it",
        SESSION_VAR(net_interactive_timeout),
        CMD_LINE(REQUIRED_ARG),
-       VALID_RANGE(1, LONG_TIMEOUT), DEFAULT(NET_WAIT_TIMEOUT), BLOCK_SIZE(1));
+       VALID_RANGE(1, INT_MAX32/1000), DEFAULT(NET_WAIT_TIMEOUT), BLOCK_SIZE(1));
 
 static Sys_var_ulong Sys_join_buffer_size(
        "join_buffer_size",
@@ -1401,7 +1401,7 @@
        "Number of seconds to wait for more data from a connection before "
        "aborting the read",
        SESSION_VAR(net_read_timeout), CMD_LINE(REQUIRED_ARG),
-       VALID_RANGE(1, LONG_TIMEOUT), DEFAULT(NET_READ_TIMEOUT), BLOCK_SIZE(1),
+       VALID_RANGE(1, INT_MAX32/1000), DEFAULT(NET_READ_TIMEOUT), BLOCK_SIZE(1),
        NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(0),
        ON_UPDATE(fix_net_read_timeout));
 
@@ -1416,7 +1416,7 @@
        "Number of seconds to wait for a block to be written to a connection "
        "before aborting the write",
        SESSION_VAR(net_write_timeout), CMD_LINE(REQUIRED_ARG),
-       VALID_RANGE(1, LONG_TIMEOUT), DEFAULT(NET_WRITE_TIMEOUT), BLOCK_SIZE(1),
+       VALID_RANGE(1, INT_MAX32/1000), DEFAULT(NET_WRITE_TIMEOUT), BLOCK_SIZE(1),
        NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(0),
        ON_UPDATE(fix_net_write_timeout));
 
@@ -2257,7 +2257,7 @@
        "The number of seconds the server waits for activity on a "
        "connection before closing it",
        SESSION_VAR(net_wait_timeout), CMD_LINE(REQUIRED_ARG),
-       VALID_RANGE(1, IF_WIN(INT_MAX32/1000, LONG_TIMEOUT)),
+       VALID_RANGE(1, INT_MAX32/1000),
        DEFAULT(NET_WAIT_TIMEOUT), BLOCK_SIZE(1));
 
 /** propagates changes to the relevant flag of @@optimizer_switch */

Thank you for the report.

I did OLTP sysbench test and results differ not so much to say about consistent improvement with the patch. Even more, with particular number of threads, results with patch slower.

Which kind of queries affected? Which benchmark do you use? Please provide results in your environment, so we can compare with ours.

Thanks for looking into this Sveta.  The timeout values are invalid for FreeBSD as they are larger than the OS supports. 

You are correct about the performance.  I went back to my original notes and I was debugging a significant performance problem with a Java application I had written.  After updating from a pre 5.5.20 release, I had terrible performance and I narrowed it down to a bug with "As"  on column names, Sun CachedRowSet, and the MySQL JDBC driver.  That is a different problem though and doesn't belong on this bug report.

Thank you for the feedback.

Could you please point us to FreeBSD documentation describing this limitation?

I also wonder why it matters: maximum values are not default and should not affect until you set them as default.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".