Bug #16010 the values of "read_buffer_size" are different in different place
Submitted: 27 Dec 2005 10:57 Modified: 20 Feb 2008 18:46
Reporter: ming lu Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.0.25-BK OS:Windows (Windows)
Assigned to: Peter Lavin CPU Architecture:Any
Tags: Server Configuration

[27 Dec 2005 10:57] ming lu
Description:
The values of system variable like "read_buffer_size" in health->systemvariables  is different from the value in  Startupvariables->Advanced.

 

How to repeat:
Look at the value of  "read_buffer_size" in  health->systemvariables  and  Startupvariables->Advanced. They are different.
[27 Dec 2005 13:08] Valeriy Kravchuk
Thank you for a problem report. Yes, they are different, even on 1.1.6. Health->systemvariables shows the exact current values, and I have 61440 for read_buffer_size (it is a real value). Startup Variables->Advanced gives me 64K for this parameter. 

When I set it to 128K, then restarted the server and checked in Health, I've got 126976. 128K is 131072. The same difference of 4096 bytes in size. It should be explained somehow, in Help at least. So, I mark this as verified bug.
[10 Aug 2006 19:56] Michael G. Zinner
This seems to be a server issue. This is what gets written to the my.cnf file.

#Each thread that does a sequential scan allocates a buffer of this size for each table it scans. If you do many sequential scans, you may want to increase this value.
read_buffer_size=128k

And MA only displays what the server returns.

mysql> SHOW VARIABLES LIKE 'read_buffer_size';
+-----------------+-------+
|read_buffer_size | 126976|
+-----------------+-------+
[25 Aug 2006 14:16] Valeriy Kravchuk
Indeed, it looks like server problem (or, if it is a feature, it should be documented at (http://dev.mysql.com/doc/refman/5.0/en/using-system-variables.html):

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.25-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like 'read_buffer_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| read_buffer_size | 61440 |
+------------------+-------+
1 row in set (0.01 sec)

mysql> exit
Bye
openxs@suse:~/dbs/5.0> bin/mysqladmin -uroot shutdown
STOPPING server from pid file /home/openxs/dbs/5.0/var/suse.pid
060825 14:00:48  mysqld ended

[1]+  Done                    bin/mysqld_safe --read_buffer_size=64K
openxs@suse:~/dbs/5.0> bin/mysqld_safe --read_buffer_size=128K &
[1] 4462
openxs@suse:~/dbs/5.0> Starting mysqld daemon with databases from /home/openxs/d
bs/5.0/var

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.25-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like 'read_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| read_buffer_size | 126976 |
+------------------+--------+
1 row in set (0.00 sec)
[20 Feb 2008 18:46] Peter Lavin
Noted in the documentation.

"The value of this variable should be changed in multiples of 4KBs. If it             is set to a value that is not a multiple of 4 KBs, its value will be rounded down to the nearest multiple of 4 KBs." 

For example:

(none) > set read_buffer_size=131072;
Query OK, 0 rows affected (0.00 sec)

(none) > show variables like "read_buffer%";
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| read_buffer_size | 131072 |
+------------------+--------+
1 row in set (0.00 sec)

(none) > set read_buffer_size=131071;
Query OK, 0 rows affected (0.00 sec)

(none) > show variables like "read_buffer%";
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| read_buffer_size | 126976 |
+------------------+--------+
1 row in set (0.00 sec)