what does a 'show processlist' reveal?
On 17 Jan 2001 00:08:23 +0000, John Stumbles wrote:
> On Tue, 16 Jan 2001, Jeremy D. Zawodny wrote:
>
> > On Tue, Jan 16, 2001 at 08:21:19PM +0000, John Stumbles wrote:
> >
> > > I can't readily reproduce the first-time big difference
> > > scenario.
>
> OK, I can now: I have a table -
>
> MAC_connections: Data records: 6689
> +----------+------------------+------+-----+---------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +----------+------------------+------+-----+---------+-------+
> | MAC | char(12) | | PRI | | |
> | deviceID | int(10) unsigned | YES | | NULL | |
> | ifIndex | int(10) unsigned | YES | | NULL | |
> | nMACs | int(10) unsigned | YES | | NULL | |
> | mtime | timestamp(14) | YES | | NULL | |
> +----------+------------------+------+-----+---------+-------+
>
> SELECT * from MAC_connections
> or even
> SELECT count(*) from MAC_connections
>
> hangs! (it's been sitting there for the last 10 minutes and hasn't
> produced anything!)
>
> I guess it was working OK a few hours ago as I was working on a query
> involving a LEFT JOIN to this table: suddently it stopped working and
> (after hours of hair-tearing and head-banging :-) I eventually got right
> back to simple queries like the one above and found where the problem was.
>
> I can get data from other tables OK.
>
> isamchk -e MAC_connections doesn't complain:
>
> Checking ISAM file: MAC_connections
> Data records: 6689 Deleted blocks: 0
> - check file-size
> - check delete-chain
> - check index reference
> - check records and index references
>
> What's going on? Any ideas?
>
> show status shows:
> +--------------------------+------------+
> | Variable_name | Value |
> +--------------------------+------------+
> | Aborted_clients | 79 |
> | Aborted_connects | 3 |
> | Created_tmp_tables | 289 |
> | Delayed_insert_threads | 0 |
> | Delayed_writes | 0 |
> | Delayed_errors | 0 |
> | Flush_commands | 1 |
> | Handler_delete | 56663884 |
> | Handler_read_first | 199 |
> | Handler_read_key | 645684988 |
> | Handler_read_next | 581963979 |
> | Handler_read_rnd | 624846288 |
> | Handler_update | 321370818 |
> | Handler_write | 287600901 |
> | Key_blocks_used | 7822 |
> | Key_read_requests | 1654937380 |
> | Key_reads | 157365 |
> | Key_write_requests | 255616015 |
> | Key_writes | 137012352 |
> | Max_used_connections | 31 |
> | Not_flushed_key_blocks | 0 |
> | Not_flushed_delayed_rows | 0 |
> | Open_tables | 118 |
> | Open_files | 101 |
> | Open_streams | 0 |
> | Opened_tables | 1302 |
> | Questions | 540752383 |
> | Running_threads | 32 |
> | Slow_queries | 229871 |
> | Uptime | 7891238 |
> +--------------------------+------------+
>
> show variables:
> +----------------------------+---------------------------------+
> | Variable_name | Value |
> +----------------------------+---------------------------------+
> | back_log | 5 |
> | connect_timeout | 5 |
> | basedir | /opt/local/ |
> | datadir | /DATA/mySQLdata/ |
> | delayed_insert_limit | 100 |
> | delayed_insert_timeout | 300 |
> | delayed_queue_size | 1000 |
> | join_buffer | 131072 |
> | flush_time | 0 |
> | key_buffer | 8388600 |
> | language | /opt/local/share/mysql/english/ |
> | log | OFF |
> | log_update | OFF |
> | long_query_time | 10 |
> | low_priority_updates | OFF |
> | max_allowed_packet | 1048576 |
> | max_connections | 100 |
> | max_connect_errors | 10 |
> | max_delayed_insert_threads | 20 |
> | max_join_size | 4294967295 |
> | max_sort_length | 1024 |
> | max_write_lock_count | 4294967295 |
> | net_buffer_length | 16384 |
> | pid_file | /usr/local/var/mysqld.pid |
> | port | 3306 |
> | protocol_version | 10 |
> | record_buffer | 131072 |
> | skip_locking | OFF |
> | skip_networking | OFF |
> | socket | /tmp/mysql.sock |
> | sort_buffer | 2097144 |
> | table_cache | 64 |
> | thread_stack | 65536 |
> | tmp_table_size | 1048576 |
> | tmpdir | /var/tmp/ |
> | version | 3.22.32 |
> | wait_timeout | 28800 |
> +----------------------------+---------------------------------+
>
>
> regards,
>
> --
> John Stumbles [EMAIL PROTECTED]
> I.T. Services Centre, University of Reading http://www.rdg.ac.uk/~visstmbl
> +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
> never generalise
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php