March 2013

[MySQL 5.6] Performance Schema 表类型纵览 (3)

目录: 1.SETUP table配置表2.CURRENT EVENT table2.1.events_waits_current2.2.events_stages_current2.3.events_statements_current3.HISTORY table4.SUMMARY table4.1.Event Wait Summaries4.2.Stage Summaries4.3.Statement Summaries4.4.Object Wait Summaries:4.5.File I/O Summaries4.6.Table I/O and Lock Wait Summaries4.7.Connection Summaries4.8.Socket Summaries5.INSTANCE table6.其他杂项表 前面已经提到过了部分Performance Schema表,这里再总结下,PS库下主要分为几类表 1.SETUP table配置表 文档点击,上一篇博客已经介绍过,这里不再展开描述 2.CURRENT EVENT table 最近的事件表,例如 events_waits_current包含每个线程最近的事件 2.1.events_waits_current 该表列出了当前线程正在等待的事件,主要包括以下几列: THREAD_ID:线程ID EVENT_ID:当前线程的事件ID,和THREAD_ID组成一个Primary Key. END_EVENT_ID:当事件开始时,这一列被设置为NULL。当事件结束时,再更新为当前的事件ID EVENT_NAME:产生该事件的INSTRUMENT名 SOURCE:该事件产生时的源码文件;例如如果在等待一个Mutex,查看对应的源码,就可以知道在那里被阻塞住。 TIMER_START, TIMER_END, TIMER_WAIT:事件开始/结束和等待的时间,单位为皮秒(picoseconds) SPINS:互斥锁或读写锁SPIN的次数 OBJECT_SCHEMA, OBJECT_NAME, OBJECT_TYPE, OBJECT_INSTANCE_BEGIN:这几列的值取决于不同的对象类型      a.对于cond, mutex, rwlock类型,OBJECT_SCHEMA, OBJECT_NAME, 以及 OBJECT_TYPE 的值为NULL.OBJECT_INSTANCE_BEGIN表示该同步对象创建的内存地址      b.对于文件IO对象,OBJECT_SCHEMA为NULL,OBJECT_NAME为文件名,OBJECT_TYPE为FILE,OBJECT_INSTANCE_BEGIN是内存地址。      c.对于SOCKET对象,OBJECT_NAME为该socket的IP:SOCK值,OBJECT_INSTANCE_BEGIN是对象内存地址     […]

[MySQL 5.6 ] Performance Schema学习:命名规范、状态变量及其他(2)

目录: 1.SETUP table配置表2.CURRENT EVENT table2.1.events_waits_current2.2.events_stages_current2.3.events_statements_current3.HISTORY table4.SUMMARY table4.1.Event Wait Summaries4.2.Stage Summaries4.3.Statement Summaries4.4.Object Wait Summaries:4.5.File I/O Summaries4.6.Table I/O and Lock Wait Summaries4.7.Connection Summaries4.8.Socket Summaries5.INSTANCE table6.其他杂项表PS Instrument命名规范PS状态变量STATEMENT_DIGEST PS Instrument命名规范   PS instrument的命名类似于树形结构,最高层次的是instrument的类型,总共四种:idle/wait/stage/statement;再下一层的命名可能是一个子模块名(例如sync,io)等,再往下一层,例如sync,又可以划分成mutex/cond/rwlock,之后也许就是具体的某个同步锁对象,或者下一层的模块。 1.1.idle: idle对象表示socket空闲信息,在setup_instrument表里只包含一列,名字就是idle。它生产的对应事件在socket_instances中. mysql> select count(*) from setup_instruments where name like ‘idle%’; +———-+ | count(*) | +———-+ |        1 | +———-+ 1 row in set (0.00 sec) […]

[MySQL 5.6] Performance Schema 之 PS配置项(1)

