Re: Terrible performance of a specific query with lots of connections

[Ben Dowling]

Hi Rick,

The rewrite to reduce the amount of open table did reduce (half, in fact) the number of open tables, but I'm not sure that the number of open tables was the problem.

Here are the variables you requested:

mysql> SHOW VARIABLES LIKE "%open%";
+-------------------+----------+
| Variable_name     | Value    |
+-------------------+----------+
| have_openssl      | DISABLED | 
| innodb_open_files | 300      | 
| open_files_limit  | 4606     | 
+-------------------+----------+

mysql> SHOW GLOBAL STATUS LIKE '%open%'; 
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Com_ha_open            | 0     | 
| Com_show_open_tables   | 0     | 
| Open_files             | 425   | 
| Open_streams           | 0     | 
| Open_tables            | 1927  | 
| Opened_tables          | 2273  | 
| Slave_open_temp_tables | 0     | 
+------------------------+-------+
7 rows in set (0.33 sec)

mysql> SHOW GLOBAL STATUS LIKE 'Uptime'; 
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Uptime        | 514848 | 
+---------------+--------+
1 row in set (0.33 sec)

I notice that the innodb_open_files limit is relatively low - could this be the problem?

Thanks, Ben