Hi Mark,
The system in question has 1GB of RAM in it. As far
as I can tell, the box does not get stuck swapping
when the system has a query (or several) in this
state. If I log in via the command line client and
kill the query, the system continues on it's way like
nothing was wrong in the first place. It almost seems
to me that the server is getting into some strange
state while writing to temporary table. For some
reason, this fails and instead of erroring out, the
query stays in a perpetual running state.
On MySQL 4.0.16, a "key_buffer_size" of 134217728
translates into 134217728/1024 bytes per key = 131072
possible cached key values. Because this table (in
addition to several others) may have several million
rows, in order to cache say 3 million keys, I'd have
to allocate about 3GB worth of ram just to this single
buffer.
Thanks for the info though!
--- Mark Maunder <[EMAIL PROTECTED]> wrote:
> In my experience, inserting into a table with a
> unique key and more than
> 5 million records can be very slow because (AFAIK)
> it has to scan the
> entire index to check if the new record is unique
> from the PRIMARY KEY's
> point of view. (I think)
>
> If you don't have much ram (your 128 meg key buffer
> suggests you dont)
> and your disk channel isn't particularly fast, this
> may take a very long
> time.
>
> Your tmp_table_size of 200 Megs is large considering
> your key buffer is
> 128M. If the machine doesn't have enough RAM, it may
> end up swapping
> heavily trying to keep the tmp table in memory and
> get stuck they way
> you've described - although I must admit I'm not
> sure what mysql does in
> a situation where tmp_table_size is too large.
>
> How much RAM does the machine have? Can you check
> swap activity?
>
> On Thu, 2004-11-11 at 00:27, foo bar wrote:
> > Here's the whole the query, table structure, table
> > length and show variables output:
> >
> > mysql> desc summary;
> >
>
+-------------------------+------------------+------+-----+---------------------+----------------+
> > | Field | Type |
> Null |
> > Key | Default | Extra |
> >
>
+-------------------------+------------------+------+-----+---------------------+----------------+
> > | id | int(10) unsigned |
> |
> > PRI | NULL | auto_increment |
> > | host_id | int(10) unsigned |
> |
> > MUL | 0 | |
> > | alias_domain_id | int(10) unsigned | YES
> |
> > MUL | NULL | |
> > | domain_id | int(10) unsigned | YES
> |
> > MUL | NULL | |
> > | alias_id | int(10) unsigned | YES
> |
> > MUL | NULL | |
> > | sender_domain_id | int(10) unsigned | YES
> |
> > MUL | NULL | |
> > | sender_alias_id | int(10) unsigned | YES
> |
> > MUL | NULL | |
> > | time | datetime |
> |
> > MUL | 0000-00-00 00:00:00 | |
> > | val1 | int(10) unsigned |
> |
> > | 0 | |
> > | val2 | int(10) unsigned |
> |
> > | 0 | |
> > | processed | int(10) unsigned |
> |
> > | 0 | |
> >
>
+-------------------------+------------------+------+-----+---------------------+----------------+
> >
> > select count(*) from summary;
> > +----------+
> > | count(*) |
> > +----------+
> > | 34759085 |
> > +----------+
> > 1 row in set (0.05 sec)
> >
> > Query:
> > create temporary table tmp (PRIMARY
> > KEY(alias_id,domain_id))
> > select
> > alias_id,
> > domain_id,
> > sum(val1) rank
> > from summary
> > where 1=1
> > and time >= '2004-11-01 11:00:00'
> > and time <= '2004-11-09 11:00:00'
> > group by 1, 2
> > order by rank desc
> > limit 5
> >
> > 'show variables;' output:
> > Variable_name Value
> > back_log 50
> > basedir /opt/mysql-pro-4.0.16/
> > binlog_cache_size 32768
> > bulk_insert_buffer_size 8388608
> > character_set latin1
> > character_sets latin1 big5 czech euc_kr gb2312
> gbk
> > latin1_de sjis tis620 ujis dec8 dos german1 hp8
> > koi8_ru latin2 swe7 usa7 cp1251 danish hebrew
> win1251
> > estonia hungarian koi8_ukr win1251ukr greek
> win1250
> > croat cp1257 latin5
> > concurrent_insert ON
> > connect_timeout 5
> > convert_character_set
> > datadir /opt/mysql-pro-4.0.16/data/
> > default_week_format 0
> > delay_key_write ON
> > delayed_insert_limit 100
> > delayed_insert_timeout 300
> > delayed_queue_size 1000
> > flush OFF
> > flush_time 0
> > ft_boolean_syntax + -><()~*:""&|
> > ft_min_word_len 4
> > ft_max_word_len 254
> > ft_max_word_len_for_sort 20
> > ft_stopword_file (built-in)
> > have_bdb NO
> > have_crypt YES
> > have_innodb YES
> > have_isam YES
> > have_raid NO
> > have_symlink YES
> > have_openssl NO
> > have_query_cache YES
> > init_file
> > innodb_additional_mem_pool_size 1048576
> > innodb_buffer_pool_size 8388608
> > innodb_data_file_path ibdata1:10M:autoextend
> > innodb_data_home_dir
> > innodb_file_io_threads 4
> > innodb_force_recovery 0
> > innodb_thread_concurrency 8
> > innodb_flush_log_at_trx_commit 1
> > innodb_fast_shutdown ON
> > innodb_flush_method
> > innodb_lock_wait_timeout 50
> > innodb_log_arch_dir ./
> > innodb_log_archive OFF
> > innodb_log_buffer_size 1048576
> > innodb_log_file_size 5242880
> > innodb_log_files_in_group 2
> > innodb_log_group_home_dir ./
> > innodb_mirrored_log_groups 1
> > innodb_max_dirty_pages_pct 90
> > interactive_timeout 28800
> > join_buffer_size 131072
> > key_buffer_size 134217728
> > language
> > /opt/mysql-pro-4.0.16/share/mysql/english/
> > large_files_support ON
> > local_infile ON
> > locked_in_memory OFF
> > log ON
> > log_update OFF
> > log_bin OFF
> > log_slave_updates OFF
> > log_slow_queries OFF
> > log_warnings OFF
> > long_query_time 10
> > low_priority_updates OFF
> > lower_case_table_names OFF
> > max_allowed_packet 1048576
> > max_binlog_cache_size 4294967295
> > max_binlog_size 1073741824
> > max_connections 100
> > max_connect_errors 10
> > max_delayed_threads 20
> > max_heap_table_size 16777216
> > max_join_size 4294967295
> > max_relay_log_size 0
> > max_seeks_for_key 4294967295
> > max_sort_length 1024
> > max_user_connections 0
> > max_tmp_tables 32
> > max_write_lock_count 4294967295
> > myisam_max_extra_sort_file_size 268435456
> > myisam_max_sort_file_size 2147483647
> > myisam_repair_threads 1
> > myisam_recover_options OFF
> > myisam_sort_buffer_size 8388608
> > net_buffer_length 16384
> > net_read_timeout 30
> > net_retry_count 10
> > net_write_timeout 60
> > new OFF
> > open_files_limit 1024
> > pid_file
> > /opt/mysql-pro-4.0.16/data/testsystem.pid
>
=== message truncated ===
__________________________________
Do you Yahoo!?
Check out the new Yahoo! Front Page.
www.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
