Description:
Select with long IN clause causes the mysqld process to use excessive amounts of memory on the server. In many cases the mysqld process gets killed when the server hits an out of memory condition.
The server is a dual Opteron running 64bit version of RHEL4.
The mysql software is stock RPMs downloaded from the mysql site:
MySQL-client-standard-5.0.18-0.rhel4.x86_64.rpm
MySQL-devel-standard-5.0.18-0.rhel4.x86_64.rpm
MySQL-server-standard-5.0.18-0.rhel4.x86_64.rpm
MySQL-shared-standard-5.0.18-0.rhel4.x86_64.rpm
Contents of the my.cnf:
[mysqld]
set-variable = myisam_sort_buffer_size=64M
set-variable = max_connections=500
set-variable = table_cache=512
set-variable = key_buffer_size=384M
set-variable = sort_buffer=80M
set-variable = thread_stack=128k
set-variable = record_buffer=2M
set-variable = max_allowed_packet=2M
set-variable = wait_timeout=120
set-variable = thread_concurrency=8
set-variable = max_connect_errors=500
set-variable = long_query_time=3
log-slow-queries = /var/lib/mysql/slow_query.log
log = /var/lib/mysql/query.log
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
How to repeat:
The select query looks like this:
SELECT DISTINCT mf_mem_id FROM member_friends WHERE mem_id IN (2,1,6,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66, ..., 25394,25395) AND mf_rf = 'Y' AND mf_mem_id NOT IN (22845,20622, ..., 25394,25395);
There are several thousand items in each of the IN lists.
Here's the create statement for the member_friends table:
CREATE TABLE `member_friends` (
`mf_id` int(11) NOT NULL auto_increment,
`mem_username` varchar(200) NOT NULL default '',
`mf_name` varchar(200) NOT NULL default '',
`mf_date` datetime NOT NULL default '0000-00-00 00:00:00',
`mf_checked` enum('Y','N') NOT NULL default 'N',
`mem_id` int(11) NOT NULL default '0',
`mf_mem_id` int(11) NOT NULL default '0',
`mf_rf` enum('Y','N') NOT NULL default 'N',
PRIMARY KEY (`mf_id`),
KEY `idx_mf_mem_username` (`mem_username`),
KEY `idx_mf_mf_name` (`mf_name`),
KEY `idx_mf_mem_id` (`mem_id`),
KEY `idx_mf_mf_mem_id` (`mf_mem_id`),
KEY `mem_username` (`mem_username`,`mf_name`),
KEY `idx_mf_mem_username_mf_name` (`mem_username`,`mf_name`),
KEY `idx_mf_mf_rf` (`mf_rf`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
And there are approx. 19000 rows in the member friends table.
The mysql process does not trigger an out of memory problem every time the query is run. It may take serveral consectutive or simultaneous runs to create the out of memory. The important issue is that the mysql process is eating up several GB of memory when the query is executed.
Suggested fix:
This may be a duplicate of the problems reported in the following tickets:
17023
17019
15872
16449
I think the difference is we're seeing the problem with SELECT statements instead of UPDATES or DELETES.
Obviously the query in question is not very efficient. But on older releases of mysql, it runs (slowly) without a problem.
Description: Select with long IN clause causes the mysqld process to use excessive amounts of memory on the server. In many cases the mysqld process gets killed when the server hits an out of memory condition. The server is a dual Opteron running 64bit version of RHEL4. The mysql software is stock RPMs downloaded from the mysql site: MySQL-client-standard-5.0.18-0.rhel4.x86_64.rpm MySQL-devel-standard-5.0.18-0.rhel4.x86_64.rpm MySQL-server-standard-5.0.18-0.rhel4.x86_64.rpm MySQL-shared-standard-5.0.18-0.rhel4.x86_64.rpm Contents of the my.cnf: [mysqld] set-variable = myisam_sort_buffer_size=64M set-variable = max_connections=500 set-variable = table_cache=512 set-variable = key_buffer_size=384M set-variable = sort_buffer=80M set-variable = thread_stack=128k set-variable = record_buffer=2M set-variable = max_allowed_packet=2M set-variable = wait_timeout=120 set-variable = thread_concurrency=8 set-variable = max_connect_errors=500 set-variable = long_query_time=3 log-slow-queries = /var/lib/mysql/slow_query.log log = /var/lib/mysql/query.log [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid How to repeat: The select query looks like this: SELECT DISTINCT mf_mem_id FROM member_friends WHERE mem_id IN (2,1,6,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66, ..., 25394,25395) AND mf_rf = 'Y' AND mf_mem_id NOT IN (22845,20622, ..., 25394,25395); There are several thousand items in each of the IN lists. Here's the create statement for the member_friends table: CREATE TABLE `member_friends` ( `mf_id` int(11) NOT NULL auto_increment, `mem_username` varchar(200) NOT NULL default '', `mf_name` varchar(200) NOT NULL default '', `mf_date` datetime NOT NULL default '0000-00-00 00:00:00', `mf_checked` enum('Y','N') NOT NULL default 'N', `mem_id` int(11) NOT NULL default '0', `mf_mem_id` int(11) NOT NULL default '0', `mf_rf` enum('Y','N') NOT NULL default 'N', PRIMARY KEY (`mf_id`), KEY `idx_mf_mem_username` (`mem_username`), KEY `idx_mf_mf_name` (`mf_name`), KEY `idx_mf_mem_id` (`mem_id`), KEY `idx_mf_mf_mem_id` (`mf_mem_id`), KEY `mem_username` (`mem_username`,`mf_name`), KEY `idx_mf_mem_username_mf_name` (`mem_username`,`mf_name`), KEY `idx_mf_mf_rf` (`mf_rf`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 And there are approx. 19000 rows in the member friends table. The mysql process does not trigger an out of memory problem every time the query is run. It may take serveral consectutive or simultaneous runs to create the out of memory. The important issue is that the mysql process is eating up several GB of memory when the query is executed. Suggested fix: This may be a duplicate of the problems reported in the following tickets: 17023 17019 15872 16449 I think the difference is we're seeing the problem with SELECT statements instead of UPDATES or DELETES. Obviously the query in question is not very efficient. But on older releases of mysql, it runs (slowly) without a problem.