尽管Performance Schema(以下简称PS)在5.5中已经出现,但一直没有使用过,并且相比5.6,5.5的PS表要少很多。 以下从一个初学者的角度,阅读PS的官方文档,做一些简单的笔记 官方文档见:http://dev.mysql.com/doc/refman/5.6/en/performance-schema.html 目录: 1.SETUP table配置表2.CURRENT EVENT table2.1.events_waits_current2.2.events_stages_current2.3.events_statements_current3.HISTORY table4.SUMMARY table4.1.Event Wait Summaries4.2.Stage Summaries4.3.Statement Summaries4.4.Object Wait Summaries:4.5.File I/O Summaries4.6.Table I/O and Lock Wait Summaries4.7.Connection Summaries4.8.Socket Summaries5.INSTANCE table6.其他杂项表PS Instrument命名规范PS状态变量STATEMENT_DIGEST1.开启PS2.配置PS2.1 setup_timers表决定了不同的instrument使用的timer类型2.2setup_instruments 2.3 setup_consumers表 列出了事件信息的消费者类型2.4.setup_objects2.5.setup_actors 1.开启PS 首先需要强调一点,开启PS是有性能开销的,在一个性能测试场景上,我对比了阿里内部版本的Percona Server 5.5.18与官方MySQL5.6.10,发现在同等压力下,5.6版本有明显的更高的CPU开销(大约高了10~20%) 确认是否开启: 编译阶段:-DWITH_PERFSCHEMA_STORAGE_ENGINE:BOOL=ON     默认是ON,可以设为OFF来在编译阶段关闭Performance Schema 也可以在启动mysqld时,关闭选项performance_schema 如果你在error log中看到类似错误的PS表结构或者PS表找不到之类的错误,在开启实例后,可以执行一下mysql_upgrade [ERROR] Native table ‘performance_schema’.’events_waits_history’ has the wrong structure [ERROR] Native table ‘performance_schema’.’events_waits_history_long’  has the […]

[MySQL 5.6] MySQL5.6新参数

  以下罗列了我所感兴趣的MySQL5.6新参数,不定期更新本文,完善参数的说明,先大概列一下,做简单说明,以后在一个个补上 ///////////////////////////////////////////////  #mysqld table_open_cache_instances #对table cache进行划分,减少锁竞争 metadata_locks_hash_instances # 对server层的metalock hash进行划分, metadata_locks_cache_size# metadata lock cache的大小,这是总的大小,可以适当调大来提升并发度   #replication #crash safe relay_log_info_repository #是以文件(FILE) 还是表(table)的方式记录relaylog坐标信息 master_info_repository # 是以文件(FILE) 还是表(table)的方式记录binlog信息   #gtid gtid_mode # 设置为ON,表示打开gtid,默认为OFF enforce_gtid_consistency #如果设置该选项,则只允许记录事务安全的语句 gtid_next gtid_owned gtid_purged gtid_executed     #control option   slave_parallel_workers #备库复制worker线程数 slave_checkpoint_group #在并发复制时总共执行这么多次事务后做一次checkpoint,更新show slave status的数据 slave_checkpoint_period #在复制执行这么长时间后做一次checkpoint slave_pending_jobs_size_max #在多线程复制时,在队列中Pending的事件所占用的最大内存,默认为16M,如果内存富余,或者延迟较大时,可以适当调大;注意这个值要比主库的max_allowed_packet大 binlog_checksum   # […]

[MySQL 5.6] 优化函数lock_number_of_rows_locked

lock_number_of_rows_locked是一个统计一个事务中占有多少记录锁的函数,通过执行show engine innodb status触发。 在我们的生产环境中,类似SHOW ENGINE INNODB STATUS这样的语句是被频繁执行的,而从perf top,也观察到过占用超过20%的cpu。 quoted code from lock_number_of_rows_locked: for (lock = UT_LIST_GET_FIRST(trx_lock->trx_locks); lock != NULL; lock = UT_LIST_GET_NEXT(trx_locks, lock)) { if (lock_get_type_low(lock) == LOCK_REC) { ulint n_bit; ulint n_bits = lock_rec_get_n_bits(lock); for (n_bit = 0; n_bit < n_bits; n_bit++) { if (lock_rec_get_nth_bit(lock, n_bit)) { n_records++; } } } }   […]

[MySQL 5.6] Innodb并发控制改进

