高性能Mysql读后感(三)

其他一些关于索引的话

建立索引

* 所建立的索引最好支持多种过滤条件 *

* 在所建立的索引列上避免多个范围条件 *

* 过滤数据的同时最好能照顾到排序 *

维护索引和表

找到并修复损坏的表 ( corruption )

分为 * 索引损坏 ** 数据损坏 *

check table 来检查是否发生了表损坏 (MyISAM友好)
repair table 修复损坏的表 (MyISAM友好)
InnoDB 表 alter table t1 ENGINE=INNODB; 来重建表.
如果数据损坏了, 恢复备份吧.

维护索引统计信息

mysql 的查询优化器会通过两个API接口 records_in_range() 和 info() 提供的索引值分布信息, 来决定
如何使用索引.

records_in_range(): 通过向存储引擎传入两个边界值 来获取 这个范围大概有多少条记录.
MyISAM 返回精确值: InnoDB 返回估算值.
info(): 返回各种类型的数据, 索引的基数(每个索引有多少条记录)等等.

* mysql 优化器使用基于成本的模型, 衡量成本的主要指标就是一个查询需要扫描多少行. 所以, 如果 mysql 优化器 records_in_range() 和 info() 提供的信息不准确. 那么优化器可能做出错误的决定.*

ANALYZE TABLE —> 重新生成统计信息

  • Memory 引擎不存储统计信息
  • MyISAM 将统计信息 存储在 磁盘中, ANALYZE TABLE 需要进行一次全索引扫描来计算索引基数. (整个过程会锁表)
  • mysql5.5 及其以前的版本, InnoDB 不保存索引统计信息在磁盘, 而是通过随机的索引访问进行评估并保存在内存中.
    ps: InnoDB 在首次打开表, 或是 执行 ANALYZE TABLE , 或是 表的大小发生了非常大的变化(大小变化超过了1/16或插入了20亿行数据) 的时候计算索引的统计信息. * 在表的数据量很大时, 这可能会产生严重的性能问题, I/O 和 锁 *

减少碎片

* 索引的碎片 ** 数据的碎片 *

* 索引的碎片化 * 会降低查询效率, 尤其是 范围查询, 索引覆盖扫描等.

* 数据的碎片 *
* 行碎片: 单条数据行 被存储在多个地方的多个片段中. (根据 id 去访问一条数据行, 也会产生影响)
* 行间碎片: 多个数据行之前 不是被顺序的存储在磁盘中. (对全表扫描和聚簇索引扫描有很大影响影响)
* 剩余空间碎片: 硬盘上的 ‘数据页’ 中有大量的空余空间. (服务器会读取大量不需要的数据, 浪费)

MyISAM 三种情况都会发生, InnoDB 不会出现短小的行碎片, 会将其合并到一个片段中.
* OPTIMIZE TABLE * 来优化表 , 不支持此命令的 存储引擎可以 alter table t1 ENGINE=< engine >来操作.

展开阅读全文

高性能MySQL(三)复制

11-05

