Thanks. I was already looking at ways to rewrite this query - the link will be
helpful.
I would have expected the query to be slow, but it looked to me like the
database stopped processing the query at all after a while. Now that I
understand just how slow it might be even under the best of circumstances
though, I think rewriting it is the way to go.
Thanks again,
Bill
Quoting [EMAIL PROTECTED]:
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