从5.6开始,如果使用了GCC Build-in的原子操作,在进入Innodb层的线程并发控制走与之前不同的逻辑,5.5也可以调用通过原子操作进行并发控制的逻辑,但需要打开只读选项innodb_thread_concurrency_timer_based来控制. quoted code in 5.6: function srv_conc_enter_innodb: ifdef HAVE_ATOMIC_BUILTINS srv_conc_enter_innodb_with_atomics(trx); #else srv_conc_enter_innodb_without_atomics(trx); #endif /* HAVE_ATOMIC_BUILTINS */ srv_conc_enter_innodb_without_atomics是MySQL5.5的调用逻辑,在5.6中开始默认编译情况下,调用srv_conc_enter_innodb_with_atomics逻辑,其不同之处在于使用GCC Build-in的原子操作,来避免热点锁srv_conc_mutex的频繁加锁/释放。 与5.5不同的是,在5.6中采用了一种称为adaptive sleep的方法,来替代5.5使用的直接sleep固定时间的方式。这样Innodb可以根据系统的负载做一些自适应调整。新增一个参数innodb_adaptive_max_sleep_delay(文档见http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_adaptive_max_sleep_delay ),文档的表述有误,已report bug list(http://bugs.mysql.com/bug.php?id=68594) 原先的做法是在进入Innodb层前,先查看当前在Innodb中活跃的线程数是否超过innodb_thread_concurrency; 如果超过了,则sleep一段时间,再重试,如果还是超过并发限制时,就给其分配一个slot,让其进入信号量等待。 在5.6中,当使用原子操作进行并发控制时,如果innodb_adaptive_max_sleep_delay大于0, 会对另外一个参数innodb_thread_sleep_delay的值做自适应调整.它的逻辑也很简单。 我们这里假定innodb_adaptive_max_sleep_delay的值大于0  当线程能够进入Innodb层时: a.如果当前线程之前sleep过一次,并且当前innodb_thread_sleep_delay>20,将innodb_thread_sleep_delay减1 b.如果当前没有等待的线程,将innodb_thread_sleep_delay除以2 如果线程目前因并发控制无法进入Innodb层: a.如果当前innodb_thread_sleep_delay>innodb_adaptive_max_sleep_delay ,将innodb_thread_sleep_delay的值设置为innodb_adaptive_max_sleep_delay b.sleep  innodb_thread_sleep_delay毫秒 c.如果该线程已经sleep了超过1次,将innodb_thread_sleep_delay++ 可以看到innodb_thread_sleep_delay降低比增加的更快。这样在并发线程数很高时,当限制并发数早就达到,其他线程的每次sleep时间会缓慢拉长。而当Innodb层很空闲时,sleep时间又会快速降到非常低 调整sleep到一个优化值的目的是,过小的sleep值可能会产生太多的线程切换,但过长的sleep时间,在并发比较空闲的时候又会影响性能。新的并发控制策略有利于随着负载的变化而做自适应调整。 另外注意,在使用原子操作进行并发控制后,就再也没有使用信号量让线程进行等待了。 这种自适应调整策略的效率依然有待于评估,也不确定其对某些工作负载是否存在不利影响。合适的参数配置应当由性能测试来给出。 原创文章,转载请注明: 转载自Simple Life 本文链接地址: [MySQL 5.6] Innodb并发控制改进 Post Footer automatically generated by wp-posturl plugin for […]

[MySQL 5.6] innodb_flush_method新值O_DIRECT_NO_FSYNC 及bug#68555

Facebook的Mark大神最近一直在测试5.6的性能,并且发现了不少问题. 看来Facebook是要跳过5.5,直接上5.6了。同为互联网行业,Facebook的许多需求和我们是类似的,online ddl, 热点数据更新问题等。。。 当然,我最关注的还是5.6存在的bug。 related blog: http://mysqlha.blogspot.com/2013/03/mysql-56-cached-update-only-workload.html http://mysqlha.blogspot.com/2013/03/mysql-56-no-odirectnofsync-for-you.html related bug: http://bugs.mysql.com/bug.php?id=45892 提到两个问题,一个是从5.6.7开始innodb_flush_method有一个新值:O_DIRECT_NO_FSYNC。他的含义也很简单。当文件被设置为O_DIRECT时,如果将其设置为O_DIRECT_NO_FSYNC时,就无需在写文件后,再做一次flush(实际上是随后的调用逻辑性能太差了,而不仅仅是fsync很慢的缘故)。 从函数fil_flush可以很清晰的看到,fil_buffering_disabled为true时,很快就释放全局锁fil_system->mutex,返回。根据mark的测试,其性能提升也非常理想: update-only & IO-bound workload updates/second for update 1 row by PK via sysbench     8      16      32      64     128     256   concurrent clients 18234   24359   10379    9795    9843   10283   O_DIRECT 17996   26853   30265   28923   29293   29477   O_DIRECT_NO_FSYNC   可惜的是,这种设置只对部分文件系统是安全的,一些文件系统,例如XFS,即使设置了O_DIRECT,还需要将Metadata信息fsync到磁盘。另外当free list为空时(脏页太快,Page cleaner跟不上),用户线程可能去从LRU获取一个空闲block,这会导致如下backtrace。 os_thread_sleep,fil_flush,fil_flush_file_spaces,buf_flush_sync_datafiles, buf_flush_single_page_from_LRU,buf_LRU_get_free_block, buf_page_init_for_read,buf_read_page_low,.. 这种场景发生在IO-BOUND负载下,即使在扫描lru也没有发现非脏block可以转移到free list后,会去尝试从lru尾部刷一个脏block(buf_flush_single_page_from_LRU),然后将其放到free list上,这其中如果包含了sync操作,显然会大大的影响用户线程的性能。 […]

