MySQL Bugs: #21915: Changing limits of table_cache when setting max

Bug #21915	Changing limits of table_cache when setting max_connections
Submitted:	30 Aug 2006 1:01	Modified:	14 Dec 2006 3:14
Reporter:	Miguel Solorzano	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0\5.1\4.1	OS:	Windows (Windows)
Assigned to:	Tomash Brechko	CPU Architecture:	Any

Description:
When starting the server with the default start options:

D:\mysql\bin>mysqld --standalone --console
060829 21:34:04  InnoDB: Started; log sequence number 0 46403
060829 21:34:04 [Note] mysqld: ready for connections.
Version: '5.1.12-beta-nt'  socket: ''  port: 3306  Source distribution
060829 21:34:04 [Note] SCHEDULER: Manager thread booting
060829 21:34:04 [Note] SCHEDULER: Loaded 0 events
060829 21:34:04 [Note] SCHEDULER: Suspending operations

The default value of table_open_cache is:

c:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.1.12-beta-nt

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

mysql> show variables like "%table_open_cache%"; show variables like "max_connections";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| table_open_cache | 64    |
+------------------+-------+
1 row in set (0.00 sec)

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 100   |
+-----------------+-------+
1 row in set (0.00 sec)

However if I tried to start the server with max_connections = 450:

D:\mysql\bin>mysqld --standalone --console --max-connections=450
060829 21:38:12 [Warning] Changed limits: max_open_files: 2048  max_connections: 450  table_cache: 794
060829 21:38:12  InnoDB: Started; log sequence number 0 46403
060829 21:38:12 [Note] mysqld: ready for connections.
Version: '5.1.12-beta-nt'  socket: ''  port: 3306  Source distribution
060829 21:38:12 [Note] SCHEDULER: Manager thread booting
060829 21:38:12 [Note] SCHEDULER: Loaded 0 events
060829 21:38:12 [Note] SCHEDULER: Suspending operations

See above the change done in the limit of table_cache: 794

c:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.1.12-beta-nt

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

mysql> show variables like "%table_open_cache%"; show variables like "max_connections";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| table_open_cache | 794   |
+------------------+-------+
1 row in set (0.00 sec)

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 450   |
+-----------------+-------+
1 row in set (0.00 sec)
-------------------------------XXX-------------------------------
C:\mysql\bin>mysqld --standalone --console --max-connections=450
060829 21:44:05 [Warning] Changed limits: max_open_files: 2048  max_connections: 450  table_cache: 794
060829 21:44:06  InnoDB: Started; log sequence number 0 98887
060829 21:44:07 [Note] mysqld: ready for connections.
Version: '5.0.24-community'  socket: ''  port: 3306  MySQL Community Edition (GPL)
-------------------------------XXX-------------------------------
C:\mysql4121\bin>mysqld --standalone --console --max-connections=450
060829 21:45:19 [Warning] Changed limits: max_open_files: 2048  max_connections: 450  table_cache: 794
060829 21:45:19  InnoDB: Started; log sequence number 0 46233
mysqld: ready for connections.
Version: '4.1.21-community'  socket: ''  port: 3306  MySQL Community Edition (GPL)
-------------------------------XXX-------------------------------
C:\mysql4027\bin>mysqld-nt --standalone --console --max-connections=450
060829 21:48:21  InnoDB: Started
mysqld-nt: ready for connections.
Version: '4.0.27-nt'  socket: ''  port: 3306  Official MySQL binary

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.0.27-nt

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

mysql> show variables like "%table_cache%"; show variables like "max_connections";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| table_cache   | 64    |
+---------------+-------+
1 row in set (0.00 sec)

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 450   |
+-----------------+-------+
1 row in set (0.00 sec)

How to repeat:
see description.

Suggested fix:
Change the limit for a value around 300 instead of 794.

This behavior is more problematic in that it cannot be explicitly overridden by explicitly setting table_cache:

C:\Program Files\MySQL\MySQL Server 5.0>mysqld-nt --standalone --console --max_connections=450 --table_cache=300
061024 11:32:51 [Warning] Changed limits: max_open_files: 2048  max_connections:  450  table_cache: 794

This causes problems for users hosting MySQL on Windows platforms that need to increase the max_connections.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/14480

ChangeSet@1.2328, 2006-10-27 17:25:21+04:00, kroki@moonlight.intranet +2 -0
  BUG#21915: Changing limits of table_cache when setting max_connections
  
  If the user has specified --max-connections=N or --table-open-cache=M
  options to the server, a warning could be given that some values were
  recalculated, and table-open-cache could we assigned greater value.
  
  Note that both warning and increase of table-open-cache were totally
  harmless.
  
  This patch fixes recalculation code to ensure that table-open-cache will
  be never increased automatically and that warning will be given only if
  some values had to be decreased due to operation system limits.
  
  No test case is provided because we neither can't predict nor control
  operation system limits on maximal number of open files.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/14561

