mysql show processlist命令详解

SHOW PROCESSLIST显示哪些线程正在运行。您也可以使用mysqladmin processlist语句得到此信息。如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程（也就是，与您正在使用的MySQL账户相关的线程）。请参见13.5.5.3节，“KILL语法”。如果您不使用FULL关键词，则只显示每个查询的前100个字符。

本语句报告TCP/IP连接的主机名称（采用host_name:client_port格式），以方便地判定哪个客户端正在做什么。

如果您得到“too many connections”错误信息，并且想要了解正在发生的情况，本语句是非常有用的。MySQL保留一个额外的连接，让拥有SUPER权限的账户使用，以确保管理员能够随时连接和检查系统（假设您没有把此权限给予所有的用户）。

mysql> show full processlist\G;
*************************** 1. row ***************************
     Id: 3
   User: root
   Host: 10.202.11.118:26602
     db: ud_omcs
Command: Sleep
   Time: 9
  State: 
   Info: NULL
*************************** 2. row ***************************
     Id: 208
   User: root
   Host: 10.202.37.75:40383
     db: ud_omcs
Command: Sleep
   Time: 3
  State: 
   Info: NULL
*************************** 3. row ***************************
     Id: 435
   User: root
   Host: 10.118.62.108:54675
     db: route
Command: Sleep
   Time: 1875
  State: 
   Info: NULL
*************************** 4. row ***************************
     Id: 446
   User: root
   Host: 10.118.62.108:54799
     db: route
Command: Query
   Time: 2950
  State: Sending data
   Info: update  bas_vehicle_mline_info m left join bas_vehicle_zone_info z on m.req_line_id = z.req_line_id left join bas_vehicle_cargo_info c on z.req_zone_id = c.req_zone_id SET m.is_sync=2,z.is_sync=2,c.is_sync=2 WHERE m.is_sync = 0 and   m.line_id in(10000001, 10000002, 10000003, 10000004, 10000005, 10000006, 10000007, 10000008, 10000009, 10000010, 10000011, 10000012, 10000013, 10000014, 10000015, 10000016, 10000017, 10000018, 10000019, 10000020)
*************************** 5. row ***************************
     Id: 458
   User: root
   Host: localhost
     db: route
Command: Query
   Time: 0
  State: starting
   Info: show full processlist
5 rows in set (0.00 sec)

ERROR: 
No query specified

mysql>

1、各列说明

先简单说一下各列的含义和用途，

第一列：id，不用说了吧，一个标识，你要kill一个语句的时候很有用。

user列：显示单前用户，如果不是root，这个命令就只显示你权限范围内的sql语句。

host列：显示这个语句是从哪个ip的哪个端口上发出的。呵呵，可以用来追踪出问题语句的用户。

db列：显示这个进程目前连接的是哪个数据库。

command列：显示当前连接的执行的命令，一般就是休眠（sleep），查询（query），连接（connect）。

time列：此这个状态持续的时间，单位是秒。

state列：显示使用当前连接的sql语句的状态，很重要的列，后续会有所有的状态的描述，请注意，state只是语句执行中的某一个状态，一个sql语句，已查询为例，可能需要经过copying to tmp table，Sorting result，Sending data等状态才可以完成。

info列：显示这个sql语句，因为长度有限，所以长的sql语句就显示不全，但是一个判断问题语句的重要依据。

2、这个命令中最关键的就是state列，mysql列出的状态主要有以下几种：

