mysqlreport分析

zuoxingyu 2011-04-19 02:45:35 


MySQL 5.0.77-log         uptime 3 17:23:3       Mon Apr 18 22:57:51 2011

 

__ Key _________________________________________________________________

Buffer used    52.33M of  64.00M  %Used:  81.76

  Current      11.94M            %Usage:  18.65

Write hit      98.77%

Read hit       99.21%

 

__ Questions ___________________________________________________________

Total          43.02M   133.7/s

  QC Hits      18.91M    58.8/s  %Total:  43.97

  DMS          17.89M    55.6/s           41.60

  Com_          4.93M    15.3/s           11.46

  COM_QUIT      1.28M     4.0/s            2.97

  +Unknown        347     0.0/s            0.00

Slow 2 s      661.26k     2.1/s            1.54  %DMS:   3.70  Log:  ON

DMS            17.89M    55.6/s           41.60

  SELECT       16.40M    51.0/s           38.12         91.64

  INSERT      992.85k     3.1/s            2.31          5.55

  UPDATE      267.91k     0.8/s            0.62          1.50

  DELETE      235.19k     0.7/s            0.55          1.31

  REPLACE           0       0/s            0.00          0.00

Com_            4.93M    15.3/s           11.46

  set_option    2.56M     7.9/s            5.94

  change_db     1.28M     4.0/s            2.97

  begin       894.75k     2.8/s            2.08

 

__ SELECT and Sort _____________________________________________________

Scan          571.49k     1.8/s %SELECT:   3.49

Range           2.26M     7.0/s           13.75

Full join          13     0.0/s            0.00

Range check         0       0/s            0.00

Full rng join      44     0.0/s            0.00

Sort scan      27.70k     0.1/s

Sort range    240.09k     0.7/s

Sort mrg pass     147     0.0/s

 

__ Query Cache _________________________________________________________

Memory usage  240.04M of 512.00M  %Used:  46.88

Block Fragmnt  16.18%

Hits           18.91M    58.8/s

Inserts        13.04M    40.5/s

Insrt:Prune    6.28:1    34.1/s

Hit:Insert     1.45:1

 

__ Table Locks _________________________________________________________

Waited             36     0.0/s  %Total:   0.00

Immediate      22.44M    69.7/s

 

__ Tables ______________________________________________________________

Open              681 of 4096    %Cache:  16.63

Opened          3.77k     0.0/s

 

__ Connections _________________________________________________________

Max used           52 of 1000      %Max:   5.20

Total           1.28M     4.0/s

 

__ Created Temp ________________________________________________________

Disk table      1.21k     0.0/s

Table         128.73k     0.4/s    Size:   1.0G

File              271     0.0/s

 

__ Threads _____________________________________________________________

Running             1 of    6

Cached             11 of   16      %Hit:  99.97

Created           406     0.0/s

Slow                0       0/s

 

__ Aborted _____________________________________________________________

Clients            51     0.0/s

Connects            4     0.0/s

 

__ Bytes _______________________________________________________________

Sent           41.02G  127.5k/s

Received        7.58G   23.6k/s

 

__ InnoDB Buffer Pool __________________________________________________

Usage           1.00G of   1.00G  %Used: 100.00

Read hit       99.99%

Pages

  Free              1            %Total:   0.00

  Data         55.35k                     84.46 %Drty:   0.16

  Misc          10184                     15.54

  Latched                                  0.00

Reads          91.53G  284.5k/s

  From file     8.25M    25.6/s            0.01

  Ahead Rnd    301736     0.9/s

  Ahead Sql    388983     1.2/s

Writes         11.05M    34.3/s

Flushes       733.22k     2.3/s

Wait Free           0       0/s

 

__ InnoDB Lock _________________________________________________________

Waits             111     0.0/s

Current             0

Time acquiring

  Total         29594 ms

  Average         266 ms

  Max            6305 ms

 

__ InnoDB Data, Pages, Rows ____________________________________________

Data

  Reads        11.33M    35.2/s

  Writes        1.30M     4.0/s

  fsync       659.22k     2.0/s

  Pending

    Reads           0

    Writes          0

    fsync           0

 

Pages

  Created      14.53k     0.0/s

  Read         31.46M    97.8/s

  Written     733.22k     2.3/s

 

Rows

  Deleted     151.24k     0.5/s

  Inserted      1.39M     4.3/s

  Read        376.04G    1.2M/s

  Updated     263.93k     0.8/s


上面是MYSQLREPORT的输出信息，里面标注红色的是有疑问的地方。
1：服务器的TEMP_TABLE_SIZE=1000了，怎么还有Disk table 1.21k 呢？