复制是如何工作的rn第一步是在主库上记录二进制日志,在记录二进制日志后,主库会告诉存储引擎可以提交事务了。rnrn下一步,备库将主库的二进制日志复制到其本地的中继日志中。首先,备库会启动一个工作线程,称为I/O线程,I/O线程跟主库建立一个普通的客户端连接,然后在主库上启动一个特殊的二进制转存(binlog dump)线程,这个转存线程会读取主库上的二进制日志中事件。它不会对事件进行轮询,如果该线程追赶上了主库,它将进入睡眠状态,直到主库发送信号量通知其有新的事件产生时才会被唤醒。备库I/O线程会将接收到的事件记录到中继日志中。rnrn备库的SQL线程执行最后一步,该线程从中继日志中读取事件并在备库执行,从而实现备库数据的更新。rnrn rnrn这种复制架构最重要的一点是在主库上并发运行的查询在备库只能串行化执行,因为只有一个SQL线程来重放中继日志中的事件。这将会影响主备不同步,虽然有一些针对该问题的解决方案,但大多数用户仍然受制于单线程。rnrn rnrnbinlog dump线程最开始的时候很可能会去读取仍存储在主库日志缓存或操作系统缓存或磁盘缓存中的事件,因此这时在主库上还没有任何物理磁盘的读操作。但是,当几小时后甚至几天后从库连接到主库上,会首先开始读取几小时或几天之前写入的二进制日志,此时主库很可能不会再有这些数据的缓存,那么读磁盘操作就不可避免的发生了,如果主库没有任何的空闲的I/O资源,这时你可能会感受到主库服务震荡。rnrn rnrn下面我们着重讲一下转存(binlog dump)线程的工作原理。rnrn rnrnMySQL主从同步的实现中,从库连接到主库,并向主库发送一个COM_BINLOG_DUMP命令。该过程和一个用户访问MySQL的过程类似,主库中的binlog dump线程和用户线程都是由统一的连接管理机制管理,属于同一个线程池。不同的是binlog dump线程会一直存活。rnrnbinlog dump线程按照从库请求的binlog名字和pos找到对应的binlog文件,然后读取binlog的envent不断的发往从库,当主库处于空闲状态时,binlog dump线程会在一个信号量(update_cond即主库的binlog更新状态)上等待。rnrn rnrn这种机制存在一个问题,由于binlog dump线程在等待主库中的update_cond信号量时,不能感知到从库的变化,如果从库这时候死亡,或者执行了stop slave,则该binlog dump线程将一直存活。processlist显示线程处于:rnrnHas sent all binlog to slave; waiting for binlog to be updatedHas sent all binlog to slave; waiting for binlog to be updated。rnrn当主库的binlog中有新的写入时激活所有的binlog dump线程,这些线程再次和从库连接时才发现从库已经退出,然后该线程自己也会退出。rnrn rnrn设想在一个idle的主库中,一个从库由于网络原因失去了和主库的连接,此时其对应的binlog dump线程依然存活,网络恢复之后,该从库重新连接主库。则主库中将存在两个线程对应同一个从库。MySQL为了避免这个问题,在一个从库向主库发送COM_BINLOG_DUMP命令过程中。会遍历当前的binlog dump线程,如果发现连接上来的从库的server-id和既有线程对应的server-id相同,则将既有的那些server-id相同的线程杀死,防止一个从库对应两个binlog dump线程。rnrn rnrn如果server-id相同的两个从库连到同一个idle的主库上。则COM_BINLG_DUMP命令会不断的把另一台从库的binlog dump线程杀死,而从库由于不清楚情况,会不断的重连主库,重连导致的结果是不断的再次杀死其他server-id相同的从库。进入一个死循环。rnrn rnrn从库的错误日志中显示类似如下的日志:rnrn121016 13:42:46 [Note] Slave: connected to master 'MySQLsync@127.0.0.1:6600',replication resumed in log 'MySQL-bin.000019' at position 2345 121016 13:42:46 [Note] Slave: received end packet from server, apparent master shutdown: 121016 13:42:46 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'MySQL-bin.000019' position 2345 rnrn rnrn在主库中执行show processlist可以看见两个sql dump线程交替重连,线程ID不断变化说明,之前的线程被杀死并产生新的线程。只有停掉server-id相同的从库,才能保证COM_BINLOG_DUMP正常执行。rnrn rnrn从库退出而对应的binlog dump线程不退出,这种现象应该算是一个BUG,但是由于现有MySQL同步的实现中,主库和从库的交互性比较弱,主库只有从网络活动上才能获知从库是否存活,而在主库没有监测到网络活动时从库退出,也没有更好的办法来处理binlog dump线程。为了避免大量的binlog dump线程沦为僵尸。开发人员就在COM_BINLOG_DUMP执行的过程中进行一下清理,将server-id相同的老线程都杀死。而那些在主库idle时从库退出的dump线程则会变成僵尸,一直等到主库有binlog写入事件发生。rnrn rnrn rnrn复制线程状态rnrn主库binlog dump线程状态rnrn下面列出了主库binlog dump线程State列的最常见的状态。(如果你没有在主服务器上看见任何binlog dump线程,这说明复制没有在运行—即,目前没有连接任何从服务器)rnrn rnrnSending binlog event to slavernrn二进制日志由各种事件组成,一个事件通常为一个更新加一些其它信息。这个状态表示线程已经从二进制日志读取了一个事件并且正将它发送到从服务器。rnrn rnrnFinished reading one binlog; switching to next binlogrnrn线程已经读完二进制日志文件并且正打开下一个要发送到从服务器的日志文件。rnrn rnrnHas sent all binlog to slave; waiting for binlog to be updatedrnrn线程已经从二进制日志读取所有主要的更新并已经发送到了从服务器。线程现在正空闲,等待由主服务器上新的更新导致的出现在二进制日志中的新事件。rnrn rnrnWaiting to finalize terminationrnrn线程停止时发生的一个很简单的状态。rnrn rnrn备库I/O线程状态rnrn下面列出了从服务器的I/O线程的State列的最常见的状态。该状态也出现在Slave_IO_State列,由SHOW SLAVE STATUS显示。这说明你可以只通过该语句仔细浏览所发生的事情。rnrn rnrnConnecting to masterrnrn线程正试图连接主服务器。rnrn rnrnChecking master versionrnrn建立同主服务器之间的连接后立即临时出现的状态。rnrn rnrnRegistering slave on masterrnrn建立同主服务器之间的连接后立即临时出现的状态。rnrn rnrnRequesting binlog dumprnrn建立同主服务器之间的连接后立即临时出现的状态。线程向主服务器发送一条请求,索取从请求的二进制日志文件名和位置开始的二进制日志的内容。rnrn rnrnWaiting to reconnect after a failed binlog dump requestrnrn如果二进制日志转储请求失败(由于没有连接),线程进入睡眠状态,然后定期尝试重新连接。可以使用–master-connect-retry选项指定重试之间的间隔。rnrn rnrnReconnecting after a failed binlog dump requestrnrn线程正尝试重新连接主服务器。rnrn rnrnWaiting for master to send eventrnrn线程已经连接上主服务器,正等待二进制日志事件到达。如果主服务器正空闲,会持续较长的时间。如果等待持续slave_read_timeout秒,则发生超时。此时,线程认为连接被中断并企图重新连接。rnrn rnrnQueueing master event to the relay logrnrn线程已经读取一个事件,正将它复制到中继日志供SQL线程来处理。rnrn rnrnWaiting to reconnect after a failed master event readrnrn读取时(由于没有连接)出现错误。线程企图重新连接前将睡眠master-connect-retry秒。rnrn rnrnReconnecting after a failed master event readrnrn线程正尝试重新连接主服务器。当连接重新建立后,状态变为Waiting for master to send event。rnrn rnrnWaiting for the slave SQL thread to free enough relay log spacernrn正使用一个非零relay_log_space_limit值,中继日志已经增长到其组合大小超过该值。I/O线程正等待直到SQL线程处理中继日志内容并删除部分中继日志文件来释放足够的空间。rnrn rnrnWaiting for slave mutex on exitrnrn线程停止时发生的一个很简单的状态。rnrn rnrnI/O线程的State列也可以显示语句的文本。这说明线程已经从中继日志读取了一个事件,从中提取了语句,并且正在执行语句。rnrn rnrn备库SQL线程状态rnrn下面列出了从服务器的SQL线程的State列的最常见的状态。rnrn rnrnReading event from the relay logrnrn线程已经从中继日志读取一个事件,可以对事件进行处理了。rnrn rnrnHas read all relay log; waiting for the slave I/O thread to update itrnrn线程已经处理了中继日志文件中的所有事件,现在正等待I/O线程将新事件写入中继日志。rnrn rnrnWaiting for slave mutex on exitrnrn线程停止时发生的一个很简单的状态。rnrn rnrn过大的复制延迟rnrn如果系统在备库出现延迟时就无法很好地工作,那么应用程序也许就不应该用到复制。但是也有一些办法可以让备库跟上主库。rnrn rnrn最简单的办法是配置InnoDBrnrn使其不要那么频繁地刷新磁盘,这样事务提交的更快些。设置innodb_flush_log_at_trx_commit=2来实现。还可以在备库上禁止二进制日志记录,把innodb_locks_unsafe_for_binlog设置为1,并把MyISAM的delay_key_write设置为ALL。但是这些设置以牺牲安全换取速度。如果需要将备库提升为主库,记得把这些选项设置回安全值。rnrn rnrn不要重要写操作中代价较高的部分rnrn重构应用程序或者优化查询通常是最好的保持备库同步的办法。如果可以把工作转移备库,那么就只有一台备库需要执行,然后我们可以把写的结果回传到主库,例如,通过执行LOAD DATA INFILE。rnrn rnrn在复制之外并行写入rnrn所有写操作都应该从主库传递到备库?如果能确定一些写入可以轻易地在复制之外执行,就可以并行化这些操作以利用备库的写入容量。例如,一些归档数据,可以在主备上分别进行归档操作。rnrn rnrn为复制线程预取缓存rnrn通过程序实现,在SQL线程更新前提前读取中继日志并将其转化为SELECT语句执行。这会使服务器将数据从磁盘加载到内存中,这样SQL线程执行到相应语句时,就无需从磁盘读取数据。rnrn rnrn限制主库过大的包rnrn修改主库max_allowed_packet值,太大的包会使二进制事务变的复杂。rnrn rnrnMySQL二进制日志转存线程并没有通过轮询的方式从主库请求事件,而是由主库来通知备库新的事件,因为前者低效且缓慢。从主库读取一个二进制日志事件是一个阻塞型网络调用,当主库记录事件后,马上就开始发送。因此可以说,只要复制转存线程被唤醒并且能够通过网络传输数据,事件就会很快到达备库。rnrn rnrn rnrnMySQL复制的高级特性rnrn我们在这里着重讲下MySQL5.5版本的半同步复制和复制心跳两项新特性。rnrn rnrn半同步复制在提交过程中增加了一个延迟:当提交事务时,在客户端接收到查询结束反馈前必须保证二进制日志已经传输到至少一台备库上。主库将事务提交到磁盘上之后会增加一些延迟。同样的,这也增加了客户端延迟,因此其执行大量事务的速度不会比将这些事务传递给备库的速度更快。rnrn rnrn关于半同步,有一些普遍的误解,下面是它不会去做的:rnrn在备库提示其已经收到事件前,会阻塞主库上的事务提交。事实上在主库上已经完成事务提交,只有通知客户端被延迟了。rnrn直到备库执行完事务后,才不会阻塞客户端。备库在接收到事务后发送反馈而非完成事务后发送。rnrn半同步不总能够工作。如果备库一直没有回应已收到事件,会超时并转化为正常的异步复制模式。(试想一下,如果主备网络断了,那么半同步将直接导致性能问题)rnrn rnrn事实上半同步复制在某些场景下确实能够提供足够的灵活性以改善性能,在主库关闭sync_binlog的情况下保证更加安全。写入远程的内存(一台备库反馈)比写入本地的磁盘(写入并刷新)要更快。有人进行过测试,使用半同步复制相比在主库上进行强持久化的性能有两倍改善。在任何系统上都没有绝对的持久化,只有更高的持久化层次,并且看起来半同步复制应该是一种比其他替代方案开销更小的系统数据持久化方法。rnrn rnrn配置半同步复制rnrn rnrn在Master上安装Master插件:rnrnmaster> INSTALL PLUGIN rpl_semi_sync_master SONAME "semisync_master.so";rnrn在每台Slave上安装Slave插件:rnrnslave> INSTALL PLUGIN rpl_semi_sync_slave SONAME "semisync_slave.so";rnrn rnrn在主库上[MySQLd]配置中增加:rnrnrpl_semi_sync_master_enabled = 1rnrnrpl_semi_sync_master_timeout=milliseconds rnrn#同步复制超时时间,超时后恢复到异步复制rnrnrpl_semi_sync_master_wait_no_slave=ON|OFFrnrn#如果设为OFF,则当Master没有任何Slave时恢复异步复制,否则等待到超时rnrn rnrn在备库上[MySQLd]配置中增加:rnrnrpl_semi_sync_slave_enabled = 1rnrn rnrn另外有两个跟踪级别的配置变量:rnrnrpl_semi_sync_master_trace_level = 32rnrnrpl_semi_sync_slave_trace_level = 32rnrn rnrn监控半同步复制,可以查看以下几个状态变量信息rnrnRpl_semi_sync_master_clients,master连接了多了半同步slavernrnRpl_semi_sync_master_status,master半同步复制状态rnrnRpl_semi_sync_slave_status,slave半同步复制状态rnrn rnrn另外还有几个状态变量:rnrnRpl_semi_sync_master_net_avg_wait_time rnrnRpl_semi_sync_master_net_wait_time rnrnRpl_semi_sync_master_net_waits rnrnRpl_semi_sync_master_no_times rnrnRpl_semi_sync_master_no_tx rnrnRpl_semi_sync_master_timefunc_failures rnrnRpl_semi_sync_master_tx_avg_wait_time rnrnRpl_semi_sync_master_tx_wait_time rnrnRpl_semi_sync_master_tx_waits rnrnRpl_semi_sync_master_wait_pos_backtraversernrnRpl_semi_sync_master_wait_sessions rnrnRpl_semi_sync_master_yes_tx rnrnRpl_status rnrn rnrn除了半同步复制,MySQL5.5还提供了复制心跳,保证备库一直与主库相联系,避免悄无声息地断开连接。如果出现断开的网络连接,备库会注意到丢失的心跳数据。在备库可以用CHANGE MASTER TO MASTER_HEARTBEAT_PERIOD = interval来启动心跳监控,interval是每隔多长时间探测一下是否落后主服务器,主服务器是否在线。rnrn rnrn rnrn其它复制技术rnrnMySQL的复制之所以慢,主要是因为备库上的SQL线程是单线程顺序执行的。如果能够让其并行执行,那么就会快很多。rnrn rnrnMySQL5.6增加了多线程(并行)复制以减少当前单线程复制的瓶颈,但是它是基于分库实现的,就是说一个线程只能对应该一个库。rnrn rnrn还有就是自己实现一个SLAVE,模拟备库I/O线程,连接主库,读取二进制日志到本地,然后结合自己业务特点,表结构等,实现并行SQL更新,因为是针对自身业务,比如可以按表来分多线程,表里可以加上主键限制,保证事务的顺序执行。rnrn rnrn这里强烈推荐淘宝的MySQL多线程同步MySQL-Transfer开源工具,详细介绍参见:rnrn[url=http://wenku.it168.com/d_000355271.shtml]MySQL多线程同步-Transfer使用说明[/url]rn[url=http://v.youku.com/v_show/id_XNDc4MzU1MzQ4.html]MySQL异步复制延迟解决的架构设计与运维架构—在线播放—优酷网[/url]rn[url=http://dinglin.iteye.com/blog/search?query=transfer]追风刀·丁奇的博客[/url]rn[url=http://code.taobao.org/p/mysql-transfer/src/]源码在阿里网站上[/url] rnrn rn 论坛

没有更多推荐了,返回首页