Checking table
　正在检查数据表（这是自动的）。
Closing tables
　正在将表中修改的数据刷新到磁盘中，同时正在关闭已经用完的表。这是一个很快的操作，如果不是这样的话，就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。
Connect Out
　复制从服务器正在连接主服务器。
Copying to tmp table on disk
　由于临时结果集大于tmp_table_size，正在将临时表从内存存储转为磁盘存储以此节省内存。
Creating tmp table
　正在创建临时表以存放部分查询结果。
deleting from main table
　服务器正在执行多表删除中的第一部分，刚删除第一个表。
deleting from reference tables
　服务器正在执行多表删除中的第二部分，正在删除其他表的记录。
Flushing tables
　正在执行FLUSH TABLES，等待其他线程关闭数据表。
Killed
　发送了一个kill请求给某线程，那么这个线程将会检查kill标志位，同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位，不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了，那么kill请求会在锁释放时马上生效。
Locked
在mysql服务器层，该线程正在等待表锁，在存储引擎级别实现的锁，例如，innoDB的行锁，并不会体现在线程状态中。对应MyISAM来说这是一个比较典型的状态，但在其他没有行锁的引擎中也经常会出现。
Sending data
　这个有多种情况，线程可能在多个状态之间传递数据，或者在生成结果集，或者在向客户端返回数据。
Sorting for group
　正在为GROUP BY做排序。
Sorting for order
　正在为ORDER BY做排序。
Opening tables
　这个过程应该会很快，除非受到其他因素的干扰。例如，在执ALTER TABLE或LOCK TABLE语句行完以前，数据表无法被其他线程打开。正尝试打开一个表。
Removing duplicates
　正在执行一个SELECT DISTINCT方式的查询，但是MySQL无法在前一个阶段优化掉那些重复的记录。因此，MySQL需要再次去掉重复的记录，然后再把结果发送给客户端。
Reopen table
　获得了对一个表的锁，但是必须在表结构修改之后才能获得这个锁。已经释放锁，关闭数据表，正尝试重新打开数据表。
Repair by sorting
　修复指令正在排序以创建索引。
Repair with keycache
　修复指令正在利用索引缓存一个一个地创建新索引。它会比Repair by sorting慢些。
Searching rows for update
　正在讲符合条件的记录找出来以备更新。它必须在UPDATE要修改相关的记录之前就完成了。
Sleeping
　正在等待客户端发送新请求.
System lock
　正在等待取得一个外部的系统锁。如果当前没有运行多个mysqld服务器同时请求同一个表，那么可以通过增加--skip-external-locking参数来禁止外部系统锁。
Upgrading lock
　INSERT DELAYED正在尝试取得一个锁表以插入新记录。
Updating
　正在搜索匹配的记录，并且修改它们。
User Lock
　正在等待GET_LOCK()。
Waiting for tables
　该线程得到通知，数据表结构已经被修改了，需要重新打开数据表以取得新的结构。然后，为了能的重新打开数据表，必须等到所有其他线程关闭这个表。以下几种情况下会产生这个通知：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE,或OPTIMIZE TABLE。
waiting for handler insert
　INSERT DELAYED已经处理完了所有待处理的插入操作，正在等待新的请求。
　大部分状态对应很快的操作，只要有一个线程保持同一个状态好几秒钟，那么可能是有问题发生了，需要检查一下。
　还有其他的状态没在上面中列出来，不过它们大部分只是在查看服务器是否有存在错误是才用得着。

mysql 查看当前连接数

命令： show processlist;
如果是root帐号，你能看到所有用户的当前连接。如果是其它普通帐号，只能看到自己占用的连接。
show processlist;只列出前100条，如果想全列出请使用show full processlist;
mysql> show processlist;

命令： show status;

[plain] view plain copy

print ?