[MySQL 5.6] 5.6 新参数innodb_lru_scan_depth 浅析

innodb_lru_scan_depth是5.6新增加的参数,根据官方文档描述,它会影响page cleaner线程每次刷脏页的数量,这是一个每1秒 loop一次的线程。 在Innodb内部,这个参数对应变量为srv_LRU_scan_depth grep了一把,有几个地方会涉及到这个参数 1.buf/buf0lru.cc  buf_LRU_free_from_unzip_LRU_list 在扫描bp->unzip_LRU时保证扫描深度不超过srv_LRU_scan_depth,以从其中释放一个压缩块的非压缩页。 在5.5中,则有一个计算公式     distance = 100 + (n_iterations               * UT_LIST_GET_LEN(buf_pool->unzip_LRU)) / 5; n_iterations越大,表示扫描了多次(或者说一次请求空闲块进入这个函数的次数),值不超过5. buf_LRU_free_from_common_LRU_list 与上述情况类似,但扫描的是bp->LRU。 这两个函数主要用于从LRU获取空闲块(例如free list已空),均有一个参数scan_all,当为true时,表示扫描全部LRU链表,这时候srv_LRU_scan_depth就不起作用了。 我们知道获取空闲块的入口函数是buf_LRU_get_free_block,之前也做过5.5关于这个函数的分析(见http://mysqllover.com/?p=387)  在5.6中,如果free list为空,则 >如果有flush在发生,等待完成并重试 >如果buf_pool->try_LRU_scan为true,则扫描srv_LRU_scan_depth深度的LRU,成功则返回空闲快 >如果上一步失败,iteration=1,扫描整个LRU链表 >如果上一步失败,iteration>1,依然扫描整个LRU链表,但sleep 100000us 2.buf/buf0flu.cc: 这里主要是page cleaner线程调用 buf_flush_page_cleaner_thread  //page cleaner线程入口 |—>buf_flush_LRU_tail               |–>扫描LRU,调用srv_LRU_scan_depth/PAGE_CLEANER_LRU_BATCH_CHUNK_SIZE()次buf_flush_LRU函数,每次尝试去处理100个block.划分成chunk的目的是防止用户线程在请求空闲块时等待时间太长       […]

备库由于表无主键导致延迟

由于ROW模式的复制已经广泛使用,但对于没有主键的表而言,如果发生大更新,在备库上会表现出极大的延迟,因为在binlog中产生的大量行记录将无法根据主键快速查找,最差的情况,需要对每条修改的记录进行全表扫描。 5.6已经解决了这个问题,可以只扫描一次表;5.5最新的版本只是在错误日志里输出了一些信息。 Port 5.6的实现不太现实,因为改动太大。因此我做了些小改动,对于无主键表上的DELETE/UPDATE,转换为STATEMENT模式的binlog记录。 以下是一个改动非常简单的patch,基于Percona5.5.18 Index: /PS5518/branches/PS-r3633-nopk-logstmt/sql/sys_vars.cc =================================================================== — /PS5518/branches/PS-r3633-nopk-logstmt/sql/sys_vars.cc (revision 3639) +++ /PS5518/branches/PS-r3633-nopk-logstmt/sql/sys_vars.cc (revision 3641) @@ -396,6 +396,13 @@ CMD_LINE(OPT_ARG), DEFAULT(FALSE), NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(binlog_direct_check)); +static Sys_var_mybool Sys_binlog_use_stmt_for_non_pk( + “binlog_use_stmt_for_non_pk”, + “if a table doesn’t have primary key ,then log the changes (SQLCOM_DELETE” + “and SQLCOM_UPDATE) using STATEMENT.”, + SESSION_VAR(binlog_use_stmt_for_non_pk), + CMD_LINE(OPT_ARG), DEFAULT(FALSE)); + static […]