帮我看一下，我的show global status 有什么问题

需要怎么样调整，现在的slow query 不少啊myisam 表，mysql> show global status;
+-----------------------------------+------------+
| Variable_name                     | Value      |
+-----------------------------------+------------+
| Aborted_clients                   | 610        |
| Aborted_connects                  | 0          |
| Binlog_cache_disk_use             | 0          |
| Binlog_cache_use                  | 0          |
| Bytes_received                    | 4084206342 |
| Bytes_sent                        | 670517952  |
| Com_admin_commands                | 336        |
| Com_alter_db                      | 0          |
| Com_alter_table                   | 600        |
| Com_analyze                       | 0          |
| Com_backup_table                  | 0          |
| Com_begin                         | 0          |
| Com_change_db                     | 277        |
| Com_change_master                 | 0          |
| Com_check                         | 0          |
| Com_checksum                      | 0          |
| Com_commit                        | 38811      |
| Com_create_db                     | 0          |
| Com_create_function               | 0          |
| Com_create_index                  | 2          |
| Com_create_table                  | 300        |
| Com_dealloc_sql                   | 0          |
| Com_delete                        | 3154       |
| Com_delete_multi                  | 308        |
| Com_do                            | 0          |
| Com_drop_db                       | 0          |
| Com_drop_function                 | 0          |
| Com_drop_index                    | 1          |
| Com_drop_table                    | 300        |
| Com_drop_user                     | 0          |
| Com_execute_sql                   | 0          |
| Com_flush                         | 3          |
| Com_grant                         | 0          |
| Com_ha_close                      | 0          |
| Com_ha_open                       | 0          |
| Com_ha_read                       | 0          |
| Com_help                          | 0          |
| Com_insert                        | 1473283    |
| Com_insert_select                 | 241        |
| Com_kill                          | 0          |
| Com_load                          | 0          |
| Com_load_master_data              | 0          |
| Com_load_master_table             | 0          |
| Com_lock_tables                   | 417        |
| Com_optimize                      | 0          |
| Com_preload_keys                  | 0          |
| Com_prepare_sql                   | 0          |
| Com_purge                         | 0          |
| Com_purge_before_date             | 0          |
| Com_rename_table                  | 0          |
| Com_repair                        | 0          |
| Com_replace                       | 0          |
| Com_replace_select                | 0          |
| Com_reset                         | 0          |
| Com_restore_table                 | 0          |
| Com_revoke                        | 0          |
| Com_revoke_all                    | 0          |
| Com_rollback                      | 113        |
| Com_savepoint                     | 0          |
| Com_select                        | 7251068    |
| Com_set_option                    | 958234     |
| Com_show_binlog_events            | 0          |
| Com_show_binlogs                  | 1          |
| Com_show_charsets                 | 35         |
| Com_show_collations               | 680        |
| Com_show_column_types             | 0          |
| Com_show_create_db                | 0          |
| Com_show_create_table             | 1639       |
| Com_show_databases                | 96         |
| Com_show_errors                   | 0          |
| Com_show_fields                   | 2473       |
| Com_show_grants                   | 0          |
| Com_show_innodb_status            | 0          |
| Com_show_keys                     | 388        |
| Com_show_logs                     | 0          |
| Com_show_master_status            | 0          |
| Com_show_ndb_status               | 0          |
| Com_show_new_master               | 0          |
| Com_show_open_tables              | 0          |
| Com_show_privileges               | 0          |
| Com_show_processlist              | 340        |
| Com_show_slave_hosts              | 0          |
| Com_show_slave_status             | 0          |
| Com_show_status                   | 3050       |
| Com_show_storage_engines          | 0          |
| Com_show_tables                   | 224        |
| Com_show_triggers                 | 1637       |
| Com_show_variables                | 699        |
| Com_show_warnings                 | 0          |
| Com_slave_start                   | 0          |
| Com_slave_stop                    | 0          |
| Com_stmt_close                    | 897837     |
| Com_stmt_execute                  | 912317     |
| Com_stmt_fetch                    | 0          |
| Com_stmt_prepare                  | 897837     |
| Com_stmt_reset                    | 0          |
| Com_stmt_send_long_data           | 0          |
| Com_truncate                      | 0          |
| Com_unlock_tables                 | 417        |
| Com_update                        | 384930     |
| 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                       | 797270     |
| Created_tmp_disk_tables           | 3486       |
| Created_tmp_files                 | 9          |
| Created_tmp_tables                | 138203     |
| Delayed_errors                    | 0          |
| Delayed_insert_threads            | 0          |
| Delayed_writes                    | 0          |
| Flush_commands                    | 1          |
| Handler_commit                    | 46311      |
| Handler_delete                    | 285850     |
| Handler_discover                  | 0          |
| Handler_prepare                   | 0          |
| Handler_read_first                | 67265      |
| Handler_read_key                  | 26894285   |
| Handler_read_next                 | 398816483  |
| Handler_read_prev                 | 111644361  |
| Handler_read_rnd                  | 530421     |
| Handler_read_rnd_next             | 70476818   |
| Handler_rollback                  | 10         |
| Handler_savepoint                 | 0          |
| Handler_savepoint_rollback        | 0          |
| Handler_update                    | 828571     |
| Handler_write                     | 15495286   |
| Key_blocks_not_flushed            | 4294967291 |
| Key_blocks_unused                 | 447202     |
| Key_blocks_used                   | 463920     |
| Key_read_requests                 | 100174317  |
| Key_reads                         | 1272938    |
| Key_write_requests                | 16085485   |
| Key_writes                        | 3340404    |
| Last_query_cost                   | 0.000000   |
| Max_used_connections              | 101        |
| Not_flushed_delayed_rows          | 0          |
| Open_files                        | 22         |
| Open_streams                      | 0          |
| Open_tables                       | 64         |
| Opened_tables                     | 6014       |
| Questions                         | 12717693   |
| Rpl_status                        | NULL       |
| Select_full_join                  | 294        |
| Select_full_range_join            | 0          |
| Select_range                      | 36644      |
| Select_range_check                | 10969      |
| Select_scan                       | 24964      |
| Slow_launch_threads               | 0          |
| Slow_queries                      | 276889     |
| Sort_merge_passes                 | 6          |
| Sort_range                        | 2543       |
| Sort_rows                         | 109214     |
| Sort_scan                         | 1256       |
| Table_locks_immediate             | 2428657    |
| Table_locks_waited                | 2035       |
| Tc_log_max_pages_used             | 0          |
| Tc_log_page_size                  | 0          |
| Tc_log_page_waits                 | 0          |
| Threads_cached                    | 0          |
| Threads_connected                 | 6          |
| Threads_created                   | 797269     |
| Threads_running                   | 1          |
| Uptime                            | 1724087    |
+-----------------------------------+------------+