mysql> show status;
+------------------------------------------+----------------------+
| Variable_name | Value |
+------------------------------------------+----------------------+
| Aborted_clients | 777 |
| Aborted_connects | 16 |
| Binlog_cache_disk_use | 532 |
| Binlog_cache_use | 8333221 |
| Binlog_stmt_cache_disk_use | 0 |
| Binlog_stmt_cache_use | 18732 |
| Bytes_received | 303 |
| Bytes_sent | 12460 |
| Com_admin_commands | 0 |
| Com_assign_to_keycache | 0 |
| Com_alter_db | 0 |
| Com_alter_db_upgrade | 0 |
| Com_alter_event | 0 |
| Com_alter_function | 0 |
| Com_alter_procedure | 0 |
| Com_alter_server | 0 |
| Com_alter_table | 0 |
| Com_alter_tablespace | 0 |
| Com_analyze | 0 |
| Com_begin | 0 |
| Com_binlog | 0 |
| Com_call_procedure | 0 |
| Com_change_db | 0 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_event | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_procedure | 0 |
| Com_create_server | 0 |
| Com_create_table | 0 |
| Com_create_trigger | 0 |
| Com_create_udf | 0 |
| Com_create_user | 0 |
| Com_create_view | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 0 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_event | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_procedure | 0 |
| Com_drop_server | 0 |
| Com_drop_table | 0 |
| Com_drop_trigger | 0 |
| Com_drop_user | 0 |
| Com_drop_view | 0 |
| Com_empty_query | 0 |
| Com_execute_sql | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 0 |
| Com_insert_select | 0 |
| Com_install_plugin | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_release_savepoint | 0 |
| Com_rename_table | 0 |
| Com_rename_user | 0 |
| Com_repair | 0 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_resignal | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_rollback_to_savepoint | 0 |
| Com_savepoint | 0 |
| Com_select | 1 |
| Com_set_option | 0 |
| Com_signal | 0 |
| Com_show_authors | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_contributors | 0 |
| Com_show_create_db | 0 |
| Com_show_create_event | 0 |
| Com_show_create_func | 0 |
| Com_show_create_proc | 0 |
| Com_show_create_table | 0 |
| Com_show_create_trigger | 0 |
| Com_show_databases | 0 |
| Com_show_engine_logs | 0 |
| Com_show_engine_mutex | 0 |
| Com_show_engine_status | 0 |
| Com_show_events | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 0 |
| Com_show_function_status | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_master_status | 0 |
| Com_show_open_tables | 0 |
| Com_show_plugins | 0 |
| Com_show_privileges | 0 |
| Com_show_procedure_status | 0 |
| Com_show_processlist | 6 |
| Com_show_profile | 0 |
| Com_show_profiles | 0 |
| Com_show_relaylog_events | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 2 |
| Com_show_storage_engines | 0 |
| Com_show_table_status | 0 |
| Com_show_tables | 0 |
| Com_show_triggers | 0 |
| Com_show_variables | 0 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reprepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_uninstall_plugin | 0 |
| Com_unlock_tables | 0 |
| Com_update | 0 |
| Com_update_multi | 0 |
| Com_xa_commit | 0 |
| Com_xa_end | 0 |
| Com_xa_prepare | 0 |
| Com_xa_recover | 0 |
| Com_xa_rollback | 0 |
| Com_xa_start | 0 |
| Compression | OFF |
| Connections | 1107216 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 564 |
| Created_tmp_tables | 0 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
| Innodb_buffer_pool_pages_data | 376178 |
| Innodb_buffer_pool_bytes_data | 4261109760 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_bytes_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 4419408 |
| Innodb_buffer_pool_pages_free | 0 |
| Innodb_buffer_pool_pages_misc | 18446744073709437581 |
| Innodb_buffer_pool_pages_total | 262143 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 506357 |
| Innodb_buffer_pool_read_ahead_evicted | 49891 |
| Innodb_buffer_pool_read_requests | 3550554620 |
| Innodb_buffer_pool_reads | 8777938 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 159577779 |
| Innodb_data_fsyncs | 13359191 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 133311885312 |
| Innodb_data_reads | 9323868 |
| Innodb_data_writes | 17580993 |
| Innodb_data_written | 163731449856 |
| Innodb_dblwr_pages_written | 4419408 |
| Innodb_dblwr_writes | 119202 |
| Innodb_have_atomic_builtins | ON |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 39730223 |
| Innodb_log_writes | 12957426 |
| Innodb_os_log_fsyncs | 13004841 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 25546702336 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 539547 |
| Innodb_pages_read | 9323581 |
| Innodb_pages_written | 4419408 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 26866245 |
| Innodb_row_lock_time_avg | 1367 |
| Innodb_row_lock_time_max | 55497 |
| Innodb_row_lock_waits | 19649 |
| Innodb_rows_deleted | 15511 |
| Innodb_rows_inserted | 1868056 |
| Innodb_rows_read | 1657696248 |
| Innodb_rows_updated | 7778106 |
| Innodb_truncated_status_writes | 0 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 26773 |
| Key_blocks_used | 19 |
| Key_read_requests | 67555 |
| Key_reads | 19 |
| Key_write_requests | 69364 |
| Key_writes | 11544 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 436 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 99 |
| Open_streams | 0 |
| Open_table_definitions | 327 |
| Open_tables | 1301 |
| Opened_files | 22851 |
| Opened_table_definitions | 0 |
| Opened_tables | 0 |
| Performance_schema_cond_classes_lost | 0 |
| Performance_schema_cond_instances_lost | 0 |
| Performance_schema_file_classes_lost | 0 |
| Performance_schema_file_handles_lost | 0 |
| Performance_schema_file_instances_lost | 0 |
| Performance_schema_locker_lost | 0 |
| Performance_schema_mutex_classes_lost | 0 |
| Performance_schema_mutex_instances_lost | 0 |
| Performance_schema_rwlock_classes_lost | 0 |
| Performance_schema_rwlock_instances_lost | 0 |
| Performance_schema_table_handles_lost | 0 |
| Performance_schema_table_instances_lost | 0 |
| Performance_schema_thread_classes_lost | 0 |
| Performance_schema_thread_instances_lost | 0 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 3426 |
| Qcache_free_memory | 38784160 |
| Qcache_hits | 9223399 |
| Qcache_inserts | 3505151 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 400457 |
| Qcache_queries_in_cache | 19336 |
| Qcache_total_blocks | 42289 |
| Queries | 27883378 |
| Questions | 9 |
| Rpl_status | AUTH_MASTER |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 0 |
| Slave_heartbeat_period | 0.000 |
| Slave_open_temp_tables | 0 |
| Slave_received_heartbeats | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 30 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | |
| Ssl_cipher_list | |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_default_timeout | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | NONE |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | |
| Table_locks_immediate | 13127661 |
| Table_locks_waited | 648 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 6 |
| Threads_cached | 7 |
| Threads_connected | 2 |
| Threads_created | 54593 |
| Threads_running | 2 |
| Uptime | 651324 |
| Uptime_since_flush_status | 651324 |
+------------------------------------------+----------------------+
312 rows in set (0.01 sec)

