|
This is an upstream bug:
http://bugs.mysql.com/bug.php?id=75753
Description
In certain cases, the optimizer can eliminate partitions from a query plan, based on conditions in the WHERE clause. This is called partition pruning:
http://dev.mysql.com/doc/refman/5.5/en/partitioning-pruning.html
However, even when partition pruning is in use, the MYI and MYD files of all MyISAM partitions in a given table are opened, even if some of them won't be read.
This causes problems for users who have tables with a lot of MyISAM partitions. A simply query targeting one partition of a table with 100 partitions can easily create thousands of file descriptors if there are a few concurrent users.
How to repeat:
I'll use one of the example tables from the partition pruning documentation:
CREATE TABLE t2 (
|
fname VARCHAR(50) NOT NULL,
|
lname VARCHAR(50) NOT NULL,
|
region_code TINYINT UNSIGNED NOT NULL,
|
dob DATE NOT NULL
|
) ENGINE=MyISAM
|
PARTITION BY RANGE( YEAR(dob) ) (
|
PARTITION d0 VALUES LESS THAN (1970) ENGINE=MyISAM,
|
PARTITION d1 VALUES LESS THAN (1975) ENGINE=MyISAM,
|
PARTITION d2 VALUES LESS THAN (1980) ENGINE=MyISAM,
|
PARTITION d3 VALUES LESS THAN (1985) ENGINE=MyISAM,
|
PARTITION d4 VALUES LESS THAN (1990) ENGINE=MyISAM,
|
PARTITION d5 VALUES LESS THAN (2000) ENGINE=MyISAM,
|
PARTITION d6 VALUES LESS THAN (2005) ENGINE=MyISAM,
|
PARTITION d7 VALUES LESS THAN MAXVALUE ENGINE=MyISAM
|
);
|
If we insert a row:
INSERT INTO t2 VALUES ('John', 'Doe', 1, '1982-06-23');
|
Now let's SELECT from the table. We know partition pruning will be used in this particular query:
mysql> EXPLAIN PARTITIONS SELECT * FROM t2 WHERE dob = '1982-06-23';
|
+----+-------------+-------+------------+--------+---------------+------+---------+------+------+-------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
|
+----+-------------+-------+------------+--------+---------------+------+---------+------+------+-------+
|
| 1 | SIMPLE | t2 | d3 | system | NULL | NULL | NULL | NULL | 1 | |
|
+----+-------------+-------+------------+--------+---------------+------+---------+------+------+-------+
|
1 row in set (0.00 sec)
|
The optimizer says the results will only be in partition "d3", but let's see what files MySQL actually opens when the query is executed.
Shell 1
mysql> FLUSH TABLES;
|
Query OK, 0 rows affected (0.00 sec)
|
Shell 2
[gmontee@localhost ~]$ sudo strace -p `pidof mysqld` -ff -o `pidof mysqld`.out -e trace=open
|
Process 4295 attached with 17 threads
|
Shell 1
mysql> SELECT * FROM t2 WHERE dob = '1982-06-23';
|
+-------+-------+-------------+------------+
|
| fname | lname | region_code | dob |
|
+-------+-------+-------------+------------+
|
| John | Doe | 1 | 1982-06-23 |
|
+-------+-------+-------------+------------+
|
1 row in set (0.00 sec)
|
Shell 2
^CProcess 4295 detached
|
Process 4297 detached
|
Process 4298 detached
|
Process 4299 detached
|
Process 4300 detached
|
Process 4301 detached
|
Process 4302 detached
|
Process 4303 detached
|
Process 4304 detached
|
Process 4305 detached
|
Process 4306 detached
|
Process 4308 detached
|
Process 4309 detached
|
Process 4310 detached
|
Process 4311 detached
|
Process 4316 detached
|
Process 4319 detached
|
[gmontee@localhost ~]$ cat 4295.out.4319
|
open("./tmp/t2.frm", O_RDONLY) = 16
|
open("./tmp/t2.par", O_RDONLY) = 17
|
open("./tmp/t2.par", O_RDONLY) = 16
|
open("/var/lib/mysql/tmp/t2#P#d0.MYI", O_RDWR) = 16
|
open("./tmp/t2#P#d0.MYD", O_RDWR) = 17
|
open("/var/lib/mysql/tmp/t2#P#d1.MYI", O_RDWR) = 18
|
open("./tmp/t2#P#d1.MYD", O_RDWR) = 19
|
open("/var/lib/mysql/tmp/t2#P#d2.MYI", O_RDWR) = 20
|
open("./tmp/t2#P#d2.MYD", O_RDWR) = 21
|
open("/var/lib/mysql/tmp/t2#P#d3.MYI", O_RDWR) = 22
|
open("./tmp/t2#P#d3.MYD", O_RDWR) = 23
|
open("/var/lib/mysql/tmp/t2#P#d4.MYI", O_RDWR) = 24
|
open("./tmp/t2#P#d4.MYD", O_RDWR) = 25
|
open("/var/lib/mysql/tmp/t2#P#d5.MYI", O_RDWR) = 26
|
open("./tmp/t2#P#d5.MYD", O_RDWR) = 27
|
open("/var/lib/mysql/tmp/t2#P#d6.MYI", O_RDWR) = 28
|
open("./tmp/t2#P#d6.MYD", O_RDWR) = 29
|
open("/var/lib/mysql/tmp/t2#P#d7.MYI", O_RDWR) = 30
|
open("./tmp/t2#P#d7.MYD", O_RDWR) = 31
|
Suggested fix
If partition pruning is in use, it is a waste to open partitions that will not be checked.
|