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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