Aborted_clients 由于客户没有正确关闭连接已经死掉，已经放弃的连接数量。
Aborted_connects 尝试已经失败的MySQL服务器的连接的次数。
Connections 试图连接MySQL服务器的次数。
Created_tmp_tables 当执行语句时，已经被创造了的隐含临时表的数量。
Delayed_insert_threads 正在使用的延迟插入处理器线程的数量。
Delayed_writes 用INSERT DELAYED写入的行数。
Delayed_errors 用INSERT DELAYED写入的发生某些错误(可能重复键值)的行数。
Flush_commands 执行FLUSH命令的次数。
Handler_delete 请求从一张表中删除行的次数。
Handler_read_first 请求读入表中第一行的次数。
Handler_read_key 请求数字基于键读行。
Handler_read_next 请求读入基于一个键的一行的次数。
Handler_read_rnd 请求读入基于一个固定位置的一行的次数。
Handler_update 请求更新表中一行的次数。
Handler_write 请求向表中插入一行的次数。
Key_blocks_used 用于关键字缓存的块的数量。
Key_read_requests 请求从缓存读入一个键值的次数。
Key_reads 从磁盘物理读入一个键值的次数。
Key_write_requests 请求将一个关键字块写入缓存次数。
Key_writes 将一个键值块物理写入磁盘的次数。
Max_used_connections 同时使用的连接的最大数目。
Not_flushed_key_blocks 在键缓存中已经改变但是还没被清空到磁盘上的键块。
Not_flushed_delayed_rows 在INSERT DELAY队列中等待写入的行的数量。
Open_tables 打开表的数量。
Open_files 打开文件的数量。
Open_streams 打开流的数量(主要用于日志记载）
Opened_tables 已经打开的表的数量。
Questions 发往服务器的查询的数量。
Slow_queries 要花超过long_query_time时间的查询数量。
Threads_connected 当前打开的连接的数量。
Threads_running 不在睡眠的线程数量。

Uptime 服务器工作了多少秒。

查看配置参数：

SHOW VARIABLES LIKE '%timeout%'

[plain] view plain copy

print ?

Variable_name Value
------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------
auto_increment_increment 1
auto_increment_offset 1
autocommit ON
automatic_sp_privileges ON
back_log 384
basedir /usr
big_tables OFF
binlog_cache_size 1048576
binlog_direct_non_transactional_updates OFF
binlog_format MIXED
binlog_stmt_cache_size 32768
bulk_insert_buffer_size 67108864
character_set_client utf8
character_set_connection utf8
character_set_database utf8
character_set_filesystem binary
character_set_results utf8
character_set_server utf8
character_set_system utf8
character_sets_dir /usr/share/mysql/charsets/
collation_connection utf8_general_ci
collation_database utf8_general_ci
collation_server utf8_general_ci
completion_type NO_CHAIN
concurrent_insert AUTO
connect_timeout 10
datadir /var/lib/mysql/
date_format %Y-%m-%d
datetime_format %Y-%m-%d %H:%i:%s
default_storage_engine InnoDB
default_week_format 0
delay_key_write ON
delayed_insert_limit 100
delayed_insert_timeout 300
delayed_queue_size 1000
div_precision_increment 4
engine_condition_pushdown ON
error_count 0
event_scheduler OFF
expire_logs_days 30
external_user
flush OFF
flush_time 0
foreign_key_checks ON
ft_boolean_syntax + -><()~*:""&|
ft_max_word_len 84
ft_min_word_len 4
ft_query_expansion_limit 20
ft_stopword_file (built-in)
general_log OFF
general_log_file /var/lib/mysql/VM84.log
group_concat_max_len 1024
have_compress YES
have_crypt YES
have_csv YES
have_dynamic_loading YES
have_geometry YES
have_innodb YES
have_ndbcluster NO
have_openssl DISABLED
have_partitioning YES
have_profiling YES
have_query_cache YES
have_rtree_keys YES
have_ssl DISABLED
have_symlink YES
hostname VM84
identity 0
ignore_builtin_innodb OFF
init_connect
init_file
init_slave
innodb_adaptive_flushing ON
innodb_adaptive_hash_index ON
innodb_additional_mem_pool_size 16777216
innodb_autoextend_increment 8
innodb_autoinc_lock_mode 1
innodb_buffer_pool_instances 1
innodb_buffer_pool_size 4294967296
innodb_change_buffering all
innodb_checksums ON
innodb_commit_concurrency 0
innodb_concurrency_tickets 500
innodb_data_file_path ibdata1:10M:autoextend
innodb_data_home_dir
innodb_doublewrite ON
innodb_fast_shutdown 1
innodb_file_format Antelope
innodb_file_format_check ON
innodb_file_format_max Antelope
innodb_file_per_table ON
innodb_flush_log_at_trx_commit 2
innodb_flush_method
innodb_force_load_corrupted OFF
innodb_force_recovery 0
innodb_io_capacity 200
innodb_large_prefix OFF
innodb_lock_wait_timeout 50
innodb_locks_unsafe_for_binlog OFF
innodb_log_buffer_size 16777216
innodb_log_file_size 536870912
innodb_log_files_in_group 2
innodb_log_group_home_dir ./
innodb_max_dirty_pages_pct 75
innodb_max_purge_lag 0
innodb_mirrored_log_groups 1
innodb_old_blocks_pct 37
innodb_old_blocks_time 0
innodb_open_files 300
innodb_print_all_deadlocks OFF
innodb_purge_batch_size 20
innodb_purge_threads 0
innodb_random_read_ahead OFF
innodb_read_ahead_threshold 56
innodb_read_io_threads 4
innodb_replication_delay 0
innodb_rollback_on_timeout OFF
innodb_rollback_segments 128
innodb_spin_wait_delay 6
innodb_stats_method nulls_equal
innodb_stats_on_metadata ON
innodb_stats_sample_pages 8
innodb_strict_mode OFF
innodb_support_xa ON
innodb_sync_spin_loops 30
innodb_table_locks ON
innodb_thread_concurrency 0
innodb_thread_sleep_delay 10000
innodb_use_native_aio ON
innodb_use_sys_malloc ON
innodb_version 5.5.36
innodb_write_io_threads 4
insert_id 0
interactive_timeout 300
join_buffer_size 8388608
keep_files_on_create OFF
key_buffer_size 33554432
key_cache_age_threshold 300
key_cache_block_size 1024
key_cache_division_limit 100
large_files_support ON
large_page_size 0
large_pages OFF
last_insert_id 0
lc_messages en_US
lc_messages_dir /usr/share/mysql/
lc_time_names en_US
license GPL
local_infile ON
lock_wait_timeout 31536000
locked_in_memory OFF
log OFF
log_bin ON
log_bin_trust_function_creators OFF
log_error /var/lib/mysql/VM84.err
log_output FILE
log_queries_not_using_indexes OFF
log_slave_updates OFF
log_slow_queries ON
log_warnings 1
long_query_time 2.000000
low_priority_updates OFF
lower_case_file_system OFF
lower_case_table_names 0
max_allowed_packet 16777216
max_binlog_cache_size 18446744073709547520
max_binlog_size 1073741824
max_binlog_stmt_cache_size 18446744073709547520
max_connect_errors 1024
max_connections 2048
max_delayed_threads 20
max_error_count 64
max_heap_table_size 67108864
max_insert_delayed_threads 20
max_join_size 18446744073709551615
max_length_for_sort_data 1024
max_long_data_size 16777216
max_prepared_stmt_count 16382
max_relay_log_size 0
max_seeks_for_key 18446744073709551615
max_sort_length 1024
max_sp_recursion_depth 0
max_tmp_tables 32
max_user_connections 0
max_write_lock_count 18446744073709551615
metadata_locks_cache_size 1024
min_examined_row_limit 0
multi_range_count 256
myisam_data_pointer_size 6
myisam_max_sort_file_size 21474836480
myisam_mmap_size 18446744073709551615
myisam_recover_options DEFAULT
myisam_repair_threads 1
myisam_sort_buffer_size 536870912
myisam_stats_method nulls_unequal
myisam_use_mmap OFF
net_buffer_length 16384
net_read_timeout 30
net_retry_count 10
net_write_timeout 60
new OFF
old OFF
old_alter_table OFF
old_passwords OFF
open_files_limit 65535
optimizer_prune_level 1
optimizer_search_depth 62
optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on
performance_schema OFF
performance_schema_events_waits_history_long_size 10000
performance_schema_events_waits_history_size 10
performance_schema_max_cond_classes 80
performance_schema_max_cond_instances 1000
performance_schema_max_file_classes 50
performance_schema_max_file_handles 32768
performance_schema_max_file_instances 10000
performance_schema_max_mutex_classes 200
performance_schema_max_mutex_instances 1000000
performance_schema_max_rwlock_classes 30
performance_schema_max_rwlock_instances 1000000
performance_schema_max_table_handles 100000
performance_schema_max_table_instances 50000
performance_schema_max_thread_classes 50
performance_schema_max_thread_instances 1000
pid_file /var/run/mysqld/mysqld.pid
plugin_dir /usr/lib64/mysql/plugin/
port 3306
preload_buffer_size 32768
profiling OFF
profiling_history_size 15
protocol_version 10
proxy_user
pseudo_slave_mode OFF
pseudo_thread_id 2634591
query_alloc_block_size 8192
query_cache_limit 2097152
query_cache_min_res_unit 4096
query_cache_size 67108864
query_cache_type ON
query_cache_wlock_invalidate OFF
query_prealloc_size 8192
rand_seed1 0
rand_seed2 0
range_alloc_block_size 4096
read_buffer_size 2097152
read_only OFF
read_rnd_buffer_size 16777216
relay_log
relay_log_index
relay_log_info_file relay-log.info
relay_log_purge ON
relay_log_recovery OFF
relay_log_space_limit 0
report_host
report_password
report_port 3306
report_user
rpl_recovery_rank 0
secure_auth OFF
secure_file_priv
server_id 84
skip_external_locking ON
skip_name_resolve ON
skip_networking OFF
skip_show_database OFF
slave_compressed_protocol OFF
slave_exec_mode STRICT
slave_load_tmpdir /tmp
slave_max_allowed_packet 1073741824
slave_net_timeout 3600
slave_skip_errors 1032,1062
slave_transaction_retries 10
slave_type_conversions
slow_launch_time 2
slow_query_log ON
slow_query_log_file /var/lib/mysql/VM84-slow.log
socket /var/lib/mysql/mysql.sock
sort_buffer_size 8388608
sql_auto_is_null OFF
sql_big_selects ON
sql_big_tables OFF
sql_buffer_result OFF
sql_log_bin ON
sql_log_off OFF
sql_low_priority_updates OFF
sql_max_join_size 18446744073709551615
sql_mode
sql_notes ON
sql_quote_show_create ON
sql_safe_updates OFF
sql_select_limit 18446744073709551615
sql_slave_skip_counter 0
sql_warnings OFF
ssl_ca
ssl_capath
ssl_cert
ssl_cipher
ssl_key
storage_engine InnoDB
stored_program_cache 256
sync_binlog 0
sync_frm ON
sync_master_info 0
sync_relay_log 0
sync_relay_log_info 0
system_time_zone CST
table_definition_cache 400
table_open_cache 2048
thread_cache_size 8
thread_concurrency 16
thread_handling one-thread-per-connection
thread_stack 262144
time_format %H:%i:%s
time_zone SYSTEM
timed_mutexes OFF
timestamp 1408104399
tmp_table_size 67108864
tmpdir /tmp
transaction_alloc_block_size 8192
transaction_prealloc_size 4096
tx_isolation REPEATABLE-READ
unique_checks ON
updatable_views_with_limit YES
version 5.5.36-log
version_comment MySQL Community Server (GPL) by Remi
version_compile_machine x86_64
version_compile_os Linux
wait_timeout 28800
warning_count 0

posted on 2014-01-07 09:41 duanxz 阅读(2800) 评论(0) 编辑收藏举报

mysql show processlist命令 详解

1、各列说明

2、这个命令中最关键的就是state列，mysql列出的状态主要有以下几种：

mysql show processlist命令详解