解决方案 »

免费领取超大流量手机卡，每月29元包185G流量+100分钟通话, 中国电信官方发货

MySQL 5.0.22-log         uptime 20 5:51:37      Fri Dec 18 16:35:08 2009__ Key _________________________________________________________________
Buffer used   453.05M of 512.00M  %Used:  88.49
  Current     108.89M            %Usage:  21.27
Write hit      78.74%
Read hit       98.72%__ Questions ___________________________________________________________
Total          12.91M     7.4/s
  DMS           9.19M     5.3/s  %Total:  71.21
  Com_          3.86M     2.2/s           29.93
  -Unknown    952.02k     0.5/s            7.38
  COM_QUIT    804.34k     0.5/s            6.23
Slow 1 s      277.01k     0.2/s            2.15  %DMS:   3.01  Log:  ON
DMS             9.19M     5.3/s           71.21
  SELECT        7.31M     4.2/s           56.64         79.53
  INSERT        1.48M     0.8/s           11.50         16.14
  UPDATE      394.05k     0.2/s            3.05          4.29
  DELETE        3.61k     0.0/s            0.03          0.04
  REPLACE           0       0/s            0.00          0.00
Com_            3.86M     2.2/s           29.93
  set_option  978.54k     0.6/s            7.58
  stmt_execut 952.99k     0.5/s            7.38
  stmt_close  937.84k     0.5/s            7.27__ SELECT and Sort _____________________________________________________
Scan           25.78k     0.0/s %SELECT:   0.35
Range          38.82k     0.0/s            0.53
Full join         312     0.0/s            0.00
Range check    11.72k     0.0/s            0.16
Full rng join       0       0/s            0.00
Sort scan       1.38k     0.0/s
Sort range      2.70k     0.0/s
Sort mrg pass       6     0.0/s__ Table Locks _________________________________________________________
Waited          2.04k     0.0/s  %Total:   0.08
Immediate       2.59M     1.5/s__ Tables ______________________________________________________________
Open               64 of   64    %Cache: 100.00
Opened          6.07k     0.0/s__ Connections _________________________________________________________
Max used          101 of  100      %Max: 101.00
Total         804.66k     0.5/s__ Created Temp ________________________________________________________
Disk table      3.50k     0.0/s
Table         146.60k     0.1/s    Size:  32.0M
File                9     0.0/s__ Threads _____________________________________________________________
Running             1 of   38
Cached              0 of    0      %Hit:      0
Created       804.66k     0.5/s
Slow                0       0/s__ Aborted _____________________________________________________________
Clients           628     0.0/s
Connects            0       0/s__ Bytes _______________________________________________________________
Sent          788.05M   450.5/s
Received        4.12G    2.4k/s
用mysqlsla 查看主要是这条sql语句很厉害，有75.86%
以前没调整索引大小的时候这个比例是88%,现在好像降下来一点了Count         : 14.37k  (75.86%)
Time          : 967006 s total, 67.302756 s avg, 2 s to 22138 s max  (54.62%)
  95% of Time : 40419 s total, 2.961316 s avg, 2 s to 7 s max
