Edit Question - KnowledgeHub

Title Body

This mysql query is running for around 10 hours and has not finished. Something is horribly wrong. Two tables (text and spam) are here. Spam stores the ids of spam entrys in text that I want to delete. DELETE FROM tname.text WHERE old_id IN (SELECT textid FROM spam); spam has just 2 columns, both are ints. 800K entries has a file size of several Mbs. Both ints are primary keys. text has 3 columns. id (prim key), text, flags. around 1200K entries, and around 2.1 gigabyte size (most spam). The server is a xeon quad, 2 gigabyte ram (don't ask me why). Only apache (why?) and mysqld is running. Its an old free bsd and mysql 4.1.2 (don't ask me why) Threads: 6 Questions: 188805 Slow queries: 318 Opens: 810 Flush tables: 1 Open tables: 157 Queries per second avg: 7.532 Mysql my.cnf: [mysqld] datadir=/usr/local/mysql log-error=/usr/local/mysql/mysqld.err pid-file=/usr/local/mysql/mysqld.pid tmpdir=/var/tmp innodb_data_home_dir = innodb_log_files_in_group = 2 join_buffer_size=2M key_buffer_size=32M max_allowed_packet=1M max_connections=800 myisam_sort_buffer_size=32M query_cache_size=8M read_buffer_size=2M sort_buffer_size=2M table_cache=256 skip-bdb log-slow-queries = slow.log long_query_time = 1 #skip-innodb #default-table-type=innodb innodb_data_file_path = /usr/local/mysql/ibdata1:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/ innodb_buffer_pool_size = 128M innodb_log_file_size = 16M innodb_log_buffer_size = 8M #innodb_flush_log_at_trx_commit=1 #innodb_additional_mem_pool_size=1M #innodb_lock_wait_timeout=50 log-bin server-id=201 [isamchk] key_buffer_size=128M read_buffer_size=128M write_buffer_size=128M sort_buffer_size=128M [myisamchk] key_buffer_size=128M[server:~] dmesg | grep memory real memory = 2146828288 (2047 MB) avail memory = 2095534080 (1998 MB) read_buffer_size=128M write_buffer_size=128M sort_buffer_size=128M tmpdir=/var/tmp

Tags (comma-separated) Cancel