2：INNODB_BUFFOR_SIZE=1000M。USAGE全用了，是不是要调大这个值？

3：现在系统里是INNODB和MYISAM混用的，此系统是读多写少的，怎么样配置参数比较合适？
...全文
79 7 打赏 收藏 转发到动态 举报
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
zuoxingyu 2011-04-19
  • 打赏
  • 举报
 回复
既然我疑问的这些没有问题，那么其他参数呢？？帮忙看看啊。
zuoxingyu 2011-04-19
  • 打赏
  • 举报
 回复
内存

InnoDB既缓存数据，又缓存索引，并将其缓存于一个很大的缓冲池中。可以通过STATUS比较物理磁盘的读取和内存读取的比例来判断缓冲池的命中率，InnoDB缓冲池命中率应不小于99%。

命中率=innodb_buffer_pool_read_requests / (innodb_buffer_pool_read_requests + innodb_buffer_pool_read_ahead + innodb_buffer_pool_reads)

平均每次读取的字节数= innodb_data_read / innodb_data_reads

注：innodb_buffer_pool_read_requests：从缓冲池中读取页的次数

Innodb_buffer_pool_read_ahead：预读的次数

Innodb_buffer_pool_reads：从磁盘读取页的次数

Innodb_data_read：读入的字节数

Innodb_data_reads：发起读取请求的次数，每次读取可能需要读取多个页

rucypli 2011-04-19
  • 打赏
  • 举报
 回复 
1：服务器的TEMP_TABLE_SIZE=1000了，怎么还有Disk table 1.21k 呢？


http://wenku.baidu.com/view/f4950318964bcf84b9d57bdc.html
小小小小周 2011-04-19
  • 打赏
  • 举报
 回复
楼上说的对
rucypli 2011-04-19
  • 打赏
  • 举报
 回复 
2：INNODB_BUFFOR_SIZE=1000M。USAGE全用了，是不是要调大这个值？



 

Innodb_buffer_pool_read_requests | 98792359561 | 

 Innodb_buffer_pool_reads | 9186489 |


mysql> select (98792359561-9186489)*100/98792359561;
+---------------------------------------+
| (98792359561-9186489)*100/98792359561 |
+---------------------------------------+
| 99.9907 |
+---------------------------------------+
1 row in set (0.00 sec)
缓存命中率已经非常高了 不需要加大Innodb_buffer_pool
zuoxingyu 2011-04-19
  • 打赏
  • 举报
 回复
show global status like '%Innodb_buffer_pool%';如下


| Innodb_buffer_pool_pages_data | 50752 | 

| Innodb_buffer_pool_pages_dirty | 437 | 

| Innodb_buffer_pool_pages_flushed | 768803 | 

| Innodb_buffer_pool_pages_free | 0 | 

| Innodb_buffer_pool_pages_misc | 14784 | 

| Innodb_buffer_pool_pages_total | 65536 | 

| Innodb_buffer_pool_read_ahead_rnd | 334932 | 

| Innodb_buffer_pool_read_ahead_seq | 430981 | 

| Innodb_buffer_pool_read_requests | 98792359561 | 

| Innodb_buffer_pool_reads | 9186489 | 

| Innodb_buffer_pool_wait_free | 0 | 

| Innodb_buffer_pool_write_requests | 11662806 |
zuoxingyu 2011-04-19
  • 打赏
  • 举报
 回复
耽误版主几分钟时间看看。
自己坐个沙发先。
mysqlreport报告分析记录
mysqlreport报告分析记录
mysql性能分析工具mysqlreport)
mysqlreport是由hackmysql.com 的站长所撰写的 Perl Scritps，旨在协助 MySQL DBA 搜集与分析 MySQL 的运作状况。用着感觉不错。 官方网站： http://hackmysql.com/ 软件下载： http://hackmysql.com/mysqlreport
mysqlreport-3.5.tar.gz
此工具可以打印出mysql的所有工作状态，并生成报表。以供截取，分析
MySQLReport分析报告三
mysqlreport官方手册：http://hackmysql.com/mysqlreportguide __ InnoDB Buffer Pool __________________________________________________ Usage 1.00G of 1.00G %Used: 100.00 Read hit 99.97% Pa
mysqlreport分析工具详解
MySQL也有一个小工具mysqlreport，来统计一些信息指导调参数，最好是写一个任务，一个小时执行一次，这样可以看到高峰、低峰时期的统计信息，更有利于参数调优。进行MySQL的配置优化，首先必须找出MySQL的性能瓶颈...
MySQL

56,687

社区成员

56,710

社区内容

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章