Bill Duffy <[EMAIL PROTECTED]> wrote on 08/31/2005 11:32:02 AM: > When I run a query like this > > DELETE FROM test_P_Cookie WHERE ViewID IN ( SELECT ViewID FROM > test_x_RM_4508_48875 ) > > it never completes. > > test_x_RM_4508_48875 contains 180139 rows. test_P_Cookie contains 22,957,702 > rows. There does not appear to be any iowait problem and CPU is about 50 % in > use if top is to be believed. I am running this on RHEL3. > > Table structures: > > CREATE TABLE `test_x_RM_4508_48875` ( > `ViewID` int(11) NOT NULL default '0', > `VisitID` int(11) NOT NULL default '0' > ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin; > > CREATE TABLE `test_P_Cookie` ( > `ViewID` int(11) NOT NULL default '0', > `P_CookieID` int(11) NOT NULL default '0', > UNIQUE KEY `test_P_Cookie1` (`ViewID`,`P_CookieID`) > ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin; > > I tried tuning around this thinking it was a performance issue, but can't seem > to get anywhere. innodb settings below: > > | innodb_additional_mem_pool_size | 20971520 | > | innodb_autoextend_increment | 8 | > | innodb_buffer_pool_awe_mem_mb | 0 | > | innodb_buffer_pool_size | 268435456 | > | innodb_data_file_path | ibdata1:10M:autoextend | > | innodb_data_home_dir | /var/lib/mysql/ | > | innodb_fast_shutdown | ON | > | innodb_file_io_threads | 4 | > | innodb_file_per_table | OFF | > | innodb_locks_unsafe_for_binlog | OFF | > | innodb_flush_log_at_trx_commit | 1 | > | innodb_flush_method | | > | innodb_force_recovery | 0 | > | innodb_lock_wait_timeout | 50 | > | innodb_log_arch_dir | /var/lib/mysql/ | > | innodb_log_archive | OFF | > | innodb_log_buffer_size | 8388608 | > | innodb_log_file_size | 268435456 | > | innodb_log_files_in_group | 2 | > | innodb_log_group_home_dir | /var/lib/mysql/ | > | innodb_max_dirty_pages_pct | 90 | > | innodb_table_locks | ON | > | innodb_max_purge_lag | 0 | > | innodb_mirrored_log_groups | 1 | > | innodb_open_files | 300 | > | innodb_thread_concurrency | 8 | > | interactive_timeout | 28800 | > > I tried this in MySQL 4.1.8, 4.1.12, and 4.1.14. Any help would be > appreciated, > > Bill >
I think that the subquery processing is what is killing your performance. In the best-case scenario, the subquery will be executed once and each row of your 22million-row table will have to check itself against a 180thousand-term IN () clause (without the benefit of an index). That just takes a lot of CPU cycles to perform. Have you tried the multi-table delete syntax? http://dev.mysql.com/doc/mysql/en/delete.html It should use an index or indexes if they available. This is how it could look (there are two valid syntaxes, this is one of them): DELETE test_P_Cookie FROM test_P_Cookie INNER JOIN test_x_RM_4508_48875 ON test_P_Cookie.ViewID = text_x_RM_4508_48875.ViewID; Because test_P_Cookie is the only table listed in the DELETE clause, that is the only table that will lose records. If you had an index on text_x_RM_4508_48875.ViewID, this will really fly. I would guess it might take roughly 20 seconds, more or less, with the dataset you describe. Without the index, you will have to wait a bit longer. I would guess in the 2-5 minute range. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
