When I'm posting a question here I usually post a simplified abstraction. This
time I have to post the full problem - and my problem is that my query takes
far too long to complete. The query is quite speedy with very small data sets,
but it slows down unacceptably with data sets of any decent size.
Conceptually, here's what I'm trying to do: I've got a set of tasks to execute
("frames"). The frames which are ready to execute are in "wait" mode. These
frames are associated with "layers" (in the table "layers"), and these layers
have 0 or more "layer resource requirements" (in the table "lrsrc"). I also
have a table of compute nodes ("nodes"). Each of these nodes has 0 or more
"node resources" (in the table "nrsrc"). If a layer requires "linux" and
"perl" resources, frames in that layer will only run on compute nodes which
have "linux" and "perl" resources. This query is attempting to match a render
node named "foo" with a suitable frame for it to render. In this data set I've
got 30 jobs, 434 layers, and 196036 frames of which 14309 are in "wait" mode.
This question is a followup to this thread, which helped me write the query:
http://lists.mysql.com/mysql/174017
Here's the query:
SELECT frames.id,frames.lid,frames.frame,frames.tile,layers.rsrcc,layers.layer
FROM nodes,frames,jobs,layers
LEFT JOIN lrsrc ON frames.id=lrsrc.id AND frames.lid=lrsrc.lid
LEFT JOIN nrsrc ON nodes.name=nrsrc.name AND nrsrc.rsrc=lrsrc.rsrc
WHERE frames.id=jobs.id
AND layers.id=frames.id
AND layers.lid=frames.lid
AND frames.state="wait"
AND (jobs.partition=nodes.partition OR nodes.partition IS NULL)
AND nodes.name="foo"
GROUP BY frames.id,frames.lid,frames.frame,frames.tile,nrsrc.name
HAVING layers.rsrcc=COUNT(nrsrc.rsrc)
LIMIT 1;
Here's the result:
+----+-----+-------+------+-------+----------------+
| id | lid | frame | tile | rsrcc | layer |
+----+-----+-------+------+-------+----------------+
| 22 | 0 | 6 | 0 | 0 | unconventional |
+----+-----+-------+------+-------+----------------+
1 row in set (13.09 sec)
Here's the EXPLAIN:
+----+-------------+--------+--------+-------------------+---------+---------+--------------------------------------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra
|
+----+-------------+--------+--------+-------------------+---------+---------+--------------------------------------+-------+---------------------------------+
| 1 | SIMPLE | nrsrc | system | PRIMARY,rsrc,name | NULL | NULL |
NULL | 0 | const row not found
|
| 1 | SIMPLE | nodes | const | PRIMARY,partition | PRIMARY | 50 |
const | 1 | Using temporary; Using filesort
|
| 1 | SIMPLE | frames | ref | state,cid-id,id | state | 1 |
const | 16100 | Using where; Using index
|
| 1 | SIMPLE | jobs | eq_ref | PRIMARY,partition | PRIMARY | 4 |
sherman.frames.id | 1 | Using where
|
| 1 | SIMPLE | layers | eq_ref | PRIMARY | PRIMARY | 5 |
sherman.frames.lid,sherman.frames.id | 1 |
|
| 1 | SIMPLE | lrsrc | index | NULL | rsrc | 30 |
NULL | 435 | Using index
|
+----+-------------+--------+--------+-------------------+---------+---------+--------------------------------------+-------+---------------------------------+
And here's the setup of my tables:
CREATE TABLE `nrsrc` (
`name` varchar(15) NOT NULL default '',
`rsrc` varchar(15) NOT NULL default '',
PRIMARY KEY (`rsrc`,`name`),
KEY `rsrc` (`rsrc`),
KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `nodes` (
`name` varchar(50) NOT NULL default '',
`ip` int(15) unsigned NOT NULL default '0',
`state` enum('out','idle','busy','mia') NOT NULL default 'out',
`partition` varchar(12) default NULL,
`rsrcc` tinyint(4) unsigned NOT NULL default '0',
`lastcontact` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`name`),
KEY `partition` (`partition`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `frames` (
`id` int(10) unsigned NOT NULL default '0',
`lid` tinyint(3) unsigned NOT NULL default '0',
`frame` smallint(5) unsigned NOT NULL default '0',
`state` enum('depend','wait','run','fail','succeed') NOT NULL default
'depend',
`tile` tinyint(3) unsigned NOT NULL default '0',
`node` varchar(15) default NULL,
PRIMARY KEY (`tile`,`frame`,`lid`,`id`),
KEY `state` (`state`),
KEY `cid-id` (`lid`,`id`),
KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `jobs` (
`id` int(10) unsigned NOT NULL auto_increment,
`submit_time` timestamp NOT NULL default '0000-00-00 00:00:00',
`name` varchar(25) NOT NULL default '',
`logdir` varchar(50) default NULL,
`owner` varchar(10) default NULL,
`show` varchar(8) default NULL,
`shot` varchar(8) default NULL,
`partition` varchar(12) default NULL,
`priority` float default NULL,
`jobfile` varchar(80) default NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`),
KEY `partition` (`partition`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `layers` (
`id` int(10) unsigned NOT NULL default '0',
`lid` tinyint(3) unsigned NOT NULL default '0',
`order` tinyint(3) unsigned NOT NULL default '0',
`layer` varchar(14) default NULL,
`shell` varchar(30) default NULL,
`script` text,
`rsrcc` tinyint(4) unsigned NOT NULL default '0',
PRIMARY KEY (`lid`,`id`),
KEY `order` (`order`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `lrsrc` (
`id` int(10) unsigned NOT NULL default '0',
`lid` tinyint(3) unsigned NOT NULL default '0',
`rsrc` varchar(30) NOT NULL default '',
PRIMARY KEY (`rsrc`,`lid`,`id`),
KEY `rsrc` (`rsrc`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