Lock Time (s) : 531915 s total, 37.02081 s avg, 0 to 20884 s max  (40.59%)
  95% of Lock : 0 total, 0 avg, 0 to 0 max
Rows sent     : 17 avg, 0 to 20 max  (83.54%)
Rows examined : 3.60k avg, 12 to 4.62M max  (68.03%)
Database      : tb
Users         :
        root@localhost  : 100.00% (1436 of query, 93.50% (17709) of all usersQuery abstract:
SELECT iid,title,pic_path,price FROM tb_goods WHERE nick='S' ORDER BY lmsn DESC LIMIT N,N;explain Query :
mysql> explain select iid,title,pic_path,price from tb_goods where nick='vivizone' order by lmsn desc limit 480,20 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_goods
         type: ref
possible_keys: nickgood
          key: nickgood
      key_len: 32
          ref: const
         rows: 806
        Extra: Using where
1 row in set (0.00 sec)
SELECT iid,title,pic_path,price FROM tb_goods WHERE nick='S' ORDER BY lmsn DESC LIMIT N,N;需要一个 (nick,lmsn) 的复合索引。
现在的索引就是这样| tb_goods |          1 | nickgood  |            1 | nick        | A         |       54177 |       10 | NULL   |      | BTREE      | NULL    |
| tb_goods |          1 | nickgood  |            2 | lmsn        | A         |      328451 |     NULL | NULL   |      | BTREE      | NULL    |
SELECT iid,title,pic_path,price FROM tb_goods WHERE nick='S' 速度如何SELECT iid,title,pic_path,price FROM tb_goods ORDER BY lmsn DESC LIMIT N,N 速度如何
75%左右slow query 都是 "SELECT iid,title,pic_path,price FROM tb_goods ORDER BY lmsn DESC LIMIT N,N"SELECT iid,title,pic_path,price FROM tb_goods WHERE nick='S' 速度应该比 "SELECT iid,title,pic_path,price FROM tb_goods ORDER BY lmsn DESC LIMIT N,N" 好一点吧，
SELECT iid,title,pic_path,price FROM tb_goods ORDER BY lmsn DESC LIMIT M,N
这里如果M比较大的话出现slow query的可能性也比较大Count         : 14.91k  (74.34%)
Time          : 968333 s total, 64.953917 s avg, 2 s to 22138 s max  (54.56%)
  95% of Time : 41532 s total, 2.932637 s avg, 2 s to 7 s max
Lock Time (s) : 531915 s total, 35.679836 s avg, 0 to 20884 s max  (40.59%)
  95% of Lock : 0 total, 0 avg, 0 to 0 max
Rows sent     : 17 avg, 0 to 20 max  (10.29%)
Rows examined : 3.48k avg, 12 to 4.62M max  (60.50%)
Database      : tb
Users         :
        root@localhost  : 100.00% (14908) of query, 93.70% (18791) of all usersQuery abstract:
SELECT iid,title,pic_path,price FROM tb_goods WHERE nick='S' ORDER BY lmsn DESC LIMIT N,N;Query sample:
select iid,title,pic_path,price from tb_goods where nick='cinderella鏉庨噾濞?
                                 order by lmsn desc
                                        limit 100,20;
你在lmsn字段上建立索引试试，去掉复合索引，看看速度如何
单独的索引nick,lmsn 这样吗？现在表大概有520w rows ,改起来太慢了，这样能用到2个索引？
tb_goods |          1 | lmsn      |            1 | lmsn        | A         |        3085 |     NULL | NULL   |      | BTREElmsn 上的粒度太小了吧，520w 行不同的才3085个
独立索引不会比复合索引快，你的表是什么存储引擎？利用一下show profiles 来看一下具体每一步骤的时间消耗。
不是，只是一个取舍，如果只有LIMIT M,N慢，那么在lmsn字段上建立索引，速度估计要快一些
不可能的，如果是 where a=?? order by b这种情况下，单独在 b 上创建索引，可以说是根本没有用的。　
可以利用的索引应该是 (a,b) 或者 (a)从你的执行计划上看应该是没有问题了。现在需要调整的或者是系统参数，或者是其它的结构。利用一下show profiles 来看一下具体每一步骤的时间消耗。
在nick,lmsn分开建立索引会快些，因为where语句和排序是分开使用不同的字段。
至于改起来慢，那只是一时，如果不改是长期慢。
慢速sql语句：
SELECT iid,title,pic_path,price FROM tb_goods WHERE nick='S' ORDER BY lmsn DESC LIMIT N,N;
show profiles 我的mysql 5.0.22 不支持啊
nick,lmsn分开建，2个索引都能用到，不是说 mysql只能用一个索引吗？我的mysql 版本5.0.22
在nick,lmsn分开建立索引会快些，因为where语句和排序是分开使用不同的字段。
不可能吧。试想一下，1000 个人，10种颜色的帽子。
现在让你把戴红帽子的人挑出来按身高排序.
你手上有两张表格 (索引)1. 颜色, 目前队列中的位置
2. 身高, 目前队列中的位置
SELECT iid,title,pic_path,price FROM tb_goods WHERE nick='S' ORDER BY lmsn DESC limit 1看一下需要多久？
limit 1 的话，很快，一般在0.01左右
分别给出以下的时间消耗。SELECT SQL_NO_CACHE iid,title,pic_path,price FROM tb_goods WHERE nick='S' ORDER BY lmsn DESC limit 1
0.01 sSELECT SQL_NO_CACHE  iid,title,pic_path,price FROM tb_goods WHERE nick='S'
??? sSELECT SQL_NO_CACHE iid,title,pic_path,price FROM tb_goods WHERE nick='S' ORDER BY lmsn
??? s SELECT SQL_NO_CACHE iid,title,pic_path,price FROM tb_goods WHERE nick='S' ORDER BY lmsn DESC LIMIT N,N;
??? s
select SQL_NO_CACHE iid,title,pic_path,price from tb_goods where nick='dmcdf' order by lmsn desc  limit 0,20; 0.01s
select SQL_NO_CACHE iid,title,pic_path,price from tb_goods where nick='dmcdf'  limit 0,20; 0.07sselect SQL_NO_CACHE iid,title,pic_path,price from tb_goods where nick='dmcdf' order by lmsn desc  limit 360,20; 0.20s
在slow query中有这个
select  iid,title,pic_path,price from tb_goods where nick='dmcdf' order by lmsn desc  limit 320,20;但是我在mysql 终端里查询这条花的时间久很短（0.03s），不知道为什么？
类似这样的：select  iid,title,pic_path,price from tb_goods where nick='dmcdf' order by lmsn desc  limit 320,20;一般2s的比较多，我设置的slow query time 是1s
select  SQL_NO_CACHE  iid,title,pic_path,price from tb_goods where nick='dmcdf' order by lmsn desc  limit 320,20;多测试几下，看一下耗时多少。
第一次运行：20 rows in set (2.67 sec)
后面多次执行大概在0.02~0.03 sec
我估计这里第一次去读索引了，所以可能导致速度慢MySQL 5.0.22-log         uptime 23 6:48:47      Mon Dec 21 17:32:18 2009__ Key _________________________________________________________________
Buffer used   453.05M of 512.00M  %Used:  88.49
  Current      73.78M            %Usage:  14.41
Write hit      75.43%
Read hit       98.63%
用mysqlreport 看当前的key_buffer_size 只有73.78MB,太少了，我设置的是512MB
内存有多大？
感觉要调整table_cache和key_buffer_size的大小吧转：(http://www.askwan.com/post/4/)
table_cache指示表高速缓存的大小。当Mysql访问一个表时，如果在Mysql表缓冲区中还有空间，那么这个表就被打开并放入表缓冲区，这样做的好处是可以更快速地访问表中的内容。一般来说，可以通过查看数据库运行峰值时间的状态值Open_tables和Opened_tables，用以判断是否需要增加table_cache的值，即如果open_tables接近table_cache的时候，并且Opened_tables这个值在逐步增加，那就要考虑增加这个值的大小了。在mysql默认安装情况下，table_cache的值在2G内存以下的机器中的值默认时256到512，如果机器有4G内存,则默认这个值是2048，但这决意味着机器内存越大，这个值应该越大，因为table_cache加大后，使得mysql对SQL响应的速度更快了，不可避免的会产生更多的死锁（dead lock），这样反而使得数据库整个一套操作慢了下来，严重影响性能。所以平时维护中还是要根据库的实际情况去作出判断，找到最适合你维护的库的table_cache值，有人说：“性能优化是一门艺术”，这话一点没错。大凡艺术品，大都是经过千锤百炼，精雕细琢而成。
内存2G,server上还有其他东西 apache,tomcat,java ...
table_cache 我没调整现在还是64，不知道这个参数效果怎么样
检查一下你的key_buffer_size，table_open_cache　设置是多少？
key_buffer_size:512MB
table_cache:256