友善交流技术...

发布新日志

  • mysql查看当前运行状态(转)

    2015-01-21 14:59:35

    mysql查看当前运行状态

    show processlist;只列出前100条,如果想全列出请使用show full processlist;
    mysql> show processlist;命令: show status;

    状态名 作用域 详细解释
    Aborted_clients Global 由于客户端没有正确关闭连接导致客户端终止而中断的连接数
    Aborted_connects Global 试图连接到MySQL服务器而失败的连接数
    Binlog_cache_disk_use Global 使用临时二进制日志缓存但超过binlog_cache_size值并使用临时文件来保存事务中的语句的事务数量
    Binlog_cache_use Global 使用临时二进制日志缓存的事务数量
    Bytes_received Both 从所有客户端接收到的字节数。
    Bytes_sent Both 发送给所有客户端的字节数。
    com*   各种数据库操作的数量
    Compression Session 客户端与服务器之间只否启用压缩协议
    Connections Global 试图连接到(不管是否成功)MySQL服务器的连接数
    Created_tmp_disk_tables Both 服务器执行语句时在硬盘上自动创建的临时表的数量
    Created_tmp_files Global mysqld已经创建的临时文件的数量
    Created_tmp_tables Both 服务器执行语句时自动创建的内存中的临时表的数量。如果Created_tmp_disk_tables较大,你可能要增加tmp_table_size值使临时 表基于内存而不基于硬盘
    Delayed_errors Global 用INSERT DELAYED写的出现错误的行数(可能为duplicate key)。
    Delayed_insert_threads Global 使用的INSERT DELAYED处理器线程数。
    Delayed_writes Global 写入的INSERT DELAYED行数
    Flush_commands Global 执行的FLUSH语句数。
    Handler_commit Both 内部提交语句数
    Handler_delete Both 行从表中删除的次数。
    Handler_discover Both MySQL服务器可以问NDB CLUSTER存储引擎是否知道某一名字的表。这被称作发现。Handler_discover说明通过该方法发现的次数。
    Handler_prepare Both A counter for the prepare phase of two-phase commit operations.
    Handler_read_first Both 索引中第一条被读的次数。如果较高,它建议服务器正执行大量全索引扫描;例如,SELECT col1 FROM foo,假定col1有索引。
    Handler_read_key Both 根据键读一行的请求数。如果较高,说明查询和表的索引正确。
    Handler_read_next Both 按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列,该值增加。
    Handler_read_prev Both 按照键顺序读前一行的请求数。该读方法主要用于优化ORDER BY … DESC。
    Handler_read_rnd Both 根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。
    Handler_read_rnd_next Both 在数据文件中读下一行的请求数。如果你正进行大量的表扫描,该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。
    Handler_rollback Both 内部ROLLBACK语句的数量。
    Handler_savepoint Both 在一个存储引擎放置一个保存点的请求数量。
    Handler_savepoint_rollback Both 在一个存储引擎的要求回滚到一个保存点数目。
    Handler_update Both 在表内更新一行的请求数。
    Handler_write Both 在表内插入一行的请求数。
    Innodb_buffer_pool_pages_data Global 包含数据的页数(脏或干净)。
    Innodb_buffer_pool_pages_dirty Global 当前的脏页数。
    Innodb_buffer_pool_pages_flushed Global 要求清空的缓冲池页数
    Innodb_buffer_pool_pages_free Global 空页数。
    Innodb_buffer_pool_pages_latched Global 在InnoDB缓冲池中锁定的页数。这是当前正读或写或由于其它原因不能清空或删除的页数。
    Innodb_buffer_pool_pages_misc Global 忙的页数,因为它们已经被分配优先用作管理,例如行锁定或适用的哈希索引。该值还可以计算为 Innodb_buffer_pool_pages_total – Innodb_buffer_pool_pages_free – Innodb_buffer_pool_pages_data。
    Innodb_buffer_pool_pages_total Global 缓冲池总大小(页数)。
    Innodb_buffer_pool_read_ahead_rnd Global InnoDB初始化的“随机”read-aheads数。当查询以随机顺序扫描表的一大部分时发生。
    Innodb_buffer_pool_read_ahead_seq Global InnoDB初始化的顺序read-aheads数。当InnoDB执行顺序全表扫描时发生。
    Innodb_buffer_pool_read_requests Global InnoDB已经完成的逻辑读请求数。
    Innodb_buffer_pool_reads Global 不能满足InnoDB必须单页读取的缓冲池中的逻辑读数量。
    Innodb_buffer_pool_wait_free Global 一般情况,通过后台向InnoDB缓冲池写。但是,如果需要读或创建页,并且没有干净的页可用,则它还需要先等待页面清空。该计数器对等待实例进行记数。如果已经适当设置缓冲池大小,该值应小。
    Innodb_buffer_pool_write_requests Global 向InnoDB缓冲池的写数量。
    Innodb_data_fsyncs Global fsync()操作数。
    Innodb_data_pending_fsyncs Global 当前挂起的fsync()操作数。
    Innodb_data_pending_reads Global 当前挂起的读数。
    Innodb_data_pending_writes Global 当前挂起的写数。
    Innodb_data_read Global 至此已经读取的数据数量(字节)。
    Innodb_data_reads Global 数据读总数量。
    Innodb_data_writes Global 数据写总数量。
    Innodb_data_written Global 至此已经写入的数据量(字节)。
    Innodb_dblwr_pages_written Global 已经执行的双写操作数量
    Innodb_dblwr_writes Global 双写操作已经写好的页数
    Innodb_log_waits Global 我们必须等待的时间,因为日志缓冲区太小,我们在继续前必须先等待对它清空
     
    Innodb_log_write_requests Global 日志写请求数。
    Innodb_log_writes Global 向日志文件的物理写数量。
    Innodb_os_log_fsyncs Global 向日志文件完成的fsync()写数量。
    Innodb_os_log_pending_fsyncs Global 挂起的日志文件fsync()操作数量。
    Innodb_os_log_pending_writes Global 挂起的日志文件写操作
    Innodb_os_log_written Global 写入日志文件的字节数。
    Innodb_page_size Global 编译的InnoDB页大小(默认16KB)。许多值用页来记数;页的大小很容易转换为字节。
    Innodb_pages_created Global 创建的页数。
    Innodb_pages_read Global 读取的页数。
    Innodb_pages_written Global 写入的页数。
    Innodb_row_lock_current_waits Global 当前等待的待锁定的行数。
    Innodb_row_lock_time Global 行锁定花费的总时间,单位毫秒。
    Innodb_row_lock_time_avg Global 行锁定的平均时间,单位毫秒。
    Innodb_row_lock_time_max Global 行锁定的最长时间,单位毫秒。
    Innodb_row_lock_waits Global 一行锁定必须等待的时间数。
    Innodb_rows_deleted Global 从InnoDB表删除的行数。
    Innodb_rows_inserted Global 插入到InnoDB表的行数。
    Innodb_rows_read Global 从InnoDB表读取的行数。
    Innodb_rows_updated Global InnoDB表内更新的行数。
    Key_blocks_not_flushed Global 键缓存内已经更改但还没有清空到硬盘上的键的数据块数量。
    Key_blocks_unused Global 键缓存内未使用的块数量。你可以使用该值来确定使用了多少键缓存
    Key_blocks_used Global 键缓存内使用的块数量。该值为高水平线标记,说明已经同时最多使用了多少块。
    Key_read_requests Global 从缓存读键的数据块的请求数。
    Key_reads Global 从硬盘读取键的数据块的次数。如果Key_reads较大,则Key_buffer_size值可能太小。可以用Key_reads/Key_read_requests计算缓存损失率。
    Key_write_requests Global 将键的数据块写入缓存的请求数。
    Key_writes Global 向硬盘写入将键的数据块的物理写操作的次数。
    Last_query_cost Session 用查询优化器计算的最后编译的查询的总成本。用于对比同一查询的不同查询方案的成本。默认值0表示还没有编译查询。 默认值是0。Last_query_cost具有会话范围。
    Max_used_connections Global 服务器启动后已经同时使用的连接的最大数量。
    ndb*   ndb集群相关
    Not_flushed_delayed_rows Global 等待写入INSERT DELAY队列的行数。
    Open_files Global 打开的文件的数目。
    Open_streams Global 打开的流的数量(主要用于记录)。
    Open_table_definitions Global 缓存的.frm文件数量
    Open_tables Both 当前打开的表的数量。
     
    Opened_files Global 文件打开的数量。不包括诸如套接字或管道其他类型的文件。 也不包括存储引擎用来做自己的内部功能的文件。
    Opened_table_definitions Both 已经缓存的.frm文件数量
    Opened_tables Both 已经打开的表的数量。如果Opened_tables较大,table_cache 值可能太小。
    Prepared_stmt_count Global 当前的预处理语句的数量。 (最大数为系统变量: max_prepared_stmt_count)
    Qcache_free_blocks Global 查询缓存内自由内存块的数量。
    Qcache_free_memory Global 用于查询缓存的自由内存的数量。
    Qcache_hits Global 查询缓存被访问的次数。
    Qcache_inserts Global 加入到缓存的查询数量。
    Qcache_lowmem_prunes Global 由于内存较少从缓存删除的查询数量。
    Qcache_not_cached Global 非缓存查询数(不可缓存,或由于query_cache_type设定值未缓存)。
    Qcache_queries_in_cache Global 登记到缓存内的查询的数量。
    Qcache_total_blocks Global 查询缓存内的总块数。
    Queries Both 服务器执行的请求个数,包含存储过程中的请求。
    Questions Both 已经发送给服务器的查询的个数。
    Rpl_status Global 失败安全复制状态(还未使用)。
    Select_full_join Both 没有使用索引的联接的数量。如果该值不为0,你应仔细检查表的索引
    Select_full_range_join Both 在引用的表中使用范围搜索的联接的数量。
    Select_range Both 在第一个表中使用范围的联接的数量。一般情况不是关键问题,即使该值相当大。
    Select_range_check Both 在每一行数据后对键值进行检查的不带键值的联接的数量。如果不为0,你应仔细检查表的索引。
    Select_scan Both 对第一个表进行完全扫描的联接的数量。
    Slave_heartbeat_period Global 复制的心跳间隔
    Slave_open_temp_tables Global 从服务器打开的临时表数量
    Slave_received_heartbeats Global 从服务器心跳数
    Slave_retried_transactions Global 本次启动以来从服务器复制线程重试次数
    Slave_running Global 如果该服务器是连接到主服务器的从服务器,则该值为ON。
    Slow_launch_threads Both 创建时间超过slow_launch_time秒的线程数。
    Slow_queries Both 查询时间超过long_query_time秒的查询的个数。
    Sort_merge_passes Both 排序算法已经执行的合并的数量。如果这个变量值较大,应考虑增加sort_buffer_size系统变量的值。
    Sort_range Both 在范围内执行的排序的数量。
    Sort_rows Both 已经排序的行数。
    Sort_scan Both 通过扫描表完成的排序的数量。
    ssl*   ssl连接相关
    Table_locks_immediate Global 立即获得的表的锁的次数。
    Table_locks_waited Global 不能立即获得的表的锁的次数。如果该值较高,并且有性能问题,你应首先优化查询,然后拆分表或使用复制。
    Threads_cached Global 线程缓存内的线程的数量。
    Threads_connected Global 当前打开的连接的数量。
    Threads_created Global 创建用来处理连接的线程数。如果Threads_created较大,你可能要增加thread_cache_size值。缓存访问率的计算方法Threads_created/Connections。
    Threads_running Global 激活的(非睡眠状态)线程数。
    Uptime Global 服务器已经运行的时间(以秒为单位)。
    Uptime_since_flush_status Global 最近一次使用FLUSH STATUS 的时间(以秒为单位)。



  • Between and 索引使用情况-mysql神奇的现象

    2014-04-09 16:47:55

    Between and 有条件的使用索引,做了如下的测试

    1.表的结构如下

    Create Table


    CREATE TABLE `city` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` char(30) DEFAULT NULL,
      `subcity` int(11) DEFAULT NULL,
      `tdate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
      `cont` varchar(2000) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `name_idx` (`name`)
    ) ENGINE=MyISAM AUTO_INCREMENT=81 DEFAULT CHARSET=utf8

    2. 测试结果如下

    测试场景1

    表数据量是10条:

    SELECT * FROM city WHERE id  BETWEEN 1 AND 5; 5/10使用索引

    SELECT * FROM city WHERE id  BETWEEN 1 AND 6; 不使用索引

    测试场景2

    表数据量是条14

    SELECT * FROM city WHERE id  BETWEEN 1 AND 5 ; 5/14使用索引

    SELECT * FROM city WHERE id  BETWEEN 1 AND 6; 不使用索引  

    测试场景3

    表数据量是条50

    SELECT * FROM city WHERE id  BETWEEN 1 AND 11; 11/50 使用索引

    SELECT * FROM city WHERE id  BETWEEN 1 AND 12;不使用索引

    测试场景4

    表数据量是条1000

    SELECT * FROM city WHERE id  BETWEEN 1 AND 179; 179/1000 使用索引

    SELECT * FROM city WHERE id  BETWEEN 1 AND 180;不使用索引

    测试场景5

    表数据量是条10000

    SELECT * FROM city WHERE id  BETWEEN 1 AND 9000; 使用索引

    SELECT * FROM city WHERE id  BETWEEN 1 AND 10000; 使用索引

    测试场景6

    表数据量是条100000

    SELECT * FROM city WHERE id  BETWEEN 1 AND 100000; 使用索引

    SELECT * FROM city WHERE id  BETWEEN 1 AND 90000; 使用索引


     3.个人的结果分析

      1)如果表数据量小14条时,取5条时就会使用索引,否则就使用全表描述

      2)如果表数据量在1000左右时,取出超过18%时,就不会使用索引

      3)如果表数据量超地10000时,只要使用between and 都使用索引

     以上是基于测试结果分析出来的,不知道正确与否?有时间要看一下源代码才行确定的上面的分析正确性的.贴出来,希望大家拍砖!

  • 浅析innodb_support_xa与innodb_flush_log_at_trx_commit (转载)

    2013-12-11 16:49:36

    浅析innodb_support_xa与innodb_flush_log_at_trx_commit

    分类: mysql 356人阅读 评论(0) 收藏 举报

            很久以前对innodb_support_xa存在一点误解,当初一直认为innodb_support_xa只控制外部xa事务,内部的xa事务是 mysql内部进行控制,无法人为干预(这里说的内部xa事务主要是指binlog与innodb的redo log保持一致性所采用的内部xa事务)。直到前阵子在微博上看到有人讨论mysql数据安全时才仔细去手册上查看了关于 innodb_support_xa的解释,这几天又与同事再次讨论了这个问题,于是想着还是将其记录下来。先看官方手册上对 innodb_support_xa的解释:

    “EnablesInnoDBsupport for two-phase commit in XA transactions, causing an extra disk flush for transaction preparation. This set-ting is the default. The XA mechanism is used internally and is essential for any server that has its binary log turned on and is accepting changes to its data from more than one thread. If you turn it off, transactions can be written to the binary log in a different order from the one in which the live database is committing them. This can produce different data when the binary log is replayed in disaster recovery or on a replication slave. Do not turn it off on a replication master server unless you have an unusual setup where only one thread is able to change data.”

    从官方解释来看,innodb_support_xa的作用是分两类:第一,支持多实例分布式事务(外部xa事务),这个一般在分布式数据库环境中 用得较多。第二,支持内部xa事务,说白了也就是说支持binlog与innodb redo log之间数据一致性。今天的重点是讨论第二类内部xa事务。

            首先我们需要明白为什么需要保持binlog与redo log之间数据一致性,这里分两个方面来解释:

    第一,保证binlog里面存在的事务一定在redo log里面存在,也就是binlog里不会比redo log多事务(可以少,因为redo log里面记录的事务可能有部分没有commit,这些事务最终可能会被rollback)。先来看这样一个场景(后面的场景都是假设binlog开 启):在一个AB复制环境下主库crash,然后进行crash recovery,此时如果binlog里面的的事务信息与redo log里面的信息不一致,那么就会出现主库利用redo log进行恢复后,然后binlog部分的内容复制到从库去,然后出现主从数据不一致状态。所以需要保证binlog与redo log两者事务一致性。

    第二,保证binlog里面事务顺序与redo log事务顺序一致性。这也是很重要的一点,假设两者记录的事务顺序不一致,那么会出现类似于主库事务执行的顺序是ta, tb, tc,td,但是binlog里面记录的是ta,tc, tb, td,binlog复制到从库后导致主从的数据不一致。当然也由于当初蹩脚的设计导致BGC被打破,这里就不详说了。

            为了达到上面说的两点,mysql是怎么来实现的呢?没错,答案是内部xa事务(核心是2pc)。现在mysql内部一个处理流程大概是这样:

    1. prepare ,然后将redo log持久化到磁盘

    2. 如果前面prepare成功,那么再继续将事务日志持久化到binlog

    3. 如果前面成功,那么在redo log里面写上一个commit记录

    那么假如在进行着三步时又任何一步失败,crash recovery是怎么进行的呢? 此时会先从redo log将最近一个检查点开始的事务读出来,然后参考binlog里面的事务进行恢复。如果是在1 crash,那么自然整个事务都回滚;如果是在2 crash,那么也会整个事务回滚;如果是在3 crash(仅仅是commit记录没写成功),那么没有关系因为2中已经记录了此次事务的binlog,所以将这个进行commit。所以总结起来就是 redo log里凡是prepare成功,但commit失败的事务都会先去binlog查找判断其是否存在(通过XID进行判断,是不是经常在binlog里面 看到Xid=xxxx?这就是xa事务id),如果有则将这个事务commit,否则rollback。

            在这三个步骤中因为持久化需求每一步都需要fsync,但是如果真的每一步都需要fsync,那么sync_binlog与 innodb_flush_log_at_trx_commit两个参数的意义又在哪?这里还没理得很清楚,希望自己以后补上来或是谁帮忙解答一下。

            前面已经解释完了通过内部xa事务来保证binlog里记录的事务不会比redo log多(也可以间接的理解为binlog一定只记录提交事务),这么做的原因是为了crash recovery后主从保持一致性。接下来解释目前是怎么来保证binlog与redo log之间顺序一致的。

            为什么要保证binlog里事务与redo log里事务顺序一致性原因前面已经解释过。为了保证这一点带来的问题相信了解过BGC的朋友都知道----臭名昭著的 prepare_commit_mutex,没错就是它导致了正常情况下无法实现BGC,原理是什么?在每次进行xa事务时,在prepare阶段事务先 拿到一个全局的prepare_commit_mutex, 然后执行前面说的持久化(fsync)redo log与binlog,然后等fsync完了之后再释放prepare_commit_mutex,这样相当于串行化的效果虽然保证了binlog与 redo log之间顺序一致性,但是却导致每个事务都需要一个fsync操作,而大家都知道在一次持久化的过程中代价最大的操作就是fsync了,而想 write()这些不落地的操作代价相对来说就很小。所以BGC得核心在于很多事务需要的fsync合并成一个fsync去做。

             说了这么多就只为了解释innodb_support_xa=1的价值在哪,但是刚才也说了由于xa事务中需要多次fsync,所以开启后会对性能有一 定影响。从percona博客上看到06年他们测试时开启后tps下降一半,但是我实际用mysql-5.5.12+sysbench-0.5+10块 SAS(raid 10)测试结果性能下面没那么明显。在oltp模式下tps几乎没差别,不过它默认读写比例是4:1,后来换成纯update测试,开始xa事务性能下降 也仅仅是5%左右,没有传说中那么大的差别。所以我怀疑可能的原因有两个:第一,现在的mysql性能相对于06有了较大提升;第二,我测试的机器较好 (10块SAS盘做raid10),这样即使开启了xa事务,需要较多的fsync,但是由于存储方面能抗住,所以没有体现出太大的劣势。

            接下来顺便谈一下innodb_flush_log_at_trx_commit意义以及合理设置。 innodb_flush_log_at_trx_commit有0、1、2三个值分别代表不同的使redo log落地策略。0表示每秒进行一次flush,但是每次事务commit不进行任何操作(每秒调用fsync使数据落地到磁盘,不过这里需要注意如果底 层存储有cache,比如raid cache,那么这时也不会真正落地,但是由于一般raid卡都带有备用电源,所以一般都认为此时数据是安全的)。1代表每次事务提交都会进行 flush,这是最安全的模式。2表示每秒flush,每次事务提交时不flush,而是调用write将redo log buffer里面的redo log刷到os page cache。

            那现在来比较三种策略的优劣势:1由于每次事务commit都会是redo log落地所以是最安全的,但是由于fsync的次数增多导致性能下降比较厉害。0表示每秒flush,每次事务提交不进行任何操作,所以mysql crash或者os crash时会丢失一秒的事务。2相对于0来说了多了每次事务commit时会有一次write操作,此时数据虽然没有落地到磁盘但是只要没有 os crash,即使mysql crash,那么事务是不会丢失的。2相对于0来说会稍微安全一点点。

            所以关于这两个参数,我的建议是主库开始innodb_support_xa=1,从库不开(因为从库一般不会记binlog),数据一致性还是很重要 的。而对于innodb_flush_log_at_trx_commit,除非是对数据很重要,不能丢事务,否则我建议设置成2。我看到有些公司设置成 0。其实我个人认为都设置成0了就没有多少理由不设置成2,因为2带来的性能损耗是每个事务一个write操作,write操作的开销相对于fsync还 是小很多的,但是这点开销换来了即使mysql挂掉事务依然不会丢的好处。

  • mysqltuner 及mysqlreport 性能测试的监控及分析工具

    2013-11-15 16:41:49

    mysqltuner and mysqlreport 工具使用

    1.2     mysqltuner.pl

      本工具建议定期运行,发现目前MYSQL数据库存在的问题及修改相关的参数.

    1.2.1  安装

    直接下载

    [root@even ~]# wget  http://mysqltuner.pl/mysqltuner.pl

    [root@even ~]# chmod +x mysqltuner.pl

    [root@even ~]# cp mysqltuner.pl /home/mysql/

    1.2.2  生成的报告如下

    [root@xx xxx]# ./mysqltuner.pl

     

     >>  MySQLTuner 1.0.0 - Major Hayden <major@mhtx.net>

     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/

     >>  Run with '--help' for additional options and output filtering

    [!!] Successfully authenticated with no password - SECURITY RISK!

     

    -------- General Statistics --------------------------------------------------

    [--] Skipped version check for MySQLTuner script

    [OK] Currently running supported MySQL version 5.1.57-community-log

    [OK] Operating on 64-bit architecture

     

    -------- Storage Engine Statistics -------------------------------------------

    [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster

     

     

    [--] Data in MyISAM tables: 18G (Tables: 3395)

    [--] Data in MRG_MYISAM tables: 218M (Tables: 11)

    [--] Data in InnoDB tables: 3G (Tables: 449)

    [--] Data in MEMORY tables: 95M (Tables: 15)

    [!!] Total fragmented tables: 188

     

    -------- Performance Metrics -------------------------------------------------

    [--] Up for: 8d 0h 22m 0s (7B q [10K qps], 3M conn, TX: 6131B, RX: 2280B)

    [--] Reads / Writes: 62% / 38%

    [--] Total buffers: 1.5G global + 96.2M per thread (1000 max threads)

    [!!] Maximum possible memory usage: 95.5G (1226% of installed RAM)

    [OK] Slow queries: 0% (8K/7B)

    [!!] Highest connection usage: 100%  (1001/1000)

    [OK] Key buffer size / total MyISAM indexes: 512.0M/6.5G

    [OK] Key buffer hit rate: 100.0% (6B cached / 301K reads)

    [OK] Query cache efficiency: 99.2% (7B cached / 7B selects)

    [!!] Query cache prunes per day: 6074

    [OK] Sorts requiring temporary tables: 0% (2 temp sorts / 3M sorts)

    [!!] Joins performed without indexes: 2029

    [!!] Temporary tables created on disk: 33% (2M on disk / 7M total)

    [OK] Thread cache hit rate: 99% (1K created / 3M connections)

    [!!] Table cache hit rate: 0% (2K open / 222K opened)

    [OK] Open file limit used: 35% (2K/8K)

    [OK] Table locks acquired immediately: 97% (226M immediate / 230M locks)

    [!!] InnoDB data size / buffer pool: 3.9G/256.0M

     

    -------- Recommendations -----------------------------------------------------

    General recommendations:

        Run OPTIMIZE TABLE to defragment tables for better performance

        Reduce your overall MySQL memory footprint for system stability

        Reduce or eliminate persistent connections to reduce connection usage

        Increasing the query_cache size over 128M may reduce performance

        Adjust your join queries to always utilize indexes

        Temporary table size is already large - reduce result set size

        Reduce your SELECT DISTINCT queries without LIMIT clauses

        Increase table_cache gradually to avoid file descriptor limits

    Variables to adjust:

      *** MySQL's maximum memory usage is dangerously high ***

      *** Add RAM before increasing MySQL buffer variables ***

        max_connections (> 1000)

        wait_timeout (< 3600)

        interactive_timeout (< 3600)

        query_cache_size (> 512M) [see warning above]

        join_buffer_size (> 32.0M, or always use indexes with joins)

        table_cache (> 2000)

    innodb_buffer_pool_size (>= 3G)

     

    分析本报告:

    (1)   链接数要求1000,每一个链接需要的内存是95M左右,需要总的内存是96g,目前本机的内存只有8G Highest connection usage: 100%  (1001/1000)

    建议链接数大于1000,最大值是1001

    (2)   Key buffer size / total MyISAM indexes: 512.0M/6.5G  建议调整成6.5G左右

    (3)   Temporary tables created on disk: 33% (2M on disk / 7M total)  说明了需要调整tmp_table_size大小的

    (4)   系统整体建议的参数为:

        max_connections (> 1000)

        wait_timeout (< 3600)

        interactive_timeout (< 3600)

        query_cache_size (> 512M) [see warning above]

        join_buffer_size (> 32.0M, or always use indexes with joins)

        table_cache (> 2000)

    innodb_buffer_pool_size (>= 3G

    1.1     Mysqlreport

    1.1.1  安装

    下载地址

    http://hackmysql.com/mysqlreport

    Chmod 777 mysqlreport

    ./mysqlreport

     

    1.1.2  使用

    生成的报告如下

    [root@xxx xxx]# ./mysqlreport --flush-status

    Use of uninitialized value in multiplication (*) at ./mysqlreport line 829.

    Use of uninitialized value in formline at ./mysqlreport line 1227.

    MySQL 5.1.57-community-  uptime 8 0:40:50       Wed Nov 13 10:08:06 2013

     

    __ Key _________________________________________________________________

    Buffer used    27.52M of 512.00M  %Used:   5.37

      Current      97.83M            %Usage:  19.11

    Write hit      99.55%

    Read hit      100.00%

     

    __ Questions ___________________________________________________________

    Total           7.13G   10.3k/s

      QC Hits       7.03G   10.1k/s  %Total:  98.58

      Com_          6.82G    9.8k/s           95.69

      -Unknown      6.81G    9.8k/s           95.52

      DMS          85.78M   123.7/s            1.20

      COM_QUIT      3.38M     4.9/s            0.05

    Slow 1 s        8.44k     0.0/s            0.00  %DMS:   0.01  Log:  ON

    DMS            85.78M   123.7/s            1.20

      SELECT       53.86M    77.6/s            0.76         62.79

      DELETE       19.56M    28.2/s            0.27         22.81

      INSERT        9.83M    14.2/s            0.14         11.46

      UPDATE        1.64M     2.4/s            0.02          1.91

      REPLACE     883.42k     1.3/s            0.01          1.03

    Com_            6.82G    9.8k/s           95.69

      admin_comma   6.81G    9.8k/s           95.52

      set_option    5.99M     8.6/s            0.08

      change_db     3.19M     4.6/s            0.04

     

    __ SELECT and Sort _____________________________________________________

    Scan            3.93M     5.7/s %SELECT:   7.31

    Range           8.95M    12.9/s           16.61

    Full join       2.03k     0.0/s            0.00

    Range check         0       0/s            0.00

    Full rng join  48.99k     0.1/s            0.09

    Sort scan       2.17M     3.1/s

    Sort range    947.47k     1.4/s

    Sort mrg pass       2     0.0/s

     

    __ Query Cache _________________________________________________________

    Memory usage  215.91M of 512.00M  %Used:  42.17

    Block Fragmnt  15.00%

    Hits            7.03G   10.1k/s

    Inserts         5.77M     8.3/s

    Insrt:Prune  118.57:1     8.3/s

    Hit:Insert  1217.91:1

     

    __ Table Locks _________________________________________________________

    Waited          4.68M     6.7/s  %Total:   2.02

    Immediate     226.55M   326.6/s

     

    __ Tables ______________________________________________________________

    Open             2000 of 2000    %Cache: 100.00

    Opened        241.76k     0.3/s

     

    __ Connections _________________________________________________________

    Max used         1001 of 1000      %Max: 100.10

    Total           3.38M     4.9/s

     

    __ Created Temp ________________________________________________________

    Disk table      2.45M     3.5/s

    Table           4.85M     7.0/s    Size: 256.0M

    File            2.06k     0.0/s

     

    __ Threads _____________________________________________________________

    Running             2 of  781

    Cached             21 of  500      %Hit:  99.96

    Created         1.28k     0.0/s

    Slow                0       0/s

     

    __ Aborted _____________________________________________________________

    Clients         8.91k     0.0/s

    Connects        2.39k     0.0/s

     

    __ Bytes _______________________________________________________________

    Sent            6.14T    8.9M/s

    Received        2.29T    3.3M/s

     

    __ InnoDB Buffer Pool __________________________________________________

    Usage         256.00M of 256.00M  %Used: 100.00

    Read hit      100.00%

    Pages

      Free              0            %Total:   0.00

      Data         16.13k                     98.45 %Drty:   0.00

      Misc            254                      1.55

      Latched                                  0.00

    Reads           5.26G    7.6k/s

      From file    60.56k     0.1/s            0.00

      Ahead Rnd      1001     0.0/s

      Ahead Sql      1242     0.0/s

    Writes         10.89M    15.7/s

    Flushes       163.88k     0.2/s

    Wait Free           0       0/s

     

    __ InnoDB Lock _________________________________________________________

    Waits             158     0.0/s

    Current             0

    Time acquiring

      Total        401132 ms

      Average        2538 ms

      Max           21434 ms

     

    __ InnoDB Data, Pages, Rows ____________________________________________

    Data

      Reads        71.09k     0.1/s

      Writes        1.37M     2.0/s

      fsync         1.30M     1.9/s

      Pending

        Reads           0

        Writes          0

        fsync           0

     

    Pages

      Created       8.49k     0.0/s

      Read        169.24k     0.2/s

      Written     163.88k     0.2/s

     

    Rows

      Deleted     237.02k     0.3/s

      Inserted    255.62k     0.4/s

      Read          1.77G    2.5k/s

      Updated     180.99k     0.3/s

  • innodb_flush_log_at_trx_commit对mysql性能影响

    2012-09-10 16:38:30

    对两种不同引擎的性能表现情况.
    _________________________________
    innodb_flush_log_at_trx_commit=1 
    root@migo-V200:~# mysqlslap --concurrency=50,100 --iterations=1 --number-int-cols=4 --number-char-cols=35 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=myisam,innodb --number-of-queries=100 --debug-info -uroot -ptester;

    Benchmark
    Running for engine myisam
    Average number of seconds to run all queries: 0.038 seconds
    Minimum number of seconds to run all queries: 0.038 seconds
    Maximum number of seconds to run all queries: 0.038 seconds
    Number of clients running queries: 50
    Average number of queries per client: 2

    Benchmark
    Running for engine myisam
    Average number of seconds to run all queries: 0.045 seconds
    Minimum number of seconds to run all queries: 0.045 seconds
    Maximum number of seconds to run all queries: 0.045 seconds
    Number of clients running queries: 100
    Average number of queries per client: 1

    Benchmark
    Running for engine innodb
    Average number of seconds to run all queries: 0.759 seconds
    Minimum number of seconds to run all queries: 0.759 seconds
    Maximum number of seconds to run all queries: 0.759 seconds
    Number of clients running queries: 50
    Average number of queries per client: 2

    Benchmark
    Running for engine innodb
    Average number of seconds to run all queries: 1.102 seconds
    Minimum number of seconds to run all queries: 1.102 seconds
    Maximum number of seconds to run all queries: 1.102 seconds
    Number of clients running queries: 100
    Average number of queries per client: 1


    User time 0.03, System time 0.07
    Maximum resident set size 3204, Integral resident set size 0
    Non-physical pagefaults 2541, Physical pagefaults 0, Swaps 0
    Blocks in 0 out 0, Messages in 0 out 0, Signals 0
    Voluntary context switches 3113, Involuntary context switches 106
    _________________________________
    innodb_flush_log_at_trx_commit=2
    root@migo-V200:~# mysqlslap --concurrency=50,100 --iterations=1 --number-int-cols=4 --number-char-cols=35 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=myisam,innodb --number-of-queries=100 --debug-info -uroot -ptester;

    Benchmark
    Running for engine myisam
    Average number of seconds to run all queries: 0.040 seconds
    Minimum number of seconds to run all queries: 0.040 seconds
    Maximum number of seconds to run all queries: 0.040 seconds
    Number of clients running queries: 50
    Average number of queries per client: 2

    Benchmark
    Running for engine myisam
    Average number of seconds to run all queries: 0.068 seconds
    Minimum number of seconds to run all queries: 0.068 seconds
    Maximum number of seconds to run all queries: 0.068 seconds
    Number of clients running queries: 100
    Average number of queries per client: 1

    Benchmark
    Running for engine innodb
    Average number of seconds to run all queries: 0.040 seconds
    Minimum number of seconds to run all queries: 0.040 seconds
    Maximum number of seconds to run all queries: 0.040 seconds
    Number of clients running queries: 50
    Average number of queries per client: 2

    Benchmark
    Running for engine innodb
    Average number of seconds to run all queries: 0.052 seconds
    Minimum number of seconds to run all queries: 0.052 seconds
    Maximum number of seconds to run all queries: 0.052 seconds
    Number of clients running queries: 100
    Average number of queries per client: 1


    User time 0.04, System time 0.06
    Maximum resident set size 3016, Integral resident set size 0
    Non-physical pagefaults 2514, Physical pagefaults 0, Swaps 0
    Blocks in 0 out 0, Messages in 0 out 0, Signals 0
    Voluntary context switches 3123, Involuntary context switches 104
    _________________________________
    innodb_flush_log_at_trx_commit=0
    root@migo-V200:~# mysqlslap  --concurrency=50,100 --iterations=1 --number-int-cols=4 --number-char-cols=35 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=myisam,innodb --number-of-queries=100 --debug-info -uroot -ptester;

    Benchmark
    Running for engine myisam
    Average number of seconds to run all queries: 0.045 seconds
    Minimum number of seconds to run all queries: 0.045 seconds
    Maximum number of seconds to run all queries: 0.045 seconds
    Number of clients running queries: 50
    Average number of queries per client: 2

    Benchmark
    Running for engine myisam
    Average number of seconds to run all queries: 0.046 seconds
    Minimum number of seconds to run all queries: 0.046 seconds
    Maximum number of seconds to run all queries: 0.046 seconds
    Number of clients running queries: 100
    Average number of queries per client: 1

    Benchmark
    Running for engine innodb
    Average number of seconds to run all queries: 0.038 seconds
    Minimum number of seconds to run all queries: 0.038 seconds
    Maximum number of seconds to run all queries: 0.038 seconds
    Number of clients running queries: 50
    Average number of queries per client: 2

    Benchmark
    Running for engine innodb
    Average number of seconds to run all queries: 0.056 seconds
    Minimum number of seconds to run all queries: 0.056 seconds
    Maximum number of seconds to run all queries: 0.056 seconds
    Number of clients running queries: 100
    Average number of queries per client: 1


    User time 0.04, System time 0.06
    Maximum resident set size 3024, Integral resident set size 0
    Non-physical pagefaults 2520, Physical pagefaults 0, Swaps 0
    Blocks in 0 out 0, Messages in 0 out 0, Signals 0
    Voluntary context switches 3143, Involuntary context switches 63

     如此一个参数 影响这么大,真是没有想到.但是

    默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入(flush)硬盘,这是很费时的。特别是使用电 池供电缓存(Battery backed up cache)时。设成2对于很多运用,特别是从MyISAM表转过来的是可以的,它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬 盘,所以你一般不会丢失超过1-2秒的更新。设成0会更快一点,但安全方面比较差,即使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统 挂了时才可能丢数据。 


  • mysqldumpslow 使用说明

    2012-08-31 16:09:03

    MySQL 5.1 Reference Manual :: 4 MySQL Programs :: 4.6 MySQL Administrative and Utility Programs :: 4.6.8 mysqldumpslow — Summarize Slow Query Log Files
    shell> mysqldumpslow [options] [log_file ...]
    mysqldumpslow supports the following options.

    Table 4.12. mysqldumpslow Options

    Format Option File Description
    -a Do not abstract all numbers to N and strings to S
    -n num Abstract numbers with at least the specified digits
    --debug debug Write debugging information
    -g pattern Only consider statements that match the pattern
    --help Display help message and exit
    -h name Host name of the server in the log file name
    -i name Name of the server instance
    -l Do not subtract lock time from total time
    -r Reverse the sort order
    -s value How to sort output
    -t num Display only first num queries
    --verbose verbose Verbose mode
     --help

    Display a help message and exit.

     -a

    Do not abstract all numbers to N and strings to 'S'.

     --debug, -d

    Run in debug mode.

     -g pattern

    Consider only queries that match the (grep-style) pattern.

     -h host_name

    Host name of MySQL server for *-slow.log file name. The value can contain a wildcard. The default is * (match all).

     -i name

    Name of server instance (if using mysql.server startup script).

     -l

    Do not subtract lock time from total time.

     -n N

    Abstract numbers with at least N digits within names.

     -r

    Reverse the sort order.

     -s sort_type

    How to sort the output. The value of sort_type should be chosen from the following list:

    t, at: Sort by query time or average query time

    l, al: Sort by lock time or average lock time

    r, ar: Sort by rows sent or average rows sent

    c: Sort by count

    By default, mysqldumpslow sorts by average query time (equivalent to -s at).

     -t N

    Display only the first N queries in the output.

     --verbose, -v

    Verbose mode. Print more information about what the program does.

    Example of usage:

    #按执行次数进行排序
    shell> mysqldumpslow -t 5 -s c mysqld51-apple-slow.log

    Reading mysql slow query log from /usr/local/mysql/data/mysqld51-apple-slow.log

    Count: 3  Time=2.53s (7s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
     insert into t2 select * from t1 limit N

    Count: 3  Time=2.13s (6s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
     insert into t1 select * from t1


    Count: 1  Time=4.32s (4s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
     insert into t2 select * from t1


  • mysql执行profile分析

    2012-08-22 16:57:35

     目标:测试两次运行相同的SQL语句,了解mysql处理过程及处理时间分析
     表结构:
      
     第一次执行语句
       select * from fang where name ='wang';
       show profile for query 5;
        
       
      第二次执行语句 
       select * from fang where name ='wang';
       show profile for query 6;
       
     结果分析
       一个SQL语句首次执行要17个过程大概,而SQL第二或是多次执行时,过程大概也就只有5步,就可以完成,同时时间也在100倍左右的差别。
      第二次执行时间减少在:Opening tables,lock,init,执行计划,数据缓存等方面。但是为什么init会这么的耗费时间呢?
      
Open Toolbar