ChangeSet@1.2554, 2006-10-30 17:47:02+03:00, kroki@moonlight.intranet +2 -0
  BUG#21915: Changing limits of table_cache when setting max_connections
  
  If the user has specified --max-connections=N or --table-open-cache=M
  options to the server, a warning could be given that some values were
  recalculated, and table-open-cache could be assigned greater value.
  
  Note that both warning and increase of table-open-cache were totally
  harmless.
  
  This patch fixes recalculation code to ensure that table-open-cache will
  be never increased automatically and that a warning will be given only if
  some values had to be decreased due to operating system limits.
  
  No test case is provided because we neither can't predict nor control
  operating system limits for maximal number of open files.

Queued to 4.1-runtime, 5.0-runtime and 5.1-runtime.

Fix was pushed into 4.1.22, 5.0.29 and 5.1.13

I'm confused. Is this fixed in 4.1.22? I search the changelog and the bug #21915 is not listed. Any ideas? I have the same problem on 4.1.21

Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/

Bugfix/behaviour change documented in changelogs for 4.1.22, 5.0.30, and 5.1.13.

I'm running 4.1.22 and this is NOT fixed. I still get:
Changed limits: max_open_files: 2048  max_connections: 500  table_cache: 769

Sorry for the mix-up.  4.0.22 was cloned off on 2006-10-27, the patch was pushed to the main tree on 2006-10-30, so it's 4.0.23.

Please disregard my previous comment, I mistook 4.0 for 4.1.  The true story is:
4.1.22 was cloned off on 2006-11-02, the same day the patch was pushed into the main tree, however the cone off has probably happened a few hours earlier than the push, so the patch is likely in 4.1.23 and up.

I don't see that listed here:
http://dev.mysql.com/doc/refman/4.1/en/news-4-1-23.html

That's because it is mistakenly listed as the first entry in http://dev.mysql.com/doc/refman/4.1/en/news-4-1-22.html.  We thought it got into 4.1.22, but apparently it was 4.1.23.

Thanks.

In the meantime how can I fix this? Can you email me the RC of 4.1.23 so I can upgrade? How soon will it be released? Also would the performance on Windows servers be improved on "busy" mysql DB's (running vbulletin forums). If the max opened files limit can be increased from 2048 to say 4096?

thanks

Hayden,

First of all, do you really need this patch?  The problem was that when you set max_connections on the command line, table_open_cache was also increased.  However, you may do

  SET GLOBAL table_open_cache= NNN;

after that, or set max_connections with SET GLOBAL too (as opposite to command line).  So I think you don't really need this patch at all.

> In the meantime how can I fix this? Can you email me the RC of 4.1.23 so
> I can upgrade?

I guess the only option would be to apply the patch manually, and rebuild the server.

> How soon will it be released?

According http://www.mysql.com/company/legal/lifecycle/ 4.1 is past its Active Support.  You may want to start planning the migration to 5.0 instead.

> Also would the performance on Windows servers be improved on "busy"
> mysql DB's (running vbulletin forums).

High value of 'table_open_cache' may result in running out of file descriptors.  I think it won't harm the performance though. 

> If the max opened files limit can be increased from 2048 to say 4096?

On Windows the answer is no, 2048 is the limit.

In my current my.ini I have:
table_cache=1536
max_connections=500

Are you saving my.ini should now be:
SET GLOBAL table_open_cache= 1536;
SET GLOBAL max_connections=500;

Do i need the ";" at the end of each line? Also is it "table_cache" or "table_open_cache" for mysql 4.1 or both are the same command?

Mysql stats show a growing amount of "opened tables" about a 1000 per hour. I wanted to raise to 1536 to slow down the rate of opened tables.

> According http://www.mysql.com/company/legal/lifecycle/ 4.1 is past its Active
> Support.  You may want to start planning the migration to 5.0 instead.

I've heard on vbulletin.org that 5.0 has been slower than 4.1. What's your take on this as per use with the vbullitin software?

> In my current my.ini I have:
> table_cache=1536
> max_connections=500

OK, now I see, nothing to do with this bug.  You simply ask too much.  The approximate formula is table_cache*2+max_connections.  So 1536*2+500 > 2048,
for 500 connections maximal table_cache is 700 something.

> OK, now I see, nothing to do with this bug.  You simply ask too much.  The
> approximate formula is table_cache*2+max_connections.  So 1536*2+500 > 2048,
> for 500 connections maximal table_cache is 700 something.

Ok in "Mysql Administrator" under the "Connection health" tab. The MAX my connections have ever gone to is just over 100 users.

At vbulletin.com under their server tune forum they recommended a value of 600 max_connections as they said it will set a better table_cache value for better performance.

My question: Will setting my max_connections to 200 or 300 (resulting in larger table_cache) improve performance? Or should I leave it at 500 max connections?

Thanks, you've been a big help!

Since both connections and table cache share the same pool of available file descriptors, decreasing the one of them gives the way to increase the other.  I can't tell if increasing table_cache would improve the performance in your case or not, but there's probably no need to increase max_connections higher than it's needed.  If you have ~100, set it to 150--200, increase table_cache to the possible maximum, and see if it helps.

Ok changed it to 200 and here's what I'm seeing so far:
- Opened_tables have stop increasing and is very close to the value of open_tables
- The CPU usage seems to increase slightly
- No noticeable performance change for better or worst
- I may try Max_connections 700 next to see if this lowers CPU usage