Mysql – thesql fine tuning: open_tables, opened_tables

max-connectionsMySQL

I'm a novice to database administration and have been depending on forum suggestions and answers to help resolve my database hang ups and too many connections errors; I've managed to reduce the frequency of too many connections errors but I'd like to eliminate them at this level.

According to an answer by RolandoMySQLDBA on this post, "If Open_tables climbs above (Opened_tables / Uptime), I have a cause for concern.."

My Server specs is 2.0Mhz and 484Mem; I've had a client whose having a lot of hits upto 1000 requests per hour and with processing mainly of their 2 InnoDB tables, that translates to roughly 3000 connections made to both tables in an hour.

From advise on another forum, I've made changes to my.cnf and it translates to this:

[mysqld]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock
skip-locking
key_buffer_size = 30M
max_allowed_packet = 1M
table_cache = 300
sort_buffer_size = 256K
read_buffer_size = 1M
read_rnd_buffer_size = 1M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
net_buffer_length = 12K
thread_stack = 1M
innodb_buffer_pool_size=60M
interactive_timeout=300
wait_timeout=300

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 48M
sort_buffer_size = 48M
#key_buffer_size = 256M
#sort_buffer_size = 256M
#read_buffer = 2M
#write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

When I checked the parameters of opened_tables, open_tables and uptime as per the post, I get these values:

Uptime = 78018
Opened_tables = 1606999
Open tables = 300

therefore: Opened_tables / Uptime=1606999/78018= 20.6

However, my Opened_tables variable has not changed in 3 hours; It has been the same.

After these changes, the server load had occurred twice in the past 24Hours (at night) and I didn't record the value for Opened_tables at this period; But as you'll note from the calculation, the Opened_tables value increased drastically last night during when many client requests came through.

Output of thread variables are:

 Threads_cached                    | 7          |
| Threads_connected                 | 14         |
| Threads_created                   | 306        |
| Threads_running                   | 2 

and i note from the mysql documentation (http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Threads_connected) that I need to increase the thread_cache_size .

my max_connections is the default;

So basically:

  1. I need to increase thread_cache_size;
  2. I need to increase the
    table_open_cache (but a very large value also causes
    unreliability
    )

I've just had a "too many connections" error at the time of posting this and can't get results for Opened_tables and Open_tables; Also, I can't login with another session to the server; I automatically get disconnected.

QUESTIONS:

  1. What method should I use to approximate the values of
    thread_cache_size and table_open_cache?

  2. Is my max_connections too low? How can I calculate the best max_connections setting?

  3. How can I reduce the "too many connections" error (without creating another user and having that user be the one making the connections rather than root) ?

    NOTE: No I can't upgrade memory as at now. Thanks.
    Right now the only way I deal with this is to close the port form incoming messages (which is not exactly ethical) or to restart server to pending messages in system can be processed and memory freed.


After my now too many connections errors – which I've resolved by blocking the receiving port; my variables are now:

Opened_tables = 1970498 
Open_tables =300

| Threads_cached                    | 6          |
| Threads_connected                 | 15         |
| Threads_created                   | 437        |
| Threads_running                   | 2          |

Any advise i dealing with this wisely and efficiently will be much appreciated. Thanks in Advance.

[3]:

Best Answer

Three points:

  • I don't see the immediate connection between your "too many connections" and the table cache. There might be such connection, but you have not provided info which suggests the two relate.

  • I disagree with the advice about "If Open_tables climbs above (Opened_tables / Uptime), I have a cause for concern.."

  • You haven't listed the table_open_cache & table_definition_cache settings. The table_cache variable is an old one.

Some suggestions:

  • With regard "too many connections", try the following: Set max_connections to some reasonable value, like 300, throwing around a number. Set open_files_limit to a very high values, like 8192. By default you are only allowed 1024 open files on linux. Sockets, threads, table descriptors -- all map to files. Do you have some connection pool which holds many open (idle) connections, with some keepalive? How many processes do you get on a typical SHOW PROCESSLIST?

  • With regard table cache: I suggest set a high value. With 3,000 queries per hour you're not remotely near the problem of having a problem with that. Why don't you set: table_open_cache=2048 table_definition_cache=512 (even though you may only have a couple tables)

  • With regard the advice about open tables, you should be more interested in the number of tables opened per second (or minute, whatever) in any given time period, rather than looking at the overall number of tables opened since the server started to run. So you should monitor your opened_tables status variable every few minutes, and do the math. You would typically want to see less than 1 table opened in a second. Again, I'm throwing numbers around, but just to give you proportion. The open_tables is nice to know, as well. On good usage it can be very high, and that means your server is actively reading from tables. Fine! As long as the number of opened_tables/sec does not rise too high, this is no worry.

  • There could be other reasons for "too many connections". Are the queries very long? Such that they just accumulate? Are some background tasks failing to complete? (You do have a wait_timeout=300 which is good; however some PHP job might be issuing new queries every few seconds, failing to close properly?

  • Noting again: watch over SHOW PROCESSLIST and see what comes up.