基于mysql的日志分析系统设计

基于MySql的日志分析系统设计,漆兴 beidou77gmail.com,主要内容,日志分析系统查询需求分析 访问特点分析 基于性能考虑的系统体系架构 基于需求的mysql优化及表设计 基于需求的memcache使用 其他开源工具的使用 总结,系统简介,分析各大产品线的访问情况，以图形和图表的方式，提供各种监控及访问信息，为决策者提供可靠的数据支持 系统目前支持的分析指标有，Hits、带宽、UIP（独立用户IP）、下载速度、下载时长、响应时间、受访URL、受访域名、来路URL、来路域名、全国用户分布统计、运营商分布统计、受访文件大小、文件类型、Squid命中率、请求响应类型、异常用户统计 系统基础工作:各业务部门统一web服务器的日志格式,系统需求特点,海量数据 实时性 写多读少 系统现状:天表每天增量千万级,每天入库上1亿条。 数据库增量400G，www日志存储增量近2TB,系统部分需求展示1,系统部分需求展示2,系统整体架构,系统架构说明,该系统架构根据功能模块可分为如下节点 : A(Agent) B(Bee) D(Data) M(Manger) R(Relay),系统执行流程,采集节点,功能：负责推送日志到B点 实现过程：利用Rsync实现推送，以接口方式访问M点获取Rsync的目标地址 动作：在每五分钟内切割完日志并推送。每小时获取M点更新的配置完成自更新 数据格式：压缩后的统一规范定义的标准日志格式,运算节点,功能：根据需求分析日志并推送到D点 运算机制：逐行分析日志 + 多进程 工具:使用FaceBook的HipHop加快运算速度 频率：每两分钟调度分析脚本 分析结果：保存为文本，格式为sql语句。如insert into table values ( ),( ),( ),Relay点,存在的意义 : 保障数据传输的速度及效率，减少网络问题导致的数据阻塞及不完整性 问题重现 :电信和网通之间的互相访问问题，导致日志传输丢失或不能在规定时间内到达指定节点 解决方法 :电信服务器访问电信，网通服务器则访问网通,数据节点,功能：负责将接收到的sql文本入库 动作：在每两分钟运行入库脚本。每天定时创建分钟表(m_表)，每小时将分钟表中过去一小时的数据聚合,即h_表，每天聚合前一天的小时表数据，即天表d_，以及触发器及存储过程的调用。将最近三天的分钟表，最近三个月的小时表，定义为热数据，并定期创建为merge类型，方便程序的编写。,展示节点,数据访问接口:通过增加数据中心层来封装对数据库及缓存等数据的读取，方便程序员编写代码，减少业务逻辑 数据库代理:Amoeba 展示方式: 图形+报表+Flash 使用工具: Mysql5.1+Php5.3+Amoeba+Fushionchart+Apache +Memcache等,管理节点,功能: 掌握各大节点的系统运行状况，资源使用情况 任务列表:负责管理调度系统其他节点,管理各节点的Rsync地址，分析B点的运算结果，健康检查，日志传送数据的完整性及过期信息处理等工作 工具:Gearman 好处:Gearman使任务的分发变的更加灵活，避免登录多个节点获取信息，提高运维效率,方便多服务器管理 。,Gearman介绍,Gearman流: Client:请求的发起者 Job:请求调度人，负责把Client的请求转发给相关的Worker Worker:请求的处理人，,Gearman实例,具体实例: 在各大分析点起守护进程worker.php监听指定的端口 在M点命令行下运行client.php cmd 来执行各种工作 cmd 相关安全性检查,数据节点瓶颈分析,Vmstat下bo，wa的值都很大，磁盘随机访问量大 2. IO瓶颈:insert 频繁且量大，造成磁盘写IO增大 3. cpu瓶颈:sum,order by,group by操作比较多，cpu容易出现瓶颈 4. select:量大sending data比较耗时，索引失效，全表遍历造成磁盘读IO量大，造成读等待 5.累积伤害值:cpu过度使用造成大量进程的等待，系统响应变慢进程数累积增加，导致内存使用增加，内存耗尽则导致虚拟内存的使用，最终又导致磁盘IO和cpu的超负荷使用，其他系统开销增多，系统平衡被打破,数据节点-展示相关,表引擎:使用MyISAM,Memory 表操作:多为insert，无delete ,update Query分析:Select操作及sum,avg,group by ,order by,limit Where定向:多为时间粒度及产品线等多角度混合查询。 时间粒度:最近五分钟,最近一小时，最近25小时等 查询条件:按产品线,运营商，城市，机房，服务器,数据节点表的设计,考虑到需求上涉及到的操作时间相关,如最近五分钟，最近一天，最近一小时等,从数据库中读取的数据大且更新频繁，所以采用按时间拆表及对时间建立索引的方案,使用引擎MyISAM具体如下: 1.对各种时间粒度建立索引应对复杂的组合查询，按天，小时，每五分钟(一天288个点)建立索引。采用整形如选择2010年04月03的128个五分钟,where minf=20100403128,这种方式虽然增大了字段长度，但是对索引的查找及索引的基数的扩大都是有帮助的，属于用空间换时间。 2.使用分区特性，在每天建立的m_分钟表中按小时建子分区 3，在MyISAM表中尽量使用定长类型,数据节点表的设计续,4.将IP字段存储为整形 5.大数据量表按时间拆表 6.规范表命名 m_20100317_www_top_refere,h_,d_ 7.使用merge表 8.对于过期的只读表进行myisampack 9.使用enum 使PROCEDURE ANALYSE() 10.根据业务需求将产品线及时间建立联合索引,数据节点的优化Mysql架构优化,增加数据库节点 按产品线分库 按时间拆表,数据节点的优化单D点的优化,瓶颈: 磁盘IO 优化方式: 初期: 1.将m,h,d表的索引文件及数据文件分布到不同磁盘 2.将数据库指向不同的磁盘 3.禁止系统更新文件的atime属性,数据节点的优化单D点的优化,瓶颈: 磁盘IO是主要问题 优化方式:硬件升级 后期: 操作系统及文件系统调优 raid0 或 lvm条带化 修改相关mysql参数 sql语句的慢查询分析及索引调优,数据节点的优化单D点性能分析,没优化前: 负载比较高，时常会超过10，CPU Idle经常会小于30%，有时Idle为0，CPU io wait 比较大 优化后: CPU的负载降了一半左右，同时磁盘写入性能比之前提升了一倍之多,数据节点的优化特殊需求优化,使用tmpfs作cache磁盘(ramdisk） 优点:内存操作，没有磁盘IO消耗，不用修改应用程序 缺点:cache空间有限 Mount bind /dev/shm /var/www/cache 写一个清cache的脚本程序，配置在cron中，3每小时执行一次，检查/dev/shm的使用率超过60%时，使用find命令找出太旧的cache文件删除掉,数据节点的优化infobright使用,1.采用开源ICE版进行相关日志分析 2.将涉及到跨天及跨小时的非实时数据，导入到infobright 3.充分利用列数据的特点，提搞了select速度及减少了预处理的量，和相关统计报表工作 4.效果:千万级表，包含sum,group by,order by操作 ICE比MyISAM快几倍,数据节点的优化infobright特点,列存储 适合范围查询及群组操作，查询高效 服务形式及接口跟mysql一样，学习成本低 高压缩比列，减少磁盘空间 无需建索引，避免索引的维护及增长问题 缺点： ICE版无DML操作，但支持load data infile,数据节点的优化硬件,Scale up 方案 目的 :增大系统的I/O吞吐量 Raid 或 LVM条带化,数据节点的优化Mysql应用优化,适当的数据冗余，尽量避免数据库的join操作 几个时间段对比操作，使用union的效率高于in 和or的连表操作 对热数据进行预处理,避免用户引发计算,所有计算结果尽可能提前生成,后台程序生成结果直接调用 频繁更新的表，使用Memory表 对于不定长的字段类型可指定前缀长度,MySQL参数设置,1. 提高read_buffer_size的值 2.高并发插入优化 Concurrent-insert =2 3.delay_key_write 4. bulk_insert_buffer_size, max_allowed_packet 5.关闭query_cache 6. key_buffer及key_buffer_size的值增大 7.sort_buffer_size，并不是越大越好 8.加大max_length_for_sort_data,对于big result让其采用改进版的排序算法,MySQL相关设置,1.增大tmp_table_size 2.增大table_cache及thread_cache的值，避免频繁建立和断开链接 3.用mysql_unbuffered_query取代mysql_query, 4.用mysql_pconnect取代mysql_connect 5.使用SQL_BIG_RESULT来提示mysql优化引擎更好的处理大数据量sql 6.使用MyISAM表可使用索引数据的预加载功能,数据节点的优化多D点的架构,展现层向Proxy发起Query请求，Proxy将请求分发到多个DB，然后将结果合并后返回 当单个Proxy负载过高的时候，可以启用多个Proxy，展现层通过简单的取模来连接不同的Proxy,数据节点的优化多D点的设计,启用多个D点(比如分静态池和动态池)，单独产品线的从某个D点取数据，跨产品线的时候从多个D点取数据并进行合并。测试了如下方案: 1.基于php5.3的Mysqlnd 2.Ameoba,多D点方案测试:mysqlnd,如图:mysqlnd少了从mysql驱动中复制数据到php扩展这一步。 更亮的特点是:异步获取数据的能力,多D点方案测试:mysqlnd,吸引力:除了性能上的提升,mysqlnd支持异步获取数据 困难:需要改动应用层的取数据函数，因为之前这部分代码不统一，所以需要改动不少 从测试来看，异步取多个数据库的结果时，可能会出现返回数据不正确，以及执行顺序错误等状况 (怀疑是和Apache在一起使用的问题，CLI下正常),多D点方案测试:Amoeba,Amoeba测试结果: 支持高可用性，负载均衡。 对多数据库读取的结果只是分别执行然后直接拼接 高并发情况下，有时会出现到Amoeba的连接无响应 高版本下高并发的性能表现已经改善不少,数据节点的优化结果,通过上面几个方案的测试, 架构调整选择Amoeba Proxy是目前比较合适的方案，数据切分可以通过XML灵活配置，对应用层的改动比较小，也相对比较稳定. 由于磁盘做radio0，对数据的保护不够，所以要加入备份的考虑及产品线增多后数据缓存的利用率,数据节点的优化缓存,Memcache 客户端缓存数据 页面静态化 Php级opcode缓存 xCache,数据节点的优化Memcache,数据点的优化Memacahe应用,memcache有1m限制。如果列表太大,采取拆分数据，用key+特殊标识来保存整个序列。在获取的时候批量get来一次性得到这个列表。 预处理:提前生成需求数据到cache中 写库:进行数据的预处理,写入到memcache服务器中 读库 :根据时间选择应该已在cache中的数据+最近生成的数据 拼成最新数据展现 缺点:维护多个存储操作增加了应用层逻辑复杂度 优点:减少从数据库读取海量数据的问题及避免重复计算,数据节